app.py

import streamlit as st
import altair as alt
import pandas as pd

st.title('Evaluating Illinois’ Correctional Spending')
st.header('Brie McCabe & Jacob Fieldman')
st.subheader('IS 445: Final Project Pt.3')
#Actual Title


st.markdown("This website dives into the State of Illinois Department of Corrections and its spending. We can track where money is going and how much the state is spending over time. The State of Illinois spends over $1.1 Billion dollars every year on the Department of Corrections. Here, we can analyze where exactly that money is going.")

# Employee Pay Cleaning
df = pd.read_csv('State_Employee_Pay.csv')

df_copy = df.copy
df['Period Pay Rate'] = df['Period Pay Rate'].str.replace(",", "")
df['YTD Gross'] = df['YTD Gross'].str.replace(",", "")
df['Period Pay Rate'] = df['Period Pay Rate'].astype(int)
df['YTD Gross'] = df['YTD Gross'].astype(int)
df = df.dropna()
df = df[df['YTD Gross']!= 0]
df['Agency'] = df['Agency'].str.lower()
df['Agency Division'] = df['Agency Division'].str.lower()
df['Employee Name'] = df['Employee Name'].str.lower()
df['Position Title'] = df['Position Title'].str.lower()

df_c = df[df['Agency'] == 'corrections']



#jacobs stuff 
df_prisoner=pd.read_csv('https://huggingface.co/spaces/445final/Viz_for_Public/resolve/main/sept-2025-Prison.csv')
df_prisoner=df_prisoner.iloc[4:]
df_prisoner.columns = df_prisoner.iloc[0]      # set first row as column names
df_prisoner = df_prisoner.iloc[1:].reset_index(drop=True)  
df_prisoner = df_prisoner[df_prisoner.columns[~df_prisoner.columns.isna()]]
df_prisoner = df_prisoner.dropna().reset_index(drop=True)
grouped = df_prisoner.groupby("Parent Institution")["Name"].count()

# #the chart
# import altair as alt
# import pandas as pd

# # Count rows per Parent Institution
# counts = df_prisoner.groupby("Parent Institution").size().reset_index(name="b")

# source = {"values": counts.to_dict(orient="records")}

# select = alt.selection_point(name="select", on="click")
# highlight = alt.selection_point(name="highlight", on="pointerover", empty=False)

# stroke_width = (
#     alt.when(select).then(alt.value(2, empty=False))
#     .when(highlight).then(alt.value(1))
#     .otherwise(alt.value(0))
# )

# chart=alt.Chart(source, height=200).mark_bar(
#     fill="#4C78A8", stroke="black", cursor="pointer"
# ).encode(
#     x="Parent Institution:O",
#     y="b:Q",
    
#     fillOpacity=alt.when(select).then(alt.value(1)).otherwise(alt.value(0.3)),
#     strokeWidth=stroke_width,
# ).configure_scale(bandPaddingInner=0.2).add_params(select, highlight)

# chart


# Count rows per Parent Institution
#counts = df_prisoner.groupby("Parent Institution").size().reset_index(name="# of Inmates")

# Selection for click
#select = alt.selection_point(fields=['Parent Institution'], on='click')

# Chart without complicated nested strokeWidth
#chart = alt.Chart(counts, height=500).mark_bar(fill="#4C78A8", stroke="black", cursor="pointer").encode(
#    x="Parent Institution:O",
#    y="# of Inmates:Q",
#    fillOpacity=alt.condition(select, alt.value(1), alt.value(0.3)),
#    strokeWidth=alt.condition(select, alt.value(2), alt.value(0))
#).add_params(select)

#st.altair_chart(chart, use_container_width=True)


# annual=pd.read_csv('anual cost of prisoners.csv')
# lines=alt.Chart(annual).mark_line().encode(
#     x='Year',
#     y='Annual Cost of Incarceration Per Inmate'
# )




# lines


annual = pd.read_csv('acp.csv')
annual.columns = annual.columns.str.strip()


