AnsweredAssumed Answered

How to downsample too much data?

Question asked by jpbarna on Jan 22, 2018
Latest reply on Jan 24, 2018 by fmpdude

Hi!

 

I have an Excel file that has sample data from a GPS. It tracks several variables that store 10 to 12 times per second. This is a lot of data for my needs, and I want to reduce the number of samples.

 

I have two questions:

 

  1. In a long list of records (one file has more than  60000 rows), how can I calculate the average of each second and get rid of the original data? I know how to calculate an average, for sure... but I'd like to do it in a efficient manner in FileMaker Pro for the sake of speed.
  2. Other way to eliminate data would be to keep the first sample for each second and delete the followers until the first one in the next second).

 

I did something calculating the seconds value of the timestamp (to detect the first integer second for each set of records) and then store the value in a variable looping down until the second value in the filed changes, store the value in the variable and repeating, but it was WAYYYY slow...

 

Any help, please?

 

Thanks!

 

JP

 

The Excel data (is CSV really) comes like this (this is for the first 2 seconds)

 

    

TimestampSecondsVelocityAccelerationOdometerLatitudeLongitudeHeart RateBody Load
19/01/2018 22:1700,01-0,170-38,0183607-57,5819600
19/01/2018 22:170,10,01-0,130-38,0183605-57,581959900
19/01/2018 22:170,20,01-0,10-38,0183607-57,581960200
19/01/2018 22:170,360,01-0,080-38,0183608-57,581960100
19/01/2018 22:170,490-0,050-38,018361-57,581960200
19/01/2018 22:170,60-0,040-38,0183612-57,581960200
19/01/2018 22:170,60-0,040-38,0183612-57,581960200
19/01/2018 22:170,70-0,030-38,0183611-57,581959900
19/01/2018 22:170,860-0,020-38,018361-57,581959400
19/01/2018 22:1710-0,010-38,018361-57,581958500
19/01/2018 22:1710-0,010-38,018361-57,581958500
19/01/2018 22:171,110-0,010-38,018361-57,581958200
19/01/2018 22:171,260-0,010-38,018361-57,581957900
19/01/2018 22:171,30-0,010-38,0183612-57,58195800
19/01/2018 22:171,4000-38,0183611-57,58195800
19/01/2018 22:171,51000-38,0183612-57,581958200
19/01/2018 22:171,67000-38,0183614-57,58195800
19/01/2018 22:171,7000-38,0183616-57,581957900
19/01/2018 22:171,8000-38,0183617-57,581957900
19/01/2018 22:171,9000-38,0183617-57,581957600
19/01/2018 22:172,07000-38,018362-57,581957700

Outcomes