<jupyter_start><jupyter_text>Mexico City's crime exploratory analysis<jupyter_code>## Dependencies
import pandas as pd
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>A. Data collection<jupyter_code>## Import data from FGJ open cases CSV file
## Reference: https://datos.cdmx.gob.mx/dataset/carpetas-de-investigacion-fgj-de-la-ciudad-de-mexico/resource/48fcb848-220c-4af0-839b-4fd8ac812c0f
FGJ_data = pd.read_csv('data/carpetas_completa_septiembre_2021.csv', low_memory=False)<jupyter_output><empty_output><jupyter_text>B. Data exploration<jupyter_code>FGJ_data.head(3)
FGJ_data.tail(3)
FGJ_data.shape
FGJ_data.describe()
FGJ_data.isna().any()
FGJ_data.dtypes
FGJ_data[['delito', 'fiscalia', 'agencia','categoria_delito','alcaldia_hechos','competencia']].nunique()
FGJ_data.delito.value_counts()
FGJ_data.fiscalia.value_counts()
FGJ_data.categoria_delito.value_counts()
FGJ_data.alcaldia_hechos.value_counts()
FGJ_data.competencia.value_counts()<jupyter_output><empty_output><jupyter_text>1. Define analysis time frame<jupyter_code>## Crime incidence for the last 20 years (number of registers in FGJ db) 
year_incidence = FGJ_data.ao_hechos.value_counts()
year_incidence[year_incidence.index >= 1991].sort_index(ascending=False)
FGJ_data[['ao_hechos','mes_hechos','competencia']].loc[FGJ_data['ao_hechos'] > 2000].sort_values(['ao_hechos','mes_hechos']).shape
FGJ_data[['ao_hechos','mes_hechos','competencia']].loc[FGJ_data['ao_hechos'] > 2000].dropna().sort_values(['ao_hechos','mes_hechos']).shape
FGJ_data.loc[(FGJ_data['ao_hechos'] >= 2014) & (FGJ_data['ao_hechos'] <= 2017)].groupby(['ao_hechos','mes_hechos']).count()['delito']
FGJ_data.loc[(FGJ_data['ao_inicio'] >= 2014) & (FGJ_data['ao_inicio'] <= 2017)].groupby(['ao_inicio','mes_inicio']).count()['delito']
FGJ_data.loc[(FGJ_data['ao_hechos'] >= 2014) & (FGJ_data['ao_hechos'] <= 2017)]['alcaldia_hechos'].value_counts()<jupyter_output><empty_output><jupyter_text>C. Clean dataset<jupyter_code>print(FGJ_data.shape)
FGJ_data.columns
## Select columns for clean dataset
columns = ['ao_hechos', 'mes_hechos', 'fecha_hechos', 'delito', 'categoria_delito',
           'colonia_hechos', 'alcaldia_hechos', 'longitud', 'latitud']

## Build clean dataset according to defined analysis timeframe (2016-2021) 
FGJ_clean = FGJ_data.loc[(FGJ_data['ao_hechos'] >= 2016)][columns]
print(FGJ_clean.shape)
## Convert timestamp to datetimeindex
FGJ_clean['fecha_hechos'] = pd.to_datetime(FGJ_clean.fecha_hechos.values)
## Set datetimeindex
FGJ_clean.set_index('fecha_hechos', inplace=True)
## FGJ_clean.categoria_delito.value_counts()
FGJ_clean.alcaldia_hechos.value_counts().head(20)
## Get alcaldias list
alcaldias_list = FGJ_clean.alcaldia_hechos.value_counts().head(16).index
alcaldias_list
## Filter by alcaldias list
FGJ_alcaldias = FGJ_clean.loc[FGJ_clean['alcaldia_hechos'].isin(alcaldias_list)]
print(FGJ_alcaldias.shape)
FGJ_alcaldias.head(3)<jupyter_output>(1264614, 8)<jupyter_text>2. Increasing / decreasing crime rates<jupyter_code>## Build crime list
delitos_list = FGJ_alcaldias.delito.value_counts().index
## Build crime rate change DF for last year
crime_rate = pd.DataFrame(delitos_list.sort_values())
crime_rate.set_index(delitos_list.sort_values(), inplace=True)
crime_rate.drop(0, axis=1, inplace=True)