annual['AC'] = annual['AC'].replace('[\$,]', '', regex=True).astype(float)


lines = alt.Chart(annual).mark_line().encode(
    x=alt.X('Year:O', title='Year'),
    y=alt.Y('AC:Q', title='Average Cost per Inmate')).properties(
    width=500,
    height=400,
    title='Average Cost per Inmate Over Time').interactive()

annual.columns = annual.columns.str.strip()



# Create line chart
lines2 = alt.Chart(annual).mark_line().encode(
    x=alt.X('Year:O', title='Year'),
    y=alt.Y('AE:Q', title='Annual DOC Expenditures')).properties(
    width=500,
    height=400,
    title='Annual DOC Over Time'
).interactive()

col1, col2 = st.columns(2)

with col1:
    st.altair_chart(lines, use_container_width=True)

with col2:
    st.altair_chart(lines2, use_container_width=True)

st.markdown("The visualization above a brief overview of DOC spending as cost per inmate increases every year. The first visualization uses data from the IDOC (Illinois Department of Corrections) website where there is a report of the average annual cost of incarceration for one individual. Over the last 14 years, the annual cost of incarceration for an inmate in Illinois has more than doubled. In 2011, the cost was just over 20,000 dollars and in 2024, it costs over 52,000 dollars. The second visualization depicts the steady increase in annual expenditures by the DOC.")



#Brie

# cleaning
df_p = df_prisoner

df_p['Parent Institution'] = df_p['Parent Institution'].str.lower()


df_cc = df_c[
    df_c['Agency Division'].str.contains('correctional center', case=False, na=False) |
    (df_c['Agency Division'] == 'kewanee life skills re-entry center') |
    (df_c['Agency Division'] == 'joliet mental health corr center') |
    (df_c['Agency Division'] == 'western il correction center-mt sterling')]

cc_replacements = {
    'big muddy river  (ina}': 'big muddy river',
    'shawnee  (vienna)': 'shawnee', 
    'il river  (canton)': 'illinois river', 
    'southwestern il': 'southwestern illinois',
    "decatur women's": 'decatur',
    'graham  (hillsboro)':'graham',
    'logan  (lincoln)': 'logan',
    'hill  (galesburg)':'hill',
    'western il correction center-mt sterling':'western illinois',
    'kewanee life skills re-entry center':'kewanee life skills reentry center',
    'joliet mental health corr center':'joliet treatment center'}


df_cc['Agency Division'] = df_cc['Agency Division'].str.lower().str.replace('correctional center', '').str.strip()
df_cc['Agency Division'] = df_cc['Agency Division'].replace(cc_replacements)

df_c = df_cc
df_c['Agency Division'] = df_c['Agency Division'].str.title()
df_p['Parent Institution'] = df_p['Parent Institution'].str.title()
df_c['Position Title'] = df_c['Position Title'].str.title()

# Prison Counts df
df_cc_bc = df_cc.groupby('Agency Division').agg(employeeCount=('Employee Name', 'count')).reset_index()
df_p_bc = df_p.groupby('Parent Institution').agg(inmateCount = ('IDOC #', 'count')).reset_index()
df_cc_bc['Parent Institution'] = df_cc_bc['Agency Division']
prisonCounts = pd.merge(df_cc_bc, df_p_bc, on='Parent Institution')
del prisonCounts['Parent Institution']
prisonCounts['Agency Division'] = prisonCounts['Agency Division'].str.title()
prisonCounts = prisonCounts.rename(columns={'Agency Division': 'Institution'})


# Visualizaton
df_long = prisonCounts.melt(
    id_vars=['Institution'], 
    value_vars=['employeeCount', 'inmateCount'],
    var_name='CountType', 
    value_name='Count')

df_long['CountType'] = df_long['CountType'].replace({
    'employeeCount': 'Employees',
    'inmateCount': 'Inmates'})

# Selection for click
select = alt.selection_point(fields=['Institution'], on='click')


