<jupyter_start><jupyter_text>Clean Slate: Estimating offenses eligible for expungement under varying conditions> Prepared by [<NAME>](https://github.com/laurafeeney) for Code for Boston's [Clean Slate project](https://github.com/codeforboston/clean-slate). PurposeThis notebook is an alternative to [MA_Data-2_mergecharges](https://github.com/codeforboston/clean-slate/blob/master/analyses/notebooks/MA_Data-2_MergeCharges.ipynb). That notebook creates duplicates in the charges because chapter & section do not uniquely identify expungability. Ideally we would create an updated "Master Crime List" and update the full data flow. This is a crutch in the meantime, since we have expungability for all offenses for the NW and suffolk data that we currently have. This notebook will also clean up the individual level data.-----<jupyter_code>import pandas as pd
import numpy as np
import regex as re
import glob, os
# individual-level data from NW district. This is as raw as possible.

nw_ind = pd.read_csv('../../data/raw/nw.csv', encoding='cp1252') 
nw_ind.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 75725 entries, 0 to 75724
Data columns (total 10 columns):
 #   Column          Non-Null Count  Dtype  
---  ------          --------------  -----  
 0   Person ID       75725 non-null  object 
 1   Court           75725 non-null  object 
 2   Offense Date    74915 non-null  object 
 3   Age at Offense  74783 non-null  float64
 4   Filed           75725 non-null  object 
 5   Status          75725 non-null  object 
 6   Count           75725 non-null  int64  
 7   Charge          75725 non-null  object 
 8   Disposition     72259 non-null  object 
 9   Dispo Date      71881 non-null  object 
dtypes: float64(1), int64(1), object(8)
memory usage: 5.8+ MB<jupyter_text>Add column for CMRCreate a column indicating if the offense is against the Code of Massachusetts Regulations (CMR) rather than a criminal violation. These are things like traffic violations, watershed violations, headlights issues, aftermarket tinting on a car, not having a hunting license, car registration issues etc. Unclear whether these should be included in the analysis<jupyter_code>nw_ind['CMRoffense'] = None
nw_ind.loc[nw_ind['Charge'].str.contains("CMR"), 'CMRoffense'] = 'yes'
nw_ind.CMRoffense.fillna("no", inplace=True)
nw_ind.loc[nw_ind['CMRoffense']=='yes']['Charge'].value_counts()<jupyter_output><empty_output><jupyter_text>read in datasets with expungability infoprosecution_charges_detailed is a processed file with some expungement information. This file was based on the prosecution_northwestern and prosecution_suff originally created by [MA_Data-2_mergecharges](https://github.com/codeforboston/clean-slate/blob/master/analyses/notebooks/MA_Data-2_MergeCharges.ipynb). We did further processing in R in order to clean up the expungability column and remove duplicates.It also has an extra_criteria column to show what, beyond chapter & section, is needed to determine expungability, and dummy vars for sex and murder offenses to help with later analysis.<jupyter_code>PCD = pd.read_csv('../../data/processed/prosecution_charges_detailed.csv', encoding='cp1252') 
PCD.rename(columns={"Expungeable.":"Expungeable"}, inplace=True)
columns = ['Charge', 'Chapter', 'Section', 'Expungeable', 'sex', 'murder', 'extra_criteria']
PCD = PCD[columns]
PCD.info()
# This sheet fills in the missing information from the PCD file. This was manually filled in based on examination of the charges
# vs the statute, and confirmed in conversation with Sana from cfjj in August 2020

addtl_exp = pd.read_csv('../../data/raw/missing_expungeability_08-02.csv', encoding='cp1252') 
addtl_exp.rename(columns={"Expungeable.":"Expungeable"}, inplace=True)
columns = ['Charge', 'Expungeable', 
           'Reason not expungeable', 'Analysis notes']
addtl_exp = addtl_exp[columns]
addtl_exp['Expungeable'].replace({'yes': 'Yes', 'no': 'No', 'na': 'NotApplicable'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Merge NW data with expungabilityThis step will be the most messy. Hopefully some updates to the data pipeline will be made and this will be simplified.1. merge individual level data (nw_ind) with the prosecution_charges_detailed (PCD) which has most data filled in 2. merge the resulting data set with the manually filled information on expungability (addtl_exp)At some point both 1 and 2 will be replaced by mering individual data with the Master Crime List, once that is the most up to date source of which offenses may be expunged.Note, we cannot use the Suffolk county data to answer the questions because it does not have age.<jupyter_code>nw_merged = nw_ind.merge(PCD, on='Charge', how='left')
nw_merged = nw_merged.merge(addtl_exp, on='Charge', how='left')

# combine the expungability information from the two data sets

nw_merged['Expungeable'] = nw_merged['Expungeable_x']
nw_merged.Expungeable.fillna(nw_merged.Expungeable_y, inplace=True)
nw_merged = nw_merged.drop(columns = ['Expungeable_x', 'Expungeable_y'])
nw_merged['Expungeable'].value_counts(dropna=False)
pd.crosstab(nw_merged['Expungeable'], nw_merged['CMRoffense'])
nw_merged.loc[(nw_merged['Expungeable'] == "Yes") & (nw_merged['CMRoffense'] == "yes"), ['Expungeable']] = "NA"

# what is still missing?

nw_merged.loc[nw_merged.Expungeable.isnull()]['Charge'].value_counts(dropna = False)
nw_merged.loc[(nw_merged['Charge'] == "BURGLARY, UNARMED & ASSAULT c266 Â§14"),['Expungeable']] = "No" # not expungealbe

# Don't have enough info on what crime was attempted in order to determine expungability. These aren't a high %, and
# many may be purged out through the other reasons an offense may not be expungable. 

nw_merged.loc[(nw_merged['Charge'] == "ATTEMPT TO COMMIT CRIME c274 Â§6"),['Expungeable']] = "Attempt" # need more info 
nw_merged.loc[(nw_merged['Charge'] == "ATTEMPT TO COMMIT CRIME, HABITUAL c274 Â§6"),['Expungeable']] = "Attempt" # need more info
nw_merged['Expungeable'].value_counts(dropna=False)
nw_merged.to_csv('../../data/processed/merged_nw.csv', index=False)<jupyter_output><empty_output><jupyter_text>Merge suffolk data with expungabilityRepeat the steps above. Note, we cannot use the Suffolk county data to answer the questions because it does not have age.<jupyter_code># individual-level data from NW district. This is as raw as possible.

suff_ind = pd.read_csv('../../data/raw/suff.csv', encoding='cp1252') 
suff_ind.info()
suff_ind['CMRoffense'] = None
suff_ind.loc[suff_ind['Charge'].str.contains("CMR"), 'CMRoffense'] = 'yes'
suff_ind.CMRoffense.fillna("no", inplace=True)
#suff_ind.loc[suff_ind['CMRoffense']=='yes']['Charge'].value_counts()
suff_merged = suff_ind.merge(PCD, on='Charge', how='left')
suff_merged = suff_merged.merge(addtl_exp, on='Charge', how='left')

# combine the expungability information from the two data sets

suff_merged['Expungeable'] = suff_merged['Expungeable_x']
suff_merged.Expungeable.fillna(suff_merged.Expungeable_y, inplace=True)
suff_merged = suff_merged.drop(columns = ['Expungeable_x', 'Expungeable_y'])
suff_merged['Expungeable'].value_counts(dropna=False)
pd.crosstab(suff_merged['Expungeable'], suff_merged['CMRoffense'])
# what is still missing?

suff_merged.loc[suff_merged.Expungeable.isnull()]['Charge'].value_counts(dropna = False)
suff_merged.loc[(suff_merged['Charge'] == "BURGLARY, UNARMED & ASSAULT c266 Â§14"),['Expungeable']] = "No" # not expungealbe
suff_merged.loc[(suff_merged['Charge'] == "ATTEMPT TO COMMIT CRIME c274 Â§6"),['Expungeable']] = "Attempt" # need more info 
suff_merged['Expungeable'].value_counts(dropna=False)
suff_merged.to_csv('../../data/processed/merged_suff.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1170143838279124, 'fraction_numerical': 0.018141764934560062, 'mean_word_length': 5.480268681780017, 'pattern_counts': {'":': 2, '<': 22, '<?xml version=': 0, '>': 23, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '536700', 'license': 'MIT', 'n_tokens_mistral': 2675, 'n_tokens_neox': 2440, 'n_words': 725, 'path': 'notebooks/MA_Data-2_MergeCharges_alt.ipynb', 'repo_name': 'actsao/clean-slate-data', 'size': '7723'}