カテゴリー

pandas

【python&Excel】pandasでエクセルファイルを読み込み・書き出し

2021年9月25日

こんな人にオススメ

pandasを使用してcsvファイルを扱うのはいけるけど、Excelの場合はどうするの?

シートが複数枚ある場合はちゃんと読み込めるの?

ということで、今回はpythonのpandasを使用してExcelファイルを読み込み・書き出す方法を解説する。csvファイルについては以下参照。

【python&csv読み込み】pythonを使ってcsvを読み込み

こんな人にオススメ pythonを使{ ...

続きを見る

csvファイルだと1シートしか存在しないが、Excelになるとシートが複数枚になることも。そんな時はちゃんと読めるかというと読める。

シート指定もできるし、全シートの読み込みもできる。今回は基礎的な方法を解説していく。

運営者のメガネとです。YouTubeTwitterInstagramも運営中。

自己紹介はこちらから、お問い合わせはこちらからお願いいたします。

運営者メガネ

作成したコード全文

下準備

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ファイルを読み込んだ後に出力するだけのシンプルな関数を定義する。pandasExcelファイルを読み込むときはpd.read_excelで可能。

**kwargsは可変長キーワード引数で、このread関数を使用するときにpd.read_excelの引数を後から追加できるようにするために使用。詳しくは以下。

【plotly&kwargs】グラフ作成時の設定を後から追加できるように

こんな人にオススメ plotlyでグ} ...

続きを見る

あとは読み込んだデータフレームを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で欲しいシート番号もしくはシート名を指定する。出力形式はdictkeysにはシート指定した時の番号もしくは名称が入る。

ここでは0番目という番号と'name'という名称を指定したので、dictkeys0'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とすると可能になる。この時のdictkeysはシート名となる。

もちろんdict形式なのでkeysvaluesでそれぞれ出力するとその中身が確認できる。行数や列数が異なっていてもきっちり合うように対応してくれる。

# 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種類用意。df2data6では先頭の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の扱いは以下の通りで、文字列として使用した0A01はそのまま0が使用されており、数値として使用された00.000.10000.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が必要になる。

追記しない限りopenpyxlimportしなくてもいいが、追記する場合は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

最後にファイル書き込みの小技を紹介する。ここで使用するデータフレームはNaNNoneTrueが入っているもの。しかし、データフレームにするとNoneNaNに変換される。

なのでここではNaNTrueの扱いを見つつ、その他の小技を解説する。

空行・空列を入れる

# 表の上から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で複数ヘッダーの設定と書き読み

こんな人にオススメ 以前pandas{ ...

続きを見る

【pd&MultiIndex】pandasで複数インデックスの設定と書き読み

こんな人にオススメ pandasで行| ...

続きを見る

多項式関数のグラフ
【辞書&pandas】dict{name: , val: {a: [~], b:[~]}}のpandas化

こんな人にオススメ ネスト ...

続きを見る

スイッチボット

2022/9/11

【SwitchBotロックレビュー】これからのスタンダードになりうるスマートロック

こんな人にオススメ SwitchBotからスマートロック「SwitchBotロック」が発売された ...

生活に役立つ

2022/10/25

【メガネ厳選】クソ便利に使っているサービスやアイテム達

このページでは執筆者「メガネ」が実際に使って便利だと感じているサ ...

マウス

2022/9/11

【Logicool MX ERGO vs MX Master 3】ERGOをメインにした決定的な理由

こんな疑問・お悩みを持っている人におすすめ 執筆者はLogicoolのハイエンӠ ...

完全ワイヤレスイヤホン(TWS)

2022/11/21

【ながら聴きイヤホン比較】SONY LinkBuds、ambie、BoCoはどれがおすすめ?

こんな人におすすめ 耳を塞がない開放型のイヤホンに完全ワイヤレスӟ ...

macOSアプリケーション

2022/10/15

【M1 Mac】MacBook Proに入れている便利でニッチなアプリを21個紹介する

こんな人におすすめ MacBookを購入してLINEとか必要最低限のアプリは入れた。 ...

完全ワイヤレスイヤホン(TWS)

2022/10/23

【SENNHEISER MOMENTUM True Wireless 3レビュー】高レベルでバランス型の高音質イヤホン

こんな人におすすめ SENNHEISER MOMENTUM True Wireless 3って実際のところどうなの? 評判は良い ...

完全ワイヤレスイヤホン(TWS)

2022/11/21

【SONY WF-1000XM4レビュー】神とゴミのハーフ&ハーフ

こんな人におすすめ SONYのフラグシップモデル「SONY WF-1000XM4」ってどれくらい性 ...

完全ワイヤレスイヤホン(TWS)

2022/8/19

【Nothing ear (1)レビュー】ライトな完成度、アップデートに期待

こんな人にオススメ 完全ワイヤレスイヤホン(TWS)でスケルトンボディ ...

Pythonを学びたいけど独学できる時間なんてない人へのすゝめ

執筆者は大学の研究室・大学院にて独学でPythonを習得した。

でも社会人になったら独学で行うには時間も体力もなくて大変だ。

時間がない社会人だからこそプロの教えを乞うのが効率的。

ここでは色んなタイプに合ったプログラミングスクールの紹介をする。

  • この記事を書いた人

メガネ

ベンチャー企業のWebエンジニア駆け出し。独学のPythonで天文学系の大学院を修了→新卒を1.5年で辞める→転職→今に至る。
常時金欠のガジェット好きでM1 MacBook Pro x Galaxy S22 Ultraの狂人。
人見知りで根暗だったけど、人生楽しもうと思って良い方向に狂う→人生が楽しい

ガジェットのレビューとPythonコードを記事にしています。ぜひ楽しんでください🦊
自己紹介と半生→変わって楽しいの繰り返し

-pandas
-, ,