<jupyter_start><jupyter_text>Prepare Data for WorkshopWorkshop: https://github.com/IBMDeveloperUK/geopandas-workshopData downloaded from https://data.police.uk/data/ for Metropolitan Police Service into `/data/crime_data` folder, unzipped and all files moved to one folder `/data/crime_data/by_year`.<jupyter_code>%matplotlib inline
import numpy as np
import pandas as pd
import geopandas as gpd
import zipfile<jupyter_output><empty_output><jupyter_text>Convert crime data to easier readable formatThese files are uploaded to https://github.com/IBMDeveloperUK/geopandas-workshop/tree/master/data<jupyter_code>years = ['2017','2018']
months = ['01','02','03','04','05','06','07','08','09','10','11','12']

for year in years:
    for month in months:
        if month == '01':
            df = pd.read_csv("data/crime_data/"+year+"-"+month+"-metropolitan-stop-and-search.csv")
        else:
            df2 = pd.read_csv("data/crime_data/"+year+"-"+month+"-metropolitan-stop-and-search.csv")
            df = df.append(df2)
    df = df.drop(columns=['Gender', 'Age range','Self-defined ethnicity',
                                  'Officer-defined ethnicity','Removal of more than just outer clothing'])
    df.to_csv("data/crime_data/by_year/"+year+"-metropolitan-stop-and-search.csv")
    zip_file = zipfile.ZipFile("data/crime_data/by_year/"+year+"-metropolitan-stop-and-search.zip", 'w')
    zip_file.write("data/crime_data/by_year/"+year+"-metropolitan-stop-and-search.csv", compress_type=zipfile.ZIP_DEFLATED)
    zip_file.close()

!rm data/crime_data/by_year/*.csv
years = ['2017','2018']
months = ['01','02','03','04','05','06']

for year in years:
    for month in months:
        if month == '01':
            df = pd.read_csv("data/crime_data/"+year+"-"+month+"-metropolitan-street.csv")
        else:
            df2 = pd.read_csv("data/crime_data/"+year+"-"+month+"-metropolitan-street.csv")
            df = df.append(df2)
    df = df.drop(columns=['Reported by','Falls within','LSOA name'])
    df.to_csv("data/crime_data/by_year/"+year+"-1-metropolitan-street.csv")
    zip_file = zipfile.ZipFile("data/crime_data/by_year/"+year+"-1-metropolitan-street.zip", 'w')
    zip_file.write("data/crime_data/by_year/"+year+"-1-metropolitan-street.csv", compress_type=zipfile.ZIP_DEFLATED)
    zip_file.close()

!rm data/crime_data/by_year/*.csv
years = ['2017','2018']
months = ['07','08','09','10','11','12']

for year in years:
    for month in months:
        if month == '07':
            df = pd.read_csv("data/crime_data/"+year+"-"+month+"-metropolitan-street.csv")
        else:
            df2 = pd.read_csv("data/crime_data/"+year+"-"+month+"-metropolitan-street.csv")
            df = df.append(df2)
    df = df.drop(columns=['Reported by','Falls within','LSOA name'])
    df.to_csv("data/crime_data/by_year/"+year+"-2-metropolitan-street.csv")
    zip_file = zipfile.ZipFile("data/crime_data/by_year/"+year+"-2-metropolitan-street.zip", 'w')
    zip_file.write("data/crime_data/by_year/"+year+"-2-metropolitan-street.csv", compress_type=zipfile.ZIP_DEFLATED)
    zip_file.close()

!rm data/crime_data/by_year/*.csv<jupyter_output><empty_output><jupyter_text>Clean up Borough shape files<jupyter_code># https://data.london.gov.uk/dataset/2011-boundary-files
# Included are Output Area (OA), Lower Super Output Area (LSOA) and Middle-Level Super Output Area (MSOA) bounadries.
#Each geography is provided at Extent of the Realm (BFE), Coastline (BFC) and Generalised Coastline (BGC).

Boundaries = gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_City_of_London.shp")
#Boundaries[]
Boundaries.plot();
Boundaries = Boundaries.append(gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_Westminster.shp"))
Boundaries.plot();
Boundaries = Boundaries.append(gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_Camden.shp"))
Boundaries = Boundaries.append(gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_Islington.shp"))
Boundaries = Boundaries.append(gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_Hackney.shp"))
Boundaries = Boundaries.append(gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_Tower_Hamlets.shp"))
Boundaries = Boundaries.append(gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_Southwark.shp"))
Boundaries = Boundaries.append(gpd.read_file("data/2011_london_boundaries/LSOA_2011_BFE_London/LSOA_2011_BFE_Lambeth.shp"))

Boundaries.plot();
Boundaries.plot(column='POPDEN',cmap="Reds",scheme='quantiles');
Boundaries.head()
Boundaries.to_file("data/boundaries.shp")<jupyter_output><empty_output><jupyter_text>Convert to lat/lon - work in progress<jupyter_code>#https://pypi.org/project/OSGridConverter/
#!pip install OSGridConverter

#from OSGridConverter import grid2latlong

#l=grid2latlong('TG 532151  181867')
#(l.latitude,l.longitude)
#from OSGridConverter import latlong2grid
#g=latlong2grid(51.993742,-0.975257, tag = ‘WGS84’)
#str(g)<jupyter_output><empty_output><jupyter_text>boroughs in lat/lon<jupyter_code>#https://skgrange.github.io/www/data/london_boroughs.json

boroughs2 = gpd.read_file("data/london_boroughs.json")
boroughs2.tail()
boroughs2.plot();
london = boroughs2.dissolve(by='inner_statistical',aggfunc='sum')
london
london.head()<jupyter_output><empty_output><jupyter_text>Bounding box for extracting London OSM data<jupyter_code>bounding_box = london.envelope

bb = gpd.GeoDataFrame(gpd.GeoSeries(bounding_box), columns=['geometry'])
bb.head()
bb.plot();
london2 = london.drop([0, 0])
london2.head()
london2.plot();
xmin, ymin, xmax, ymax = london2.total_bounds
xmin, ymin, xmax, ymax<jupyter_output><empty_output><jupyter_text>Open Street Map data<jupyter_code># http://download.geofabrik.de/europe/great-britain.html

roads_all = gpd.read_file("data/england-latest-free/gis_osm_roads_free_1.shp")
roads_all.head()

roads = roads_all.cx[xmin:xmax, ymin:ymax]
roads.to_file("data/london_inner_roads.shp")
pois_all = gpd.read_file("data/england-latest-free/gis_osm_pois_free_1.shp")
pois_all.head()

pois = pois_all.cx[xmin:xmax, ymin:ymax]
pois.to_file("data/london_inner_pois.shp")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15442350686681572, 'fraction_numerical': 0.037527946343021396, 'mean_word_length': 5.691239316239316, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 6, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '284445', 'license': 'MIT', 'n_tokens_mistral': 2573, 'n_tokens_neox': 2332, 'n_words': 304, 'path': 'notebooks/prepare-uk-crime-data.ipynb', 'repo_name': 'kant/geopandas-workshop', 'size': '6262'}