parse_nsf.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inflation = {1970:6.456185567,\n",
    "1971:6.185185185,\n",
    "1972:5.992822967,\n",
    "1973:5.641891892,\n",
    "1974:5.081135903,\n",
    "1975:4.656133829,\n",
    "1976:4.402460457,\n",
    "1977:4.133663366,\n",
    "1978:3.84202454,\n",
    "1979:3.450413223,\n",
    "1980:3.040048544,\n",
    "1981:2.755775578,\n",
    "1982:2.595854922,\n",
    "1983:2.515060241,\n",
    "1984:2.410972089,\n",
    "1985:2.328066914,\n",
    "1986:2.285583942,\n",
    "1987:2.205105634,\n",
    "1988:2.117497887,\n",
    "1989:2.02016129,\n",
    "1990:1.916602907,\n",
    "1991:1.839207048,\n",
    "1992:1.785459729,\n",
    "1993:1.733564014,\n",
    "1994:1.690283401,\n",
    "1995:1.643700787,\n",
    "1996:1.596558317,\n",
    "1997:1.560747664,\n",
    "1998:1.536809816,\n",
    "1999:1.503601441,\n",
    "2000:1.454703833,\n",
    "2001:1.41445511,\n",
    "2002:1.392440245,\n",
    "2003:1.361413043,\n",
    "2004:1.326098465,\n",
    "2005:1.282642089,\n",
    "2006:1.242559524,\n",
    "2007:1.208393632,\n",
    "2008:1.163492801,\n",
    "2009:1.167832168,\n",
    "2010:1.148555708,\n",
    "2011:1.113828368,\n",
    "2012:1.091027875,\n",
    "2013:1.075107296,\n",
    "2014:1.058301648,\n",
    "2015:1.056962025,\n",
    "2016:1.04375,\n",
    "2017:1.022031824,\n",
    "2018:1,\n",
    "2019:1,\n",
    "2020:1,\n",
    "2021:1,\n",
    "2022:1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "import fnmatch\n",
    "import zipfile\n",
    "import xmltodict\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "data2 = []\n",
    "for file in sorted(os.listdir('download/nsf')):\n",
    "    if fnmatch.fnmatch(file,'*.zip'):\n",
    "        year, _ = file.split('.')\n",
    "        if int(year) < 1970:\n",
    "            continue\n",
    "        archive = zipfile.ZipFile(os.path.join('download/nsf',file), 'r')\n",
    "        print(file)\n",
    "        for zfile in archive.filelist:\n",
    "            try: \n",
    "                xmlf = archive.read(zfile)\n",
    "                o = xmltodict.parse(xmlf)\n",
    "\n",
    "                n = 1\n",
    "                y = o['rootTag']['Award']['AwardAmount']\n",
    "                \n",
    "                xs = []\n",
    "                ems = []\n",
    "                \n",
    "                org = []\n",
    "                d1 = ''\n",
    "                d2 = ''\n",
    "                try:\n",
    "                    org = [_['Name'] for _ in o['rootTag']['Award']['FoaInformation']]\n",
    "                except:\n",
    "                    pass\n",
    "                try:\n",
    "                    d1 = o['rootTag']['Award']['Organization']['Directorate']['LongName']\n",
    "                    d2 =  o['rootTag']['Award']['Organization']['Division']['LongName']\n",
    "                except:\n",
    "                    pass\n",
    "                if 'Investigator' in o['rootTag']['Award']:\n",
    "                    if 'FirstName' in o['rootTag']['Award']['Investigator']:\n",
    "                        if o['rootTag']['Award']['Investigator']['FirstName'] is None:\n",
    "                                continue\n",
    "                        x = o['rootTag']['Award']['Investigator']['FirstName'] + ' ' + o['rootTag']['Award']['Investigator']['LastName']\n",
    "                        data.append({'name' : x, 'award': y,'year':year,'n':n})\n",
    "                        xs.append(x)\n",
    "                        ems.append(o['rootTag']['Award']['Investigator']['EmailAddress'])\n",
    "                    else:\n",
    "                        n = len(o['rootTag']['Award']['Investigator'])\n",
    "                        for inv in o['rootTag']['Award']['Investigator']:\n",
    "                            if inv['FirstName'] is None:\n",
    "                                continue\n",
    "                            x = inv['FirstName'] + ' ' + inv['LastName']\n",
    "                            data.append({'name' : x, 'award': y,'year':year,'n':n})\n",
    "                            xs.append(x)\n",
    "                            ems.append(inv['EmailAddress'])\n",
    "\n",
    "                    if False and 'Robert Wood' in xs:\n",
    "                        print(o['rootTag']['Award'])\n",
    "                        print(o)\n",
    "                    #data2.append({'name' : xs, 'award': y,'year':year,'n':n, 'org':org,'d1':d1,'d2':d2,'ems':ems})\n",
    "                    data2.append({'name' : xs, 'award': y,'year':year,'n':n})\n",
    "\n",
    "            except:\n",
    "                print(year)\n",
    "                #print(inv)\n",
    "                #print(o['rootTag']['Award']['Investigator'])\n",
    "                pass\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data)\n",
    "df2 = pd.DataFrame(data2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape,df2.shape\n",
    "df.award = pd.to_numeric(df.award)\n",
    "df.year = pd.to_numeric(df.year)\n",
    "df['avg_award'] = df.award/df.n\n",
    "df2.award = pd.to_numeric(df2.award)\n",
    "df2.year = pd.to_numeric(df2.year)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ls *nsf*.pkl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.to_pickle('nsf.pkl')\n",
    "#df2.to_pickle('nsf2.pkl')\n",
    "#df2 = pd.read_pickle('nsf2.pkl')\n",
    "#df = pd.read_pickle('nsf.pkl')\n",
    "df['infaward']= df.award * df.year.map(inflation)\n",
    "df2['infaward'] = df2.award * df2.year.map(inflation)\n",
    "df['avg_award'] = df.award/df.n\n",
    "df['avg_infaward'] = df.infaward/df.n\n",
    "df.to_pickle('nsf.pkl')\n",
    "df2.to_pickle('nsf2.pkl')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.name == 'Abhinav Gupta']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.name == 'Martial Hebert']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.name == 'Christopher Atkeson'].infaward.astype(np.int).sum(), df[df.name == 'Martial Hebert'].infaward.astype(np.int).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.name == 'Christopher Atkeson'].avg_infaward.sum(), df[df.name == 'Martial Hebert'].avg_infaward.sum(), df[df.name == 'Abhinav Gupta'].avg_infaward.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df.name == 'Martial Hebert']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[df2.name.map(lambda x: 'Martial Hebert' in x)]\n",
    "df2[df2.year ==2020]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}