crime_rate['Sep-2020'] = FGJ_alcaldias.loc[(FGJ_alcaldias.ao_hechos == 2020)&(FGJ_alcaldias.mes_hechos == 'Septiembre')].delito.value_counts()
crime_rate['Ago-2021'] = FGJ_alcaldias.loc[(FGJ_alcaldias.ao_hechos == 2021)&(FGJ_alcaldias.mes_hechos == 'Agosto')].delito.value_counts()

crime_rate.dropna(inplace=True)
crime_rate = crime_rate.loc[(crime_rate['Sep-2020'] >= 50) & (crime_rate['Ago-2021'] >= 50)]

crime_rate['Pct_Change'] = (crime_rate['Ago-2021'] - crime_rate['Sep-2020'])/crime_rate['Sep-2020']
## Top 5 crime increase rate
top_increase = crime_rate.sort_values(['Pct_Change'], ascending=False).head(5).index.values
top_increase
## Top 5 crime decrease rate
top_decrease = crime_rate.sort_values(['Pct_Change'], ascending=False).tail(5).index.values
top_decrease
FGJ_last_year = FGJ_alcaldias.loc[FGJ_alcaldias.index.to_series().between('2020-09-01','2021-08-31')].sort_index()
FGJ_last_year.loc[FGJ_last_year.delito.isin(top_increase)].groupby(['delito',pd.Grouper(freq='m')]).count()['ao_hechos'].unstack().T.plot(
    kind='line',
    title="Delitos al Alza")

plt.xlabel('')
plt.ylabel('Casos')
plt.legend(bbox_to_anchor=(1.27, -0.15), ncol=1)
plt.show()
FGJ_last_year.loc[FGJ_last_year.delito.isin(top_decrease)].groupby(['delito',pd.Grouper(freq='m')]).count()['ao_hechos'].unstack().T.plot(
    kind='line',
    title="Delitos a la Baja")

plt.xlabel('')
plt.ylabel('Casos')
plt.legend(bbox_to_anchor=(2.7, -0.15), ncol=1)
plt.show()<jupyter_output><empty_output><jupyter_text>3. Crime count by Alcaldía<jupyter_code>FGJ_alcaldias.groupby(['alcaldia_hechos']).count()['delito'].sort_values(ascending=False)
FGJ_alcaldias.groupby(['alcaldia_hechos']).count()['delito'].sort_values().plot(
    kind='barh',
    title="Incidencia Delictiva")

plt.xlabel('Delitos')
plt.ylabel('')
plt.grid(True, which='both', axis='x')
plt.show()<jupyter_output><empty_output><jupyter_text>4. Crime rate by Alcaldía<jupyter_code>## Build crime rate change DF for last year
alcaldias_rate = pd.DataFrame(alcaldias_list.sort_values())
alcaldias_rate.set_index(alcaldias_list.sort_values(), inplace=True)
alcaldias_rate.drop(0, axis=1, inplace=True)

alcaldias_rate['Sep-2020'] = FGJ_alcaldias[(FGJ_alcaldias.ao_hechos == 2020)&(FGJ_alcaldias.mes_hechos == 'Septiembre')].groupby(['alcaldia_hechos']).count()['delito']
alcaldias_rate['Ago-2021'] = FGJ_alcaldias[(FGJ_alcaldias.ao_hechos == 2021)&(FGJ_alcaldias.mes_hechos == 'Agosto')].groupby(['alcaldia_hechos']).count()['delito']

alcaldias_rate['Pct_Change'] = (alcaldias_rate['Ago-2021'] - alcaldias_rate['Sep-2020'])/alcaldias_rate['Sep-2020']
alcaldias_rate
## Top 5 crime increase rate (least decrease rate)
alcaldias_rate.sort_values(['Pct_Change'], ascending=False).head(5)
## Top 5 crime decrease rate
alcaldias_rate.sort_values(['Pct_Change'], ascending=False).tail(5)
alcaldias_rate.sort_values(['Pct_Change'], ascending=False).plot(
    kind='bar',
    figsize=(12,5),
    title="Índice Delictivo")

