Report on Expungement Outcomes in the Virginia Court Data

Code for Charlottesville

Last updated: 10/3/2021

Table of contents

1. Introduction
2. Loading the data from our Postgres DB
3. How many records qualify as automatic, automatic (pending), petition, petition (pending), or not expungable, in every by year in the data?
4. How do these counts break down by race?
5. What are the most frequent reasons for each expungability outcome?
6. How do these reasons differ by race?

Introduction

Previously, we developed a program to auto-encode every record in the Virginia court data by expungability under the new law: either automatic, petition, automatic or petition pending a mandatory waiting period, or not eligible for expungement. We also automatically coded the reasons for each expungement decision, and phrased the reasons in the language of the new law. We then constructed a loop that applied these auto-encoders to each individuals' records in the Virginia court data. We saved the data in a Postgres database on our Oracle cloud virtual computers.

In this document, we load the data and summarize it in ways that provide answers to the following questions:

1. How many records qualify as automatic, automatic (pending), petition, petition (pending), or not expungeable, in every by year in the data.

2. How do these counts break down by race?

3. What are the most frequent reasons for each expungability outcome?

4. How do these reasons differ by race?

We begin by loading the following Python packages:

import sqlalchemy as sa
from sqlalchemy import create_engine
import psycopg2 as db
import pandas as pd
import numpy as np
import os
from matplotlib import pyplot as plt
import matplotlib.ticker as ticker
import seaborn as sns
import sidetable
pd.set_option('display.max_colwidth', None)

Loading the data from our Postgres DB

First we create a connection to the database:

postPass=os.environ["POSTGRES_PASS"]
try:
    conn = db.connect(host='localhost', database='expunge', user='jupyter', password=postPass, port='5432')
except:
    print("I am unable to connect to the database") 

cur = conn.cursor()
try:
    tables=cur.execute("select * from pg_catalog.pg_tables WHERE schemaname != 'information_schema' AND schemaname != 'pg_catalog';")
    print(cur)
    
except:
    print("I can't drop our test database!")

<cursor object at 0x7f8ca2c2a750; closed: 0>

Then we use this connection to query the entire dataset. This is memory intensive -- working wholly within SQL would be more efficient. But I choose to work in the Python environment to be able to use pandas and the Python plotting packages:

myquery = """
SELECT * FROM public.expunge
"""
courtdata = pd.read_sql(myquery, con=conn)
courtdata.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9054266 entries, 0 to 9054265
Data columns (total 28 columns):
 #   Column                       Dtype 
---  ------                       ----- 
 0   person_id                    object
 1   HearingDate                  object
 2   CodeSection                  object
 3   codesection                  object
 4   ChargeType                   object
 5   chargetype                   object
 6   Class                        object
 7   DispositionCode              object
 8   disposition                  object
 9   Plea                         object
 10  Race                         object
 11  Sex                          object
 12  fips                         int64 
 13  convictions                  object
 14  arrests                      object
 15  felony10                     object
 16  sevenyear                    object
 17  tenyear                      object
 18  within7                      object
 19  within10                     object
 20  class1_2                     object
 21  class3_4                     object
 22  expungable                   object
 23  old_expungable               object
 24  expungable_no_lifetimelimit  object
 25  reason                       object
 26  sameday                      object
 27  lifetime                     object
dtypes: int64(1), object(27)
memory usage: 1.9+ GB

len(courtdata['person_id'].unique())

3082954

#Under

The resulting data contain 9,054,266 distinct records from 3,082,954 different people over the time frame from 2000 to 2020. The first few rows of the data look like this:

courtdata.head()

	person_id	HearingDate	CodeSection	codesection	ChargeType	chargetype	Class	DispositionCode	disposition	Plea	...	within7	within10	class1_2	class3_4	expungable	old_expungable	expungable_no_lifetimelimit	reason	sameday	lifetime
