归类求和汇总

 #!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Thu Aug 11 21:14:40 2022
@author: m
"""
 
import pandas as pd
import numpy as np
import tkinter as tk
import os
import threading
#import datetime as dt
root=tk.Tk()
 
canvas1 = tk.Canvas(root, width = 300, height = 300) # create the canvas
canvas1.pack()
entry1 = tk.Entry (root)
canvas1.create_window(150, 100, window=entry1)
 
 
def posi():
    global x
    x = str(entry1.get())
    position_path=r'C:\\Users\\ma\\Downloads\\持仓_' + x + '.csv' #r'/Users/m/Documents/In/持仓_' + x + '.csv'
    position=pd.read_csv(position_path,header=0,encoding="gbk",thousands=',') 
#    date = str(dt.date.today())
    position['product']=position['合约'].str[:2] #添加product列
    PL_instr_tmp=position.pivot_table(index=['product','合约'],values='持仓盈亏',aggfunc=np.sum,margins=True,margins_name='All').round(2)
    PL_prod=position.pivot_table(index='product',values='持仓盈亏',aggfunc=np.sum,margins=True,margins_name='All').round(2)
    tmp_Path = 'C:\\Users\\ma\\Downloads\\持仓_PL_tmp.xlsx'
    PL_instr_tmp.to_excel(tmp_Path,sheet_name = "PL",index = True,na_rep = 0,inf_rep = 0)
    PL_instr=pd.read_excel(tmp_Path,header=0)
    PL_unrel=pd.merge(PL_instr,PL_prod,on='product',how='left')
    PL_unrel.rename(columns={'product':'品种','持仓盈亏_x':'持仓盈亏_合约','持仓盈亏_y':'持仓盈亏_品种'},inplace=True)
    result_path = r'C:\\Users\\ma\\Downloads\\持仓_PL'+ x + '.xlsx'
    PL_unrel.to_excel(result_path,sheet_name = "PL",index = False)
    os.remove(tmp_Path)
 
def deal():
    global y
    y = str(entry1.get())
    deal_path=r'C:\\Users\\ma\\Downloads\\成交记录_' + y + '.csv'
    deal=pd.read_csv(deal_path,header=0,encoding="gbk",thousands=',') 
#    date = str(dt.date.today())
    deal['product']=deal['合约'].str[:2] #添加product列
    PL_instr_tmp=deal.pivot_table(index=['product','合约'],values='平仓盈亏',aggfunc=np.sum,margins=True,margins_name='All').round(2)
    PL_prod=deal.pivot_table(index='product',values='平仓盈亏',aggfunc=np.sum,margins=True,margins_name='All').round(2)
    PL_instr_tmpblt=deal.pivot_table(index=['product','合约'],values='平仓盈亏(逐笔)',aggfunc=np.sum,margins=True,margins_name='All').round(2)
    PL_prod_blt=deal.pivot_table(index='product',values='平仓盈亏(逐笔)',aggfunc=np.sum,margins=True,margins_name='All').round(2)
    tmp_Path = 'C:\\Users\\ma\\Downloads\\成交记录_PL_tmp.xlsx'
    tmp_Path_blt = 'C:\\Users\\ma\\Downloads\\成交记录_PL_tmpblt.xlsx'
    PL_instr_tmp.to_excel(tmp_Path,sheet_name = "PL",index = True,na_rep = 0,inf_rep = 0)
    PL_instr_tmpblt.to_excel(tmp_Path_blt,sheet_name = "PL",index = True,na_rep = 0,inf_rep = 0)
    PL_instr=pd.read_excel(tmp_Path,header=0)
    PL_instr_blt=pd.read_excel(tmp_Path_blt,header=0)
    PL_unrel1=pd.merge(PL_instr,PL_prod,on='product',how='left')
    PL_unrel2=pd.merge(PL_instr_blt,PL_prod_blt,on='product',how='left')
    PL_unrel=pd.merge(PL_unrel1,PL_unrel2,on=['product','合约'],how='left')
    PL_unrel.rename(columns={'product':'品种','平仓盈亏_x':'平仓盈亏_合约','平仓盈亏_y':'平仓盈亏_品种','平仓盈亏(逐笔)_x':'平仓盈亏(逐笔)_合约','平仓盈亏(逐笔)_y':'平仓盈亏(逐笔)_品种'},inplace=True)
    result_path = r'C:\\Users\\ma\\Downloads\\成交记录_PL'+ y + '.xlsx'
    PL_unrel.to_excel(result_path,sheet_name = "PL",index = False)
    os.remove(tmp_Path)
    os.remove(tmp_Path_blt)

 
    
def thread_it(func, *args):
    t = threading.Thread(target=func, args=args)
    t.setDaemon(True)
    t.start()    
 
 
button1=tk.Button(root,text='持仓文件日期 (yymmdd)',command=lambda: thread_it(posi, ),fg='black' ) #生成button1
button2=tk.Button(root,text='成交记录日期 (yymmdd)',command=lambda: thread_it(deal, ),fg='black' ) #生成button2
canvas1.create_window(150, 140, window=button1)
canvas1.create_window(150, 180, window=button2)
     
root.mainloop()