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

[Choropleth Mapで日本のCOVID19感染状況を表示する（改訂版）](https://qiita.com/toshikawa/items/d7c6f43016fd440508ed)ではindexに読み込んでいた。
データが一括更新なので、[Splunk本家](https://covid-19.splunkforgood.com/coronavirus__covid_19_)と同様にCSV読み込みに変更する

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

なお

- [NipponMap: Japanese Map Data and Functions](https://cran.r-project.org/web/packages/NipponMap/index.html)
- [東洋経済Online新型コロナウイルス国内感染の状況](https://toyokeizai.net/sp/visual/tko/covid19/)
- [NHK 新型コロナウイルス 特設サイト](https://www3.nhk.or.jp/news/special/coronavirus/data/)

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

#Appsの作成と準備

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

```bash: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](https://github.com/splunk/corona_virus)で作っていたので、同様に`mkdir`した。
後ほど、地図データ等を格納する。
#データの読み込み

```python: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')
```

なお、自分の環境は

```python: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`がないので、注意が必要
:thinking: `pandas`使わずにCSVにできるかな？

:smile: [できました。](https://qiita.com/toshikawa/items/d8f28af5a8d590a52a7b)

```zsh: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`におくことを想定
:sweat: `$SPLUNK_HOME`が設定されていなくて大変でした。

###inputs.conf
```configuration: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`は適当でいい模様

#日次統計
```perl:日次統計
| 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_*
```
![qiita_nichiji.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/4636369f-174f-8d81-e3b7-25c9f1085295.png)
CSV版で作っていなかった。

#Single Chart
```java: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
```
![qiita_single.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/8bf37b78-b5ba-4127-e752-9afee5bfbc20.png)
データが累積数なので素直に縦横変換及び`sort`で表示
`transpose`でできた余計なフィールドを`where`で削除している。


#Area Chart
```java: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
```
![qiita_area.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/3ca9c646-64d1-24b5-750d-adf8924b53dd.png)
`sort`を使用しないと４７都道府県表示になるので、上位１０都道府県の表示とした。

#Daily Infection Rate
```java: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% |
[本家](https://covid-19.splunkforgood.com/coronavirus__covid_19_)で出していたので。
計算式は　`当日-前日/前日*100`

#Bubble Chart
```java: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
```
![qiita_bubble.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/f1c4da3a-423d-6e24-7fa3-7ac3de1092f7.png)
本家では死亡者数を円の大きさにしていた。
こちらでは一日の感染者数にしてみた。
なお`マーク`は最小サイズ`5`最大サイズ`30`

#確認された症例の総数（日次感染率）
```java: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
```
![qiita_TDC.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/0e55ceb5-0efd-6481-6abd-0d3f14b5ec8f.png)

こちらも本家から
_Column Chart_の`グラフのオーバーレイ`で`軸として表示`を_ON_にして二つのフィールドを表示している。
#Daily Infection Rate
```java: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"
```

![qiita_DIR.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/9f2e2fde-219f-6e73-c706-2b1fe8f459d4.png)
これも本家から。
世界が１０を切っているが、日本はまだまだ。
#dashboard
```xml: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>
```
![localhost_8000_ja-JP_app_covid19_japan_covid19japan.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/cf5dd2c9-4ad4-528f-c934-a0e69e39e1bd.png)

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

#Choropleth Map
```java: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`の作成方法は[前回](https://qiita.com/toshikawa/items/d7c6f43016fd440508ed)を参考にしてください。


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