0	292031000000162	2017-08-18	18.2-91	covered elsewhere	Felony	Felony	O	Dismissed	Dismissed	NA	...	TRUE	TRUE	FALSE	FALSE	Petition	TRUE	Petition	Dismissal of felony charges	FALSE	FALSE
1	195240000000019	2011-12-14	18.2-250	covered elsewhere	Felony	Felony	5	Nolle Prosequi	Dismissed	NA	...	FALSE	TRUE	FALSE	FALSE	Petition	TRUE	Petition	Dismissal of felony charges	FALSE	FALSE
2	34090000000256	2014-12-17	18.2-95	covered elsewhere	Felony	Felony	U	Nolle Prosequi	Dismissed	NA	...	TRUE	TRUE	FALSE	FALSE	Petition	TRUE	Petition	Dismissal of felony charges	FALSE	FALSE
3	34091000000145	2013-01-24	19.2-306	covered elsewhere	Felony	Felony	U	Dismissed	Dismissed	NA	...	FALSE	TRUE	FALSE	FALSE	Petition	TRUE	Petition	Dismissal of felony charges	FALSE	FALSE
4	244010000000958	2019-05-29	18.2-248	covered elsewhere	Felony	Felony	U	Nolle Prosequi	Dismissed	NA	...	TRUE	TRUE	FALSE	FALSE	Petition	TRUE	Petition	Dismissal of felony charges	FALSE	FALSE

5 rows × 28 columns

How many records qualify as automatic, automatic (pending), petition, petition (pending), or not expungable, in every by year in the data?

NA    8313136
Name: Plea, dtype: int64

First we extract year from the HearingDate column:

courtdata['year'] = pd.DatetimeIndex(pd.to_datetime(courtdata['HearingDate'], errors='coerce')).year

There were several errors in how hearing date was entered into the data, with some nonsensical, future years:

courtdata.year.value_counts()

2009.0    813349
2010.0    789518
2011.0    779835
2012.0    760602
2013.0    743072
2014.0    718450
2015.0    679561
2017.0    668354
2019.0    666773
2018.0    662967
2016.0    654794
2007.0    134757
2008.0    133051
2006.0    127121
2004.0    120752
2002.0    120492
2003.0    119054
2005.0    117721
2001.0    117505
2000.0    111797
2020.0     13227
2021.0       751
2022.0       261
2023.0       108
2024.0        78
2028.0        59
2025.0        58
2026.0        36
2029.0        29
2027.0        26
2031.0        14
2030.0        14
2033.0        11
2048.0         6
2040.0         6
2037.0         4
2045.0         4
2041.0         4
2044.0         4
2042.0         3
2065.0         3
2052.0         3
2058.0         3
2060.0         3
2038.0         3
2035.0         2
2049.0         2
2047.0         2
2034.0         2
2054.0         2
2032.0         2
2050.0         2
2043.0         2
2036.0         1
2051.0         1
2059.0         1
2062.0         1
2039.0         1
2055.0         1
Name: year, dtype: int64

We replace these future years with missing values for now, until we can dig into the data to see what went wrong with these codes:

courtdata.year[courtdata.year > 2020] = np.nan

