<jupyter_start><jupyter_text>Merging all MSOA data In this notebook, we merge the transient MSOA data to use in forecasting.This is the final version. Other versions will be removed.Features:1. Income2. Houseprice3. Electricity consumption4. Index of multiple deprivation decile5. Rural-urban classification (cat)6. EV count (transient) <- variable to predict7. Public EV charger count (transient)8. Private EV charger count (transient)9. Solar panel count (transient)10. Average EV price (transient)Time frame: Jan 2014 - April 2021 monthlyKeeping MSOA codes in England and Wales for which there are MOT centres (N_m = 5740)  Steady variablesLoad the steady features .csv and retain the steady features only. This data has been processed and only contains the MSOA regions with MOT centres. 1.-5. Income, Houseprice, Electricity Consumption, IMD, RUC<jupyter_code># load income and just keep most recent as we will not use this as a time-dependent variable
df_steady = pd.read_csv('data/msoa_today_steady.csv')
df_steady = df_steady[['MSOA11CD','total_annual_income_2019','houseprice_avg_2019',
                       'elec_consumption_2019', 'IMDRank','IMDDecil','RUC11']]
print(df_steady.shape)
df_steady.head()<jupyter_output>(5740, 7)<jupyter_text>Transient variablesWe will keep 2014-01 onwards.<jupyter_code>ordered_dates = pd.date_range("2014-01-01", periods=88, freq="MS")<jupyter_output><empty_output><jupyter_text>6. EV Count (target variable)First select MSOA that are consistent with steady features. Then select the MSOA in the remaining dataframe as the MSOA regions to keep throughout analysis.<jupyter_code>df1 = pd.read_csv('data/msoa_ev_count_dated.csv')
df1.set_index('msoa11cd', inplace=True)
for col in df1.columns:
    df1.rename(columns={col:col[:7]},inplace=True)
#df1 = df1[['index','2021-04']]
#df1.rename(columns={'index':'MSOA11CD', '2021-04':'ev_count_2021'}, inplace=True)
df1 = df1[df1.columns.sort_values()]
df1 = df1[df1.columns[-88:]]
df1.head()
for col_name in df1.columns:
    df1.rename(columns={col_name: pd.to_datetime(col_name + '-01')},inplace=True)
print(df_steady.shape)
print(df1.shape)
#Need to pad MSOAs with zero EVs
# drop msoa that aren't in steady
df1.reset_index(inplace=True)
# Pad missing MSOA regions
missing_msoa = list(set(df_steady.MSOA11CD.unique()) - set(df1.msoa11cd.unique()))

#df1.set_index(['msoa11cd'],inplace=True)
# store the dates
columns_names = df1.columns.values

# for each msoa add a row of zeros
df_missing_msoa = pd.DataFrame(columns=columns_names)
for i in range(len(missing_msoa)):
    df_missing_msoa = df_missing_msoa.append(pd.Series({"msoa11cd": missing_msoa[i],
                         columns_names[1]: 0}),ignore_index=True)
df_missing_msoa.fillna(0,inplace=True)

df1= df1.append(df_missing_msoa)
df1.reset_index(drop=True)

df1 = df1[df1['msoa11cd'].isin(df_steady.MSOA11CD.unique())]

print(df1.shape)
print(df1.msoa11cd.nunique())
# stack the data
df1.set_index('msoa11cd',inplace=True)
df1.stack().isna().sum()

df1.columns[-1]
df_full = pd.DataFrame(df1.stack(),columns=['ev_count'])
df_full
msoa_to_keep = df_full.index.droplevel(1).unique()<jupyter_output><empty_output><jupyter_text>Charger data7. Private charger data available from 2015 annually.8. Public charger data available from 10-2019 quarterly.<jupyter_code>df_private_chargers = pd.read_csv('data/private_chargers_msoa_timeseries.csv',index_col=0)
df_public_chargers = pd.read_csv('data/public_chargers_msoa_timeseries.csv',index_col=0)
df_private_chargers.drop(df_private_chargers[~df_private_chargers['MSOA11CD'].isin(np.array(msoa_to_keep))].index,inplace=True)
df_public_chargers.drop(df_public_chargers[~df_public_chargers['MSOA11CD'].isin(np.array(msoa_to_keep))].index,inplace=True)<jupyter_output><empty_output><jupyter_text>7. Public chargersDrop LSOA data, and extrapolate backwards<jupyter_code>df_public_chargers = df_public_chargers[['MSOA11CD','10-2019_msoa', '01-2020_msoa', '04-2020_msoa', '07-2020_msoa',
       '10-2020_msoa', '01-2021_msoa', '04-2021_msoa']]
