【Python實戰(zhàn)私活】200元：批量Excel的Vlookup和透視

金融表格信息自動化提取填表

花了一晚上的時間才算把這個單子啃完，算上定金賺了200塊，業(yè)務邏輯不是很難，難點是表格的處理很繁瑣。先炫耀下成果:)

項目需求如下：

一共有4個表，我們姑且稱它們?yōu)楸?，2，3，4吧，表1和表2的買入方和賣出方，需要分別取表3和表4，進行全名替換，也就是如果有簡稱，需要去表3和表4中將對應的機構(gòu)名替換為全稱，替換完以后，要按照表1中標黃的6個字段和表2進行比對，如果有全部相同的選項，那就是經(jīng)紀人經(jīng)手的交易，需要在后續(xù)生成的透視表中單獨生成一列，也就是是否有經(jīng)紀人作為標記，其次，最終的展現(xiàn)結(jié)果需要表1中的所有數(shù)據(jù)保持原樣，也就是咱們剛才做的機構(gòu)名替換我還要還原回去，但是在這個原樣表旁邊，多了一個買入和賣出機構(gòu)的信息表，這里面要他們的替換名，屬實說我當時覺得代碼可能會太過啰嗦，不過好在想到了簡單的處理辦法。除了這個表，就是經(jīng)紀人表，經(jīng)紀人表需要原始數(shù)據(jù)保持不變，同樣要生成買入機構(gòu)和賣出機構(gòu)的相關交易信息，同樣涉及數(shù)據(jù)的替換和還原。

先看下處理后的數(shù)據(jù)圖：

表1：這個表格就是全部的交易明細數(shù)據(jù)，它是有可能由多個工作表組成的數(shù)據(jù)集，我的代碼需要將這些信息想做整合，然后才能實現(xiàn)接下來的需求?？紤]到表格眾多，為了使程序具有復用性，我全部把每個功能封裝進了函數(shù)中，看下表格1的處理代碼：

#?合并表1中所有的工作表
def?df_form1(file_path):
????app?=?x?w.App(visible=False,add_book=False)
????wb?=?app.books.open(file_path)
????df_list?=?[]
????for?i?in?wb.sheets:
????????df?=?i.range('A4').expand('table').options(pd.DataFrame).value
????????df_list.append(df)
????df_form1?=?pd.concat(df_list)
????df_org?=?pd.concat(df_list)
????df_form1['券面總額']?/=10000
????df_form1.rename({'券面總額':'券面總額（萬元）'},inplace=True,axis=1)
????df_form1['成交凈價']?=?np.round(df_form1['成交凈價'],1)
????df_form1.reset_index(drop=False,inplace=True)
????df_org.reset_index(drop=False,inplace=True)?
????wb.close()
????app.quit()
????return?(df_form1,df_org)

表2：表2左側(cè)的表格是原始數(shù)據(jù)，右側(cè)是買入和賣出信息，也是最終輸出的結(jié)果表之一，看下我封裝的代碼：

表1和表2都要注意，如果不做索引重置，那么成交編號這個列名就會成為我們的行索引，那么在最后的處理時，就會報錯，因此 使用方法來定義每個表格的功能能夠很好的將需求和實現(xiàn)完美的分離，隨時可以做極簡的修改。

#提取form2信息

def?df_form2(file_path):
????app?=?xw.App(visible=False,add_book=False)
????wb?=?app.books.open(file_path)
????df?=?wb.sheets[0].range('A2').expand('table').options(pd.DataFrame).value
????df_org?=?wb.sheets[0].range('A2').expand('table').options(pd.DataFrame).value
????df['成交凈價']?=?np.round(df['成交凈價'],1)
????df.reset_index(drop=False,inplace=True)
????df_org.reset_index(drop=False,inplace=True)
????wb.close()
????app.quit()
????return?(df,df_org)

表1和表2的代碼你會發(fā)現(xiàn)，我返回了一個元組，里面其實是替換數(shù)據(jù)的dataframe對象和沒有替換的dataframe對象，這樣我就可以不用寫兩次代碼完成我的需求了。

表3：

表4：

接下來是主要的業(yè)務代碼：

首先，我將表1和表2的買入方的機構(gòu)全名替換寫到了一個函數(shù)中去，這樣做的主要原因是它們的業(yè)務邏輯是相同的，沒必要寫兩遍代碼，因此也是函數(shù)封裝：

#?買入方對應表3,4機構(gòu)全名
#?buyer_ser
def?buyer_proc(buyer_ser,df_form3,df_form4):
????form1_borgs?=?list(buyer_ser)
????form3_orgs?=?list(df_form3['機構(gòu)簡稱'])
????form4_orgs?=?list(df_form4['子機構(gòu)'])
????for?i?in?range(len(form1_borgs)):
????????if?form1_borgs[i]?in?form3_orgs:
????????????ii?=?form3_orgs.index(form1_borgs[i])
????????????buyer_ser.loc[i]?=?df_form3.loc[ii,'機構(gòu)全稱']
????????elif?form1_borgs[i]?in?form4_orgs:
????????????ii?=?form4_orgs.index(form1_borgs[i])
????????????buyer_ser.loc[i]?=?df_form4.loc[ii,'主機構(gòu)']
????????else:
????????????continue
????return?buyer_ser

同理，賣出方如下：

