<jupyter_start><jupyter_text>Load and filter data Concentration data https://data.london.gov.uk/dataset/london-atmospheric-emissions-inventory--laei--2016, see  concentrations excel files https://data.london.gov.uk/download/london-atmospheric-emissions-inventory--laei--2016/20fd5087-ee4e-4037-8b3a-61271f31348a/LAEI_2016_Concentrations_Data_Excel.zip<jupyter_code>no2 = pandas.read_csv('../data/CSV/LAEI2016_2016_NO2.csv')
pm25 = pandas.read_csv('../data/CSV/LAEI2016_2016_PM25.csv')<jupyter_output><empty_output><jupyter_text>Educational establishment data, from https://get-information-schools.service.gov.uk/Downloads, see all establishment data https://ea-edubase-api-prod.azurewebsites.net/edubase/downloads/public/edubasealldata20200310.csv<jupyter_code># edubase appears to use non ut8 encoding
edub = pandas.read_csv('../data/CSV/edubasealldata20200310.csv', encoding = "ISO-8859-1", low_memory = False)<jupyter_output><empty_output><jupyter_text>32 London borough from this list https://en.wikipedia.org/wiki/List_of_London_boroughs, this can be used to filter edubase to extract estvblishments in London<jupyter_code>bors = ['Barking and Dagenham', 'Barnet' ,'Bexley','Brent', 'Bromley','Camden','Croydon','Ealing','Enfield','Greenwich','Hackney', 'Hammersmith and Fulham','Haringey','Harrow','Havering','Hillingdon','Hounslow',
 'Islington', 'Kensington and Chelsea','Kingston upon Thames', 'Lambeth', 'Lewisham', 'Merton', 'Newham', 'Redbridge', 'Richmond upon Thames', 'Southwark', 'Sutton', 'Tower Hamlets', 'Waltham Forest','Wandsworth','Westminster']<jupyter_output><empty_output><jupyter_text>There are several ways of filtering edubase to show only nurseries, the EstablishmentTypeGroup (name) field has a "local authority nursery school" option, but there are only 95 of these in London, PhaseOfEducation (name) has a "nursery" option these are the same as the local authority option.  A better filter are establishments which have nursery classes, where there are 2316, this doesn't include all of the nursery lablled establishment, so both filters are used<jupyter_code>print(len(edub.loc[edub["LA (name)"].isin(bors)]), "establishments in the London boroughs")
print(len(edub.loc[(edub["LA (name)"].isin(bors) & (edub["PhaseOfEducation (name)"] == "Nursery"))]), "establishments in the London boroughs")
print(len(edub.loc[(edub["LA (name)"].isin(bors) & (edub["TypeOfEstablishment (name)"] == "Local authority nursery school"))]), "establishments in the London boroughs")
print(len(edub.loc[(edub["LA (name)"].isin(bors) & (edub["NurseryProvision (name)"] == "Has Nursery Classes"))]), "establishments in the London boroughs")
print(len(edub.loc[(edub["LA (name)"].isin(bors) & (edub["NurseryProvision (name)"] == "Has Nursery Classes")  & (edub["TypeOfEstablishment (name)"] == "Local authority nursery school"))]), "establishments in the London boroughs")
print(len(edub.loc[(edub["LA (name)"].isin(bors) & (edub["NurseryProvision (name)"] == "Has Nursery Classes")  & (edub["PhaseOfEducation (name)"] == "Nursery"))]), "establishments in the London boroughs")
nurseries = edub.loc[(edub["LA (name)"].isin(bors) & (edub["NurseryProvision (name)"] == "Has Nursery Classes"))]
nurseries =nurseries.append(edub.loc[(edub["LA (name)"].isin(bors) & (edub["TypeOfEstablishment (name)"] == "Local authority nursery school"))])
nurseries = nurseries.drop_duplicates('URN')
# exclude those with no spatial reference
nurseries = nurseries.loc[nurseries.Easting !=0]
nurseries.to_csv("../data/london_nurseries_edubase_2019.csv")<jupyter_output><empty_output><jupyter_text>Test data Quick check to verfiy that the coordinate systems are the same, e.g. do the coordinates overlap, looks a little off<jupyter_code>plt.figure(figsize=(30,30))
plt.scatter(no2.x.values, no2.y.values,c = no2.conc.values )
plt.scatter(nurseries.Easting.values, nurseries.Northing.values)
plt.savefig('../data/test_map.png')<jupyter_output><empty_output><jupyter_text>Use KD nearest neighbours from scipy to calculate the closest points, as a position in a list of points<jupyter_code>from scipy.spatial import KDTree
from shapely.geometry import Point
nursery_pts = nurseries[["Easting", "Northing"]].values
no2_tree = KDTree(no2[['x','y']].values)
nearest_no2 = no2_trees.query(nursery_pts)
pm25_tree = KDTree(pm25[['x','y']].values)
nearest_pm25 = pm25_tree.query(nursery_pts)<jupyter_output><empty_output><jupyter_text>Extract the point data and append to the nursery df<jupyter_code>no2_x = []
no2_y = []
no2_concs = []
for i in nearest_no2[1]:
    no2_x.append(no2.loc[i]["x"])
    no2_y.append(no2.loc[i]["y"])
    no2_concs.append(no2.loc[i]["conc"])
