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)
