Python實(shí)戰(zhàn)：實(shí)現(xiàn)Excel多個(gè)工作簿合并及多功能數(shù)據(jù)透視

Python實(shí)現(xiàn)多個(gè)工作簿合并及多功能數(shù)據(jù)透視

大家好，這次項(xiàng)目給大家?guī)淼氖荘ython實(shí)現(xiàn)多個(gè)Excel工作簿合并，并對(duì)合并后的結(jié)果集進(jìn)行數(shù)據(jù)透視的示例。對(duì)于數(shù)據(jù)透視，Excel當(dāng)中已經(jīng)有了比較強(qiáng)大的透視功能，但是缺點(diǎn)是靈活性不足，就是對(duì)于時(shí)間頻率、周期以及計(jì)算方式都不夠靈活，因此Python正好彌補(bǔ)了這塊不足。

首先看下幾天的數(shù)據(jù)集的樣子：

這個(gè)是要合并的所有的工作簿

這是每個(gè)工作簿的樣子，每個(gè)文件的內(nèi)部樣式都要保持一致能才能進(jìn)行完整的合并。

接下來簡單談?wù)勎业墓δ軐?shí)現(xiàn)：

1. 功能模塊介紹：

使用Path模塊：

• 讀取指定文件夾下的所有excel文件

使用Pandas

• read_excel()讀取所有工作簿
• concat()合并多有數(shù)據(jù)
• pivot_table() 進(jìn)行簡單數(shù)據(jù)透視
• resample()日期重采樣進(jìn)行不同時(shí)段的數(shù)據(jù)匯總
• rolling() 探查指定固定時(shí)間段數(shù)據(jù)的變化程度
• to_excel()匯總數(shù)據(jù)到新的工作簿

2. 擴(kuò)展：時(shí)間選擇

• 按照固定頻率：
• 每幾天/周/月：例如：‘7D’
• 時(shí)間頻率設(shè)置參數(shù)如下：

代碼：

導(dǎo)入擴(kuò)展包

import?pandas?as?pd
from?pathlib?import?Path

數(shù)據(jù)整合

#?數(shù)據(jù)整合：
def?data_concat():
????#?提示用戶輸入文件夾路徑：
????folder_path?=?Path(input('請(qǐng)輸入要處理文件的文件夾路徑：'))
????#?提取所有文件列表：
????file_list?=?folder_path.glob('*merge.xlsx')
????#?整合所有列表
????df_list?=?[]
????for?i?in?file_list:
????????if?not?i.name.startswith('~$'):
????????????df?=?pd.read_excel(i)
????????????df_list.append(df)
????dfs?=?pd.concat(df_list,axis=0)?#?axis=0?代表上下合并，axis=1代表左右合并
????#?提示用戶需要提取哪列或者哪幾列數(shù)據(jù),name和date是必須添加的數(shù)據(jù)，因此也要進(jìn)行整合
????col_list?=?input('請(qǐng)輸入要提取的列名，多個(gè)列名用空格間隔：').split('?')
????if?('name'?in?col_list)&('date'?in?col_list):
????????pass
????elif?'name'?in?col_list:
????????col_list.append('date')
????elif?'date'?in?col_list:
????????col_list.append(['name'])
????else:
????????col_list.extend(['name','date'])
????df?=?dfs[col_list]
????df.set_index('name',inplace=True)
????#?輸出數(shù)據(jù)：
????return?df

實(shí)現(xiàn)簡單數(shù)據(jù)透視

我使用了pivot_table()函數(shù)來生成以股票名為行索引，日期為列索引，不同計(jì)算匯總添加新列的方式生成透視結(jié)果。我將用戶指定的多個(gè)計(jì)算添加到列表中，然后依次執(zhí)行，并生成新的列添加到透視結(jié)果中，這樣就能很簡單快速的生成透視結(jié)果了

