Solved

min, max and median time


Badge

hey, I am trying to find Min, Max and average time. My time is saved hh:mm. Can I use the Statistic Calculator?

icon

Best answer by mark2atsafe 9 October 2018, 19:03

View original

14 replies

Userlevel 4
Badge +25

Nice 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]

Badge +2

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.

timestats.fmwt

Userlevel 4
Badge +25

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. 

timestats.fmwt

Good solution, but you'll need to pad the minutes where it is less than 10 (so 14:05 not 14:5). Just add it to the AttributeManager expression:

 

@Value(_list{0}):@PadLeft(@round(@Evaluate((0.@Value(_list{1})*60)),0),2,0)

You might also want to pad the hours too, if they were originally that way (so 01:45 & 02:25 average to 02:05)
Userlevel 2
Badge +17

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

0684Q00000ArKOeQAN.png

Just be aware that "median" is different from "average (mean)". Recommend you to make sure that which one (or both?) is required.

Userlevel 2
Badge +17

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

0684Q00000ArKOeQAN.png

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.

0684Q00000ArMOCQA3.png

 

 

Badge

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.

Userlevel 2
Badge +17

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.

Difficult to find any issue only seeing the screenshot of the table. Could you please post other images which illustrate how you have set the StatisticsCalculator parameters?

 

 

Badge

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
Userlevel 2
Badge +17

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.

Check if the attribute "Analyze" has a value immediately after reading. The field name in the previous screenshot was "Attribute to Analyze".

 

 

Badge

hey @takashi, please see attached image
apologies, they are both dummy data sets.

 

The Excel file read into FME includes the headers "Group" and "Analyze"
Badge
Check if the attribute "Analyze" has a value immediately after reading. The field name in the previous screenshot was "Attribute to Analyze".

 

 

I have attached the dummy files. statistic-calc-test.fmwtest.xlsx

 

 

Userlevel 2
Badge +17

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 got this result with FME 2017.1.2.1 and FME 2018.1.0.2.I remember that there was an issue on the Table View in a specific version of FME Data Inspector. Can you see the attribute values in the Feature Information window?

 

Userlevel 4
Badge +25
I have attached the dummy files. statistic-calc-test.fmwtest.xlsx

 

 

I don't get the same problem, but I do notice that you have two Excel readers in your workspace. It might be that you are reading the wrong file? Or that the second file is somehow interfering with the results of the first?

 

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.

 

 

Badge
I have attached the dummy files. statistic-calc-test.fmwtest.xlsx

 

 

I will try updating FME and get back to you.

 

Cheers

 

 

Reply