CODEWITHSUNDEEP
python
xopherwwl
xopherwwl

Reputation: 39

Get a yearly averaged value

Get a yearly averaged value. Hi all!

So i have this data with date, no. of crimes, average housing price and etc as shown below

date        average_price   no_of_crimes
1995-01-01  133025          0.0 
1995-02-01  131468          0.0 
1995-03-01  132260          0.0 
1995-04-01  133370          0.0 
1995-05-01  133911          0.0 
... ... ... ... ... ... ... ...
2019-09-01  925955          6384.0  
2019-10-01  927864          7208.0  
2019-11-01  955615          6843.0  
2019-12-01  968404          7461.0  1
2020-01-01  1019028         6405.0  1

from here, how do I average the no_of_crimes or average_price into yearly. like from 1995-01-01 to 1995-12-01 the mean for crimes, and then proceed 1996-01-01 to 1996-12-01 and so on?

Upvotes: 0

Views: 33

Answers (1)

r-beginners
r-beginners

Reputation: 35155

The date column is set to DatetimeIndex, and a new year column is added to sum up the data.

import pandas as pd
import numpy as np

date = pd.to_datetime(pd.date_range('1995-01-01', '2020-01-01', freq='MS'))

ave_price = np.random.randint(1000,10000, (301,))
no_crimes = np.random.randint(100,1000, (301,))
df = pd.DataFrame({'average_price': ave_price, 'no_of_crimes': no_crimes}, index=date)

df_y = df.set_index([df.index.year, df.index])
df_y.index.names = ['year', 'date']

df_y = df_y.mean(level='year')

df_y
    average_price   no_of_crimes
 year       
1995    6168.833333 532.750000
1996    6637.666667 536.916667
1997    6022.750000 615.250000
1998    6317.750000 401.166667
1999    4840.166667 606.75000

Upvotes: 1

Related Questions