pandasでexcelファイルを読み込む方法について解説します。
pandasによるExcelファイル読み込み方法 ~ read_excel ~
本記事では、pandasでExcelファイルを読み込む方法を紹介します。pandasでExcelを読み込む場合はread_excel関数を使用します。
まずは、read_excel関数を使ってファイルを読み込む基本を説明し、その後にデータにあわせてファイルを読み込むときの指定方法を色々と紹介していきます。
pd.read_excelの公式ドキュメントの記載はこちらを参照してください。
基本的な読み込み方法
以下のようなsample_1というシートがあるexcel1.xlsxというファイルを使用して、read_excel関数によるExcelファイルの基本的な読み込み方法を見ていきます。
pandasのread_excel関数でExcelファイルを読み込む場合には、以下のようにread_excelに対象となるExcelファイルパスを指定します。
import pandas as pd
# Excelファイルを指定して読み込む
df = pd.read_excel('excel1.xlsx')
print(df)
【実行結果】 no product_code client date price 0 1 A 顧客1 2022-03-01 1000 1 2 A 顧客2 2022-03-01 1000 2 3 B 顧客1 2022-03-01 500 3 4 B 顧客1 2022-03-01 500 4 5 C 顧客1 2022-03-01 2000 5 6 A 顧客2 2022-03-15 1000 6 7 A 顧客2 2022-03-15 1000 7 8 C 顧客3 2022-03-15 2000 8 9 B 顧客2 2022-03-31 500 9 10 B 顧客2 2022-03-31 500
読み込んだファイルは上記結果のようにpandasのDataFrameの形式で読み込まれます。Excelの1行目は列名として読み込まれ、インデックスについては自動的に0から始まる番号が付与されます。
read_excelは、engine引数で指定されるモジュールを使ってファイルを開きます。version 1.2.0からロジックが以下のようになっているため環境によってはimport error等でモジュールが足りない旨のエラーが出るかもしれません。
- OpenDocument format (.odf, .ods, .odt) : odfが使用される
- xls format : xlrdが使用される
- xlsb format : pyxlsbが使用される
- その他 : openpyxlが使用される
実行時にモジュールが足りない旨のエラーが出る用でしたら、エラー内容をよく確認してpip等により必要モジュールをインストールするようにしてください。
シート名を指定して読み込む方法
read_excel関数は、デフォルトでは初めのシート(一番左のシート）が読み込まれます。シート名を明確に指定したい場合には、sheet_nameでシート名を指定します。
import pandas as pd
# シート名を指定して読み込む
df = pd.read_excel('excel1.xlsx',
sheet_name='sample_1')
print(df)
【実行結果】 no product_code client date price 0 1 A 顧客1 2022-03-01 1000 1 2 A 顧客2 2022-03-01 1000 2 3 B 顧客1 2022-03-01 500 3 4 B 顧客1 2022-03-01 500 4 5 C 顧客1 2022-03-01 2000 5 6 A 顧客2 2022-03-15 1000 6 7 A 顧客2 2022-03-15 1000 7 8 C 顧客3 2022-03-15 2000 8 9 B 顧客2 2022-03-31 500 9 10 B 顧客2 2022-03-31 500
openで開いたファイルから読み込む方法
上記の例ではファイルパスを直接指定していました。read_excel関数は、以下のようにファイルパスではなくopen関数で開いたオブジェクトを渡してExcelを読み込むことも可能です。
import pandas as pd
# openで開いたファイルから読み込む
with open('excel1.xlsx', 'rb') as f:
df = pd.read_excel(f)
print(df)
【実行結果】 no product_code client date price 0 1 A 顧客1 2022-03-01 1000 1 2 A 顧客2 2022-03-01 1000 2 3 B 顧客1 2022-03-01 500 3 4 B 顧客1 2022-03-01 500 4 5 C 顧客1 2022-03-01 2000 5 6 A 顧客2 2022-03-15 1000 6 7 A 顧客2 2022-03-15 1000 7 8 C 顧客3 2022-03-15 2000 8 9 B 顧客2 2022-03-31 500 9 10 B 顧客2 2022-03-31 500
上記まででExcelファイルをpandasのDataFrameに読み込む基本的な方法を見てきました。以降では、read_excelを実行する際にデータにあわせたファイル読み込み指定をする方法を色々と細かく見ていきましょう。
データにあわせたファイル読み込みの指定方法
上記で見たようにヘッダー行が1行目にあって、その下にデータがあるようなシンプルなデータであればread_excelで簡単に読み込んで扱うことが可能です。
しかし、企業などで使用されているExcelデータは、作成者が好き勝手に作っているデータがほとんどでヘッダーやフッターがあったりと色々な形式のデータがあると思います。また、必ずしもすべての列情報が必要ではないというようなケースもあるでしょう。
pandasのread_excelでは、読み込む対象データにあわせて引数で色々な指定をすることができます。以降では、各種指定方法について紹介していきます。
ヘッダーを指定する方法 header
Excelファイルにはヘッダー行があるようなデータもたくさんあるかと思います。そのような場合には、headerでヘッダー行を指定することができます。いくつかのケースを使って説明します。
特定の行をヘッダーとして指定する
単一行をヘッダーに指定する場合
excel2.xlsxのsample_1シートで、以下のようにヘッダー行の上に読み込みに不要なデータ（以下例では1行目）があるようなケースを考えます。この時にデータとして取得したいのは2行目以降のデータです。
このようなデータで、ヘッダーとなる行を指定する場合には、以下のようにheaderでヘッダー行となる行番号を指定します。read_excelでは、行番号は0からなので覚えておきましょう。Excelでの1行目の番号は0になります。
import pandas as pd
# ヘッダー行を指定する(単一行の場合)
df = pd.read_excel('excel2.xlsx',
sheet_name='sample_1',
header=1)
print(df)
【実行結果】 no product_code client date price 0 1 A 顧客1 2022-03-01 1000 1 2 A 顧客2 2022-03-01 1000 2 3 B 顧客1 2022-03-01 500 3 4 B 顧客1 2022-03-01 500 4 5 C 顧客1 2022-03-01 2000 5 6 A 顧客2 2022-03-15 1000 6 7 A 顧客2 2022-03-15 1000 7 8 C 顧客3 2022-03-15 2000 8 9 B 顧客2 2022-03-31 500 9 10 B 顧客2 2022-03-31 500
複数行をヘッダーに指定する場合
excel2.xlsxのsample_2シートで、以下のようにヘッダー行が複数行になっているような例を考えます。以下の例では、2行目、3行目がヘッダー行となります。
このようなデータで、複数行をヘッダー行と指定する場合には、headerに行番号を列挙したリストを指定します。このようにすることで、pandasは指定した行をMultiIndexとして取得します。
import pandas as pd
# ヘッダー行を指定する(複数行の場合)
df = pd.read_excel('excel2.xlsx',
sheet_name='sample_2',
header=[1, 2])
print(df)
【実行結果】
product_infomation
no product_code client date price
0 1 A 顧客1 2022-03-01 1000
1 2 A 顧客2 2022-03-01 1000
2 3 B 顧客1 2022-03-01 500
3 4 B 顧客1 2022-03-01 500
4 5 C 顧客1 2022-03-01 2000
5 6 A 顧客2 2022-03-15 1000
6 7 A 顧客2 2022-03-15 1000
7 8 C 顧客3 2022-03-15 2000
8 9 B 顧客2 2022-03-31 500
9 10 B 顧客2 2022-03-31 500
ヘッダー行がないデータを読み込む場合
excel2.xlsxのsample_3シートで、ヘッダー行がなく1行目からデータが開始しているような以下のデータの場合を考えます。
ヘッダー行がないようなデータを読み込む場合には、以下のようにheader=Noneで指定します。
import pandas as pd
# ヘッダー行がないファイルを読み込む
df = pd.read_excel('excel2.xlsx',
sheet_name='sample_3',
header=None)
print(df)
【実行結果】
0 1 2 3 4
0 1 A 顧客1 2022-03-01 1000
1 2 A 顧客2 2022-03-01 1000
2 3 B 顧客1 2022-03-01 500
3 4 B 顧客1 2022-03-01 500
4 5 C 顧客1 2022-03-01 2000
5 6 A 顧客2 2022-03-15 1000
6 7 A 顧客2 2022-03-15 1000
7 8 C 顧客3 2022-03-15 2000
8 9 B 顧客2 2022-03-31 500
9 10 B 顧客2 2022-03-31 500
結果を見てもわかるようにheader=Noneとした場合、列名としては0~の数字がふられます。
インデックス列を指定する方法 index_col
read_excel関数のデフォルトの読み込みでは、インデックスには0からの数字が自動で割り振られます。しかし、Excel内のデータの中にインデックスとして使用できる列がある場合には、その列をインデックスとして扱いたくなります。
このような時にread_excel関数でインデックス列を指定する場合には、index_colで指定することができます。以下のexcel1.xlsxファイルを使ってインデックス列を指定する方法をみてみましょう。
単一列をインデックス列に指定する場合
単一の列を指定する場合には、以下の例のようにindex_colに該当する列の番号を指定します。列番号は0からなので注意しましょう。
以下の例では、product_codeの列をインデックスに指定しています。
import pandas as pd
# インデックス列を指定する(単一列の場合)
df = pd.read_excel('excel1.xlsx',
index_col=1)
print(df)
【実行結果】
no client date price
product_code
A 1 顧客1 2022-03-01 1000
A 2 顧客2 2022-03-01 1000
B 3 顧客1 2022-03-01 500
B 4 顧客1 2022-03-01 500
C 5 顧客1 2022-03-01 2000
A 6 顧客2 2022-03-15 1000
A 7 顧客2 2022-03-15 1000
C 8 顧客3 2022-03-15 2000
B 9 顧客2 2022-03-31 500
B 10 顧客2 2022-03-31 500
上記結果を見ると自動で振られる0からの数字ではなく、product_codeがインデックスとなっているのが分かるかと思います。
複数列をインデックス列に指定する場合
read_excel関数でインデックス列を指定する際には、複数の列を指定することができます。複数の列を指定する場合には以下のように、列番号を列挙したリストを渡します。
このようにすることで、pandasは指定した列をMultiIndexとしてインデックスに設定します。
import pandas as pd
# インデックス列を指定する(複数列の場合)
df = pd.read_excel('excel1.xlsx',
index_col=[1, 2])
print(df)
【実行結果】
no date price
product_code client
A 顧客1 1 2022-03-01 1000
顧客2 2 2022-03-01 1000
B 顧客1 3 2022-03-01 500
顧客1 4 2022-03-01 500
C 顧客1 5 2022-03-01 2000
A 顧客2 6 2022-03-15 1000
顧客2 7 2022-03-15 1000
C 顧客3 8 2022-03-15 2000
B 顧客2 9 2022-03-31 500
顧客2 10 2022-03-31 500
上記結果を見ると、product_codeとclientでMultiIndexとして設定されていることが分かります。
読み込む列を指定する方法 usecols
分析で扱うExcelデータは、沢山の列情報を持っていることがよくあります。しかし、分析で必要になる列情報はその中の一部ということがほとんどです。
このような場合にread_excelではusecolsで取り込む列を指定することができます。指定の方法がいくつかあるので、以下のexcel1.xlsxファイルを使っていくつかの例をみていきましょう。
列名称で指定する場合
列名称で指定する場合には、以下のようにusecolsに具体的な列名称（例では’product_code’等)を列挙したリストを指定します。
import pandas as pd
# 読み込む列を指定する（列名称で指定）
df = pd.read_excel('excel1.xlsx',
usecols=['product_code', 'client', 'price'])
print(df)
【実行結果】 product_code client price 0 A 顧客1 1000 1 A 顧客2 1000 2 B 顧客1 500 3 B 顧客1 500 4 C 顧客1 2000 5 A 顧客2 1000 6 A 顧客2 1000 7 C 顧客3 2000 8 B 顧客2 500 9 B 顧客2 500
列番号で指定する場合
列番号の数値で指定する場合には、以下のようにusecolsに対象とする列番号を列挙したリストを指定します。列番号は0からなので注意しましょう。
import pandas as pd
# 列の指定方法（intで指定）
df = pd.read_excel('excel1.xlsx',
usecols=[1, 2, 4])
print(df)
【実行結果】 product_code client price 0 A 顧客1 1000 1 A 顧客2 1000 2 B 顧客1 500 3 B 顧客1 500 4 C 顧客1 2000 5 A 顧客2 1000 6 A 顧客2 1000 7 C 顧客3 2000 8 B 顧客2 500 9 B 顧客2 500
Excelの列名(A, B, …)で指定する場合
Excelでの列名(A, B, …)でも指定することができます。この際には範囲を指定するA:Eのような指定も使用することができます。
具体的には、以下のようにusecolsに指定列や列範囲を示す文字列を’,'(カンマ)区切りの文字列を指定します。
import pandas as pd
# 列の指定方法（Excelの列(A,B,...)でまとめて指定）
df = pd.read_excel('excel1.xlsx',
usecols='B:C, E')
print(df)
【実行結果】 product_code client price 0 A 顧客1 1000 1 A 顧客2 1000 2 B 顧客1 500 3 B 顧客1 500 4 C 顧客1 2000 5 A 顧客2 1000 6 A 顧客2 1000 7 C 顧客3 2000 8 B 顧客2 500 9 B 顧客2 500
注意点として、リストではなく’,'(カンマ)区切りの文字列で指定する必要がある点に注意しましょう。例えば、usecols=[‘B:C’, ‘E’]というような指定はValueErrorとなります。
列名称を書き換えて読み込む方法 names
データベースから出力したデータなどは列名が英語表記となっているものなども多くあるかと思います。分析の際には日本語名等に直して取り込んで扱いたい等、列名称を書き換えて読み込みたい場合、read_excel関数ではnamesで指定することができます。
以下のexcel1.xlsxファイルを使ってみてみましょう。
具体的には、以下のようにnamesに書き換え後の名称のリストを指定します。
import pandas as pd
# 列名称を変更して読み込む
df = pd.read_excel('excel1.xlsx',
names=['製造番号', '製品コード', '顧客', '日付', '価格'])
print(df)
【実行結果】 製造番号 製品コード 顧客 日付 価格 0 1 A 顧客1 2022-03-01 1000 1 2 A 顧客2 2022-03-01 1000 2 3 B 顧客1 2022-03-01 500 3 4 B 顧客1 2022-03-01 500 4 5 C 顧客1 2022-03-01 2000 5 6 A 顧客2 2022-03-15 1000 6 7 A 顧客2 2022-03-15 1000 7 8 C 顧客3 2022-03-15 2000 8 9 B 顧客2 2022-03-31 500 9 10 B 顧客2 2022-03-31 500
結果を見てみると、指定した列名称で読み込みができていることが分かります。
Excelファイルではヘッダー行やフッター行などが含まれるデータがよくあります。以下のexcel3.xlsxのようなファイルで見てみましょう。以下の例でいうところの1~3行目、15~17行目のような部分が読み飛ばしたい部分です。
このようなExcelデータの場合で、ヘッダーやフッターを読み飛ばしたいときには、以下の例のようにskiprowsやskipfotterを使用して読み飛ばす行数を指定します。
import pandas as pd
# 上下のヘッダーやフッターを読み飛ばす
df = pd.read_excel('excel3.xlsx',
skiprows=3,
skipfooter=3)
print(df)
【実行結果】 no product_code client date price 0 1 A 顧客1 2022-03-01 1000 1 2 A 顧客2 2022-03-01 1000 2 3 B 顧客1 2022-03-01 500 3 4 B 顧客1 2022-03-01 500 4 5 C 顧客1 2022-03-01 2000 5 6 A 顧客2 2022-03-15 1000 6 7 A 顧客2 2022-03-15 1000 7 8 C 顧客3 2022-03-15 2000 8 9 B 顧客2 2022-03-31 500 9 10 B 顧客2 2022-03-31 500
まとめ
pandasでExcelファイルを読み込むためのread_excel関数について使用方法を解説しました。
pandasのread_excel関数では以下引数で読み込みの細かな設定が可能
- header：ヘッダーを指定する
- index_col：インデックス列を指定する
- usecols：読み込む列を指定する
- names：列名称を書き換えて読み込む
- skiprows：ヘッダーを読み飛ばす
- skipfooter：フッターを読み飛ばす
実際の企業などで使用されているExcelは好き勝手なフォーマットで作られていることも多いので上記のような扱いに慣れておくと非常に便利です。色々と使ってread_excel関数に慣れてもらえればと思います。
上記で紹介しているソースコードについてはgithubにて公開しています。参考にしていただければと思います。