df_public_chargers.rename(columns={'10-2019_msoa':pd.to_datetime('2019-10-01'),
                                   '01-2020_msoa':pd.to_datetime('2020-01-01'),
                                   '04-2020_msoa':pd.to_datetime('2020-04-01'),
                                   '07-2020_msoa':pd.to_datetime('2020-07-01'),
                                   '10-2020_msoa':pd.to_datetime('2020-10-01'), 
                                   '01-2021_msoa':pd.to_datetime('2021-01-01'),
                                   '04-2021_msoa':pd.to_datetime('2021-04-01')},inplace=True)
df_public_chargers.set_index('MSOA11CD',inplace=True)
df_public_chargers = func_linear_interp_dates(df_public_chargers, ordered_dates)
df_public_chargers.shape<jupyter_output><empty_output><jupyter_text>8. Private chargers<jupyter_code>df_private_chargers = df_private_chargers[['MSOA11CD', '2015_msoa', '2016_msoa', '2017_msoa',
       '2018_msoa', '2019_msoa', '2020_msoa', '2021_msoa']]
df_private_chargers.set_index('MSOA11CD',inplace=True)
df_private_chargers.rename(columns={'2015_msoa': pd.to_datetime('2015-01-01'),
                                    '2016_msoa':pd.to_datetime('2016-01-01'),
                                    '2017_msoa':pd.to_datetime('2017-01-01'),
                                    '2018_msoa':pd.to_datetime('2018-01-01'),
                                    '2019_msoa':pd.to_datetime('2019-01-01'),
                                    '2020_msoa':pd.to_datetime('2020-01-01'),
                                    '2021_msoa':pd.to_datetime('2021-01-01')},inplace=True)
df_private_chargers = func_linear_interp_dates(df_private_chargers, ordered_dates)
df_private_chargers.shape<jupyter_output><empty_output><jupyter_text>7. (T) PV count<jupyter_code>df_pv = pd.read_csv('data/pv_msoa_fit_timeseries_2014.csv',index_col=0)
df_pv.reset_index(level=0, inplace=True)
df_pv.rename(columns={'MLSOA Code':'MSOA11CD'},inplace=True)
print(df_pv.shape)
df_pv.head()
# Retain only MSOAs with MOT centres
df_pv.drop(df_pv[~df_pv['MSOA11CD'].isin(np.array(df_steady.MSOA11CD.unique()))].index,inplace=True)
df_pv.shape
# Pad missing MSOA regions
missing_msoa = list(set(df_steady.MSOA11CD.unique()) - set(df_pv.MSOA11CD.unique()))

#df1.set_index(['msoa11cd'],inplace=True)
# store the dates
columns_names = df_pv.columns.values

# for each msoa add a row of zeros
df_missing_msoa = pd.DataFrame(columns=columns_names)
for i in range(len(missing_msoa)):
    df_missing_msoa = df_missing_msoa.append(pd.Series({"MSOA11CD": missing_msoa[i],
                         columns_names[1]: 0}),ignore_index=True)
df_missing_msoa.fillna(0,inplace=True)

df_pv= df_pv.append(df_missing_msoa)
df_pv.reset_index(drop=True)

df_pv = df_pv[df_pv['MSOA11CD'].isin(df_steady.MSOA11CD.unique())]

print(df_pv.shape)
print(df_pv.MSOA11CD.nunique())
# make columns timestamps, and extrapolate forward
df_pv.set_index(['MSOA11CD'],inplace=True)
for col_name in df_pv.columns:
    df_pv.rename(columns={col_name: pd.to_datetime(col_name + '-01')},inplace=True)
