We want to make a single .csv with all months of 2017; bike theft data only.
PMR (test to see if can edit and commit)
Import libraries (pandas should always be imported as pd)
import pandas as pd
import glob, os
Load data - this solution is not scalable
JanSt = pd.read_csv('../data/Crime_data_2017/2017-01/2017-01-cambridgeshire-street.csv')
FebSt = pd.read_csv('../data/Crime_data_2017/2017-02/2017-02-cambridgeshire-street.csv')
MarSt = pd.read_csv('../data/Crime_data_2017/2017-03/2017-03-cambridgeshire-street.csv')
AprSt = pd.read_csv('../data/Crime_data_2017/2017-04/2017-04-cambridgeshire-street.csv')
MaySt = pd.read_csv('../data/Crime_data_2017/2017-05/2017-05-cambridgeshire-street.csv')
JunSt = pd.read_csv('../data/Crime_data_2017/2017-06/2017-06-cambridgeshire-street.csv')
JulSt = pd.read_csv('../data/Crime_data_2017/2017-07/2017-07-cambridgeshire-street.csv')
AugSt = pd.read_csv('../data/Crime_data_2017/2017-08/2017-08-cambridgeshire-street.csv')
SepSt = pd.read_csv('../data/Crime_data_2017/2017-09/2017-09-cambridgeshire-street.csv')
OctSt = pd.read_csv('../data/Crime_data_2017/2017-10/2017-10-cambridgeshire-street.csv')
NovSt = pd.read_csv('../data/Crime_data_2017/2017-11/2017-11-cambridgeshire-street.csv')
DecSt = pd.read_csv('../data/Crime_data_2017/2017-12/2017-12-cambridgeshire-street.csv')
Produce a list of all the data frames
frames = [JanSt,FebSt,MarSt,AprSt,MaySt,JunSt,JulSt,AugSt,SepSt,OctSt,NovSt,DecSt]
Concatenate data into single data frame
St = pd.concat(frames)
Extract only data on bicycle thefts
Bikethefts = St[St['Crime type'].str.contains("Bicycle theft")]
View the head (first 5 rows) of the bike theft data
pd.DataFrame.head(Bikethefts)
Crime ID | Month | Reported by | Falls within | Longitude | Latitude | Location | LSOA code | LSOA name | Crime type | Last outcome category | Context | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
9 | 7618ab88baac08d51a23432420127e44144ce4748d5112... | 2017-01 | Cambridgeshire Constabulary | Cambridgeshire Constabulary | 0.135772 | 52.232788 | On or near Kirkwood Road | E01017975 | Cambridge 001A | Bicycle theft | Investigation complete; no suspect identified | NaN |
10 | e498705835a2699a86fc3adf17cd7b00eae728ced382b4... | 2017-01 | Cambridgeshire Constabulary | Cambridgeshire Constabulary | 0.131681 | 52.229005 | On or near Crowland Way | E01017975 | Cambridge 001A | Bicycle theft | Investigation complete; no suspect identified | NaN |
11 | 529d80f26b78125e9f9386a0f0cf82582fe1059a991d0e... | 2017-01 | Cambridgeshire Constabulary | Cambridgeshire Constabulary | 0.130128 | 52.230525 | On or near Crathern Way | E01017975 | Cambridge 001A | Bicycle theft | Investigation complete; no suspect identified | NaN |
12 | 08e44ae155f1be07b25214f94996eb75e6e7c93a0ca3c1... | 2017-01 | Cambridgeshire Constabulary | Cambridgeshire Constabulary | 0.135772 | 52.232788 | On or near Kirkwood Road | E01017975 | Cambridge 001A | Bicycle theft | Investigation complete; no suspect identified | NaN |
88 | 76d55f7b71de6f014123fd9f69a9fde85c93ca3b4e228a... | 2017-01 | Cambridgeshire Constabulary | Cambridgeshire Constabulary | 0.126413 | 52.233362 | On or near Minerva Way | E01017979 | Cambridge 001E | Bicycle theft | Investigation complete; no suspect identified | NaN |
Extract only the columns of interest: Crime ID, Month, Longitude, Latitude, Location and Last outcome category.
BikeSub = Bikethefts[['Crime ID', 'Month', 'Longitude', 'Latitude','Location','Last outcome category']]
View the head of the subsetted bike theft data
pd.DataFrame.head(BikeSub)
Crime ID | Month | Longitude | Latitude | Location | Last outcome category | |
---|---|---|---|---|---|---|
9 | 7618ab88baac08d51a23432420127e44144ce4748d5112... | 2017-01 | 0.135772 | 52.232788 | On or near Kirkwood Road | Investigation complete; no suspect identified |
10 | e498705835a2699a86fc3adf17cd7b00eae728ced382b4... | 2017-01 | 0.131681 | 52.229005 | On or near Crowland Way | Investigation complete; no suspect identified |
11 | 529d80f26b78125e9f9386a0f0cf82582fe1059a991d0e... | 2017-01 | 0.130128 | 52.230525 | On or near Crathern Way | Investigation complete; no suspect identified |
12 | 08e44ae155f1be07b25214f94996eb75e6e7c93a0ca3c1... | 2017-01 | 0.135772 | 52.232788 | On or near Kirkwood Road | Investigation complete; no suspect identified |
88 | 76d55f7b71de6f014123fd9f69a9fde85c93ca3b4e228a... | 2017-01 | 0.126413 | 52.233362 | On or near Minerva Way | Investigation complete; no suspect identified |
Write the processed data as a csv file.
BikeSub.to_csv('../data/processed/bikethefts.csv', index = False)