<jupyter_start><jupyter_text>Import data for 2014The 2014 data is given as weekly income values for MSOA regions, need to scale down to monthly and scale up to LSOA regions.Assumptions: constant income such that we can take monthly = weekly * 4.345<jupyter_code>colnames = ['MSOA_code','MSOA_name','local_authority_code','local_authority_name',
            'region_code','region_name','total_weekly_income','upper_confidence_limit',
            'lower_confidence_limit','confidence_interval']

df_income_2014 = pd.read_csv('raw_data_to_edit/totalannualincome2014.csv', skiprows=5, names=colnames, header=None)
df_income_2014.head()<jupyter_output><empty_output><jupyter_text>Add monthly income column<jupyter_code>df_income_2014['total_monthly_income'] = df_income_2014['total_weekly_income'] * 4.345
df_income_2014['total_annual_income'] = df_income_2014['total_weekly_income'] * 52
df_income_2014.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 7202 entries, 0 to 7201
Data columns (total 12 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 0   MSOA_code               7202 non-null   object 
 1   MSOA_name               7201 non-null   object 
 2   local_authority_code    7201 non-null   object 
 3   local_authority_name    7201 non-null   object 
 4   region_code             7201 non-null   object 
 5   region_name             7201 non-null   object 
 6   total_weekly_income     7201 non-null   float64
 7   upper_confidence_limit  7201 non-null   float64
 8   lower_confidence_limit  7201 non-null   float64
 9   confidence_interval     7201 non-null   float64
 10  total_monthly_income    7201 non-null   float64
 11  total_annual_income     7201 non-null   float64
dtypes: float64(6), object(6)
memory usage: 675.3+ KB<jupyter_text>Import data for 2016 and 2018The 2016 and 2018 data are given as annual income values for MSOA regions, need to scale up to monthly and scale up to LSOA regions.Assumptions: constant income over the year, so monthly salary = annual/12 for all regions<jupyter_code>colnames = ['MSOA_code','MSOA_name','local_authority_code','local_authority_name',
            'region_code','region_name','total_annual_income','upper_confidence_limit',
            'lower_confidence_limit','confidence_interval']

df_income_2016 = pd.read_csv('raw_data_to_edit/totalannualincome2016.csv', skiprows=3, names=colnames, usecols=[0,1,2,3,4,5,6,7,8,9],header=None)
df_income_2016.info()
df_income_2016.head()
df_income_2018 = pd.read_csv('raw_data_to_edit/totalannualincome2018.csv', skiprows=5, names=colnames,header=None)
df_income_2018.head()<jupyter_output><empty_output><jupyter_text>Convert numeric values from object to floats, and add a monthly income column<jupyter_code>cols_from_object_to_float = ['total_annual_income', 'upper_confidence_limit',
                            'lower_confidence_limit', 'confidence_interval']
for col in cols_from_object_to_float:
    df_income_2016[col].replace(',','', regex=True, inplace=True)
    df_income_2018[col].replace(',','', regex=True, inplace=True)
    
df_income_2016[cols_from_object_to_float] = df_income_2016[cols_from_object_to_float].astype(float)
df_income_2018[cols_from_object_to_float] = df_income_2018[cols_from_object_to_float].astype(float)
df_income_2016['total_monthly_income'] = df_income_2016['total_annual_income']/12
df_income_2018['total_monthly_income'] = df_income_2018['total_annual_income']/12

df_income_2014.tail()<jupyter_output><empty_output><jupyter_text>Check same size data frames<jupyter_code>print("There are {} rows and {} unique entries for 2014.".format(df_income_2014.shape[0],df_income_2014['MSOA_code'].nunique()))
print("There are {} rows and {} unique entries for 2016.".format(df_income_2016.shape[0],df_income_2016['MSOA_code'].nunique()))
print("There are {} rows and {} unique entries for 2018.".format(df_income_2018.shape[0],df_income_2018['MSOA_code'].nunique()))<jupyter_output>There are 7202 rows and 7202 unique entries for 2014.
There are 7202 rows and 7202 unique entries for 2016.
There are 7201 rows and 7201 unique entries for 2018.<jupyter_text>Find the MSOA code that is missing from 2018.<jupyter_code>msoa_2014 = sorted(pd.Series(df_income_2014['MSOA_code']).astype('str'))
msoa_2016 = sorted(pd.Series(df_income_2016['MSOA_code']).astype('str'))
msoa_2018 = sorted(pd.Series(df_income_2018['MSOA_code']).astype('str'))
print(list(set(msoa_2014) - set(msoa_2018)))
print(list(set(msoa_2016) - set(msoa_2018)))<jupyter_output>['Source: ONS']
['Source: ONS']<jupyter_text>We therefore delete the last rows in the 2014 and 2016 data set.<jupyter_code>df_income_2014.drop(df_income_2014.tail(1).index,inplace=True)
df_income_2016.drop(df_income_2016.tail(1).index,inplace=True)
assert df_income_2014.shape[0] == df_income_2016.shape[0]
assert df_income_2018.shape[0] == df_income_2016.shape[0]<jupyter_output><empty_output><jupyter_text>Visualisations of the 2018 income data in MSOA discretisation<jupyter_code>import geopandas as gpd
msoa = gpd.read_file('data/Middle_Layer_Super_Output_Areas_(December_2011)_Boundaries.geojson')
lsoa = gpd.read_file('data/Lower_Layer_Super_Output_Areas__December_2011__Boundaries_Full_Clipped__BFC__EW_V3.geojson')
msoa.to_crs({'init': 'epsg:27700'}, inplace=True)
lsoa.to_crs({'init': 'epsg:27700'}, inplace=True)
msoa_income_2018 = msoa.merge(df_income_2018, left_on='msoa11cd', right_on='MSOA_code')
msoa_income_2018.shape
msoa_income_2018.plot(column='total_annual_income', cmap='OrRd')<jupyter_output><empty_output><jupyter_text>Merge the data for 2014, 2016, 2018 and fill the missing years through averaging.<jupyter_code>print(df_income_2018.columns)
print(df_income_2016.columns)
print(df_income_2014.columns)

df_income_2014 = df_income_2014[['MSOA_code','total_annual_income']]

df_income_2016 = df_income_2016[['MSOA_code','total_annual_income']]

df_income_2018 = df_income_2018[['MSOA_code','total_annual_income']]
df_income_2018.head()
df_msoa_income = df_income_2014.merge(df_income_2016, on='MSOA_code', suffixes=('_2014','_2016'))
df_msoa_income = df_msoa_income.merge(df_income_2018, on='MSOA_code')
df_msoa_income.rename(columns={'total_annual_income':'total_annual_income_2018'},inplace=True)
df_msoa_income.head()
# interpolate
msoa_gradient_14_16 = (df_msoa_income.total_annual_income_2016-df_msoa_income.total_annual_income_2014)/2
df_msoa_income['total_annual_income_2015'] = df_msoa_income.total_annual_income_2014+msoa_gradient_14_16


msoa_gradient_16_18 = (df_msoa_income.total_annual_income_2018-df_msoa_income.total_annual_income_2016)/2
df_msoa_income['total_annual_income_2017'] = df_msoa_income.total_annual_income_2016+msoa_gradient_16_18

msoa_gradient_average = ( msoa_gradient_14_16 + msoa_gradient_16_18)/2
msoa_gradient_average.mean()
df_msoa_income['total_annual_income_2013'] = df_msoa_income.total_annual_income_2014 - msoa_gradient_average
df_msoa_income['total_annual_income_2019'] = df_msoa_income.total_annual_income_2018 + msoa_gradient_average
ordered_years = ['MSOA_code','total_annual_income_2013','total_annual_income_2014','total_annual_income_2015',
                'total_annual_income_2016','total_annual_income_2017','total_annual_income_2018','total_annual_income_2019']
df_msoa_income = df_msoa_income[ordered_years]
df_msoa_income.head()
#df_msoa_income.to_csv('msoa_annual_income.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10848232289286194, 'fraction_numerical': 0.08536093560962495, 'mean_word_length': 7.1938325991189425, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '268478', 'license': 'MIT', 'n_tokens_mistral': 3304, 'n_tokens_neox': 2660, 'n_words': 410, 'path': 'preprocessing/P1-process_msoa_income.ipynb', 'repo_name': 'ClimateSubak/EV-forecasting', 'size': '7439'}