2

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)'
2
  • Have you tried 'geometry' : lambda x: x.centroid
    – underdark
    Aug 10, 2022 at 18:39
  • 1
    It's also worth noting that the centroid may not lie on the original GPS track, especially if the track is curved. The centroid is also susceptible to outliers from GPS noise. Your initial approach using coordinate medians would be less susceptible. Of course, this can still produce points that are not on the original track since lat and lon are treated independently.
    – underdark
    Aug 10, 2022 at 18:44

2 Answers 2

0

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)
5
  • Thank you but it doesn't work in my case. Please check the edit.
    – KayEss
    Jun 5, 2020 at 12:59
  • Why is not working? Please add any error message that could be useful. Jun 5, 2020 at 14:09
  • I made an edit, thank you
    – KayEss
    Jun 8, 2020 at 6:30
  • 1
    Why are you using 'apply' as string? Jun 14, 2020 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. Mar 10, 2021 at 16:20
0

For each group you can apply unary_union to create a multipoint and then calculate its centroid.

(I found the movement data for gps tracked birds here if anyone should want it, 1.1 GB).

import geopandas as gpd
import pandas as pd
import numpy as np
from shapely import unary_union

#Read the csv data
dtypes = {"event-id":int, "timestamp":object, "location-long":float, "location-lat":float,
          "tag-local-identifier":object}
df = pd.read_csv(filepath_or_buffer=r"/home/bera/Downloads/Migration of red-backed shrike populations (data from Pedersen et al. 2020).csv",
                 usecols=dtypes.keys(), dtype=dtypes)
df = df.dropna(axis=0)
df["timestamp"] = pd.to_datetime(df["timestamp"])

# df.head(1)
#   event-id    timestamp            location-long  location-lat    tag-local-identifier
#   16192498389 2011-06-29 20:30:00 -5.873595264          45.95511625   38


#Create a geodataframe from the dataframe
geometries = gpd.points_from_xy(x=df["location-long"], y=df["location-lat"], crs=4326)
df = gpd.GeoDataFrame(data=df, geometry=geometries, crs=4326)
df = df.drop(columns=["location-long","location-lat"])

df["randomint"] = np.random.randint(low=1, high=100, size=df.shape[0]) #Create a numeric column to sum in the grouping

#Group by week, sum randomint column, union the geometries to a multipoint and calculate its centroid
df_grouped = df.groupby(["tag-local-identifier", pd.Grouper(key="timestamp", freq="W")]).agg(
    {"randomint":"sum", "geometry": lambda x: unary_union(x).centroid})

df_grouped = gpd.GeoDataFrame(data=df_grouped, geometry=df_grouped.geometry, crs=4326) #The grouping is returning a dataframe

df.to_file(r"/home/bera/Downloads/df.gpkg")
df_grouped.to_file(r"/home/bera/Downloads/df_grouped.gpkg")

The tracks for three individuals:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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