missing_dates = list(set(ordered_dates)-set(df1.columns))
len(missing_dates)
# Extrapolate forward missing current dates
df_pv = func_linear_interp_dates(df_pv, ordered_dates)
df_pv.shape<jupyter_output><empty_output><jupyter_text>10. EV price<jupyter_code>data_prices = {pd.to_datetime('2015-03-01'): [25278.29],
               pd.to_datetime('2015-09-01'): [25358.64],
               pd.to_datetime('2016-03-01'): [24005.53], 
               pd.to_datetime('2016-09-01'): [24232.42],
               pd.to_datetime('2017-03-01'): [29987.22],
               pd.to_datetime('2017-09-01'): [30312.54], 
               pd.to_datetime('2018-03-01'): [30201.62], 
               pd.to_datetime('2018-09-01'): [30570.0], 
               pd.to_datetime('2019-03-01'): [35588.92],
               pd.to_datetime('2019-09-01'): [35211.67],
               pd.to_datetime('2020-03-01'): [32866.25], 
               pd.to_datetime('2020-09-01'): [33102.64], 
               pd.to_datetime('2021-03-01'): [45427.67]}

df_ev_prices =  pd.DataFrame(data=data_prices)
df_ev_prices
df_ev_prices = func_linear_interp_dates(df_ev_prices, ordered_dates)
print(df_ev_prices.shape)<jupyter_output>(1, 88)<jupyter_text>Stack all the variables<jupyter_code># 1. steady
df_steady.reset_index(inplace=True)
df_steady.rename(columns={'msoa11cd':'MSOA11CD'},inplace=True)
df_steady.set_index('MSOA11CD',inplace=True)
df_steady.head()
msoa_index =  df_full.index.get_level_values('msoa11cd')
date_index = df_full.index.get_level_values(1)
# steady cars
df_full = pd.concat([df_full, df_steady.loc[msoa_index].set_index(df_full.index)], axis=1)
# 2. pv count
df_full = pd.concat([df_full, pd.DataFrame(df_pv.stack(),columns=['pv_count'])], axis=1)
# 3. public charger count
df_full = pd.concat([df_full, pd.DataFrame(df_public_chargers.stack(),columns=['public_charger_count'])], axis=1)
# 4. private charger count
df_full = pd.concat([df_full, pd.DataFrame(df_private_chargers.stack(),columns=['private_charger_count'])], axis=1)
# 5. ev price 
df_ev_prices_to_concat = df_ev_prices.transpose().rename(columns={'0':'ev_price'})
df_ev_prices_to_concat.columns = ["ev_price"]

#6. monthly count
# We also add a monthly count to capture temporal info
df_ev_prices_to_concat.insert(0, 'n_months', range(0, len(df_ev_prices_to_concat)))
df_ev_prices_to_concat = df_ev_prices_to_concat.loc[date_index].set_index(df_full.index)
df_full = pd.concat([df_full, df_ev_prices_to_concat], axis=1)
df_full.isna().sum().sum()
df_full.RUC11.nunique()
df_full.index.set_names(['MSOA11CD', 'date'], inplace=True)
df_full.head()
#df_full.to_csv('data/msoa_2014_2021_no_onehotencode.csv')<jupyter_output><empty_output><jupyter_text>One hot encode categorical variablesThe one categorical variable we have is the rural-ruban classification, of which there are 8 categories. We one hot encode and join with df_full.<jupyter_code>pd.get_dummies(df_full['RUC11'])
df_full = pd.concat([df_full, pd.get_dummies(df_full['RUC11'])],axis=1)
df_full.drop(columns=['RUC11'],inplace=True)
print(df_full.shape)
df_full.head()
#df_full.to_csv('data/msoa_full_updated_ev_count_onehotencode.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13857128896706733, 'fraction_numerical': 0.06840613700772012, 'mean_word_length': 5.016460905349795, 'pattern_counts': {'":': 2, '<': 40, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '622101', 'license': 'MIT', 'n_tokens_mistral': 4315, 'n_tokens_neox': 3844, 'n_words': 571, 'path': 'preprocessing/P8-merge_msoa_transient.ipynb', 'repo_name': 'ClimateSubak/EV-forecasting', 'size': '10233'}