7,612
edits
Changes
Jump to navigation
Jump to search
Working with NIHIn April 2019, Ed updated the NSF, and other federal grant NIH data.
In April 2019, EdThe update process was:
→NIH
{{Project|Has project output=Data|Has sponsor=McNair ProjectsCenter
|Has title=Federal Grant Data
|Has owner=Catherine Kirby, Jeemin Sim, Avesh Krishna,
|Does subsume=NSF Data, NIH Data,
}}
<onlyinclude>The [[Federal Grant Data]] project collects and processes [[NIH Data]], [[NSF Data]], and other federal grant information from structured government sources and imports it into a relational database for use. See also: The [[Trial Data Project]] and the [[FDA Trials Data]] project.</onlyinclude>
==GeneralLatest Update==
==Latest Update=NSF===
*Visit https://www.nsf.gov/awardsearch/download.jsp
*Retrieve 2017, 2018, and 2019 zips to E:\projects\grants\NSF
*Extract them to E:\projects\grants\NSF\XML
*Remove 3 bad XML files that cause the parser to crash
*Move the whole thing back to E:\mcnair\Projects\Federal Grant Data\NSF\NSF Extracted Data
*Fix up and run Jeemin_NSF_XML_Parser.py, so that it also takes ProgramElement (see below) and make:
**
*Run LoadNSF.sql to produce tables NSFGeneral, NSFInvestigator and NSFInstitution in dbase '''grants'''
Note that '''program_code'''s are available from
*STTR: https://www.nsf.gov/awardsearch/lookup?type=program&qrytxt=*STTR*
*SBIR: https://www.nsf.gov/awardsearch/lookup?type=program&qrytxt=*SBIR*
Combined SBIR/STTR codes
'168E','5370','169E','166E','165E','164E','5371','163E','167E','5151','5727','4804','Y052','1532','Y813','2282','6537','Y350','4645','9311','2266','5370','168E','1591','1505','Z408'
SBIR Codes:
'168E','5370','169E','166E','165E','164E','5371','163E','167E','5151','5727','4804','Y052','1532','Y813','2282','6537','Y350','4645','9311','2266'
STTR Codes:
'5370','168E','1591','1505','Z408'
Unfortunately, we need the Program Element code, shown below, but our extractor doesn't currently pull it.
<xsd:element maxOccurs="unbounded" name="ProgramElement">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Code" type="xsd:int"/>
<xsd:element name="Text" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Correction: This appears fixed. There is a programelementcode in nsfgeneral. However, the code is XXXX in 42.6% of cases.
===NIH===
The 2018 update process was:
*Visit https://exporter.nih.gov/ExPORTER_Catalog.aspx
*Retrieve 2016, 2017, and 2018 csvs to E:\projects\grants\NIH\Source Files
*Add old data files to same directory
*Run LoadNIH.sql to product table nih in dbase '''grants'''
Supporting info for NIH grants:
*A data dictionary for the NIH grants is here: https://exporter.nih.gov/about.aspx
*'''Activity''' Codes are here: https://grants.nih.gov/grants/funding/ac_search_results.htm
*STTR codes are: R41,R42,UT1,UT2
*SBIR codes are: R43,R44,U43,U44
*SBIR/STTR Joint codes: RC3,SB1,UB1,UC3
*'''Total_Cost''' is only available in certain instances but is the best cost variable.
The 2022 update process was:
*Visit https://exporter.nih.gov/ExPORTER_Catalog.aspx
*Retrieve 2019, 2020, and 2021 csvs to E:\projects\grants\NIH\Source Files
*Open each in excel, copy and paste to a tab-delimited text file.
*Remove quotes from the file using regex (in textpad) and save as UTF8
*Run clean.py to fix the bad records
*Run LoadNIH.sql to load to the grants database (put input files in z:/bulk/grants)
Example processing code (for selected stats for Vermont):
CREATE TABLE nih_vt_stats AS
SELECT extract(year from award_notice_date) as year, direct_cost_amt+indirect_cost_amt as sum_amounts, total_cost, full_project_num,
CASE WHEN direct_cost_amt+indirect_cost_amt>total_cost THEN direct_cost_amt+indirect_cost_amt ELSE total_cost END AS amount,
CASE WHEN org_name LIKE 'UNIVERSITY OF VERMONT%' THEN 1 ELSE 0 END AS uvm
FROM nih WHERE org_state='VT';
--Updated Rows 9493
SELECT year, uvm, sum(amount), count(DISTINCT full_project_num)
FROM nih_vt_stats GROUP BY year, uvm ORDER BY uvm,year;
==grants.gov==
