都道府県別の熱中症搬送人員数の予測と実際をEXCELの組み合わせグラフで描くでは独立変数として日最高気温,日平均水蒸気圧,65歳以上人口,人口密度を投入し都道府県別の熱中症搬送人員数を予測した.以前の記事ではe-Statからの社会疫学的指標を加えて熱中症搬送人員数を分析した.社会疫学的指標としては日最高気温,日平均水蒸気圧,都道府県人口に加えて過去30日間の平均気温,エアコン保有台数,年間収入のジニ係数,光熱・水道費,実収入,第1次産業就業者比率,第2次産業就業者比率,都市公園数,都市緑化割合,自然公園割合,自然公園数,生活保護被保護人員である.
今回は社会疫学的指標を独立変数として加えた熱中症搬送人員数の予測と実際を示す.
クエリ
SQL Serverで下記クエリを実行する.
USE HeatStrokeDB GO WITH CTE_MOVING_AVERAGE(date, pref, Avg30) AS( SELECT D.年月日 , D.都道府県 , AVG(D.日平均気温) OVER (PARTITION BY D.都道府県 ORDER BY D.年月日 ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS '移動平均30日間' FROM dbo.T_DailyAvgTemp AS D ) SELECT M.都道府県コード AS PrefCode , M.都道府県 AS Pref , M.年月日 AS Date , YEAR(M.年月日) AS YEAR , CAST(EXP(-22.2 + 0.3141 * M.日最高気温 - 0.1068 * CTE_MOVING_AVERAGE.Avg30 + 0.09617 * V.日平均蒸気圧 + LOG(P.総人口) + 0.0001203 * A.[ルームエアコン【台】] - 3.301 * G.年間収入のジニ係数 + 0.00009799 * I.[光熱・水道費【円】] - 0.000002910 * I.[実収入【円】] + 0.01777 * L.第1次産業就業者比率*100 - 0.005739 * L.第2次産業就業者比率*100 + 0.00002997 * PA.H9101_都市公園数 - 0.1554 * (PA.[H920502_都市緑地面積【ha】]/dbo.T_AREA.[B1102_総面積【ha】])*100 - 0.004013 * (CAST(NA.[B2101_自然公園面積【ha】] AS float) / CAST(dbo.T_AREA.[B1102_総面積【ha】] AS float))*100 + 0.02068 * NA.[B2102_都道府県立自然公園数【箇所】] - 0.0000005096 * W.[生活保護被保護実人員【人】] ) AS int) AS Predict , H.[搬送人員(計)] AS Real FROM dbo.T_MaxTemperature AS M INNER JOIN CTE_MOVING_AVERAGE ON CTE_MOVING_AVERAGE.date=M.年月日 AND CTE_MOVING_AVERAGE.pref=M.都道府県 INNER JOIN dbo.T_HeatStroke AS H ON M.都道府県コード = H.都道府県コード AND M.年月日 = H.日付 INNER JOIN dbo.T_VaporPressure AS V ON M.都道府県コード=V.都道府県コード AND M.年月日=V.年月日 INNER JOIN dbo.T_Population AS P ON M.都道府県コード = P.都道府県コード AND YEAR(M.年月日) = P.調査年 INNER JOIN dbo.T_AirCon AS A ON M.都道府県コード = LEFT(A.都道府県コード, 2) AND CASE WHEN YEAR(M.年月日) >= 2014 THEN 2014 ELSE CASE WHEN YEAR(M.年月日) >= 2009 THEN 2009 ELSE 2004 END END = A.調査年 INNER JOIN dbo.T_Gini AS G ON M.都道府県コード = LEFT(G.都道府県コード, 2) AND CASE WHEN YEAR(M.年月日) >= 2014 THEN 2014 ELSE 2009 END = G.調査年 INNER JOIN dbo.T_Income AS I ON M.都道府県コード = LEFT(I.都道府県コード, 2) AND CASE WHEN YEAR(M.年月日) >= 2014 THEN 2014 ELSE CASE WHEN YEAR(M.年月日) >= 2009 THEN 2009 ELSE 2004 END END = I.調査年 INNER JOIN dbo.T_LaborForce AS L ON CASE WHEN YEAR(M.年月日) >= 2020 THEN 2020 ELSE CASE WHEN YEAR(M.年月日) >= 2015 THEN 2015 ELSE CASE WHEN YEAR(M.年月日) >= 2010 THEN 2010 ELSE 2005 END END END = L.調査年 AND M.都道府県コード = LEFT(L.都道府県コード, 2) INNER JOIN dbo.T_PARKS AS PA ON M.都道府県コード = LEFT(PA.都道府県コード, 2) AND CASE WHEN YEAR(M.年月日) >= 2021 THEN 2021 ELSE YEAR(M.年月日) END = PA.調査年 INNER JOIN dbo.T_NaturePark AS NA ON CASE WHEN YEAR(M.年月日) >= 2021 THEN 2021 ELSE YEAR(M.年月日) END = NA.調査年 AND M.都道府県コード = LEFT(NA.都道府県コード, 2) INNER JOIN dbo.T_AREA ON M.都道府県コード = LEFT(dbo.T_AREA.都道府県コード, 2) AND CASE WHEN YEAR(M.年月日) >= 2021 THEN 2021 ELSE YEAR(M.年月日) END = dbo.T_AREA.調査年 INNER JOIN dbo.T_Wellfare AS W ON M.都道府県コード = LEFT(W.都道府県コード,2) AND YEAR(M.年月日) = W.調査年 ORDER BY M.都道府県コード, M.年月日
(71656 行処理されました)
EXCEL VBAでの処理
EXCELで下記プロシージャを実行する.
Sub makechart() Dim mySh1 As Worksheet Dim mySh2 As Worksheet Dim myLstObj As ListObject Dim h As Long Dim myRng1 As Range Dim i As Long Dim Cht As Chart Dim j As Long Dim myXValue() As Date Dim myValue1() As Integer Dim myValue2() As Integer Dim SeriesM As Series Dim SeriesR As Series Dim myPref As String Set mySh1 = Worksheets("Sheet1") Set myLstObj = mySh1.ListObjects(1) For h = 1 To 47 Set mySh2 = Worksheets.Add With mySh2 For i = 2008 To 2019 Set Cht = .Shapes.AddChart2(XlChartType:=xlColumnClustered, _ Left:=200 * ((i - 2008) Mod 4), _ Top:=200 * ((i - 2008) \ 4), _ Width:=200, _ Height:=200).Chart With myLstObj .Range.AutoFilter field:=1, Criteria1:=h .Range.AutoFilter field:=4, Criteria1:=i Set myRng1 = Intersect(.DataBodyRange, _ .Range.SpecialCells(xlCellTypeVisible)) myPref = myRng1.Cells(1, 2) For j = 0 To myRng1.Rows.Count - 1 ReDim Preserve myXValue(j) ReDim Preserve myValue1(j) ReDim Preserve myValue2(j) myXValue(j) = myRng1.Cells(j + 1, 3) myValue1(j) = myRng1.Cells(j + 1, 5) myValue2(j) = myRng1.Cells(j + 1, 6) Next j Set SeriesM = Cht.SeriesCollection.NewSeries With SeriesM .Name = "Model" .XValues = myXValue .Values = myValue1 .ChartType = xlLine .Format.Line.Weight = 1# '.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2 End With Set SeriesR = Cht.SeriesCollection.NewSeries With SeriesR .Name = "Real" .XValues = myXValue .Values = myValue2 .ChartType = xlColumnClustered End With .Range.AutoFilter field:=4 .Range.AutoFilter field:=1 End With With Cht .HasTitle = True .ChartTitle.Caption = i .ChartGroups(1).GapWidth = 0 End With Next i .Name = myPref End With Next h End Sub
結果
北海道
![北海道](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/3a9e55ab952008fc6b13462864613f48.png?resize=525%2C394&ssl=1)
青森
![青森](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/654c89d277051d23bb7ee45efb182be9.png?resize=525%2C394&ssl=1)
岩手
![岩手](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/8c4474389463795f2faab3efa2358bce.png?resize=525%2C394&ssl=1)
宮城
![宮城](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/9aafcf1219271e2b8dff14c5d810b54f.png?resize=525%2C394&ssl=1)
秋田
![秋田](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/77fc2a09bcb2d05ed96ca9dce1e6e75c.png?resize=525%2C394&ssl=1)
山形
![山形](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/11aa1a4d963550f26fbd694a613aa11b.png?resize=525%2C394&ssl=1)
福島
![福島](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/ac96cdb3b52efdc12768be7c9dbda77c.png?resize=525%2C394&ssl=1)
茨城
![茨城](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/8a59e4f4cced3ca3ca26a253de7103c0.png?resize=525%2C394&ssl=1)
栃木
![栃木](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/dc07e291fa6247879b8d20e0ae9346d9.png?resize=525%2C394&ssl=1)
群馬
![群馬](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/a20ad4271eba4ae24eb8cc8d6de0c56e.png?resize=525%2C394&ssl=1)
埼玉
![埼玉](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/e9cfbe6e83258607a630434fcce4aebb.png?resize=525%2C394&ssl=1)
千葉
![千葉](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/69f1c06312443a18027cea12789543ba.png?resize=525%2C394&ssl=1)
東京
![東京](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/cfa825b5955fad7fe5d791a4a69b8471.png?resize=525%2C394&ssl=1)
神奈川
![神奈川](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/8a825832525772133805789e41b06fc9.png?resize=525%2C394&ssl=1)
新潟
![新潟](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/f603f5dbdbcc5183ce252bec461a12bb.png?resize=525%2C394&ssl=1)
富山
![富山](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/0a0756ad661f42c54d428b9563740bf5.png?resize=525%2C394&ssl=1)
石川
![石川](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/487897c54fa5c1db2607ef29e679ddee.png?resize=525%2C394&ssl=1)
福井
![福井](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/17652e99adfd67db6d6938c1ca6ea32b.png?resize=525%2C394&ssl=1)
山梨
![山梨](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/3a477d23491ed0af4a4a006d72e16116.png?resize=525%2C394&ssl=1)
長野
![長野](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/e00cf6fef2648c91235e91fdcb9ae2fa.png?resize=525%2C394&ssl=1)
岐阜
![岐阜](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/629c8c973fb3ad1621fa5aa208dc3f05.png?resize=525%2C394&ssl=1)
静岡
![静岡](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/b22072fb3e1f77afdd4979975fcb58f0.png?resize=525%2C394&ssl=1)
愛知
![愛知](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/0b48fab835316a2e18f21db8f0bcf595.png?resize=525%2C394&ssl=1)
三重
![三重](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/9737b4c05bfee5bc57881530a5fcbd00.png?resize=525%2C394&ssl=1)
滋賀
![滋賀](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/b30e7045d15ac6ab7382244c179304b3.png?resize=525%2C394&ssl=1)
京都
![京都](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/a1cc635727a9a64c2437bb1e19aabced.png?resize=525%2C394&ssl=1)
大阪
![大阪](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/6564c3ad978d046dc006c88e6c77527d.png?resize=525%2C394&ssl=1)
兵庫
![兵庫](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/051c393d914c652ba7fea3eca2865044.png?resize=525%2C394&ssl=1)
奈良
![奈良](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/92277e4fd62061cd344a2296c89d46fc.png?resize=525%2C394&ssl=1)
和歌山
![和歌山](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/4d8803043766d659b4ca12902e4f3eb5.png?resize=525%2C394&ssl=1)
鳥取
![鳥取](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/9992170816e28a28558a12b3785db3ea.png?resize=525%2C394&ssl=1)
島根
![島根](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/d13d1a91fdbe7272701654a8f25c03ff.png?resize=525%2C394&ssl=1)
岡山
![岡山](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/e5417a12b0e46a856b2458f1a1afe281.png?resize=525%2C394&ssl=1)
広島
![広島](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/3a0c5b08b3d795076903e7b4f72cb19c.png?resize=525%2C394&ssl=1)
山口
![山口](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/f3350f1379d5c520b4b64dea1bfbd1b2.png?resize=525%2C394&ssl=1)
徳島
![徳島](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/f739f8398744cee1d6f3e6382da330ce.png?resize=525%2C394&ssl=1)
香川
![香川](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/2d7fc11859c58a260b60fabd2d66a947.png?resize=525%2C394&ssl=1)
愛媛
![愛媛](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/57cf521dfa442f0bced6579811e80fa7.png?resize=525%2C394&ssl=1)
高知
![高知](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/8fe04cf50108ddb6364ab7a4a70fed08.png?resize=525%2C394&ssl=1)
福岡
![福岡](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/803f82e3982a1a0abc8db46a48444675.png?resize=525%2C394&ssl=1)
佐賀
![佐賀](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/a9078d039da2df6ae9ff3aca9ed76ab7.png?resize=525%2C394&ssl=1)
長崎
![長崎](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/74981bfa9030bd35e11f4a672b3ef7c8.png?resize=525%2C394&ssl=1)
熊本
![熊本](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/9f9b6fc4ad0944c43a2da0dd7d4443f1.png?resize=525%2C394&ssl=1)
大分
![大分](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/d2db20ad7746d25b6829528556ba14e4.png?resize=525%2C394&ssl=1)
宮崎
![宮崎](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/3ad985b9efbb615f95edce1e0d89bb04.png?resize=525%2C394&ssl=1)
鹿児島
![鹿児島](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/56056dbd7ba638ee731813962bd73ad3.png?resize=525%2C394&ssl=1)
沖縄
![沖縄](https://i0.wp.com/www.muscle-hypertrophy.com/wp-content/uploads/2023/07/079897633b9c75f5db5fc7217e79eb68.png?resize=525%2C394&ssl=1)
まとめ
各都道府県の熱中症搬送人員数を社会疫学的指標をもとに予想し実際と比較した.予測精度はまだまだである.予測精度を評価する指標が必要である.