デスクワークを超絶快適にするおすすめギアたち詳しく見る

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

この記事でわかること!

  • Google ColaboratoryでGoogleドライブ上の複数のExcelファイルを1つのファイルにする方法
  • Pandasを使った簡単なデータ加工の例
  • Pandasのconcat()で縦方向に連結する方法
目次

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

じょじお

Google Drive上の複数のExcelファイルをマージする例です。Google Colaboratoryを使ってPythonで結合します。

ぽこがみさま

PythonでExcelファイルを扱う方法はいくつかありますが、今回はPandasを使います。データの加工や分析に便利です。

Google Colaboratoryとは?

Pandasライブラリとは?

今回用意したExcelファイル

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

サンプルExcelファイル1

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

サンプルExcelファイル2

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

結果Excelファイル

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

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

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

じょじお

Google Colabに下記のコードを書きました。

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)
じょじお

事前にGoogle ColabにGoogle Driveをマウントしておく必要があります。マウント方法は下記の記事をご覧ください。

Pythonスクリプト実行結果

Google Colaboratory Pandas

ポイント解説

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

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

# 空白行を削除します。1レコードに対してすべての列がNanの場合にその行を削除。
df.dropna(axis="index",how='all',inplace=True)
how=引数に設定できる値
all行あるいは列のすべてのデータが空白(Nan)のとき。
any行あるいは列にひとつでも空白(Nan)があるとき。
Google Colaboratory Pandas

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

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

fillnaの使用例説明
df.fillna(method=’ffile’)前方の値で穴埋めする。
df.fillna(method=’bfile’)後方の値で穴埋めする。
df.fillna(0)すべての空白セルに0を入力する。
fillnaのメソッド
# 1列目の欠損値を穴埋めする。上の値で穴埋め
df.fillna(method="ffill", inplace=True)
Google Colaboratory Pandas

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

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

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

Pandasの関連記事

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

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

まとめ

じょじお

Python PandasでExcelをマージする方法について紹介しました。

Google関連のおすすめの本

▲Google Apps Scriptの入門書として間違いのない一冊です。ノンプログラマーの方にもわかりやすく解説されています。V8ランライム対応版にアップデート済みため情報も新しいです。

▲こちらGoogle Apps Scriptの本ではないですが、Google Workspace(旧G Suite)を自動化したりアプリ化するには、Google AppSheetという選択肢もあります。Google AppSheetはノーコードでアプリを作成できます。

▲Google for Educationの使い方にとどまらず実際の運用ででてくる問題への対処方法などもかかれていて面白いです。教師の方達の共著なので現実的な内容となっています。

Pythonを学ぶなら?

じょじお

社会人がPythonを学ぶならデイトラがコスパ最強です。

デイトラPythonコースのメリット
  • SNS(インスタ・Twitter・Youtube等)のAPIを駆使したマーケ特化のモダンなシステムを作りながら学べる。
  • 機械学習の基礎を実用的なシステムを作りながら学べる。
  • 現役エンジニアがメンター。1年間Slackで質問し放題。
  • SlackやTwitterで受講生の発信が盛んなのでひとりでの学習でもモチベ維持しやすい。
  • 月に2~4回行われるオンラインセミナー(現役のマーケター・フリーランサー・デザイナー社長・エンジニア等のWeb界隈のすごい人達が講師)が無料で受講可能。
メンターさんがとても親切でガチ中のガチな初心者でも質問しやすい環境でした。

この記事が気に入ったら
フォローしてね!

シェアしてくれたらウレシイ(·:゚д゚:·)
  • URLをコピーしました!
  • URLをコピーしました!
目次
閉じる