Emergency Rental Assistance
This is currently a work in progress.
This project uses a public data set from the Emergency Rental Assistance Program in Mesa, Arizona. Funds were disbursed to renters impacted by the pandemic. Because of the nature of this program it is important that it be accessible to all who need it. For this reason, I took a different approach in this particular project to explore the possibility that accessibility may have been impacted. For example, is there anything about the online form that the data indicates could've caused any problems? I explore the data around the actual funding in a Tableau project elsewhere.
Because I worked with similar programs, I am aware that there are case workers and others who tend to follow up to clarify anything that isn't clear in the online form and to gather additional information and documentation. The exploration below is not to come to any conclusions about the reasons someone does or doesn't qualify, but to gather ideas that can be further explored with those working cases such as these.
City of Mesa Emergency Rental Assistance Program Public Data set from https://citydata.mesaaz.gov/Community-Services/Emergency-Rent-and-Utilities-Assistance-Program-PU/qtu7-zizs The Emergency Rental and Utility Assistance Program (erap) provides financial assistance to households financially impacted by COVID for rental and utility assistance to prevent eviction and homelessness and ended February 2023. The data below is no longer receiving updates.
Who Qualified:
- City of Mesa Resident (cannot assist if you live on a County Island in Mesa)
- Impacted financially during COVID (loss of job, reduced hours, medical expenses, etc.)
- any household member that has been unemployed for 90 days or more
- Annual household income is at or below 50% if area median income
- First time applying for assistance or previously assistance received was for less than 6 months.
Data Preparation
In Excel, I used LOWER and SUBSTITUTE (replacing spaces with an underscore) to convert the column headers to appropriate naming conventions.
Data Exploration
I explore the data set below, mainly trying to learn about the "dirty" data, rather than just trying to clean it up. I'm looking at errors in fields entered by the applicant, and inconsistencies across categories, along with relationships between them. We start by looking at a sample of the data, and how many rows the data set contains (18447). In trying to learn about the dirty data, I can't rule out that some were not serious applications (particularly incomplete ones), but perhaps an effort to learn more about the application by getting through it without supplying one's own information.
SELECT *
FROM erap.csv
LIMIT 10;SELECT COUNT(*)
FROM erap.csv;SELECT application_status, COUNT(*) AS count, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM erap.csv
GROUP BY application_status
ORDER BY count DESC;A breakdown of the different reasons for ineligibility can be useful for better understanding the results of the applications.
SELECT reason_ineligible, COUNT(*) AS count_reason, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM erap.csv
GROUP BY reason_ineligible
ORDER BY count_reason DESCN/A likely means that the applicant is eligible or it hasn't yet been determined that they are ineligible. Other and Unknown of course are unclear. Max Payments refers to an application after someone has already met the maximum of 18 months worth of payments.
We can get a count of the applicants who were not approved for funds where the reason is not obvious, and where the application status is not duplicate, "in review" or received.
SELECT COUNT(*) AS count
FROM erap.csv
WHERE REPLACE(total_approved_amount, ',', '')::float = 0
AND reason_ineligible NOT IN ('Max Payments - 18', 'Not Financially Impacted by COVID', 'Did Not Meet Income Requirements', 'Not a Mesa Resident')
AND application_status NOT IN ('Duplicate', 'In Review', 'Received');The data includes a few fields that were typed in manually rather than chosen among a few options. The first one I examine is the applicant city. I am interested in looking at what reasons applicants are deemed ineligible when they entered something other than the city of Mesa, considering that one would have to be a resident of Mesa to be eligible to receive funds.
This is a good opportunity to examine the numbers of each category of ineligibility reason.
--How many entered their city as anything other than "Mesa" and did that affect eligibility? --
SELECT COUNT(*) AS count_error_city, reason_ineligible
FROM erap.csv
WHERE applicant_city NOT LIKE 'Mesa'
GROUP BY reason_ineligible
ORDER BY count_error_city DESC;The majority are N/A which seems to indicate that they are in fact eligible. Many are Incomplete. It's likely that none of the other reasons would populate if the person never completed their application.
SELECT COUNT (DISTINCT applicant_city) as count_city
FROM erap.csv;For reference, we should know how many entries are not 'Mesa', 'MESA', or 'mesa'.
SELECT COUNT(applicant_city) AS count
FROM erap.csv
WHERE applicant_city NOT IN ('Mesa', 'MESA', 'mesa');This is a word cloud showing all values entered in city besides "Mesa", "MESA", and "mesa". Any values containing numbers are in a green color, and size correlates with frequency of the value.