chart = alt.Chart(df_long).mark_bar(
    stroke="black", 
    cursor="pointer"
).encode(
    x=alt.X('Institution:N', title='Correctional Institution'),
    y='Count:Q',
    color=alt.Color('CountType:N', legend=alt.Legend(title="Count Type")),
    xOffset='CountType:N',
    fillOpacity=alt.condition(select, alt.value(1), alt.value(0.3)),
    strokeWidth=alt.condition(select, alt.value(2), alt.value(0))
).add_params(select).properties(
    title='Employee and Inmate Counts by Institution')

st.altair_chart(chart, use_container_width=True)

st.markdown("Currently, Illinois has over 24,000 incarcerated individuals. At an average of $52,000 per person, the total cost of incarceration is substantial. Beyond the direct costs of housing people in custody, the state must also fund the staffing and operation of correctional facilities.")
st.markdown("The visualization below shows the number of incarcerated individuals and staff across Illinois facilities, as well as the ratio of inmates to employees. By grouping the data by correctional facility, the graph provides a detailed view of how many people are connected to each facility.")

# Brie Employee Count by Facility

df1 = df_c

#df1['Correctional Institution'] = df1['Agency Division']

agency_division_list = df1['Agency Division'].unique().tolist()

# Selction
selected_division = st.selectbox('Correctional Institution:', options=agency_division_list, index=0)

# horizontal bar chart
def division_positions(selected_division):
    df_db = df1[df1['Agency Division'] == selected_division]
    
    bar_chart_data = df_db.groupby('Position Title').agg(
        employeeCount=('Employee Name', 'count'),
        meanYTDGross=('YTD Gross', 'mean')).reset_index()

    bar_chart_data = bar_chart_data.sort_values('meanYTDGross', ascending=False)

    chart_bar = alt.Chart(bar_chart_data).mark_bar().encode(
        x=alt.X('employeeCount', title = 'Employee Count'),
        y=alt.Y('Position Title:O', title='Position'),
        tooltip=['Position Title', 'employeeCount', alt.Tooltip(
            'meanYTDGross:Q', format='$,.00f', title='Avg YTD Gross Salary')]
    ).properties(title=f'Employee Count by Position in: {selected_division}', 
                 height=alt.Step(20)).interactive()
    
    return chart_bar

if selected_division:
    final_chart = division_positions(selected_division)
    st.altair_chart(final_chart, use_container_width=True)

st.markdown("This graph provides insight into the range of positions held by staff across Illinois correctional facilities. Using the dropdown menu at the top, users can select a specific facility of interest. By hovering over any bar, users can view the number of employees in that position as well as the average salary associated with it.This breakdown highlights the many professional roles required to operate a correctional facility, underscoring the scale and complexity of the staffing needed to keep these institutions running.")

#jacobs line chart salary over time
dfot=pd.read_csv('State_Employee_Pay_hired_after_1.1.2011_20251210.csv')

# Clean the salary column in case it has $ or commas
dfot['YTD Gross'] = df['YTD Gross'].replace('[\$,]', '', regex=True).astype(float)
dfot = dfot[dfot['Position Title']=='CORRECTIONAL OFFICER']

#dfot2 = dfot

#dfot2['Position Title'] = dfot2[dfot2['Position Title']=='CORRECTIONAL OFFICER'] 

dfot['Agency Division'] = dfot['Agency Division'].str.lower()


dfot = dfot[
    dfot['Agency Division'].str.contains('correctional center', case=False, na=False) |
    (dfot['Agency Division'] == 'kewanee life skills re-entry center') |
    (dfot['Agency Division'] == 'joliet mental health corr center') |
    (dfot['Agency Division'] == 'western il correction center-mt sterling')]

