import pandas as pd
import openpyxl
################################
# 微信 & 支付宝 -> 合并列，打印各清洗多少条数据
#   各分类列
# 其他类，等待手动分类
#   其他类列
#
# 打印各个分类总金额 & 其他类总金额，微信收款单列一项
# 简餐（40-）	零食	饕餮&社交（40+）	交通	大件好物	购物欲	衣物	通信&月付产品	学习&文具	日用起居	放松	医疗	房租	其他													
################################


def aggregate(path_merge):
    df = pd.read_excel(path_merge, skipfooter=14)
    sum = pd.Series(df['金额'].sum())
    groupby_sum = df.groupby('分类')[['金额']].sum().astype(int)
    groupby_max = df.groupby('分类')[['金额']].max()
    groupby = pd.merge(groupby_sum, groupby_max, on='分类', suffixes=('_sum', '_max'))
    sorted_df = df.sort_values(by=['分类', '商品'], ascending=False, ignore_index=True)
    
    groupby = pd.merge(groupby.index.to_series(), groupby, left_index=True, right_index=True)  # add the column from index
    groupby = pd.concat([groupby, sum], ignore_index=True)
    groupby = groupby.rename(columns={0: '总支出'})
    print(groupby)

    return groupby, sorted_df
    
if __name__ == '__main__':
    path_merge = 'merge.xlsx'
    path_result = 'result.xlsx'
    groupby, sorted_df = aggregate(path_merge)
    groupby.to_excel(path_result, sheet_name="Aggregation", index=False)  # save
    
    book = openpyxl.load_workbook(path_result)
    writer = pd.ExcelWriter(path_result, engine='openpyxl')   
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    sorted_df.to_excel(writer, sheet_name="Sheet1", index=False)  # save

    # format xl
    sheet = book['Sheet1']
    sheet.column_dimensions['F'].width = 40
    sheet.column_dimensions['E'].width = 20
    sheet.column_dimensions['A'].width = 20
    book.save(path_result)

    print("write successfully!\n")