<jupyter_start><jupyter_text>Set Up<jupyter_code># imports and juppyter environment settings
%run -i settings.py
# setup environment variables
%run -i setup.py
# To auto-reload modules in jupyter notebook (so that changes in files *.py doesn't require manual reloading):
# for auto-reloading external modules
# see http://stackoverflow.com/questions/1907993/autoreload-of-modules-in-ipython
%reload_ext autoreload
%autoreload 2

# you can now enable 2x images by just adding the line:
# see: https://gist.github.com/minrk/3301035
%config InlineBackend.figure_format = 'png'
file = f'{BASE_DIR}/data/circuit_court_2009_2019.csv.gz'
df = pd.read_csv(file, parse_dates =['offense_date'])
df.head(5)<jupyter_output><empty_output><jupyter_text>Summary Statistics<jupyter_code># In this example, the data frame is described and [‘object’] is passed to include parameter
# to see description of object series. [.20, .40, .60, .80] is passed to percentile parameter 
# to view the respective percentile of Numeric series.
# see: https://www.geeksforgeeks.org/python-pandas-dataframe-describe-method/
perc = [0.20, .40, .60, 0.80]
include = ['object', 'float', 'int']
df.describe(percentiles= perc, include=include).T<jupyter_output><empty_output><jupyter_text>Data Cleaning<jupyter_code># number of na values in each column
df.isna().sum()<jupyter_output><empty_output><jupyter_text>For our study, 'personId', 'offense_date', 'final_disposition', 'fips', 'race', 'gender', 'charge', 'fips_area' are mandatory columns.So removing all records where these field values are missing will be removed<jupyter_code>df.dropna(axis=0, subset=['final_disposition'], inplace=True)
df.dropna(axis=0, subset=['race'], inplace=True)
df.isna().sum()
# for the moment we consider case_class as important field, so we will impute missing value 'unknown'
df['ammended_charge'].fillna('unknown', inplace=True)
df.isna().sum()
duplicate_records = pd.DataFrame(df.duplicated(), columns=['isduplicate'])
duplicate_records = duplicate_records.reset_index()
duplicate_records.columns = [str(column) for column in duplicate_records.columns]
duplicate_records.set_index('index')
duplicate_records.groupby('isduplicate').count()<jupyter_output><empty_output><jupyter_text>Our dataset has 974935 duplicate records, so we purge these records from our dataset<jupyter_code>#drop duplicate records
df = df.drop_duplicates(['person_id', 'offense_date', 'final_disposition', 'fips', 'race', 'gender', 'charge', 'ammended_charge'])
len(df)<jupyter_output><empty_output><jupyter_text>df * We can see that hearing_date, fips, gender, charge_type and person_id do not have any missing data, however we need to address the missing data  for other columns.<jupyter_code>df.isna().sum()<jupyter_output><empty_output><jupyter_text>Per our domain expert if ammended_charge is reduced to **'DWI'** then the case is a candidate for expungement.<jupyter_code>dwi_ammended_charge_df =  df [df.ammended_charge.str.contains('DWI') == True]
len(dwi_ammended_charge_df)<jupyter_output><empty_output><jupyter_text>Our aim is to derive the cadidate cases for expungement based on hearing_results, let us examine the uniuqe values for hearing_result<jupyter_code># top 15 hearing results
hearing_result_counts = df['final_disposition'].value_counts()
subset = hearing_result_counts[:15]
sns.barplot(y=subset.index, x=subset.values)
# to 15 hearing results by gender
df_result_bygendger = df.groupby(['final_disposition', 'gender'])\
                        .size()\
                        .unstack()\
                        .fillna(0)\
                        .sort_values(['Female', 'Male'], ascending=False)
df_stacked = df_result_bygendger.head(15).stack()
df_stacked.name = 'total'
df_stacked= df_stacked.reset_index()
sns.barplot(x='total', y='final_disposition', hue='gender', data= df_stacked)<jupyter_output><empty_output><jupyter_text>Preprocessing Data  Adding fips_area name column<jupyter_code>#load fips code table
fips_file = 'reference-data/va-fips-codes.csv'
fips_df = pd.read_csv(fips_file)
fips_df = fips_df[['CountyFIPSCode', 'GUName']]
fips_df
#add fips_GUName
df = pd.merge(df,fips_df,left_on='fips', right_on='CountyFIPSCode',  how='left')\
    .drop(columns=['CountyFIPSCode'], axis=1)\
    .rename(columns={'GUName': 'fips_area'})
df<jupyter_output><empty_output><jupyter_text>Identifying the candidates for case expungement Assumptions maded based on domain expert input:1. If the final_disposition is any the following values 'Dismissed','Noile Prosequi','Not Guilty', 'Withdrawn', 'Not Found', 'No Indictment Presented', 'No Longer Under Advisement', 'Not True Bill' then the case is candidate for expungement.2. **TODO:** If the charges are ammended to DWI then also it can be a candidate for expungement if the person does not have any prior felony charges<jupyter_code>cand_list =['Dismissed','Noile Prosequi','Not Guilty', 'Withdrawn', 'Not Found', 'No Indictment Presented', 'No Longer Under Advisement', 'Not True Bill']
df['candidate'] = [1 if x in cand_list else 0 for x in df['final_disposition']]
df
df.groupby(['candidate']).count().head()
df.groupby(['candidate','race','gender']).count()
df.to_csv(
    PROCESSED_PATH + "district_court_2009_2019_cleansed.csv.gz",
    index=False,
    compression="gzip",
    header=True,
    quotechar='"',
    doublequote=True,
    line_terminator="\n",
)
delete_file(PROCESSED_PATH + "district_court_2009_2019.csv.gz")
#!jupyter nbconvert va_circuit_court_eda.ipynb --to pdf<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12518115942028984, 'fraction_numerical': 0.014673913043478261, 'mean_word_length': 6.254927726675427, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '668197', 'license': 'MIT', 'n_tokens_mistral': 1920, 'n_tokens_neox': 1836, 'n_words': 463, 'path': 'va_circuit_court_eda.ipynb', 'repo_name': 'mh4ey/va-case-expungement', 'size': '5520'}