熱中症データベースに気象庁の日平均風速テーブルを追加する

 気象庁の過去の気象データ・ダウンロードからは膨大な気象データをダウンロードできる.今回の記事ではSQL Server内に構築した熱中症データベースに日平均風速のテーブルを追加する.

データのダウンロード

地点を選ぶ

 熱中症搬送人員数は各都道府県単位で記録されている.各都道府県を代表する日平均風速はどの気象観測所だろうか.おそらく県庁所在地であろう,と検討をつける.人口が最も多いと想定されるからである.

 「地点を選ぶ」では都道府県庁所在地の気象観測所にチェックを入れていく.

「地点を選ぶ」では都道府県庁所在の気象観測所にチェック
「地点を選ぶ」では都道府県庁所在の気象観測所にチェック

項目を選ぶ

 「項目を選ぶ」では日別値および日平均風速をチェックする.

「項目を選ぶ」では日別値および日平均風速をチェック
「項目を選ぶ」では日別値および日平均風速をチェック

期間を選ぶ

 「期間を選ぶ」では2008年から2022年にかけて4月1日から9月30日を選んでいく.

「期間を選ぶ」では2008年から2022年にかけて4月1日から9月30日を選ぶ
「期間を選ぶ」では2008年から2022年にかけて4月1日から9月30日を選ぶ

表示オプションを選ぶ

 「表示オプションを選ぶ」では「観測環境などの変化の前後で値が不均質となったデータの扱い」について「観測環境などの変化前の値を表示(格納)しない」にチェックする.「その他」で「都道府県名を格納」をチェックする.

「表示オプションを選ぶ」でのチェック項目
「表示オプションを選ぶ」でのチェック項目

ダウンロード

 選択済みのデータ量が100%を超えていないことを確認して「CSVファイルをダウンロード」する.

「選択済みのデータ量」が100%を超えていないことを確認してダウンロード
「選択済みのデータ量」が100%を超えていないことを確認してダウンロード

メモ帳での処理

 ダウンロードしたcsvファイルをメモ帳で開くとトップ2行にダウンロードした時刻が表示されている.不要なので削除し保存して閉じる.

EXCELでの処理

Power Queryでの処理

 複数ファイルからの一括インポートの際は「データの取得」「ファイルから」「フォルダーから」を選ぶ.

「データの取得」「ファイルから」「フォルダーから」
「データの取得」「ファイルから」「フォルダーから」

 ダウンロードファルダを選ぶと下図に遷移するため「データの変換」を選ぶ.

「データの変換」を選ぶ
「データの変換」を選ぶ

 Name列を右クリックして「テキストフィルター」「指定の値で始まる」を選ぶ.

Name列を右クリックして「テキストフィルター」「指定の値で始まる」を選ぶ
Name列を右クリックして「テキストフィルター」「指定の値で始まる」を選ぶ

 フィルター抽出条件にdataと入力する.

ここではdataと入力している
ここではdataと入力している

 Contentの右側のボタンを押下する.

Contentの右側のボタンを押下する
Contentの右側のボタンを押下する

 「ファイルの結合」画面に推移する.そのままOKする.

「ファイルの結合」画面に遷移する
「ファイルの結合」画面に遷移する

 必要なデータを赤枠で囲ってある.「列の削除」,「1行目をヘッダーとして使用」,「上位の行を削除」などを駆使してピボット解除の手前まで持っていく.

必要な項目を赤枠で囲ってある
必要な項目を赤枠で囲ってある

 さらにフィルターで他のファイルの上位にある数値以外の行を削除する.

フィルターで他のファイルの上位にある数値以外の行を削除
フィルターで他のファイルの上位にある数値以外の行を削除

 年月日列を選び,「その他の列のピボット解除」を行う.

年月日列を選び,「その他の列のピボット解除」
年月日列を選び,「その他の列のピボット解除」

 3列となるはずなので列名をそれぞれ「年月日」,「都道府県」,「平均風速」とする.

列名を記述し直す
列名を記述し直す

 年月日と平均風速のデータ型をそれぞれ「日付型」「10進数」に変更する.

 フィルターでnullを削除する.0で置換するか迷うところであるが,風速が0であることと,観測機器の故障などで値が不明の場合は別であることを考慮して削除する方針とした.

フィルターでnullを削除する
フィルターでnullを削除する

 閉じて読み込む.ファイル名をT_Wind.xlsxとして保存する.

都道府県コードを取得する

 都道府県コードはここにある.コピペでT_Wind.xlsxの新しいワークシートに貼り付け,整形してテーブル化する.列名をそれぞれPrefCode, Prefectureとする.

 都府県名から「県」「都」「府」を削除する.

 T_Wind.xlsxのDownloadsシートの列「年月日」と「都道府県」の間に列を一つ挿入し,列名を「都道府県コード」とし,直下のセルに以下の関数を記述する.

=XLOOKUP([@都道府県], テーブル2[Prefecture],テーブル2[PrefCode])

 都道府県コードが出現するはずである.

txtファイルにエクスポート

 別名で保存でタブ区切りテキストファイルに保存する.ファイル名はT_Wind.txtとなるはずである.

SQL Serverでの処理

ウィザードでインポート

 下図の列マッピングを参考にT_Wind.txtファイルをインポートする.

列マッピング
列マッピング

クエリ

 下記クエリを発行して結果をEXCELにコピペし,HeatStroke2.xlsxのファイル名で保存する.

USE HeatStrokeDB;
GO
SELECT	P.FiscalYear
,	T.年月日	AS Date
,	T.都道府県コード	AS PrefCode
,	T.都道府県	AS Pref
,	T.日最高気温	AS Temp
,	V.日平均蒸気圧	AS Vapor
,	W.平均風速	AS Wind
,	P.Population	AS Pop
,	H.[搬送人員(計)]	AS Num
FROM	dbo.T_HeatStroke	AS H
INNER	JOIN	dbo.T_MaxTemperature	AS T
ON	H.都道府県コード = T.都道府県コード
AND	H.日付 = T.年月日
INNER	JOIN	dbo.T_VaporPressure	AS V
ON	H.都道府県コード = V.都道府県コード
AND	H.日付 = V.年月日
INNER	JOIN	dbo.T_Wind	AS W
ON	H.都道府県コード = W.都道府県コード
AND	H.日付 = W.年月日
INNER	JOIN	dbo.T_Population AS P
ON	H.都道府県コード = P.PrefCode
AND	YEAR(H.日付) = P.FiscalYear
ORDER	BY PrefCode, Date
(70089 行処理されました)

まとめ

 気象庁のサイトから平均風速のデータをダウンロードし,HeatStrokeDBにテープルとしてインポートした.熱中症搬送人員数に風速が影響するか否かは別の機会に検討する.

“熱中症データベースに気象庁の日平均風速テーブルを追加する” への1件の返信

コメントを残す

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

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