CODEWITHSUNDEEP
pandasdatetime
r.xav
r.xav

Reputation: 23

Calculating daily means of different years (datetime)

I have the following data (small sample for simplicity):

           max
date              
2006-01-01  1448.0
2006-01-02  1264.0
2006-01-03   844.0
2006-01-04  1423.0
2006-01-05  1429.0
2007-01-01  1517.0
2007-01-02  1378.0
2007-01-03  1342.0
2007-01-04  1235.0
2007-01-05  1479.0

I'm trying to calculate the mean for each day, resulting in a dataframe containing an index column with MM-DD and the respective mean.

Thanks a lot, as I'm fairly new to pandas.

Upvotes: 0

Views: 82

Answers (2)

Nathan Furnal
Nathan Furnal

Reputation: 2410

Provided that the index is a datetime index, group by the day.

df.groupby(df.index.day)["max"].mean()

Upvotes: 2

cookesd
cookesd

Reputation: 1336

Convert your series to a dataframe and make the date column a datetime type if it's not already. Then you can make a column that contains the month and day for each row and do a grouped aggregation.

df = pd.DataFrame({'max': {'2006-01-01': 1448.0, '2006-01-02': 1264.0, '2006-01-03': 844.0, '2006-01-04': 1423.0, '2006-01-05': 1429.0, '2007-01-01': 1517.0, '2007-01-02': 1378.0, '2007-01-03': 1342.0, '2007-01-04': 1235.0, '2007-01-05': 1479.0}})

# Name the index and make it a separate column to perform operations
df.index.name = 'date'
df = df.reset_index()

# Make the date a datetime index
df['date'] = pd.to_datetime(df['date'])
# Make a column with just month and day
df['MM_DD'] = df['date'].dt.month.astype(str) + "_" + df['date'].dt.day.astype(str)

# conduct the grouped aggregation (mean)
df.groupby('MM_DD')['max'].agg('mean')

# MM_DD
# 1_1    1482.5
# 1_2    1321.0
# 1_3    1093.0
# 1_4    1329.0
# 1_5    1454.0
# Name: max, dtype: float64

Upvotes: 1

Related Questions