#?賣出方對應表3機構(gòu)全名
#?seller_ser
def?seller_proc(seller_ser,df_form3,df_form4):
????form1_sorgs?=?list(seller_ser)
????form3_orgs?=?list(df_form3['機構(gòu)簡稱'])
????form4_orgs?=?list(df_form4['子機構(gòu)'])
????for?i?in?range(len(form1_sorgs)):
????????if?form1_sorgs[i]?in?form3_orgs:
????????????ii?=?form3_orgs.index(form1_sorgs[i])
????????????seller_ser.loc[i]?=?df_form3.loc[ii,'機構(gòu)全稱']
????????elif?form1_sorgs[i]?in?form4_orgs:
????????????ii?=?form4_orgs.index(form1_sorgs[i])
????????????seller_ser.loc[i]?=?df_form4.loc[ii,'主機構(gòu)']
????????else:
????????????continue
????return?seller_ser

然后第三個功能是做經(jīng)紀人的判斷，也就是在一開始我說的必須要表1的6個字段和表2的6個字段完全一致才能做這樣的判斷，注意是替換后的數(shù)據(jù)匹配。

這里我使用pandas中的merge函數(shù)進行匹配，給這個自定義函數(shù)傳入它們要進行匹配的字段列表即可實現(xiàn)

#?判斷是否是經(jīng)紀人成交
def?marker(list1,df1,df2):
????df?=?pd.merge(
????????left=df1,
????????right=df2,
????????left_on=list1,
????????right_on=list1
????)
????list_jd?=?[]
????for?i?in?df1['成交編號'].index:
????????if?df1.loc[i,'成交編號']?in?list(df.loc[:,'成交編號_x']):
????????????list_jd.append('是')
????????else:
????????????list_jd.append('')
????return?list_jd

然后生成總表3，也就是在結(jié)果集中總的明細數(shù)據(jù)集中的買入和賣出交易信息表

#?生成總表3
def?df3_gen(df1,df2):
????df?=?df1[['買入方','賣出方','券面總額（萬元）','成交凈價']]
????df['通過經(jīng)紀人成交']?=?marker(['買入方','賣出方','券面總額（萬元）','成交凈價'],
????????????????????????????????df1,
????????????????????????????????df2???
???????????????????????)
????df.rename({'買入方':'買入機構(gòu)全稱',
????????????'賣出方':'賣出機構(gòu)全稱',
????????????'券面總額':'券面總額（萬元）',
????????????'成交凈價':'凈價一位小數(shù)'
????????????},inplace=True,axis=1)
????return?df

代碼執(zhí)行流程：

1. 生成買入賣出機構(gòu)替換后的dataframe對象：

form1_path?=?Path(r'D:\Working\私活\財務\完整成交明細表.xlsx')
form2_path?=?Path(r'D:\Working\私活\財務\經(jīng)紀人成交明細表.xlsx')
df1?=?df_form1(form1_path)[0]
df2?=?df_form2(form2_path)[0]

#?查找表1對應的表3,4機構(gòu)名全稱
df_form3?=?pd.read_excel(r'D:\Working\私活\財務\機構(gòu)簡稱全稱對應表.xlsx')
df_form4?=?pd.read_excel(r'D:\Working\私活\財務\主從機構(gòu)對應表.xlsx')
#?改寫機構(gòu)名
df1['買入方']?=?buyer_proc(df1['買入方'],df_form3,df_form4)
df1['賣出方']?=?seller_proc(df1['賣出方'],df_form3,df_form4)
df2['買入方']?=?buyer_proc(df2['買入方'],df_form3,df_form4)
df2['賣出方']?=?seller_proc(df2['賣出方'],df_form3,df_form4)

生成總表3：

df3?=?df3_gen(df1,df2)

生成總表4：

df4?=?df3[df3['通過經(jīng)紀人成交']=='是'].groupby(['買入機構(gòu)全稱'])['券面總額（萬元）'].sum()

生成總表5：

df5?=??df3[df3['通過經(jīng)紀人成交']=='是'].groupby(['賣出機構(gòu)全稱'])['券面總額（萬元）'].sum()

生成總表6：

df6?=?df2[['買入方','賣出方','成交凈價']]
df6.rename({'買入方':'買入機構(gòu)全稱',
????????????'賣出方':'賣出機構(gòu)全稱',
????????????'成交凈價':'凈價一位小數(shù)'},
????????????inplace=True,
????????????axis=1)
df6.set_index('買入機構(gòu)全稱',inplace=True)

以上所有最后需要輸出的dataframe我全部生成好了，接下來需要在一個excel文檔中的不同部位將他們寫入，這個時候就需要用到xlwings的range區(qū)域?qū)ο髞韼椭?，看下代碼：

final_file1?=?Path(r'D:\Working\私活\財務\final_total.xlsx')
final_file2?=?Path(r'D:\Working\私活\財務\final_agent.xlsx')
app?=?xw.App(visible=False,add_book=False)
workbook1?=?app.books.add()
dff1?=?df_form1(form1_path)[1]
dff1.set_index('成交編號',inplace=True)
workbook1.sheets[0].range('A1').value?=?dff1
df3.set_index('買入機構(gòu)全稱',inplace=True)
workbook1.sheets[0].range('J1').value?=?df3
workbook1.sheets[0].range('P1').value?=?df4
workbook1.sheets[0].range('S1').value?=?df5
workbook1.save(final_file1)
workbook2?=?app.books.add()
dff2?=?df_form2(form2_path)[1]
dff2.set_index('成交編號',inplace=True)
workbook2.sheets[0].range('A1').value?=?dff2
workbook2.sheets[0].range('K1').value?=df6
workbook2.save(final_file2)
workbook1.close()
workbook2.close()
app.quit()

最終可以批量化，自動的生成所需要的數(shù)據(jù)，而且所有的功能都被封裝到了方法中，后續(xù)有變動不需要管數(shù)據(jù)是怎么樣的，只要去看功能是否有變化，如果數(shù)據(jù)變?yōu)楦嗟奈募枰幚恚敲粗恍枰獙憘€循環(huán)語句，直接拿來方法使用就可以了，是不是很方便？