import os
import pandas as pd
import geopandas as gpd
FOLDER_RAW = os.getenv('DIR_DATA_RAW')
DATA_CRIME = 'On_Street_Crime_In_Camden.csv'
DATA_POP = 'Population_20Projections_20_latest_20GLA_20set_.xlsx'
DATA_MAP = 'geo_export_aba7f1fe-addd-4eff-a2f6-fa63b09e4d6f.shp'
DATA_LIGHT = 'geo_export_e87e8b43-cf73-48e4-ad5c-692f56b45394.shp'
SAMPLE_SIZE = 5
SEED = 42
df_crime = pd.read_csv(filepath_or_buffer=FOLDER_RAW + "/" + DATA_CRIME,
parse_dates=["Outcome Date", "Epoch"])
df_pop = pd.read_excel(io=FOLDER_RAW + "/" + DATA_POP,
sheet_name="WardP Summry",
header=None,
names=['Ward Code',
'Ward Name',
'2015',
'2016',
'2017',
'2018',
'2019',
'2020'],
usecols="B,D,K:P",
skiprows=5,
nrows=18)
shp_ward = gpd.read_file(filename=FOLDER_RAW + "/camden_ward_boundary/" + DATA_MAP)
shp_light = gpd.read_file(filename=FOLDER_RAW + "/camden_street_lighting/" + DATA_LIGHT)
4.1. Viewing the data¶
Below we display how the data looks in its raw form.
4.1.1. On Street Crime in Camden¶
Key columns to take further are:
Outcome Date
Category
Ward Name
For further development, would like to also include:
Street Name
Outcome Category
Longitude
Latitude
df_crime.sample(n=SAMPLE_SIZE, random_state=SEED)
| Category | Street ID | Street Name | Context | Outcome Category | Outcome Date | Service | Location Subtype | ID | Persistent ID | Epoch | Ward Code | Ward Name | Easting | Northing | Longitude | Latitude | Spatial Accuracy | Last Uploaded | Location | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 72295 | Violence and sexual offences | 964951 | On or near Granary Street | NaN | Under investigation | 2018-11-01 | Police Force | NaN | 69923957 | b448b5ba18f667c0c41578896c1413bbb118a67a119c75... | 2018-11-01 | E05000143 | St Pancras and Somers Town | 529735.22 | 183678.71 | -0.130788 | 51.537113 | This is only an approximation of where the cri... | 16/01/2019 | (51.537113, -0.130788) |
| 63873 | Theft from the person | 965109 | On or near Chalk Farm Road | NaN | Under investigation | 2019-06-01 | Police Force | NaN | 75346137 | eff0e590a6e1d1bb8b63982f979576456d61c804109523... | 2019-06-01 | E05000130 | Camden Town with Primrose Hill | 528361.22 | 184320.67 | -0.150354 | 51.543196 | This is only an approximation of where the cri... | 28/10/2019 | (51.543196, -0.150354) |
| 100452 | Burglary | 967848 | On or near Hodes Row | NaN | Court result unavailable | 2016-10-01 | Police Force | NaN | 48516072 | e9f5c6d9f6e1fe8ccd68da2793b0e93cfeca87fd6bcd9d... | 2016-04-01 | E05000134 | Gospel Oak | 528010.21 | 185549.72 | -0.154966 | 51.554321 | This is only an approximation of where the cri... | 11/07/2018 | (51.554321, -0.154966) |
| 3195 | Public order | 965357 | On or near Springfield Walk | NaN | Investigation complete; no suspect identified | 2018-01-01 | Police Force | NaN | 62659107 | 18446443d68fcd10c190003334a190a30f36913428f451... | 2018-01-01 | E05000140 | Kilburn | 525571.29 | 183598.75 | -0.190822 | 51.537335 | This is only an approximation of where the cri... | 13/04/2018 | (51.537335, -0.190822) |
| 97931 | Violence and sexual offences | 967842 | On or near Mansfield Road | NaN | Investigation complete; no suspect identified | 2016-02-01 | Police Force | NaN | 45572039 | 45490e377849b18772082bb02d74f38a5d63bdeba6ca79... | 2015-12-01 | E05000134 | Gospel Oak | 528007.87 | 185498.85 | -0.155056 | 51.553872 | This is only an approximation of where the cri... | 05/05/2016 | (51.553872, -0.155056) |
4.1.2. Camden Population Projections¶
Key columns to take further are:
Ward Name
2015, …, 2020
df_pop.sample(n=SAMPLE_SIZE, random_state=SEED)
| Ward Code | Ward Name | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | |
|---|---|---|---|---|---|---|---|---|
| 0 | E05000128 | Belsize | 13959.21 | 14210.18 | 14308.59 | 14554.40 | 14643.24 | 14704.67 |
| 1 | E05000129 | Bloomsbury | 11530.05 | 11458.64 | 11652.23 | 11890.26 | 11731.53 | 11689.13 |
| 8 | E05000136 | Haverstock | 13545.17 | 13808.57 | 13843.23 | 14160.22 | 14035.79 | 13994.86 |
| 5 | E05000133 | Frognal and Fitzjohns | 13189.08 | 13360.85 | 13692.78 | 14127.59 | 14078.03 | 14116.99 |
| 3 | E05000131 | Cantelowes | 12995.70 | 13403.75 | 13337.70 | 13734.33 | 13742.31 | 13760.46 |
4.1.3. Camden Ward Boundary¶
Key columns to take further are:
name
geometry
shp_ward.sample(n=SAMPLE_SIZE, random_state=SEED)
| descriptio | name | geometry | |
|---|---|---|---|
| 0 | None | Belsize | LINESTRING (-0.17512 51.54360, -0.17483 51.544... |
| 1 | None | Bloomsbury | LINESTRING (-0.14358 51.52398, -0.14354 51.523... |
| 8 | None | Haverstock | LINESTRING (-0.16186 51.54876, -0.16124 51.548... |
| 5 | None | Frognal and Fitzjohns | LINESTRING (-0.19040 51.56273, -0.18987 51.562... |
| 3 | None | Cantelowes | LINESTRING (-0.12910 51.54883, -0.12908 51.548... |
4.1.4. Camden Street Lighting¶
Key columns to take further are:
lamp_type
ward_name
wattage
Note: This dataset is updated daily so the date we have for street lighting is the date the author collected the data.
For further development, would like to also include:
street_nam
longitude
latitude
geometry
shp_light.sample(n=SAMPLE_SIZE, random_state=SEED)
| column_hei | column_typ | descriptio | easting | heritage_s | lamp_type | date_last_ | time_last_ | latitude | local_auth | longitude | northing | organisati | postcode | spatial_ac | street_nam | switch_reg | ward_name | wattage | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 8675 | 500.0 | Lighting Column | OPPOSITE 59 | 529594.0 | No | Cosmopolis | 2020-12-03 | 10:23:46.000 | 51.545560 | 707191 | -0.132478 | 184615.0 | http://opendatacommunities.org/id/london-borou... | NW1 9UU | Plotted By Council Officer | CAMDEN MEWS | 35/18 | Cantelowes | 60.0 | POINT (-0.13248 51.54556) |
| 9755 | 800.0 | Lighting Column | O/S PUB, 1ST FROM J/W ST PANCRAS WAY | 529350.0 | No | Cosmopolis | 2020-12-03 | 10:23:47.000 | 51.541833 | 707476 | -0.136149 | 184194.0 | http://opendatacommunities.org/id/london-borou... | NW1 0SS | Plotted By Council Officer | RANDOLPH STREET | 35/18 | Cantelowes | 60.0 | POINT (-0.13615 51.54183) |
| 9573 | 800.0 | Lighting Column | OPP NO 30 DRAYTON HOUSE | 529586.0 | No | Cosmopolis | 2020-12-03 | 10:23:48.000 | 51.525980 | 708463 | -0.133395 | 182436.0 | http://opendatacommunities.org/id/london-borou... | NW1 2BE | Plotted By Council Officer | GORDON STREET | 35/18 | Bloomsbury | 90.0 | POINT (-0.13340 51.52598) |
| 11138 | 400.0 | Lighting Column | OPP CHURCH | 526256.0 | Yes | High Pressure Sodium | 2020-12-03 | 10:23:36.000 | 51.555339 | 701823 | -0.180230 | 185619.0 | http://opendatacommunities.org/id/london-borou... | NW3 6UP | Plotted By Council Officer | CHURCH ROW | 35/18 | Frognal and Fitzjohns | 100.0 | POINT (-0.18023 51.55534) |
| 10987 | 500.0 | Lighting Column | O/S 27 | 524618.0 | No | Generic LED Lighting | 2020-12-03 | 10:23:32.000 | 51.554714 | 70450 | -0.203883 | 185509.0 | http://opendatacommunities.org/id/london-borou... | NW2 3RJ | Plotted By Council Officer | MENELIK ROAD | 35/18 | Fortune Green | 42.0 | POINT (-0.20388 51.55471) |