Aim:
Creating an excel file with python to display:
Each Province, State and Crime categories ranging from (April 1998- March 2019)
import pandas as pd
import numpy as np
import os
# reading the excel data and sheet
General = pd.ExcelFile(r'C:\Users\Nnabugwu kevin\Desktop\DRAFT REQUEST ANWOLO - Copy.xlsx')
df1 = pd.read_excel(General, 'province and station')
df2 = pd.read_excel(General, 'crime categories')
from datetime import datetime
import calendar
df1.head()
Province | Police station | |
---|---|---|
0 | Eastern Cape | Aberdeen |
1 | Eastern Cape | Addo |
2 | Eastern Cape | Adelaide |
3 | Eastern Cape | Afsondering |
4 | Eastern Cape | Alexandria |
df2.head()
Crime Categories | |
---|---|
0 | Domestic Violence |
1 | Murder |
2 | Attempted murder |
3 | Total Sexual offences |
4 | Assault GBH |
df1['Crime Categories'] = ''
df1['Start Date'] = ''
df1['End Date'] = ''
df1.head()
Province | Police station | Crime Categories | Start Date | End Date | |
---|---|---|---|---|---|
0 | Eastern Cape | Aberdeen | |||
1 | Eastern Cape | Addo | |||
2 | Eastern Cape | Adelaide | |||
3 | Eastern Cape | Afsondering | |||
4 | Eastern Cape | Alexandria |
start_date = pd.date_range('1998-04-01', '2019-03-01', freq='MS')
end_date = pd.date_range('1998-04-30', '2019-03-31', freq='M')
crime_list = ['Domestic Violence', 'Murder', 'Attempted murder', 'Total Sexual offences',
'Assault GBH', 'Common Assault', 'Robbery with aggravating circumstances',
'Common Robbery', 'Arson', 'Malicious damage to property',
'Burglary at non-residential premises', 'Burglary at residential premises', 'Theft of motor vehicle',
'Theft out of/ from motor vehicle', 'Stock theft', 'Illegal possession of fiream and ammunition', 'Drug-related crime',
'Driving under the influence of alcohol or drugs',
'Sexual offences detected as a result of police action', 'All theft not mentioned elsewhere',
'Commercial crime', 'Shoplifting']
# populating the data
for crime in crime_list:
for i in df1['Police station']:
df1['Crime Categories'] = crime
df1['Start Date'] = pd.Series(start_date)
df1['End Date'] = pd.Series(end_date)
df1.head()
Province | Police station | Crime Categories | Start Date | End Date | |
---|---|---|---|---|---|
0 | Eastern Cape | Aberdeen | Shoplifting | 1998-04-01 | 1998-04-30 |
1 | Eastern Cape | Addo | Shoplifting | 1998-05-01 | 1998-05-31 |
2 | Eastern Cape | Adelaide | Shoplifting | 1998-06-01 | 1998-06-30 |
3 | Eastern Cape | Afsondering | Shoplifting | 1998-07-01 | 1998-07-31 |
4 | Eastern Cape | Alexandria | Shoplifting | 1998-08-01 | 1998-08-31 |
df1.tail()
Province | Police station | Crime Categories | Start Date | End Date | |
---|---|---|---|---|---|
1136 | Western Cape | Wellington | Shoplifting | NaT | NaT |
1137 | Western Cape | Wolseley | Shoplifting | NaT | NaT |
1138 | Western Cape | Woodstock | Shoplifting | NaT | NaT |
1139 | Western Cape | Worcester | Shoplifting | NaT | NaT |
1140 | Western Cape | Wynberg | Shoplifting | NaT | NaT |