「空列だらけの巨大テーブル、どう読む？」— Python In Excel or PowerQueryで“意味ある列”だけ抽出するには

レガシーシステム刷新にあたり、業務パターンやユースケース毎に既存データがどのように埋まっているのか分析することがあります。

メインフレーム等のレガシーシステムにありがちなのですが、全てのデータパターンに対応できるように、やたらと項目数の多いデータがあります。数千、数百はざらです。これが非常にメンドクサイ。

どうメンドクサイかというと、業務パターンやユースケースによって、値の埋まる列が異なるため、特定の業務パターンやユースケースに着目してレコードを抽出すると、値の埋まっていない列だらけで、分析しようとすると、数千、数百の列のうち意味のある列を手作業で探す羽目になるので、とにかくメンドクサイです。

本記事では、そんな項目過多のデータ構造に立ち向かうために、Python In ExcelとPowerQueryによる抽出ロジックをご紹介します。

Python In ExcelとPowerQueryを選んだ理由：

• 業務用のPCにはExcelがインストールされていると思いますので、Excelでできることとしました。
• Excel VBAでもできますが、PythonやPowerQueryの方が簡潔に記述できるので。

空列を一掃し、特定の業務パターンやユースケースに必要な情報だけを残すことで、分析の視認性と効率性改善の一助になれば幸いです。

とここまで書いていて申し訳ないです。Python In Excelは実務に絶えなかったです。
サンプルだと問題なく動きますが、10万行×500列だとまったく応答なしで固まってしまいました。参考程度のコードとしてください。PoweerQueryは結果が返ってきます。

サンプルデータのテーブル

セル範囲A1:G4のテーブルです。ITEM1、ITEM3、ITEM5の列が空の列です。
データパターンを増やすために""空文字セルと全くの空っぽのセルを分けてデータを作っています。
緑色が""文字セル。黄色が全くの空セルです。
Python In ExcelとPowerQueryの""空文字と空の扱いは、次の通り

セルの値	Python In Excel	PowerQuery
""空文字	""	""
空っぽ	None	null

PowerQueryで値あり列のみだけのテーブルを作る

DropEmtpyColumns関数

まずは、値無し列を削除する関数DropEmtpyColumns関数です。

DropEmtpyColumns

(InputTable as table)=>
let
  // 空文字""もnullとみなし、空文字をnullに置き換えます。
  Changed = Table.ReplaceValue(InputTable, "", null, Replacer.ReplaceValue, Table.ColumnNames(InputTable)),

  // 列を削除した後の列の並び順が最初と変わってしまうのでもとに戻せるように覚えておく
  AllColumnNames = Table.ColumnNames(Changed),

  // ★今回の肝その1:
  // テーブルの全列の統計情報を取得します。その列のnull行数、その列の全行数などが取得できます。
  // これを使って、全行数 - null行数 = ゼロ を計算し、ゼロなら、その列は全部空白ということ。
  Profile = Table.Profile(Changed),

  // ★今回の肝その2:
  // 統計情報をメモリに展開することでカラムの遅延評価を強制的評価させて確定させています。
  Profile2 = Table.Buffer(Profile),

  // 統計情報から全行数 - null行数がゼロ超のカラム名を取得します。
  NonEmptyColumns = Table.SelectRows(Profile2, each [Count] - [NullCount] > 0)[Column],

  // InputTableから非null列だけを残します。
  DropEmptyColumns = Table.SelectColumns(InputTable, NonEmptyColumns),

  // 最初に覚えた列の並び順にします。MissingField.Ignoreをつけるのは、全null列が削除されているので、最初に覚えた列名が存在しないエラーを回避するため。
  // この列の並び替えは無駄かもしれない。この後いろいろ処理した後に最終的に欲しい列で並べるユースケースもあると思うので。
  Result = Table.SelectColumns(DropEmptyColumns, AllColumnNames, MissingField.Ignore)
in
  Result

結果はこんな感じです。

今回の肝その1

Profile = Table.Profile(Changed),

の部分が肝その1です。
Table.Profile関数はテーブルの全列の統計情報を取得します。統計情報には、その列のnull行数、その列の全行数などがあります。これを使って、全行数 - null行数 = ゼロ を計算し、ゼロなら、その列は全部空白ということになるので、ゼロ超の列を残せばよいという考え方です。

ちなみにTable.Profile関数の統計情報は次のようなイメージです。

実は、統計情報を使う案の前のバージョンは、次のようなコードで全行×全列を走査してnullを判定していたのですが、10万件オーダーの千数百項目のデータ分析でめちゃくちゃ遅くて使い物にならなかったので統計情報を使うようにしました。

List.Select(
    Table.ColumnNames(Source), // → 列ごとに
    (colName) =>
        List.AnyTrue(          // → その列の値リストに対して
            List.Transform(    // → 各行の値を評価（≠ null）
                Table.Column(Source, colName),
                each _ <> null
            )
        )
)

今回の肝その2

Profile2 = Table.Buffer(Profile),

の部分が肝その2です。Bufferせずに、

Table.SelectRows(Profile, each [Count] - [NullCount] > 0)[Column],

を実行すると、統計情報にCount列が見つからないエラーとなります。式の順序を変えて[NullCount]だけにしてもNullCountが見つからないエラーとなります。

公式ドキュメントで見つけられなかったのですが(わかる方コメントください)、PowerQueryは遅延評価を採用しているためではないかと推測しています。

Table.Profile関数は、入力テーブルの各列に対して統計情報のテーブルを生成しますが、その出力は動的に構成されるテーブルであり、列の存在や値が完全に確定していない状態で保持されているのではないかと考えています。

その結果、

Table.SelectRows(Profile, each [Count] - [NullCount] > 0)[Column],

は、まだ評価されていないProfileの行に対して演算を試みることになり、PowerQueryが「この列は存在するのか？」と判断できず、エラーになったのではないかと思います。
Profileを強制的に評価させればよいと考え、Table.Buffer関数を使ってメモリに展開させたところ、意図したとおり挙動しました。

強制的に評価させてしまえばよいので、Profileに[Count] - [NullCount]の計算結果列"NonNullCount"を追加しても、意図したとおり挙動します。こんな感じです。

Profile2 = Table.AddColumn(Profile, "NonNullCount", each [Count] - [NullCount], Int64.Type),

let
    ソース = Excel.CurrentWorkbook(){[Name="T_TEST_DROP_EMPTY_COLUMNS"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"注文ID", type text}, {"ITEM1_dte", type datetime}, {"ITEM2_dte", type datetime}, {"ITEM3_txt", type text}, {"ITEM4_txt", type text}, {"ITEM5_num", Int64.Type}, {"ITEM6_num", Int64.Type}}),
    // ここで今回の肝となる関数DropEmptyColumnsを呼び出して値あり列だけにします
    Result = DropEmptyColumns(変更された型)
in
    Result

Python In Excelで値あり列のみだけのテーブルを作る

最初、dropna関数で列を削除しようと考えたので、NaN値にreplaceしようとしたのですが、途中の計算でNaN値があると、Pyhton In ExcelだとセルにNaN値が入るようなことをすると、セルがExcel error: #NUM!エラーとなり、
その次のステップでdoprna関数を適用しようとすると、#NUM!エラーなのでNaN値と判定されず、列が削除されなかったです。

ですので、ここでは""空文字にreplaceしています。
あと、df_header = df_filled.iloc[0].valuesでヘッダー行を取得していますが、.valuesをつけないと、行インデクス列が左端についてしまうので、注意してください。

import pandas as pd

## input
df_input = xl("A1:G4")

## Noneを""空文字に片寄します 
df_filled = df_input.fillna("")

## ヘッダー行とデータ部分に分離
## valuesをつけないと行インデックス列が左端に勝手につくので注意。
df_header = df_filled.iloc[0].values
df_data = df_filled.iloc[1:]

## データを列方向(axis=0)に見て、値あり列、全て値無し列か判定
## TRUE:値あり列、FALSE:全て値無し列
keep_columns = ~(df_data.eq("")).all(axis=0)

## 値あり列だけ残す
df_masked = df_data.loc[:, keep_columns].values

## ヘッダーとデータを合成して返す
result = pd.DataFrame(df_masked, columns=df_header[keep_columns])

result

どこか適当なセルに=PY(上記のコード)を入力して、

「Excelの値(E)」を選べば、次のように値ありの列だけの表が返ってきます。