Data pre-processing

Census Data

Data cover the entire Mexico City, but have a sub-unit (Manzana) that we do not consider. After importing, select AGEB, replace special character * with Nan values, select non-string objects and convert them into float data type. Save file.

import pandas as pd
import numpy as np

INEGI portal, inegi.org.mx, has been down for a while. Use the local version.

df = pd.read_csv('data/RESAGEBURB_09XLSX20.csv')
df.head(3)

df = df[df['NOM_LOC']=='Total AGEB urbana']
df = df.replace('*', np.nan)
df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2433 entries, 3 to 68915
Columns: 231 entries, CVEGEO to VPH_SINTIC
dtypes: int64(6), object(225)
memory usage: 4.3+ MB

Most of the entries are objects. We need to convert them in numbers.

df1 = df.iloc[:,:9]
df2 = df.iloc[:,9:]
df2 = df2.fillna(0).astype(float)

df = pd.concat([df1,df2], axis=1)

#save file
df.to_csv('data/census_data2020.csv', index = False)

VAW Data

VAW data of Mexico City is included in the crime victim dataset. This means that I have to identify which rows are significant to the project based on the description of the crime and the victim’s gender.

# Read data
vaw = pd.read_csv('data/victimas_completa_marzo_2022.csv')
vaw.head()

Inspect with info() method to get better data insight.

vaw.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 780439 entries, 0 to 780438
Data columns (total 22 columns):
 #   Column           Non-Null Count   Dtype  
---  ------           --------------   -----  
 0   idCarpeta        780439 non-null  float64
 1   Año_inicio       780438 non-null  float64
 2   Mes_inicio       780438 non-null  object 
 3   FechaInicio      780438 non-null  object 
 4   Delito           780439 non-null  object 
 5   Categoria        780439 non-null  object 
 6   Sexo             628287 non-null  object 
 7   Edad             495406 non-null  float64
 8   TipoPersona      774405 non-null  object 
 9   CalidadJuridica  780438 non-null  object 
 10  competencia      780439 non-null  object 
 11  Año_hecho        780120 non-null  float64
 12  Mes_hecho        780120 non-null  object 
 13  FechaHecho       780120 non-null  object 
 14  HoraHecho        780130 non-null  object 
 15  HoraInicio       780438 non-null  object 
 16  AlcaldiaHechos   779234 non-null  object 
 17  ColoniaHechos    745330 non-null  object 
 18  Calle_hechos     778237 non-null  object 
 19  Calle_hechos2    280532 non-null  object 
 20  latitud          745544 non-null  float64
 21  longitud         745542 non-null  float64
dtypes: float64(6), object(16)
memory usage: 131.0+ MB

There are 679 500 observations, which are quite a lot! Notice that the non-null values of latitude and longitude are less than the total entries. Therefore, drop all rows that have a NaN value in these columns and run a preliminary sub-selection by keeping female victims and latest year available.

From the data structure, I know that “Ano_inicio” field corresponds to the opening date of the investigation, while “Ano_hecho” represents the date on which the crime was committed. Then, go for the second.

# drop Nan values
vaw = vaw.dropna(subset=['latitud', 'longitud'])

# sort unique years in the data
year = sorted(vaw['Año_hecho'].unique())
print("Last year available: ", year[-1])

Last year available:  2022.0

Filter data accordingly

vaw = vaw[vaw['Año_hecho']== 2021]
vaw = vaw[vaw['Sexo']=='Femenino']

Abortion in Mexico: a Crime?

Since 2021, abortion in Mexico is no longer a crime, although its legalisation still varies by state (source).

Yet, in our data it still labeled as a crime:

vaw[vaw['Delito']== 'ABORTO'].sample(1)

Considering abortion as a crime is beyond the scope of this project, but you too may find this interesting.

Back to Data Preparation

Select vaw crimes that match with the strings “sexual, domestic, femicide” and then print the results.

vaw = vaw[vaw['Delito'].str.contains('sexual|familiar|feminicidio', case=False)]

print('List of crimes after text query:\n', vaw['Delito'].unique())

