Pythonで複数のExcelファイルを1つのファイルにまとめる。

Google ColaboratoryのPythonで複数のExcelを結合したい。

Google Colaboratoryとは？

Pandasライブラリとは？

今回用意したExcelファイル

２つのExcelファイルがあります。それぞれシート1枚目にしたの表データがあります。表の列のフォーマットは同じです。テストのため2ファイルだけ用意しましたがファイル数が増えても動くようにします。

サンプルExcelファイル1

商品番号	商品	(空列)	部材名	(空列)	部材番号	部材仕様	部材使用数	(空列)
1	くるま		たいや		0001	くろ	4個
(空行)
1			エンジン		0002	しるばー	1個
(空行)
1			ハンドル		0003	茶色	1個

サンプルExcelファイル2

商品番号	商品	(空列)	部材名	(空列)	部材番号	部材仕様	部材使用数	(空列)
2	バイク		バイクのたいや		0004	くろ	2個
(空行)
2			バイクのエンジン		0005	しるばー	1個
(空行)
2			バイクのハンドル		0006	茶色	1個

結果Excelファイル

上記のような複数のExcelファイルの表データを下記のように１つの表に結合して新規Excelファイルに出力します。

商品	部材名	部材仕様	部材使用数	ファイル名
くるま	たいや	くろ	4個	くるま.xlsx
くるま	エンジン	しるばー	1個	くるま.xlsx
くるま	ハンドル	茶色	1個	くるま.xlsx
バイク	バイクのたいや	くろ	2個	ばいく.xlsx
バイク	バイクのエンジン	しるばー	1個	ばいく.xlsx
バイク	バイクのハンドル	茶色	1個	ばいく.xlsx

今回作成したPythonスクリプト

from glob import glob
import pandas



# GoogleDriveのExcelファイルが保存されたフォルダからExcelファイルのパスを抽出する。
books = glob("drive/MyDrive/ForProgramTest/merge/*.*xls*")

# マージ用の空のデータフレームを用意する。
resultdf = pandas.DataFrame()

for book in books: 
  try:
    # excel開く
    df = pandas.read_excel(book,sheet_name=0,usecols="A:M")

    # 必要な列だけスライス表記で抽出します。
    df = df.iloc[:,[1,3,6,7]]

    # 空白行を削除します。1レコードに対してすべての列がNanの場合にその行を削除。
    df.dropna(axis="index",how='all',inplace=True)

    # 1列目の欠損値を穴埋めする。上の値で穴埋め
    df.fillna(method="ffill", inplace=True)
    
    # 新規列を挿入しファイル名列とするため、ファイル名の入った変数を渡す。
    df.insert(loc=len(df.columns),column="ファイル名",value=book)

    # マージ用データフレームに追記していく。
    resultdf = pandas.concat([df,resultdf])

  except PermissionError:
    print("アクセス権エラーの可能性があります。" , book)

  except Exception:
    print("何かのエラーが起きました。" , book)

# 結果を書き込むExcelファイルのパス
resultbook = "drive/MyDrive/ForProgramTest/merge/result/result.xlsx"

# マージ用データフレームをExcelへ書き込む
resultdf.to_excel(resultbook,sheet_name="result_ST",index=False,header=True)

Pythonスクリプト実行結果

ポイント解説

空白行などの余分な行を削除するには？

dropna()で空白セルがある行を削除できます。すべての列が空白の時とかひとつでも空白列があるとき調整できます。

# 空白行を削除します。1レコードに対してすべての列がNanの場合にその行を削除。
df.dropna(axis="index",how='all',inplace=True)

how=引数に設定できる値
all	行あるいは列のすべてのデータが空白（Nan）のとき。
any	行あるいは列にひとつでも空白（Nan）があるとき。

空白セルを上の値で埋めるには？

下図のB列の商品列は本来すべて「くるま」であるべきなのですが、その前提があるがゆえに3行目以降のB列の入力が省略されています。こういった空白セルの穴埋めはfillna()を使います。

fillnaの使用例	説明
df.fillna(method=’ffile’)	前方の値で穴埋めする。
df.fillna(method=’bfile’)	後方の値で穴埋めする。
df.fillna(0)	すべての空白セルに0を入力する。

# 1列目の欠損値を穴埋めする。上の値で穴埋め
df.fillna(method="ffill", inplace=True)

Pandas DataFrameを縦方向に連結するには？

縦に積み上げていく連結はconcat()を使います。データベースでいうUnionです。

# マージ用データフレームに追記していく。
resultdf = pandas.concat([df,resultdf])

Pandasの関連記事

▲Pandasの使い方についてまとめています。

▲PythonでExcelを扱うにはOpenPyXLライブラリを使う方法もあります。

まとめ

Pythonを学ぶなら？