#?實(shí)現(xiàn)簡單數(shù)據(jù)透視
def?pivot_data(df):
????#?提示用戶需要進(jìn)行匯總的方法：
????method_list?=?input('請(qǐng)輸入要匯總的方法：求和,非重復(fù)計(jì)數(shù),平均值,最大值,最小值,方差,標(biāo)準(zhǔn)差,多個(gè)方法以空格間隔：').split('?')
????table?=?pd.pivot_table(df,index='name',columns='date',fill_value=0)
????for?i?in?range(len(method_list)):
????????if?method_list[i]?==?'求和':
????????????table[method_list[i]]?=?table.sum(1)
????????elif?method_list[i]?==?'平均值':
????????????table[method_list[i]]?=?table.mean(1)
????????????print(table[method_list[i]])
????????elif?method_list[i]?==?'最大值':
????????????table[method_list[i]]?=?table.max(1)
????????elif?method_list[i]?==?'最小值':
????????????table[method_list[i]]?=?table.min(1)
????????elif?method_list[i]?==?'去重計(jì)數(shù)':
????????????table[method_list[i]]?=?table.unique(1)
????????elif?method_list[i]?==?'標(biāo)準(zhǔn)差':
????????????table[method_list[i]]?=?table.std(1)
????????elif?method_list[i]?==?'方差':
????????????table[method_list[i]]?=?table.var(1)
????????else:
????????????print('方法不存在')
????table.to_excel('res.xlsx')

生成的數(shù)據(jù)集是這樣的：

生成具有時(shí)間頻率透視功能的表格

在實(shí)際工作中，我們經(jīng)常需要對(duì)一定時(shí)間頻率的數(shù)據(jù)做統(tǒng)計(jì)，這里我使用了Python中時(shí)間序列相關(guān)的內(nèi)容，對(duì)股票數(shù)據(jù)，進(jìn)行不同時(shí)間頻率的數(shù)據(jù)透視，可以得到以固定時(shí)間頻率變化的匯總結(jié)果。

#?具有時(shí)間頻率透視功能的表格
def?freq_pivot(df):
????#?對(duì)數(shù)據(jù)索引重置，使時(shí)間作為行索引
????date_format=?'''
????????可供選擇日期頻率：
????????每幾個(gè)日歷日：nD，例如'7D'
????????每幾個(gè)工作日：nB, 例如'7B'
????????每月最后一個(gè)日歷日：nM，例如'3M'
????????每月最后一個(gè)工作日：nBM，例如'3BM'
????????指定每周星期幾算起：W-Mon
????????等等
????'''
????date_freq?=?input('請(qǐng)輸入你想要的時(shí)間頻率(輸入時(shí)間需要小于現(xiàn)有數(shù)據(jù)時(shí)間跨度)：')
????df['date']?=?pd.to_datetime(df['date'])
????df2?=?df.reset_index().set_index('date')
????method_list?=?input('請(qǐng)輸入要匯總的方法：求和,平均值,最大值,最小值,方差,標(biāo)準(zhǔn)差,多個(gè)方法以空格間隔：').split('?')
????for?i?in?range(len(method_list)):
?????????if?method_list[i]?==?'求和':
????????????df3?=?df2.groupby('name').resample(date_freq).sum()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????elif?method_list[i]?==?'平均值':
????????????df3?=?df2.groupby('name').resample(date_freq).mean()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')?????????
?????????elif?method_list[i]?==?'最大值':
????????????df3?=?df2.groupby('name').resample(date_freq).max()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')?
?????????elif?method_list[i]?==?'最小值':
????????????df3?=?df2.groupby('name').resample(date_freq).min()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????elif?method_list[i]?==?'標(biāo)準(zhǔn)差':
????????????df3?=?df2.groupby('name').resample(date_freq).std()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????elif?method_list[i]?==?'方差':
????????????df3?=?df2.groupby('name').resample(date_freq).var()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????else:
????????????print('方法不存在')
def?freq_pivot(df):
????#?對(duì)數(shù)據(jù)索引重置，使時(shí)間作為行索引
????date_format=?'''
????????可供選擇日期頻率：
????????每幾個(gè)日歷日：nD，例如'7D'
????????每幾個(gè)工作日：nB, 例如'7B'
????????每月最后一個(gè)日歷日：nM，例如'3M'
????????每月最后一個(gè)工作日：nBM，例如'3BM'
????????指定每周星期幾算起：W-Mon
????????等等
????'''
????date_freq?=?input('請(qǐng)輸入你想要的時(shí)間頻率(輸入時(shí)間需要小于現(xiàn)有數(shù)據(jù)時(shí)間跨度)：')
????df['date']?=?pd.to_datetime(df['date'])
????df2?=?df.reset_index().set_index('date')
????method_list?=?input('請(qǐng)輸入要匯總的方法：求和,平均值,最大值,最小值,方差,標(biāo)準(zhǔn)差,多個(gè)方法以空格間隔：').split('?')
????for?i?in?range(len(method_list)):
?????????if?method_list[i]?==?'求和':
????????????df3?=?df2.groupby('name').resample(date_freq).sum()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????elif?method_list[i]?==?'平均值':
????????????df3?=?df2.groupby('name').resample(date_freq).mean()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')?????????
?????????elif?method_list[i]?==?'最大值':
????????????df3?=?df2.groupby('name').resample(date_freq).max()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')?
?????????elif?method_list[i]?==?'最小值':
????????????df3?=?df2.groupby('name').resample(date_freq).min()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????elif?method_list[i]?==?'標(biāo)準(zhǔn)差':
????????????df3?=?df2.groupby('name').resample(date_freq).std()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????elif?method_list[i]?==?'方差':
????????????df3?=?df2.groupby('name').resample(date_freq).var()
????????????df3.to_excel(f'時(shí)間頻率為{date_freq}{method_list[i]}透視.xlsx')
?????????else:
????????????print('方法不存在')

