180万件のデータをPower Queryで処理してEXCELがオーバーフローした話

 EXCEL のワークシートに格納できるレコード数は 1,048,576 行である.今回 e-Stat からダウンロードしたファイルをピボット解除したらその上限を超えてしまったのでその記事を書こう.

目次

全国の食料品の物価を知りたい

情報は総務省 e-Stat にある

 今回は小売物価統計調査 小売物価統計調査(動向編)のデータをダウンロードすることを目標とする.

データベースから csv ファイルをダウンロード

 「ダウンロード」ボタンをクリックすると設定画面が出てくるオプション画面であるが,注意点がいくつかある.

  • 「ダウンロード範囲」は「全データ」が最初からチェックされているが,変更しないこと
  • 「ファイル形式」は「CSV形式」が最初からチェックされているが,変更しないこと
  • 「ヘッダの出力」と「コードの出力」は「出力する」に最初からチェックが入っているが,変更しないこと
  • 「注釈を表示する」は外したほうがよい
  • 「桁区切りを使用しない」にチェックを入れること
ダウンロードのオプション
ダウンロードのオプション

どうしても退治できない DataFormat.Error

 ダウンロードしたファイルをそのまま EXCEL から Power Query で開こうとすると,下記のエラーが出て先に進まない.

 どうやら原因は数値の入るべきセルに *** とか … とか – などの文字列が入り込んでいるためらしい.

DataFormat.Errorのために先に進めない
DataFormat.Errorのために先に進めない

データクレンジング

発想を切り替えてテキストエディタで前処理

 Power Query にデータを食わせる前に下ごしらえが必要だ.かと言って EXCEL では余計な型の変換をしでかす可能性もある.ならばテキストエディタを使えばよいではないかと思いついて実行してみたら,上手く行った.

csvファイルを右クリックして「プログラムから開く」「メモ帳」
csvファイルを右クリックして「プログラムから開く」「メモ帳」

注釈行を削除

 上から 10 行を選択したところである.注釈に確かにエラー文字列の注記がある…

csvファイルをテキストエディタで開いたところ.エラー文字列に注意
csvファイルをテキストエディタで開いたところ.エラー文字列に注意

 「編集」メニューから「削除」する.

上から10行を選択して削除
上から10行を選択して削除

市区町村コードを削除するな!

 5 桁の数字が 11 行目にある.これは市区町村コードであり,後で使うので削除してはいけない.

メモ帳の「置換」で文字列を一括削除

 続いて「編集」メニューの「置換…」を選ぶ.

「編集」から「置換」
「編集」から「置換」

 先程注釈にあった ***, …, – の文字列をここで一括削除しておく.

検索文字列のアスタリスクはエスケープしなくてもよい
検索文字列のアスタリスクはエスケープしなくてもよい
ピリオドもエスケープしなくてよい
ピリオドもエスケープしなくてよい
半角ハイフンもエスケープする必要はない
半角ハイフンもエスケープする必要はない

新しいメモ帳に全部のデータをコピペして保存

 上記の作業を5つのファイル全てに施し,新しくメモ帳のファイルを作成して全データをコピペする.「名前を付けて保存」する.

名前を付けて保存
名前を付けて保存

EXCELでヘッダーを整形

テキストファイルウィザード

 EXCEL を起動する.「空白のブック」ではなく,「開く」から「参照」をクリックする.

「空白のブック」ではなく「開く」から「参照」をクリック
「空白のブック」ではなく「開く」から「参照」をクリック

 ファイルの種類を「テキストファイル」に切り替え,先程テキストエディタで保存したテキストファイルを指定する.

 テキストファイルウィザードが起動する.

区切り文字はカンマ

 テキストファイルウィザードは 3 段階で設定してファイルを開く仕様になっている.

 最初の設定は区切りの種類を指定する.

  • 「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」
  • 「スペースによって右または左に揃えられた固定長フィールドのデータ」

