第7节，pandas汇总excel数据

现有如下数据

要求按照城市汇总，算出每个城市的店铺数量和销售总额。

1. 打开文件

import pandas as pd


result_file = "./data/数据汇总.xlsx"
df = pd.read_excel(result_file)

2. groupby

# 店铺数量
# 店铺数量
count_series = df.groupby('城市')['店铺'].count()
print(count_series)

以城市做groupby，统计店铺的数量，输出结果为

城市
上海    4
北京    3
Name: 店铺, dtype: int64

count_series 的类型是series，将其转为DataFrame

count_df = pd.DataFrame(count_series)       # 转成df
count_df['城市'] = count_df.index            # 以索引生成城市列
count_df.reset_index(drop=True, inplace=True)  # 去掉索引
print(count_df)

输出内容

   店铺  城市
0   4  上海
1   3  北京

对销售额做同样的操作

# 店铺销售额
sale_series = df.groupby('城市')['销售额'].sum()
sale_df = pd.DataFrame(sale_series)
sale_df['城市'] = sale_df.index
sale_df.reset_index(drop=True, inplace=True)

3. merge 合并

merge_df = pd.merge(sale_df, count_df, on='城市')         # 以城市为基准合并
merge_df = merge_df[['城市', '店铺', '销售额']]             # 重新排列顺序
merge_df.rename({'店铺': '店铺数量', '销售额': '销售总额'})   # 重新命名

4. 回写excel

# mode 设置为a 表示追加sheet
writer = pd.ExcelWriter(result_file, mode="a", engine='openpyxl')
merge_df.to_excel(writer, sheet_name="销售数据汇总", index=False)
writer.save()

5.全部代码

import pandas as pd


result_file = "./data/数据汇总.xlsx"
df = pd.read_excel(result_file)

# 店铺数量
count_series = df.groupby('城市')['店铺'].count()
count_df = pd.DataFrame(count_series)
count_df['城市'] = count_df.index
count_df.reset_index(drop=True, inplace=True)

# 店铺销售额
sale_series = df.groupby('城市')['销售额'].sum()
sale_df = pd.DataFrame(sale_series)
sale_df['城市'] = sale_df.index
sale_df.reset_index(drop=True, inplace=True)

merge_df = pd.merge(sale_df, count_df, on='城市')
merge_df = merge_df[['城市', '店铺', '销售额']]
merge_df.rename({'店铺': '店铺数量', '销售额': '销售总额'})

# mode 设置为a 表示追加sheet
writer = pd.ExcelWriter(result_file, mode="a", engine='openpyxl')
merge_df.to_excel(writer, sheet_name="销售数据汇总", index=False)
writer.save()