plt.xlabel('')
plt.ylabel('Delitos')
plt.grid(True, which='both', axis='y')
plt.legend(bbox_to_anchor=(1,1))
plt.show()<jupyter_output><empty_output><jupyter_text>5. Distinctive crimes by Alcaldía<jupyter_code>len(delitos_list)
top_delitos = FGJ_alcaldias.groupby('delito').count()['ao_hechos'].sort_values(ascending=False).head(10).index
top_delitos
distinctive_crimes = pd.DataFrame(columns = [alcaldias_list.sort_values()], index = top_delitos.sort_values())
for alcaldia in alcaldias_list:
    distinctive_crimes[alcaldia] = FGJ_alcaldias[FGJ_alcaldias['alcaldia_hechos'] == alcaldia].groupby(['delito'])['ao_hechos'].count()
## Compute proportional crime rates
for alcaldia in alcaldias_list:
    distinctive_crimes[alcaldia] = distinctive_crimes[alcaldia] / distinctive_crimes[alcaldia].sum() 
distinctive_crimes
distinctive_crimes.T.sort_index(ascending=False).plot(
    kind='barh',
    figsize=(10,8),
    stacked=True,
    legend=True,
    title="Delitos característicos")

plt.xlabel('Proporción Incidencia [%]')
plt.ylabel('')
plt.legend(bbox_to_anchor=(1.025,1.015))
plt.show()
# distinctive_crimes.sort_index(ascending=False).plot(kind='barh', stacked=True, legend=True)
# plt.legend(bbox_to_anchor=(1.2,1.05))
# plt.show()<jupyter_output><empty_output><jupyter_text>6. Crime seasonality<jupyter_code>FGJ_alcaldias.groupby([pd.Grouper(freq='m')]).count()['delito'].plot(
    kind='line',
    figsize=(15,5),
    color='red',
    title='Incidencia Delictiva')

plt.xlabel('Fecha')
plt.ylabel('Delitos')
plt.grid(True, which='both', axis='x')
plt.show()
FGJ_alcaldias.groupby(FGJ_alcaldias.index.month).count()['delito'].plot(
    kind='bar',
    figsize=(10,5),
    color='gray',
    title='Estacionalidad por mes')

plt.xlabel('Mes')
plt.ylabel('Delitos')
plt.show()
FGJ_alcaldias.groupby(FGJ_alcaldias.index.isocalendar().week).count()['delito'].plot(
    kind='bar',
    figsize=(10,5),
    color='orange',
    title='Estacionalidad por semana')

plt.xlabel('Semana')
plt.ylabel('Delitos')
plt.show()
FGJ_alcaldias.groupby(FGJ_alcaldias.index.day).count()['delito'].plot(
    kind='bar',
    figsize=(10,5),
    color='purple',
    title='Estacionalidad por día')

plt.xlabel('Día')
plt.ylabel('Delitos')
plt.show()<jupyter_output><empty_output><jupyter_text>7. Crime concentration<jupyter_code>total_delitos = len(FGJ_alcaldias)
total_colonias = len(FGJ_alcaldias['colonia_hechos'].unique())

print(f"Total delitos: {total_delitos :,}")
print(f"95% = {total_delitos * 0.95 :,.1f} delitos")
print(f"\nTotal colonias: {total_colonias :,}")
print(f"3% = {total_colonias * .03 :,.1f} colonias")
delitos_50 = FGJ_alcaldias.groupby('colonia_hechos').count()['delito'].sort_values(ascending=False).head(50).values.sum()

print(f"Delitos (50 colonias): {delitos_50:,}")
print(f"Concentración (50 colonias) = {delitos_50 / total_delitos * 100 :.1f}%")
FGJ_alcaldias.groupby('colonia_hechos').count()['delito'].sort_values(ascending=False).plot(
    kind='line',
    color='green',
    title="Distribución (delitos por colonia)",
    xticks=[])

plt.xlabel('Colonias')
plt.ylabel('Delitos')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1718267900840515, 'fraction_numerical': 0.02819448877540164, 'mean_word_length': 8.4853683148335, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 53, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '460311', 'license': 'MIT', 'n_tokens_mistral': 4090, 'n_tokens_neox': 3763, 'n_words': 434, 'path': 'Crime Analysis CDMX.ipynb', 'repo_name': 'rperezme-data/Crime-Analysis-CDMX', 'size': '9399'}