foreign_donations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Source\n",
    "\n",
    "Foreign donation data source: https://catalog.data.gov/dataset/foreign-gifts-and-contracts-report-2011\n",
    "\n",
    "Data description from source:\n",
    "\n",
    ">\"The Foreign Gift and Contracts Report contains all gifts received within the previous six years from any foreign source, contracts with a foreign entity, and/or any ownership interests in or control over the institution by a foreign entity reported by Title IV-eligible domestic institutions that offer a bachelor's degree or higher, or that offer a transfer program of not less than two years that is acceptable for credit toward a bachelor's degree, when the total value of all gifts given in a calendar year is $250,000 or greater or if the institution is owned or controlled by a foreign source.\"\n",
    "\n",
    "Data last updated: August 9, 2018\n",
    "\n",
    "**Note**: All recorded donations dated back 6 years, _i.e._ 2012-2018."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initial loading of foreign gifts data from data.gov"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>OPEID</th>\n",
       "      <th>Institution Name</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Foreign Gift Received Date</th>\n",
       "      <th>Foreign Gift Amount</th>\n",
       "      <th>Gift Type</th>\n",
       "      <th>Country of Giftor</th>\n",
       "      <th>Giftor Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>324200</td>\n",
       "      <td>Carnegie Mellon University</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>2015-01-01</td>\n",
       "      <td>77391377</td>\n",
       "      <td>Contract</td>\n",
       "      <td>QATAR</td>\n",
       "      <td>Qatar Foundation for Education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>143400</td>\n",
       "      <td>American University (The)</td>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>2012-01-25</td>\n",
       "      <td>500000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>BAHRAIN</td>\n",
       "      <td>Kingdom of Bahrain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>143400</td>\n",
       "      <td>American University (The)</td>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>2013-05-28</td>\n",
       "      <td>500000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>BAHRAIN</td>\n",
       "      <td>Sheikh Salman Bin Al-Khalifa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>143400</td>\n",
       "      <td>American University (The)</td>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>2015-09-10</td>\n",
       "      <td>500000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>KUWAIT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>143400</td>\n",
       "      <td>American University (The)</td>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>2012-05-17</td>\n",
       "      <td>610000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>143400</td>\n",
       "      <td>American University (The)</td>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>2014-04-24</td>\n",
       "      <td>1000000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>Anonymous</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>143400</td>\n",
       "      <td>American University (The)</td>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>2015-04-13</td>\n",
       "      <td>1000000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>ENGLAND</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>211500</td>\n",
       "      <td>Amherst College</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>MA</td>\n",
       "      <td>2018-04-05</td>\n",
       "      <td>500000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>MONACO</td>\n",
       "      <td>Prince Albert II</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2013-03-22</td>\n",
       "      <td>23</td>\n",
       "      <td>Contract</td>\n",
       "      <td>FRANCE</td>\n",
       "      <td>Center Hospitalier Univers. Vaudois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2018-03-28</td>\n",
       "      <td>130</td>\n",
       "      <td>Contract</td>\n",
       "      <td>VIETNAM</td>\n",
       "      <td>Intel Products Vietnam Co., Ltd.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2018-06-28</td>\n",
       "      <td>450</td>\n",
       "      <td>Contract</td>\n",
       "      <td>MEXICO</td>\n",
       "      <td>Tecnologico de Monterrey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2015-12-04</td>\n",
       "      <td>895</td>\n",
       "      <td>Contract</td>\n",
       "      <td>ISRAEL</td>\n",
       "      <td>Aora Solar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2015-11-12</td>\n",
       "      <td>2580</td>\n",
       "      <td>Contract</td>\n",
       "      <td>QATAR</td>\n",
       "      <td>RasGas Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2016-09-06</td>\n",
       "      <td>5310</td>\n",
       "      <td>Contract</td>\n",
       "      <td>QATAR</td>\n",
       "      <td>Sidra Medical and Research Center</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>324200</td>\n",
       "      <td>Carnegie Mellon University</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>67700000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>THAILAND</td>\n",
       "      <td>CMKL University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>229000</td>\n",
       "      <td>Michigan State University</td>\n",
       "      <td>East Lansing</td>\n",
       "      <td>MI</td>\n",
       "      <td>2012-12-14</td>\n",
       "      <td>45128974</td>\n",
       "      <td>Contract</td>\n",
       "      <td>CANADA</td>\n",
       "      <td>MasterCard Foundation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>137000</td>\n",
       "      <td>University of Colorado Boulder</td>\n",
       "      <td>Boulder</td>\n",
       "      <td>CO</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>43021197</td>\n",
       "      <td>Contract</td>\n",
       "      <td>UNITED ARAB EMIRATES</td>\n",
       "      <td>EMIRATES INSTITUTION FOR ADVANCED S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>217800</td>\n",
       "      <td>Massachusetts Institute of Technology</td>\n",
       "      <td>Cambridge</td>\n",
       "      <td>MA</td>\n",
       "      <td>2014-05-06</td>\n",
       "      <td>43000000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>SAUDI ARABIA</td>\n",
       "      <td>Mr. Mohammed A. Jameel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>373500</td>\n",
       "      <td>Virginia Commonwealth University</td>\n",
       "      <td>Richmond</td>\n",
       "      <td>VA</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>40092588</td>\n",
       "      <td>Contract</td>\n",
       "      <td>QATAR</td>\n",
       "      <td>Qatar Foundation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>113100</td>\n",
       "      <td>California Institute of Technology</td>\n",
       "      <td>Pasadena</td>\n",
       "      <td>CA</td>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>40000000</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>SINGAPORE</td>\n",
       "      <td>Tianqiao Chen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>110800</td>\n",
       "      <td>University of Arkansas</td>\n",
       "      <td>Fayetteville</td>\n",
       "      <td>AR</td>\n",
       "      <td>2015-12-31</td>\n",
       "      <td>39108635</td>\n",
       "      <td>Contract</td>\n",
       "      <td>IRAQ</td>\n",
       "      <td>Republic of Iraq, Higher Committee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2017-10-12</td>\n",
       "      <td>5436</td>\n",
       "      <td>Contract</td>\n",
       "      <td>UNITED ARAB EMIRATES</td>\n",
       "      <td>Scholarships Office, The</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2014-01-22</td>\n",
       "      <td>5763</td>\n",
       "      <td>Contract</td>\n",
       "      <td>JAPAN</td>\n",
       "      <td>Asia University</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2016-08-04</td>\n",
       "      <td>5980</td>\n",
       "      <td>Contract</td>\n",
       "      <td>VIETNAM</td>\n",
       "      <td>Intel Products Vietnam Co Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>108100</td>\n",
       "      <td>Arizona State University</td>\n",
       "      <td>Tempe</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2016-11-15</td>\n",
       "      <td>5980</td>\n",
       "      <td>Contract</td>\n",
       "      <td>VIETNAM</td>\n",
       "      <td>Intel Products Vietnam Co Ltd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID   OPEID                       Institution Name          City State  \\\n",
       "0    1  324200             Carnegie Mellon University    Pittsburgh    PA   \n",
       "1    2  143400              American University (The)    Washington    DC   \n",
       "2    3  143400              American University (The)    Washington    DC   \n",
       "3    4  143400              American University (The)    Washington    DC   \n",
       "4    5  143400              American University (The)    Washington    DC   \n",
       "5    6  143400              American University (The)    Washington    DC   \n",
       "6    7  143400              American University (The)    Washington    DC   \n",
       "7    8  211500                        Amherst College       Amherst    MA   \n",
       "8    9  108100               Arizona State University         Tempe    AZ   \n",
       "9   10  108100               Arizona State University         Tempe    AZ   \n",
       "10  11  108100               Arizona State University         Tempe    AZ   \n",
       "11  12  108100               Arizona State University         Tempe    AZ   \n",
       "12  13  108100               Arizona State University         Tempe    AZ   \n",
       "13  14  108100               Arizona State University         Tempe    AZ   \n",
       "14  15  324200             Carnegie Mellon University    Pittsburgh    PA   \n",
       "15  16  229000              Michigan State University  East Lansing    MI   \n",
       "16  17  137000         University of Colorado Boulder       Boulder    CO   \n",
       "17  18  217800  Massachusetts Institute of Technology     Cambridge    MA   \n",
       "18  19  373500       Virginia Commonwealth University      Richmond    VA   \n",
       "19  20  113100     California Institute of Technology      Pasadena    CA   \n",
       "20  21  110800                 University of Arkansas  Fayetteville    AR   \n",
       "21  22  108100               Arizona State University         Tempe    AZ   \n",
       "22  23  108100               Arizona State University         Tempe    AZ   \n",
       "23  24  108100               Arizona State University         Tempe    AZ   \n",
       "24  25  108100               Arizona State University         Tempe    AZ   \n",
       "\n",
       "   Foreign Gift Received Date  Foreign Gift Amount      Gift Type  \\\n",
       "0                  2015-01-01             77391377       Contract   \n",
       "1                  2012-01-25               500000  Monetary Gift   \n",
       "2                  2013-05-28               500000  Monetary Gift   \n",
       "3                  2015-09-10               500000  Monetary Gift   \n",
       "4                  2012-05-17               610000  Monetary Gift   \n",
       "5                  2014-04-24              1000000  Monetary Gift   \n",
       "6                  2015-04-13              1000000  Monetary Gift   \n",
       "7                  2018-04-05               500000  Monetary Gift   \n",
       "8                  2013-03-22                   23       Contract   \n",
       "9                  2018-03-28                  130       Contract   \n",
       "10                 2018-06-28                  450       Contract   \n",
       "11                 2015-12-04                  895       Contract   \n",
       "12                 2015-11-12                 2580       Contract   \n",
       "13                 2016-09-06                 5310       Contract   \n",
       "14                 2017-10-27             67700000       Contract   \n",
       "15                 2012-12-14             45128974       Contract   \n",
       "16                 2014-08-01             43021197       Contract   \n",
       "17                 2014-05-06             43000000  Monetary Gift   \n",
       "18                 2015-07-01             40092588       Contract   \n",
       "19                 2016-11-29             40000000  Monetary Gift   \n",
       "20                 2015-12-31             39108635       Contract   \n",
       "21                 2017-10-12                 5436       Contract   \n",
       "22                 2014-01-22                 5763       Contract   \n",
       "23                 2016-08-04                 5980       Contract   \n",
       "24                 2016-11-15                 5980       Contract   \n",
       "\n",
       "       Country of Giftor                          Giftor Name  \n",
       "0                  QATAR       Qatar Foundation for Education  \n",
       "1                BAHRAIN                   Kingdom of Bahrain  \n",
       "2                BAHRAIN         Sheikh Salman Bin Al-Khalifa  \n",
       "3                 KUWAIT                                  NaN  \n",
       "4                ENGLAND                                  NaN  \n",
       "5                ENGLAND                           Anonymous   \n",
       "6                ENGLAND                                  NaN  \n",
       "7                 MONACO                     Prince Albert II  \n",
       "8                 FRANCE  Center Hospitalier Univers. Vaudois  \n",
       "9                VIETNAM     Intel Products Vietnam Co., Ltd.  \n",
       "10                MEXICO             Tecnologico de Monterrey  \n",
       "11                ISRAEL                           Aora Solar  \n",
       "12                 QATAR               RasGas Company Limited  \n",
       "13                 QATAR    Sidra Medical and Research Center  \n",
       "14              THAILAND                      CMKL University  \n",
       "15                CANADA                MasterCard Foundation  \n",
       "16  UNITED ARAB EMIRATES  EMIRATES INSTITUTION FOR ADVANCED S  \n",
       "17          SAUDI ARABIA               Mr. Mohammed A. Jameel  \n",
       "18                 QATAR                     Qatar Foundation  \n",
       "19             SINGAPORE                        Tianqiao Chen  \n",
       "20                  IRAQ  Republic of Iraq, Higher Committee   \n",
       "21  UNITED ARAB EMIRATES             Scholarships Office, The  \n",
       "22                 JAPAN                      Asia University  \n",
       "23               VIETNAM        Intel Products Vietnam Co Ltd  \n",
       "24               VIETNAM        Intel Products Vietnam Co Ltd  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%matplotlib widget\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "\n",
    "sns.set()\n",
    "\n",
    "donations_df = pd.read_excel('./data/ForeignGifts.xls', skiprows=1)\n",
    "# donations_df.loc[:, 'Foreign Gift Amount'] = donations_df.loc[:, 'Foreign Gift Amount'].apply('{:,}'.format)\n",
    "donations_df.head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Universities receiving foreign donations  \n",
    "### Top 10 Universities based on number of foreign donations received"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Institution Name\n",
       "University of California, Los Angeles          3611\n",
       "California Institute of Technology             3364\n",
       "Johns Hopkins University                       1181\n",
       "Columbia University in the City of New York     828\n",
       "Ohio State University (The)                     795\n",
       "University of Michigan - Ann Arbor              702\n",
       "Stanford University                             659\n",
       "Harvard University                              564\n",
       "Massachusetts Institute of Technology           415\n",
       "University of Colorado Denver                   412\n",
       "Name: ID, dtype: int64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(donations_df\n",
    " .groupby('Institution Name')   # Grouping by unisveristy\n",
    " .count()                       # Applying count() to group\n",
    " .iloc[:, 0]                    # Only display first column\n",
    " .sort_values(ascending=False)  # Sort in decending order\n",
    " .head(10))                     # Only show top 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8521aaaf4c724807b55234f47eae4ec7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "\n",
    "top10_by_counts = (\n",
    "    donations_df.groupby('Institution Name')\n",
    "    .count()\n",
    "    .iloc[:, 0]\n",
    "    .sort_values(ascending=True)\n",
    "    .iloc[-10:])\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(10,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(top10_by_counts.index, top10_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations received')\n",
    "ax.set_title('Top 10 universities based on number of foreign donations recieved (2012-2018)')\n",
    "for i, v in enumerate(top10_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)\n",
    "plt.tight_layout()\n",
    "fig.show()\n",
    "plt.savefig('temp.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 schools based on total dollars of foreign donations received"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Institution Name\n",
       "Carnegie Mellon University               1378003807\n",
       "Harvard University                       1143899998\n",
       "Massachusetts Institute of Technology     660830374\n",
       "Johns Hopkins University                  447459034\n",
       "Northwestern University                   420550173\n",
       "Georgetown University                     415622195\n",
       "University of California, Berkeley        336801721\n",
       "Duke University                           314693237\n",
       "Stanford University                       313775195\n",
       "New York University                       291391003\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(donations_df\n",
    " .groupby('Institution Name')     # Group by university\n",
    " .sum()                           # Apply sum() to all columns\n",
    " .loc[:, 'Foreign Gift Amount']   # Only display 'Foresign Gift Amount' columm\n",
    " .sort_values(ascending=False)    # Sort in decending\n",
    " .head(10))                        # Display top 10 entries only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import dill\n",
    "\n",
    "\n",
    "top10_schools_all = (donations_df\n",
    " .groupby('Institution Name')     # Group by university\n",
    " .sum()                           # Apply sum() to all columns\n",
    " .loc[:, 'Foreign Gift Amount']   # Only display 'Foresign Gift Amount' columm\n",
    " .sort_values(ascending=False)    # Sort in decending\n",
    " .head(10))                        # Display top 10 entries only\n",
    "\n",
    "dill.dump(top10_schools_all, open('./top_10_schools_all.pkd', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "46456436c13c4c0fa5fae94d7a58d63f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10_by_dollars = (donations_df\n",
    "    .groupby('Institution Name')\n",
    "    .sum()\n",
    "    .loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=True)\n",
    "    .iloc[-10:])\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(10,5))                                  # Initialize pyplot area\n",
    "ax.barh(top10_by_dollars.index, top10_by_dollars.values*(10**-9), color='purple')   # Generating bar chart\n",
    "ax.set_xlabel('Billion Dollars')\n",
    "ax.set_title('Top 10 universities based on dollars of foreign donations received (2012-2018)')\n",
    "for i, v in enumerate(top10_by_dollars):\n",
    "    ax.text(v*(10**-9), i,'\\$'+str('{:.2f}'.format(v*(10**-9))+'B'), color='black')\n",
    "plt.margins(x=0.15)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 schools based on total dollars of foreign donations received (cash only)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Institution Name\n",
       "Harvard University                             729378154\n",
       "Massachusetts Institute of Technology          582888432\n",
       "Johns Hopkins University                       447443471\n",
       "Stanford University                            305730258\n",
       "Duke University                                242147330\n",
       "Columbia University in the City of New York    146003397\n",
       "New York University                            140389153\n",
       "University of Pennsylvania                     135399121\n",
       "University of California, Berkeley             132745273\n",
       "Yale University                                112274698\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(donations_df\n",
    " .loc[donations_df['Gift Type']=='Monetary Gift']  # Pullinng all rows that are 'Monetary Gift' only (at 'Gift Type' column)\n",
    " .groupby('Institution Name')                      # Group by university\n",
    " .sum()                                            # Apply sum() over columns\n",
    " .loc[:, 'Foreign Gift Amount']                    # Only display 'Foreign Gift Amount column'\n",
    " .sort_values(ascending=False)                     # Sort in descending order\n",
    " .head(10))                                        # Display top 10 entries only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4849143acbb24ca4bf59295f6873800d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10_by_cash = (donations_df.loc[donations_df['Gift Type']=='Monetary Gift']\n",
    " .groupby('Institution Name')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=True)\n",
    " .iloc[-10:])\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(10,5))                                   # Initializing pyplot area\n",
    "ax.barh(top10_by_cash.index, top10_by_cash.values*(10**-6), color='purple')    # Generating bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Top 10 universities based on dollar of foreign cash donations recieved (2012-2018)')\n",
    "for i, v in enumerate(top10_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)\n",
    "\n",
    "# Note: this is donated contract + cash gifts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Source\n",
    "\n",
    "Democracy Index data scrapped from: https://en.wikipedia.org/wiki/Democracy_Index, original data from: https://www.eiu.com/public/topical_report.aspx?campaignid=democracyindex2019\n",
    "\n",
    "Democracy Index description from source: \n",
    ">\"The EIU Democracy Index provides a snapshot of the state of world democracy for 165 independent states and two territories. The Democracy Index is based on five categories: electoral process and pluralism; civil liberties; the functioning of government; political participation; and political culture. Based on their scores on 60 indicators within these categories, each country is then itself classified as one of four types of regime: full democracy; flawed democracy; hybrid regime; and authoritarian regime.\"\n",
    "\n",
    "**Note**: Full Democracy Index report available on repository"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initial loading of Democracy Index data\n",
    "\n",
    "**Note**: Data scrapped from Wikipedia, which cited the [EIU](https://www.eiu.com/topic/democracy-index), via the MS Excel \"data from web\" function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Score</th>\n",
       "      <th>Electoral process and pluralism</th>\n",
       "      <th>Functioning of government</th>\n",
       "      <th>Political participation</th>\n",
       "      <th>Political culture</th>\n",
       "      <th>Civil liberties</th>\n",
       "      <th>Regime type</th>\n",
       "      <th>Region</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Norway</th>\n",
       "      <td>1</td>\n",
       "      <td>9.87</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.64</td>\n",
       "      <td>10.00</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.71</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iceland</th>\n",
       "      <td>2</td>\n",
       "      <td>9.58</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.29</td>\n",
       "      <td>8.89</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.71</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>3</td>\n",
       "      <td>9.39</td>\n",
       "      <td>9.58</td>\n",
       "      <td>9.64</td>\n",
       "      <td>8.33</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.41</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Zealand</th>\n",
       "      <td>4</td>\n",
       "      <td>9.26</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.29</td>\n",
       "      <td>8.89</td>\n",
       "      <td>8.13</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finland</th>\n",
       "      <td>5</td>\n",
       "      <td>9.25</td>\n",
       "      <td>10.00</td>\n",
       "      <td>8.93</td>\n",
       "      <td>8.89</td>\n",
       "      <td>8.75</td>\n",
       "      <td>9.71</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ireland</th>\n",
       "      <td>6</td>\n",
       "      <td>9.24</td>\n",
       "      <td>10.00</td>\n",
       "      <td>7.86</td>\n",
       "      <td>8.33</td>\n",
       "      <td>10.00</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>7</td>\n",
       "      <td>9.22</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.29</td>\n",
       "      <td>8.33</td>\n",
       "      <td>9.38</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada</th>\n",
       "      <td>7</td>\n",
       "      <td>9.22</td>\n",
       "      <td>9.58</td>\n",
       "      <td>9.64</td>\n",
       "      <td>7.78</td>\n",
       "      <td>9.38</td>\n",
       "      <td>9.71</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>9</td>\n",
       "      <td>9.09</td>\n",
       "      <td>10.00</td>\n",
       "      <td>8.93</td>\n",
       "      <td>7.78</td>\n",
       "      <td>8.75</td>\n",
       "      <td>10.00</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Switzerland</th>\n",
       "      <td>10</td>\n",
       "      <td>9.03</td>\n",
       "      <td>9.58</td>\n",
       "      <td>9.29</td>\n",
       "      <td>7.78</td>\n",
       "      <td>9.38</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>11</td>\n",
       "      <td>9.01</td>\n",
       "      <td>9.58</td>\n",
       "      <td>9.29</td>\n",
       "      <td>8.33</td>\n",
       "      <td>8.75</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Luxembourg</th>\n",
       "      <td>12</td>\n",
       "      <td>8.81</td>\n",
       "      <td>10.00</td>\n",
       "      <td>8.93</td>\n",
       "      <td>6.67</td>\n",
       "      <td>8.75</td>\n",
       "      <td>9.71</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>13</td>\n",
       "      <td>8.68</td>\n",
       "      <td>9.58</td>\n",
       "      <td>8.57</td>\n",
       "      <td>8.33</td>\n",
       "      <td>7.50</td>\n",
       "      <td>9.41</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Kingdom</th>\n",
       "      <td>14</td>\n",
       "      <td>8.52</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.50</td>\n",
       "      <td>8.89</td>\n",
       "      <td>7.50</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uruguay</th>\n",
       "      <td>15</td>\n",
       "      <td>8.38</td>\n",
       "      <td>10.00</td>\n",
       "      <td>8.57</td>\n",
       "      <td>6.11</td>\n",
       "      <td>7.50</td>\n",
       "      <td>9.71</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Latin America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>16</td>\n",
       "      <td>8.29</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.86</td>\n",
       "      <td>8.33</td>\n",
       "      <td>6.88</td>\n",
       "      <td>8.82</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>16</td>\n",
       "      <td>8.29</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.14</td>\n",
       "      <td>7.78</td>\n",
       "      <td>8.13</td>\n",
       "      <td>8.82</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mauritius</th>\n",
       "      <td>18</td>\n",
       "      <td>8.22</td>\n",
       "      <td>9.17</td>\n",
       "      <td>8.21</td>\n",
       "      <td>5.56</td>\n",
       "      <td>8.75</td>\n",
       "      <td>9.41</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Costa Rica</th>\n",
       "      <td>19</td>\n",
       "      <td>8.13</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.50</td>\n",
       "      <td>6.67</td>\n",
       "      <td>7.50</td>\n",
       "      <td>9.41</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Latin America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>20</td>\n",
       "      <td>8.12</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.86</td>\n",
       "      <td>7.78</td>\n",
       "      <td>6.88</td>\n",
       "      <td>8.53</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Rank  Score  Electoral process and pluralism  \\\n",
       "Country                                                        \n",
       "Norway             1   9.87                            10.00   \n",
       "Iceland            2   9.58                            10.00   \n",
       "Sweden             3   9.39                             9.58   \n",
       "New Zealand        4   9.26                            10.00   \n",
       "Finland            5   9.25                            10.00   \n",
       "Ireland            6   9.24                            10.00   \n",
       "Denmark            7   9.22                            10.00   \n",
       "Canada             7   9.22                             9.58   \n",
       "Australia          9   9.09                            10.00   \n",
       "Switzerland       10   9.03                             9.58   \n",
       "Netherlands       11   9.01                             9.58   \n",
       "Luxembourg        12   8.81                            10.00   \n",
       "Germany           13   8.68                             9.58   \n",
       "United Kingdom    14   8.52                             9.58   \n",
       "Uruguay           15   8.38                            10.00   \n",
       "Austria           16   8.29                             9.58   \n",
       "Spain             16   8.29                             9.58   \n",
       "Mauritius         18   8.22                             9.17   \n",
       "Costa Rica        19   8.13                             9.58   \n",
       "France            20   8.12                             9.58   \n",
       "\n",
       "                Functioning of government  Political participation  \\\n",
       "Country                                                              \n",
       "Norway                               9.64                    10.00   \n",
       "Iceland                              9.29                     8.89   \n",
       "Sweden                               9.64                     8.33   \n",
       "New Zealand                          9.29                     8.89   \n",
       "Finland                              8.93                     8.89   \n",
       "Ireland                              7.86                     8.33   \n",
       "Denmark                              9.29                     8.33   \n",
       "Canada                               9.64                     7.78   \n",
       "Australia                            8.93                     7.78   \n",
       "Switzerland                          9.29                     7.78   \n",
       "Netherlands                          9.29                     8.33   \n",
       "Luxembourg                           8.93                     6.67   \n",
       "Germany                              8.57                     8.33   \n",
       "United Kingdom                       7.50                     8.89   \n",
       "Uruguay                              8.57                     6.11   \n",
       "Austria                              7.86                     8.33   \n",
       "Spain                                7.14                     7.78   \n",
       "Mauritius                            8.21                     5.56   \n",
       "Costa Rica                           7.50                     6.67   \n",
       "France                               7.86                     7.78   \n",
       "\n",
       "                Political culture  Civil liberties     Regime type  \\\n",
       "Country                                                              \n",
       "Norway                      10.00             9.71  Full democracy   \n",
       "Iceland                     10.00             9.71  Full democracy   \n",
       "Sweden                      10.00             9.41  Full democracy   \n",
       "New Zealand                  8.13            10.00  Full democracy   \n",
       "Finland                      8.75             9.71  Full democracy   \n",
       "Ireland                     10.00            10.00  Full democracy   \n",
       "Denmark                      9.38             9.12  Full democracy   \n",
       "Canada                       9.38             9.71  Full democracy   \n",
       "Australia                    8.75            10.00  Full democracy   \n",
       "Switzerland                  9.38             9.12  Full democracy   \n",
       "Netherlands                  8.75             9.12  Full democracy   \n",
       "Luxembourg                   8.75             9.71  Full democracy   \n",
       "Germany                      7.50             9.41  Full democracy   \n",
       "United Kingdom               7.50             9.12  Full democracy   \n",
       "Uruguay                      7.50             9.71  Full democracy   \n",
       "Austria                      6.88             8.82  Full democracy   \n",
       "Spain                        8.13             8.82  Full democracy   \n",
       "Mauritius                    8.75             9.41  Full democracy   \n",
       "Costa Rica                   7.50             9.41  Full democracy   \n",
       "France                       6.88             8.53  Full democracy   \n",
       "\n",
       "                            Region  \n",
       "Country                             \n",
       "Norway              Western Europe  \n",
       "Iceland             Western Europe  \n",
       "Sweden              Western Europe  \n",
       "New Zealand     Asia & Australasia  \n",
       "Finland             Western Europe  \n",
       "Ireland             Western Europe  \n",
       "Denmark             Western Europe  \n",
       "Canada               North America  \n",
       "Australia       Asia & Australasia  \n",
       "Switzerland         Western Europe  \n",
       "Netherlands         Western Europe  \n",
       "Luxembourg          Western Europe  \n",
       "Germany             Western Europe  \n",
       "United Kingdom      Western Europe  \n",
       "Uruguay              Latin America  \n",
       "Austria             Western Europe  \n",
       "Spain               Western Europe  \n",
       "Mauritius       Sub-Saharan Africa  \n",
       "Costa Rica           Latin America  \n",
       "France              Western Europe  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "labels = [                                    # List of column headings for democracy index data\n",
    "    'Rank',\n",
    "    'Country',\n",
    "    'Score',\n",
    "    'Electoral process and pluralism',\n",
    "    'Functioning of government',\n",
    "    'Political participation',\n",
    "    'Political culture',\n",
    "    'Civil liberties',\n",
    "    'Regime type',\n",
    "    'Region',\n",
    "    '','','','','']                          # Empty labels needed because of the shape of the data file\n",
    "democracy_df = pd.read_excel(\n",
    "    './data/democracy_index.xlsx',\n",
    "    names=labels,                            # Use list generated above for column labels\n",
    "    skiprows=148)                            # Number of rows to skip determined by brute force trail and error\n",
    "\n",
    "democracy_df.drop(\n",
    "    ['', '.1', '.2', '.3', '.4'],            # Dropping empty columns        \n",
    "    axis=1,\n",
    "    inplace=True)\n",
    "democracy_df = democracy_df.iloc[2:310]      # Dropping superfluous columns to the left\n",
    "democracy_df.loc[:, 'Country'] = (\n",
    "    democracy_df.loc[:, 'Country']           # There were extra whitespace to the left of the name of countries \n",
    "    .str                                     # Accessing string methods\n",
    "    .strip())                                # Strip white space on both side\n",
    "democracy_df = (                             \n",
    "    democracy_df.loc[democracy_df['Country'] # Slicing out all empty rows\n",
    "    .isnull() == False])                     # Identifying empty rows by empty cells in 'Country' column    \n",
    "democracy_df.fillna(                         # Fill in missing rankings\n",
    "    method='ffill',\n",
    "    inplace=True)\n",
    "democracy_df.set_index('Country', inplace=True)\n",
    "democracy_df.rename(                         # Rename the one country name with superscript notes\n",
    "    index={'South Korea[n 2]': 'South Korea'},\n",
    "    inplace=True)\n",
    "democracy_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9,)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "democracy_df.loc['Qatar'].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Democracy Index data clean-up and merger with donation dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pulling all unique foreign nations that made a donation to US universities\n",
    "\n",
    "donation_countries = set(donations_df\n",
    "                         .loc[:, 'Country of Giftor']\n",
    "                         .str\n",
    "                         .title())                      # Transform name of countries to title case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pulling all countries with a democracy rating\n",
    "\n",
    "democracy_countries = set(democracy_df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Bahamas',\n",
       " 'Barbados',\n",
       " 'Bermuda',\n",
       " 'Bosnia And Herzegovina',\n",
       " 'Bosnia-Hercegovina',\n",
       " 'British West Indies',\n",
       " 'Burma',\n",
       " 'Cayman Islands (The)',\n",
       " 'Ceylon',\n",
       " 'Curaçao',\n",
       " 'England',\n",
       " 'Grenada',\n",
       " 'Guam',\n",
       " 'Guernsey',\n",
       " 'Isle Of Man',\n",
       " 'Jersey',\n",
       " 'Korea',\n",
       " 'Liechtenstein',\n",
       " 'Marshal Islands (The)',\n",
       " 'Monaco',\n",
       " 'Palestine, State Of',\n",
       " 'Puerto Rico',\n",
       " 'Scotland',\n",
       " 'St. Lucia',\n",
       " 'Swaziland',\n",
       " 'Syrian Arab Republic',\n",
       " 'The Netherlands',\n",
       " 'Tokelau',\n",
       " 'Trinidad',\n",
       " 'United Nations',\n",
       " 'Usa',\n",
       " 'Virgin Islands (British)',\n",
       " 'Wales'}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Find countries that donated that does not appear in democracy index\n",
    "\n",
    "intersect = donation_countries & democracy_countries  # Set intersection\n",
    "donation_countries - intersect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Construct rename dictionary to correlate country names between the two datasets\n",
    "\n",
    "countries_dict = {\n",
    " 'Bahamas': 'Not Rated',                      # Countries that noted but not rated a Democracy Index are marked 'Not Rated'\n",
    " 'Barbados': 'Not Rated',\n",
    " 'Bermuda': 'United Kingdom',                 # Oversea territory of the UK\n",
    " 'Bosnia And Herzegovina': 'Bosnia and Herzegovina',\n",
    " 'Bosnia-Hercegovina': 'Bosnia and Herzegovina',\n",
    " 'British West Indies': 'United Kingdom',     # Former British territory\n",
    " 'Burma': 'Myanmar',\n",
    " 'Cayman Islands (The)': 'United Kingdom',    # Oversea territory of the UK\n",
    " 'Ceylon': 'Sri Lanka',                       # Part of the Netherlands\n",
    " 'Curaçao': 'Netherlands',\n",
    " 'England': 'United Kingdom',                 # Part of the UK\n",
    " 'Grenada': 'Not Rated',\n",
    " 'Guam': 'United States',                     # Territory of the US\n",
    " 'Guernsey': 'United Kingdom',                # British Crown Dependency\n",
    " 'Isle Of Man': 'United Kingdom',             # British Crown Dependency\n",
    " 'Jersey': 'United Kingdom',                  # British Crown Dependency\n",
    " 'Korea': 'South Korea',\n",
    " 'Liechtenstein': 'Not Rated',\n",
    " 'Marshal Islands (The)': 'Not Rated',\n",
    " 'Monaco': 'Not Rated',\n",
    " 'Palestine, State Of': 'Palestine',\n",
    " 'Puerto Rico': 'United States',             # Commonwealth of the US\n",
    " 'Scotland': 'United Kingdom',               # Part of the UK\n",
    " 'St. Lucia': 'Not Rated',\n",
    " 'Swaziland': 'Not Rated',\n",
    " 'Syrian Arab Republic': 'Syria',\n",
    " 'The Netherlands': 'Netherlands',\n",
    " 'Tokelau': 'New Zealand',                   # Dependent territory of New Zealand\n",
    " 'Trinidad': 'Trinidad and Tobago',\n",
    " 'United Nations': 'Not Rated',\n",
    " 'Usa': 'United States',\n",
    " 'Virgin Islands (British)': 'United Kingdom',  # Oversea territory of the UK\n",
    " 'Wales': 'United Kingdom',                  # Part of the UK\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>OPEID</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Foreign Gift Received Date</th>\n",
       "      <th>Foreign Gift Amount</th>\n",
       "      <th>Gift Type</th>\n",
       "      <th>Giftor Name</th>\n",
       "      <th>Country to merge on</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Score</th>\n",
       "      <th>Electoral process and pluralism</th>\n",
       "      <th>Functioning of government</th>\n",
       "      <th>Political participation</th>\n",
       "      <th>Political culture</th>\n",
       "      <th>Civil liberties</th>\n",
       "      <th>Regime type</th>\n",
       "      <th>Region</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country of Giftor</th>\n",
       "      <th>Institution Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"15\" valign=\"top\">Afghanistan</th>\n",
       "      <th>Ball State University</th>\n",
       "      <td>830</td>\n",
       "      <td>178600</td>\n",
       "      <td>Muncie</td>\n",
       "      <td>IN</td>\n",
       "      <td>2013-08-28</td>\n",
       "      <td>3000000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>Ministry of Education</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>1243</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2012-11-01</td>\n",
       "      <td>1331460</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>2482</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2012-10-01</td>\n",
       "      <td>715815</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>2557</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2012-05-31</td>\n",
       "      <td>690710</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>6736</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2013-05-31</td>\n",
       "      <td>250004</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>7164</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2013-06-30</td>\n",
       "      <td>240059</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>8562</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2012-03-31</td>\n",
       "      <td>144089</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>9178</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2013-03-30</td>\n",
       "      <td>115952</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johns Hopkins University</th>\n",
       "      <td>12676</td>\n",
       "      <td>207700</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>2013-04-30</td>\n",
       "      <td>23375</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio University</th>\n",
       "      <td>7315</td>\n",
       "      <td>310000</td>\n",
       "      <td>Athens</td>\n",
       "      <td>OH</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>224000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>Mn Pub Wrks Islamic Rep Afghanistan</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio University</th>\n",
       "      <td>7999</td>\n",
       "      <td>310000</td>\n",
       "      <td>Athens</td>\n",
       "      <td>OH</td>\n",
       "      <td>2018-10-18</td>\n",
       "      <td>171004</td>\n",
       "      <td>Contract</td>\n",
       "      <td>Mn Pub Wrks Islamic Rep Afghanistan</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio University</th>\n",
       "      <td>9060</td>\n",
       "      <td>310000</td>\n",
       "      <td>Athens</td>\n",
       "      <td>OH</td>\n",
       "      <td>2018-07-17</td>\n",
       "      <td>121098</td>\n",
       "      <td>Contract</td>\n",
       "      <td>Mn Pub Wrks Islamic Rep Afghanistan</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Illinois at Urbana-Champaign</th>\n",
       "      <td>4690</td>\n",
       "      <td>177500</td>\n",
       "      <td>Champaign</td>\n",
       "      <td>IL</td>\n",
       "      <td>2014-06-09</td>\n",
       "      <td>378900</td>\n",
       "      <td>Monetary Gift</td>\n",
       "      <td>Council for the Develop of Social</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Illinois at Urbana-Champaign</th>\n",
       "      <td>6570</td>\n",
       "      <td>177500</td>\n",
       "      <td>Champaign</td>\n",
       "      <td>IL</td>\n",
       "      <td>2018-10-08</td>\n",
       "      <td>259619</td>\n",
       "      <td>Contract</td>\n",
       "      <td>DSM Biotechnology Center</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Illinois at Urbana-Champaign</th>\n",
       "      <td>19312</td>\n",
       "      <td>177500</td>\n",
       "      <td>Champaign</td>\n",
       "      <td>IL</td>\n",
       "      <td>2014-06-24</td>\n",
       "      <td>1225689</td>\n",
       "      <td>Contract</td>\n",
       "      <td>Ministry of Health of Rwanda</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>141.0</td>\n",
       "      <td>2.85</td>\n",
       "      <td>3.42</td>\n",
       "      <td>0.64</td>\n",
       "      <td>3.89</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.82</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <th>University of Oklahoma</th>\n",
       "      <td>3675</td>\n",
       "      <td>318400</td>\n",
       "      <td>Norman</td>\n",
       "      <td>OK</td>\n",
       "      <td>2018-08-20</td>\n",
       "      <td>499908</td>\n",
       "      <td>Contract</td>\n",
       "      <td>Western African Management Service</td>\n",
       "      <td>Angola</td>\n",
       "      <td>119.0</td>\n",
       "      <td>3.72</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2.86</td>\n",
       "      <td>5.56</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.94</td>\n",
       "      <td>Authoritarian</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Argentina</th>\n",
       "      <th>Columbia University in the City of New York</th>\n",
       "      <td>6335</td>\n",
       "      <td>270700</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>2016-07-19</td>\n",
       "      <td>280000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>FUNDACIÓN RED DE ACCIÓN POLÍTICA</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>48.0</td>\n",
       "      <td>7.02</td>\n",
       "      <td>9.17</td>\n",
       "      <td>5.36</td>\n",
       "      <td>6.11</td>\n",
       "      <td>6.25</td>\n",
       "      <td>8.24</td>\n",
       "      <td>Flawed democracy</td>\n",
       "      <td>Latin America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harvard University</th>\n",
       "      <td>4579</td>\n",
       "      <td>215500</td>\n",
       "      <td>Cambridge</td>\n",
       "      <td>MA</td>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>390341</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>48.0</td>\n",
       "      <td>7.02</td>\n",
       "      <td>9.17</td>\n",
       "      <td>5.36</td>\n",
       "      <td>6.11</td>\n",
       "      <td>6.25</td>\n",
       "      <td>8.24</td>\n",
       "      <td>Flawed democracy</td>\n",
       "      <td>Latin America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harvard University</th>\n",
       "      <td>5979</td>\n",
       "      <td>215500</td>\n",
       "      <td>Cambridge</td>\n",
       "      <td>MA</td>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>297798</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>48.0</td>\n",
       "      <td>7.02</td>\n",
       "      <td>9.17</td>\n",
       "      <td>5.36</td>\n",
       "      <td>6.11</td>\n",
       "      <td>6.25</td>\n",
       "      <td>8.24</td>\n",
       "      <td>Flawed democracy</td>\n",
       "      <td>Latin America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harvard University</th>\n",
       "      <td>6448</td>\n",
       "      <td>215500</td>\n",
       "      <td>Cambridge</td>\n",
       "      <td>MA</td>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>266268</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>48.0</td>\n",
       "      <td>7.02</td>\n",
       "      <td>9.17</td>\n",
       "      <td>5.36</td>\n",
       "      <td>6.11</td>\n",
       "      <td>6.25</td>\n",
       "      <td>8.24</td>\n",
       "      <td>Flawed democracy</td>\n",
       "      <td>Latin America</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                  ID   OPEID  \\\n",
       "Country of Giftor Institution Name                                             \n",
       "Afghanistan       Ball State University                          830  178600   \n",
       "                  Johns Hopkins University                      1243  207700   \n",
       "                  Johns Hopkins University                      2482  207700   \n",
       "                  Johns Hopkins University                      2557  207700   \n",
       "                  Johns Hopkins University                      6736  207700   \n",
       "                  Johns Hopkins University                      7164  207700   \n",
       "                  Johns Hopkins University                      8562  207700   \n",
       "                  Johns Hopkins University                      9178  207700   \n",
       "                  Johns Hopkins University                     12676  207700   \n",
       "                  Ohio University                               7315  310000   \n",
       "                  Ohio University                               7999  310000   \n",
       "                  Ohio University                               9060  310000   \n",
       "                  University of Illinois at Urbana-Champaign    4690  177500   \n",
       "                  University of Illinois at Urbana-Champaign    6570  177500   \n",
       "                  University of Illinois at Urbana-Champaign   19312  177500   \n",
       "Angola            University of Oklahoma                        3675  318400   \n",
       "Argentina         Columbia University in the City of New York   6335  270700   \n",
       "                  Harvard University                            4579  215500   \n",
       "                  Harvard University                            5979  215500   \n",
       "                  Harvard University                            6448  215500   \n",
       "\n",
       "                                                                    City  \\\n",
       "Country of Giftor Institution Name                                         \n",
       "Afghanistan       Ball State University                           Muncie   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Johns Hopkins University                     Baltimore   \n",
       "                  Ohio University                                 Athens   \n",
       "                  Ohio University                                 Athens   \n",
       "                  Ohio University                                 Athens   \n",
       "                  University of Illinois at Urbana-Champaign   Champaign   \n",
       "                  University of Illinois at Urbana-Champaign   Champaign   \n",
       "                  University of Illinois at Urbana-Champaign   Champaign   \n",
       "Angola            University of Oklahoma                          Norman   \n",
       "Argentina         Columbia University in the City of New York   New York   \n",
       "                  Harvard University                           Cambridge   \n",
       "                  Harvard University                           Cambridge   \n",
       "                  Harvard University                           Cambridge   \n",
       "\n",
       "                                                              State  \\\n",
       "Country of Giftor Institution Name                                    \n",
       "Afghanistan       Ball State University                          IN   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Johns Hopkins University                       MD   \n",
       "                  Ohio University                                OH   \n",
       "                  Ohio University                                OH   \n",
       "                  Ohio University                                OH   \n",
       "                  University of Illinois at Urbana-Champaign     IL   \n",
       "                  University of Illinois at Urbana-Champaign     IL   \n",
       "                  University of Illinois at Urbana-Champaign     IL   \n",
       "Angola            University of Oklahoma                         OK   \n",
       "Argentina         Columbia University in the City of New York    NY   \n",
       "                  Harvard University                             MA   \n",
       "                  Harvard University                             MA   \n",
       "                  Harvard University                             MA   \n",
       "\n",
       "                                                              Foreign Gift Received Date  \\\n",
       "Country of Giftor Institution Name                                                         \n",
       "Afghanistan       Ball State University                                       2013-08-28   \n",
       "                  Johns Hopkins University                                    2012-11-01   \n",
       "                  Johns Hopkins University                                    2012-10-01   \n",
       "                  Johns Hopkins University                                    2012-05-31   \n",
       "                  Johns Hopkins University                                    2013-05-31   \n",
       "                  Johns Hopkins University                                    2013-06-30   \n",
       "                  Johns Hopkins University                                    2012-03-31   \n",
       "                  Johns Hopkins University                                    2013-03-30   \n",
       "                  Johns Hopkins University                                    2013-04-30   \n",
       "                  Ohio University                                             2018-09-18   \n",
       "                  Ohio University                                             2018-10-18   \n",
       "                  Ohio University                                             2018-07-17   \n",
       "                  University of Illinois at Urbana-Champaign                  2014-06-09   \n",
       "                  University of Illinois at Urbana-Champaign                  2018-10-08   \n",
       "                  University of Illinois at Urbana-Champaign                  2014-06-24   \n",
       "Angola            University of Oklahoma                                      2018-08-20   \n",
       "Argentina         Columbia University in the City of New York                 2016-07-19   \n",
       "                  Harvard University                                          2016-07-01   \n",
       "                  Harvard University                                          2013-07-01   \n",
       "                  Harvard University                                          2012-07-01   \n",
       "\n",
       "                                                               Foreign Gift Amount  \\\n",
       "Country of Giftor Institution Name                                                   \n",
       "Afghanistan       Ball State University                                    3000000   \n",
       "                  Johns Hopkins University                                 1331460   \n",
       "                  Johns Hopkins University                                  715815   \n",
       "                  Johns Hopkins University                                  690710   \n",
       "                  Johns Hopkins University                                  250004   \n",
       "                  Johns Hopkins University                                  240059   \n",
       "                  Johns Hopkins University                                  144089   \n",
       "                  Johns Hopkins University                                  115952   \n",
       "                  Johns Hopkins University                                   23375   \n",
       "                  Ohio University                                           224000   \n",
       "                  Ohio University                                           171004   \n",
       "                  Ohio University                                           121098   \n",
       "                  University of Illinois at Urbana-Champaign                378900   \n",
       "                  University of Illinois at Urbana-Champaign                259619   \n",
       "                  University of Illinois at Urbana-Champaign               1225689   \n",
       "Angola            University of Oklahoma                                    499908   \n",
       "Argentina         Columbia University in the City of New York               280000   \n",
       "                  Harvard University                                        390341   \n",
       "                  Harvard University                                        297798   \n",
       "                  Harvard University                                        266268   \n",
       "\n",
       "                                                                   Gift Type  \\\n",
       "Country of Giftor Institution Name                                             \n",
       "Afghanistan       Ball State University                             Contract   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Johns Hopkins University                     Monetary Gift   \n",
       "                  Ohio University                                   Contract   \n",
       "                  Ohio University                                   Contract   \n",
       "                  Ohio University                                   Contract   \n",
       "                  University of Illinois at Urbana-Champaign   Monetary Gift   \n",
       "                  University of Illinois at Urbana-Champaign        Contract   \n",
       "                  University of Illinois at Urbana-Champaign        Contract   \n",
       "Angola            University of Oklahoma                            Contract   \n",
       "Argentina         Columbia University in the City of New York       Contract   \n",
       "                  Harvard University                                Contract   \n",
       "                  Harvard University                                Contract   \n",
       "                  Harvard University                                Contract   \n",
       "\n",
       "                                                                                       Giftor Name  \\\n",
       "Country of Giftor Institution Name                                                                   \n",
       "Afghanistan       Ball State University                                      Ministry of Education   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Johns Hopkins University                                                     NaN   \n",
       "                  Ohio University                              Mn Pub Wrks Islamic Rep Afghanistan   \n",
       "                  Ohio University                              Mn Pub Wrks Islamic Rep Afghanistan   \n",
       "                  Ohio University                              Mn Pub Wrks Islamic Rep Afghanistan   \n",
       "                  University of Illinois at Urbana-Champaign     Council for the Develop of Social   \n",
       "                  University of Illinois at Urbana-Champaign              DSM Biotechnology Center   \n",
       "                  University of Illinois at Urbana-Champaign          Ministry of Health of Rwanda   \n",
       "Angola            University of Oklahoma                        Western African Management Service   \n",
       "Argentina         Columbia University in the City of New York     FUNDACIÓN RED DE ACCIÓN POLÍTICA   \n",
       "                  Harvard University                                                           NaN   \n",
       "                  Harvard University                                                           NaN   \n",
       "                  Harvard University                                                           NaN   \n",
       "\n",
       "                                                              Country to merge on  \\\n",
       "Country of Giftor Institution Name                                                  \n",
       "Afghanistan       Ball State University                               Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Johns Hopkins University                            Afghanistan   \n",
       "                  Ohio University                                     Afghanistan   \n",
       "                  Ohio University                                     Afghanistan   \n",
       "                  Ohio University                                     Afghanistan   \n",
       "                  University of Illinois at Urbana-Champaign          Afghanistan   \n",
       "                  University of Illinois at Urbana-Champaign          Afghanistan   \n",
       "                  University of Illinois at Urbana-Champaign          Afghanistan   \n",
       "Angola            University of Oklahoma                                   Angola   \n",
       "Argentina         Columbia University in the City of New York           Argentina   \n",
       "                  Harvard University                                    Argentina   \n",
       "                  Harvard University                                    Argentina   \n",
       "                  Harvard University                                    Argentina   \n",
       "\n",
       "                                                                Rank  Score  \\\n",
       "Country of Giftor Institution Name                                            \n",
       "Afghanistan       Ball State University                        141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Johns Hopkins University                     141.0   2.85   \n",
       "                  Ohio University                              141.0   2.85   \n",
       "                  Ohio University                              141.0   2.85   \n",
       "                  Ohio University                              141.0   2.85   \n",
       "                  University of Illinois at Urbana-Champaign   141.0   2.85   \n",
       "                  University of Illinois at Urbana-Champaign   141.0   2.85   \n",
       "                  University of Illinois at Urbana-Champaign   141.0   2.85   \n",
       "Angola            University of Oklahoma                       119.0   3.72   \n",
       "Argentina         Columbia University in the City of New York   48.0   7.02   \n",
       "                  Harvard University                            48.0   7.02   \n",
       "                  Harvard University                            48.0   7.02   \n",
       "                  Harvard University                            48.0   7.02   \n",
       "\n",
       "                                                               Electoral process and pluralism  \\\n",
       "Country of Giftor Institution Name                                                               \n",
       "Afghanistan       Ball State University                                                   3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Johns Hopkins University                                                3.42   \n",
       "                  Ohio University                                                         3.42   \n",
       "                  Ohio University                                                         3.42   \n",
       "                  Ohio University                                                         3.42   \n",
       "                  University of Illinois at Urbana-Champaign                              3.42   \n",
       "                  University of Illinois at Urbana-Champaign                              3.42   \n",
       "                  University of Illinois at Urbana-Champaign                              3.42   \n",
       "Angola            University of Oklahoma                                                  2.25   \n",
       "Argentina         Columbia University in the City of New York                             9.17   \n",
       "                  Harvard University                                                      9.17   \n",
       "                  Harvard University                                                      9.17   \n",
       "                  Harvard University                                                      9.17   \n",
       "\n",
       "                                                               Functioning of government  \\\n",
       "Country of Giftor Institution Name                                                         \n",
       "Afghanistan       Ball State University                                             0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Johns Hopkins University                                          0.64   \n",
       "                  Ohio University                                                   0.64   \n",
       "                  Ohio University                                                   0.64   \n",
       "                  Ohio University                                                   0.64   \n",
       "                  University of Illinois at Urbana-Champaign                        0.64   \n",
       "                  University of Illinois at Urbana-Champaign                        0.64   \n",
       "                  University of Illinois at Urbana-Champaign                        0.64   \n",
       "Angola            University of Oklahoma                                            2.86   \n",
       "Argentina         Columbia University in the City of New York                       5.36   \n",
       "                  Harvard University                                                5.36   \n",
       "                  Harvard University                                                5.36   \n",
       "                  Harvard University                                                5.36   \n",
       "\n",
       "                                                               Political participation  \\\n",
       "Country of Giftor Institution Name                                                       \n",
       "Afghanistan       Ball State University                                           3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Johns Hopkins University                                        3.89   \n",
       "                  Ohio University                                                 3.89   \n",
       "                  Ohio University                                                 3.89   \n",
       "                  Ohio University                                                 3.89   \n",
       "                  University of Illinois at Urbana-Champaign                      3.89   \n",
       "                  University of Illinois at Urbana-Champaign                      3.89   \n",
       "                  University of Illinois at Urbana-Champaign                      3.89   \n",
       "Angola            University of Oklahoma                                          5.56   \n",
       "Argentina         Columbia University in the City of New York                     6.11   \n",
       "                  Harvard University                                              6.11   \n",
       "                  Harvard University                                              6.11   \n",
       "                  Harvard University                                              6.11   \n",
       "\n",
       "                                                               Political culture  \\\n",
       "Country of Giftor Institution Name                                                 \n",
       "Afghanistan       Ball State University                                     2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Johns Hopkins University                                  2.50   \n",
       "                  Ohio University                                           2.50   \n",
       "                  Ohio University                                           2.50   \n",
       "                  Ohio University                                           2.50   \n",
       "                  University of Illinois at Urbana-Champaign                2.50   \n",
       "                  University of Illinois at Urbana-Champaign                2.50   \n",
       "                  University of Illinois at Urbana-Champaign                2.50   \n",
       "Angola            University of Oklahoma                                    5.00   \n",
       "Argentina         Columbia University in the City of New York               6.25   \n",
       "                  Harvard University                                        6.25   \n",
       "                  Harvard University                                        6.25   \n",
       "                  Harvard University                                        6.25   \n",
       "\n",
       "                                                               Civil liberties  \\\n",
       "Country of Giftor Institution Name                                               \n",
       "Afghanistan       Ball State University                                   3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Johns Hopkins University                                3.82   \n",
       "                  Ohio University                                         3.82   \n",
       "                  Ohio University                                         3.82   \n",
       "                  Ohio University                                         3.82   \n",
       "                  University of Illinois at Urbana-Champaign              3.82   \n",
       "                  University of Illinois at Urbana-Champaign              3.82   \n",
       "                  University of Illinois at Urbana-Champaign              3.82   \n",
       "Angola            University of Oklahoma                                  2.94   \n",
       "Argentina         Columbia University in the City of New York             8.24   \n",
       "                  Harvard University                                      8.24   \n",
       "                  Harvard University                                      8.24   \n",
       "                  Harvard University                                      8.24   \n",
       "\n",
       "                                                                    Regime type  \\\n",
       "Country of Giftor Institution Name                                                \n",
       "Afghanistan       Ball State University                           Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Johns Hopkins University                        Authoritarian   \n",
       "                  Ohio University                                 Authoritarian   \n",
       "                  Ohio University                                 Authoritarian   \n",
       "                  Ohio University                                 Authoritarian   \n",
       "                  University of Illinois at Urbana-Champaign      Authoritarian   \n",
       "                  University of Illinois at Urbana-Champaign      Authoritarian   \n",
       "                  University of Illinois at Urbana-Champaign      Authoritarian   \n",
       "Angola            University of Oklahoma                          Authoritarian   \n",
       "Argentina         Columbia University in the City of New York  Flawed democracy   \n",
       "                  Harvard University                           Flawed democracy   \n",
       "                  Harvard University                           Flawed democracy   \n",
       "                  Harvard University                           Flawed democracy   \n",
       "\n",
       "                                                                           Region  \n",
       "Country of Giftor Institution Name                                                 \n",
       "Afghanistan       Ball State University                        Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Johns Hopkins University                     Asia & Australasia  \n",
       "                  Ohio University                              Asia & Australasia  \n",
       "                  Ohio University                              Asia & Australasia  \n",
       "                  Ohio University                              Asia & Australasia  \n",
       "                  University of Illinois at Urbana-Champaign   Asia & Australasia  \n",
       "                  University of Illinois at Urbana-Champaign   Asia & Australasia  \n",
       "                  University of Illinois at Urbana-Champaign   Asia & Australasia  \n",
       "Angola            University of Oklahoma                       Sub-Saharan Africa  \n",
       "Argentina         Columbia University in the City of New York       Latin America  \n",
       "                  Harvard University                                Latin America  \n",
       "                  Harvard University                                Latin America  \n",
       "                  Harvard University                                Latin America  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add column to foreign donation dataset for merging with Democracy Index dataset\n",
    "\n",
    "donations_df.loc[:, 'Country to merge on'] = (     # Adding column\n",
    "    donations_df.loc[:, 'Country of Giftor']       # Copy 'Country of Giftor' column\n",
    "    .str\n",
    "    .title())                                      # But transfor to title case\n",
    "\n",
    "donations_df.replace(\n",
    "    to_replace={'Country to merge on': countries_dict},   # Replace country name with countries_dict dictionary\n",
    "    inplace=True)\n",
    "\n",
    "merged_df = pd.merge(\n",
    "    donations_df, \n",
    "    democracy_df, \n",
    "    how='left', \n",
    "    left_on='Country to merge on', \n",
    "    right_index=True)\n",
    "\n",
    "merged_df.loc[:, 'Country of Giftor'] = (                # Transforming 'Country of Giftor' column to title case\n",
    "    merged_df.loc[:, 'Country of Giftor']\n",
    "    .str\n",
    "    .title())\n",
    "\n",
    "merged_df = (                                            # Setting up multi-index\n",
    "    merged_df\n",
    "    .set_index(['Country of Giftor', 'Institution Name'])\n",
    "    .sort_index())\n",
    "\n",
    "merged_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_csv('donation_democracy_index.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifying schools receiving most donations from authoritarian (according to DI definition) countries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 schools based on number of donations recived from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Institution Name\n",
       "University of California, Los Angeles          412\n",
       "California Institute of Technology             346\n",
       "Texas A&M University                           162\n",
       "Stanford University                            159\n",
       "Arizona State University                       157\n",
       "Saint John's University                        155\n",
       "Johns Hopkins University                       117\n",
       "University of Michigan - Ann Arbor             106\n",
       "Columbia University in the City of New York     96\n",
       "Northwestern University                         89\n",
       "Name: ID, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authoritarian_df = (\n",
    "    merged_df.\n",
    "    loc[merged_df['Regime type']=='Authoritarian'])\n",
    "\n",
    "top10schools_by_counts = (\n",
    "    authoritarian_df.groupby('Institution Name')\n",
    "    .count()\n",
    "    .iloc[:, 0]\n",
    "    .sort_values(ascending=False)\n",
    "    .head(10))\n",
    "\n",
    "top10schools_by_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0e329f75ca2643ba93676cc3d0c8ebf1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10schools_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "ax.barh(top10schools_by_counts.index, top10schools_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations received')\n",
    "ax.set_title('Top 10 universities based on number of donations recieved from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(top10schools_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)\n",
    "\n",
    "\n",
    "# Note: this is donated contract + cash gifts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 schools based on total dollars of donations recived from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "top10schools_by_dollars = (\n",
    "    authoritarian_df.groupby('Institution Name')\n",
    "    .sum().loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .head(10))\n",
    "top10schools_by_dollars\n",
    "\n",
    "dill.dump(top10schools_by_dollars, open('./top_10_schools_auth.pkd', 'wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a14e1b8b1c134a7689365a5b83a42686",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10schools_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(10,5))                                 # Begin pyplot area\n",
    "ax.barh(top10schools_by_dollars.index, top10schools_by_dollars.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million Dollars')\n",
    "ax.set_title('Top 10 universities based on dollars of donations received from authoritarian countries')\n",
    "for i, v in enumerate(top10schools_by_dollars):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)\n",
    "plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 schools based on total dollars of cash donations recived from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Institution Name\n",
       "Massachusetts Institute of Technology          190198291\n",
       "Harvard University                              87979142\n",
       "Stanford University                             75421948\n",
       "New York University                             56297438\n",
       "Johns Hopkins University                        53533107\n",
       "Texas A&M University                            52364406\n",
       "University of Pennsylvania                      35101067\n",
       "Duke University                                 30564760\n",
       "Yale University                                 24353520\n",
       "Columbia University in the City of New York     21755763\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top10schools_by_cash = (\n",
    "    authoritarian_df.loc[authoritarian_df['Gift Type']=='Monetary Gift']\n",
    "    .groupby('Institution Name')\n",
    "    .sum()\n",
    "    .loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .head(10))\n",
    "top10schools_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-62-5dc0f80de9e2>:2: RuntimeWarning: More than 20 figures have been opened. Figures created through the pyplot interface (`matplotlib.pyplot.figure`) are retained until explicitly closed and may consume too much memory. (To control this warning, see the rcParam `figure.max_open_warning`).\n",
      "  fig, ax = plt.subplots(figsize=(5,5))                                   # Initializing pyplot area\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b9045e85a3a04efab76cf0121c48fb36",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10schools_by_cash.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                   # Initializing pyplot area\n",
    "ax.barh(top10schools_by_cash.index, top10schools_by_cash.values*(10**-6), color='purple')    # Generating bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Top 10 universities based on dollars of cash donations received from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(top10schools_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifying authoritarian countries making the most donations to US universities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 authoritarian countries based on the number of donations made to US universities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "China                   1597\n",
       "Saudi Arabia             719\n",
       "Qatar                    444\n",
       "United Arab Emirates     177\n",
       "Kuwait                    83\n",
       "Kazakhstan                54\n",
       "Russia                    46\n",
       "Vietnam                   43\n",
       "Jordan                    22\n",
       "Iraq                      18\n",
       "Name: ID, dtype: int64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top10countries_by_counts = (\n",
    "    authoritarian_df.groupby('Country of Giftor')\n",
    "    .count().iloc[:, 0]\n",
    "    .sort_values(ascending=False)\n",
    "    .head(10))\n",
    "\n",
    "top10countries_by_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-64-e2b07841c3f6>:2: RuntimeWarning: More than 20 figures have been opened. Figures created through the pyplot interface (`matplotlib.pyplot.figure`) are retained until explicitly closed and may consume too much memory. (To control this warning, see the rcParam `figure.max_open_warning`).\n",
      "  fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6e84240ca8cf417ca4cc62a428a36427",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10countries_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(top10countries_by_counts.index, top10countries_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations made')\n",
    "ax.set_title('Top 10 authoritarian countries based on number of donations made (2012-2018)')\n",
    "for i, v in enumerate(top10countries_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 authoritarian countries based on total donation dollar amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "Qatar                   1478676069\n",
       "China                    680273016\n",
       "Saudi Arabia             649971586\n",
       "United Arab Emirates     230945101\n",
       "Russia                    99461804\n",
       "Kuwait                    65782667\n",
       "Kazakhstan                62179114\n",
       "Iraq                      44276761\n",
       "Vietnam                   11799413\n",
       "Afghanistan                8891774\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top10countries_by_dollars = (\n",
    "    authoritarian_df.groupby('Country of Giftor')\n",
    "    .sum().loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .head(10))\n",
    "\n",
    "top10countries_by_dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bd8d3eb343934f7d96ab633d4868a79e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10countries_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(10,5))                                   # Initializing pyplot area\n",
    "ax.barh(top10countries_by_dollars.index, top10countries_by_dollars.values*(10**-9), color='purple')    # Generating bar chart\n",
    "ax.set_xlabel('Billion dollars')\n",
    "ax.set_title('Top 10 authoritarian countries based on total donation dollar amount (2012-2018)')\n",
    "for i, v in enumerate(top10countries_by_dollars):\n",
    "    ax.text(v*(10**-9), i,'\\$'+str('{:.2f}'.format(v*(10**-9))+'B'), color='black')\n",
    "plt.margins(x=0.15)\n",
    "plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 authoritarian countries based on total cash donation dollar amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "China                   323674776\n",
       "Saudi Arabia            194561169\n",
       "Russia                   95026310\n",
       "Qatar                    74621261\n",
       "United Arab Emirates     50152823\n",
       "Kuwait                   45888708\n",
       "Kazakhstan               36639149\n",
       "Afghanistan               3890364\n",
       "Burma                     2781683\n",
       "Egypt                     2720885\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top10countries_by_cash = (\n",
    "    authoritarian_df.loc[authoritarian_df['Gift Type']=='Monetary Gift']\n",
    "    .groupby('Country of Giftor')\n",
    "    .sum().loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .head(10))\n",
    "\n",
    "top10countries_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "afc7bfb8868e40bba42b9ef216cc2164",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "top10countries_by_cash.sort_values(inplace=True)\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                   # Initializing pyplot area\n",
    "ax.barh(top10countries_by_cash.index, top10countries_by_cash.values*(10**-6), color='purple')    # Generating bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Top 10 authoritarian countries based on total cash donationn made (2012-2018)')\n",
    "for i, v in enumerate(top10countries_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Some quick observations:\n",
    "\n",
    "* School receiving largest number of gifts from authoritarian countries: **University of California, Los Angeles** (UCLA)\n",
    "* School receiving largest dollar amount of total gifts from authoritarian countries: **Carnegie Mellon University** (CMU)\n",
    "* School receiving largest dollar amount of cash gifts from authoritarian countries: **Massachusetts Institute of Technology** (MIT)\n",
    "* Authoritarian country donating the largest number of gifts: **China**\n",
    "* Authoritarian country donating the largest dollar amount of total gifts: **Qatar**\n",
    "* Authoritarian country donating the largest dollar amount of cash gifts: **China** (**Saudi Arabia** is 2<sup>nd</sup>)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## University of California, Los Angeles (UCLA)\n",
    "\n",
    "UCLA recieved the most number of individual foreign donations (3,611) between 2012-2018. Out of those, 412 individual donations were from authoritarian countries (as defined by the Democracy Index), which made UCLA also the university recieving the most number of indivudual donations from authoritarian countries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Donation data from UCLA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>OPEID</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Foreign Gift Received Date</th>\n",
       "      <th>Foreign Gift Amount</th>\n",
       "      <th>Gift Type</th>\n",
       "      <th>Giftor Name</th>\n",
       "      <th>Country to merge on</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Score</th>\n",
       "      <th>Electoral process and pluralism</th>\n",
       "      <th>Functioning of government</th>\n",
       "      <th>Political participation</th>\n",
       "      <th>Political culture</th>\n",
       "      <th>Civil liberties</th>\n",
       "      <th>Regime type</th>\n",
       "      <th>Region</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Institution Name</th>\n",
       "      <th>Country of Giftor</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">University of California, Los Angeles</th>\n",
       "      <th>Australia</th>\n",
       "      <td>1293</td>\n",
       "      <td>131500</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>2012-02-15</td>\n",
       "      <td>1255460</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.09</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.93</td>\n",
       "      <td>7.78</td>\n",
       "      <td>8.75</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>5530</td>\n",
       "      <td>131500</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>313320</td>\n",
       "      <td>Contract</td>\n",
       "      <td>LOWY MEDICAL RESEARCH INSTITUTE</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.09</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.93</td>\n",
       "      <td>7.78</td>\n",
       "      <td>8.75</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>12779</td>\n",
       "      <td>131500</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>21896</td>\n",
       "      <td>Contract</td>\n",
       "      <td>UNIV OF NEW SOUTH WALES</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.09</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.93</td>\n",
       "      <td>7.78</td>\n",
       "      <td>8.75</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>13946</td>\n",
       "      <td>131500</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>10560</td>\n",
       "      <td>Contract</td>\n",
       "      <td>Global Kinetics Corporation</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.09</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.93</td>\n",
       "      <td>7.78</td>\n",
       "      <td>8.75</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>14200</td>\n",
       "      <td>131500</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>9588</td>\n",
       "      <td>Contract</td>\n",
       "      <td>LOWY MEDICAL RESEARCH</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.09</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.93</td>\n",
       "      <td>7.78</td>\n",
       "      <td>8.75</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Asia &amp; Australasia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            ID   OPEID  \\\n",
       "Institution Name                      Country of Giftor                  \n",
       "University of California, Los Angeles Australia           1293  131500   \n",
       "                                      Australia           5530  131500   \n",
       "                                      Australia          12779  131500   \n",
       "                                      Australia          13946  131500   \n",
       "                                      Australia          14200  131500   \n",
       "\n",
       "                                                                City State  \\\n",
       "Institution Name                      Country of Giftor                      \n",
       "University of California, Los Angeles Australia          Los Angeles    CA   \n",
       "                                      Australia          Los Angeles    CA   \n",
       "                                      Australia          Los Angeles    CA   \n",
       "                                      Australia          Los Angeles    CA   \n",
       "                                      Australia          Los Angeles    CA   \n",
       "\n",
       "                                                        Foreign Gift Received Date  \\\n",
       "Institution Name                      Country of Giftor                              \n",
       "University of California, Los Angeles Australia                         2012-02-15   \n",
       "                                      Australia                         2018-03-19   \n",
       "                                      Australia                         2016-07-01   \n",
       "                                      Australia                         2018-03-01   \n",
       "                                      Australia                         2017-07-01   \n",
       "\n",
       "                                                         Foreign Gift Amount  \\\n",
       "Institution Name                      Country of Giftor                        \n",
       "University of California, Los Angeles Australia                      1255460   \n",
       "                                      Australia                       313320   \n",
       "                                      Australia                        21896   \n",
       "                                      Australia                        10560   \n",
       "                                      Australia                         9588   \n",
       "\n",
       "                                                        Gift Type  \\\n",
       "Institution Name                      Country of Giftor             \n",
       "University of California, Los Angeles Australia          Contract   \n",
       "                                      Australia          Contract   \n",
       "                                      Australia          Contract   \n",
       "                                      Australia          Contract   \n",
       "                                      Australia          Contract   \n",
       "\n",
       "                                                                             Giftor Name  \\\n",
       "Institution Name                      Country of Giftor                                    \n",
       "University of California, Los Angeles Australia                                      NaN   \n",
       "                                      Australia          LOWY MEDICAL RESEARCH INSTITUTE   \n",
       "                                      Australia                  UNIV OF NEW SOUTH WALES   \n",
       "                                      Australia              Global Kinetics Corporation   \n",
       "                                      Australia                   LOWY MEDICAL RESEARCH    \n",
       "\n",
       "                                                        Country to merge on  \\\n",
       "Institution Name                      Country of Giftor                       \n",
       "University of California, Los Angeles Australia                   Australia   \n",
       "                                      Australia                   Australia   \n",
       "                                      Australia                   Australia   \n",
       "                                      Australia                   Australia   \n",
       "                                      Australia                   Australia   \n",
       "\n",
       "                                                         Rank  Score  \\\n",
       "Institution Name                      Country of Giftor                \n",
       "University of California, Los Angeles Australia           9.0   9.09   \n",
       "                                      Australia           9.0   9.09   \n",
       "                                      Australia           9.0   9.09   \n",
       "                                      Australia           9.0   9.09   \n",
       "                                      Australia           9.0   9.09   \n",
       "\n",
       "                                                         Electoral process and pluralism  \\\n",
       "Institution Name                      Country of Giftor                                    \n",
       "University of California, Los Angeles Australia                                     10.0   \n",
       "                                      Australia                                     10.0   \n",
       "                                      Australia                                     10.0   \n",
       "                                      Australia                                     10.0   \n",
       "                                      Australia                                     10.0   \n",
       "\n",
       "                                                         Functioning of government  \\\n",
       "Institution Name                      Country of Giftor                              \n",
       "University of California, Los Angeles Australia                               8.93   \n",
       "                                      Australia                               8.93   \n",
       "                                      Australia                               8.93   \n",
       "                                      Australia                               8.93   \n",
       "                                      Australia                               8.93   \n",
       "\n",
       "                                                         Political participation  \\\n",
       "Institution Name                      Country of Giftor                            \n",
       "University of California, Los Angeles Australia                             7.78   \n",
       "                                      Australia                             7.78   \n",
       "                                      Australia                             7.78   \n",
       "                                      Australia                             7.78   \n",
       "                                      Australia                             7.78   \n",
       "\n",
       "                                                         Political culture  \\\n",
       "Institution Name                      Country of Giftor                      \n",
       "University of California, Los Angeles Australia                       8.75   \n",
       "                                      Australia                       8.75   \n",
       "                                      Australia                       8.75   \n",
       "                                      Australia                       8.75   \n",
       "                                      Australia                       8.75   \n",
       "\n",
       "                                                         Civil liberties  \\\n",
       "Institution Name                      Country of Giftor                    \n",
       "University of California, Los Angeles Australia                     10.0   \n",
       "                                      Australia                     10.0   \n",
       "                                      Australia                     10.0   \n",
       "                                      Australia                     10.0   \n",
       "                                      Australia                     10.0   \n",
       "\n",
       "                                                            Regime type  \\\n",
       "Institution Name                      Country of Giftor                   \n",
       "University of California, Los Angeles Australia          Full democracy   \n",
       "                                      Australia          Full democracy   \n",
       "                                      Australia          Full democracy   \n",
       "                                      Australia          Full democracy   \n",
       "                                      Australia          Full democracy   \n",
       "\n",
       "                                                                     Region  \n",
       "Institution Name                      Country of Giftor                      \n",
       "University of California, Los Angeles Australia          Asia & Australasia  \n",
       "                                      Australia          Asia & Australasia  \n",
       "                                      Australia          Asia & Australasia  \n",
       "                                      Australia          Asia & Australasia  \n",
       "                                      Australia          Asia & Australasia  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UCLA_df = (\n",
    "    merged_df.query('`Institution Name` == \"University of California, Los Angeles\"')   # Using pd.DataFrame.query\n",
    "    .reset_index()                                                                     # Back quotes used to escape index\n",
    "    .set_index(['Institution Name', 'Country of Giftor']))                             # labels with space\n",
    "\n",
    "UCLA_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of foreign donations to UCLA grouped by countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "England                 892\n",
       "Japan                   535\n",
       "Canada                  495\n",
       "China                   394\n",
       "Hong Kong               290\n",
       "Switzerland             197\n",
       "Germany                 159\n",
       "Taiwan                  121\n",
       "France                  117\n",
       "Australia                83\n",
       "South Korea              73\n",
       "Spain                    61\n",
       "Israel                   60\n",
       "Korea                    28\n",
       "Singapore                19\n",
       "The Netherlands          17\n",
       "Saudi Arabia             13\n",
       "Italy                    10\n",
       "Norway                    7\n",
       "Ireland                   6\n",
       "South Africa              6\n",
       "Brazil                    5\n",
       "Greece                    4\n",
       "Malaysia                  4\n",
       "India                     3\n",
       "Denmark                   3\n",
       "United Arab Emirates      3\n",
       "Bahamas                   2\n",
       "Eritrea                   1\n",
       "Ecuador                   1\n",
       "Russia                    1\n",
       "Ceylon                    1\n",
       "Name: ID, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(UCLA_df.groupby('Country of Giftor')\n",
    " .count()\n",
    " .iloc[:,0]\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total dollars donated to UCLA by each country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "England                 51186060\n",
       "Japan                   23878716\n",
       "Hong Kong               16606114\n",
       "Switzerland             14286549\n",
       "China                   13784326\n",
       "Saudi Arabia            12032553\n",
       "Germany                 11816638\n",
       "France                   5884617\n",
       "Canada                   5529592\n",
       "Israel                   5334192\n",
       "Korea                    4147817\n",
       "Spain                    3532947\n",
       "Taiwan                   3470583\n",
       "Australia                2223854\n",
       "The Netherlands          2068299\n",
       "Ireland                  1887475\n",
       "South Africa             1824427\n",
       "South Korea              1715667\n",
       "Denmark                  1072162\n",
       "India                     990140\n",
       "Russia                    673000\n",
       "Greece                    539275\n",
       "United Arab Emirates      453438\n",
       "Singapore                 410505\n",
       "Italy                     371846\n",
       "Bahamas                   299983\n",
       "Norway                    289839\n",
       "Brazil                    276205\n",
       "Malaysia                  250625\n",
       "Ceylon                       100\n",
       "Ecuador                       21\n",
       "Eritrea                        3\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(UCLA_df.groupby('Country of Giftor')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total cash only donations made to UCLA by each country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "England                 27045805\n",
       "Hong Kong               11326545\n",
       "Japan                    9526275\n",
       "China                    5814723\n",
       "Spain                    3329965\n",
       "Taiwan                   3105107\n",
       "Switzerland              1693504\n",
       "Canada                   1368880\n",
       "South Korea              1104869\n",
       "The Netherlands           590691\n",
       "Greece                    539275\n",
       "France                    483368\n",
       "Singapore                 410505\n",
       "Korea                     369230\n",
       "Germany                   304694\n",
       "Norway                    289839\n",
       "Brazil                    276205\n",
       "Malaysia                  250625\n",
       "Saudi Arabia              210620\n",
       "Israel                    112939\n",
       "Bahamas                    99983\n",
       "Australia                  17785\n",
       "Italy                       4425\n",
       "Ireland                     1100\n",
       "United Arab Emirates        1021\n",
       "India                        140\n",
       "South Africa                 121\n",
       "Ceylon                       100\n",
       "Ecuador                       21\n",
       "Eritrea                        3\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(UCLA_df.loc[UCLA_df['Gift Type']=='Monetary Gift']\n",
    " .groupby('Country of Giftor')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of donations made to UCLA by authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "China                   394\n",
       "Saudi Arabia             13\n",
       "United Arab Emirates      3\n",
       "Russia                    1\n",
       "Eritrea                   1\n",
       "Name: ID, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UCLA_by_counts = (\n",
    "    UCLA_df.loc[UCLA_df['Regime type']=='Authoritarian']\n",
    "    .groupby('Country of Giftor')\n",
    "    .count()\n",
    "    .iloc[:,0].sort_values(ascending=False))\n",
    "\n",
    "UCLA_by_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7231c7f9dbd8402aba30cab91ba4fe1e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "UCLA_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(UCLA_by_counts.index, UCLA_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations made')\n",
    "ax.set_title('Number of donations made to UCLA by authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(UCLA_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total donation dollars received by UCLA from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "China                   13784326\n",
       "Saudi Arabia            12032553\n",
       "Russia                    673000\n",
       "United Arab Emirates      453438\n",
       "Eritrea                        3\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UCLA_by_dollars = (\n",
    "    UCLA_df.loc[UCLA_df['Regime type']=='Authoritarian']\n",
    "    .groupby('Country of Giftor')\n",
    "    .sum()\n",
    "    .loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False))\n",
    "\n",
    "UCLA_by_dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8efded5d33d744f7837f3388ed647078",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "UCLA_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(UCLA_by_dollars.index, UCLA_by_dollars.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total donation accepted by UCLA from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(UCLA_by_dollars):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total cash donation accepted by UCLA from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "China                   5814723\n",
       "Saudi Arabia             210620\n",
       "United Arab Emirates       1021\n",
       "Eritrea                       3\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UCLA_by_cash = (\n",
    "    UCLA_df.loc[(UCLA_df['Regime type']=='Authoritarian') & (UCLA_df['Gift Type']=='Monetary Gift')]\n",
    "    .groupby('Country of Giftor')\n",
    "    .sum().loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False))\n",
    "\n",
    "UCLA_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8e752597db1049c59d9f043f2df0da05",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "UCLA_by_cash.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(UCLA_by_cash.index, UCLA_by_cash*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total cash donation accepted by UCLA from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(UCLA_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Carnegie Mellon University (CMU)\n",
    "\n",
    "UCLA recieved the most donations in dollar amount from authoritarian countries in 2012-2018. These donations include contracts and cash. However, CMU was not within the top ten list of receiving the most individual donations from authoritarian countries. This implies that CMU accepted fewer donations from these countries, but each donations were of significantly higher dollar amount."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Donation data from CMU"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>OPEID</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Foreign Gift Received Date</th>\n",
       "      <th>Foreign Gift Amount</th>\n",
       "      <th>Gift Type</th>\n",
       "      <th>Giftor Name</th>\n",
       "      <th>Country to merge on</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Score</th>\n",
       "      <th>Electoral process and pluralism</th>\n",
       "      <th>Functioning of government</th>\n",
       "      <th>Political participation</th>\n",
       "      <th>Political culture</th>\n",
       "      <th>Civil liberties</th>\n",
       "      <th>Regime type</th>\n",
       "      <th>Region</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Institution Name</th>\n",
       "      <th>Country of Giftor</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Carnegie Mellon University</th>\n",
       "      <th>Bermuda</th>\n",
       "      <td>5717</td>\n",
       "      <td>324200</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>2016-02-16</td>\n",
       "      <td>75000000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8.52</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.89</td>\n",
       "      <td>7.5</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bermuda</th>\n",
       "      <td>5718</td>\n",
       "      <td>324200</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>2016-02-16</td>\n",
       "      <td>75000000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8.52</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.89</td>\n",
       "      <td>7.5</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bermuda</th>\n",
       "      <td>5719</td>\n",
       "      <td>324200</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>2016-02-16</td>\n",
       "      <td>75000000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8.52</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.89</td>\n",
       "      <td>7.5</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bermuda</th>\n",
       "      <td>5720</td>\n",
       "      <td>324200</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>2016-02-16</td>\n",
       "      <td>75000000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8.52</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.89</td>\n",
       "      <td>7.5</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bermuda</th>\n",
       "      <td>5721</td>\n",
       "      <td>324200</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>2016-02-16</td>\n",
       "      <td>75000000</td>\n",
       "      <td>Contract</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>14.0</td>\n",
       "      <td>8.52</td>\n",
       "      <td>9.58</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.89</td>\n",
       "      <td>7.5</td>\n",
       "      <td>9.12</td>\n",
       "      <td>Full democracy</td>\n",
       "      <td>Western Europe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                ID   OPEID        City State  \\\n",
       "Institution Name           Country of Giftor                                   \n",
       "Carnegie Mellon University Bermuda            5717  324200  Pittsburgh    PA   \n",
       "                           Bermuda            5718  324200  Pittsburgh    PA   \n",
       "                           Bermuda            5719  324200  Pittsburgh    PA   \n",
       "                           Bermuda            5720  324200  Pittsburgh    PA   \n",
       "                           Bermuda            5721  324200  Pittsburgh    PA   \n",
       "\n",
       "                                             Foreign Gift Received Date  \\\n",
       "Institution Name           Country of Giftor                              \n",
       "Carnegie Mellon University Bermuda                           2016-02-16   \n",
       "                           Bermuda                           2016-02-16   \n",
       "                           Bermuda                           2016-02-16   \n",
       "                           Bermuda                           2016-02-16   \n",
       "                           Bermuda                           2016-02-16   \n",
       "\n",
       "                                              Foreign Gift Amount Gift Type  \\\n",
       "Institution Name           Country of Giftor                                  \n",
       "Carnegie Mellon University Bermuda                       75000000  Contract   \n",
       "                           Bermuda                       75000000  Contract   \n",
       "                           Bermuda                       75000000  Contract   \n",
       "                           Bermuda                       75000000  Contract   \n",
       "                           Bermuda                       75000000  Contract   \n",
       "\n",
       "                                             Giftor Name Country to merge on  \\\n",
       "Institution Name           Country of Giftor                                   \n",
       "Carnegie Mellon University Bermuda                   NaN      United Kingdom   \n",
       "                           Bermuda                   NaN      United Kingdom   \n",
       "                           Bermuda                   NaN      United Kingdom   \n",
       "                           Bermuda                   NaN      United Kingdom   \n",
       "                           Bermuda                   NaN      United Kingdom   \n",
       "\n",
       "                                              Rank  Score  \\\n",
       "Institution Name           Country of Giftor                \n",
       "Carnegie Mellon University Bermuda            14.0   8.52   \n",
       "                           Bermuda            14.0   8.52   \n",
       "                           Bermuda            14.0   8.52   \n",
       "                           Bermuda            14.0   8.52   \n",
       "                           Bermuda            14.0   8.52   \n",
       "\n",
       "                                              Electoral process and pluralism  \\\n",
       "Institution Name           Country of Giftor                                    \n",
       "Carnegie Mellon University Bermuda                                       9.58   \n",
       "                           Bermuda                                       9.58   \n",
       "                           Bermuda                                       9.58   \n",
       "                           Bermuda                                       9.58   \n",
       "                           Bermuda                                       9.58   \n",
       "\n",
       "                                              Functioning of government  \\\n",
       "Institution Name           Country of Giftor                              \n",
       "Carnegie Mellon University Bermuda                                  7.5   \n",
       "                           Bermuda                                  7.5   \n",
       "                           Bermuda                                  7.5   \n",
       "                           Bermuda                                  7.5   \n",
       "                           Bermuda                                  7.5   \n",
       "\n",
       "                                              Political participation  \\\n",
       "Institution Name           Country of Giftor                            \n",
       "Carnegie Mellon University Bermuda                               8.89   \n",
       "                           Bermuda                               8.89   \n",
       "                           Bermuda                               8.89   \n",
       "                           Bermuda                               8.89   \n",
       "                           Bermuda                               8.89   \n",
       "\n",
       "                                              Political culture  \\\n",
       "Institution Name           Country of Giftor                      \n",
       "Carnegie Mellon University Bermuda                          7.5   \n",
       "                           Bermuda                          7.5   \n",
       "                           Bermuda                          7.5   \n",
       "                           Bermuda                          7.5   \n",
       "                           Bermuda                          7.5   \n",
       "\n",
       "                                              Civil liberties     Regime type  \\\n",
       "Institution Name           Country of Giftor                                    \n",
       "Carnegie Mellon University Bermuda                       9.12  Full democracy   \n",
       "                           Bermuda                       9.12  Full democracy   \n",
       "                           Bermuda                       9.12  Full democracy   \n",
       "                           Bermuda                       9.12  Full democracy   \n",
       "                           Bermuda                       9.12  Full democracy   \n",
       "\n",
       "                                                      Region  \n",
       "Institution Name           Country of Giftor                  \n",
       "Carnegie Mellon University Bermuda            Western Europe  \n",
       "                           Bermuda            Western Europe  \n",
       "                           Bermuda            Western Europe  \n",
       "                           Bermuda            Western Europe  \n",
       "                           Bermuda            Western Europe  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CMU_df = (\n",
    "    merged_df.query('`Institution Name` == \"Carnegie Mellon University\"')   # Using pd.DataFrame.query\n",
    "    .reset_index()                                                                     # Back quotes used to escape index\n",
    "    .set_index(['Institution Name', 'Country of Giftor']))                             # labels with space\n",
    "\n",
    "CMU_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of foreign donations to CMU grouped by countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "Japan                   25\n",
       "Qatar                   22\n",
       "South Korea             19\n",
       "India                   16\n",
       "Bermuda                 10\n",
       "England                  8\n",
       "Switzerland              7\n",
       "Taiwan                   6\n",
       "China                    6\n",
       "The Netherlands          4\n",
       "Portugal                 4\n",
       "Russia                   4\n",
       "France                   3\n",
       "Colombia                 3\n",
       "South Africa             3\n",
       "Canada                   3\n",
       "Hong Kong                3\n",
       "Chile                    2\n",
       "Singapore                2\n",
       "Germany                  2\n",
       "Czech Republic           1\n",
       "British West Indies      1\n",
       "United Arab Emirates     1\n",
       "Italy                    1\n",
       "Korea                    1\n",
       "Norway                   1\n",
       "Rwanda                   1\n",
       "Saudi Arabia             1\n",
       "Sweden                   1\n",
       "Thailand                 1\n",
       "Mexico                   1\n",
       "Name: ID, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(CMU_df.groupby('Country of Giftor')\n",
    " .count()\n",
    " .iloc[:,0]\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total dollars donated to CMU by each country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "Bermuda                 750000000\n",
       "Qatar                   376325840\n",
       "Thailand                 67700000\n",
       "Portugal                 37492100\n",
       "India                    29399473\n",
       "Japan                    21851301\n",
       "Canada                   11937151\n",
       "Hong Kong                11250000\n",
       "South Korea               9822307\n",
       "Taiwan                    8150000\n",
       "Switzerland               8093900\n",
       "South Africa              7940779\n",
       "United Arab Emirates      5000000\n",
       "England                   4290688\n",
       "Singapore                 4165000\n",
       "China                     4146500\n",
       "Colombia                  3792101\n",
       "Russia                    2860500\n",
       "Germany                   2795576\n",
       "Chile                     2583435\n",
       "The Netherlands           2383856\n",
       "France                    1088287\n",
       "Saudi Arabia              1050000\n",
       "Korea                      964000\n",
       "Italy                      720000\n",
       "Sweden                     617566\n",
       "Norway                     383990\n",
       "Czech Republic             317520\n",
       "Mexico                     307603\n",
       "British West Indies        295000\n",
       "Rwanda                     279334\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(CMU_df.groupby('Country of Giftor')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total cash only donations made to CMU by each country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "India             16650000\n",
       "Canada            11687151\n",
       "Qatar              5456800\n",
       "Switzerland        4800000\n",
       "Japan              3637243\n",
       "China              2500000\n",
       "England            2029602\n",
       "Hong Kong          1250000\n",
       "Czech Republic      317520\n",
       "Rwanda              279334\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(CMU_df.loc[CMU_df['Gift Type']=='Monetary Gift']\n",
    " .groupby('Country of Giftor')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(t_stat, p_value, f'Different={p_value<0.01}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'CMU_by_countsy_counts' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-41-693247f0072a>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      5\u001b[0m     .iloc[:,0].sort_values(ascending=False))\n\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 7\u001b[1;33m \u001b[0mCMU_by_countsy_counts\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'CMU_by_countsy_counts' is not defined"
     ]
    }
   ],
   "source": [
    "CMU_by_counts = (\n",
    "    CMU_df.loc[CMU_df['Regime type']=='Authoritarian']\n",
    "    .groupby('Country of Giftor')\n",
    "    .count()\n",
    "    .iloc[:,0].sort_values(ascending=False))\n",
    "\n",
    "CMU_by_countsy_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CMU_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(CMU_by_counts.index, CMU_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations made')\n",
    "ax.set_title('Number of donations made to CMU by authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(CMU_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total donation dollars received by CMU from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country of Giftor\n",
       "Qatar                   376325840\n",
       "United Arab Emirates      5000000\n",
       "China                     4146500\n",
       "Russia                    2860500\n",
       "Saudi Arabia              1050000\n",
       "Rwanda                     279334\n",
       "Name: Foreign Gift Amount, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CMU_by_dollars = (\n",
    "    CMU_df.loc[CMU_df['Regime type']=='Authoritarian']\n",
    "    .groupby('Country of Giftor')\n",
    "    .sum().loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False))\n",
    "\n",
    "CMU_by_dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2ab548fc8969411ea5d6c7cd2a30bd02",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Canvas(toolbar=Toolbar(toolitems=[('Home', 'Reset original view', 'home', 'home'), ('Back', 'Back to previous …"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "CMU_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(10, 3))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(CMU_by_dollars.index, CMU_by_dollars.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total donation accepted by CMU from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(CMU_by_dollars):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)\n",
    "plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total cash donation accepted by CMU from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CMU_by_cash = (\n",
    "    CMU_df.loc[(CMU_df['Regime type']=='Authoritarian') & (CMU_df['Gift Type']=='Monetary Gift')]\n",
    "    .groupby('Country of Giftor')\n",
    "    .sum().loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False))\n",
    "\n",
    "CMU_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CMU_by_cash.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(CMU_by_cash.index, CMU_by_cash.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total cash donation accepted by CMU from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(CMU_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Massachusetts Institute of Technology (MIT)\n",
    "\n",
    "If only cash donations were being measured, then MIT received the most cash donations from authoritarian countries. Without a full analysis we can't be sure, but we can see from CMU that often foreign countries would make a massive donation in the form of a contract. These type of donations generally were much higher in dolalr value than purely cash donations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of foreign donations to MIT grouped by countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(MIT_df.groupby('Country of Giftor')\n",
    " .count()\n",
    " .iloc[:,0]\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total dollars donated to MIT by each country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(MIT_df.groupby('Country of Giftor')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total cash donated to MIT by each country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(MIT_df.loc[MIT_df['Gift Type']=='Monetary Gift']\n",
    " .groupby('Country of Giftor')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of donations made to MIT by authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MIT_by_counts = (MIT_df.loc[MIT_df['Regime type']=='Authoritarian']\n",
    " .groupby('Country of Giftor')\n",
    " .count()\n",
    " .iloc[:,0].sort_values(ascending=False))\n",
    "\n",
    "MIT_by_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MIT_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(MIT_by_counts.index, MIT_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations made')\n",
    "ax.set_title('Number of donations made to MIT by authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(MIT_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total donation dollars received by MIT from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MIT_by_dollars = (MIT_df.loc[MIT_df['Regime type']=='Authoritarian']\n",
    " .groupby('Country of Giftor')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))\n",
    "\n",
    "MIT_by_dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MIT_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(MIT_by_dollars.index, MIT_by_dollars.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total donation accepted by MIT from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(MIT_by_dollars):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total cash donation accepted by MIT from authoritarian countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MIT_by_cash = (MIT_df.loc[(MIT_df['Regime type']=='Authoritarian') & (MIT_df['Gift Type']=='Monetary Gift')]\n",
    " .groupby('Country of Giftor')\n",
    " .sum().loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False))\n",
    "\n",
    "MIT_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MIT_by_cash.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(MIT_by_cash.index, MIT_by_cash.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total cash donation accepted by MIT from authoritarian countries (2012-2018)')\n",
    "for i, v in enumerate(MIT_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### People's Republic of China\n",
    "\n",
    "China was ranked as the authoritarian country that made the most number of individual donations and also the hightest amount of total cash donations between 2012 to 2018."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of donations made by China to US universities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "china_df = authoritarian_df.loc['China'].copy()\n",
    "china_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites China donated to the most number of times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "china_by_counts = (china_df.groupby('Institution Name')\n",
    " .count()\n",
    " .iloc[:,0]\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "china_by_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "china_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(china_by_counts.index, china_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations made')\n",
    "ax.set_title('Number of donations made by China to US universities (2012-2018)')\n",
    "for i, v in enumerate(china_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites China donated most amount of total dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "china_by_dollars = (china_df.groupby('Institution Name')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "china_by_dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "china_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(china_by_dollars.index, china_by_dollars.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total donation donated by China to US universities (2012-2018)')\n",
    "for i, v in enumerate(china_by_dollars):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites China donated most amount of total cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "china_by_cash = (china_df.loc[china_df['Gift Type']=='Monetary Gift']\n",
    " .groupby('Institution Name')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "china_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "china_by_cash.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(china_by_cash.index, china_by_cash.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total cash donation donated by China to US universities (2012-2018)')\n",
    "for i, v in enumerate(china_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### State of Qatar\n",
    "\n",
    "Qatar was ranked as the authoritarian country that made the highest total dollar amount of donations to US universities between 2012 to 2018. This was primarily due to the large contract-based donations Qatar made, as China was the authoritarian country that made the highest cash donations, followed by Saudi Arabia."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of donations made by Qatar to US universities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qatar_df = authoritarian_df.loc['Qatar'].copy()\n",
    "qatar_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites Qatar donated to the most number of times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qatar_by_counts = (qatar_df.groupby('Institution Name')\n",
    " .count()\n",
    " .iloc[:,0]\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "qatar_by_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qatar_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(qatar_by_counts.index, qatar_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations made')\n",
    "ax.set_title('Number of donations made by Qatar to US universities (2012-2018)')\n",
    "for i, v in enumerate(qatar_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites Qatar donated most amount of total dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qatar_by_dollars = (qatar_df.groupby('Institution Name')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "qatar_by_dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qatar_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(qatar_by_dollars.index, qatar_by_dollars.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total donation donated by Qatar to US universities (2012-2018)')\n",
    "for i, v in enumerate(qatar_by_dollars):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites Qatar donated most amount of total cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qatar_by_cash = (qatar_df.loc[qatar_df['Gift Type']=='Monetary Gift']\n",
    " .groupby('Institution Name')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "qatar_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qatar_by_cash.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(qatar_by_cash.index, qatar_by_cash.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total cash donation donated by Qatar to US universities (2012-2018)')\n",
    "for i, v in enumerate(qatar_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Kingdom of Saudi Arabia\n",
    "\n",
    "Saudi Arabia only ranked as the 2nd authoritarian country that donated the most amount of cash to US university, as China was ranked the 1st. We have shown analyses of China above, below is of Saudi Arabia."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List of donations made by Saudi Arabia to US universities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saudi_df = authoritarian_df.loc['Saudi Arabia'].copy()\n",
    "saudi_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites Saudi Arabia donated to the most number of times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saudi_by_counts = (saudi_df.groupby('Institution Name')\n",
    " .count()\n",
    " .iloc[:,0]\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "saudi_by_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saudi_by_counts.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(saudi_by_counts.index, saudi_by_counts.values, color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Number of donations made')\n",
    "ax.set_title('Number of donations made by Saudi Arabia to US universities (2012-2018)')\n",
    "for i, v in enumerate(saudi_by_counts.values):           # Add numeric labels to bars for clarity\n",
    "     ax.text(v, i, str(v), color='black')\n",
    "plt.margins(x=0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites Saudi Arabia donated most amount of total dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saudi_by_dollars = (saudi_df.groupby('Institution Name')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "saudi_by_dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saudi_by_dollars.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(saudi_by_dollars.index, saudi_by_dollars.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total donation donated by Saudi Arabia to US universities (2012-2018)')\n",
    "for i, v in enumerate(saudi_by_dollars):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The 10 US universitites Saudi Arabia donated most amount of total cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saudi_by_cash = (saudi_df.loc[saudi_df['Gift Type']=='Monetary Gift']\n",
    " .groupby('Institution Name')\n",
    " .sum()\n",
    " .loc[:, 'Foreign Gift Amount']\n",
    " .sort_values(ascending=False)\n",
    " .head(10))\n",
    "\n",
    "saudi_by_cash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saudi_by_cash.sort_values(inplace=True)\n",
    "fig, ax = plt.subplots(figsize=(5,5))                                 # Begin pyplot area\n",
    "\n",
    "ax.barh(saudi_by_cash.index, saudi_by_cash.values*(10**-6), color='purple')    # Generate basic bar chart\n",
    "ax.set_xlabel('Million dollars')\n",
    "ax.set_title('Total cash donation donated by Saudi Arabia to US universities (2012-2018)')\n",
    "for i, v in enumerate(saudi_by_cash):\n",
    "    ax.text(v*(10**-6), i,'\\$'+str('{:.2f}'.format(v*(10**-6))+'M'), color='black')\n",
    "plt.margins(x=0.25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Summary foreign donation information\n",
    "\n",
    "Below are the summary foreign donation information of the top 50 US universities sorted by number of individual foreign donations recieved"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "most_number = (\n",
    "    merged_df.groupby('Institution Name')\n",
    "    .count()\n",
    "    .iloc[:, 0]\n",
    "    .sort_values(ascending=False)\n",
    "    .copy())\n",
    "\n",
    "most_dollars = (\n",
    "    merged_df.groupby('Institution Name')\n",
    "    .sum()\n",
    "    .loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .copy())\n",
    "\n",
    "most_cash = (\n",
    "    merged_df.loc[merged_df['Gift Type']=='Monetary Gift']\n",
    "    .groupby('Institution Name')\n",
    "    .sum()\n",
    "    .loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .copy())\n",
    "\n",
    "simplified_schools = pd.DataFrame(most_number).rename({'ID': 'Number of donations received'}, axis=1)\n",
    "simplified_schools.loc[:, 'Total dollars received (including contracts)'] = most_dollars\n",
    "simplified_schools.loc[:, 'Total cash received'] = most_cash\n",
    "\n",
    "simplified_schools.head(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below are the summary foreign donation information of the top 50 countries sorted by number of individual donations made"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "most_number_c = (\n",
    "    merged_df.groupby('Country of Giftor')\n",
    "    .count()\n",
    "    .iloc[:, 0]\n",
    "    .sort_values(ascending=False)\n",
    "    .copy())\n",
    "\n",
    "most_dollars_c = (\n",
    "    merged_df.groupby('Country of Giftor')\n",
    "    .sum()\n",
    "    .loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .copy())\n",
    "\n",
    "most_cash_c = (\n",
    "    merged_df.loc[merged_df['Gift Type']=='Monetary Gift']\n",
    "    .groupby('Country of Giftor')\n",
    "    .sum()\n",
    "    .loc[:, 'Foreign Gift Amount']\n",
    "    .sort_values(ascending=False)\n",
    "    .copy())\n",
    "\n",
    "simplified_countries = pd.DataFrame(most_number_c).rename({'ID': 'Number of donations received'}, axis=1)\n",
    "simplified_countries.loc[:, 'Total dollars received (including contracts)'] = most_dollars_c\n",
    "simplified_countries.loc[:, 'Total cash received'] = most_cash_c\n",
    "simplified_countries.columns = ['Number of donations given', 'Total dollars given', 'Total cash given']\n",
    "\n",
    "simplified_countries.head(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Statistical analyses\n",
    "\n",
    "Here we consider the correlation (via Pearson's correlation coefficient) between the number of donation recieved, total dollars received, and total cash received by each school."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "simplified_schools.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(5,5))                                  \n",
    "ax.scatter(\n",
    "    simplified_schools.loc[:, 'Number of donations received'], \n",
    "    simplified_schools.loc[:, 'Total dollars received (including contracts)']*(10**-9),\n",
    "    color='purple',\n",
    ")\n",
    "ax.set_xlabel('Number of donations')\n",
    "ax.set_ylabel('Billion Dollars')\n",
    "ax.set_title('Correlation graph between number of donations received and total dollars received by each US university')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the Pearson's correlation coefficient and visual observation of the correlation graph, there is little correlation between the number of donations received by a university and the total dollar amount it received."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we consider the correlation (via Pearson's correlation coefficient) between the number of donation made, total dollars donated, and total cash donated by each country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "simplified_countries.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(5,5))                                  \n",
    "ax.scatter(\n",
    "    simplified_countries.loc[:, 'Number of donations given'], \n",
    "    simplified_countries.loc[:, 'Total dollars given']*(10**-9),\n",
    "    color='purple')\n",
    "ax.set_xlabel('Number of donations')\n",
    "ax.set_ylabel('Billion Dollars')\n",
    "ax.set_title('Correlation graph between number of donations given and total dollars given by each country')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The correlation between number of donations given by each countries and the total dollar amount (including contracts) it gave is relatively strong, according to Pearson's correlation coefficient and visual observation of the correlation graph."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}