Masayan tech blog.

  1. ブログ記事一覧>
  2. Pythonでpandasを用いてエクセルファイルの読み書きを行う方法

Pythonでpandasを用いてエクセルファイルの読み書きを行う方法

公開日

環境

  • 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を用いてエクセルファイルの読み書きを行う方法について紹介しています。ぜひ参考にしてみてください。