How to aggregate data according to week and get centroid of all points from that week?

Question

I have a dataframe that looks like this:

I want to extract data by aggregating the dates by week, and taking a sum of the observation counts like this:

pd.DataFrame(data.groupby([pd.Grouper(key='OBSERVATION DATE', freq='W')])['OBSERVATION COUNT'].sum())

In addition, I want to get a centroid of the latitudes and longitudes for that particular week. Is there any way to do this?

Answer 1

Since I didn't have a reproducible example of the data structure you're using, I just created my own to illustrate how to perform this operation.

If what you want are the coordinates (not the actual geometries), you can accomplish this very easily in Pandas. You'll only need GeoPandas if you actually want to generate geometries too.

It's also worth mentioning that the data you're working with has a column called COUNT, which might indicate number of observations at each row. If that's the case, you might want to weigh the results of the centroid coordinates using that COUNT variable. In my solution below, I show you how to generate both results.

# Importing the relevant libraries
import pandas as pd
import geopandas as gpd

# Creating my synthetic dataset
data = pd.DataFrame({'pt_id':[1,2,3,4,5,6],
                     'date':['2017-10-01',
                             '2017-10-02',
                             '2017-11-01',
                             '2017-11-02',
                             '2017-12-01',
                             '2017-12-02'],
                     'num_points':[100,200,
                                   200,500,
                                   300,100],
                     'lon':[15, 25, 37, 17, 21, 35],
                     'lat':[33, 22, 11, 12, 23, 31]})

# Transforming the "date" column to DateTime 
data['date'] = pd.to_datetime(data['date'])

# Creating temporary columns for the weight calculations
data['lon_weights'] = data['lon'] * data['num_points']
data['lat_weights'] = data['lat'] * data['num_points']

# Performing the actual grouping and main calculations
results = (data
           .groupby(pd.Grouper(key='date', 
                               freq='W'))
           # Choosing which functions get used on which columns
           .agg({'lon':'mean',
                 'lat':'mean',
                 'num_points':'sum',
                 'lon_weights':'sum',
                 'lat_weights':'sum'})
           # Renaming columns for clarity
           .rename(columns={'lon':'lon_simple_avg',
                            'lat':'lat_simple_avg',})
           # Removing the "date" column from the index and making it a 
           # regular column again
           .reset_index()
           # Creating two new columns that represent the weighted average 
           # using the "num_points" variable as weights
           .assign(lon_weighted = lambda x: x['lon_weights']/x['num_points'],
                   lat_weighted = lambda x: x['lon_weights']/x['num_points'])
           # Dropping the "weights" columns. Thy were only needed for the 
           # calculation of the weighted averages.
           .drop(columns=['lon_weights','lat_weights']))

# Generating a GeoDataFrame with the centroid points using the simple average
# of the original coordinates.
results_avg_geo = (gpd.GeoDataFrame(results
                                   .copy(), 
                                   # I'm just guessing the CRS here. You
                                   # might want to change this in yours
                                   crs='epsg:4326',
                                   geometry=gpd.points_from_xy(
                                       # Note that I'm using the 'simple 
                                       # average' columns here.
                                       results['lon_simple_avg'], 
                                       results['lat_simple_avg']))
                   # Dropping rows with NA geometries
                   .loc[results['lon_simple_avg'].notna()])

# Generating a GeoDataFrame with the centroid points using the weighted average
# of the original coordinates.
results_wgt_geo = (gpd.GeoDataFrame(results.copy(), 
                                   crs='epsg:4326',
                                   geometry=gpd.points_from_xy(
                                       # Note that I'm using the 'weighted 
                                       # average' columns here.
                                       results['lon_weighted'], 
                                       results['lat_weighted']))
                   # Dropping rows with NA geometries
                   .loc[results['lon_simple_avg'].notna()])

I hope the snippet above helps to illustrate what you need to do and it's clear how to adapt it to your own case.