nurseries["no2_x"] = no2_x
nurseries["no2_y"] = no2_y
nurseries["no2_µg/m3"] = no2_concs
pm_x = []
pm_y = []
pm_concs =[]
 
for i in nearest_pm25[1]:
    pm_x.append(pm25.loc[i]["x"])
    pm_y.append(pm25.loc[i]["y"])
    pm_concs.append(pm25.loc[i]["conc"])
nurseries["pm_x"] = pm_x
nurseries["pm_y"] = pm_y
nurseries["pm25_µg/m3"] = pm_concs<jupyter_output><empty_output><jupyter_text>Test that the difference is <=10m<jupyter_code>max(nurseries["pm_x"] - nurseries["Easting"])
max(nurseries["pm_y"] - nurseries["Northing"])
max(nurseries["no2_y"] - nurseries["Northing"])
max(nurseries["no2_x"] - nurseries["Easting"])
nurseries.to_csv("../data/london_nurseries_edubase_2019_with_concs.csv")<jupyter_output><empty_output><jupyter_text>Do a couple of visual checks to make sure it is correctly projected<jupyter_code>fig, ax = plt.subplots(figsize=(30,30))
ax.scatter(nurseries.Easting.values[0:-1], nurseries.Northing.values[0:-1])
nurseries = nurseries[np.isfinite(nurseries['Easting'])]
nurseries = nurseries[np.isfinite(nurseries['Northing'])]
fig, ax = plt.subplots(figsize =(30,30))
ax.scatter(nurseries.Easting.values, nurseries.Northing.values)
 
for i, txt in enumerate(nurseries['EstablishmentName'].values):
    ax.annotate(txt, (nurseries.Easting.values[i], nurseries.Northing.values[i]),clip_on=True)
ax.set_xlim(xmin = min(nurseries.Easting.values), xmax = max(nurseries.Easting.values))
ax.set_ylim(ymin = min(nurseries.Northing.values), ymax = max(nurseries.Northing.values))

plt.figure(figsize=(30,30))
plt.scatter(no2.x.values, no2.y.values,c = no2.conc.values)
plt.scatter(nurseries.Easting.values, nurseries.Northing.values)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1588430535798957, 'fraction_numerical': 0.026552868658131817, 'mean_word_length': 7.62125340599455, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 37, 'https://': 5, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '575472', 'license': 'MIT', 'n_tokens_mistral': 2516, 'n_tokens_neox': 2322, 'n_words': 423, 'path': 'notebooks/concentration_mapping.ipynb', 'repo_name': 'ucesres/no2_nurseries', 'size': '6327'}