List of crimes after text query:
 ['VIOLENCIA FAMILIAR' 'ABUSO SEXUAL' 'ACOSO SEXUAL'
 'CONTRA LA INTIMIDAD SEXUAL' 'ACOSO SEXUAL AGRAVADO EN CONTRA DE MENORES'
 'FEMINICIDIO' 'FEMINICIDIO POR ARMA BLANCA' 'TENTATIVA DE FEMINICIDIO'
 'FEMINICIDIO POR DISPARO DE ARMA DE FUEGO'
 'PRIVACION DE LA LIBERTAD PERSONAL (REALIZAR ACTO SEXUAL)'
 'FEMINICIDIO POR GOLPES']

Using str.contains() as filter method in data preparation is highly not recommended because the chance of missing important information is real. For example, strings can have special characters. In this case, I didn’t find any viable alternative.

Let’s now begin to translate the DataFrame from Spanish to English starting from the columns names:

# select columns and rename
vaw = vaw[['FechaHecho', 'Delito', 'latitud', 'longitud']]
vaw.columns = ['Time', 'Crime', 'latitude', 'longitude']

vaw

and continuing with the body. List all the values for ‘Crime’:

vaw['Crime'].unique()

array(['VIOLENCIA FAMILIAR', 'ABUSO SEXUAL', 'ACOSO SEXUAL',
       'CONTRA LA INTIMIDAD SEXUAL',
       'ACOSO SEXUAL AGRAVADO EN CONTRA DE MENORES', 'FEMINICIDIO',
       'FEMINICIDIO POR ARMA BLANCA', 'TENTATIVA DE FEMINICIDIO',
       'FEMINICIDIO POR DISPARO DE ARMA DE FUEGO',
       'PRIVACION DE LA LIBERTAD PERSONAL (REALIZAR ACTO SEXUAL)',
       'FEMINICIDIO POR GOLPES'], dtype=object)

Several types of ‘FEMICIDE’ are in. I use a simple regular expression to join them and replace them with the English word.

vaw['Crime'] = vaw['Crime'].str.replace(r'.*FEMINICIDIO.*', r'FEMICIDE', regex = True)

Conclude the translation for the other crime types.

from_text = ['VIOLENCIA FAMILIAR', 'ABUSO SEXUAL', 'ACOSO SEXUAL',
             'CONTRA LA INTIMIDAD SEXUAL', 'ACOSO SEXUAL AGRAVADO EN CONTRA DE MENORES',
             'FEMICIDE', 'PRIVACION DE LA LIBERTAD PERSONAL (REALIZAR ACTO SEXUAL)']

replace_with = ['DOMESTIC VIOLENCE', 'SEXUAL ABUSE', 'SEXUAL HARASSMENT',
        'AGAINST SEXUAL INTIMACY', 'AGGRAVATED SEXUAL HARASSMENT AGAINST MINORS', 'FEMICIDE',
        'DEPRIVATION OF PERSONAL LIBERTY (PERFORMING A SEXUAL ACT)']

vaw = vaw.replace(dict(zip(from_text, replace_with)))

vaw.head()

#save file
vaw.to_csv('data/vaw_filtered.csv', index = False)

Neighbourhood

Add to neighbourhood variables the total population per unit by merging with ‘Population per Neighbourhood’.

• Date source:

  CDMX neighborhoods - Mexico City Open Data Portal

• Data Structure:

  Structure of the data is described in a separate excel file download here

import geopandas as gpd

# Read the data
gdf = gpd.read_file('data/alcaldías_cdmx/alcaldias_cdmx.shp')
gdf = gdf.rename(columns = {'nomgeo':'NOMGEO'})
#gdf.head(5)

Population per Neighbourhood

• Date source:

  CDMX Population per Neighbourhood - Mexico City Open Data Portal

• Data Structure:

  Structure of the data is described in a separate excel file download here

df = pd.read_csv('data/poblacion_total_tasa_crecimiento_1.1.csv')
df = df[df['Año'] == 2020]
df = df.reset_index(drop=True)
#df

# drop the last summary row
df = df.drop([16])
df = df.rename(columns = {'Alcaldia':'NOMGEO', 'Población total': 'POBTOT' })
df = df.apply(lambda x: x.astype(str).str.upper())

merge = gdf.merge(df, on='NOMGEO')
merge = merge.rename(columns = {'cvegeo':'CVEGEO'})
merge['POBTOT'] = merge['POBTOT'].str.replace(',', '').astype(int)

gdf = merge[['CVEGEO', 'NOMGEO', 'POBTOT', 'geometry']]

# Write to GeoJSON

gdf.to_file("data/CDMX_municipalities.gjson", driver="GeoJSON")