Splunkで日本のCOVID19感染状況を表示する（改訂２版）

Choropleth Mapで日本のCOVID19感染状況を表示する（改訂版）ではindexに読み込んでいた。
データが一括更新なので、Splunk本家と同様にCSV読み込みに変更する

表示データは2020/4/8ダッシュボード2020/4/10日次統計2020/4/14

なお

• NipponMap: Japanese Map Data and Functions
• 東洋経済Online新型コロナウイルス国内感染の状況
• NHK 新型コロナウイルス 特設サイト

のデータを使用しています。
作成している方々に感謝します。

Appsの作成と準備

Appsの作成

とりあえずはlookupや設定を分離したいのでAppsを作成する。

covid19_japan

.
├── bin
│   └── README
├── default
│   ├── app.conf
│   └── data
│       └── ui
│           ├── nav
│           │   └── default.xml
│           └── views
│               └── README
├── local
│   └── app.conf
├── lookups
└── metadata
    ├── default.meta
    └── local.meta

lookupsは本家Splunk corona_virusで作っていたので、同様にmkdirした。
後ほど、地図データ等を格納する。

データの読み込み

dl_json_to_csv.py

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

url = 'https://www3.nhk.or.jp/news/special/coronavirus/data/47patients-data.json'
import requests
import json
import pandas as pd
headers={'accept': 'application/json', 'content-type': 'application/json'}
response=requests.get(url,headers=headers)
json_obj = response.json()
data=pd.json_normalize(json_obj, record_path='data47')['data']
pref=pd.json_normalize(json_obj, record_path='data47')['name']
date=pd.json_normalize(json_obj, record_path='category')[0]
df=pd.DataFrame(list(data)
               ,index =pref
               ,columns=date)
df.index.name='pref'
df.to_csv('covid_japan.csv', encoding='UTF8')

なお、自分の環境は

env

Python 3.7.6 (default, Jan  8 2020, 13:42:34)
[Clang 4.0.1 (tags/RELEASE_401/final)] :: Anaconda, Inc. on darwin

requestsとpandasはインポート済
Splunkのpython3はpandasがないので、注意が必要
pandas使わずにCSVにできるかな？

できました。

dl_json.sh

cd $SPLUNK_HOME/etc/apps/covid19_japan/lookups
python ../bin/dl_json_to_csv.py

chmod 755 dl_json.shして$SPLUNK_HOME/etc/apps/{apps}/binにおくことを想定
$SPLUNK_HOMEが設定されていなくて大変でした。

inputs.conf

inputs.conf

