读取数据 筛选数据 数据透视 聚合数据 写入文件

# coding=utf-8
import pandas as pd
import datetime


pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.float_format', lambda x: '%.f' % x)
plt.rcParams['font.family'] = 'SimHei'

IO = 'D:\空运****\****.xlsx'
data = pd.read_excel(io=IO)
delta = datetime.timedelta(days=2)
data['周数'] = (data['航班日期'] + delta).dt.isocalendar().week
data['月份'] = data['航班日期'].dt.month


start_date = '2022-01-01'
end_date = '2022-01-15'
week_num = 3
month_num = 1


month_end_date_data = data[(start_date <= data['航班日期']) & (data['航班日期'] <= end_date)]
month_end_date_data_revenue_table = pd.pivot_table(month_end_date_data, index=['流向'],
                                                   values=['提单号', '计费重', '应收总额'],
                                                   aggfunc={'提单号': len, '计费重': sum, '应收总额': sum},
                                                   margins=True).reset_index()
month_end_date_data_revenue_table['计费重'] = month_end_date_data_revenue_table['计费重']/1000
month_end_date_data_revenue_table['应收总额'] = month_end_date_data_revenue_table['应收总额']/10000
month_end_date_data_revenue_table = month_end_date_data_revenue_table[['流向', '提单号', '计费重', '应收总额']]
title_1 = str(start_date[-5:] + '至' + end_date[-5:])


week_data = data[data['周数'] == week_num]
week_data_revenue_table = pd.pivot_table(week_data, index=['流向'], values=['提单号', '计费重', '应收总额'],
                                         aggfunc={'提单号': len, '计费重': sum, '应收总额': sum},
                                         margins=True).reset_index()
week_data_revenue_table['计费重'] = week_data_revenue_table['计费重']/1000
week_data_revenue_table['应收总额'] = week_data_revenue_table['应收总额']/10000
week_data_revenue_table = week_data_revenue_table[['流向', '提单号', '计费重', '应收总额']]
title_2 = '第' + str(week_num) + '周'


month_data = data[data['月份'] == month_num]
month_data_revenue_table = pd.pivot_table(month_data, index=['流向'], values=['提单号', '计费重', '应收总额'],
                                          aggfunc={'提单号': len, '计费重': sum, '应收总额': sum},
                                          margins=True).reset_index()
month_data_revenue_table['计费重'] = month_data_revenue_table['计费重']/1000
month_data_revenue_table['应收总额'] = month_data_revenue_table['应收总额']/10000
month_data_revenue_table = month_data_revenue_table[['流向', '提单号', '计费重', '应收总额']]
title_3 = '2022年' + str(month_num) + '月'


writer = pd.ExcelWriter(r'D:\*****\*****.xlsx')
month_end_date_data_revenue_table.to_excel(writer, sheet_name=title_1, index=False)
week_data_revenue_table.to_excel(writer, sheet_name=title_2, index=False)
month_data_revenue_table.to_excel(writer, sheet_name=title_3, index=False)
writer.save()


print('success')