topic Re: Charting series data in Dashboards & Visualizations

Charting series data

cmeo — Tue, 27 Jul 2010 12:47:55 GMT

This seems to be a variation on a common theme, but I'm having trouble nutting it out.

The data source CSV contains single lines each with a state, city, and 7-day forecast of min temperature, max temp, forecast description. Example:

loc_id,location,state,forecast_date,issue_date,issue_time,min_0,max_0,min_1,max_1,min_2, max_2,min_3,max_3,min_4,max_4,min_5,max_5,min_6,max_6,min_7,max_7,forecast_0,forecast_1, forecast_2,forecast_3,forecast_4,forecast_5,forecast_6,forecast_7

056037,Armidale,NSW,20100721,20100721,093748,,11,-3,12,-3,13,-3,14,,,,,,,,,Early frost. Mostly fine.,Early frost. Mostly sunny.,Early frost then fine.,Early frost then fine.,,,,,

069134,Batemans Bay,NSW,20100721,20100721,055926,,15,2,15,2,17,2,16,,,,,,,,,Fine. Partly cloudy.,Fine. Mostly sunny.,Mostly fine.,Fine.,,,,,

063005,Bathurst,NSW,20100721,20100721,055926,,11,,,,,,,,,,,,,,,Early frost. Mostly fine.,,,,,,,,

069139,Bega,NSW,20100721,20100721,055926,,15,,,,,,,,,,,,,,,Fine. Partly cloudy.,,,,,,,,

048013,Bourke,NSW,20100721,20100721,055926,,15,,,,,,,,,,,,,,,Fine. Partly cloudy.,,,,,,,,

Note that some values are empty.

What I want to do is filter by state and location and display today's forecast and also the 7 day forecast. E.g.

TODAY: min 14, max 21, cloudy Tomorrow: min 15, max 23, sunny [is there some way to work out what day of week the next 5 entries are?] min x, max x, etc ... ...

Charting min_[1-7], max_[1-7] and forecast_[1-7] is proving to be extremely tricky. I've had a look at the search commands and nothing seems to do what I want. It seems to me that what I need is a sort of 'reverse multikv' command to take a single line and split it into multiple events for charting. In addition this would need to make sure that items with same index, e.g. min_1, max_1 and forecast_1, stay together.

There must be another way but I don't see it.

Re: Charting series data

gkanapathy — Tue, 27 Jul 2010 13:20:49 GMT

try (version with only 3 days):

state=NSW location=Armidale 
| eval maxminfcst=split("0|".max_0."|".min_0."|".forecast_0.";".
                        "1|".max_1."|".min_1."|".forecast_1.";".
                        "2|".max_2."|".min_2."|".forecast_2.";".
                        "3|".max_3."|".min_3."|".forecast_3     ,
                     ";"
| mvexpand maxminfcst
| rex field=maxminfcst "(?<days>\d+)|(?<max>\d+)|(?<min>\d+)|(?<forecast>.*)"
| dayofweeknum=case(days=0,99, days=1,98, days>1,date_wday+days)
| lookup dayofweek_table dayofweeknum OUTPUT dayofweektext
| fields dayofweektext max min forecast

dayofweek_table would be a hypothetical lookup table that mapped day numbers to a name:

dayofweeknum,dayofweektext
0,Sunday
1,Monday
2,Tuesday
3,Wednesday
4,Thursday
5,Friday
6,Saturday
99,Today
98,Tomorrow

Re: Charting series data

cmeo — Wed, 28 Jul 2010 12:56:22 GMT

This is great, but the 'split' function is documented where exactly?

Re: Charting series data

cmeo — Wed, 28 Jul 2010 13:04:05 GMT

Or at any rate this usage of it. I've not seen that format to reference a field inline -- .fieldname. -- mentioned anywhere.

Re: Charting series data

cmeo — Wed, 28 Jul 2010 13:45:56 GMT

OK I see what this is doing. Mental!

Re: Charting series data

cmeo — Wed, 28 Jul 2010 13:53:44 GMT

The "|" need to be escaped in the rex or this doesn't work properly:
rex field=maxminfcst"(?\d+)|(?\d+)|(?\d+)|(?.*)"

Otherwise, very clever indeed.

Re: Charting series data

gkanapathy — Thu, 29 Jul 2010 01:16:46 GMT

okay, sorry. didn't actually test that, so yeah, possibly typos and mistakes like that in it.

Re: Charting series data

gkanapathy — Thu, 29 Jul 2010 01:18:19 GMT

yeah, and it's not split() function, it's really the . concatenation operator that's probably confusing.