Excelで人流データの差分を計算する

Excelを使って、「全国の人流オープンデータ（滞在人口1 kmメッシュデータ）」の差分を計算する方法を示します。

つまり、異なる年月の、同じメッシュの滞在人口を引き算する方法です。 この方法で作成したデータを元に統計地図を作成することで、滞在人口の変化を容易にを可視化できます。

「全国の人流オープンデータ」は、月ごとのデータが1つのCSVファイルとして提供されていますが、月によって収録されているデータの行数が異なります（滞在人口が10人未満のメッシュはデータが掲載されていません）。 したがって、２つのデータを並べて引き算しただけでは、正しい数字が得られません。

「全国のオープン人流データ」のファイル形式

「全国のオープン人流データ」のファイル形式を確認しておきます。 オープンデータ（人流データ）定義書によれば、1 kmメッシュデータのCSVファイルには、以下の図に示すデータが入っています。

国土交通省不動産・建設経済局（2021）『オープンデータ（人流データ）定義書』

提供されているCSVファイルは月毎のファイルに分かれていますが、データの中に「年」と「月」のデータ項目があることから、すべてのデータを縦に繋げて1つのファイルにしても、問題がない（重複なくデータを特定できる）ことがわかります。

以下では、ExcelのPower Query（パワークエリ）を使って、このCSVファイルに掲載されているデータを操作し、月毎の差分を計算する方法を紹介します。

データの準備

ダウンロードしたデータのディレクトリ構造は、以下のようになっていると思います。

40/
 ├─ 2019/
 │    ├─ 01/
 │    │    └─ monthly_mesh_population.zip
 │    ├─ 02/
 │    │    └─ monthly_mesh_population.zip
 │    └─ …（12まで）
 ├─ 2020/
 │    └─ 同様
 └─ 2021/
      └─ 同様

（必要な年・月の）zipファイルをその場所で展開します¹。 その結果、以下のようなディレクトリ構造になると思います。

40/
 ├─ 2019/
 │    ├─ 01/
 │    │    ├─ monthly_mdp_mesh1km.csv
 │    │    └─ monthly_mdp_mesh1km.csv.zip
 │    ├─ 02/
 │    │    ├─ monthly_mdp_mesh1km.csv
 │    │    └─ monthly_mesh_population.zip
 │    └─ …（12まで）
 ├─ 2020/
 │    └─ 同様
 └─ 2021/
      └─ 同様

Excel（Power Query）の操作

Windows

1. Excelで新しいファイルを開き、[データ]タブをクリックします。

2. リボンの左端にある[データの取得]をクリックし、続けて[ファイルから]➡️[フォルダーから]を選択します。

3. フォルダ選択の「参照」ウィンドウが開くので、「40」フォルダを選択し、[選択]ボタンをクリックします。

4. 「40」フォルダ以下にあるファイルのリストが表示されますので、[データの変換]をクリックします。

5. Power Queryエディターのウィンドウが開きます。表示されている内容は先ほどと同じで、「40」フォルダ以下にあるファイルのリストです。

6. 拡張子が「csv」のファイルだけを抽出します。 「Extension」列（左から3列目）の[▼]をクリックし、「.csv」だけにチェックが入った状態しにして、[OK]ボタンをクリックします。

7. 36個のCSVファイルのリストが表示されます。 Content列のアイコン（↓↓︎）をクリックして、36個のCSVファイルを結合します。

8. 「ファイルの結合」ウィンドウが表示されるので、[OK]ボタンをクリックします。

9. 下図のようなテーブルが表示されます。 後の処理を軽くするために、テーブルをシンプルにしておきます。 「Source.Name」「prefcode」「citycode」の3つの列を削除します（順番に、列名を右クリックして「列を削除」をを繰り返します）。

10. データのピボットを行います。 具体的には、「year」列のデータ（2019、2020、2021）をそれぞれ列名にして、列のデータにそれぞれの年の「population」列の数字が表示されるようにします。 まず、「変換」タブをクリックします。 次に「year」列を選択した状態で、[列のピボット]をクリックします。

11. 「列のピボット」というウィンドウが開きます。 [値列]のプルダウンメニューから「population」を選択して[OK]ボタンをクリックします（処理に少し時間がかかります）。

12. 「year」「population」の2列がなくなって、「2019」「2020」「2021」の3列が追加されているのを確認します。 [ホーム]タブをクリックし、[閉じて読み込む]をクリックすると、クエリをExcelに読み込みます。 そのままクエリの実行が完了するのを、気長に待ちます。

