CARES Act data: PPP, EIDL and more.
Data files can be downloaded from the DataKind Google Drive
Please either fork or make a development branch of the repo to contribute.
Please ask a fellow volunteer to review your code with a pull request before merging with the master branch! You can always ask @JohnMcCambridge or @kbmorales if you don't know who to ask to review!
bin/
for in production executable filesbin/pull_data.R
authenticates with Google Drive, downloads PPP flat filesbin/ppp_data_merge.R
reads in PPP flat files
code/
folder with individual project subfolders on the CARES act data. Enhancements people make can go here, grouped by "project". A project is any discreet enhancement to the data, like adding in NAICS code industry identifiers. All projects should be documented in the README. Project examples:code/NAICS/
is where scripts go for joining NAICS and PPP datacode/census_mapping/
for US census joins, mapping, etc.
docs/
for references, data dictionaries, manuals, etc. Each project should have an accompanyingdocs/project_name/
folderdata/
for raw data files that scripts rely on or that others would find useful--I think tidied data files should be uploaded to Google Drive to make it easier for others to use. Please organize data files roughly by topic! Please cite sources in the README!tests/
for each project's unit tests; i.e.,tests/NAICS/
All finalized code should be able to be run on the output of the setup scripts
in bin/
, or on a dataset read in as a CSV file created by cleaning code.
- PPP Loan Data: Small Business Administration's DropBox
- NAICS code dictionary US Census NAICS Files
- Data catlog of other sources assembled by Dave RM
Rows: 4,885,388
Potential duplicate rows: ~4,353 (still investigating)
Variables:
variable | n Missing | % Missing | Validation Notes |
---|---|---|---|
LoanRange | 4224170 | 86.5 | see notes |
BusinessName | 4224171 | 86.5 | no values for loan amounts under 150K |
Address | 4224170 | 86.5 | no values for loan amounts under 150K |
City | 1 | 0.0 | see notes |
State | 0 | 0.0 | see notes |
Zip | 224 | 0.0 | see notes |
NAICSCode | 133527 | 2.7 | validation pending |
BusinessType | 4723 | 0.1 | |
RaceEthnicity | 0 | 0.0 | 89.3% "Unanswered" |
Gender | 0 | 0.0 | 77.7% "Unanswered" |
Veteran | 0 | 0.0 | 84.7% "Unanswered" |
NonProfit | 4703708 | 96.3 | see notes |
JobsRetained | 324122 | 6.6 | see notes |
DateApproved | 0 | 0.0 | earliest: 2020-04-03 latest: 2020-06-30 |
Lender | 0 | 0.0 | |
CD | 0 | 0.0 | |
LoanAmount | 661218 | 13.5 | no values for loan amounts over 150K |
LoanRange
is missing from all state data, giving the 86.5% missing
number, but actual loan amount is included instead. To address this we
have created a computed field LoanRange_Unified
which assigns all precise
numeric loan values from the 'Under 150K' State files into compatible groups.
Within these groups, some values are improbably low e.g.:
LoanRange | n | % |
---|---|---|
Less than Zero | 1 | 0.0 |
Zero | 71 | 0.0 |
Up to $10 | 217 | 0.0 |
$100 - $1000 | 26318 | 0.5 |
Additionally we have created numeric fields for other calcuations, such
as ranking and summing across groups: LoanRangeMin
, LoanRangeMid
,
LoanRangeMax
.
City
is not a formalized field and contains open-text values, meaning it
cannot be used as-is for any kind of geo-coding or validation
State
contains a small number of odd values:
State | n | % | notes |
---|---|---|---|
AE | 1 | 0.0 | zipcode suggests this is indeed a military address outside the US |
FI | 1 | 0.0 | zipcode suggests this should be FL |
XX | 210 | 0.0 |
All non-missing values are in valid 5 digit format, but not all of those match to real zip codes. Further validation pending. Note also that just because a zip code is valid does not mean it can be mapped to a ZCAT (e.g., PO Box Zips)
Has only Y or NA values, and so can be assumed to be a required question, implying actual Missingness of 0%
contains some improbable values, and many values are Zero:
JobsRetained | n | % |
---|---|---|
Less than Zero | 7 | 0.0 |
Zero | 554146 | 11.3 |
Most of the data are "Not Answered" due to these questions being optional.
Adds NAICS industry identifiers to the PPP data. See the notebook