Changes

{{Project|Has project output=Data|Has sponsor=McNair ProjectsCenter

 ==General== Working with <onlyinclude>The [[Federal Grant Data]] project collects and processes [[NIHData]], [[NSFData]], and other federal grant datainformation 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>

===NSF=== The update process was:

*Run 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*Add new data to old, removing dups so that it also takes ProgramElement (see below) and creating three filesmake:**general.txt**institutions.txt**investigators.txt*Run LoadNSF.sql to to product 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===

*'''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;