I have a dataframe with multiple columns including SerialNumber, DateTime, GPS latitude and GPS longitude. Background: Tractors marked with Serial numbers are working(driving) on fields, sending data about GPS latitude and longitude every few seconds. Since the data is coming in every few seconds the dataset is huge (few million rows).

EDIT: I want to group by my data on SerialNumber and DateTime. Furthermore i want to aggregate DateTime on lets say 5min, and other features like TotalWorkingHours and AverageFuelConsumption on sum/mean/max etc. The problem comes with aggregating GPS data. From GPSLatitude and GPS Longitude i created geometry column, which is GeoSeries, and i want to use geometry.apply(lambda x: x.centroid) for aggregating geometry after group by.

This is my code so far but it is not working:

geometry = [Point(xy) for xy in zip(trac_df.GpsLongitude, trac_df.GpsLatitude)]
crs = {'init': 'epsg:4326'}
gd_trac_df = gpd.GeoDataFrame(trac_df, crs=crs, geometry=geometry)

trac_agg = gd_trac_df.groupby(['SerialNumber', pd.Grouper(key='DateTime', freq='5min')]).agg({
             'geometry' :  'apply(lambda x: x.centroid)', #It obviously fails here
             #'GPSLongitude' : 'median',
             #'GPSLatitude' : 'medain',
             'TotalWorkingHours' : 'max',
             'Engine_rpm' : 'mean', 
             'EngineLoad' : 'mean', 
             'FuelConsumption_l_h' : 'mean',
             'SpeedGearbox_km_h' : 'mean',
             'SpeedRadar_km_h' : 'mean',
             'TempCoolant_C' : 'mean',                                                                                                
             'PtoFront_rpm' : 'mean',                                                                                                 
             'PtoRear_rpm' : 'mean',
             'GearShift' : lambda x: ','.join(x.astype(str)), 
             'TempAmbient_C' : 'mean',
             'ParkingBreakStatus' : 'min',
             'DifferentialLockStatus' : 'max',
             'AllWheelDriveStatus' : lambda x: ','.join(x.astype(str)),
             'CreeperStatus' : lambda x: ','.join(x.astype(str))}).reindex(['TotalWorkingHours', 'geometry',
                      'Engine_rpm',  'EngineLoad', 'FuelConsumption_l_h', 'SpeedGearbox_km_h',
                        'SpeedRadar_km_h',  'TempCoolant_C', 'PtoFront_rpm',  'PtoRear_rpm',
                         'GearShift', 'TempAmbient_C', 'ParkingBreakStatus', 'DifferentialLockStatus',  
                         'AllWheelDriveStatus', 'CreeperStatus', 'geometry'], axis=1)

Any suggestions how to deal with aggregating geometry GeoSeries within group by?

EDIT #2: After using the code, i got the following message:

'SeriesGroupBy' object has no attribute 'apply(lambda x: x.centroid)'
  • Can you share the data – BERA Jul 12 '20 at 18:05

I would suggest using the centroid instead of averaging the coordinates values. Something like this should do the trick:

centroids = df.groupby('date')['geometry'].apply(lambda x: x.centroid)
  • Thank you but it doesn't work in my case. Please check the edit. – Kuki Jun 5 '20 at 12:59
  • Why is not working? Please add any error message that could be useful. – ramiroaznar Jun 5 '20 at 14:09
  • I made an edit, thank you – Kuki Jun 8 '20 at 6:30
  • 1
    Why are you using 'apply' as string? – ramiroaznar Jun 14 '20 at 11:44
  • Another option is to use a clustering algorithm - for example, DBSCAN ( scikit-learn.org/stable/modules/generated/… ) is often used to reduce the number of GPS points to improve accuracy. – Charlie Parr Mar 10 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.