/opt/tljh/user/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  """Entry point for launching an IPython kernel.

The following table lists raw counts of each expungability outcome by year:

pd.crosstab(courtdata.year, courtdata.expungable)

expungable	Automatic	Automatic (pending)	Not eligible	Petition	Petition (pending)
year
2000.0	13633	0	61602	36562	0
2001.0	12768	0	68663	36074	0
2002.0	11919	0	73100	35473	0
2003.0	10752	0	75154	33148	0
2004.0	8397	0	80403	31952	0
2005.0	7381	0	78876	31464	0
2006.0	7039	0	87114	32968	0
2007.0	7937	0	92179	34641	0
2008.0	6933	0	93220	32898	0
2009.0	241992	0	402583	168774	0
2010.0	228820	0	412903	147795	0
2011.0	226272	0	416232	127132	10199
2012.0	217066	0	418471	115079	9986
2013.0	209801	0	416822	106310	10139
2014.0	44211	170575	389841	91698	22125
2015.0	43395	166443	362852	85410	21461
2016.0	44051	162160	348354	80016	20213
2017.0	42513	173038	352786	77613	22404
2018.0	42147	177121	345373	74237	24089
2019.0	44868	190851	332028	71499	27527
2020.0	501	2436	8202	921	1167

And this table lists the row percents -- out of all records in each year, what percent falls within each expungability outcome?

rowtable = (pd.crosstab(courtdata.year, courtdata.expungable, normalize='index')*100).round(2).reset_index()
rowtable

expungable	year	Automatic	Automatic (pending)	Not eligible	Petition	Petition (pending)
0	2000.0	12.19	0.00	55.10	32.70	0.00
1	2001.0	10.87	0.00	58.43	30.70	0.00
2	2002.0	9.89	0.00	60.67	29.44	0.00
3	2003.0	9.03	0.00	63.13	27.84	0.00
4	2004.0	6.95	0.00	66.59	26.46	0.00
5	2005.0	6.27	0.00	67.00	26.73	0.00
6	2006.0	5.54	0.00	68.53	25.93	0.00
7	2007.0	5.89	0.00	68.40	25.71	0.00
8	2008.0	5.21	0.00	70.06	24.73	0.00
9	2009.0	29.75	0.00	49.50	20.75	0.00
10	2010.0	28.98	0.00	52.30	18.72	0.00
11	2011.0	29.02	0.00	53.37	16.30	1.31
12	2012.0	28.54	0.00	55.02	15.13	1.31
13	2013.0	28.23	0.00	56.09	14.31	1.36
14	2014.0	6.15	23.74	54.26	12.76	3.08
15	2015.0	6.39	24.49	53.40	12.57	3.16
16	2016.0	6.73	24.77	53.20	12.22	3.09
17	2017.0	6.36	25.89	52.78	11.61	3.35
18	2018.0	6.36	26.72	52.10	11.20	3.63
19	2019.0	6.73	28.62	49.80	10.72	4.13
20	2020.0	3.79	18.42	62.01	6.96	8.82

We can also plot these percentages over time:

lineplot = pd.melt(rowtable,
                  id_vars = ['year'],
                  value_vars = ['Automatic', 'Automatic (pending)',
                                'Not eligible', 'Petition', 'Petition (pending)'])

plt.figure(figsize=(12,7))
sns.lineplot(x='year', y='value', hue='expungable',data=lineplot)
plt.ylabel('Percent of total records in that year')
plt.xlabel('Year')
plt.title('Expungability Outcomes by Year')
plt.ylim(0,100)
plt.locator_params(axis="x", integer=True, tight=True, nbins=20)

How do these counts break down by race?

Race is coded in the data, but in an inconsistent way:

courtdata.stb.freq(['Race'])

	Race	count	percent	cumulative_count	cumulative_percent
0	White Caucasian(Non-Hispanic)	3517348	38.847412	3517348	38.847412
1	Black(Non-Hispanic)	2501329	27.625972	6018677	66.473384
2	White Caucasian (Non-Hispanic)	1280318	14.140495	7298995	80.613879
3	Black (Non-Hispanic)	1006077	11.111635	8305072	91.725514
4	Hispanic	291017	3.214142	8596089	94.939656
5	Other(Includes Not Applicable.. Unknown)	109833	1.213052	8705922	96.152709
6	White	101797	1.124299	8807719	97.277007
7	Asian Or Pacific Islander	88771	0.980433	8896490	98.257440
8	Black	71787	0.792853	8968277	99.050293
9	MISSING	31319	0.345903	8999596	99.396196
10	Unknown (Includes Not Applicable.. Unknown)	24917	0.275196	9024513	99.671392
11	Other (Includes Not Applicable.. Unknown)	18524	0.204589	9043037	99.875981
12	American Indian	9034	0.099776	9052071	99.975757
13	Unknown	1605	0.017726	9053676	99.993484
14	Asian or Pacific Islander	491	0.005423	9054167	99.998907
15	American Indian or Alaskan Native	65	0.000718	9054232	99.999624
16	American Indian Or Alaskan Native	34	0.000376	9054266	100.000000

We have to combine and collapse these categories to get a clear picture of how expungability outcomes depend on race:

replace_map = {'Black(Non-Hispanic)':'Black (Non-Hispanic)', 
               'White Caucasian(Non-Hispanic)':'White (Non-Hispanic)', 
               'Other(Includes Not Applicable.. Unknown)':'Other',
               'White Caucasian (Non-Hispanic)':'White (Non-Hispanic)',
               'Unknown (Includes Not Applicable.. Unknown)':'Other', 
               'NA':'Other',
               'Asian Or Pacific Islander':'Asian or Pacific Islander', 
               'Black (Non-Hispanic)':'Black (Non-Hispanic)', 
               'White':'White (Non-Hispanic)',
               'American Indian':'American Indian or Alaskan Native', 
               'Unknown':'Other',
               'Other (Includes Not Applicable.. Unknown)':'Other', 
               'Black':'Black (Non-Hispanic)',
               'American Indian or Alaskan Native':'American Indian or Alaskan Native',
               'American Indian Or Alaskan Native':'American Indian or Alaskan Native', 
               'Asian or Pacific Islander':'Asian or Pacific Islander'}
courtdata.Race = courtdata.Race.replace(replace_map)

courtdata.stb.freq(['Race'])

	Race	count	percent	cumulative_count	cumulative_percent
0	White (Non-Hispanic)	4899463	54.112205	4899463	54.112205
1	Black (Non-Hispanic)	3579193	39.530460	8478656	93.642665
2	Hispanic	291017	3.214142	8769673	96.856808
3	Other	154879	1.710564	8924552	98.567371
4	Asian or Pacific Islander	89262	0.985856	9013814	99.553227
5	MISSING	31319	0.345903	9045133	99.899130
6	American Indian or Alaskan Native	9133	0.100870	9054266	100.000000

We can now generate a cross-tabulation of race and expungability outcome. The following table contains row percents: of all records from a person of the given race, what percent falls within each expungability outcome?

rowtable = (pd.crosstab(courtdata.Race, courtdata.expungable, normalize='index')*100).round(2).reset_index()
rowtable

expungable	Race	Automatic	Automatic (pending)	Not eligible	Petition	Petition (pending)
0	American Indian or Alaskan Native	32.40	27.24	26.80	11.68	1.87
1	Asian or Pacific Islander	30.69	19.75	30.35	16.54	2.67
2	Black (Non-Hispanic)	11.70	8.96	61.23	16.56	1.55
3	Hispanic	29.88	27.48	31.17	10.24	1.23
4	MISSING	48.76	45.58	2.70	2.57	0.39
5	Other	25.36	31.78	29.01	11.73	2.12
6	White (Non-Hispanic)	17.99	11.40	52.26	16.21	2.13

We can plot these percents:

barplot = pd.melt(rowtable,
                  id_vars = ['Race'],
                  value_vars = ['Automatic', 'Automatic (pending)',
                                'Not eligible', 'Petition', 'Petition (pending)'])

plt.figure(figsize=(18, 6))
sns.barplot(x='Race', y='value', hue='expungable', data=barplot)

<AxesSubplot:xlabel='Race', ylabel='value'>

What are the most frequent reasons for each expungability outcome?

We can assess the most common reasons for each expungability decision:

reasons = pd.DataFrame(courtdata.groupby(['expungable', 'reason']).size())
reasons = reasons.rename({0:'count'}, axis=1)

autoex = reasons.query("expungable == 'Automatic'")
autoex = autoex.assign(percent = round(100*(autoex['count'] / sum(autoex['count'])), 2))

autopend = reasons.query("expungable == 'Automatic (pending)'")
autopend = autopend.assign(percent = round(100*(autopend['count'] / sum(autopend['count'])), 2))

petex = reasons.query("expungable == 'Petition'")
petex = petex.assign(percent = round(100*(petex['count'] / sum(petex['count'])), 2))

petpend = reasons.query("expungable == 'Petition (pending)'")
petpend = petpend.assign(percent = round(100*(petpend['count'] / sum(petpend['count'])), 2))

notex = reasons.query("expungable == 'Not eligible'")
notex = notex.assign(percent = round(100*(notex['count'] / sum(notex['count'])), 2))

autoex.sort_values('count', ascending=False)

		count	percent
expungable	reason
Automatic	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions within 7 years from disposition date	914798	62.13
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years and no convictions on the person's record	557424	37.86
	Deferred dismissal of misdemeanor charges covered under 19.2-392.6-A	180	0.01

autopend.sort_values('count', ascending=False)

		count	percent
expungable	reason
Automatic (pending)	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions since the disposition date. However, because the disposition date is within 7 years of the current date, the record is not yet eligible for expungement	1042696	100.0

petex.sort_values('count', ascending=False)

		count	percent
expungable	reason
Petition	Dismissal of misdemeanor charges, but with arrests or charges in the past 3 years	423682	29.19
	Dismissal of felony charges	412847	28.44
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years, but with convictions on the person's record	325715	22.44
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years, but with prior convictions on the person's record	98159	6.76
	Conviction or deferred dismissal of felony charges that are not ones listed under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind within 10 years from disposition date	95532	6.58
	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions within 7 years from disposition date	66140	4.56
	Deferred dismissal of misdemeanor charges not covered under 19.2-392.6-A	19315	1.33
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years and no convictions on the person's record	6840	0.47
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind within 7 years from disposition date	3392	0.23
	Dismissal of misdemeanor charges, but with convictions on the person's record	51	0.00
	Deferred dismissal of misdemeanor charges covered under 19.2-392.6-A	1	0.00

petpend.sort_values('count', ascending=False)

		count	percent
expungable	reason
Petition (pending)	Conviction or deferred dismissal of felony charges that are not ones listed under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind since disposition date. However, because the disposition date is within 10 years of the current date, the record is not yet eligible for expungement	93451	55.16
	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions since the disposition date. However, because the disposition date is within 7 years of the current date, the record is not yet eligible for expungement	74067	43.72
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind since the disposition date. However, because the disposition date is within 7 years of the current date, the record is not yet eligible for expungement	1911	1.13

notex.sort_values('count', ascending=False)

		count	percent
expungable	reason
Not eligible	Conviction of misdemeanor charges that are not excluded from expungement by 19.2-392.12, but with convictions of another kind within 7 years from disposition date	1205282	24.51
	Conviction or deferred dismissal of misdemeanor charges that are excluded from expungement by 19.2-392.12	1114690	22.67
	Conviction or deferred dismissal of felony charges, but with convictions within 10 years from disposition date	644911	13.11
	Dismissal of felony charges; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	639084	12.99
	Dismissal of misdemeanor charges, but with arrests or charges in the past 3 years; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	580747	11.81
	Conviction or deferred dismissal of felony charges, but with a felony conviction within the last 10 years	136923	2.78
	Conviction or deferred dismissal of felony charges, but with a prior conviction of a Class 1 or 2 felony or any other felony punishable by imprisonment for life	95236	1.94
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years, but with convictions on the person's record; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	88370	1.80
	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions since the disposition date. However, because the disposition date is within 7 years of the current date, the record is not yet eligible for expungement; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	68580	1.39
	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions within 7 years from disposition date; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	68477	1.39
	Conviction or deferred dismissal of felony charges that are ineligible for expungement under 19.2-392.12	58847	1.20
	Conviction or deferred dismissal of felony charges, but with a class 3 or 4 felony conviction within the past 20 years	57841	1.18
	Conviction or deferred dismissal of felony charges that are not ones listed under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind within 10 years from disposition date; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	47885	0.97
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years, but with prior convictions on the person's record; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	41568	0.85
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years and no convictions on the person's record; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	29800	0.61
	Conviction or deferred dismissal of felony charges that are not ones listed under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind since disposition date. However, because the disposition date is within 10 years of the current date, the record is not yet eligible for expungement; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	27441	0.56
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, but with felony convictions within the last 10 years	5578	0.11
	Deferred dismissal of misdemeanor charges not covered under 19.2-392.6-A; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	3793	0.08
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind since the disposition date. However, because the disposition date is within 7 years of the current date, the record is not yet eligible for expungement; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	1420	0.03
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind within 7 years from disposition date; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	896	0.02
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, but with a prior conviction for a class 3 or 4 felony within the past 20 years	502	0.01
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, but with a prior conviction for a class 1 or 2 felony or another felony punishable by imprisonment for life	190	0.00
	Deferred dismissal of misdemeanor charges covered under 19.2-392.6-A; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	2	0.00
	Dismissal of misdemeanor charges, but with convictions on the person's record; HOWEVER, the outcome is changed to not eligible because the lifetime limit of two expungements has been exceeded	2	0.00

How do these reasons differ by race?

reasons = pd.DataFrame(courtdata.groupby(['expungable', 'reason', 'Race']).size().unstack())
reasons = reasons.rename({0:'count'}, axis=1)

autoex = reasons.query("expungable == 'Automatic'")
autoex = (100. * autoex / autoex.sum()).round(0)

autopend = reasons.query("expungable == 'Automatic (pending)'")
autopend = (100. * autopend / autopend.sum()).round(0)

petex = reasons.query("expungable == 'Petition'")
petex = (100. * petex / petex.sum()).round(0)

petpend = reasons.query("expungable == 'Petition (pending)'")
petpend = (100. * petpend / petpend.sum()).round(0)

notex = reasons.query("expungable == 'Not eligible'")
notex = (100. * notex / notex.sum()).round(0)

autoex.sort_values('White (Non-Hispanic)', ascending=False)

	Race	American Indian or Alaskan Native	Asian or Pacific Islander	Black (Non-Hispanic)	Hispanic	MISSING	Other	White (Non-Hispanic)
expungable	reason
Automatic	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions within 7 years from disposition date	70.0	59.0	63.0	82.0	59.0	65.0	60.0
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years and no convictions on the person's record	30.0	41.0	37.0	18.0	41.0	35.0	40.0
	Deferred dismissal of misdemeanor charges covered under 19.2-392.6-A	NaN	NaN	0.0	0.0	NaN	0.0	0.0

autopend.sort_values('White (Non-Hispanic)', ascending=False)

	Race	American Indian or Alaskan Native	Asian or Pacific Islander	Black (Non-Hispanic)	Hispanic	MISSING	Other	White (Non-Hispanic)
expungable	reason
Automatic (pending)	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions since the disposition date. However, because the disposition date is within 7 years of the current date, the record is not yet eligible for expungement	100.0	100.0	100.0	100.0	100.0	100.0	100.0

petex.sort_values('White (Non-Hispanic)', ascending=False)

	Race	American Indian or Alaskan Native	Asian or Pacific Islander	Black (Non-Hispanic)	Hispanic	MISSING	Other	White (Non-Hispanic)
expungable	reason
Petition	Dismissal of misdemeanor charges, but with arrests or charges in the past 3 years	24.0	27.0	29.0	28.0	14.0	30.0	29.0
	Dismissal of felony charges	22.0	25.0	32.0	13.0	18.0	24.0	26.0
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years, but with convictions on the person's record	29.0	30.0	20.0	34.0	33.0	30.0	24.0
	Conviction or deferred dismissal of felony charges that are not ones listed under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind within 10 years from disposition date	8.0	5.0	7.0	9.0	13.0	3.0	7.0
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years, but with prior convictions on the person's record	5.0	4.0	7.0	3.0	1.0	5.0	7.0
	Conviction of misdemeanor charges listed in 19.2-392.6 B with no convictions within 7 years from disposition date	8.0	6.0	3.0	10.0	14.0	6.0	5.0
	Deferred dismissal of misdemeanor charges not covered under 19.2-392.6-A	2.0	2.0	1.0	3.0	7.0	2.0	1.0
	Dismissal of misdemeanor charges with no arrests or charges in the past 3 years and no convictions on the person's record	1.0	1.0	0.0	1.0	0.0	1.0	1.0
	Conviction of misdemeanor charges not listed in 19.2-392.6 A or B and not ineligible for expungement under 19.2-392.12, with no felonies within the last 10 years, no class 3 or 4 felony conviction within the past 20 years, no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of another kind within 7 years from disposition date	0.0	0.0	0.0	0.0	0.0	0.0	0.0
	Deferred dismissal of misdemeanor charges covered under 19.2-392.6-A	NaN	NaN	NaN	NaN	NaN	NaN	0.0
	Dismissal of misdemeanor charges, but with convictions on the person's record	NaN	NaN	0.0	0.0	NaN	NaN	0.0

petpend.sort_values('White (Non-Hispanic)', ascending=False)

	Race	American Indian or Alaskan Native	Asian or Pacific Islander	Black (Non-Hispanic)	Hispanic	Other	White (Non-Hispanic)
expungable	reason
Petition (pending)	Conviction of misdemeanor charges with no class 3 or 4 felony conviction within the past 20 years, no felony within the past 10 years, and no class 1 or 2 felony or any other felony punishable by imprisonment for life, but with convictions of another kind within 7 years from disposition date	91.0	87.0	74.0	95.0	91.0	77.0
	Not convicted, misdemeanor, but prior convictions on the person's record	7.0	11.0	24.0	3.0	7.0	21.0
	Conviction of misdemeanor charges other than those covered under 19.2-392.6-A, 19.2-392.6-B, and 19.2-392.12, with no class 3 or 4 felony conviction within the past 20 years, no felony within the past 10 years, and no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of any other kind within 7 years from disposition date	1.0	2.0	2.0	2.0	2.0	3.0
	Conviction of felony charges covered in section 19.2-392.12, but has been convicted of another felony within the past 10 years of the petition, with no class 3 or 4 felony conviction within the past 20 years, and no class 1 or 2 felony or any other felony punishable by imprisonment for life	NaN	NaN	NaN	NaN	NaN	0.0
	Not convicted, felony charge covered in 19.2-392.12	NaN	0.0	0.0	0.0	0.0	0.0
	Dismissal of misdemeanor charges with no prior convictions on the person's record, but with arrests or charges in the past 3 years	NaN	NaN	0.0	NaN	NaN	NaN

notex.sort_values('White (Non-Hispanic)', ascending=False)

	Race	American Indian or Alaskan Native	Asian or Pacific Islander	Black (Non-Hispanic)	Hispanic	Other	White (Non-Hispanic)
expungable	reason
Not eligible	Conviction and convicted of another felony within the past 10 years of the petition	61.0	50.0	54.0	56.0	50.0	58.0
	Not convicted, felony charge covered in a section other than 19.2-392.12	27.0	41.0	31.0	27.0	39.0	31.0
	Conviction with a prior class 3 or 4 felony conviction within the past 20 years	11.0	9.0	14.0	16.0	11.0	10.0
	Conviction of felony charges that are not covered in section 19.2-392.12	NaN	NaN	0.0	0.0	0.0	0.0
	Conviction of misdemeanor charges covered under 19.2-392.12, with no class 3 or 4 felony conviction within the past 20 years, no felony within the past 10 years, and no class 1 or 2 felony or any other felony punishable by imprisonment for life, and no convictions of any other kind within 7 years from disposition date	0.0	0.0	0.0	0.0	0.0	0.0
	Conviction of misdemeanor charges with no class 3 or 4 felony conviction within the past 20 years, no felony within the past 10 years, and no class 1 or 2 felony or any other felony punishable by imprisonment for life, but with convictions of another kind within 7 years from disposition date	0.0	0.0	0.0	0.0	0.0	0.0
	Conviction, previously convicted of a class 1 or 2 felony or any other felony punishable by imprisonment for life	0.0	0.0	1.0	0.0	0.0	0.0