cc_replacements = {
    'big muddy river  (ina}': 'big muddy river',
    'shawnee  (vienna)': 'shawnee', 
    'il river  (canton)': 'illinois river', 
    'southwestern il': 'southwestern illinois',
    "decatur women's": 'decatur',
    'graham  (hillsboro)':'graham',
    'logan  (lincoln)': 'logan',
    'hill  (galesburg)':'hill',
    'western il correction center-mt sterling':'western illinois',
    'kewanee life skills re-entry center':'kewanee life skills reentry center',
    'joliet mental health corr center':'joliet treatment center'}


dfot['Agency Division'] = dfot['Agency Division'].str.lower().str.replace('correctional center', '').str.strip()
dfot['Agency Division'] = dfot['Agency Division'].replace(cc_replacements)
# Compute average salary per agency division per year
median_salary = dfot.groupby('Year').agg({'YTD Gross': 'mean'}).reset_index()

median_salary = median_salary[median_salary['Year']!= 2025]


#making list of correctional facilities I want



# # Create the line chart
# lines = alt.Chart(avg_salary).mark_line(point=True).encode(
#     x=alt.X('Year:O', title='Year'),            # treat Year as ordinal
#     y=alt.Y('YTD Gross:Q', title='Average Salary'),
#     color=alt.Color('Agency Division:N', title='Division'),
#     tooltip=['Year', 'Agency Division', 'YTD Gross']
# ).interactive()  # enables zooming and panning

# lines

filtered_df = median_salary

lines = alt.Chart(filtered_df).mark_line(point=True).encode(
    x=alt.X('Year:O', title='Year'),
    y=alt.Y('YTD Gross:Q', title='Average Salary'),
    tooltip=['Year', 'YTD Gross']).properties(title = 'Average Correctional Officer Salary Over Time').interactive()

lines

st.markdown("As shown in the previous visualization, Correctional Officer is the most common role across every state correctional facility. This chart builds on that insight by displaying the median Correctional Officer salary over time. By comparing salary trends across facilities and years, the chart allows viewers to better understand how the state invests in its largest segment of correctional staff.")

st.image(image = 'Screenshot 2025-12-10 183054.png', caption='Image from the John Howard Association')

st.markdown("The graph above is from the John Howard Association and displays the number of individuals incarcerated in 2024 grouped by sentence length. This shows that more than 75% of individuals will be incarcerated for 5+ years.")

st.markdown("The State of Illinois allocates a significant portion of its annual budget to the Department of Corrections. Rising per-person incarceration costs, along with increasing staffing and salary demands, continue to shape how much the state must spend to operate its correctional system.")
st.markdown("Given these trends, Illinois may benefit from reassessing how resources are distributed within the DOC. Investing more in therapists, social workers, educators, and other rehabilitation-focused staff rather than concentrating the budget primarily on officer roles could reduce long-term costs. Strengthening rehabilitation efforts has the potential to shorten incarceration periods, lower recidivism rates, and ultimately decrease the overall financial burden on the state.")

st.subheader('Sources')

url1 = "https://data.illinois.gov/Government-and-Public-Employees/State-Employee-Pay/iu6r-a89d/about_data"
url2 = "https://data.illinois.gov/Government-and-Public-Employees/State-Employee-Pay-hired-after-1-1-2011/2ke2-x724/about_data"
url3 = "https://idoc.illinois.gov/reportsandstatistics/financialimpactstatements.html"
url4 = "https://idoc.illinois.gov/reportsandstatistics/prison-population-data-sets.html"
url5 = "https://idoc.illinois.gov/reportsandstatistics/annualreports.html"
url6 = "https://www.thejha.org/idoc-data-analysis"

st.markdown("1.) Primary Dataset: [Current State Employee Pay Data](%s)" % url1)
st.markdown("2.) [State Employee Pay Hired After 1.1.2011 Data](%s)" % url2)
st.markdown("3.) [Financial Impact Statements](%s)" % url3)
st.markdown("4.) [2024 Prisoner Data](%s)" % url4)
st.markdown("5.) [Annual Reports](%s)" % url5)
st.markdown("6.) [The John Howard Association Chart](%s)" % url6)