# 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')
读取数据 筛选数据 数据透视 聚合数据 写入文件
最新推荐文章于 2024-03-31 19:10:28 发布