Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@toshikawa(川俣 利久)

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

Last updated at Posted at 2020-04-10

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

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

なお

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

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

:smile: できました。

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

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_*

qiita_nichiji.png
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

qiita_single.png
データが累積数なので素直に縦横変換及び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

qiita_area.png
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

qiita_bubble.png
本家では死亡者数を円の大きさにしていた。
こちらでは一日の感染者数にしてみた。
なおマークは最小サイズ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

qiita_TDC.png

こちらも本家から
_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"

qiita_DIR.png
これも本家から。
世界が１０を切っているが、日本はまだまだ。
#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>

localhost_8000_ja-JP_app_covid19_japan_covid19japan.png

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

#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の作成方法は前回を参考にしてください。

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

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?