13. 「40」というシートが追加され、下図のようなデータが表示されます。 このExcelファイルに名前をつけて保存しましょう。

MacOS

1. Excelで新しいファイルを開き、[データ]タブをクリックします。

2. リボンの左端にある[データファイル指定（Power Query）]をクリックします。

3. 「データソースの選択」ウィンドウが開くので、[空のクエリ]をクリックします。

4. 「データソースへの接続」というウィンドウが表示されます。クエリの2行目を以下のように書き換えます（ダブルクォーテーションの中身は「40」フォルダの絶対パス²です。あなたPCの環境に合わせて書き換えてください）。正しく書き換えられたら、[次へ]ボタンをクリックします。

ソース = Folder.Files("/Users/kzktmr/Documents/GIS/Data/40/")

5. 「ファイルアクセスを許可」というウィンドウが開くので、[選択…]をクリックします。

6. フォルダ選択ウィンドウが開くので、「40」フォルダが選択されていることを確認して、[アクセス権を付与]をクリックします。

7. 「Power Queryエディター」ウィンドウが開きます。 「40」フォルダ以下にあるすべてのファイルがリスト表示されています。

8. 拡張子が「csv」のファイルだけを抽出します。 「Extension」列（左から3列目）の[▼]をクリックし、「.csv」だけにチェックが入った状態しにして、[OK]ボタンをクリックします。

9. 36個のCSVファイルのリストが表示されます。 Content列のアイコン（↓↓︎）をクリックします。

10. 「ファイルの結合」ウィンドウが表示されるので、[OK]ボタンをクリックします。

11. 下図のようなテーブルが表示されます。 後の処理を軽くするために、テーブルをシンプルにしておきます。 「Source.Name」「prefcode」「citycode」の3つの列を削除します（順番に、列名を右クリックして「列を削除」をを繰り返します）。

12. データのピボットを行います。 具体的には、「year」列のデータ（2019、2020、2021）をそれぞれ列名にして、列のデータにそれぞれの年の「population」列の数字が表示されるようにします。 まず、「変換」タブをクリックします。 次に「year」列を選択した状態で、[列のピボット]をクリックします。

13. 「列のピボット」というウィンドウが開きます。 [値列]のプルダウンメニューから「population」を選択して[OK]ボタンをクリックします。

14. 「year」「population」の2列がなくなって、「2019」「2020」「2021」の3列が追加されているのを確認します。 [ホーム]タブをクリックし、[閉じて読み込む]をクリックすると、クエリをExcelに読み込みます （「このクエリでは、Microsoft Excel で使用できる列は返されませんでした。」という小さなウィンドウが表示されますが、[OK]ボタンを押してウィンドウを閉じます）。そのままクエリの実行が完了するのを、しばらく待ちます。

15. 「クエリ」シートに、下図のようなデータが表示されます。 このExcelファイルに名前をつけて保存しましょう。

差分の計算とCSVファイルの書き出し

通常のエクセルの操作と同じです。 「2020-2019」「2021-2020」「2021-2019」の3つの列を追加して、それぞれの差分を計算してみましょう。

Excelのフィルター機能を使って、必要な行だけを表示します。 全てを選択しコピー、新しいExcelシートに貼り付けます（下図は「10月」「平日」「昼間」のデータだけを表示した例です）。 [ファイル]メニューから「名前を付けて保存」を選択します。保存場所を適切に設定し、ファイルの種類（ファイル形式）を「CSV UTF-8（コンマ区切り）」にして、[保存]ボタンをクリックします。

jSTAT MAPで表示

jSTAT MAPに読み込んで、統計地図を作成してみましょう。

脚注

1. WindowsのPower ShellやMacOSのターミナルを使って、すべてのZIPファイルを一括して展開することも可能ではありますが、やや複雑なのでここでは紹介しません。高々36個のファイルですので、1つずつマウスで選択して展開しましょう。また、Windowsの場合、ZIPファイルをダブルクリックしただけでは展開されませんので注意が必要です（MacOSの場合はダブルクリックだけでOK）。ZIPファイルをダブルクリックして表示されるCSVファイルをコピーして、ZIPファイルがあるフォルダに貼り付けると良いと思います。

2. 「40」フォルダの絶対パスを取得する方法はいくつかありますが、そのうちの1つを示します：(1)ファインダーでフォルダを右クリックし、コンテキストメニューを開く(2)オプション（⌥）キーを押すと、「コピー」が「“40”フォルダのパス名をコピー」に変わるので、これを選択。これでクリップボードに「40」フォルダの絶対パス名がコピーされます。