こんな人にオススメ
pandasを使用してcsvファイルを扱うのはいけるけど、Excelの場合はどうするの？
シートが複数枚ある場合はちゃんと読み込めるの？
ということで、今回はpythonのpandasを使用してExcelファイルを読み込み・書き出す方法を解説する。csvファイルについては以下参照。
-
-
【python&csv読み込み】pythonを使ってcsvを読み込み
続きを見る
csvファイルだと1シートしか存在しないが、Excelになるとシートが複数枚になることも。そんな時はちゃんと読めるかというと読める。
シート指定もできるし、全シートの読み込みもできる。今回は基礎的な方法を解説していく。
作成したコード全文
下準備
import numpy as np import pandas as pd import openpyxl import pandas.io.formats.excel # 「.xlsx」形式を使用するにはopenpyxlのインストールが必要（pandasの内部で使用） # importはしなくてもいいがインストールしておかないと以下のエラー # ImportError: Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl. # 「.xls」形式を使用するにはxlrdのインストールが必要 # importはしないがインストールしておかないと以下のエラー # ImportError: Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.
まずは下準備としてのimport関連。openpyxlは複数シートを扱うときに使用する。基本はpandasだけimportしておけば大抵のことはこなせる。
なお、pandasだけimportしておけばいいんだけど、予め「openpyxl」と「xlrd」のインストールは必要。pandasの内部で使用するから。
anacondaを使用してライブラリを入れている人は以下のコードでいけるはず（自分のバージョンとかそこら辺は要チェック）。
conda install -c anaconda openpyxl conda install -c anaconda xlrd
pandas1.2以降ではopenpyxlは「.xlsx」形式、xlrdは「.xls」形式のExcelファイルを扱うときに必要になる。それ以前ではどちらの形式もxlrdでいける。
使用するExcelファイル
# 使用するExcelのファイル名 file = './books.xlsx'
また、使用するExcelファイルの名称は「books.xlsx」。このファイルは「Sheet1」「name」「#3」という謎のシート3枚で構成されている。中身は以下。
Sheet1
| A | B | C |
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
name
| a | b | c | d |
| 7 | 1 | 2 | 4 |
| 7 | 0 | 6 | 9 |
| 0 | 9 | 1 | 10 |
| 3 | 9 | 4 | 7 |
| 10 | 2 | 9 | 2 |
#3
| 19 | 12 | 16 |
| 17 | 11 | 10 |
このファイルを基準にpandasでのExcelファイルの使い方を解説する。
ファイル読み込み&データフレーム作成&出力の関数
def read(**kwargs):
# データの読み込み
# **kwargsで引数を後から追加できるように
df = pd.read_excel(file, **kwargs)
print(df)
return df
まずはExcelファイルを読み込んだ後に出力するだけのシンプルな関数を定義する。pandasでExcelファイルを読み込むときはpd.read_excelで可能。
**kwargsは可変長キーワード引数で、このread関数を使用するときにpd.read_excelの引数を後から追加できるようにするために使用。詳しくは以下。
あとは読み込んだデータフレームをprintで確認しつつ、returnもしておくことで、関数外での操作にも対応させておいた。
シンプルに出力すると初めのシートのみ
# シンプルに出力すると初めのシートのみ出力 read() # A B C # 0 1 2 3 # 1 4 5 6 # 2 7 8 9
ということで早速read関数を使用してExcelファイルを読み込んでみる。シンプルに読み込むとExcelファイルの1つ目のシート、今回ではSheet1の中身が出力される。
中身の出力は関数内のprintで行われており、何かしらの変数に代入しない限りはretrun部分は反映されない。ここではとりあえずの確認がしたかったのでread()のみにした。
シート指定で中身を出力
先ほどはシンプルに読み込むことで初めのシートが出力できた。ここではシートを指定して読み込む方法を解説する。1枚だけではなく複数枚も対応させる。
シート番号で指定
# シートの番号を指定することも可能 read(sheet_name=0) # A B C # 0 1 2 3 # 1 4 5 6 # 2 7 8 9
特定のシートを読み込むにはpd.read_excelの引数sheet_nameを使用する。ここでは0番目のシートという意味でsheet_name=0とした。
もちろん1番目のシート「name」にすることも可能。実際に指定してみるとnameのシートの中身が出力されていることがわかる。
read(sheet_name=1) # a b c d # 0 7 1 2 4 # 1 7 0 6 9 # 2 10 2 9 2 # 3 3 9 4 7 # 4 0 9 1 10
シート名で指定
# Excelファイルに存在するシート名の出力 all_sheets = pd.ExcelFile(file).sheet_names print(all_sheets) # ['Sheet1', 'name', '#3']
シート名でシート指定したい場合はシート名がわかっていないといけない。そんな時はpd.ExcelFileにファイル名を入れ、.sheet_namesとすることで全シート名を出力可能。
今回は3シート存在するので出力は3種類のlistとなる。これを参考に'name'をシート指定してみるとちゃんとその通り出力されることがわかる。
# シート名を指定しても出力可能 read(sheet_name='name') # a b c d # 0 7 1 2 4 # 1 7 0 6 9 # 2 10 2 9 2 # 3 3 9 4 7 # 4 0 9 1 10
複数のシートを指定
# listで選択すると複数シートの出力可能
# 出力形式はdict
df = read(sheet_name=[0, 'name'])
# {0: A B C
# 0 1 2 3
# 1 4 5 6
# 2 7 8 9, 'name': a b c d
# 0 7 1 2 4
# 1 7 0 6 9
# 2 10 2 9 2
# 3 3 9 4 7
# 4 0 9 1 10}
複数のシートを同時に指定したい場合はlistで欲しいシート番号もしくはシート名を指定する。出力形式はdictでkeysにはシート指定した時の番号もしくは名称が入る。
ここでは0番目という番号と'name'という名称を指定したので、dictのkeysは0と'name'となる。
print(type(df)) # <class 'dict'> print(df[0]) # A B C # 0 1 2 3 # 1 4 5 6 # 2 7 8 9 # 初めのシートの本来の名称は'Sheet1'だけど0で指定したから0で出力される print(df.keys()) # dict_keys([0, 'name'])
なお、複数シートの指定をlistではなくtupleで行った場合はエラーとなるので注意。
# tupelで指定するとエラー
read(sheet_name=(0, 'name'))
# TypeError: '>=' not supported between instances of 'tuple' and 'int'
read(sheet_name=('Sheet1', 'name'))
# TypeError: '>=' not supported between instances of 'tuple' and 'int'
全てのシートを指定
# sheet_name=Noneとすると全シートを読み込み
df = read(sheet_name=None)
# {'Sheet1': A B C
# 0 1 2 3
# 1 4 5 6
# 2 7 8 9, 'name': a b c d
# 0 7 1 2 4
# 1 7 0 6 9
# 2 10 2 9 2
# 3 3 9 4 7
# 4 0 9 1 10, '#3': 19 12 16
# 0 17 11 10}
先ほどは複数シートを個別で指定していたが、一括で全シートを読み込みたい時はsheet_name=Noneとすると可能になる。この時のdictのkeysはシート名となる。
もちろんdict形式なのでkeysとvaluesでそれぞれ出力するとその中身が確認できる。行数や列数が異なっていてもきっちり合うように対応してくれる。
# 1シートずつ出力されていることが確認できる
for key, val in df.items():
print(key)
print(val)
print()
# Sheet1
# A B C
# 0 1 2 3
# 1 4 5 6
# 2 7 8 9
# name
# a b c d
# 0 7 1 2 4
# 1 7 0 6 9
# 2 10 2 9 2
# 3 3 9 4 7
# 4 0 9 1 10
# #3
# 19 12 16
# 0 17 11 10
なお、sheet_name=Noneとせずに全シート名をそのまま指定してもいい。既に変数all_sheetsで全シート名は取得しているのでこれを活用。
# 全シート名を指定してもいい
print(all_sheets)
# ['Sheet1', 'name', '#3']
read(sheet_name=all_sheets)
# {'Sheet1': A B C
# 0 1 2 3
# 1 4 5 6
# 2 7 8 9, 'name': a b c d
# 0 7 1 2 4
# 1 7 0 6 9
# 2 10 2 9 2
# 3 3 9 4 7
# 4 0 9 1 10, '#3': 19 12 16
# 0 17 11 10}
データの書き込み
続いてはデータの書き込み。こちらもpandasを使用するとサクッと可能。
シンプルに書き込み
dct = {
'data1': (1, 2, 3, 4),
'data2': (4, 22, 31, 400),
'data3': ('a', 'b', 'c', 'd'),
}
index = ('row1', 'row2', 'row3', 'row4')
df = pd.DataFrame(dct, index=index)
print(df)
# data1 data2 data3
# row1 1 4 a
# row2 2 22 b
# row3 3 31 c
# row4 4 400 d
書き込みたいデータは上のもの。dictをベースにしてヘッダー合わせて5行4列のデータとした。中身は数値と文字を混在。
これをExcelファイルへと書き込みたい場合はシンプルにdf.to_excelとするだけ。csvファイルの時はto_csvだったのでそのExcel版という感じ。
# データフレームをexcelファイルへ書き込み
df.to_excel('output.xlsx', sheet_name='シート1')
このように出力すると謎にヘッダーとインデックスが中央揃えで太文字と枠線が入った状態でExcelに出力される。
回避するには以下のように書式を無くせばいい。これ以降も書式は残したままにしておく、要するに次のコードと次の画像のみ書式を消す。
# ヘッダー、インデックスの書式がいらないなら以下のように書式を消せばいい
pandas.io.formats.excel.ExcelFormatter.header_style = None
df.to_excel('output_formated.xlsx')
なお、シート名を空文字にするとエラーになるが、空白だとエラーとならない。
# シート名を空にするとエラー
df.to_excel('output_blank.xlsx', sheet_name='')
# ValueError: Title must have at least one character
# シート名を空白にするのはOK
df.to_excel('output_space.xlsx', sheet_name=' ')
また、シート名の指定がない場合は自動的に「Sheet1」として扱われる。
# sheet_nameを指定しないと「Sheet1」になる
df.to_excel('output_noname.xlsx')
複数シートへの書き込み
dct = {
'data1': (1, 2, 3, 4),
'data2': (4, 22, 31, 400),
'data3': ('a', 'b', 'c', 'd'),
}
index = ('row1', 'row2', 'row3', 'row4')
df = pd.DataFrame(dct, index=index)
dct2 = {
'data4': (100, 200, 300, 400),
'data5': (-4, -22, -31, -400),
'data6': ('0A', '01', 00.00, 0.100),
}
index2 = ('row0', 'row1', 'row2', 'row3')
df2 = pd.DataFrame(dct2, index=index2)
複数シートへ別々に書き込むことも可能。複数シートにデータを書き込みたいからデータフレームを2種類用意。df2のdata6では先頭の0の扱いを見るために作成。
それぞれのデータフレームは以下のように構成されている。
print(df) # data1 data2 data3 # row1 1 4 a # row2 2 22 b # row3 3 31 c # row4 4 400 d print(df2) # data4 data5 data6 # row0 100 -4 0A # row1 200 -22 01 # row2 300 -31 0.0 # row3 400 -400 0.1
複数シート出力はto_exceだけでは賄えず、pd.ExcelWriterを使用する。これはcsvなどのテキストファイルに書き込む際に使用されるwith openの書き方に似ている。
1データフレームずつto_excelで出力するのをpd.ExcelWriterでまとめて1ファイルにするイメージ。
# 複数シート対応のExcelへ出力
# 0.00は0に、0.100は0.1に変換されてExcelへ
with pd.ExcelWriter('output_multi.xlsx') as writer:
df.to_excel(writer, sheet_name='シート1')
df2.to_excel(writer, sheet_name='Sheet2')
なお、df2の0の扱いは以下の通りで、文字列として使用した0Aと01はそのまま0が使用されており、数値として使用された00.00と0.100は0と0.1に短縮された。
既存のファイルへの追記
dct3 = {
'data100': (1e1, 2e2, 3e3, 4e4),
}
df3 = pd.DataFrame(dct3)
print(df3)
# data100
# 0 10.0
# 1 200.0
# 2 3000.0
# 3 40000.0
今度は既存のExcelファイルへと書き込みを行う。そのために新しくデータフレームを追加。今度は1列だけのデータで指数表示を追加した。
既存ファイルへと書き込む場合も同じようにpd.ExcelWriterを使用するが、既存のファイルを読み込むというopenpyxl.load_workbookが必要になる。
追記しない限りopenpyxlはimportしなくてもいいが、追記する場合はimportが必要になる。
# mode='a'にして追記モードにて追記
with pd.ExcelWriter('output_multi.xlsx', mode='a') as writer:
writer.book = openpyxl.load_workbook('output_multi.xlsx')
df3.to_excel(writer, sheet_name='s3')
なお、追記モードであるmode='a'を省略してしまうとpd.ExcelWriterの時点で新しくファイルが作成（再作成）されるため、loadできずにエラーとなる。
再作成したExcelファイルではシートが存在していないため、シートは少なくとも1つ必要といった趣旨のエラーとなる。
# 追記モードの設定をしないと、そもそもシートがないのに作成するという解釈となりエラー # IndexError: At least one sheet must be visible
また、追記に関しては別名での保存も可能。ここでは新規でファイルを作成したため、mode='a'は指定しない。指定してしまうと逆にそんなファイルがないとエラーになる。
# 別名でファイルを作成してもいい
# この時は新規でファイルを作成するので追記モードのmode='a'は解除しないといけない
with pd.ExcelWriter('output_multi2.xlsx') as writer:
writer.book = openpyxl.load_workbook('output_multi.xlsx')
df3.to_excel(writer, sheet_name='SHEET3')
ファイルへの書き込みの小技
dct = {
'data1': (1, 2, 3, np.nan, 4),
'data2': (4, 22, 31, 400, None),
'data3': ('a', 'b', 'c', 'd', True),
}
index = ('row1', 'row2', 'row3', 'row4', 'row5')
df = pd.DataFrame(dct, index=index)
print(df)
# data1 data2 data3
# row1 1.0 4.0 a
# row2 2.0 22.0 b
# row3 3.0 31.0 c
# row4 NaN 400.0 d
# row5 4.0 NaN True
最後にファイル書き込みの小技を紹介する。ここで使用するデータフレームはNaNにNoneにTrueが入っているもの。しかし、データフレームにするとNoneはNaNに変換される。
なのでここではNaNやTrueの扱いを見つつ、その他の小技を解説する。
空行・空列を入れる
# 表の上から5行分、空白行を入れる
df.to_excel('output_startrow.xlsx', startrow=5)
まずは表の上から任意の行数だけ空白の行を入れるというもの。これは出力時に引数startrowを指定すればいい。ここでは5行分の空行を入れておいた。
列に関しても同じように操作可能。列の場合はstartcolとすればいい。列の方でも5列分の空白列を作成した。
# 表の左から5列分、空白列を入れる
df.to_excel('output_startcol.xlsx', startcol=5)
空白扱いのセルを任意の文字で埋める
# 空白扱いのセルを任意の文字で埋める
df.to_excel('output_na_rep.xlsx', na_rep='kuhaku')
完全にスルーしていたけど、NaNの部分は空白となり、Trueの部分は大文字でTRUEと入力されるようになる。Excelにおいてboolは全て大文字で表現される。
もし空白セルが嫌なら引数na_repで埋めることが可能。ここでは'kuhaku'という文字列で埋めてみた。
なお、埋める文字をバラバラにしたいとして、配列で埋める文字を指定しても配列がそのままExcelファイルへと反映されるだけ。元のデータフレームからいじらないといけない。
# 配列にして埋めようとしても配列がそのまま入るだけ
df.to_excel('output_na_rep_arr.xlsx', na_rep=('kuhaku1', 'kuhaku2'))
元のデータフレームをいじる方法は色々あるが、例えばreplaceを使用する場合だと列ごとで置き換える内容を変更可能。これを駆使してNaNの部分を埋めてみた。
# データフレームの時点で置き換えるなどの対処が必要
# data1列ではNaNをkoko1に、data2列ではkoko2に置き換え
# data3列ではないので置き換えなし
replace_dct = {'data1': 'koko1', 'data2': 'koko2', 'data3': 'koko3'}
print(df.replace(np.nan, replace_dct))
# data1 data2 data3
# row1 1.0 4.0 a
# row2 2.0 22.0 b
# row3 3.0 31.0 c
# row4 koko1 400.0 d
# row5 4.0 koko2 True
python x Excelで便利に
今回はpythonのpandasを使用してExcelファイルの読み込みから書き出しまで行った。csvファイルは実質テキストファイルと同じだからいじる部分が少ない。
一方で、Excelファイルになると書式なども入れることができるので、今回の読み込みから書き出しまでを習得してより高度なテクニックにつなげていきたい。
関連記事
-
-
【pandas&NaN】データフレームdfのNaN部分を空にする
続きを見る
-
-
【pd&MultiColumns】pandasで複数ヘッダーの設定と書き読み
続きを見る
-
-
【pd&MultiIndex】pandasで複数インデックスの設定と書き読み
続きを見る
-
-
【辞書&pandas】dict{name: , val: {a: [~], b:[~]}}のpandas化
続きを見る
