環境
- Windows 10
- Python 3.10.1
- VSCode
- Pandas 1.4.1
事前準備
予め「openpyxl」のインストールが必要(pandasの内部で使用する)
※バージョン次第では、xlrdもインストールする必要もあるので注意
pip install openpyxlエクセルの読み取り
前提
プログラミング言語一覧というシートで以下のようなデータをもつsample.xlsxを読み取りたいと思います
id | name | type |
1 | PHP | 動的型付け |
2 | Java | 静的型付け |
3 | Python | 動的型付け |
4 | Ruby | 動的型付け |
使用するエクセルのフォーマットルールを事前に決めておいたほうがpandasでデータを扱いやすくなります。今回は以下のようなルールにしました
- 各シートにヘッダー行がある
- 各シートに一番左の列に1から始まる連番ないしID列がある
- 各シートに任意のシート名がついている
- indexはもともと用意されている0始まりの連番を使用する(エクセルの実際のカラムのいずれもindexとして使用しない)
ワークブック・ワークシートの取得
read_excelを使用することにより、エクセルのデータをpandas.DataFrameで読み取ることが可能です。
import pandas as pd
from shared.Domain.x_file_system_path import XFileSystemPath
from shared.Domain.xstr import XStr
df = pd.read_excel(
XFileSystemPath(XStr("tests/Domain/Excel/sample.xlsx")).to_absolute().of_text(),
# 基本はすべてのシートを読み込むためNoneで指定
sheet_name=None,
# ヘッダ行は0行目とする
header=0,
# index行はデフォルトのまま、0始まりの連番とする
index_col=None,
)取得したdfは{シート名: データ}のようなシート名をキーとした辞書型で取得することが可能です
今回はsheet_nameを指定していないので、1エクセルブックの全シートが取得されているため、df[シート名]とすることで、対象のシートのデータのみ取得することが可能です
ファイルパスの扱いはオブジェクトを生成して行っています。
セルの取得
pandasのiatメソッドを使用して特定のセルを取得します
例えば、エクセルのname列のPythonという文字列(エクセルの行番号：4, 列番号:2)を取得したい場合は、以下のようにします
pandasのdataFrameの行・列が0始まりなのが個人的には少し扱いにくいので、行はヘッダ行分と合わせて2マイナスし、列はiatが0始まりなのでマイナス1すると実際のエクセルの行・列番号を指定する形でよくなるので、以下のようにしています
worksheet = df["プログラミング言語一覧"]
index_number = 4
column_number = 2
cell = worksheet.iat[index_number - 2, column_number - 1]
# Python単一レコードの取得
pandasのilocメソッドを使用してレコードを取得します
例えば、エクセルの2行目を取得したい場合は、以下のようにします(エクセルの行番号は2ですが、実際のdfの行番号は0です)
worksheet = df["プログラミング言語一覧"]
index_number = 2
record = worksheet.iloc[index_number - 2]
# id name type
# 1 PHP 動的型付けilocで行番号のみ指定した場合は全カラムが対象になります
複数レコードの取得
こちらもpandasのilocメソッドを使用してレコードを取得します
例えばエクセルの3行目から4行目(全カラム)を取得したい場合は以下のようになります
worksheet = df["プログラミング言語一覧"]
start_row = 3
end_row = 4
start_column = 1
end_column = 3
records = worksheet.iloc[start_row - 2 : end_row - 1, start_column - 1 : end_column]
# [2 rows x 3 columns]
# id name type
# 2 Java 静的型付け
# 3 Python 動的型付け条件付きでレコードを取得
dataFrameに対してquery()を使用することで、カラムの条件などでフィルター処理を行うことが可能です
worksheet = df["プログラミング言語一覧"]
start_row = 2
end_row = 5
start_column = 1
end_column = 3
records = worksheet.iloc[start_row - 2 : end_row - 1, start_column - 1 : end_column]
filterd_records = records.query("type == '動的型付け'")
# id name type0 1 PHP 動的型付け1 2 Java 静的型付け2 3 Python 動的型付け3 4 Ruby 動的型付け[4 rows x 3 columns]
↓
# id name type0 1 PHP 動的型付け2 3 Python 動的型付け3 4 Ruby 動的型付け[3 rows x 3 columns]エクセルの書き出し
- 書き出しは、df.to_excel()で可能です
- 出力する際はinputとして辞書型のデータを渡す必要があるため、エクセルに出力したいデータを事前に辞書型で作成します
- 辞書型のデータをdataFrame型に変換するには、from_dict()を使用します。
import pandas as pd
from shared.Domain.x_file_system_path import XFileSystemPath
from shared.Domain.xstr import XStr
data = {
"id": [1, 2, 3, 4], // id列
"name": ["PHP", "Java", "Python", "Ruby"], // name列
"type": ["動的型付け", "静的型付け", "動的型付け", "動的型付け"], // type列
}
df = pd.DataFrame.from_dict(data, orient="index").T
df.to_excel(
XFileSystemPath(XStr("tests/Domain/Excel/output_sample.xlsx"))
.to_absolute()
.of_text(),
index=False,
)from_dict()のパラメータorientにindexを指定することで、辞書データのキー(idやnameなど)をdataframeのカラムにすることが可能です。また、エクセル出力する際にdataFrameのindexが出力されてほしくないので、インデックスが不要な場合は index=Falseとしています
まとめ
いかがでしたでしょうか。本記事では、Pythonでpandasを用いてエクセルファイルの読み書きを行う方法について紹介しています。ぜひ参考にしてみてください。