PythonでExcelファイルをチャンク単位で効率的に処理する方法

はじめに

ビジネスの世界では、今なおExcelが絶対的な存在です。データエンジニアリングの現場でも、多くの同僚が重要な意思決定のためにExcelを活用している姿を目にし、驚かされることが少なくありません。筆者自身はMS OfficeやExcelスプレッドシートの熱心なファンというわけではありませんが、大規模なExcelスプレッドシートを効率的に扱うための便利なテクニックを紹介します。

準備：PandasでExcelを扱うための基礎知識

コードを見ていく前に、PandasでExcelスプレッドシートを扱う際の基本的なポイントを確認しておきましょう。

1. 必要なライブラリのインストール

まず、openpyxlとxlwtをインストールしてください。インストール済みかどうか不明な場合は、Pythonターミナルで pip freeze または pip list を実行すると、利用可能なパッケージ一覧を確認できます。

この記事では、以下の手順で進めます。

• タプル形式のデータを渡してExcelスプレッドシートを新規作成する
• 作成したファイルのデータをPandasのDataFrameに読み込む
• DataFrameのデータを新しいワークブックに書き出す

import xlsxwriter
import pandas as pd

2. サンプルデータでExcelスプレッドシートを作成する

辞書形式のデータをExcelスプレッドシートに書き込む小さな関数を用意します。各ステップの処理内容はコメントとして記載しています。

# 関数：write_data_to_files
def write_data_to_files(inp_data, inp_file_name):
"""
function : この関数に渡されたデータを対象ファイルに書き込む
args : inp_data : 書き込むタプル形式のデータ
       file_name : データを保存する対象ファイル名
return : なし
assumption : 作成するファイルとこのコードは同じディレクトリにあるものとする
"""
print(f" *** Writing the data to - {inp_file_name}")

# ワークブックを作成
workbook = xlsxwriter.Workbook(inp_file_name)

# ワークシートを追加
worksheet = workbook.add_worksheet()

# 先頭のセルから書き込み開始。行・列ともに0始まり
row = 0
col = 0

# 入力データを読み取り、行と列に書き込む
for player, titles in inp_data:
    worksheet.write(row, col, player)
    worksheet.write(row, col + 1, titles)
    row += 1

# ワークブックを閉じる
workbook.close()
print(f" *** Completed writing the data to - {inp_file_name}")

# 関数：excel_functions_with_pandas
def excel_functions_with_pandas(inp_file_name):
"""
function : PandasでExcelに対して適用できる主な操作を簡単に紹介する
args : inp_file_name : 入力となるExcelスプレッドシート
return : なし
assumption : 入力のExcelスプレッドシートとこのコードは同じディレクトリにあるものとする
"""
data = pd.read_excel(inp_file_name)

# 上位2行を表示
print(f" *** Displaying top 2 rows of - {inp_file_name} \n {data.head()} ")

# データ型などの情報を確認
print(f" *** Displaying info about {inp_file_name} - {data.info()}")

# 新しいシート「Sheet2」を作成してデータを書き込む
new_players_info = pd.DataFrame(data=[
{"players": "new Roger Federer", "titles": 20},
{"players": "new Rafael Nadal", "titles": 20},
{"players": "new Novak Djokovic", "titles": 17},
{"players": "new Andy Murray", "titles": 3}], columns=["players", "titles"])

new_data = pd.ExcelWriter(inp_file_name)
new_players_info.to_excel(new_data, sheet_name="Sheet2")

if __name__ == '__main__':
    # ファイル名とデータを定義
    file_name = "temporary_file.xlsx"

    # 保存用のタプルデータ
    file_data = (['player', 'titles'], ['Federer', 20], ['Nadal', 20], ['Djokovic', 17], ['Murray', 3])

    # file_dataをfile_nameに書き込む
    # write_data_to_files(file_data, file_name)

    # ExcelファイルをPandasに読み込んで関数を適用
    # excel_functions_with_pandas(file_name)

実行結果

*** Writing the data to - temporary_file.xlsx
*** Completed writing the data to - temporary_file.xlsx
*** Displaying top 2 rows of - temporary_file.xlsx
player titles
0 Federer 20
1 Nadal 20
2 Djokovic 17
3 Murray 3
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4 entries, 0 to 3
Data columns (total 2 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 player 4 non-null object
1 titles 4 non-null int64
dtypes: int64(1), object(1)
memory usage: 192.0+ bytes
*** Displaying info about temporary_file.xlsx - None

大規模なExcelファイルをチャンク単位で処理する

大きなCSVファイルを扱う場合、Pandasにはchunksizeオプションなど、データを分割して処理するための選択肢がいくつか用意されています。しかし残念ながら、Excelスプレッドシートについては、デフォルトではチャンク処理のオプションが提供されていません。

そこで役立つのが、以下のプログラムです。Excelスプレッドシートをチャンク単位で処理したい場合に非常に有効です。

実装例

def global_excel_to_db_chunks(file_name, nrows):
"""
function : Excelスプレッドシートをチャンク単位で処理する
args : file_name : 入力となるExcelスプレッドシート
       nrows : 1回のチャンクで読み込む行数
return : なし
assumption : 入力のExcelスプレッドシートとこのコードは同じディレクトリにあるものとする
"""
chunks = []
i_chunk = 0

# 先頭行はヘッダー。すでに読み込んでいるためスキップする
skiprows = 1
df_header = pd.read_excel(file_name, nrows=1)

while True:
    df_chunk = pd.read_excel(
        file_name, nrows=nrows, skiprows=skiprows, header=None)
    skiprows += nrows

    # データが存在しなければループを抜ける
    if not df_chunk.shape[0]:
        break
    else:
        print(
            f" ** Reading chunk number {i_chunk} with {df_chunk.shape[0]} Rows")
        chunks.append(df_chunk)
        i_chunk += 1

df_chunks = pd.concat(chunks)

# ヘッダーと結合できるように列名を揃える
columns = {i: col for i, col in enumerate(df_header.columns.tolist())}
df_chunks.rename(columns=columns, inplace=True)
df = pd.concat([df_header, df_chunks])

print(f' *** Reading is Completed in chunks...')

if __name__ == '__main__':
    print(f" *** Gathering & Displaying Stats on the excel spreadsheet ***")
    file_name = 'Sample-sales-data-excel.xls'
    stats = pd.read_excel(file_name)
    print(f" ** Total rows in the spreadsheet are - {len(stats.index)} Rows")

    # Excelファイルを1000行ずつのチャンクで処理する
    global_excel_to_db_chunks(file_name, 1000)

出力結果

*** Gathering & Displaying Stats on the excel spreadsheet ***
** Total rows in the spreadsheet are - 9994 Rows
** Reading chunk number 0 with 1000 Rows
** Reading chunk number 1 with 1000 Rows
** Reading chunk number 2 with 1000 Rows
** Reading chunk number 3 with 1000 Rows
** Reading chunk number 4 with 1000 Rows
** Reading chunk number 5 with 1000 Rows
** Reading chunk number 6 with 1000 Rows
** Reading chunk number 7 with 1000 Rows
** Reading chunk number 8 with 1000 Rows
** Reading chunk number 9 with 994 Rows
*** Reading is Completed in chunks...

このように、9994行のスプレッドシートが1000行ずつ10個のチャンクに分割され、順番に読み込まれていることがわかります。この手法を活用すれば、一度にメモリへ読み込むのが難しい大規模なExcelファイルでも、少しずつ安全に処理できるようになります。