生成的數(shù)據(jù)集是這樣的：

生成具有時(shí)間周期的透視表格

有時(shí)候我們想要查看每連續(xù)一定時(shí)間周期數(shù)據(jù)的變化規(guī)律是怎么樣的，這時(shí)候就需要生成具有時(shí)間周期的透視表格，在該方法中，我使用了unstack方法將行索引設(shè)定為時(shí)間，股票名設(shè)置為列索引，最終實(shí)現(xiàn)了股票在一定時(shí)間周期下的連續(xù)變化的數(shù)據(jù)

#?時(shí)間周期透視：
def?period_pivot(df):
????date_format=?'''
????????可供選擇日期周期：
????????查看每連續(xù)幾日的數(shù)據(jù)變化(如每連續(xù)10天)，請(qǐng)輸入：10
????'''
????print(date_format)
????df['date']?=?pd.to_datetime(df['date'])
????df2?=?df.reset_index().set_index(['date','name'])
????p?=?int(input('本功能為觀測(cè)連續(xù)天數(shù)的某個(gè)數(shù)學(xué)指標(biāo)的變化情況，請(qǐng)輸入你想要的時(shí)間周期（不要超過數(shù)據(jù)時(shí)間跨度）：'))
????method_list?=?input('請(qǐng)輸入要匯總的方法：求和,平均值,最大值,最小值,方差,標(biāo)準(zhǔn)差,多個(gè)方法以空格間隔：').split('?')
????for?i?in?range(len(method_list)):
????????if?method_list[i]?==?'求和':
????????????df3?=?df2.unstack(level=1).rolling(window=p).sum()
????????????df3.to_excel(f'時(shí)間周期為{p}{method_list[i]}透視.xlsx')
????????elif?method_list[i]?==?'平均值':
????????????df3?=?df2.unstack(level=1).rolling(window=p).mean()
????????????df3.to_excel(f'時(shí)間周期為{p}{method_list[i]}透視.xlsx')
????????elif?method_list[i]?==?'最大值':
????????????df3?=?df2.unstack(level=1).rolling(window=10).max()
????????????df3.to_excel(f'時(shí)間周期為{p}{method_list[i]}透視.xlsx')
????????elif?method_list[i]?==?'最小值':
????????????df3?=?df2.unstack(level=1).rolling(window=p).min()
????????????df3.to_excel(f'時(shí)間周期為{p}{method_list[i]}透視.xlsx')
????????elif?method_list[i]?==?'標(biāo)準(zhǔn)差':
????????????df3?=?df2.unstack(level=1).rolling(window=p).std()
????????????df3.to_excel(f'時(shí)間周期為{p}{method_list[i]}透視.xlsx')
????????elif?method_list[i]?==?'方差':
????????????df3?=?df2.unstack(level=1).rolling(window=p).var()
????????????df3.to_excel(f'時(shí)間周期為{p}{method_list[i]}透視.xlsx')
????????else:
????????????print('方法不存在')

生成的結(jié)果集是這樣的：