About data

JSON with a deep hierarchy from Splunk. It's easy to handle in the javascript world that retrieves using arrays, but it's almost a nightmare from Splunk.

List of prefecture names

sourcetype=toyo_json
| head 1
| spath prefectures-map{} output=prefectures_map
| stats count by prefectures_map
| spath input=prefectures_map
| table code ja en 
| sort code

I can still take it out obediently. Other data is quite difficult.

Here is a query that has the same form as using NHK data.

Daily statistics (cumulative)

Daily statistics(Cumulative value)

sourcetype=toyo_json
| head 1 |  rex "prefectures-data.*?(?<data>{.*?})"
| rex field=data "carriers.*?\[(?<carriers>\[.*?\])\]"
| rex max_match=0 field=carriers "(?<daily>\[[^\[\]]+\])"
| spath prefectures-map{} output=prefectures_map
| eval sorter=mvrange(0,mvcount(daily))
| eval daily=mvzip(sorter,daily)
| stats list(prefectures_map) as prefectures_map by daily
| mvexpand prefectures_map
| spath input=prefectures_map
| table daily code ja
| eval ja=code."_".ja
| xyseries daily ja code
| foreach *_* [ eval <<FIELD>> = mvindex(split(daily,","),'<<FIELD>>')]
| untable daily pref count
| eval daily=mvindex(split(daily,","),0)
| sort daily pref
| eval _time = if(daily=0,strptime("2020-03-11","%F"),relative_time(strptime("2020-03-11","%F"),("+".daily."d@d")))
| rex field=count "(?<count>\d+)"
| xyseries _time pref count
| rename COMMENT as "Here, it is created once in the field of prefecture name. Sorted from here in order from Hokkaido"
| rename COMMENT as "This is the base search for other panels "base1""

| transpose 0 header_field=_time column_name=pref
| sort pref
| eval pref = mvindex(split(pref,"_"),1)
| transpose 0 header_field=pref column_name=_time

Commentary

--Specify only the latest data with head 1 --In order to extract the number of infected people, prefectures-data is extracted once, and then carriers is extracted with a regular expression.
The data format is the first three dates, and the cumulative value data of prefectures.

data

[118, 0, 0, 1, 2, 0, 1, 0, 2, 1, 12, 25, 73, 43, 11, 0, 7, 0, 2, 3, 2, 2, 104, 2, 1, 15, 81, 35, 8, 14, 0, 0, 0, 1, 3, 1, 0, 2, 12, 3, 0, 0, 6, 1, 1, 0, 3]

--This data and the prefecture name cut out by spath are extracted by stats. --Since the date data is gone, I've added a number for sorting. --Extract only the required fields with stats --At this stage, pref is multivalue, so after mvexpand, it is converted to data of 47 prefectures with xyseries. .. ――Since pref is also JSON, I'll spath and number it for sorting later. --The shape is formed at the stage of xy series. However, the order is out of order ――Repeating transpose, the order is from Hokkaido. It will be the base query for the time being.

Explanation of dashboard components

From here, I will explain each panel of the dashboard
The base search is omitted.

Number of infected people and update date

Number of infected people and update date

    <panel>
      <title>last updated: $lastUpdate$</title>
      <single>
        <title>Number of infected people</title>
        <search base="base1" id="base2">
          <query>
| transpose 0 header_field=_time column_name=pref
| sort pref
| eval pref = mvindex(split(pref,"_"),1)
| transpose 0 header_field=pref column_name=_time
| rename COMMENT as "Time here+Table of prefecture names" 
| addtotals
| eventstats max(_time) as time
| eval time=strftime(time,"%F")
| fields _time Total time</query>
          <done>
            <set token="lastUpdate">$result.time$</set>
          </done>
        </search>
        <option name="drilldown">none</option>
      </single>

