hey, I am trying to find Min, Max and average time. My time is saved hh:mm. Can I use the Statistic Calculator?
Best answer by mark2atsafe
View originalhey, I am trying to find Min, Max and average time. My time is saved hh:mm. Can I use the Statistic Calculator?
Best answer by mark2atsafe
View originalNice question. You can use the StatisticsCalculator to find the maximum and minimum time quite easily, by converting the times to FME time format first, using the DateTimeConverter:
Then apply the StatisticsCalculator and convert the results back using a second DateTimeConverter.
That will work fine for the minimum and maximum times.
However... that won't really work for average times. That's because hours have 60 minutes, not 100. For example if I have 13:45 and 14:25 then the average is 13:85. The DateTimeConverter can easily handle the overflow, but it returns 14:25 (because the StatsCalculator rolls over at 13.99, not 13.59).
So, if you really need averages, it'll need a bit more work. You'll need to use the DateTimeConverter to get the minutes only (%M) then convert them to a fraction of an hour with the ExpressionEvaluator. Then add it back to the hours (as a fraction) and use the StatisticsCalculator on that.
So 13:45 becomes 13+(45/60) = 13.75 and 14:25 becomes 14+(25/60) = 14.412, the mean of which is 14.081
Now you need to extract the .081, multiple by 60 to get minutes, and patch it back onto the hours, including rounding it off and adding leading zeros:
So the average = 14:(0.081*60) = 14:05
In short, max and min are very simple to do. Average is more complicated. Unless anyone else can think of a simpler method...
[Edit: DateTimeConverter, not DateTimeCalculator]
Hi @ryanhopkins,
I would expect the Statistics Calculator to work for Max and Min but not for Average because this needs to be calculated from a numeric value:
Please find attached a proposed alternative where you first convert the time to just minutes, perform the stats analysis, and then covert it back to the original format.
Hi @ryanhopkins,
I would expect the Statistics Calculator to work for Max and Min but not for Average because this needs to be calculated from a numeric value:
Please find attached a proposed alternative where you first convert the time to just minutes, perform the stats analysis, and then covert it back to the original format.
@Value(_list{0}):@PadLeft(@round(@Evaluate((0.@Value(_list{1})*60)),0),2,0)
Another thought. If you consider the time as a date/time in a specific day (e.g. 2018-10-10), you can convert them to the number of seconds since the epoch (1970-01-01 00:00:00) containing the same offset. You can then calculate the desired statistics with the StatisticsCalculater, then re-format the results to "%H:%M".
Assuming an attribute called "my_time" stores a time value with "%H:%M" format, this expression converts it to the number of seconds since the epoch.
@DateTimeFormat(@DateTimeParse(20181010@Value(my_time),%Y%m%d%H:%M),%Es)
After calculating statistics, this expression re-formats the results to "%H:%M".
@DateTimeFormat(@DateTimeParse(@CurrentAttribute(),%Es),%H:%M)
See here to learn more about FME Date/Time functions: Date/Time Functions
Just be aware that "median" is different from "average (mean)". Recommend you to make sure that which one (or both?) is required.
Another thought. If you consider the time as a date/time in a specific day (e.g. 2018-10-10), you can convert them to the number of seconds since the epoch (1970-01-01 00:00:00) containing the same offset. You can then calculate the desired statistics with the StatisticsCalculater, then re-format the results to "%H:%M".
Assuming an attribute called "my_time" stores a time value with "%H:%M" format, this expression converts it to the number of seconds since the epoch.
@DateTimeFormat(@DateTimeParse(20181010@Value(my_time),%Y%m%d%H:%M),%Es)
After calculating statistics, this expression re-formats the results to "%H:%M".
@DateTimeFormat(@DateTimeParse(@CurrentAttribute(),%Es),%H:%M)
See here to learn more about FME Date/Time functions: Date/Time Functions
Just be aware that "median" is different from "average (mean)". Recommend you to make sure that which one (or both?) is required.
Addition. This workflow is equivalent to above one.
Thanks for all your help so far. I was going to convert time to seconds ((hh* 3600) + (mm*60) + ss)
But i am having problems with the statistic calculator - which is meant to be the easy part.
Here is my dummy data. When i run it through the statistic calculator i get <missing> data back.
Thanks for all your help so far. I was going to convert time to seconds ((hh* 3600) + (mm*60) + ss)
But i am having problems with the statistic calculator - which is meant to be the easy part.
Here is my dummy data. When i run it through the statistic calculator i get <missing> data back.
Thanks for all your help so far. I was going to convert time to seconds ((hh* 3600) + (mm*60) + ss)
But i am having problems with the statistic calculator - which is meant to be the easy part.
Here is my dummy data. When i run it through the statistic calculator i get <missing> data back.
hey @takashi, please see attached image
Thanks for all your help so far. I was going to convert time to seconds ((hh* 3600) + (mm*60) + ss)
But i am having problems with the statistic calculator - which is meant to be the easy part.
Here is my dummy data. When i run it through the statistic calculator i get <missing> data back.
hey @takashi, please see attached image
The Excel file read into FME includes the headers "Group" and "Analyze"
Thanks for all your help so far. I was going to convert time to seconds ((hh* 3600) + (mm*60) + ss)
But i am having problems with the statistic calculator - which is meant to be the easy part.
Here is my dummy data. When i run it through the statistic calculator i get <missing> data back.
I tried this in 2019, 2018, and 2017.1.1.1 - the 2017 version I have is slightly newer than yours. I notice that in between your version and mine there was a fix to the StatisticsCalculator. It was for attribute names with spaces or "non-English" characters. I don't know that this is the problem here - and it seems unlikely - but you could try updating FME if nothing else helps.
Cheers
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.