の二択であるが,前者をチェックする.「次へ」をクリックする.

データの区切り文字を指定する
データの区切り文字を指定する

 2 つ目はフィールドの区切り文字,つまりデリミタを選択する.「カンマ」にチェックを入れると,プレビューに縦線が入る.「次へ」をクリックする.

フィールドの区切り文字を特定する
フィールドの区切り文字を特定する

データ形式と削除する項目

 3 番目は列のデータ形式,つまりデータ型を指定する.また,ここで取り込まずに削除する列を指定する.

 削除する列は「表章項目コード」「表章項目」「時間軸(月)コード」「/地域」である.

データ形式,データ型の指定と削除する項目
データ形式,データ型の指定と削除する項目

 後でピボットの解除を行う列は「文字列」つまりテキスト型にする.理由は市区町村コードの先頭のゼロを消さないためである.「完了」をクリックする.

後でピボットの解除を行う列は「文字列」つまりテキスト型にする
後でピボットの解除を行う列は「文字列」つまりテキスト型にする

新しいヘッダー行を作る

1行挿入

 下図はファイルを開いたところである.1 行目のD 列から右側に市区町村コードがある.この市区町村コードを直下の市区町村名と一旦結合したい.

データファイルを開いたところ
データファイルを開いたところ

 3 行目を選択して「挿入」する.

2行目を右クリックして「挿入」
2行目を右クリックして「挿入」

「セルの書式設定」を「標準」に直しておく

 挿入した行をすべて選択して「書式設定」を開き,「表示形式」を「標準」に戻す.これは挿入した行の書式が文字列であり,そのままでは関数の再計算が行われないからである.

挿入した行をすべて選択して「書式設定」を開き,「表示形式」を「標準」に戻す
挿入した行をすべて選択して「書式設定」を開き,「表示形式」を「標準」に戻す

「市区町村コード」と「市区町村」を連結

 図では逆になっているが,「市区町村コード」「市区町村」の順に & で結合する.後の工程の Power Query で列の分割を行う.

図では市区町村コードが後になっているが,先にしたほうが良い
図では市区町村コードが後になっているが,先にしたほうが良い

コピーして「値の貼り付け」

 2 行目の A – C 列のタイトルはそのまま 3 行目にコピペする.

 数式の参照関係を解消するために新しくできたヘッダー行を選択してコピーし,そのまま「値の貼り付け」を行う.

「値の貼り付け」は数式の参照関係の解消のために行う
「値の貼り付け」は数式の参照関係の解消のために行う

参照元の2行を削除

 3 行目が新しいヘッダー行になったので元の 1-2 行目は不要となる.削除しよう.ここでファイルを保存して準備完了だ.

不要となった1-2行目を削除する
不要となった1-2行目を削除する

データの取り込み

Power Queryでデータクレンジングの続き

「データの取得」「ファイルから」「テキストまたはCSVから」

 「空白のブック」で新しくファイルを作成する.「データの取得」タブから「ファイルから」「テキストまたはCSVから」と進む.

「データの取得」「ファイルから」「テキストまたはCSVから」と進む
「データの取得」「ファイルから」「テキストまたはCSVから」と進む

「データの変換」をクリックすると Power Query へ

 「データの取り込み」でここまで編集してきたテキストファイルを指定してインポートする.

「データの取り込み」で個々まで編集してきたテキストファイルを指定
「データの取り込み」でここまで編集してきたテキストファイルを指定

 「データの変換」をクリックして Power Query エディターへ移行する.

「データの変換」をクリックしてPower Queryへ移行
「データの変換」をクリックしてPower Query エディターへ移行

 これが Power Query エディターの初期画面である.

Power Query エディターの初期画面
Power Query エディターの初期画面