[script://$SPLUNK_HOME/etc/apps/covid19_japan/bin/dl_json.sh]
python.version = python3
disabled = false
interval = 3600
index = main
sourcetype = nhk_update_corona

今のところの設定。apps/covid19_japan/default/に入れる。
きちんと動いているかは
index=_internal dl_json.shで検索してエラーが出ていなければ大丈夫
python.versionはとりあえず入れてみただけ
ここでのsourcetypeは適当でいい模様

日次統計

日次統計

| inputlookup covid_japan.csv
| rename pref as _pref
| foreach * [eval Check=max('<<FIELD>>')]
| sort 10 - Check
| fields - Check
| transpose 0 header_field=_pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| streamstats current=f last(*) as p_* window=2
| foreach p_* [ eval "<<MATCHSTR>>" = '<<MATCHSTR>>' - '<<FIELD>>'
| fillnull "<<MATCHSTR>>"]
| fields - p_*

CSV版で作っていなかった。

Single Chart

SingleChart.spl

| inputlookup covid_japan.csv
| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| addtotals
| sort _time
| where isnotnull(_time)
| table _time Total

データが累積数なので素直に縦横変換及びsortで表示
transposeでできた余計なフィールドをwhereで削除している。

Area Chart

AreaChart.spl

| inputlookup covid_japan.csv
| rename pref as _pref
| foreach * [eval Check=max('<<FIELD>>')]
| sort 10 - Check
| fields - Check
| transpose 0 header_field=_pref column_name=time
| eval _time=strptime(time,"%m/%d")
| eval time=strftime(_time,"%F")
| sort _time
| fields - _time

sortを使用しないと４７都道府県表示になるので、上位１０都道府県の表示とした。

Daily Infection Rate

DailyInfectionRate.spl

| inputlookup covid_japan.csv
|  transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| tail 2
| reverse
| eval _time=strftime(_time,"%F")
| transpose 0 header_field=_time column_name=_pref
| foreach * [eval tmp=mvappend(tmp,'<<FIELD>>'), first = max(tmp) , second = min(tmp)
| eval daily_incr = if(isnull(nullif(second,0)), first * 100.00, round((first -second) / second * 100,2))."%"]
| eval daily_count = first - second
| sort - daily_incr
| fields - first second tmp
| rename _pref as pref

pref	2020-04-07	2020-04-08	daily_count	daily_incr
香川県	2	3	1	50.00%
静岡県	22	30	8	36.36%
長野県	14	19	5	35.71%
宮崎県	12	16	4	33.33%
鹿児島県	3	4	1	33.33%
栃木県	20	26	6	30.00%
広島県	19	24	5	26.32%
神奈川県	289	356	67	23.18%
福島県	24	29	5	20.83%
石川県	55	66	11	20.00%
大分県	35	41	6	17.14%
岡山県	12	14	2	16.67%
山形県	19	22	3	15.79%
埼玉県	216	250	34	15.74%
沖縄県	34	39	5	14.71%
山梨県	22	25	3	13.64%
福岡県	199	224	25	12.56%
東京都	1194	1338	144	12.06%
岐阜県	69	77	8	11.59%
千葉県	291	324	33	11.34%

本家で出していたので。
計算式は　当日-前日/前日*100

Bubble Chart

bubblechart.spl

| inputlookup covid_japan.csv
| rename pref as _pref
| foreach * [eval Check=max('<<FIELD>>')]
| sort 10 - Check
| fields - Check
| transpose 0 header_field=_pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| untable _time pref count
| sort _time
| streamstats count as days by pref
| streamstats current=f max(count) as prev by pref
| eval daily_count = count - prev
| table pref days count daily_count
| sort - count

本家では死亡者数を円の大きさにしていた。
こちらでは一日の感染者数にしてみた。
なおマークは最小サイズ5最大サイズ30

確認された症例の総数（日次感染率）

TotalConfirmedCases.spl

| inputlookup covid_japan.csv
| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| addtotals
| streamstats count(_time) as days
| fields _time Total days
| rename "Total" as "Total Cases" 
| eval "Overall Infection Rate"='Total Cases'/days
| eventstats max("Overall Infection Rate") as "Maximum Infection Rate"
| rename "Total Cases" as "TotalCases"
| streamstats current=f window=2 last(TotalCases) as last
| rename TotalCases as "Total Cases"
| fields - last days

こちらも本家から
Column Chartのグラフのオーバーレイで軸として表示をONにして二つのフィールドを表示している。

Daily Infection Rate

DailyInfectionRate.spl

| inputlookup covid_japan.csv
| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| addtotals
| streamstats count(_time) as days
| fields _time Total days
| rename "Total" as "Total Cases" 
| eval "Overall Infection Rate"='Total Cases'/days
| eventstats max("Overall Infection Rate") as "Maximum Infection Rate"
| rename "Total Cases" as "TotalCases"
| streamstats current=f window=2 last(TotalCases) as last 
| eval perc_incr=((TotalCases-last)/last)*100 
| rename TotalCases as "Total Cases", perc_incr as "Daily Infection Rate" 
| fields - last 
| fields _time "Total Cases"  "Daily Infection Rate"

これも本家から。
世界が１０を切っているが、日本はまだまだ。

dashboard

covid19japan.xml

<dashboard>
  <label>COVID19JAPAN</label>
  <search id="baseSearch">
    <query>
      | inputlookup covid_japan.csv
    </query>
  </search>
  <row>
    <panel depends="$alwaysHideCSS$">
      <html>
     <style>
          #panelWithGridLayout1 {
             width: 35% !important;
          }
          #panelWithGridLayout1 .panel-element-row{
             display: grid;
           }
           #panel1 #singlevalue.viz-controller,
           #panel1 div.splunk-single div.ui-resizable{
             height: 230px !important;
           }
          #panelWithGridLayout2 {
             width: 35% !important;
          }
          #panelWithGridLayout2 .panel-element-row{
             display: grid;
           }


           #singleStacked1 .dashboard-element-body,
           #singleStacked2 .dashboard-element-body{
             width: 180% !important;
           }
           #areaChartPanel {
             width: 65% !important;
          }
            #bubbleChartPanel {
             width: 65% !important;
          }

     </style>
   </html>
    </panel>
  </row>
  <row>
    <panel id="panelWithGridLayout1">
      <single id="singleStacked1">
        <title>更新日</title>
        <search base="baseSearch">
          <query>
| transpose 0 header_field=pref column_name=time
| eval _time=strptime(time,"%m/%d")
| sort _time
| tail 1
| eval time=strftime(_time,"%F")
| fields time</query>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">none</option>
      </single>
      <single id="singleStacked2">
        <title>感染者数</title>
        <search base="baseSearch">
          <query>
| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| addtotals
| sort _time
| where isnotnull(_time)
| table _time Total</query>
        </search>
        <option name="drilldown">none</option>
      </single>
    </panel>
    <panel id="areaChartPanel">
      <chart>
        <title>感染者数上位１０都道府県累計感染者数</title>
        <search base="baseSearch">
          <query>| rename pref as _pref
