エクセル帳票からのデータ取得は辛いよ その１～pandasによる抽出～

はじめに

pythonによるエクセル帳票からのデータ抽出について書いていこうと思います。
何番煎じだ！って感じですが、日々苦しんでいることについて、これからエクセル帳票からデータを抜かなければいけない人たちへ、少しでも何か残せられれば幸いです。
「自分だったらこう書いてもらえたらわかりやすい」という事を念頭に、初学者向けになるべく丁寧に記事を書いていこうと思います。

自己紹介

とあるメーカーでデータサイエンス職として働いています。データ分析に必要なデータの準備工程を担当することが多いです。
ちなみにこの職について２年、python歴３年の若輩者です。
もし見ていただけた熟練者の方がいたら、「こんな風に書いたらもっとスマートだぜ！」というのがあれば教えてください。

この記事の対象者

皆さんの貴重な時間を奪うのも申し訳ないので、予め宣言しておきます。

対象者

これからエクセルからデータ抽出したい人。

非対象者

エクセルからのデータ抽出に辟易している人。（たぶんイライラを思い出させてしまう）

エクセルからのデータ抽出の辛さを既に味わっている人。（たぶんイライラを思い出させてしまう）

プロ中のプロ（退屈すると思う）

構成

その１　pandasによる抽出
その２　振り返りたくない過去、落とし穴
その３　openpyxlのはまりどころとpyexcel
その４　もうエクセル帳票やめようよ

の４部構成を予定しております。今回はその１のみの解説です。
尚、本文中のコードはjupyternotebookでの実行を想定して書いてます。
.pyで実行用のものを作りたい場合は、途中にあるdisplay(dataframe)の様な結果を表示するコードは削除してください。
それでは本編へ。

その１　pandasによるデータ抽出

これは基本ですね。
pandas エクセル　抽出　のキーワードで検索したらごまんと出てきますが、基本から見ていきましょう。
共通して使用する題材はこちら。⇒サンプル
よくある見積書を参考に作成しました。

↓↓↓こんなの

#pandasをインポート
import pandas as pd

#同階層にあるmitsumori_ex1.xlsx　を読み込む。適宜実際に読込みたいエクセルファイルのパスに変更。
data = pd.read_excel('mitsumori_ex1.xlsx', engine='openpyxl')

ライブラリが不足している場合はエラーが出ます。(xlrdが無かった場合)

ImportError: Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.

この場合はバージョン2以下のxlrdのものを入れてください。
参考 https://xlrd.readthedocs.io/en/latest/changes.html

※pandasやopenpyxlも入ってない場合は入れてくださいね。

うまく行くと、こうなります。【図１】

御覧の通り、エクセルで空白の部分はNaNだったりNoneだったりします。

今回は、複数のこの様な見積書からいつどんな部品をいくつ購入したか、単価、値引き額、金額、納入先をまとめて一つの表を作り、エクセルに出力する、という仕事を与えられた想定で、話を進めます。
つまり、インプットと求められているアウトプットイメージは下記のとおりです。【図２】
（中身は超テキトーです。あくまで例として）

今回は例として、品名、単価、数量、値引き額、金額が欲しいので、そのデータフレームを作成します。

#品名はindexでいうと12,　開始列は0列目, 終了列は4列目であることを利用してスライス
data1 = data.iloc[12:, :5]

#後で初めてNaNになるインデックスを取得して削除するため、インデックスを振り直す。
data2 = data1.reset_index(drop=True)
data2

こうすると、下記の様なデータフレームができます。【図３】

では、初めてNaNが現れるインデックスを取得し、必要な部分のみ残したデータフレームを作成します。

#0列目が初めてNaNになるインデックスを取得する
nan_idx = data2[data2.iloc[:, 0].isnull()==True].index.tolist()
print(f'nan_idx:{nan_idx}')

#nan_idxの１つめより下の行をスライスで除去
data3 = data2.iloc[:nan_idx[0], :]
display(data3)

できました。【図４】

後は品名などの項目をデータフレームのカラムに設定します。

#カラムに入れたい0番目の値をリストに格納し、カラムとして定義する。
cols = data3.iloc[0, :].tolist()
data3.columns = cols

#0番目の行は不要なのでスライスで除去
data4 = data3.iloc[1:, :]
data4

できました。【図５】

それでは、購入日と仕入先を追加しましょう。図１から購入日は0番目6列目、仕入先は2番目0列目ですので、下記の様に取得、データフレームに追加します。

#購入日の取得
buy_date = data.iloc[0, 6]

#仕入先の取得
supplier = data.iloc[2, 0]

#データフレームへの追加
data4['購入日'] = buy_date
data4['仕入先'] = supplier
data4

できました。【図６】

ただ、これだと少し違和感があるので、購入日と仕入先を左へもっていきたいと思います。

#並べ替えの方法はいくつかあるが、ここでは購入日より右、左でデータフレームを分割し、再度結合する方法をとる。
data4_r = data4.iloc[:, 5:]
data4_l = data4.iloc[:, :5]

#データフレームの結合
data5 = pd.concat([data4_r, data4_l], axis=1)
data5

できました。【図７】

これで土台はできたので、あとは関数にして使いまわせるようにしましょう。

#ファイル読み込みからデータフレーム成型まで一連の処理を関数にする。
def make_df(path):
    '''
    見積書を読込み、データフレームを成型する。
    <input>
        path : 読込みたいエクセルファイルのパス(str)
    <output>
        df_summary : 必要事項のみを抽出したデータフレーム(DataFrame)
    '''
    data = pd.read_excel(path, engine="openpyxl")
    #品名はindexでいうと12,　開始列は0列目, 終了列は4列目であることを利用してスライス
    data1 = data.iloc[12:, :5]
    data2 = data1.reset_index(drop=True)

    #0列目が初めてNaNになるインデックスを取得する
    nan_idx = data2[data2.iloc[:, 0].isnull()==True].index.tolist()

    #nan_idxの１つめより下の行をスライスで除去
    data3 = data2.iloc[:nan_idx[0], :]

    #カラムに入れたい0番目の値をリストに格納し、カラムとして定義する。
    cols = data3.iloc[0, :].tolist()
    data3.columns = cols

    #0番目の行は不要なのでスライスで除去
    data4 = data3.iloc[1:, :]

    #購入日の取得
    buy_date = data.iloc[0, 6]

    #仕入先の取得
    supplier = data.iloc[2, 0]

    #データフレームへの追加
    data4['購入日'] = buy_date
    data4['仕入先'] = supplier

    #並べ替えの方法はいくつかあるが、ここでは購入日より右、左でデータフレームを分割し、再度結合する方法をとる。
    data4_r = data4.iloc[:, 5:]
    data4_l = data4.iloc[:, :5]

    #データフレームの結合
    data5 = pd.concat([data4_r, data4_l], axis=1)

    return data5

#パスを指定して実行
file_path = 'mitumori_ex1.xlsx'
df = make_df(file_path)
df

図７と同じデータフレームができたと思います。（パスは適するものに適宜変えてください。）

次回予告

すべてがこのファイルのように単純であればいいのに…
その時私は何をすべきか、何を思ったら良いのか？誰に怒りをぶつけたら良いのか？
次回、振り返りたくない過去、落とし穴