import numpy as np
import pandas as pd 
%matplotlib inline
import matplotlib.pyplot as plt

# load data set
df = pd.read_csv('/home/data/APD/COBRA-YTD2017.csv.gz')
print "Shape of table: ", df.shape

"""
Explanation: Atlanta Police Department

The Atlanta Police Department provides Part 1 crime data at http://www.atlantapd.org/i-want-to/crime-data-downloads
A recent copy of the data file is stored in the cluster. <span style="color: red; font-weight: bold;">Please, do not copy this data file into your home directory!</span>
Introduction

This notebooks leads into an exploration of public crime data provided by the Atlanta Police Department.
The original data set and supplemental information can be found at http://www.atlantapd.org/i-want-to/crime-data-downloads
The data set is available on ARC, please, don't download into your home directory on ARC!
End of explanation
"""


dataDict = pd.DataFrame({'DataType': df.dtypes.values, 'Description': '', }, index=df.columns.values)

dataDict

"""
Explanation: Creating a data key
Let's look at the structure of this table. We're actually creating some text output that can be used to create a data dictionary.
End of explanation
"""


dataDict.loc['MI_PRINX'].Description = '' # type: int64
dataDict.loc['offense_id'].Description = 'Unique ID in the format YYDDDNNNN with the year YY, the day of the year DDD and a counter NNNN' # type: int64
dataDict.loc['rpt_date'].Description = 'Date the crime was reported' # type: object
dataDict.loc['occur_date'].Description = 'Estimated date when the crime occured' # type: object
dataDict.loc['occur_time'].Description = 'Estimated time when the crime occured' # type: object
dataDict.loc['poss_date'].Description = '' # type: object
dataDict.loc['poss_time'].Description = '' # type: object
dataDict.loc['beat'].Description = '' # type: int64
dataDict.loc['apt_office_prefix'].Description = '' # type: object
dataDict.loc['apt_office_num'].Description = '' # type: object
dataDict.loc['location'].Description = '' # type: object
dataDict.loc['MinOfucr'].Description = '' # type: int64
dataDict.loc['MinOfibr_code'].Description = '' # type: object
dataDict.loc['dispo_code'].Description = '' # type: object
dataDict.loc['MaxOfnum_victims'].Description = '' # type: float64
dataDict.loc['Shift'].Description = 'Zones have 8 or 10 hour shifts' # type: object
dataDict.loc['Avg Day'].Description = '' # type: object
dataDict.loc['loc_type'].Description = '' # type: float64
dataDict.loc['UC2 Literal'].Description = '' # type: object
dataDict.loc['neighborhood'].Description = '' # type: object
dataDict.loc['npu'].Description = '' # type: object
dataDict.loc['x'].Description = '' # type: float64
dataDict.loc['y'].Description = '' # type: float64
dataDict.to_csv("COBRA_Data_Dictionary.csv")

dataDict

"""
Explanation: We need to enter the descriptions for each entry in our dictionary manually...
End of explanation
"""


dataDict

"""
Explanation: What is object
Let's take a look at what we've got...
End of explanation
"""


print df.groupby("Shift").count().index

"""
Explanation: What is "object?"
End of explanation
"""


# function currying
def fixdatetime(fld):
    def _fix(s):
        date_col = '%s_date' % fld # "rpt_date"
        time_col = '%s_time' % fld # "rpt_time"
        if time_col in s.index:
            return str(s[date_col])+' '+str(s[time_col])
        else:
            return str(s[date_col])+' 00:00:00'
    return _fix

df.apply(fixdatetime('rpt'), axis=1)[:10]
# df.apply(_fix, axis=1)

"""
Explanation: This shows that "shift" is a unicode string...
Date and Time

Working with dates can be tricky. Often dates and times are coded as strings and need to be converted to a date and time data format.
Python provides a module datetime to deal with converting parsing and formatting dates and times. See https://docs.python.org/2/library/datetime.html
The pandas package provides functionality to convert text fields into date/time fields...given the values adhere to a given format. See http://pandas.pydata.org/pandas-docs/version/0.20/generated/pandas.to_datetime.html

Create a proper text field
In order to use the text to date/time converter our text columns need to have the appropriate format.
End of explanation
"""


for col in ['rpt', 'occur', 'poss']:
    datser = df.apply(fixdatetime(col), axis=1)
    df['%s_dt'%col] = pd.to_datetime(datser, format="%m/%d/%Y %H:%M:%S", errors='coerce')

df.head()

def fun1(x):
    def _inner1(y):
        print x, " + ", y, " = ", x+y
    return _inner1

showname = fun1('name:')

type(showname)

showname('Bob')

addthree = fun1(3)

addthree(5)

"""
Explanation: Convert Time Columns
Please refer to the following resources for working with time series data in pandas:
- https://pandas.pydata.org/pandas-docs/stable/timeseries.html
- https://pandas.pydata.org/pandas-docs/stable/api.html#id10
End of explanation
"""


print df.occur_dt.min(), '---', df.occur_dt.max()

"""
Explanation: What's the date range of the data?
End of explanation
"""


sorted(df.occur_dt.map(lambda d: d.year).unique())

"""
Explanation: Actual years from which we have data...
End of explanation
"""


# resample is like "groupby" for time
df.resample('A-DEC', closed='right', on='occur_dt').offense_id.count()
# df['Year'] = df.occur_dt.map(lambda d: d.year)
# df2 = df[(df.Year>=2010) & (df.Year<=2017)]
# df2.shape, df.shape

