商务与经济统计案例分析:2-3皇后市——python 实现

要求

1.各类别支出总额和各类别支出百分数的表格与图形显示
2.各部门支出总额和各部门支出占总支出百分数的表格显示，将小于1%的部门合并到“其他”类
3.各基金支出总额和各基金支出占总支出百分数的表格显示，将小于1%的基金合并到“其他”类

Python实现

1.各类别支出总额和各类别支出百分数的表格与图形显示

import pandas as pd
import numpy as np
# 数据读取与概览
data = pd.read_csv('F:\DataAnalysis\StatisticForBusinessAndEconomics\QueenCity\\QueenCity.csv')
data.head()()

#处理费用列中数据的$,()等值
data['Expenditure'] = data['Expenditure'].str.replace(",","").str.replace("$","").str.replace("(","-").str.replace(")","")
data['Expenditure'] = data['Expenditure'].astype('float')

#按类别进行汇总
table = data.pivot_table(values=["Expenditure"], index=["Category"], aggfunc=[np.sum], fill_value=0)
table['ratio']=table.values/sum(data["Expenditure"])
table['ratio']=table['ratio'].apply(lambda x: format(x, ".3%"))

table

2.各部门支出总额和各部门支出占总支出百分数的表格显示，将小于1%的部门合并到“其他”类

table1 = data.pivot_table(values=["Expenditure"], index=["Department Description"], aggfunc=[np.sum], fill_value=0)
table1['ratio']=table1.values/sum(data["Expenditure"])
table1["type"] = pd.cut(table1["ratio"], [0, 0.01, 1], labels=['other', ''])
table1['ratio']=table1['ratio'].apply(lambda x: format(x, ".3%")) 


table1

3.各基金支出总额和各基金支出占总支出百分数的表格显示，将小于1%的基金合并到“其他”类

table2 = data.pivot_table(values=["Expenditure"], index=["Fund"], aggfunc=[np.sum], fill_value=0)
table2['ratio']=table2.values/sum(data["Expenditure"])
table2["type"] = pd.cut(table2["ratio"], [0, 0.01, 1], labels=['other', ''])
table2['ratio']=table2['ratio'].apply(lambda x: format(x, ".3%")) 


table2