CODEWITHSUNDEEP
pythonpandas
emax
emax

Reputation: 7245

Pandas: how to aggregate data weekly?

I have a pandas dataframe the looks like the following:

df
         date    lat         lon   val
0   2010-09-01  38.5437 -9.50659    6
1   2010-09-02  38.5437 -9.50659    3
2   2010-08-10  38.5437 -9.50659    1
3   2010-08-11  38.5437 -9.50659    5
4   2010-08-12  38.5437 -9.50659    6

I would like, for each values of lat and lon, to have the average value of val by week.

For instance I would like something like the following:

df
        month   week    lat      lon        val
0   2010-09      1     38.5437  -9.50659    4.5
4   2010-08      2     38.5437  -9.50659     4

This is what I am trying to do as a first step but I get an error

df = df.resample('W', on='date')['val'].mean().reset_index(drop=True)
DataError: No numeric types to aggregate

or

df =   df.groupby([['lat', 'lon'], pd.Grouper(key='date', freq='W-MON')])['val'].mean().reset_index()
ValueError: Grouper and axis must be same length

Upvotes: 1

Views: 916

Answers (1)

jezrael
jezrael

Reputation: 862581

Convert val to numeric first and then remove [] around 'lat', 'lon':

df['val'] = pd.to_numeric(df['val'])

df['date'] = pd.to_datetime(df['date'])

df = (df.groupby(['lat', 'lon', pd.Grouper(key='date', freq='W-MON')])['val']
        .mean()
        .reset_index())
print (df)
       lat      lon       date  val
0  38.5437 -9.50659 2010-08-16  4.0
1  38.5437 -9.50659 2010-09-06  4.5

If need month periods and week of year:

df = df.groupby([df['date'].dt.to_period('m').rename('month'), 
                 df['date'].dt.isocalendar().week.rename('week'),
                 'lat', 'lon'])['val'].mean().reset_index()
print (df)
     month  week      lat      lon  val
0  2010-08    32  38.5437 -9.50659  4.0
1  2010-09    35  38.5437 -9.50659  4.5

Upvotes: 4

Related Questions