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

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


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


## この記事の対象者
皆さんの貴重な時間を奪うのも申し訳ないので、予め宣言しておきます。
<dl>
  <dt>対象者</dt>
  <dd>これからエクセルからデータ抽出したい人。</dd>
  <dt>非対象者</dt>
  <dd>エクセルからのデータ抽出に辟易している人。（たぶんイライラを思い出させてしまう）</dd>
  <dd>エクセルからのデータ抽出の辛さを既に味わっている人。（たぶんイライラを思い出させてしまう）</dd>
  <dd>プロ中のプロ（退屈すると思う）</dd>
</dl>


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

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


#その１　pandasによるデータ抽出
これは基本ですね。
pandas エクセル　抽出　のキーワードで検索したらごまんと出てきますが、基本から見ていきましょう。
共通して使用する題材はこちら。⇒[サンプル](https://github.com/tutti-tsuyoshi/excel2df.git)
よくある見積書を参考に作成しました。

↓↓↓こんなの
![input_0.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/80ced88c-9f04-8ecc-112a-063de609d89a.jpeg)


```python:
#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も入ってない場合は入れてくださいね。

うまく行くと、こうなります。【図１】
![df_img1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/1685fb19-6e3d-e46e-b6e8-86dc80939e51.jpeg)

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

今回は、複数のこの様な見積書からいつどんな部品をいくつ購入したか、単価、値引き額、金額、納入先をまとめて一つの表を作り、エクセルに出力する、という仕事を与えられた想定で、話を進めます。
つまり、インプットと求められているアウトプットイメージは下記のとおりです。【図２】
（中身は超テキトーです。あくまで例として）
![output_img.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/4be46a79-d923-f80c-ab85-f058b6c63f29.jpeg)



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

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

#後で初めてNaNになるインデックスを取得して削除するため、インデックスを振り直す。
data2 = data1.reset_index(drop=True)
data2
```
こうすると、下記の様なデータフレームができます。【図３】
![df_img2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/aa6d28c2-42b7-5cea-c525-3d037a38260d.jpeg)

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

```python
#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)
```
できました。【図４】
![df_img3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/f05e88a5-90c3-2e43-6153-6a86cb03a846.jpeg)

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

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

#0番目の行は不要なのでスライスで除去
data4 = data3.iloc[1:, :]
data4
```
できました。【図５】
![df_img4.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/bd7691af-2806-8b9c-108d-dd116180e455.jpeg)

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

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

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

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

できました。【図６】
![df_img5.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/746f25b3-2547-ac9e-cd0b-8a859913d12e.jpeg)
ただ、これだと少し違和感があるので、購入日と仕入先を左へもっていきたいと思います。

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

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

できました。【図７】
![df_img6.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/640646/a4297c64-2fb9-2c1d-498e-5972d49305b3.jpeg)


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

```python
#ファイル読み込みからデータフレーム成型まで一連の処理を関数にする。
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

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

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