I have data in the following format, measured in an interval of an hour.
Date | Restaurant id | Food Code | Average Order |
1/1/2017 0:00 | 101 | 1 | 0.004 |
1/1/2017 1:00 | 101 | 1 | 0.004 |
1/1/2017 2:00 | 101 | 1 | 0.004 |
1/1/2017 3:00 | 101 | 1 | 0.004 |
1/1/2017 4:00 | 101 | 1 | 0.003 |
1/1/2017 5:00 | 101 | 1 | 0.003 |
1/1/2017 6:00 | 101 | 1 | 0.003 |
1/1/2017 7:00 | 101 | 1 | 0.003 |
1/1/2017 8:00 | 101 | 1 | 0.004 |
1/1/2017 9:00 | 101 | 1 | 0.003 |
1/1/2017 10:00 | 101 | 1 | 0.004 |
1/1/2017 11:00 | 101 | 1 | 0.004 |
1/1/2017 12:00 | 101 | 1 | 0.004 |
1/1/2017 13:00 | 101 | 1 | 0.005 |
1/1/2017 14:00 | 101 | 1 | 0.006 |
1/1/2017 15:00 | 101 | 1 | 0.006 |
1/1/2017 16:00 | 101 | 1 | 0.006 |
1/1/2017 17:00 | 101 | 1 | 0.005 |
1/1/2017 18:00 | 101 | 1 | 0.005 |
1/1/2017 19:00 | 101 | 1 | 0.005 |
1/1/2017 20:00 | 101 | 1 | 0.004 |
1/1/2017 21:00 | 101 | 1 | 0.004 |
1/1/2017 22:00 | 101 | 1 | 0.004 |
1/1/2017 23:00 | 101 | 1 | 0.004 |
I am trying to merge each 24-hour event into a single event and take an average of the average order. The output should be like this:
Date | Restaurant id | Food Code | Average Order |
1/1/2017 | 101 | 1 | 0.003 |
I tried using a time chart, bin every time I get all 24 rows. Since I have data measured every hour which is too specific, I want to turn it to a day interval instead and take the average for the average order.
Help would be highly appreciated.
Probably means it isn't an epoch datetime anymore - treat it as a string
| eval date=mvindex(split(date," "),0)
Firstly - why do you want to have average from averages? Wouldn't average over the whole day make more sense? (it's not the same value!).
Secondly - there's not much point in manually binning over time if you can simply do a timechart.
Why not just
<<your initial search>> | timechart span=1d avg(order) by restaurant code
It worked but for the Date column, all the data appears to be 2017-01-01 00:00. The date should increment like
2017-01-01
2017-01-02
.....
| fieldformat date=strftime(date,"%Y-%m-%d")
The measurement date disappeared.
Probably means it isn't an epoch datetime anymore - treat it as a string
| eval date=mvindex(split(date," "),0)
This is the output is my command chained correctly?
Actually, the date should be Date but still, I am getting all the rows instead of 1 for 24-hour interval.
Your date is not in epoch datetime format - you need to convert it
| eval Date=strptime(Date,"%d/%m/%Y")
Assuming Date is an epoch datetime, e.g. _time
| bin Date span=1d
| stats avg("Average Order") as "Average Order" by Date "Restaurant id" "Food Code"