The value of Total is displayed I made another field to display time on the panel. When dealing with epoch time, use max`` min instead of ʻearliest`` latest` because it is stable. I checked it before.

Increasing rate of infected people

Increasing rate of infected people

      <table>
        <title>Increasing rate of infected people</title>
        <search base="base1">
          <query>| transpose 0 header_field=_time column_name=pref
| sort pref
| eval pref = mvindex(split(pref,"_"),1)
| rename COMMENT as "Up to this point, the prefecture name and daily cumulative data are created, and NHK's query can finally be used here."
| rename pref as _pref
| transpose 0 header_field=_pref column_name=_time
| 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)), round(first * 100.00,2), round((first -second) / second * 100,2))]
| eval daily_count = first - second
| sort - daily_incr
| fields - first second tmp
| rename _pref as "Prefectures", daily_incr as "Rate of increase", daily_count as "Number of people infected daily"
|table prefectures*Increasing rate of daily infections</query>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <format type="number" field="Rate of increase">
          <option name="unit">%</option>
        </format>
      </table>

Explanation of the rate of increase in the number of infected people

: sweat: I left a comment. For details, see Previous article. The display is beautiful because the Japanese field name and % are used as the unit.

Cholopreth Map

Choloprethmap

sourcetype=toyo_json
| head 1
| spath prefectures-map{} output=prefectures_map
| stats count by prefectures_map
| spath input=prefectures_map
| table code ja en value
| sort - value
| geom japansimple featureIdField=en
| fields - code ja

This can be displayed neatly because the data contains the names of prefectures in English and Japanese.

Daily Infection Rate

DailyInfectionRate

    <panel>
      <chart>
        <title>Daily infection rate</title>
        <search base="base2">
          <query>
| 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">600</option>
      </chart>
    </panel>

Daily infection rate displayed in Honke Basically, Area Chart overlays the value of Total Cases with Line Chart.

Bubble Chart

BubbleChart

      <chart>
        <title>Top 10 prefectures with daily infections * 2020/3/3 before 11/Interpolate with a number of 11</title>
        <search base="base1">
          <query>| transpose 0 header_field=_time column_name=pref
| sort pref
| eval pref = mvindex(split(pref,"_"),1)
| rename COMMENT as "Up to this point, the prefecture name and daily cumulative data are created, and NHK's query can finally be used here."
| 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
| rename COMMENT as "I have been doing this since the day when the first infected person came out."
| append [|makeresults
| eval _time = strptime("2020-01-16","%F")]
| makecontinuous _time span=1d
| reverse
| streamstats count(eval(isnull('Tokyo'))) as _count
| filldown
| foreach * [eval &lt;&lt;FIELD&gt;&gt; = if(_count=0,'&lt;&lt;FIELD&gt;&gt;', round('&lt;&lt;FIELD&gt;&gt;' / _count))]
| reverse
| rename COMMENT as "Blank is the first value, 2020/3/It is filled with the calculated value from the value of 11."
| 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 0 - 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="charting.legend.placement">bottom</option>
        <option name="height">600</option>
        <option name="refresh.display">progressbar</option>
      </chart>

Once created with only certain data, the feeling of sudden rise has disappeared. Therefore, we created a date from 2020/01/16 where the number of infected people was confirmed, and gradually lowered the blank from the latest value (not: sweat :). The size of the circle is the number of infected people per day.

Other

What I tried to make but stopped in terms of layout Area Chart

Top 10 prefectures in daily statistics(Cumulative value)

sourcetype=toyo_json
| head 1
| rex "prefectures-data.*?(?<data>{.*?})"
| rex field=data "carriers.*?\[(?<carriers>\[.*?\])\]"
| rex max_match=0 field=carriers "(?<daily>\[[^\[\]]+\])"
| spath prefectures-map{} output=prefectures_map
| stats count list(prefectures_map) as pref by daily
| eval date=mvindex(split(trim(daily,"[]"),","),0,2) , data=mvindex(split(trim(daily,"[]"),","),3,-1)
| eval _time=strptime(mvjoin(date,""),"%Y%m%d")
| mvexpand data
| table _time data count pref
| streamstats count  
| eval count = nullif(count % 47,0) | fillnull count value=47
| eval data=trim(data) ,pref=mvindex(pref,count-1)
| spath input=pref
| eval ja=code."_".ja
| table _time ja data
| sort 0 - _time data
| xyseries _time ja data
| rename COMMENT as "Here, it is created once in the field of prefecture name. Sorted from here in order from Hokkaido"
| transpose 0 header_field=_time column_name=pref
| sort pref
| eval pref = mvindex(split(pref,"_"),1)
| rename COMMENT as "Up to this point, the prefecture name and daily cumulative data are created, and NHK's query can finally be used here."
| rename pref as _pref
| foreach * [eval Check=max('<<FIELD>>')]
| sort 10 - Check
| fields - Check
| transpose 0 header_field=_pref column_name=_time
| sort _time

It would be nice to display it in Stack mode of the Area Chart.

Posted on GitHub

The directory structure is as follows

Covid19_in_japan

.
├── bin
│   ├── README
│   └── dl_toyo.py
├── default
│   ├── app.conf
│   └── data
│       └── ui
│           ├── nav
│           │   └── default.xml
│           └── views
│               └── README
├── local
│   ├── app.conf
│   ├── data
│   │   └── ui
│   │       └── views
│   │           └── covid19_in_japan.xml
│   ├── inputs.conf
│   ├── props.conf
│   └── transforms.conf
├── lookups
│   ├── japansimple
│   │   ├── grid.key
│   │   ├── grid.val
│   │   ├── ray.key
│   │   ├── ray.t.key
│   │   ├── ray.t.val
│   │   ├── ray.val
│   │   ├── seg.key
│   │   ├── seg.t.key
│   │   ├── seg.t.val
│   │   └── seg.val
│   └── japansimple.kml
└── metadata
    ├── default.meta
    └── local.meta

https://github.com/to4kawa/Covid19_in_japan I created a repository in and pushed all the files to create a README.md.

: sweat: I hope it moves without any problems.

Summary

I was able to do it for the time being. Create Apps and create a lookups folder

It should work when you place. In addition, please put japansimple.kml first.

As for JSON, I've been using Splunk so far, so it was pretty good, but I feel that it's easier to process it on the Python side.

I have included japansimple.kml for the one posted on GitHub. Since I am macosx, I am not working on Windows. At least the description in the directory in ʻinputs.conf` will need to be modified. : sweat: What should I do around here?