| foreach * [eval Check=max('&lt;&lt;FIELD&gt;&gt;')]
| sort 10 - Check
| fields - Check
| transpose 0 header_field=_pref column_name=time
| eval _time=strptime(time,"%m/%d")
| eval time=strftime(_time,"%F")
| sort _time
| fields - _time</query>
        </search>
        <option name="charting.chart">area</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="charting.drilldown">none</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel id="panelWithGridLayout2">
      <table>
        <title>日次感染者数</title>
        <search base="baseSearch">
          <query>| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| tail 2
| reverse
| eval _time=strftime(_time,"%F")
| transpose 0 header_field=_time column_name=_pref
| foreach * [eval tmp=mvappend(tmp,'&lt;&lt;FIELD&gt;&gt;'), first = max(tmp) , second = min(tmp)
| eval daily_incr = if(isnull(nullif(second,0)), first * 100.00, round((first -second) / second * 100,2))."%"]
| sort - daily_incr
| fields - first second tmp
| rename _pref as pref</query>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
    <panel id="bubbleChartPanel">
      <chart>
        <title>感染者数上位１０都道府県日次感染者数</title>
        <search base="baseSearch">
          <query>| rename pref as _pref
| foreach * [eval Check=max('&lt;&lt;FIELD&gt;&gt;')]
| sort 10 - Check
| fields - Check
| transpose 0 header_field=_pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| untable _time pref count
| sort _time
| streamstats count as days by pref
| streamstats current=f max(count) as prev by pref
| eval daily_count = count - prev
| table pref days count daily_count
| sort - count</query>
        </search>
        <option name="charting.chart">bubble</option>
        <option name="charting.chart.bubbleMaximumSize">30</option>
        <option name="charting.chart.bubbleMinimumSize">5</option>
        <option name="charting.drilldown">none</option>
        <option name="height">400</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>確認された症例の総数（日次感染率）</title>
        <search base="baseSearch">
          <query>| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| addtotals
| streamstats count(_time) as days
| fields _time Total days
| rename "Total" as "Total Cases" 
| eval "Overall Infection Rate"='Total Cases'/days
| eventstats max("Overall Infection Rate") as "Maximum Infection Rate"
| rename "Total Cases" as "TotalCases"
| streamstats current=f window=2 last(TotalCases) as last
| rename TotalCases as "Total Cases"
| fields - last days</query>
        </search>
        <option name="charting.axisY2.enabled">1</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.overlayFields">"Maximum Infection Rate","Overall Infection Rate"</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.legend.placement">bottom</option>
        <option name="height">300</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
    <panel>
      <chart>
        <title>日次感染率</title>
        <search base="baseSearch">
          <query>
| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| addtotals
| streamstats count(_time) as days
| fields _time Total days
| rename "Total" as "Total Cases" 
| eval "Overall Infection Rate"='Total Cases'/days
| eventstats max("Overall Infection Rate") as "Maximum Infection Rate"
| rename "Total Cases" as "TotalCases"
| streamstats current=f window=2 last(TotalCases) as last 
| eval perc_incr=((TotalCases-last)/last)*100 
| rename TotalCases as "Total Cases", perc_incr as "Daily Infection Rate" 
| fields - last 
| fields _time "Total Cases"  "Daily Infection Rate"</query>
        </search>
        <option name="charting.axisY2.enabled">1</option>
        <option name="charting.chart">area</option>
        <option name="charting.chart.overlayFields">"Total Cases"</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.legend.placement">bottom</option>
        <option name="height">300</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <map>
        <title>県別感染状況</title>
        <search base="baseSearch">
          <query>| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| tail 1
| eval _time="count"
| transpose 0 header_field=_time column_name=pref
| lookup prefecture.csv Kanji as pref OUTPUT Prefecture as featureId
| fields - pref
| geom japansimple
| sort - count</query>
        </search>
        <option name="drilldown">none</option>
        <option name="mapping.choroplethLayer.colorMode">categorical</option>
        <option name="mapping.map.center">(38.17,131.29)</option>
        <option name="mapping.map.zoom">5</option>
        <option name="mapping.type">choropleth</option>
        <option name="height">500</option>
      </map>
    </panel>
  </row>
</dashboard>

こんな感じの表示になります。

Choropleth Map

ChoroplethMap.spl

| inputlookup covid_japan.csv
| transpose 0 header_field=pref column_name=_time
| eval _time=strptime(_time,"%m/%d")
| sort _time
| tail 1
| eval _time="count"
| transpose 0 header_field=_time column_name=pref
| lookup prefecture.csv Kanji as pref OUTPUT Prefecture as featureId
| fields - pref
| geom japansimple
| sort - count

prefecture.csvの作成方法は前回を参考にしてください。

まとめ

本家で作っているパネルを一通り作ってみた
バブルチャートの画面が結構ひどい結果なのがつらい。
ぜひ、試してみてください