datakind-dc / cares Goto Github PK
View Code? Open in Web Editor NEWThis project forked from johnmccambridge/cares
US CARES Act Payment Protection Program data, cleaned for analysis
License: GNU General Public License v3.0
This project forked from johnmccambridge/cares
US CARES Act Payment Protection Program data, cleaned for analysis
License: GNU General Public License v3.0
posted by @JohnMcCambridge
"Then some loan information is just wrong. The loan listed for Ford’s Hometown Services Inc has the company listed at 549 Grove St in Hartford, CT. But that address doesn’t exist. The company’s website says that it’s located 60 miles (100 km) away at 549 Grove St in Worcester, MA.
The Zip code listed for a loan for La Jolla Dentistry in San Diego is 91121, a Zip code that is for Pasadena, California, 110 miles away. The correct Zip code is 92121." via https://qz.com/1878225/heres-what-we-know-is-wrong-with-the-ppp-data/
Proposing a system like:
bin/
for in production executable files (like reading in the PPP data)docs/
for references, data dictionaries, manuals, etc.data/
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 cite sources in the README!code/
folder with individual project subfolders on the CARES act data. Enhancements people make can go here. Contributions should be documented in the README. For example:
code/NAICS/
is where scripts go for joining NAICS and PPP datacode/project_name/
for the next project, etc.tests/
for each project's testsbut open to any organizational structure or documentation scheme!
All finalized code should be able to be run on the output of the ppp data script.R
file @JohnMcCambridge contributed, or on a dataframe as the result of reading in a CSV file of that data
0808 is a fully refreshed data set, from what I can see. It excludes all cancelled or refunded loans according to the data sheet that comes with it. Therefore we should work from this new dataset for all core analyses.
The old dataset does provide some interesting opportunities for diffing. However, some of the changes in the new dataset are simply fixes to the old dataset, and of course neither contains any unique identifying keys for each loan, so a true diff on a loan-to-loan basis is not possible
@kbmorales
I am worried this file has repeats of data already present in the individual states/territories folders.
@JohnMcCambridge commented 2 days ago
Is this a merge issue or a data issue on their part. I presume a data issue, because by the definition of the files the individual state/territories should only be up to 150k. Can you share some examples we can test?
@kbmorales commented 2 days ago
I think you're right. I found around 4k duplicates in the data but it doesn't seem to be from the 150k file.
@JohnMcCambridge commented 2 days ago •
If we just use duplicated I'm not convinced we are going to drop true dupes, especially if applied to the <150k files. It would seem entirely possible that an entry in there could be identical to another row, but be a legitimate row (same loan amount, to a similar business in the same zip code, for example). Keeping in mind especially many columns have NAs it's quite easy for a false dupe to appear based on only the barest of detail (loan amount + zip code, basically). 3860cfa
@kbmorales commented 2 days ago
That's a good point, but it'd also be based on the other variables with high completeness (DateApproved, NAICSCode, Lender, and JobsRetained being the more information-rich ones), so I'm less concerned about them not being true duplicates.
While we can't confirm without the BusinessName, I personally feel fine about removing dupes with duplicated but I can retain them for now.
@JohnMcCambridge commented 2 days ago
For the baseline script I would suggest we stick with a minimalist/'do no harm' principle. We could also create an 'add-on' script which applies more maximalist interventions, like row drops (rather than flagging of problematic rows) and de-duping. That way users can decide which version to use when data diving.
@kbmorales
It'd be great to have a pipe script that adds on latest census data by zip code (or even congressional district 'CD')
@JohnMcCambridge commented
Agreed. As of now I'm having difficulty finding open-access ZIP code level census data.
posted by @JohnMcCambridge
Add functionality to see detailed information when mousing over certain geography (something like this). Detailed info we might want to include on mouseover are: name of geography, numeric value of loan amount, numeric value of demographic variable. This SO thread might be helpful as a starting point: https://stackoverflow.com/questions/30964020/popup-when-hover-with-leaflet-in-r
Related files:
Need to edit color bar legend to display value ranges, instead of percentile ranges, that correspond to each color. Value ranges will need to be calculated from percentile ranges based on the demographic variable selected.
Related files:
CD values in latest 0808 data don't seem coherent: some loans in e.g., FL state have non-FL CD values!
posted by @JohnMcCambridge
"There are 1,182 loans where numeric digits appear in the city field. Some of those are clearly spill overs or duplication from the address field. On 198 loan listings the city field contains an office suite number.
Quartz was able to identify 842 loans where what appears to be a name associated with the loan is listed in the city field. For 781 of those, the loaned amount was less than $150,000 which meant the recipients identity was intended to be withheld by the SBA. This error appears 824 times on loans processed by Bank of America.
A loan listed under Morgan-Keller Inc. says the company is at 70 THOMAS JOHNSON DRIVE in the city of SUITE 200 FREDERICK, MD rather than 70 Thomas Johnson Drive, Suite 200 in the city of Frederick, MD, as their website indicates.
A loan listed under Volta Power Systems LLC has its location listed as SUPERIOR CT in the city of 12550 HOLLAND, MI. On what appears to be the company’s website, a contact address of 12550 Superior Ct. Holland, MI is listed.
For 600 loans the city field contains a five-digit number. For 519 loans, that number matches the listed Zip code. In the loans where those fields don’t match, there are clearly data errors. A loan given to an unnamed business with an address at JFK Airport in New York is listed as being in Michigan. Its zip code is listed as 48851. It’s certainly not a coincidence that the industry code for “Freight Transportation Arrangement” is 488510." via https://qz.com/1878225/heres-what-we-know-is-wrong-with-the-ppp-data/
Perhaps sourced via e.g., https://factba.se/sba-loans#methodology which covers loans to public firms, based on 8-K trawling.
We know any entries in 0808 dataset that have record dates prior to the first data set being circulated should be 'original' values, and so we can do a like-for-like comparison of those two datasets for that specific period with much greater confidence.
posted by @JohnMcCambridge
posted by @JohnMcCambridge
posted by @JohnMcCambridge
"For instance the most common street address for a business receiving a loan is “PO BOX” without a street address or box number." via https://qz.com/1878225/heres-what-we-know-is-wrong-with-the-ppp-data/
posted by @JohnMcCambridge
Set up to download from google drive
Be good to have this already in the data ready to roll
Check if the source filename (e.g., "PPP Data up to 150k - AZ.csv") matches to the values in the State
variable (e.g., State == "AK"
)
I've added data_dictionary code to test this, results are about as expected:
Allowing us to identify e.g., ZIP codes with large/small numbers of, average values or, or total loan values. See my commit here for this functionality: JohnMcCambridge@def9780
posted by @JohnMcCambridge
process Lender
across unified dataset and cross check against real bank names, and reduce to smallest possible set of consistent names
I am seeing evidence that some (all?) of the unmatched codes may be older, e.g., 722110: that's a valid 2007 NAICS code
it's possible different lenders use different code-years, or perhaps everyone is using old codes
It might be best to iterate through and if a match fails, go to the next newest code year list?
As the project matures, it would be wise to fully automate the core scripts in /bin so that a user can run them without any intervention. This would ideally include pulling of the data, not just processing. This will likely require the addition of some non-R layer.
As of now, one simple issue that cannot be fully resolved is the setting of a valid working directory for the user. Attempts at using the package here
fall short because it locks itself onto the working directory set when the library is first loaded (meaning if a user is warned they need to change their working directory and they comply, later uses of the here()
function will not behave as they might expect, since it will still be holding on to whatever the original working directory was). Other options such as relying on .Rproj files would introduce a RStudio dependency.
All of this can be resolved nicely by adding a simple non-R layer atop the final codebase.
For an example of the issue: https://hrdag.org/tech-notes/harmful.html#the-working-directory-thing
Created a data dictionary that describes what is in the data, degree of missingness, and sense of values within each variable
Any thought to put code into R package structure, even if goal is not to create a package?
Add a scatterplot to provide another way of looking at the correlation between loan amount and demographic variables. On the scatter plot, one axis would represent the demographic variable, one axis would represent the loan amount, and the points would represent individual congressional districs/counties/states, etc.
Related files:
posted by @JohnMcCambridge
"The field for business type contains checkable errors too. Excluding organizations listed as non-profits, there are 2,627 loans to organizations with “LLP” in their names which are not listed as a limited liability partnership under the business type. Similarly there are 21,287 loans to organizations with “LLC” in their names which aren’t listed as a limited liability company." via https://qz.com/1878225/heres-what-we-know-is-wrong-with-the-ppp-data/
Need to add a legend for circle sizes (which represents loan amounts). Might be a bit tricky because the circle sizes change with zoom level. We can either fix circle sizes on the map or somehow update circle sizes on the legend as we zoom, or use another creative solution.
Related files:
Set map boundary box so that the desired geographic area (all U.S. or a specific state) takes up the maximum space on the map.
Related files:
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.