Import Ford Foundation grants data into Donations List Website (CSV available)

[vipulnaik]

Grants page has downloadable CSV, see http://www.fordfoundation.org/work/our-grants/grants-database/grants-all CSV link: https://data.fordfoundation.org/v1/grants/search-csv?&MinAmount=0&MaxAmount=30000000&FiscalYearStart=2016&FiscalYearEnd=2017&Search&SortBy=1&SortDirection=0&IsBuild=

[riceissa]

The CSV link above has FiscalYearStart=2016, but it is possible to broaden that to start from 2006: https://data.fordfoundation.org/v1/grants/search-csv?&MinAmount=0&MaxAmount=30000000&FiscalYearStart=2006&FiscalYearEnd=2017&Search&SortBy=1&SortDirection=0&IsBuild=

[riceissa]

I've started on this at https://github.com/riceissa/ford-foundation

Some observations:

• The CSV export doesn't seem to include the "ISSUE AREAS AND GRANTMAKING STRATEGIES" or "TOPICS" columns seen on the website version. However, it is possible to filter by these on the website, then re-export to get just those grants that fit each strategy or topic. This requires 21 (for strategies) or 11 (for topics) exports. In this way it is possible to find the strategy/topic of each grant (by checking for inclusion in the appropriate export; each grant has an ID number so this wouldn't require matching over amount/grantee/date combinations or anything like that).
• There are "fiscal year", "start date", and "end date" fields; not sure which one goes to our donation_date column.
• There are "regions", "benefiting populations", and "benefiting locations" fields; not sure which one goes to our affected_regions or affected_countries columns. Also "benefiting populations" and "benefiting locations" contain some encoded information and appear like 1258###Children (0-19)###75@@@8595###No target###100 or 655###Moscow###100.
• There are some other fields like whether a grant is a Build Grant ("Organizations participating in BUILD receive a multi-year funding commitment, comprised of general operating support and core support for institutional strengthening."). Not sure what all to include (e.g. some could go in the notes column).

[vipulnaik]

Thanks. For the donation_date column, using the start date is fine. It looks like that is what you are doing :).

However, I noticed one problem: it is not formatted YYYY-mm-dd so the insertion commands do not work. Is that possible to fix?

I will review the other points you raised as well, plus anything . I see after the data is inserted.

[riceissa]

I fixed the date format issue.

The data still won't insert though, because the location string is too long. (But dealing with the location string is one of the points I raised earlier.)

[vipulnaik]

Inserted, cleanup remains though.