import pandas as pd from pandas import DataFrame import datetime data_boroughs = pd.read_csv("london_boroughs.csv") boroughs = DataFrame(data_boroughs, columns =["name"]) data = pd.read_csv("Average-prices-2017-02.csv") df = DataFrame(data, columns =["Date", "Region_Name", "Average_Price"]) df.Region_Name = df.Region_Name.replace("City of Westminster", "Westminster") df.Region_Name = df.Region_Name.replace("Kensington And Chelsea", "Kensington and Chelsea") df.Average_Price = df.Average_Price.astype(int) df.rename(columns={"Region_Name": "name"}, inplace=True) df = pd.merge(boroughs,df) df.Date = pd.to_datetime(df.Date) df.index = df.Date grouped=df.groupby([(df.index.year), (df.name)]).mean().astype(int) grouped = grouped.reset_index() grouped.rename(columns={"level_0": "date"}, inplace=True) grouped.to_csv("house_prices.csv", index = False)