头歌Python实训——pandas数据处理

第1关：将超市销售excel文件根据商品的类别筛选存储

任务描述

本关任务： 超市销售数据如图所示

建立excel文件“类别销售”，根据不同类别建立多个工作表，将相同类别的销售信息存放在相应的工作表中。

相关知识

为了完成本关任务，你需要掌握：1.读取excel文件，2.筛选dataframe数据,3.将数据写入工作簿和工作表

从excel文件读入数据

1. pd.read_excel(filename,sheet_name=0,header=0,index_col=None,names=None,dtype=None) filename:指定电子表格的具体路径
2. sheet_name:指定需要读取电子表格中的第几个sheet，既可以传递整数也可以传递具体的Sheet名称
3. header：是否需要将数据集的第一行用作表头，默认为是需要的
4. index_col:指定哪些列用作数据框的行索引（标签）
5. names:如果原数据集中没有字段，可以通过该参数在数据读取时给数据框添加具体的表头 
6. dtypes:通过字典的形式，指定某些列需要转换的形式 

向excel文件写入数据

1. DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, encoding=None)
2. excel_writer : 文件路径或现有的ExcelWriter
3. sheet_name :字符串,表的名称。默认“Sheet1”
4. na_rep : 字符串,默认‘ ’,缺失数据表示方式
5. float_format : 字符串,默认None。格式化浮点数的字符串
6. columns : 列表，要写入的列 header : 布尔或字符串列表，默认为Ture。如果给定字符串列表，则它是列名。
7. index :布尔,默认的Ture。行名（索引）
8. index_label : 字符串或序列，默认为None。 注意：通过ExcelWriter写入后要同执行save()保存。

DataFrame

唯一值函数

unique()是以数组形式返回列的所有唯一值

筛选数据 Pandas的.loc参数可以对多列数据进行筛选，并且支持不同筛选条件逻辑组合。

常用的筛选条件包括等于(==)，不等于(!)，大于(>)，小于(<)，大于等于(>=) ，小于等于(<=)等等。逻辑组合包括与(&)和或(|)。

1. list1=[["张三",'男'],["李四",'女'],["王五",'男'],["赵六",'女']]
2. import panda as pd
3. df=pd.DataFrame(list1,columns=["姓名","性别"])

则df

1. 姓名 性别
2. 0 张三 男
3. 1 李四 女
4. 2 王五 男
5. 3 赵六 女
6. df["性别"].unique()
7. “男”,"女"
8. df.loc[df["性别"]=="男"]
9. 姓名 性别
10. 0 张三 男
11. 2 王五 男

编程要求

根据提示，在右侧编辑器补充代码，建立excel文件“类别销售”，根据不同类别建立多个工作表，将相同类别的销售信息存放在相应的工作表中。

测试说明

平台会对你编写的代码进行测试： 输入类别，系统将显示该类别工作表的日期，商品编码,商品名称和合计金额四列信息

测试输入：调料；

预期输出：

1. 日期 商品编码 商品名称 合计金额
2. 0 2000-05-30 \t090202010048 \t裕荣虾味先虾条(原味)80g 7.7
3. 1 2000-06-22 \t090202010071 \t四季宝颗粒花生酱340g 13.3
4. 2 2000-06-24 \t090202010035 \t牛头牌鸡汤块66g 7.7
5. 3 2000-07-18 \t090202010024 \t金兰拌面拌饭酱370g 20.4

测试输入：电池；

预期输出：

1. 日期 商品编码 商品名称 合计金额
2. 0 2000-06-14 \t010105010002 \t金霸王7号2粒 5.2
3. 1 2000-06-14 \t010105010005 \t金霸王超能量（m3）7号2粒 6.2
4. 2 2000-07-16 \t010105010007 \t南孚电池7号5粒装 8.9

代码内容

1. import pandas as pd
2. df=pd.read_excel("xlscl/step1/超市销售数据.xlsx",dtype={"商品编码":str,"商品条码":str})
3. writer = pd.ExcelWriter("xlscl/step1/类别销售.xlsx")
4. #代码开始
6. df_ = df["类别"].unique()
7. for x in df_:
8. dfdata = df.loc[df["类别"]==x]
9. dfdata.to_excel(writer, sheet_name=x, index=False)
11. writer.save()
13. #代码结束

第2关：将银行信息excel文件按地区筛选存储

任务描述

本关任务：编写一个将银行信息的excel文件筛选存储的小程序 银行分布excel文件如图所示

请编写程序，在test文件夹下建立excel文件银行一线城市，将北京市、上海市、广州市、深圳市的银行编号、名称按银行编号从小到大的顺序放置到不同的excel工作表中

提示：可以将城市的名字放入列表

代码内容

