Python：計算結果をエクセル出力し加工する（月平均値の算出とエクセル化）
能書き
報告書を作成しているとき、Pythonで計算した結果をWordに表形式で貼り付けたい場合がある。 これが、TeXやhtmlであれば、プロブラム内でタグを埋め込んだ形で標準出力し、コピー＆ペーストでTeXもしくはhtml文書に貼り付けるのだが、相手がWordとなるとなかなか面倒である。PythonからWordを制御することができることも知っている（使ったことはない）が、そのためのみのコードを書くのも大変だし、後処理計算での活用などを考えるとエクセル出力しておき、それをwordに貼り付けるほうが、実用的である。
私がよく使う数表をwordに貼り付ける方法は以下の３つである。
(1) 計算結果をコンマ区切りで標準出力し、Wordにコピー＆ペースト。その後はWordで[Insert]=>[table]=>[Convert Text to Table]=>[Separate test at Commas]=.[ok]の手順で、Word上で表に加工する方法。Word上で加工できるので仕上がりは結構綺麗にいく。
(2) 計算結果をエクセルに出力しフォーマットを整える。その後はエクセル上で範囲指定しクリップボードコピー、Word上で[Paste Special]=>[As Picture(PNG)]=>[ok]の手順で、画像としてWordに貼り付ける方法。画像としてwordに貼り付けるためWord上で加工できず、結果がぼやける場合もあり、最近は使わない。
(3)計算結果の表をhtml出力してブラウザで表示し、それをコピー＆ペーストでwordに貼り付け、仕上げはword上で行う。ブラウザはsafariで行うのがやりやすいようである、
方法(3)は最近使い出したのだが、面倒なようでなかなか良い。 というのも計算結果は一回出力するのみでなく、次の処理で使う場合も多く、そうなると１ファイルに複数シートを配置できるエクセルで保存しておくのも後工程での使い勝手がよい。
エクセルからhtmlへの変換
エクセルからhtmlへの出力は、ネット上で便利なものが有り、以下のものを使わせてもらっている。
エクセルから変換したhtmlの表を貼り付けたものが下の表。二重線が消えているがhtml出力が最終成果ではないので気にしない。 必要あれば装飾をかければ良い。
| Year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Ave |
| 2001 | 19.6 | 17.8 | 16.1 | 14.5 | 21.3 | 36.1 | 35.9 | 84.0 | 36.0 | 47.7 | 39.4 | 26.7 | 33.1 |
| 2002 | 22.1 | 20.4 | 18.5 | 16.7 | 24.1 | 19.9 | 43.1 | 36.7 | 44.8 | 32.9 | 29.6 | 23.8 | 27.8 |
| 2003 | 21.4 | 19.7 | 18.0 | 16.2 | 14.6 | 13.9 | 20.6 | 22.9 | 30.7 | 29.9 | 28.1 | 21.5 | 21.5 |
| 2004 | 19.2 | 17.6 | 15.9 | 14.3 | 21.1 | 37.7 | 47.0 | 48.7 | 110.6 | 47.1 | 32.0 | 24.2 | 36.2 |
| 2005 | 22.0 | 20.0 | 18.1 | 16.3 | 14.9 | 15.0 | 44.5 | 49.7 | 92.6 | 45.4 | 32.2 | 25.6 | 33.0 |
| 2006 | 22.3 | 20.5 | 18.6 | 16.7 | 15.2 | 16.1 | 32.7 | 65.6 | 104.4 | 62.3 | 34.7 | 25.6 | 36.3 |
| 2007 | 22.8 | 20.8 | 18.8 | 16.9 | 17.7 | 20.3 | 24.5 | 35.7 | 29.5 | 33.4 | 26.8 | 21.8 | 24.1 |
| 2008 | 20.0 | 18.3 | 16.6 | 15.0 | 13.7 | 25.0 | 56.6 | 83.9 | 74.4 | 41.0 | 36.4 | 28.9 | 35.9 |
| 2009 | 24.1 | 22.2 | 20.1 | 18.1 | 16.4 | 15.3 | 18.0 | 37.2 | 31.9 | 29.1 | 25.4 | 20.2 | 23.2 |
| 2010 | 18.6 | 17.0 | 15.3 | 13.8 | 12.4 | 11.8 | 34.5 | 114.9 | 89.1 | 45.6 | 32.9 | 24.4 | 36.0 |
| 2011 | 21.8 | 19.9 | 18.0 | 16.4 | 17.9 | 28.9 | 42.7 | 129.4 | 111.2 | 55.2 | 36.3 | 27.8 | 43.9 |
| 2012 | 24.9 | 22.7 | 20.4 | 18.4 | 17.8 | 25.9 | 41.6 | 60.0 | 72.2 | 42.4 | 33.8 | 26.5 | 33.9 |
| 2013 | 23.6 | 21.6 | 19.6 | 17.6 | 15.9 | 16.2 | 28.0 | 103.1 | 82.5 | 52.8 | 38.4 | 28.2 | 37.4 |
| 2014 | 23.7 | 21.8 | 19.8 | 17.8 | 16.0 | 18.3 | 57.8 | 56.8 | 58.2 | 34.4 | 28.8 | 23.1 | 31.5 |
| 2015 | 21.2 | 19.3 | 17.5 | 15.7 | 14.6 | 18.5 | 20.7 | 43.4 | 29.2 | 28.2 | 24.8 | 20.3 | 22.8 |
| 2016 | 18.8 | 17.1 | 15.5 | 14.0 | 12.6 | 13.6 | 39.1 | 48.7 | 58.3 | 35.8 | 29.9 | 23.9 | 27.3 |
| 2017 | 21.0 | 19.5 | 17.7 | 16.0 | 14.5 | 14.5 | 29.3 | 36.6 | 51.0 | 33.2 | 29.9 | 23.8 | 25.6 |
| 2018 | 21.1 | 19.6 | 17.8 | 16.1 | 14.8 | 33.8 | 40.6 | 58.9 | 41.9 | 64.7 | 35.2 | 26.6 | 32.7 |
| 2019 | 23.2 | 21.3 | 19.4 | 17.4 | 15.6 | ||||||||
| 2020 | |||||||||||||
| Ave | 21.7 | 19.8 | 18.0 | 16.2 | 16.4 | 21.2 | 36.5 | 62.0 | 63.8 | 42.3 | 31.9 | 24.6 | 31.2 |
| RGS1 (CA=3062km2) tank model monthly and annual discharge, unit: m3/s | |||||||||||||
プログラム上の処理
Pythonプログラム上での出力したい表の処理は以下の手順で行う。
- １．pandasのデータフレーム作成
- ２．一度データフレームをそのままエクセルに保存
- ３．保存したエクセルファイルを再度pythonで呼び出し、エクセうの書式指定、罫線などの可能を行い、再保存する。
データフレーム作成
df = pd.DataFrame({ 'Year' : lyy,
'Jan' : qqm[0:n,0],
'Feb' : qqm[0:n,1],
'Mar' : qqm[0:n,2],
'Apr' : qqm[0:n,3],
'May' : qqm[0:n,4],
'Jun' : qqm[0:n,5],
'Jul' : qqm[0:n,6],
'Aug' : qqm[0:n,7],
'Sep' : qqm[0:n,8],
'Oct' : qqm[0:n,9],
'Nov' : qqm[0:n,10],
'Dec' : qqm[0:n,11],
'Ave' : qqm[0:n,12]
})
df = df.set_index('Year')
データフレーム保存
関連する内容を、１ファイルに、複数シートに分割して保存する。
fnameW='out_dis_month.xlsx' with pd.ExcelWriter(fnameW) as writer: df1_tank.to_excel(writer, sheet_name='RGS1_tank') df2_tank.to_excel(writer, sheet_name='RGS2_tank') df1_mlp.to_excel(writer, sheet_name='RGS1_mlp') df2_mlp.to_excel(writer, sheet_name='RGS2_mlp')
エクセルファイルの再読み込みと保存
この間に処理を記載する。
wb =openpyxl.load_workbook(fnameW)
#
....
#
wb.save(fnameW)
フォーマット指定
# format for i in range(2, n+1): for j in range(2, m+1): ws.cell(row=i, column=j).number_format = '0.0'
罫線（前半は１重線、後半は２重線を指定）
from openpyxl.styles.borders import Border, Side # border line bcc = Border(top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'), left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000') ) for i in range(1, n+1): for j in range(1, m+1): ws.cell(row=i, column=j).border = bcc bcc = Border(top=Side(style='double', color='000000'), bottom=Side(style='double', color='000000'), left=Side(style='double', color='000000'), right=Side(style='double', color='000000') ) for j in range(1, m+1): ws.cell(row=n, column=j).border = bcc for i in range(1, n+1): ws.cell(row=i, column=m).border = bcc
セルの結合（長い文字列を入力しセルを結合する）
ws.cell(row=n+1,column=1).value='RGS2 (CA=7860km2) tank model monthly and annual discharge, unit: m3/s' ss='A{0}:N{1}'.format(n+1,n+1); ws.merge_cells(ss)
セルの塗りつぶし
このプログラムには含まれていないが、塗りつぶしを行う場合は以下の要領で行う。
from openpyxl.styles import PatternFill # cell color fill = PatternFill(patternType='solid', fgColor='00ffff') for i in [6,11,16,21,26,31]: for j in range(3,17): ws.cell(row=i,column=j).fill = fill
主要計算部分の説明
例外処理を使っているので、その部分を解説。 このプログラムでは、１日でも欠測があれば、その月および年の平均は欠測としている。
mainで数表にする年と月を、リスト lyy と lmm で定義している。
lyy=['2001','2002','2003','2004','2005','2006','2007','2008','2009','2010', '2011','2012','2013','2014','2015','2016','2017','2018','2019','2020'] lmm=['01','02','03','04','05','06','07','08','09','10','11','12']
以下の関数で各月および各年の平均値を算出している。 引数の意味は以下の通り。
rf: 日データを示すseries.lyyy: リストlyyと同じlmmm: リストlmmと同じ。
def cal_m(rf,lyyy,lmmm): qqm=np.zeros((len(lyyy)+1,len(lmmm)+1),dtype=np.float64) kda=np.zeros((len(lyyy)+1,len(lmmm)+1),dtype=np.float64) nac=0 for i,yy in enumerate(lyyy): for j,mm in enumerate(lmmm): ss=yy+'/'+mm # 処理する年/月を指定 try: # Series: rf[ss]に対する処理（合計およびデータ数カウント）を行う qqm[i,j]=rf[ss].sum() # sum in a month kda[i,j]=rf[ss].count() # availavle days nac=np.count_nonzero(np.isnan(rf[ss])) # unavailable days except KeyError: # もし[ss]に合致する年/月のデータがなければ配列にnp.nanを代入 qqm[i,j]=np.nan kda[i,j]=np.nan if 0<nac: # 仮に指定した年/月のデータがあってもnp.nanがあれば、すなわち月の全日のデータが揃っていなければnp.nanを代入 qqm[i,j]=np.nan kda[i,j]=np.nan qqm[i,-1]=np.sum(qqm[i,:]) # sum in a year 年間合計値 kda[i,-1]=np.sum(kda[i,:]) # sum in a year of available days 年間合計値 for j in range(len(lmmm)+1): qqm[-1,j]=np.nansum(qqm[:,j]) # nanを除外したデータの合計（各月合計値） kda[-1,j]=np.nansum(kda[:,j]) # nanを除外したデータ数の合計（各月合計値） qmean=qqm/kda # 月および年のデータ合計をデータ数で除すことにより平均値を算出 return qmean
プログラム例（全文）
プログラムは、ファイルから値を読み取り、上に示した表を作るもの。 実際のプログラムの出力はエクセル。 以下にPythonによるプログラム全文を示す。
import pandas as pd import numpy as np import datetime import openpyxl from openpyxl.styles.borders import Border, Side def formx(ws,n,m): # format for i in range(2, n+1): for j in range(2, m+1): ws.cell(row=i, column=j).number_format = '0.0' # border line bcc = Border(top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'), left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000') ) for i in range(1, n+1): for j in range(1, m+1): ws.cell(row=i, column=j).border = bcc bcc = Border(top=Side(style='double', color='000000'), bottom=Side(style='double', color='000000'), left=Side(style='double', color='000000'), right=Side(style='double', color='000000') ) for j in range(1, m+1): ws.cell(row=n, column=j).border = bcc for i in range(1, n+1): ws.cell(row=i, column=m).border = bcc def wexcel(df1_tank,df2_tank,df1_mlp,df2_mlp): fnameW='out_dis_month.xlsx' with pd.ExcelWriter(fnameW) as writer: df1_tank.to_excel(writer, sheet_name='RGS1_tank') df2_tank.to_excel(writer, sheet_name='RGS2_tank') df1_mlp.to_excel(writer, sheet_name='RGS1_mlp') df2_mlp.to_excel(writer, sheet_name='RGS2_mlp') n=len(df1_tank.index)+1 m=len(df1_tank.columns)+1 wb =openpyxl.load_workbook(fnameW) # ws = wb.get_sheet_by_name('RGS1_tank') formx(ws,n,m) ws.cell(row=n+1,column=1).value='RGS1 (CA=3062km2) tank model monthly and annual discharge, unit: m3/s' ss='A{0}:N{1}'.format(n+1,n+1); ws.merge_cells(ss) # ws = wb.get_sheet_by_name('RGS2_tank') formx(ws,n,m) ws.cell(row=n+1,column=1).value='RGS2 (CA=7860km2) tank model monthly and annual discharge, unit: m3/s' ss='A{0}:N{1}'.format(n+1,n+1); ws.merge_cells(ss) # ws = wb.get_sheet_by_name('RGS1_mlp') formx(ws,n,m) ws.cell(row=n+1,column=1).value='RGS1 (CA=3062km2) MLP monthly and annual discharge, unit: m3/s' ss='A{0}:N{1}'.format(n+1,n+1); ws.merge_cells(ss) # ws = wb.get_sheet_by_name('RGS2_mlp') formx(ws,n,m) ws.cell(row=n+1,column=1).value='RGS2 (CA=7860km2) MLP monthly and annual discharge, unit: m3/s' ss='A{0}:N{1}'.format(n+1,n+1); ws.merge_cells(ss) # wb.save(fnameW) def rdata(fnameR): df=pd.read_csv(fnameR, header=0, index_col=0) # read excel data df.index = pd.to_datetime(df.index, format='%Y/%m/%d') return df def cal_m(rf,lyyy,lmmm): qqm=np.zeros((len(lyyy)+1,len(lmmm)+1),dtype=np.float64) kda=np.zeros((len(lyyy)+1,len(lmmm)+1),dtype=np.float64) nac=0 for i,yy in enumerate(lyyy): for j,mm in enumerate(lmmm): ss=yy+'/'+mm try: qqm[i,j]=rf[ss].sum() # sum in a month kda[i,j]=rf[ss].count() # availavle days nac=np.count_nonzero(np.isnan(rf[ss])) # unavailable days except KeyError: qqm[i,j]=np.nan kda[i,j]=np.nan if 0<nac: qqm[i,j]=np.nan kda[i,j]=np.nan qqm[i,-1]=np.sum(qqm[i,:]) # sum in a year kda[i,-1]=np.sum(kda[i,:]) # sum in a year of available days for j in range(len(lmmm)+1): qqm[-1,j]=np.nansum(qqm[:,j]) kda[-1,j]=np.nansum(kda[:,j]) qmean=qqm/kda return qmean def qq_mon(lyy,lmm,qq): qqm=cal_m(qq,lyy,lmm) lyy=lyy+['Ave'] n=len(lyy) df = pd.DataFrame({ 'Year' : lyy, 'Jan' : qqm[0:n,0], 'Feb' : qqm[0:n,1], 'Mar' : qqm[0:n,2], 'Apr' : qqm[0:n,3], 'May' : qqm[0:n,4], 'Jun' : qqm[0:n,5], 'Jul' : qqm[0:n,6], 'Aug' : qqm[0:n,7], 'Sep' : qqm[0:n,8], 'Oct' : qqm[0:n,9], 'Nov' : qqm[0:n,10], 'Dec' : qqm[0:n,11], 'Ave' : qqm[0:n,12] }) df = df.set_index('Year') return df def main(): lyy=['2001','2002','2003','2004','2005','2006','2007','2008','2009','2010', '2011','2012','2013','2014','2015','2016','2017','2018','2019','2020'] lmm=['01','02','03','04','05','06','07','08','09','10','11','12'] fname_list=[ 'df_rgs1_tank_result.csv', 'df_rgs2_tank_result.csv', 'df_rgs1_mlp_result.csv', 'df_rgs2_mlp_result.csv' ] for iii,fnameR in enumerate(fname_list): df0=rdata(fnameR) # daily discharge df0=df0['2001/01/01':'2019/05/31'] qq=pd.Series(df0['Q_pred'], index=df0.index) df=qq_mon(lyy,lmm,qq) print(fnameR) if iii==0: df1_tank=df if iii==1: df2_tank=df if iii==2: df1_mlp=df if iii==3: df2_mlp=df wexcel(df1_tank,df2_tank,df1_mlp,df2_mlp) #============== # Execution #============== if __name__ == '__main__': main()
以 上
