ノック40：シート別にExcelデータを出力しよう
第2章の最後のノックです。
最初は北海道のデータをExcelファイルに出力します。
pivot_tableで整形します。
import pandas as pd
#エクセル1-2-2020.xlsxファイルの読み込み
xl=pd.ExcelFile('data/1-2-2020.xlsx')
sheets = xl.sheet_names
col_data = col_data.head(3)
cols=
#欠損値をdropnaで除去してjoinで連結した値をappendで新しい項目名変更
for jjj in col_data.columns:
tg_col = '_'.join(list(col_data[jjj].dropna()))
cols.append(tg_col)
#1行目の欠損値は0行目の項目名で補完
col_data .iloc[1,1:].fillna(col_data.iloc[0,1:],inplace=True)
#1行目の発電所を削除
#列方向に対して欠損部分には左の値を代入
for iii in col_data.columns:
if iii < col_data.columns.max():
col_data[iii+1].fillna(col_data[iii],inplace=True)
#カッコの削除
col_data.replace('〔廃棄物〕','廃棄物',inplace=True)
cols=
for jjj in col_data.columns:
tg_col = '_'.join(list(col_data[jjj].dropna()))
cols.append(tg_col)
#print(cols)
#年月項目を新たに列として追加し、値はシート名を代入
datas=
for sheet in sheets:
data = xl.parse(sheets[0],skiprows=4,header=None)
data.columns=cols
data['年月']=sheet
datas.append(data)
#シートごとのデータをconcatで統合
datas=pd.concat(datas,ignore_index=True)
#ノック24 データ値の修正
#print(datas.head(1))
datas.drop(['合計_合計_発電所数','合計_合計_最大出力計','新エネルギー等発電所_計_発電所数','新エネルギー等発電所_計_最大出力計'],axis=1,inplace=True)
#print(datas.head())
#print(datas_v.head())
var_data=datas_v['変数名'].str.split('_',expand=True)
datas_v=pd.concat([datas_v,var_data],axis=1)
#####################################################
#エクセル2-2-2020.xlsxファイルの読み込み
col_ca_data=col_ca_data.head(3)
#fillnaで欠損値の穴埋め。ilocで1行目の全ての列に対して、実行。穴埋め地は0行目の同列値。
col_ca_data.iloc[1,1:].fillna(col_ca_data.iloc[0,1:],inplace=True)
#ilocで1行目の各値を抽出し、str.replaceで[発電所]という文字列を空白に置き換え（削除）
#列のデータに対して欠損値があれば左の値で補完する
for iii in col_ca_data.columns:
if iii < col_ca_data.columns.max():
col_ca_data[iii+1].fillna(col_ca_data[iii],inplace=True)
#[バイオマス]、[廃棄物]の両カッコを削除
col_ca_data.replace('〔廃棄物〕','廃棄物',inplace=True)
#dropnaで欠損値を削除し、各行の値をアンダーバーで接続
cols_ca=
for jjj in col_ca_data.columns:
tg_col = '_'.join(list(col_ca_data[jjj].dropna()))
cols_ca.append(tg_col)
xl_ca =pd.ExcelFile('data/2-2-2020.xlsx')
sheets = xl_ca.sheet_names
ca_datas=
for sheet in sheets:
capacity_data = xl_ca.parse(sheet,skiprows=4,header=None)
capacity_data = capacity_data.head(47)
capacity_data.columns= cols_ca
capacity_data['年月']=sheet
ca_datas.append(capacity_data)
ca_datas=pd.concat(ca_datas,ignore_index=True)
#データ値の修正
var_data=ca_datas_v['変数名'].str.split('_',expand=True)
ca_datas_v = pd.concat([ca_datas_v,var_data],axis=1)
#ノック29 発電所数のデータdatas_vと発電実績のデータca_dats_vを結合
datas_v_all=pd.concat([datas_v,ca_datas_v],ignore_index=True)
#ノック40
target = '北海道'
tmp=tmp.pivot_table(values='値',columns=['発電種別','項目'],index='年月',aggfunc='sum')
print(tmp.head())
実行結果
pivot_tableの引数は以下の通りです。
values：表示したいデータ
columns：列の見出し
index：行の見出し
aggfunc：sumは合計値を算出
index='年月’なので、最初の列に年月が表示され、年月ごとの値が表示されます。
列はcolumns=['発電種別','項目'] なので発電種別の列データと項目列のデータが表示されます。
target変数を都道府県に変更し、都道府県ごとにシートを作成し、上記データをエクセルに出力します。
writer = pd.ExcelWriter('data/detail_data.xlsx',mode='w')
tmp=tmp.pivot_table(values='値',columns=['発電種別','項目'],index=['年月'],aggfunc='sum')
with writer as w:
実行結果
最初の北海道だけシートが作成されるだけで終わりました。。
ログには以下のメッセージが表示されました。
UserWarning: Calling close() on already closed file.
warn("Calling close() on already closed file.")
ExcelWriterの引数にengine='openpyxl'を追加して再実行すると、成功しました。
import openpyxl
writer = pd.ExcelWriter('data/detail_data.xlsx',engine='openpyxl',mode='w')
tmp=tmp.pivot_table(values='値',columns=['発電種別','項目'],index=['年月'],aggfunc='sum')
with writer as w:
python:3.8.5
pandas:1.1.5
openpyxl:3.0.5
バージョン確認方法はpd.show_versions()を実行すれば表示されます。
pd.show_versions()
