AD419-generate-datafiles.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Program for AD419 Project and Cateogry Totals\n",
    "\n",
    "Define categories for our departments, and then break up the totals in two different ways:\n",
    "\n",
    "1. By project, using overall totals\n",
    "2. By category, for each source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "departments = {\n",
    "    'ARE': {\n",
    "        'name': 'Agricultural & Resource Economics',\n",
    "        'category': 'HUMAN'\n",
    "    },\n",
    "    'BAE': {\n",
    "        'name': 'Biological & Agricultural Engineering',\n",
    "        'category': 'AG'\n",
    "    },\n",
    "    'PLS': {\n",
    "        'name': 'Plant Sciences',\n",
    "        'category': 'AG'\n",
    "    },\n",
    "    'PLB': {\n",
    "        'name': 'Plant Biology',\n",
    "        'category': 'BIO'\n",
    "    },\n",
    "    'PPA': {\n",
    "        'name': 'Plant Pathology',\n",
    "        'category': 'AG'\n",
    "    },\n",
    "    'ASC': {\n",
    "        'name': 'Animal Science',\n",
    "        'category': 'AG'\n",
    "    },\n",
    "    'HCE': {\n",
    "        'name': 'Human Ecology',\n",
    "        'category': 'HUMAN'\n",
    "    },\n",
    "    'TXC': {\n",
    "        'name': 'Textiles & Clothing',\n",
    "        'category': 'HUMAN'\n",
    "    },\n",
    "    'ESP': {\n",
    "        'name': 'Environmental Science & Policy',\n",
    "        'category': 'ENV'\n",
    "    },\n",
    "    'ENM': {\n",
    "        'name': 'Entomology & Nematology',\n",
    "        'category': 'AG'\n",
    "    },\n",
    "    'ETX': {\n",
    "        'name': 'Environmental Toxicology',\n",
    "        'category': 'ENV'\n",
    "    },\n",
    "    'LAW': {\n",
    "        'name': 'Land, Air & Water Resources',\n",
    "        'category': 'ENV'\n",
    "    },\n",
    "    'WFB': {\n",
    "        'name': 'Wildlife, Fish & Conservation Biology',\n",
    "        'category': 'ENV'\n",
    "    },\n",
    "    'VIT': {\n",
    "        'name': 'Viticulture & Enology',\n",
    "        'category': 'AG'\n",
    "    },\n",
    "    'FST': {\n",
    "        'name': 'Food Science & Technology',\n",
    "        'category': 'HUMAN'\n",
    "    },\n",
    "    'NTR': {\n",
    "        'name': 'Nutrition',\n",
    "        'category': 'HUMAN'\n",
    "    },\n",
    "    'MCB': {\n",
    "        'name': 'Molecular & Cellular Biology',\n",
    "        'category': 'BIO'\n",
    "    },\n",
    "    'EVE': {\n",
    "        'name': 'Evolution & Ecology',\n",
    "        'category': 'BIO'\n",
    "    },\n",
    "    'MIC': {\n",
    "        'name': 'Microbiology & Molecular Genetics',\n",
    "        'category': 'BIO'\n",
    "    },\n",
    "    'NPB': {\n",
    "        'name': 'Neurobiology, Physiology & Behavior',\n",
    "        'category': 'BIO'\n",
    "    },\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loc</th>\n",
       "      <th>dept</th>\n",
       "      <th>proj</th>\n",
       "      <th>project</th>\n",
       "      <th>accession</th>\n",
       "      <th>PI</th>\n",
       "      <th>f201</th>\n",
       "      <th>f202</th>\n",
       "      <th>f203</th>\n",
       "      <th>f204</th>\n",
       "      <th>...</th>\n",
       "      <th>Textbox64</th>\n",
       "      <th>Textbox65</th>\n",
       "      <th>Textbox66</th>\n",
       "      <th>Textbox67</th>\n",
       "      <th>Textbox68</th>\n",
       "      <th>Textbox69</th>\n",
       "      <th>Textbox70</th>\n",
       "      <th>Textbox71</th>\n",
       "      <th>Textbox72</th>\n",
       "      <th>Textbox73</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>2027</td>\n",
       "      <td>CA-D-ARE-2027-H</td>\n",
       "      <td>1004905</td>\n",
       "      <td>Goodhue, Rachael</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12248471.88</td>\n",
       "      <td>23963343.51</td>\n",
       "      <td>17942419.49</td>\n",
       "      <td>1.254189e+08</td>\n",
       "      <td>2.020218e+08</td>\n",
       "      <td>230.4</td>\n",
       "      <td>691.7</td>\n",
       "      <td>155.0</td>\n",
       "      <td>260.2</td>\n",
       "      <td>1337.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>2186</td>\n",
       "      <td>CA-D-ARE-2186-RR</td>\n",
       "      <td>232974</td>\n",
       "      <td>Hardesty,  S</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12248471.88</td>\n",
       "      <td>23963343.51</td>\n",
       "      <td>17942419.49</td>\n",
       "      <td>1.254189e+08</td>\n",
       "      <td>2.020218e+08</td>\n",
       "      <td>230.4</td>\n",
       "      <td>691.7</td>\n",
       "      <td>155.0</td>\n",
       "      <td>260.2</td>\n",
       "      <td>1337.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>2194</td>\n",
       "      <td>CA-D-ARE-2194-H</td>\n",
       "      <td>1002098</td>\n",
       "      <td>Paris,  Q</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12248471.88</td>\n",
       "      <td>23963343.51</td>\n",
       "      <td>17942419.49</td>\n",
       "      <td>1.254189e+08</td>\n",
       "      <td>2.020218e+08</td>\n",
       "      <td>230.4</td>\n",
       "      <td>691.7</td>\n",
       "      <td>155.0</td>\n",
       "      <td>260.2</td>\n",
       "      <td>1337.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>2197</td>\n",
       "      <td>CA-D-ARE-2197-H</td>\n",
       "      <td>1001956</td>\n",
       "      <td>Taylor,  J</td>\n",
       "      <td>47119.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12248471.88</td>\n",
       "      <td>23963343.51</td>\n",
       "      <td>17942419.49</td>\n",
       "      <td>1.254189e+08</td>\n",
       "      <td>2.020218e+08</td>\n",
       "      <td>230.4</td>\n",
       "      <td>691.7</td>\n",
       "      <td>155.0</td>\n",
       "      <td>260.2</td>\n",
       "      <td>1337.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>2198</td>\n",
       "      <td>CA-D-ARE-2198-H</td>\n",
       "      <td>1001957</td>\n",
       "      <td>Novan, Kevin</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12248471.88</td>\n",
       "      <td>23963343.51</td>\n",
       "      <td>17942419.49</td>\n",
       "      <td>1.254189e+08</td>\n",
       "      <td>2.020218e+08</td>\n",
       "      <td>230.4</td>\n",
       "      <td>691.7</td>\n",
       "      <td>155.0</td>\n",
       "      <td>260.2</td>\n",
       "      <td>1337.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  loc dept  proj           project  accession                PI     f201  \\\n",
       "0  D   ARE  2027   CA-D-ARE-2027-H    1004905  Goodhue, Rachael      0.0   \n",
       "1  D   ARE  2186  CA-D-ARE-2186-RR     232974      Hardesty,  S      0.0   \n",
       "2  D   ARE  2194   CA-D-ARE-2194-H    1002098         Paris,  Q      0.0   \n",
       "3  D   ARE  2197  CA-D-ARE-2197-H     1001956        Taylor,  J  47119.0   \n",
       "4  D   ARE  2198   CA-D-ARE-2198-H    1001957      Novan, Kevin      0.0   \n",
       "\n",
       "   f202  f203  f204    ...        Textbox64    Textbox65    Textbox66  \\\n",
       "0   0.0   0.0   0.0    ...      12248471.88  23963343.51  17942419.49   \n",
       "1   0.0   0.0   0.0    ...      12248471.88  23963343.51  17942419.49   \n",
       "2   0.0   0.0   0.0    ...      12248471.88  23963343.51  17942419.49   \n",
       "3   0.0   0.0   0.0    ...      12248471.88  23963343.51  17942419.49   \n",
       "4   0.0   0.0   0.0    ...      12248471.88  23963343.51  17942419.49   \n",
       "\n",
       "      Textbox67     Textbox68  Textbox69  Textbox70  Textbox71  Textbox72  \\\n",
       "0  1.254189e+08  2.020218e+08      230.4      691.7      155.0      260.2   \n",
       "1  1.254189e+08  2.020218e+08      230.4      691.7      155.0      260.2   \n",
       "2  1.254189e+08  2.020218e+08      230.4      691.7      155.0      260.2   \n",
       "3  1.254189e+08  2.020218e+08      230.4      691.7      155.0      260.2   \n",
       "4  1.254189e+08  2.020218e+08      230.4      691.7      155.0      260.2   \n",
       "\n",
       "   Textbox73  \n",
       "0     1337.3  \n",
       "1     1337.3  \n",
       "2     1337.3  \n",
       "3     1337.3  \n",
       "4     1337.3  \n",
       "\n",
       "[5 rows x 66 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read in the whole CSV\n",
    "df = pd.read_csv('./ad419admin.csv', thousands=',')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean up Textbox columns\n",
    "for c in df.columns:\n",
    "    if c.startswith(\"Textbox\"):\n",
    "        del df[c]\n",
    "        \n",
    "# delete other cols we aren't using\n",
    "del df['accession']\n",
    "del df['proj']\n",
    "# f241-244 + 350 are FTE columns we don't want\n",
    "del df['f241']\n",
    "del df['f242']\n",
    "del df['f243']\n",
    "del df['f244']\n",
    "del df['f350']\n",
    "\n",
    "# remove IND and Cross dept rows\n",
    "df = df.query(\"dept != 'IND' & dept != 'XXX'\")\n",
    "\n",
    "# grab the removed just to see what they are\n",
    "df_removed = df.query(\"dept == 'IND' | dept == 'XXX'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loc</th>\n",
       "      <th>dept</th>\n",
       "      <th>project</th>\n",
       "      <th>PI</th>\n",
       "      <th>f201</th>\n",
       "      <th>f202</th>\n",
       "      <th>f203</th>\n",
       "      <th>f204</th>\n",
       "      <th>f205</th>\n",
       "      <th>f231</th>\n",
       "      <th>...</th>\n",
       "      <th>f315</th>\n",
       "      <th>f318</th>\n",
       "      <th>f332</th>\n",
       "      <th>f220</th>\n",
       "      <th>f22F</th>\n",
       "      <th>f221</th>\n",
       "      <th>f222</th>\n",
       "      <th>f223</th>\n",
       "      <th>f233</th>\n",
       "      <th>f234</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>CA-D-ARE-2027-H</td>\n",
       "      <td>Goodhue, Rachael</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16368.35</td>\n",
       "      <td>310492.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1515.32</td>\n",
       "      <td>22138.47</td>\n",
       "      <td>11042.33</td>\n",
       "      <td>345188.73</td>\n",
       "      <td>361557.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>CA-D-ARE-2186-RR</td>\n",
       "      <td>Hardesty,  S</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>67744.42</td>\n",
       "      <td>82019.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8765.78</td>\n",
       "      <td>22138.47</td>\n",
       "      <td>15561.27</td>\n",
       "      <td>128485.27</td>\n",
       "      <td>196229.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>CA-D-ARE-2194-H</td>\n",
       "      <td>Paris,  Q</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2895.00</td>\n",
       "      <td>42344.62</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1515.32</td>\n",
       "      <td>22138.47</td>\n",
       "      <td>14359.27</td>\n",
       "      <td>80357.68</td>\n",
       "      <td>83252.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>CA-D-ARE-2197-H</td>\n",
       "      <td>Taylor,  J</td>\n",
       "      <td>47119.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47119.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13074.32</td>\n",
       "      <td>152843.08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1515.32</td>\n",
       "      <td>22138.47</td>\n",
       "      <td>63603.33</td>\n",
       "      <td>240100.20</td>\n",
       "      <td>300293.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>D</td>\n",
       "      <td>ARE</td>\n",
       "      <td>CA-D-ARE-2198-H</td>\n",
       "      <td>Novan, Kevin</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2895.00</td>\n",
       "      <td>125043.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1515.32</td>\n",
       "      <td>22138.47</td>\n",
       "      <td>14359.27</td>\n",
       "      <td>163056.74</td>\n",
       "      <td>165951.74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  loc dept           project                PI     f201  f202  f203  f204  \\\n",
       "0  D   ARE   CA-D-ARE-2027-H  Goodhue, Rachael      0.0   0.0   0.0   0.0   \n",
       "1  D   ARE  CA-D-ARE-2186-RR      Hardesty,  S      0.0   0.0   0.0   0.0   \n",
       "2  D   ARE   CA-D-ARE-2194-H         Paris,  Q      0.0   0.0   0.0   0.0   \n",
       "3  D   ARE  CA-D-ARE-2197-H         Taylor,  J  47119.0   0.0   0.0   0.0   \n",
       "4  D   ARE   CA-D-ARE-2198-H      Novan, Kevin      0.0   0.0   0.0   0.0   \n",
       "\n",
       "   f205     f231    ...      f315  f318      f332       f220  f22F     f221  \\\n",
       "0   0.0      0.0    ...       0.0   0.0  16368.35  310492.61   0.0  1515.32   \n",
       "1   0.0      0.0    ...       0.0   0.0  67744.42   82019.75   0.0  8765.78   \n",
       "2   0.0      0.0    ...       0.0   0.0   2895.00   42344.62   0.0  1515.32   \n",
       "3   0.0  47119.0    ...       0.0   0.0  13074.32  152843.08   0.0  1515.32   \n",
       "4   0.0      0.0    ...       0.0   0.0   2895.00  125043.68   0.0  1515.32   \n",
       "\n",
       "       f222      f223       f233       f234  \n",
       "0  22138.47  11042.33  345188.73  361557.08  \n",
       "1  22138.47  15561.27  128485.27  196229.69  \n",
       "2  22138.47  14359.27   80357.68   83252.68  \n",
       "3  22138.47  63603.33  240100.20  300293.52  \n",
       "4  22138.47  14359.27  163056.74  165951.74  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()\n",
    "\n",
    "# df_removed['f234'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# build the default nodes (TODO: populate from df)\n",
    "data = []\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    data.append({\n",
    "        'project': row['project'],\n",
    "        'dept': row['dept'],\n",
    "        'total': row['f234']\n",
    "    })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'dept': 'ARE', 'project': 'CA-D-ARE-2027-H', 'total': 361557.08},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2186-RR', 'total': 196229.69},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2194-H', 'total': 83252.68},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2197-H ', 'total': 300293.52},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2198-H', 'total': 165951.74},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2199-H', 'total': 179449.44},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2200-H', 'total': 428557.85},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2215-H', 'total': 128428.73},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2249-H', 'total': 165148.92},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2251-H', 'total': 201054.45},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2254-H', 'total': 401403.14},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2280-H', 'total': 259799.48},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2294-H', 'total': 234828.84},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2298-H', 'total': 233247.7},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2331-CG', 'total': 259799.48},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2363-H', 'total': 213976.35},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-2364-H', 'total': 5564755.32},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-6585-H', 'total': 220922.98},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-6987-H', 'total': 316291.61},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-7307-RR', 'total': 192554.48},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-7639-RR', 'total': 413522.52},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-7733-H', 'total': 324636.88},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-7740-H', 'total': 83336.57},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-7820-H', 'total': 331263.77},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-7833-H', 'total': 241197.36},\n",
       " {'dept': 'ARE', 'project': 'CA-D-ARE-7837-H', 'total': 162547.02},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2002-RR', 'total': 188418.72},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2003-RR', 'total': 303004.67},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2024-RR', 'total': 302371.78},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2070-RR', 'total': 253198.55},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2095-RR', 'total': 331420.83},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2138-RR', 'total': 239120.97},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2155-RR', 'total': 213536.68},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2156-RR', 'total': 213536.68},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2157-RR', 'total': 213536.68},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2193-RR', 'total': 1390611.07},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2209-RR', 'total': 208817.97},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2237-OG', 'total': 289210.68},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2239-RR', 'total': 213536.68},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2240-RR', 'total': 213536.68},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2250-AH', 'total': 792457.46},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2252-H', 'total': 267061.26},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2253-RR', 'total': 267061.26},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2262-RR', 'total': 210837.55},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2263-H', 'total': 95023.38},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2283-CG ', 'total': 252335.32},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2286-H', 'total': 93409.15},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ANS-2299-CG', 'total': 234938.72},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2303-RR', 'total': 159873.21},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2304-RR', 'total': 233512.13},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2306-RR', 'total': 136130.21},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2307-RR', 'total': 233517.06},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2308-CG', 'total': 350727.67},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2339-RR', 'total': 407463.24},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2341-RR', 'total': 119550.89},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2342-RR', 'total': 119550.89},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2355-RR', 'total': 194546.67},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2356-RR', 'total': 194546.67},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2360-H', 'total': 254042.66},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2371-RR', 'total': 235033.55},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2372-CG', 'total': 221185.13},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2374-OG', 'total': 143000.08},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2380-CG', 'total': 237105.33},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2389-RR', 'total': 244447.84},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2390-RR', 'total': 244447.84},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2414-CG', 'total': 207229.47},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-2420-CG', 'total': 305259.92},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-5929-RR ', 'total': 413242.26},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-6414-RR', 'total': 165487.84},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-6578-H', 'total': 426204.83},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-6796-RR', 'total': 253764.09},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7233-RR', 'total': 230352.84},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7281-RR', 'total': 165509.34},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7366-RR', 'total': 372423.25},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7369-RR', 'total': 409668.51},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7373-RR', 'total': 207229.47},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7384-RR', 'total': 207253.84},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7507-H', 'total': 181815.06},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7624-RR', 'total': 260364.29},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7666-RR', 'total': 239120.97},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7690-H', 'total': 309959.29},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7744-RR', 'total': 459552.32},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7771-RR', 'total': 155907.82},\n",
       " {'dept': 'ASC', 'project': 'CA-D-ASC-7776-RR', 'total': 155907.82},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2005-RR', 'total': 772177.02},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2011-RR', 'total': 131459.69},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2031-H', 'total': 343550.44},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2190-RR', 'total': 147242.14},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2208-CG', 'total': 384729.14},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2212-H', 'total': 126322.62},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2225-CG', 'total': 379209.44},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2228-RR', 'total': 494349.61},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2230-RR', 'total': 168204.62},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2231-RR', 'total': 273539.31},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2270-OG', 'total': 807219.17},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2271-OG', 'total': 350897.4},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2277-RR', 'total': 326144.68},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2312-OG', 'total': 185035.14},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2314-CG', 'total': 494513.14},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2335-RR', 'total': 290519.37},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-2375-OG', 'total': 454236.79},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-6444-H', 'total': 137904.4},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-7098-H', 'total': 473636.21},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-7099-H', 'total': 506104.65},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-7725-H', 'total': 131459.69},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-7737-RR', 'total': 772177.02},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-7746-H', 'total': 218791.31},\n",
       " {'dept': 'BAE', 'project': 'CA-D-BAE-7808-RR', 'total': 247844.17},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2064-H', 'total': 379998.73},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2103-H', 'total': 328209.58},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2105-H', 'total': 1149456.8},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2141-H', 'total': 590095.02},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2150-H', 'total': 1087890.3},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2161-H', 'total': 350081.7},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2174-OG', 'total': 948073.55},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2189-RR', 'total': 241036.21},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2309-H', 'total': 687052.9},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2316-OG', 'total': 338407.37},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2351-H', 'total': 240438.55},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2353-RR', 'total': 251034.16},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-2354-RR', 'total': 240438.55},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-4162-H', 'total': 397657.7},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-4278-H', 'total': 251034.16},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-5234-H', 'total': 380991.29},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-5671-RR', 'total': 290725.37},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-6020-H', 'total': 328263.55},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-6264-H', 'total': 857282.53},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-6546-H', 'total': 362861.66},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-6772-H', 'total': 444325.29},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-6851-H', 'total': 545983.16},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-7136-H', 'total': 304914.31},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-7314-RR', 'total': 251046.42},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-7356-RR', 'total': 260966.39},\n",
       " {'dept': 'ENM', 'project': 'CA-D-ENM-7654-H', 'total': 2298344.34},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2020-H', 'total': 374374.01},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2084-H', 'total': 255647.14},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2086-H', 'total': 261191.0},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2087-H', 'total': 292331.16},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2094-H', 'total': 283711.1},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2163-H', 'total': 616384.65},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2201-H ', 'total': 437142.14},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2244-H', 'total': 286215.04},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2245-H', 'total': 487499.52},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-2367-OG', 'total': 294730.47},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-7005-H', 'total': 298908.09},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-7104-H', 'total': 687292.66},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-7122-H', 'total': 475685.48},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-7140-H', 'total': 247903.28},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-7687-H', 'total': 304065.0},\n",
       " {'dept': 'ESP', 'project': 'CA-D-ESP-7853-H', 'total': 487792.66},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-2102-H', 'total': 274050.9},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-2152-H', 'total': 272571.99},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-2227-H', 'total': 502575.98},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-2233-H', 'total': 779976.79},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-2293-SG', 'total': 2051898.26},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-2345-H', 'total': 119363.97},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-2421-CG', 'total': 123560.31},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-3926-H', 'total': 298066.86},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-6718-H', 'total': 1524797.18},\n",
       " {'dept': 'ETX', 'project': 'CA-D-ETX-7751-RR', 'total': 369194.77},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-2241-H', 'total': 680414.28},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-2264-H', 'total': 97445.44},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-2265-H', 'total': 100074.84},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-2266-H', 'total': 330519.7},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-2268-H', 'total': 292202.61},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-2365-H', 'total': 126972.56},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-2369-H', 'total': 145245.98},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-3515-H', 'total': 326650.95},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-4244-H', 'total': 388918.68},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-5490-H', 'total': 131629.89},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-5542-H', 'total': 463108.7},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-5630-H', 'total': 100296.98},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-6882-H', 'total': 402711.29},\n",
       " {'dept': 'EVE', 'project': 'CA-D-EVE-7118-H', 'total': 98666.24},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2028-H', 'total': 634302.89},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2090-H', 'total': 213773.12},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2093-H', 'total': 272696.9},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2106-H', 'total': 310820.36},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2187-H', 'total': 724638.17},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2236-RR', 'total': 336256.43},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2281-CG', 'total': 365911.21},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2282-CG', 'total': 1066127.12},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2317-H', 'total': 338377.88},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2321-OG', 'total': 281849.88},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2348-RR', 'total': 361322.3},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2366-OG', 'total': 196744.81},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2377-OG', 'total': 307852.33},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-2379-RR', 'total': 195483.28},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-3627-H', 'total': 161874.07},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-4929-H', 'total': 646461.14},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-6097-H', 'total': 351427.94},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-6568-H', 'total': 1701786.13},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-6975-H', 'total': 361344.27},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-7105-H', 'total': 306905.72},\n",
       " {'dept': 'FST', 'project': 'CA-D-FST-7598-H ', 'total': 666620.18},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2049-H', 'total': 192082.12},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2050-H', 'total': 487326.13},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2051-H', 'total': 146368.92},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2055-H', 'total': 126682.12},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2151-H', 'total': 202138.32},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2159-RR', 'total': 146368.92},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2184-RR', 'total': 147234.26},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2195-H', 'total': 103843.31},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2213-H', 'total': 89907.86},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2214-H', 'total': 143448.41},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2220-H', 'total': 98823.99},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2234-H', 'total': 84481.5},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2267-H', 'total': 255500.08},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2269-H', 'total': 171985.46},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2300-H', 'total': 158067.23},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2302-H ', 'total': 110958.15},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2319-H', 'total': 151096.51},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2344-RR', 'total': 120584.91},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2358-H', 'total': 123525.33},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2361-H', 'total': 145888.01},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-2362-H', 'total': 257664.0},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-7035-H', 'total': 104897.03},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-7513-H', 'total': 381187.65},\n",
       " {'dept': 'HCE', 'project': 'CA-D-HCE-7743-H', 'total': 151215.15},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2058-H', 'total': 145099.81},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2076-H', 'total': 122358.14},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2085-H', 'total': 125208.25},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2122-H', 'total': 303198.32},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2128-CG', 'total': 429360.92},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2177-OG', 'total': 250297.92},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2178-H', 'total': 414251.68},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2203-H', 'total': 847470.38},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2217-CG', 'total': 425354.89},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2229-H', 'total': 507427.89},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2243-H', 'total': 553400.7},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2256-CG', 'total': 635920.92},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2274-RR', 'total': 660634.14},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2295-H', 'total': 356569.7},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2296-H', 'total': 498798.0},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2315-RR', 'total': 122358.14},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2333-OG', 'total': 420909.32},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2352-H', 'total': 469607.59},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-2386-OG ', 'total': 122554.14},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-4264-H', 'total': 309180.3},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-4526-H', 'total': 373464.52},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-5332-H', 'total': 308785.21},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-5631-RR', 'total': 918607.08},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-5661-H', 'total': 520641.83},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-6036-H', 'total': 811808.18},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-6397-H', 'total': 114036.55},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-6403-RR', 'total': 327200.87},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-6864-H', 'total': 169339.44},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-6910-H', 'total': 227308.7},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-6999-H', 'total': 376553.99},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-7034-H', 'total': 482660.73},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-7137-H', 'total': 256455.24},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-7214-RR', 'total': 373464.52},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-7348-RR', 'total': 215087.81},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-7349-RR', 'total': 120880.39},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-7350-RR', 'total': 120880.39},\n",
       " {'dept': 'LAW', 'project': 'CA-D-LAW-7551-RR', 'total': 221414.92},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-2310-H', 'total': 53294.96},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-4126-H', 'total': 543605.77},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-5554-H', 'total': 618483.22},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-5896-H', 'total': 109084.6},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-6239-H', 'total': 211795.51},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-6365-H', 'total': 168706.63},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-6471-H', 'total': 48556.64},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-6731-H', 'total': 58146.64},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-6735-H', 'total': 295257.33},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-7165-H', 'total': 62918.16},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-7237-H', 'total': 240170.78},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-7399-H', 'total': 214952.25},\n",
       " {'dept': 'MCB', 'project': 'CA-D-MCB-7420-H', 'total': 347008.31},\n",
       " {'dept': 'MIC', 'project': 'CA-D-MMG-2313-H', 'total': 81518.49},\n",
       " {'dept': 'MIC', 'project': 'CA-D-MMG-5733-H', 'total': 76769.34},\n",
       " {'dept': 'MIC', 'project': 'CA-D-MMG-7235-H', 'total': 217780.93},\n",
       " {'dept': 'MIC', 'project': 'CA-D-MMG-7402-H', 'total': 64986.84},\n",
       " {'dept': 'MIC', 'project': 'CA-D-MMG-7473-H', 'total': 211160.9},\n",
       " {'dept': 'NPB', 'project': 'CA-D-NPB-2330-H', 'total': 383187.29},\n",
       " {'dept': 'NPB', 'project': 'CA-D-NPB-3512-H', 'total': 503868.4},\n",
       " {'dept': 'NPB', 'project': 'CA-D-NPB-6309-H', 'total': 804803.98},\n",
       " {'dept': 'NPB', 'project': 'CA-D-NPB-7153-H', 'total': 389995.0},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-2060-H', 'total': 276147.45},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-2092-H', 'total': 1637848.92},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-2242-H', 'total': 340306.83},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-2285-CG', 'total': 837141.47},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-2407-CG', 'total': 255443.45},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-4543-H', 'total': 1745304.75},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-5908-H', 'total': 1648725.69},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-5966-H', 'total': 503003.67},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-6316-H', 'total': 2700298.97},\n",
       " {'dept': 'NTR', 'project': 'CA-D-NTR-7836-H', 'total': 692505.85},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-2259-H', 'total': 751667.86},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-2260-H', 'total': 296373.15},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-3504-H', 'total': 119312.55},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-3511-CG', 'total': 327030.52},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-3514-CG', 'total': 373991.52},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-6167-H', 'total': 636835.15},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-6991-H', 'total': 353546.17},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-7226-H', 'total': 220838.99},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-7381-H', 'total': 366547.81},\n",
       " {'dept': 'PLB', 'project': 'CA-D-PLB-7412-H', 'total': 230911.52},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2013-H', 'total': 2260041.66},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2016-H', 'total': 952918.4},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2017-H', 'total': 279425.33},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2066-H', 'total': 1219983.83},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2073-H', 'total': 357603.83},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2078-H', 'total': 884739.72},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2079-H', 'total': 340808.54},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2125-CG', 'total': 1482012.44},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2127-CG', 'total': 683139.33},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2129-H', 'total': 850719.93},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2132-H', 'total': 406063.02},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2164-H', 'total': 229432.81},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2173-H', 'total': 233775.47},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2179-H', 'total': 1064739.62},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2183-H', 'total': 919012.02},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2205-H', 'total': 200617.29},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2218-OG', 'total': 329737.57},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2219-H', 'total': 339298.54},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2221-CG', 'total': 262918.18},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2222-CG', 'total': 263445.31},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2224-H', 'total': 724643.36},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2235-H', 'total': 279978.08},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2246-RR', 'total': 687222.01},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2247-H', 'total': 962770.72},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2272-OG', 'total': 212266.09},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2273-OG', 'total': 214788.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2276-OG', 'total': 273668.25},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2279-OG', 'total': 292535.3},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2289-OG', 'total': 236080.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2291-CG', 'total': 207636.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2305-CG', 'total': 196764.12},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2311-OG', 'total': 299355.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2324-OG', 'total': 187819.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2325-OG', 'total': 321920.18},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2327-H', 'total': 240858.96},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2329-OG', 'total': 304008.44},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2332-H', 'total': 579317.42},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2343-RR', 'total': 555935.26},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2349-H', 'total': 3112393.34},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2350-H', 'total': 515660.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2359-OG', 'total': 358947.08},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2370-CG', 'total': 217902.23},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2373-CG', 'total': 471580.44},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2378-OG', 'total': 168413.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2383-OG', 'total': 149728.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2384-OG', 'total': 234617.33},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2385-OG', 'total': 175370.32},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2418-CG', 'total': 149480.7},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-2424-CG', 'total': 140984.12},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS 5457-RR', 'total': 203897.48},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-5629-H', 'total': 750807.18},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-5884-H', 'total': 635037.43},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-5983-H', 'total': 265194.69},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6063-H', 'total': 468454.17},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6254-H', 'total': 477100.79},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6273-H', 'total': 544197.05},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6278-H', 'total': 2182727.25},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6315-H', 'total': 445881.08},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6410-H', 'total': 239504.76},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6418-H', 'total': 1721644.84},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6470-H', 'total': 372463.26},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6599-H', 'total': 433037.44},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-6884-H', 'total': 2522314.11},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7033-H', 'total': 512903.62},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7121-H', 'total': 346040.87},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7250-RR', 'total': 1428686.79},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7259-RR', 'total': 671334.09},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7262-RR', 'total': 750808.33},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7279-H', 'total': 270638.48},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7311-H', 'total': 720737.52},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7344-H', 'total': 1147603.24},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7514-RR', 'total': 508698.05},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7641-H', 'total': 400080.02},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7658-H', 'total': 188312.21},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7663-RR', 'total': 454202.22},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7680-H', 'total': 328837.88},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7749-RR', 'total': 397349.42},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7864-RR', 'total': 212473.21},\n",
       " {'dept': 'PLS', 'project': 'CA-D-PLS-7869-H', 'total': 279986.97},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2021-H', 'total': 619532.47},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2022-H', 'total': 363585.5},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2025-H', 'total': 1297756.69},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2057-CG', 'total': 638410.19},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2131-H', 'total': 502529.82},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2165-H', 'total': 459112.75},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2167-OG', 'total': 450542.33},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2185-H', 'total': 460135.27},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2223-CG', 'total': 474221.5},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2226-CG', 'total': 207580.64},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2258-CG ', 'total': 480331.03},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2283-CG', 'total': 2319053.47},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2297-H', 'total': 517720.89},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2323-OG', 'total': 159950.01},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2347-OG', 'total': 878553.82},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2368-CG ', 'total': 157116.01},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-2392-CG', 'total': 951423.07},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-5484-H', 'total': 466994.23},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-5681-H', 'total': 362829.03},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-6395-H', 'total': 341577.33},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-6456-H', 'total': 577440.2},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-6575-H', 'total': 943877.07},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-6699-H', 'total': 563534.4},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-6738-H', 'total': 879834.09},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-7268-RR', 'total': 362893.0},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-7536-H', 'total': 2872991.03},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-7629-RR', 'total': 504771.23},\n",
       " {'dept': 'PPA', 'project': 'CA-D-PPA-7757-H', 'total': 438992.19},\n",
       " {'dept': 'TXC', 'project': 'CA-D-TXC-4313-RR', 'total': 5505.0},\n",
       " {'dept': 'TXC', 'project': 'CA-D-TXC-6424-RR', 'total': 99351.41},\n",
       " {'dept': 'TXC', 'project': 'CA-D-TXC-6425-RR', 'total': 140790.13},\n",
       " {'dept': 'TXC', 'project': 'CA-D-TXC-6706-H', 'total': 269785.77},\n",
       " {'dept': 'TXC', 'project': 'CA-D-TXC-7694-H', 'total': 54781.41},\n",
       " {'dept': 'TXC', 'project': 'CA-D-TXC-7739-H', 'total': 140783.81},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-2196-H', 'total': 1171739.6},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-2322-H', 'total': 458077.79},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-2346-H', 'total': 446791.93},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-4345-H', 'total': 500147.86},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-4662-H', 'total': 441864.55},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-6013-H', 'total': 427761.83},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-6700-H', 'total': 237387.98},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-6923-H', 'total': 677161.0},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-7086-H', 'total': 2043091.92},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-7120-H', 'total': 381555.51},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-7727-H', 'total': 237387.98},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-7728-H', 'total': 679212.16},\n",
       " {'dept': 'VIT', 'project': 'CA-D-VIT-7729-H', 'total': 671245.8},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-2097-H', 'total': 656373.95},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-2098-H', 'total': 1628578.76},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-2376-RR', 'total': 656373.95},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-2381-OG', 'total': 606781.82},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-4071-H', 'total': 740050.27},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-5245-H', 'total': 701117.55},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-6126-H', 'total': 732570.0},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-6342-H', 'total': 980186.51},\n",
       " {'dept': 'WFB', 'project': 'CA-D-WFB-6721-H', 'total': 529908.74}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove all 0 totals project\n",
    "\n",
    "data = [x for x in data if x['total'] > 0]\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "with open('projectTotals.json', 'w') as fp:\n",
    "    json.dump(data, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now total up the departments\n",
    "\n",
    "df_grouped = df.groupby('dept').agg('sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_grouped.to_json('departmentTotals.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now spit out the departments to json\n",
    "\n",
    "with open('departments.json', 'w') as fp:\n",
    "    json.dump(departments, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AccessionNumber</th>\n",
       "      <th>ProjectNumber1</th>\n",
       "      <th>OrgR</th>\n",
       "      <th>Department1</th>\n",
       "      <th>ProposalNumber</th>\n",
       "      <th>AwardNumber</th>\n",
       "      <th>Title</th>\n",
       "      <th>ProjectDirector</th>\n",
       "      <th>CoProjectDirectors</th>\n",
       "      <th>FundingSource</th>\n",
       "      <th>ProjectStartDate</th>\n",
       "      <th>ProjectEndDate</th>\n",
       "      <th>ProjectStatus</th>\n",
       "      <th>IsInterdepartmental</th>\n",
       "      <th>IsAssociable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>84385</td>\n",
       "      <td>CA-D-MCB-4126-H</td>\n",
       "      <td>BMCB</td>\n",
       "      <td>Molecular and Cellular Biology</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>STRUCTURE FUNCTION STUDIES ON PHYTOCHROME</td>\n",
       "      <td>Lagarias,  J</td>\n",
       "      <td></td>\n",
       "      <td>HATCH</td>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>09/30/2017</td>\n",
       "      <td>Complete Without Final Report</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>87742</td>\n",
       "      <td>CA-D-EVE-4244-H</td>\n",
       "      <td>BEVE</td>\n",
       "      <td>Evolution and Ecology</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Properties of Selection, Mutation and Drift in...</td>\n",
       "      <td>Turelli,  M</td>\n",
       "      <td></td>\n",
       "      <td>HATCH</td>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>09/30/2017</td>\n",
       "      <td>Complete Without Final Report</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>88711</td>\n",
       "      <td>CA-D-ENM-4278-H</td>\n",
       "      <td>AENM</td>\n",
       "      <td>Entomology and Nematology</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>COMMUNICATION BETWEEN PLANTS AND INDUCED PLANT...</td>\n",
       "      <td>Karban,  R</td>\n",
       "      <td></td>\n",
       "      <td>HATCH</td>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>09/30/2017</td>\n",
       "      <td>Complete Without Final Report</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>89754</td>\n",
       "      <td>CA-D-TXC-4313-RR</td>\n",
       "      <td>ATXC</td>\n",
       "      <td>Textiles and Clothing</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Personal Protective Technologies for Current a...</td>\n",
       "      <td>Rucker,  M</td>\n",
       "      <td></td>\n",
       "      <td>HATCH/MULTI-STATE</td>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>09/30/2017</td>\n",
       "      <td>Complete Without Final Report</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>134682</td>\n",
       "      <td>CA-D-FST-4929-H</td>\n",
       "      <td>AFST</td>\n",
       "      <td>Food Science and Technology</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CHEMISTRY OF LIPIDS IN FOODS AND TISSUES</td>\n",
       "      <td>German,  J</td>\n",
       "      <td></td>\n",
       "      <td>HATCH</td>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>09/30/2017</td>\n",
       "      <td>Complete Without Final Report</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AccessionNumber    ProjectNumber1  OrgR                     Department1  \\\n",
       "0            84385   CA-D-MCB-4126-H  BMCB  Molecular and Cellular Biology   \n",
       "1            87742   CA-D-EVE-4244-H  BEVE           Evolution and Ecology   \n",
       "2            88711   CA-D-ENM-4278-H  AENM       Entomology and Nematology   \n",
       "3            89754  CA-D-TXC-4313-RR  ATXC           Textiles and Clothing   \n",
       "4           134682   CA-D-FST-4929-H  AFST     Food Science and Technology   \n",
       "\n",
       "  ProposalNumber AwardNumber  \\\n",
       "0                              \n",
       "1                              \n",
       "2                              \n",
       "3                              \n",
       "4                              \n",
       "\n",
       "                                               Title ProjectDirector  \\\n",
       "0          STRUCTURE FUNCTION STUDIES ON PHYTOCHROME    Lagarias,  J   \n",
       "1  Properties of Selection, Mutation and Drift in...     Turelli,  M   \n",
       "2  COMMUNICATION BETWEEN PLANTS AND INDUCED PLANT...      Karban,  R   \n",
       "3  Personal Protective Technologies for Current a...      Rucker,  M   \n",
       "4           CHEMISTRY OF LIPIDS IN FOODS AND TISSUES      German,  J   \n",
       "\n",
       "  CoProjectDirectors      FundingSource ProjectStartDate ProjectEndDate  \\\n",
       "0                                 HATCH       10/01/2012     09/30/2017   \n",
       "1                                 HATCH       10/01/2012     09/30/2017   \n",
       "2                                 HATCH       10/01/2012     09/30/2017   \n",
       "3                     HATCH/MULTI-STATE       10/01/2012     09/30/2017   \n",
       "4                                 HATCH       10/01/2012     09/30/2017   \n",
       "\n",
       "                   ProjectStatus  IsInterdepartmental  IsAssociable  \n",
       "0  Complete Without Final Report                False          True  \n",
       "1  Complete Without Final Report                False          True  \n",
       "2  Complete Without Final Report                False          True  \n",
       "3  Complete Without Final Report                False          True  \n",
       "4  Complete Without Final Report                False          True  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get all projects and export by project number\n",
    "project_df = pd.read_csv('./allprojects.csv')\n",
    "project_df = project_df.fillna('')\n",
    "project_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "project_data = {}\n",
    "\n",
    "for index, row in project_df.iterrows():\n",
    "    project_data[row['ProjectNumber1']] = { \n",
    "        'OrgR': row['OrgR'],\n",
    "        'AccessionNumber': row['AccessionNumber'],\n",
    "        'Title': row['Title'],\n",
    "        'ProjectDirector': row['ProjectDirector'],\n",
    "        'CoProjectDirectors': row['CoProjectDirectors'],\n",
    "        'ProjectStartDate': row['ProjectStartDate'],\n",
    "        'ProjectEndDate': row['ProjectEndDate']\n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "with open('projects.json', 'w') as fp:\n",
    "    json.dump(project_data, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6",
   "language": "python",
   "name": "python36"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}