PythonでExcelデータのマイニング

概要

私のところには、多数のExcel(xlsx)ファイルが存在します。
私が作成したものであったり、他の人が作成したものであったりします。
そして、それらを定期的に収集や集計をして、報告する必要があります。
毎回、いちいちファイルを開いて収集や集計するわけですが、
あるときふと、その手間が惜しくなり、pythonのopenpyxlを使って
効率的にデータ収集する方法を検討し、実装しました。
比較的うまく行ったので共有したいと思います。

環境

macbook
Python 3.5.1
openpyxl 2.5.3

やったこと

xlsxのデータマイニングをするpythonライブラリを比較したところ、
openpyxlが良いとのことなので使ってみた。

今回は、データマイニングが目的なので、
xlsxの作成や編集の機能ではなく、
xlsxファイルを開いたり、データを取り出す機能にフォーカスしたところ、
openpyxlが使いやすそうだったので選択肢ました。

構想

excelファイルには２通りの使い方があり、
１つは行と列がしっかりしているシンプルなもの、
もう１つは帳票のような複雑なもの。
前者は、openpyxlを使うまでもなく、直接pandasで読み込んだほうが良いと思うが、
後者は、行と列がしっかりしていないので、
データを取り出すには工夫が必要。

また、前者であっても、ヘッダーが複数行に分かれていて、
ピポッドテーブル的に使えないExcelも同様にうまくいかない。

従って、データマイニングの手順は以下とする。

キーワードが含まれるcellの位置を取得する。
そこから右下方向に次のキーワードを探しに行く。
最後のキーワードが見つかったら、そこからのオフセット位置のデータを取得する。

こうすることで、同じ列名があったとしても、
親の列名で絞り込んだ後に子の列名でデータを取得できる。
（行と列が逆で、親の行名で絞り込んだ後に子の行名を取得も可能）

使用したデータ

使用したデータは以下。
オープンデータでxlsxを検索して見つけた適当なもの。

行政保有データ(統計関連)の棚卸し結果
http://www.data.go.jp/data/dataset/cas_20180312_0001

使用例

親の行名：「管理職への任用状況等について」
子の行名：「業務統計」
取得したいデータ：業務統計の２つ右のセルデータ

まず、xlsxファイルを開く。

pyxl = MyOpenpyxl('tanaoroshikekka_toukei.xlsx')

次に、親の行名が含まれるセルを探す。
cell_parentには、親のセルが返る。
col_idxとrowにそれぞれ行番号と列番号が入る。

cell_parent = pyxl.get_cell('管理職への任用状況等について')
cell_parent.value, cell_parent.col_idx, cell_parent.row
('管理職への任用状況等について', 3, 12)

最後に、親のセルを起点（＝cell_from)に、子の行名が含まれるセルを探す。
今回はオフセットを指定しているので、業務統計セルのx方向（col）に２、y方向（row)に０のデータの値を返す。
valueに求めたい値が入っている。

cell_child = pyxl.get_cell('業務統計', cell_from=cell_parent, offset_x=2, offset_y=0)
cell_child.value, cell_child.col_idx, cell_child.row
('12～１月', 6, 12)

コード

import openpyxl as px

class MyOpenpyxl:

    def __init__(self, filename):
        self.wb = px.load_workbook(filename)
        self.ws = self.wb.active

    def get_cell(self, keyword, offset_x=0, offset_y=0, cell_from=None):
        try:
            min_col = cell_from.col_idx
            min_row = cell_from.row
        except AttributeError:
            min_col, min_row = 1, 1

        for col in self.ws.iter_cols(min_row=min_row, min_col=min_col,
                                     max_row=y+30, max_col=x+30):
            for cell in col:
                if cell.value != None and keyword in cell.value:
                    return self.ws.cell(row=cell.row + offset_y,
                                        column=cell.col_idx + offset_x)
        return None

まとめ

１つずつExcelファイルを開いていたときと比べて、
サクサクとデータ収集出来ました。
更にCLIコマンドのラッパーを作って、より便利なツールを作ってみたいと思います。