選択した列をピボット解除 (Table.Unpivot)

 下図をよく見てほしい.4列目より右側の列はすべて「市区町村コード」と「市区町村」を結合したものと,「価格」との繰り返し構造になっている.ここをピボット解除していく.

 この繰り返し構造をほどいていくのがピボット解除である.言葉で説明してもよく分からない人が多いだろう.ここでは集約とは逆のプロセスを行っている.ピボットの逆だからピボット解除なのである.

 下の模式図は第一正規形(左)がピボットされてピボットテーブル(右)になる矢印と,逆向きの矢印を描いてある.

 ディメンションが 2 つなら二次元の平面上に描けるが,3 つの場合もありうる.その場合はピボットテーブルは三次元のキューブになる.四次元以上の多次元キューブも当然ありうる.

ピボット,ピボット解除,ピボットテーブル,第一正規系の関係.ピボットテーブルになるとメジャーは集約関数でのみ表現される
ピボット,ピボット解除,ピボットテーブル,第一正規系の関係.ピボットテーブルになるとメジャーは集約関数でのみ表現される

 「01100札幌市」の列をまず選択し,シフトキーを押しながら右矢印キーを押しっぱなしにすることで対象の列をすべて選択する.その状態で「選択した列のみをピボット解除」する.

対象の列をすべて選択した状態で「選択した列のみをピボット解除」
対象の列をすべて選択した状態で「選択した列のみをピボット解除」

 ピボット解除された状態.この時点でデータ数は当初の 32,000 件あまりから 180 万件あまりへと大幅に増える.

ピボット解除とは第一正規形である
ピボット解除された状態

「列の分割」とは配列型を原子値に戻すこと (Table.SplitColumn)

 「属性」ととりあえず列名のついた列は,元々「市区町村コード」と「市区町村」を結合したものであった.これを分割する作業をここで行う.これはリレーショナルデータベースにおいて配列型を原子値に戻すという,非常に重要な作業である.

 「市区町村コード」「市区町村」の列を選択して「列の分割」「文字数による分割」と進む.

「市区町村コード」「市区町村」の列を選択して「列の分割」「文字数による分割」
「市区町村コード」「市区町村」の列を選択して「列の分割」「文字数による分割」

 「文字数による列の分割」で文字数を 5 にする.市区町村コードを先にしておいたのがここで効いている.「分割」は「繰り返し」となっているが,正解は「できるだけ左側で 1 回」である.

「文字数による列の分割」で文字数を5にする
「文字数による列の分割」で文字数を5にする

 その結果,第一正規形が完成した.後は列名の修正だけだ.

列の分割により「市区町村コード」と「市区町村」が分割された
列の分割により「市区町村コード」と「市区町村」が分割された

列名を右クリックして「名前の変更…」(Table.RenameColumns)

 列名を右クリックして「名前の変更…」を選ぶ.

列名を右クリックして「名前入の変更...」
列名を右クリックして「名前の変更…」

 列名はそれぞれ「銘柄コード」「銘柄」「年月」「市区町村コード」「市区町村」「価格」となる.

「閉じて読み込む」が,失敗する

 「閉じて読み込む」.

「閉じて読み込む」
「閉じて読み込む」

 しかし,データ件数が多すぎてオーバーフローする.「キャンセル」をクリックして接続クエリを変更することにしよう.

オーバーフローしている
オーバーフローしている

クエリと接続

「読み込み先…」

 「クエリと接続」パネルに今の接続クエリを記述した箇所が表示されているので,右クリックして「読み込み先…」を選択する.

「クエリと接続」から「読み込み先...」
「クエリと接続」から「読み込み先…」

「接続の作成のみ」と「このデータをデータモデルに追加する」

 「データのインポート」は初期状態で下図のようになっている.

「データのインポート」の初期状態
「データのインポート」の初期状態

 「テーブル」に入ったチェックを「接続の作成のみ」に変更し,「このデータをデータモデルに追加する」のチェックをオンにする.

「データのインポート」で「接続の作成のみ」と「このデータをデータモデルに追加する」
「データのインポート」で「接続の作成のみ」と「このデータをデータモデルに追加する」