1. import pandas
2. writer = pandas.ExcelWriter('test/银行一线城市.xlsx')
3. data=pandas.read_excel("test/银行信息.xlsx",dtype={"银行编号":str})
4. #代码开始
6. dfdata1 = data.loc[data["城市"] == "北京市", ["银行编号", "名称"]]
7. dfdata1 = dfdata1.sort_values("银行编号")
8. dfdata1.to_excel(writer, sheet_name="北京市", index=False)
10. dfdata2 = data.loc[data["城市"] == "上海市", ["银行编号", "名称"]]
11. dfdata2 = dfdata2.sort_values("银行编号")
12. dfdata2.to_excel(writer, sheet_name="上海市", index=False)
14. dfdata3 = data.loc[data["城市"] == "广州市", ["银行编号", "名称"]]
15. dfdata3 = dfdata3.sort_values("银行编号")
16. dfdata3.to_excel(writer, sheet_name="广州市", index=False)
18. dfdata4 = data.loc[data["城市"] == "深圳市", ["银行编号", "名称"]]
19. dfdata4 = dfdata4.sort_values("银行编号")
20. dfdata4.to_excel(writer, sheet_name="深圳市", index=False)
21. '''
22. list_ = ["北京市", "上海市", "广州市", "深圳市"]
23. for x in list_:
24. df = data.loc[data["城市"]==x, ["银行编号", "名称"]]
25. df = df.sort_values("银行编号")
26. df.to_excel(writer, sheet_name=x, index=False)
27. '''
28. #代码结束
29. writer.save()

第3关：将gdpecxcel文件按年份筛选存储

任务描述

本关任务：编写一个将GDP信息的excel文件筛选存储的小程序 各省GDPexcel文件如图所示

请编写程序，在test文件夹下建立excel文件GDP分年份，将2000-2016年每年建一个工作表，将该年各省的省份、GDP称按GDP从大到小的顺序放置 注意：工作表的名称为字符形

DataFrame类型的排序数据

pandas中的sort_values()函数，可以将数据集依照某个字段中的数据进行排序，该函数即可根据指定列数据也可根据指定行的数据排序。

DataFrame.sort_values(by=”列名”,axis=0,ascending=True，inplace=False)

by:axis轴上的某个索引或索引列表

axis=0按列排序 axis=1按行排序 默认按列

ascending是否按指定列的数组升序排列，默认为True，即升序排列

inplace是否用排序后的数据集替换原来的数据，默认为False，即不替换

代码内容

1. import pandas
2. writer = pandas.ExcelWriter('test/GDP分年份.xlsx')
3. data=pandas.read_excel("test/各省GDP.xlsx",dtype={"年份":str},)
4. #代码开始
6. for i in range(2000, 2017):
7. df = data.loc[data["年份"]==str(i),["省份", "GDP"]]
8. df = df.sort_values(by='GDP', ascending=False)
9. df.to_excel(writer, sheet_name=str(i), index=False)
10. #代码结束
11. writer.save()

第4关：统计超市销售excel文件各类别和各日的数据，并将统计结果存入新的工作簿

任务描述

本关任务： 打开超市销售数据工作簿

使用excel建立一个统计数据工作簿，建立一个工作表类别统计，按合计金额降序显示不同类别销售金额的和。

建立一个工作表日期统计，按日期升序显示不同日期销售金额的和

相关知识

为了完成本关任务，你需要掌握：1.DataFrame如何汇总数据，2.DataFrame如何排序数据。

DataFrame类型的汇总数据

Groupby可以根据一个或多个键对DataFrame计算分组摘要统计， 如计数、求和、平均值、标准差，或用户自定义函数。

例如

1. list1=[["张三",'男'],["李四",'女'],["王五",'男'],["赵六",'女']]
2. import pandas as pd
3. df=pd.DataFrame(list1,columns=["姓名","性别"])

则df

1. 姓名 性别
2. 0 张三 男
3. 1 李四 女
4. 2 王五 男
5. 3 赵六 女
6. df.groupby(["性别"])["姓名"].count()
7. 性别
8. 女 2
9. 男 2
10. DataFrame.groupby(by=None, axis=0, as_index=True)
11. by :标签或标签列表；用于确定分类的列。
12. axis : 接收 0/1；用于表示沿行(0)或列(1)分割。
13. as_index：默认Ture，汇总后会建立一个序列存放汇总的结果。汇总的列是序列的index索引，统计值的列是序列的数值。
14. 指定为False则汇总结果是一个数据集，汇总列和统计值都是列。

序列类型的排序

序列的sort_values()函数，可以将序列依照数据进行排序。 series1.sort_values(ascending=True，inplace=False) ascending是否按指定列的数组升序排列，默认为True，即升序排列 inplace是否用排序后的数据集替换原来的数据，默认为False，即不替换 序列的sort_index()函数，可以将序列依照索引列进行排序

编程要求

根据提示，在右侧编辑器补充代码，使用excel建立一个统计数据工作簿，建立一个工作表类别统计，按合计金额降序显示不同类别销售金额的和。 建立一个工作表日期统计，按日期升序显示不同日期销售金额的和

测试说明

平台会对你编写的代码进行测试： 输出两个工作类别统计工作表和日期统计工作表的数据

代码内容

1. import pandas as pd
2. df=pd.read_excel("xlscl/step1/超市销售数据.xlsx")
3. writer = pd.ExcelWriter('xlscl/step2/统计数据.xlsx')
4. #代码开始
6. df_type = df.groupby(["类别"])["合计金额"].sum()
7. df_date = df.groupby(["日期"])["合计金额"].sum()
9. df_type.sort_values(ascending=False, inplace=True)
12. df_type.to_excel(writer, sheet_name="类别统计")
13. df_date.to_excel(writer, sheet_name="日期统计")
15. writer.save()
18. #代码结束

第5关：将超市销售excel文件分别存放在多个日期工作簿的不同类别工作表中

任务描述

本关任务： 超市销售数据如图所示 在xlscl/step3/rq文件夹下根据销售的日期建立不同的excel文件，

将同一类别的销售信息存放在excel文件的不同工作表中。

并建立类别统计工作表，显示该日各类别的合计金额的和，按合计金额的降序排列。

编程要求

根据提示，在右侧编辑器补充代码。 注意：由于文件名只能是字符形，所以需要将日期型数据转换为字符形 可以使用 str(日期时间数据).replace('-',"")[:8] 将日期时间的字符的-删除再取前8个字符 此外，文件需要保存再xlscl的step3文件夹下的rq文件夹下

测试说明

平台会对你编写的代码进行测试：

测试输入： 20000525 零食

预期输出：

1. 商品编码 商品名称 合计金额
2. 0 90101030095 \t康师傅苏打夹心香草巧克力饼干96g 4.5
3. 1 90101030100 \t百力滋巧克力味50G 3.6
4. 2 90101070032 \t糙米馆荞麦150g 6.9
5. 3 90101090052 \t益民盐津杨梅180g 6.9
6. 4 90101090054 \t益民精盐桃肉160g 6.9
7. 5 90101090055 \t益民咸话梅110g 6.9
8. 6 90101090057 \t益民多味山楂180g 6.9
9. 7 90101090058 \t益达无糖口香糖蓝莓味13.5g 1.9
10. 8 90102010035 \t黑塘麦芽饼500g 27.8
11. 9 90103010048 \t渝业烧烤豆干80g 1.9
12. 10 90103010053 \t金锣Q香香肠老汤味100g 2.1
13. 类别 合计金额
14. 0 酒 384.0
15. 1 烟 344.5
16. 2 零食 76.3
17. 3 饮料 38.2
18. 4 冷饮 22.0
19. 5 奶制品 15.5
20. 6 方便面 3.5
21. 7 纸制品 2.1

测试输入： 20000530 烟

预期输出：

1. 商品编码 商品名称 合计金额
2. 0 90501000012 \t黄鹤楼（硬雅香） 40
3. 1 90501000016 \t黄金叶（软大金圆） 20
4. 2 90501000021 \t南京（红） 22
5. 类别 合计金额
6. 0 零食 115.1
7. 1 烟 82.0
8. 2 饮料 49.2
9. 3 冷饮 22.5
10. 4 奶制品 9.8
11. 5 调料 7.7
12. 6 纸制品 6.1
13. 7 方便面 3.5

代码内容

1. import pandas as pd
2. df=pd.read_excel("xlscl/step1/超市销售数据.xlsx",dtype={"商品编码":str,"商品条码":str})
3. #代码开始
5. df_dates = df["日期"].unique() # 日期list
6. for date in df_dates:
7. file_name = str(date).replace('-', '')[:8]
8. writer = pd.ExcelWriter('./xlscl/step3/rq/'+file_name+'.xlsx') # 文件
10. df_data = df.loc[df["日期"]==date] # date日期内数据
11. df_types = df_data["类别"].unique() # date日期内的类别list
13. for type in df_types:
14. df_type0 = df_data.loc[df["类别"]==type] # 单一类别信息
15. df_type0.to_excel(writer, sheet_name=type)
17. df_type1 = df_data.groupby(["类别"])["合计金额"].sum()
18. df_type1.sort_values(ascending=False, inplace=True)
19. df_type1.to_excel(writer, sheet_name="类别统计", index_label="类别")
20. writer.save()
21. #代码结束