"""
Explanation: This is an odd assortment of years... let's look at number of crimes reported each year
End of explanation
"""


df = df[df.occur_dt>='01/01/2017']

"""
Explanation: Looks like most of the data is actually from 2017! Let's throw the rest away...
End of explanation
"""


print "\n".join(sorted(df['UC2 Literal'].unique()))

df['oct'] = pd.to_timedelta(df.occur_time) # estimated time of ocurence in timedelta format

# Let's take a closer look at a few selected examples of crime...
vehicle_larceny = df[df['UC2 Literal']=='LARCENY-FROM VEHICLE'].resample('1H', closed='right', on='oct').offense_id.count()
pedestrian_robbery = df[df['UC2 Literal']=='ROBBERY-PEDESTRIAN'].resample('1H', closed='right', on='oct').offense_id.count()
residence_burglary = df[df['UC2 Literal']=='BURGLARY-RESIDENCE'].resample('1H', closed='right', on='oct').offense_id.count()
assault = df[df['UC2 Literal']=='AGG ASSAULT'].resample('1H', closed='right', on='oct').offense_id.count()

fig = plt.figure(figsize=(8,4));
plt.plot_date(vehicle_larceny.index, vehicle_larceny.values);
plt.title('Vehilce Larceny by Time of Day');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

fig = plt.figure(figsize=(8,4));
plt.plot_date(pedestrian_robbery.index, pedestrian_robbery.values);
plt.title('Pedestrian Robbery by Time of Day');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

fig = plt.figure(figsize=(8,4));
plt.plot_date(residence_burglary.index, residence_burglary.values);
plt.title('Residence Burglary by Time of Day');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

fig = plt.figure(figsize=(8,4));
plt.plot_date(assault.index, assault.values);
plt.title('Aggrivated Assault by Time of Day');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

"""
Explanation: Time Series and Plotting
The Pandas package provides a number of plotting features. Let's try them out.
- https://pandas.pydata.org/pandas-docs/stable/api.html#api-dataframe-plotting
- https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.boxplot.html
What time of day do different types of crimes most often occur at?
Types of crimes in the database:
End of explanation
"""


# Let's take a closer look at a few selected examples of crime...
seasonal_vehicle_larceny = df[df['UC2 Literal']=='LARCENY-FROM VEHICLE'].resample('M', closed='right', on='occur_dt').offense_id.count()
seasonal_pedestrian_robbery = df[df['UC2 Literal']=='ROBBERY-PEDESTRIAN'].resample('M', closed='right', on='occur_dt').offense_id.count()
seasonal_residence_burglary = df[df['UC2 Literal']=='BURGLARY-RESIDENCE'].resample('M', closed='right', on='occur_dt').offense_id.count()
seasonal_assault = df[df['UC2 Literal']=='AGG ASSAULT'].resample('M', closed='right', on='occur_dt').offense_id.count()
seasonal_rape = df[df['UC2 Literal']=='RAPE'].resample('M', closed='right', on='occur_dt').offense_id.count()

fig = plt.figure(figsize=(8,4));
plt.plot_date(seasonal_vehicle_larceny.index, seasonal_vehicle_larceny.values);
plt.title('Vehilce Larceny by Time of Year');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

fig = plt.figure(figsize=(8,4));
plt.plot_date(seasonal_pedestrian_robbery.index, seasonal_pedestrian_robbery.values);
plt.title('Pedestrian Robbery by Time of Year');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

fig = plt.figure(figsize=(8,4));
plt.plot_date(seasonal_residence_burglary.index, seasonal_residence_burglary.values);
plt.title('Residence Burglary by Time of Year');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

fig = plt.figure(figsize=(8,4));
plt.plot_date(seasonal_assault.index, seasonal_assault.values);
plt.title('Assault by Time of Year');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

fig = plt.figure(figsize=(8,4));
plt.plot_date(seasonal_rape.index, seasonal_rape.values);
plt.title('Rape by Time of Year');
plt.xlabel('Time (24h format)');
plt.ylabel('Number of incidents');

"""
Explanation: Are crimes seasonal?
End of explanation
"""


# let's create a colormap for the different colors...
crime_col = dict(zip(sorted(df['UC2 Literal'].unique()), 
                     ['red','olive','blue','black','black','royalblue','slategrey','purple','purple','darkblue','coral']))

crime_col

violent = df[df['UC2 Literal'].apply(lambda x: x in set(['AGG ASSAULT','HOMICIDE','RAPE']))]
plt.scatter(violent.x, violent.y, s=7, c=violent['UC2 Literal'].apply(lambda x: crime_col[x]), edgecolors='none')

df[df['UC2 Literal']=='ROBBERY-RESIDENCE']

violent = df[df['UC2 Literal'].apply(lambda x: x in ['ROBBERY-COMMERCIAL', 'ROBBERY-PEDESTRIAN', 'ROBBERY-RESIDENCE'])]
plt.scatter(violent.x, violent.y, s=10, c=violent['UC2 Literal'].apply(lambda x: crime_col[x]), edgecolors='none', marker='^')

"""
Explanation: Descriptive Statistics
https://pandas.pydata.org/pandas-docs/stable/basics.html#descriptive-statistics
Beats and Zones
The City of Atlanta is divided into 6 zones, each with 12 to 14 beats.
End of explanation
"""


df['Zone'] = df['beat']//100

pd.DataFrame(df.groupby(['Zone','UC2 Literal']).offense_id.count())

"""
Explanation: Crimes by Zone
In the beat ID, the hundreds place indicates the zone number... 
Let's create a separate column for zone.
End of explanation
"""