読み込みに成功

 OK をクリックすると接続に成功し,180 万件あまりのデータが読み込まれる.

接続に成功しデータが読み込まれた
接続に成功しデータが読み込まれた

 ファイルのプロパティを見てみると確かに大きなファイルサイズとなっている.

ファイルのプロパティからサイズを確認する
ファイルのプロパティからサイズを確認する

DAX

Power Query エディターの「詳細エディター」

 EXCEL に VBA, リレーショナルデータベースに SQL という言語があるように,Power Query にも言語がある.DAX と言われるものである.「詳細エディター」で確認できる.

Power Queryエディターの「詳細エディター」でDAX式を確認
Power Queryエディターの「詳細エディター」でDAXを確認

DAXを確認する

 前半は定義部分,後半は行った作業について記述してあるように見える.

 定義部分では列名とデータ型を定義し {} でくくってある.作業は Table.PromoteHeaders, Table.TransformColumnTypes, Table.Unpivot や Table.SplitColumn, Table.RenameColumns などのテーブルに対する関数で記述してある.

let
    ソース = Csv.Document(File.Contents("C:\Users\UserName\Dropbox\20190908BLOG\T_ConsumerPrices.txt"),[Delimiter="	", Columns=84, Encoding=932, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"銘柄(H27年基準) コード", Int64.Type}, {"銘柄(H27年基準)", type text}, {"時間軸(月)", type date}, {"01100札幌市", Int64.Type}, {"01202函館市", Int64.Type}, {"01204旭川市", Int64.Type}, {"02201青森市", Int64.Type}, {"03201盛岡市", Int64.Type}, {"04100仙台市", Int64.Type}, {"04202石巻市", Int64.Type}, {"05201秋田市", Int64.Type}, {"06201山形市", Int64.Type}, {"07201福島市", Int64.Type}, {"07203郡山市", Int64.Type}, {"08201水戸市", Int64.Type}, {"08202日立市", Int64.Type}, {"09201宇都宮市", Int64.Type}, {"09202足利市", Int64.Type}, {"10201前橋市", Int64.Type}, {"11100さいたま市", Int64.Type}, {"11202熊谷市", Int64.Type}, {"11203川口市", Int64.Type}, {"11208所沢市", Int64.Type}, {"12100千葉市", Int64.Type}, {"12212佐倉市", Int64.Type}, {"12227浦安市", Int64.Type}, {"13100特別区部", Int64.Type}, {"13201八王子市", Int64.Type}, {"13202立川市", Int64.Type}, {"13206府中市", Int64.Type}, {"14100横浜市", Int64.Type}, {"14130川崎市", Int64.Type}, {"14150相模原市", Int64.Type}, {"14201横須賀市", Int64.Type}, {"15100新潟市", Int64.Type}, {"15202長岡市", Int64.Type}, {"16201富山市", Int64.Type}, {"17201金沢市", Int64.Type}, {"18201福井市", Int64.Type}, {"19201甲府市", Int64.Type}, {"20201長野市", Int64.Type}, {"20202松本市", Int64.Type}, {"21201岐阜市", Int64.Type}, {"22100静岡市", Int64.Type}, {"22130浜松市", Int64.Type}, {"22210富士市", Int64.Type}, {"23100名古屋市", Int64.Type}, {"23201豊橋市", Int64.Type}, {"24201津市", Int64.Type}, {"24204松阪市", Int64.Type}, {"25201大津市", Int64.Type}, {"26100京都市", Int64.Type}, {"27100大阪市", Int64.Type}, {"27140堺市", Int64.Type}, {"27210枚方市", Int64.Type}, {"27227東大阪市", Int64.Type}, {"28100神戸市", Int64.Type}, {"28201姫路市", Int64.Type}, {"28204西宮市", Int64.Type}, {"28207伊丹市", Int64.Type}, {"29201奈良市", Int64.Type}, {"30201和歌山市", Int64.Type}, {"31201鳥取市", Int64.Type}, {"32201松江市", Int64.Type}, {"33100岡山市", Int64.Type}, {"34100広島市", Int64.Type}, {"34207福山市", Int64.Type}, {"35202宇部市", Int64.Type}, {"35203山口市", Int64.Type}, {"36201徳島市", Int64.Type}, {"37201高松市", Int64.Type}, {"38201松山市", Int64.Type}, {"38202今治市", Int64.Type}, {"39201高知市", Int64.Type}, {"40100北九州市", Int64.Type}, {"40130福岡市", Int64.Type}, {"41201佐賀市", Int64.Type}, {"42201長崎市", Int64.Type}, {"42202佐世保市", Int64.Type}, {"43100熊本市", Int64.Type}, {"44201大分市", Int64.Type}, {"45201宮崎市", Int64.Type}, {"46201鹿児島市", Int64.Type}, {"47201那覇市", Int64.Type}}),
    選択した列のみをピボット解除しました = Table.Unpivot(変更された型, {"01100札幌市", "01202函館市", "01204旭川市", "02201青森市", "03201盛岡市", "04100仙台市", "04202石巻市", "05201秋田市", "06201山形市", "07201福島市", "07203郡山市", "08201水戸市", "08202日立市", "09201宇都宮市", "09202足利市", "10201前橋市", "11100さいたま市", "11202熊谷市", "11203川口市", "11208所沢市", "12100千葉市", "12212佐倉市", "12227浦安市", "13100特別区部", "13201八王子市", "13202立川市", "13206府中市", "14100横浜市", "14130川崎市", "14150相模原市", "14201横須賀市", "15100新潟市", "15202長岡市", "16201富山市", "17201金沢市", "18201福井市", "19201甲府市", "20201長野市", "20202松本市", "21201岐阜市", "22100静岡市", "22130浜松市", "22210富士市", "23100名古屋市", "23201豊橋市", "24201津市", "24204松阪市", "25201大津市", "26100京都市", "27100大阪市", "27140堺市", "27210枚方市", "27227東大阪市", "28100神戸市", "28201姫路市", "28204西宮市", "28207伊丹市", "29201奈良市", "30201和歌山市", "31201鳥取市", "32201松江市", "33100岡山市", "34100広島市", "34207福山市", "35202宇部市", "35203山口市", "36201徳島市", "37201高松市", "38201松山市", "38202今治市", "39201高知市", "40100北九州市", "40130福岡市", "41201佐賀市", "42201長崎市", "42202佐世保市", "43100熊本市", "44201大分市", "45201宮崎市", "46201鹿児島市", "47201那覇市"}, "属性", "値"),
    位置によって分割された列 = Table.SplitColumn(選択した列のみをピボット解除しました, "属性", Splitter.SplitTextByRepeatedLengths(5), {"属性.1", "属性.2"}),
    #"名前が変更された列 " = Table.RenameColumns(位置によって分割された列,{{"属性.1", "市区町村コード"}, {"属性.2", "市区町村"}, {"値", "価格"}, {"銘柄(H27年基準) コード", "銘柄コード"}, {"銘柄(H27年基準)", "銘柄"}, {"時間軸(月)", "年月"}})
in
    #"名前が変更された列 "

まとめ

 総務省のデータベース e-Stat から小売物価統計調査 小売物価統計調査(動向編)のデータをダウンロードし,EXCEL のデータモデルにインポートした.

 集約されたデータファイルを第一正規形に変換するには Power Query のピボット解除が必須であった.

 ヘッダーの修正とデータの置換にテキストエディタが有用であった.

 テーブルに収まりきらないデータでもデータモデルに格納することで EXCEL で扱うことができた.

 このデータモデルからデータをどう取り出すかが今後の課題である.

“180万件のデータをPower Queryで処理してEXCELがオーバーフローした話” への1件の返信

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください