利用pandas处理二级office的Excel试题（一）

最近在学《利用pandas进行数据分析》，想到计算机二级office就有现成的实例，虽然比较基础，但是还是适合练练手。

一、试题概览

这是二级office的其中一套试题，对于题目中的设置单元格列宽和对其方式等就不在pandas中展示，我们直接从第5题开始。

二、初始表格

这是素材文件的初始表格。

三、实例

按要求计算均值

第五题要求求出每个月的总支出、各个类别的月均支出、每月平均总支出、最后按照总支出升序排序。

table=pd.read_excel("Excel素材.xlsx",header=1)
#读取文件
table["总支出"]=table.iloc[:,2:-1].sum(axis=1)
#总支出先取出要求和的列，再利用sum函数
table

然后是各个类别的月均支出

classify_cost=table.iloc[:,2:-1].mean(axis=0)
#同样选出相应的数据，利用mean函数
classify_cost=classify_cost.round(2)
#这里利用round()方法将数据保留为两位小数
classify_cost

最后是升序排序

table.sort_values(by="总支出",ascending=True)
#ascending=False则为降序排序

条件格式

设置条件格式要利用pandas的style属性，相关详细的文档可以参考官方文档：https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html
首先是将开支金额大于1000的用红色填充。

def color_negative(val):
    color="red" if val>1000 else "black"
    return "color:%s"%color
table.iloc[:,2:-1].style.applymap(color_negative)

插入季度

根据月份插入季度，我们首先提取出月份的数值即去掉“月”这个字，只保留具体的数值

month_num=table["月"].str[:-1].astype("int")
#这里要注意将类型转换为int类型

然后使用pandas的cut方法进行分类

season=pd.cut(month_num,4,labels=["1季度","2季度","3季度","4季度"])
season

当然也可以写一个字典，然后用map方法

dic={"1月":"1季度","2月":"1季度","3月":"1季度","4月":"2季度","5月":"2季度",
    "6月":"2季度","7月":"3季度","8月":"3季度","9月":"3季度","10月":"4季度",
    "11月":"4季度","12月":"4季度"}
table["季度"]=table["月"].map(dic)

分类汇总

分类汇总求出每个季度各类的月均支出金额，这里可以直接用pivot_table方法也可以使用groupby

new_table=table.pivot_table(["服装服饰","饮食","水电气房租","交通","通信","阅读培训","社交应酬","医疗保健","休闲旅游","个人兴趣","公益活动"],
 index="季度",aggfunc="mean").round(2)
new_table

new_table=table.groupby(["季度"])["服装服饰","饮食","水电气房租","交通","通信","阅读培训",
"社交应酬","医疗保健","休闲旅游","个人兴趣","公益活动"].mean().round(2)

折线图

new_table.T.plot(figsize=(12,9))
#这里使用了numpy的转置方法，将表的行列互换，更好地绘图