merge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Aggregation & Integration\n",
    "\n",
    "This notebook calculates the mean and median number of power outages in the 2016 Eagle-I dataset and merges the aggregated results with the demographic data in the 2010 Census dataset (CtyAvDemog2010.csv).\n",
    "\n",
    "For more information about merging dataframes, refer to:\n",
    "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"CtyAvDemog2010.csv\",delimiter=',', skiprows=0, low_memory=False, encoding='latin-1')\n",
    "# To fix error about invalid continuation byte, fix encoding: https://stackoverflow.com/questions/5552555/unicodedecodeerror-invalid-continuation-byte"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>OBJECTID</th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "      <th>Avg_PercentBlackorAfricanAmericanalone</th>\n",
       "      <th>Avg_PercentAmericanIndian_AlaskaNative</th>\n",
       "      <th>Avg_PercentAsian</th>\n",
       "      <th>...</th>\n",
       "      <th>Avg_UnemploymentPct</th>\n",
       "      <th>Avg_PercentOfIndividualsBelow200PctFederalPovertyLinePctile</th>\n",
       "      <th>Avg_PercentOfIndividualsBelow200PctFederalPovertyLine</th>\n",
       "      <th>Avg_PercentOfIndividualsLt10oPctFederalPovertyLinePctile</th>\n",
       "      <th>Avg_PercentOfIndividualsLt100PctFederalPovertyLine</th>\n",
       "      <th>Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegreePct</th>\n",
       "      <th>Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegree</th>\n",
       "      <th>Avg_PercentOfResidentsWhoAreNotCurrentlyEnrolledInHigherEd</th>\n",
       "      <th>Avg_UnemploymentPctIn2009IslandAnd2010StatesAndPR</th>\n",
       "      <th>Avg_PercentageHouseholdsBelow100PctOfFederalPovertyLineIn2009Isl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>39</td>\n",
       "      <td>31039</td>\n",
       "      <td>Cuming</td>\n",
       "      <td>41.915865</td>\n",
       "      <td>-96.788517</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>43.666667</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>6.666667</td>\n",
       "      <td>49.000000</td>\n",
       "      <td>10.333333</td>\n",
       "      <td>95.333333</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>11.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "      <td>69</td>\n",
       "      <td>53069</td>\n",
       "      <td>Wahkiakum</td>\n",
       "      <td>46.294638</td>\n",
       "      <td>-123.424458</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>...</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>35</td>\n",
       "      <td>11</td>\n",
       "      <td>35011</td>\n",
       "      <td>De Baca</td>\n",
       "      <td>34.359273</td>\n",
       "      <td>-104.368696</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>21.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>109</td>\n",
       "      <td>31109</td>\n",
       "      <td>Lancaster</td>\n",
       "      <td>40.783547</td>\n",
       "      <td>-96.688658</td>\n",
       "      <td>0.040811</td>\n",
       "      <td>0.005000</td>\n",
       "      <td>0.040541</td>\n",
       "      <td>...</td>\n",
       "      <td>2.816901</td>\n",
       "      <td>45.732394</td>\n",
       "      <td>31.112676</td>\n",
       "      <td>46.774648</td>\n",
       "      <td>13.929577</td>\n",
       "      <td>30.729730</td>\n",
       "      <td>6.662162</td>\n",
       "      <td>84.608108</td>\n",
       "      <td>4.041667</td>\n",
       "      <td>15.876712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>129</td>\n",
       "      <td>31129</td>\n",
       "      <td>Nuckolls</td>\n",
       "      <td>40.176492</td>\n",
       "      <td>-98.046842</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>35.500000</td>\n",
       "      <td>45.500000</td>\n",
       "      <td>10.500000</td>\n",
       "      <td>39.500000</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>96.500000</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3228</th>\n",
       "      <td>3229</td>\n",
       "      <td>13</td>\n",
       "      <td>123</td>\n",
       "      <td>13123</td>\n",
       "      <td>Gilmer</td>\n",
       "      <td>34.690506</td>\n",
       "      <td>-84.454624</td>\n",
       "      <td>0.004000</td>\n",
       "      <td>0.008000</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>61.400000</td>\n",
       "      <td>36.600000</td>\n",
       "      <td>62.200000</td>\n",
       "      <td>15.800000</td>\n",
       "      <td>73.400000</td>\n",
       "      <td>17.200000</td>\n",
       "      <td>96.400000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>16.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3229</th>\n",
       "      <td>3230</td>\n",
       "      <td>27</td>\n",
       "      <td>135</td>\n",
       "      <td>27135</td>\n",
       "      <td>Roseau</td>\n",
       "      <td>48.761068</td>\n",
       "      <td>-95.821504</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.018000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.200000</td>\n",
       "      <td>38.600000</td>\n",
       "      <td>24.600000</td>\n",
       "      <td>34.200000</td>\n",
       "      <td>7.800000</td>\n",
       "      <td>34.400000</td>\n",
       "      <td>6.400000</td>\n",
       "      <td>96.400000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>9.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3230</th>\n",
       "      <td>3231</td>\n",
       "      <td>28</td>\n",
       "      <td>89</td>\n",
       "      <td>28089</td>\n",
       "      <td>Madison</td>\n",
       "      <td>32.634370</td>\n",
       "      <td>-90.034160</td>\n",
       "      <td>0.394762</td>\n",
       "      <td>0.000476</td>\n",
       "      <td>0.026667</td>\n",
       "      <td>...</td>\n",
       "      <td>4.857143</td>\n",
       "      <td>38.714286</td>\n",
       "      <td>26.476190</td>\n",
       "      <td>34.666667</td>\n",
       "      <td>9.761905</td>\n",
       "      <td>38.428571</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>91.238095</td>\n",
       "      <td>4.619048</td>\n",
       "      <td>13.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3231</th>\n",
       "      <td>3232</td>\n",
       "      <td>48</td>\n",
       "      <td>227</td>\n",
       "      <td>48227</td>\n",
       "      <td>Howard</td>\n",
       "      <td>32.303471</td>\n",
       "      <td>-101.438772</td>\n",
       "      <td>0.045000</td>\n",
       "      <td>0.008000</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>...</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>59.800000</td>\n",
       "      <td>37.700000</td>\n",
       "      <td>59.600000</td>\n",
       "      <td>18.100000</td>\n",
       "      <td>73.600000</td>\n",
       "      <td>19.100000</td>\n",
       "      <td>95.400000</td>\n",
       "      <td>5.700000</td>\n",
       "      <td>17.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3232</th>\n",
       "      <td>3233</td>\n",
       "      <td>54</td>\n",
       "      <td>99</td>\n",
       "      <td>54099</td>\n",
       "      <td>Wayne</td>\n",
       "      <td>38.143642</td>\n",
       "      <td>-82.422666</td>\n",
       "      <td>0.009091</td>\n",
       "      <td>0.000909</td>\n",
       "      <td>0.003636</td>\n",
       "      <td>...</td>\n",
       "      <td>5.909091</td>\n",
       "      <td>73.545455</td>\n",
       "      <td>46.454545</td>\n",
       "      <td>78.545455</td>\n",
       "      <td>23.636364</td>\n",
       "      <td>73.636364</td>\n",
       "      <td>19.909091</td>\n",
       "      <td>94.181818</td>\n",
       "      <td>4.818182</td>\n",
       "      <td>19.636364</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3233 rows × 87 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      OBJECTID  STATEFP  COUNTYFP  GEOID       NAME        Lat         Lon  \\\n",
       "0            1       31        39  31039     Cuming  41.915865  -96.788517   \n",
       "1            2       53        69  53069  Wahkiakum  46.294638 -123.424458   \n",
       "2            3       35        11  35011    De Baca  34.359273 -104.368696   \n",
       "3            4       31       109  31109  Lancaster  40.783547  -96.688658   \n",
       "4            5       31       129  31129   Nuckolls  40.176492  -98.046842   \n",
       "...        ...      ...       ...    ...        ...        ...         ...   \n",
       "3228      3229       13       123  13123     Gilmer  34.690506  -84.454624   \n",
       "3229      3230       27       135  27135     Roseau  48.761068  -95.821504   \n",
       "3230      3231       28        89  28089    Madison  32.634370  -90.034160   \n",
       "3231      3232       48       227  48227     Howard  32.303471 -101.438772   \n",
       "3232      3233       54        99  54099      Wayne  38.143642  -82.422666   \n",
       "\n",
       "      Avg_PercentBlackorAfricanAmericanalone  \\\n",
       "0                                   0.000000   \n",
       "1                                   0.010000   \n",
       "2                                   0.010000   \n",
       "3                                   0.040811   \n",
       "4                                   0.000000   \n",
       "...                                      ...   \n",
       "3228                                0.004000   \n",
       "3229                                0.010000   \n",
       "3230                                0.394762   \n",
       "3231                                0.045000   \n",
       "3232                                0.009091   \n",
       "\n",
       "      Avg_PercentAmericanIndian_AlaskaNative  Avg_PercentAsian  ...  \\\n",
       "0                                   0.000000          0.000000  ...   \n",
       "1                                   0.000000          0.010000  ...   \n",
       "2                                   0.000000          0.000000  ...   \n",
       "3                                   0.005000          0.040541  ...   \n",
       "4                                   0.000000          0.015000  ...   \n",
       "...                                      ...               ...  ...   \n",
       "3228                                0.008000          0.002000  ...   \n",
       "3229                                0.010000          0.018000  ...   \n",
       "3230                                0.000476          0.026667  ...   \n",
       "3231                                0.008000          0.002000  ...   \n",
       "3232                                0.000909          0.003636  ...   \n",
       "\n",
       "      Avg_UnemploymentPct  \\\n",
       "0                2.333333   \n",
       "1                7.000000   \n",
       "2               10.000000   \n",
       "3                2.816901   \n",
       "4                1.000000   \n",
       "...                   ...   \n",
       "3228             3.800000   \n",
       "3229             2.200000   \n",
       "3230             4.857143   \n",
       "3231             6.500000   \n",
       "3232             5.909091   \n",
       "\n",
       "      Avg_PercentOfIndividualsBelow200PctFederalPovertyLinePctile  \\\n",
       "0                                             43.666667             \n",
       "1                                             39.000000             \n",
       "2                                             81.000000             \n",
       "3                                             45.732394             \n",
       "4                                             60.000000             \n",
       "...                                                 ...             \n",
       "3228                                          61.400000             \n",
       "3229                                          38.600000             \n",
       "3230                                          38.714286             \n",
       "3231                                          59.800000             \n",
       "3232                                          73.545455             \n",
       "\n",
       "      Avg_PercentOfIndividualsBelow200PctFederalPovertyLine  \\\n",
       "0                                             27.000000       \n",
       "1                                             25.000000       \n",
       "2                                             50.000000       \n",
       "3                                             31.112676       \n",
       "4                                             35.500000       \n",
       "...                                                 ...       \n",
       "3228                                          36.600000       \n",
       "3229                                          24.600000       \n",
       "3230                                          26.476190       \n",
       "3231                                          37.700000       \n",
       "3232                                          46.454545       \n",
       "\n",
       "      Avg_PercentOfIndividualsLt10oPctFederalPovertyLinePctile  \\\n",
       "0                                             29.000000          \n",
       "1                                             30.000000          \n",
       "2                                             64.000000          \n",
       "3                                             46.774648          \n",
       "4                                             45.500000          \n",
       "...                                                 ...          \n",
       "3228                                          62.200000          \n",
       "3229                                          34.200000          \n",
       "3230                                          34.666667          \n",
       "3231                                          59.600000          \n",
       "3232                                          78.545455          \n",
       "\n",
       "      Avg_PercentOfIndividualsLt100PctFederalPovertyLine  \\\n",
       "0                                              6.666667    \n",
       "1                                              7.000000    \n",
       "2                                             16.000000    \n",
       "3                                             13.929577    \n",
       "4                                             10.500000    \n",
       "...                                                 ...    \n",
       "3228                                          15.800000    \n",
       "3229                                           7.800000    \n",
       "3230                                           9.761905    \n",
       "3231                                          18.100000    \n",
       "3232                                          23.636364    \n",
       "\n",
       "      Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegreePct  \\\n",
       "0                                             49.000000                  \n",
       "1                                             40.000000                  \n",
       "2                                             70.000000                  \n",
       "3                                             30.729730                  \n",
       "4                                             39.500000                  \n",
       "...                                                 ...                  \n",
       "3228                                          73.400000                  \n",
       "3229                                          34.400000                  \n",
       "3230                                          38.428571                  \n",
       "3231                                          73.600000                  \n",
       "3232                                          73.636364                  \n",
       "\n",
       "      Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegree  \\\n",
       "0                                             10.333333               \n",
       "1                                              7.000000               \n",
       "2                                             15.000000               \n",
       "3                                              6.662162               \n",
       "4                                              7.500000               \n",
       "...                                                 ...               \n",
       "3228                                          17.200000               \n",
       "3229                                           6.400000               \n",
       "3230                                           9.000000               \n",
       "3231                                          19.100000               \n",
       "3232                                          19.909091               \n",
       "\n",
       "      Avg_PercentOfResidentsWhoAreNotCurrentlyEnrolledInHigherEd  \\\n",
       "0                                             95.333333            \n",
       "1                                             96.000000            \n",
       "2                                             96.000000            \n",
       "3                                             84.608108            \n",
       "4                                             96.500000            \n",
       "...                                                 ...            \n",
       "3228                                          96.400000            \n",
       "3229                                          96.400000            \n",
       "3230                                          91.238095            \n",
       "3231                                          95.400000            \n",
       "3232                                          94.181818            \n",
       "\n",
       "      Avg_UnemploymentPctIn2009IslandAnd2010StatesAndPR  \\\n",
       "0                                              1.333333   \n",
       "1                                              7.000000   \n",
       "2                                              0.000000   \n",
       "3                                              4.041667   \n",
       "4                                              2.500000   \n",
       "...                                                 ...   \n",
       "3228                                           6.000000   \n",
       "3229                                           3.000000   \n",
       "3230                                           4.619048   \n",
       "3231                                           5.700000   \n",
       "3232                                           4.818182   \n",
       "\n",
       "      Avg_PercentageHouseholdsBelow100PctOfFederalPovertyLineIn2009Isl  \n",
       "0                                             11.333333                 \n",
       "1                                             12.000000                 \n",
       "2                                             21.000000                 \n",
       "3                                             15.876712                 \n",
       "4                                             17.000000                 \n",
       "...                                                 ...                 \n",
       "3228                                          16.400000                 \n",
       "3229                                           9.600000                 \n",
       "3230                                          13.857143                 \n",
       "3231                                          17.700000                 \n",
       "3232                                          19.636364                 \n",
       "\n",
       "[3233 rows x 87 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View Demographic Data\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get average outage data for each county, on June 20, 2016\n",
    "import pandas as pd\n",
    "ei_2016_df = pd.read_csv(\"data/eaglei_outages_2016.csv\", delimiter=',', skiprows=0, low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>fips_code</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>sum</th>\n",
       "      <th>run_start_time</th>\n",
       "      <th>datetimeformat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2122</td>\n",
       "      <td>Kenai Peninsula</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>657</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5003</td>\n",
       "      <td>Ashley</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>7</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5029</td>\n",
       "      <td>Conway</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5069</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>41</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5081</td>\n",
       "      <td>Little River</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>61</td>\n",
       "      <td>2016-01-01 00:00:00</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13306019</th>\n",
       "      <td>54061</td>\n",
       "      <td>Monongalia</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>472</td>\n",
       "      <td>2016-12-31 00:00:00</td>\n",
       "      <td>2016-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13306020</th>\n",
       "      <td>54069</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>37</td>\n",
       "      <td>2016-12-31 00:00:00</td>\n",
       "      <td>2016-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13306021</th>\n",
       "      <td>54075</td>\n",
       "      <td>Pocahontas</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>200</td>\n",
       "      <td>2016-12-31 00:00:00</td>\n",
       "      <td>2016-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13306022</th>\n",
       "      <td>54099</td>\n",
       "      <td>Wayne</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-12-31 00:00:00</td>\n",
       "      <td>2016-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13306023</th>\n",
       "      <td>55103</td>\n",
       "      <td>Richland</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-12-31 00:00:00</td>\n",
       "      <td>2016-12-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13306024 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          fips_code           county          state  sum       run_start_time  \\\n",
       "0              2122  Kenai Peninsula         Alaska  657  2016-01-01 00:00:00   \n",
       "1              5003           Ashley       Arkansas    7  2016-01-01 00:00:00   \n",
       "2              5029           Conway       Arkansas    2  2016-01-01 00:00:00   \n",
       "3              5069        Jefferson       Arkansas   41  2016-01-01 00:00:00   \n",
       "4              5081     Little River       Arkansas   61  2016-01-01 00:00:00   \n",
       "...             ...              ...            ...  ...                  ...   \n",
       "13306019      54061       Monongalia  West Virginia  472  2016-12-31 00:00:00   \n",
       "13306020      54069             Ohio  West Virginia   37  2016-12-31 00:00:00   \n",
       "13306021      54075       Pocahontas  West Virginia  200  2016-12-31 00:00:00   \n",
       "13306022      54099            Wayne  West Virginia    2  2016-12-31 00:00:00   \n",
       "13306023      55103         Richland      Wisconsin    1  2016-12-31 00:00:00   \n",
       "\n",
       "         datetimeformat  \n",
       "0            2016-01-01  \n",
       "1            2016-01-01  \n",
       "2            2016-01-01  \n",
       "3            2016-01-01  \n",
       "4            2016-01-01  \n",
       "...                 ...  \n",
       "13306019     2016-12-31  \n",
       "13306020     2016-12-31  \n",
       "13306021     2016-12-31  \n",
       "13306022     2016-12-31  \n",
       "13306023     2016-12-31  \n",
       "\n",
       "[13306024 rows x 6 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# converting the run_start_time column to pandas' 'Timestamp' format\n",
    "ei_2016_df[\"datetimeformat\"] = pd.to_datetime(ei_2016_df[\"run_start_time\"], format=\"%Y-%m-%d %H:%M:%S\")\n",
    "ei_2016_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# selecting only data for June 20\n",
    "ei_20160620_df = ei_2016_df.loc[(ei_2016_df[\"datetimeformat\"] >= \"2016-06-19\") \n",
    "                                & (ei_2016_df[\"datetimeformat\"] < \"2016-06-20\")]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grouping by the fips code so that we can aggregate the whole day's data on a county by county basis\n",
    "# (each fips code represents a county)\n",
    "ei_fipscode_group = ei_20160620_df.groupby(\"fips_code\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fips_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1003</th>\n",
       "      <td>23.458333</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031</th>\n",
       "      <td>5.060606</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1039</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1041</th>\n",
       "      <td>2.238095</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1089</th>\n",
       "      <td>10.577778</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55129</th>\n",
       "      <td>6.789474</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55131</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55133</th>\n",
       "      <td>30.552239</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55137</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56021</th>\n",
       "      <td>397.230769</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1399 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  sum       \n",
       "                 mean median\n",
       "fips_code                   \n",
       "1003        23.458333   15.0\n",
       "1031         5.060606    1.0\n",
       "1039         1.000000    1.0\n",
       "1041         2.238095    2.0\n",
       "1089        10.577778    6.0\n",
       "...               ...    ...\n",
       "55129        6.789474    2.0\n",
       "55131        1.000000    1.0\n",
       "55133       30.552239    3.0\n",
       "55137        1.000000    1.0\n",
       "56021      397.230769   80.0\n",
       "\n",
       "[1399 rows x 2 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# now we can aggregate the data in each group (i.e. in each county) to get the mean and median of the  (and \n",
    "# others too if you want) \n",
    "ei_fipscode_agg = ei_fipscode_group.agg({\"sum\":[\"mean\", \"median\"] })\n",
    "ei_fipscode_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check level 1 - Index(['mean', 'median'], dtype='object')\n",
    "level1 = ei_fipscode_agg.columns.get_level_values(1) # mean and median\n",
    "level1\n",
    "\n",
    "# Assign column names to level 1 (mean and median)\n",
    "ei_fipscode_agg.columns = level1 \n",
    "\n",
    "# View aggregated dataset\n",
    "ei_fipscode_agg\n",
    "\n",
    "# Reset the index\n",
    "county_agg = ei_fipscode_agg.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that there are multiple levels of indexes in the dataframe \n",
    "- In order to merge the demographics data with the mean and median power outages, we need to convert the dataframe to a form so that the 2 datasets can be easily merged\n",
    "\n",
    "- In this case, the common variable between the eaglei (power outage data), and demographics is the fips_code and GEOID "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename fips_code as GEOID\n",
    "agg2 = county_agg.rename(columns={\"fips_code\": \"GEOID\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge on GEOID to get mean & median number of power outages with the Demographics data\n",
    "all_df = pd.merge(df, agg2, on = 'GEOID', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>OBJECTID</th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "      <th>Avg_PercentBlackorAfricanAmericanalone</th>\n",
       "      <th>Avg_PercentAmericanIndian_AlaskaNative</th>\n",
       "      <th>Avg_PercentAsian</th>\n",
       "      <th>...</th>\n",
       "      <th>Avg_PercentOfIndividualsBelow200PctFederalPovertyLine</th>\n",
       "      <th>Avg_PercentOfIndividualsLt10oPctFederalPovertyLinePctile</th>\n",
       "      <th>Avg_PercentOfIndividualsLt100PctFederalPovertyLine</th>\n",
       "      <th>Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegreePct</th>\n",
       "      <th>Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegree</th>\n",
       "      <th>Avg_PercentOfResidentsWhoAreNotCurrentlyEnrolledInHigherEd</th>\n",
       "      <th>Avg_UnemploymentPctIn2009IslandAnd2010StatesAndPR</th>\n",
       "      <th>Avg_PercentageHouseholdsBelow100PctOfFederalPovertyLineIn2009Isl</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>39</td>\n",
       "      <td>31039</td>\n",
       "      <td>Cuming</td>\n",
       "      <td>41.915865</td>\n",
       "      <td>-96.788517</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>6.666667</td>\n",
       "      <td>49.000000</td>\n",
       "      <td>10.333333</td>\n",
       "      <td>95.333333</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>11.333333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "      <td>69</td>\n",
       "      <td>53069</td>\n",
       "      <td>Wahkiakum</td>\n",
       "      <td>46.294638</td>\n",
       "      <td>-123.424458</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>...</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>35</td>\n",
       "      <td>11</td>\n",
       "      <td>35011</td>\n",
       "      <td>De Baca</td>\n",
       "      <td>34.359273</td>\n",
       "      <td>-104.368696</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>109</td>\n",
       "      <td>31109</td>\n",
       "      <td>Lancaster</td>\n",
       "      <td>40.783547</td>\n",
       "      <td>-96.688658</td>\n",
       "      <td>0.040811</td>\n",
       "      <td>0.005000</td>\n",
       "      <td>0.040541</td>\n",
       "      <td>...</td>\n",
       "      <td>31.112676</td>\n",
       "      <td>46.774648</td>\n",
       "      <td>13.929577</td>\n",
       "      <td>30.729730</td>\n",
       "      <td>6.662162</td>\n",
       "      <td>84.608108</td>\n",
       "      <td>4.041667</td>\n",
       "      <td>15.876712</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>129</td>\n",
       "      <td>31129</td>\n",
       "      <td>Nuckolls</td>\n",
       "      <td>40.176492</td>\n",
       "      <td>-98.046842</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015000</td>\n",
       "      <td>...</td>\n",
       "      <td>35.500000</td>\n",
       "      <td>45.500000</td>\n",
       "      <td>10.500000</td>\n",
       "      <td>39.500000</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>96.500000</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3228</th>\n",
       "      <td>3229</td>\n",
       "      <td>13</td>\n",
       "      <td>123</td>\n",
       "      <td>13123</td>\n",
       "      <td>Gilmer</td>\n",
       "      <td>34.690506</td>\n",
       "      <td>-84.454624</td>\n",
       "      <td>0.004000</td>\n",
       "      <td>0.008000</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>...</td>\n",
       "      <td>36.600000</td>\n",
       "      <td>62.200000</td>\n",
       "      <td>15.800000</td>\n",
       "      <td>73.400000</td>\n",
       "      <td>17.200000</td>\n",
       "      <td>96.400000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>16.400000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3229</th>\n",
       "      <td>3230</td>\n",
       "      <td>27</td>\n",
       "      <td>135</td>\n",
       "      <td>27135</td>\n",
       "      <td>Roseau</td>\n",
       "      <td>48.761068</td>\n",
       "      <td>-95.821504</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.018000</td>\n",
       "      <td>...</td>\n",
       "      <td>24.600000</td>\n",
       "      <td>34.200000</td>\n",
       "      <td>7.800000</td>\n",
       "      <td>34.400000</td>\n",
       "      <td>6.400000</td>\n",
       "      <td>96.400000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>9.600000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3230</th>\n",
       "      <td>3231</td>\n",
       "      <td>28</td>\n",
       "      <td>89</td>\n",
       "      <td>28089</td>\n",
       "      <td>Madison</td>\n",
       "      <td>32.634370</td>\n",
       "      <td>-90.034160</td>\n",
       "      <td>0.394762</td>\n",
       "      <td>0.000476</td>\n",
       "      <td>0.026667</td>\n",
       "      <td>...</td>\n",
       "      <td>26.476190</td>\n",
       "      <td>34.666667</td>\n",
       "      <td>9.761905</td>\n",
       "      <td>38.428571</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>91.238095</td>\n",
       "      <td>4.619048</td>\n",
       "      <td>13.857143</td>\n",
       "      <td>39.541667</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3231</th>\n",
       "      <td>3232</td>\n",
       "      <td>48</td>\n",
       "      <td>227</td>\n",
       "      <td>48227</td>\n",
       "      <td>Howard</td>\n",
       "      <td>32.303471</td>\n",
       "      <td>-101.438772</td>\n",
       "      <td>0.045000</td>\n",
       "      <td>0.008000</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>...</td>\n",
       "      <td>37.700000</td>\n",
       "      <td>59.600000</td>\n",
       "      <td>18.100000</td>\n",
       "      <td>73.600000</td>\n",
       "      <td>19.100000</td>\n",
       "      <td>95.400000</td>\n",
       "      <td>5.700000</td>\n",
       "      <td>17.700000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3232</th>\n",
       "      <td>3233</td>\n",
       "      <td>54</td>\n",
       "      <td>99</td>\n",
       "      <td>54099</td>\n",
       "      <td>Wayne</td>\n",
       "      <td>38.143642</td>\n",
       "      <td>-82.422666</td>\n",
       "      <td>0.009091</td>\n",
       "      <td>0.000909</td>\n",
       "      <td>0.003636</td>\n",
       "      <td>...</td>\n",
       "      <td>46.454545</td>\n",
       "      <td>78.545455</td>\n",
       "      <td>23.636364</td>\n",
       "      <td>73.636364</td>\n",
       "      <td>19.909091</td>\n",
       "      <td>94.181818</td>\n",
       "      <td>4.818182</td>\n",
       "      <td>19.636364</td>\n",
       "      <td>20.260417</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3233 rows × 89 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      OBJECTID  STATEFP  COUNTYFP  GEOID       NAME        Lat         Lon  \\\n",
       "0            1       31        39  31039     Cuming  41.915865  -96.788517   \n",
       "1            2       53        69  53069  Wahkiakum  46.294638 -123.424458   \n",
       "2            3       35        11  35011    De Baca  34.359273 -104.368696   \n",
       "3            4       31       109  31109  Lancaster  40.783547  -96.688658   \n",
       "4            5       31       129  31129   Nuckolls  40.176492  -98.046842   \n",
       "...        ...      ...       ...    ...        ...        ...         ...   \n",
       "3228      3229       13       123  13123     Gilmer  34.690506  -84.454624   \n",
       "3229      3230       27       135  27135     Roseau  48.761068  -95.821504   \n",
       "3230      3231       28        89  28089    Madison  32.634370  -90.034160   \n",
       "3231      3232       48       227  48227     Howard  32.303471 -101.438772   \n",
       "3232      3233       54        99  54099      Wayne  38.143642  -82.422666   \n",
       "\n",
       "      Avg_PercentBlackorAfricanAmericanalone  \\\n",
       "0                                   0.000000   \n",
       "1                                   0.010000   \n",
       "2                                   0.010000   \n",
       "3                                   0.040811   \n",
       "4                                   0.000000   \n",
       "...                                      ...   \n",
       "3228                                0.004000   \n",
       "3229                                0.010000   \n",
       "3230                                0.394762   \n",
       "3231                                0.045000   \n",
       "3232                                0.009091   \n",
       "\n",
       "      Avg_PercentAmericanIndian_AlaskaNative  Avg_PercentAsian  ...  \\\n",
       "0                                   0.000000          0.000000  ...   \n",
       "1                                   0.000000          0.010000  ...   \n",
       "2                                   0.000000          0.000000  ...   \n",
       "3                                   0.005000          0.040541  ...   \n",
       "4                                   0.000000          0.015000  ...   \n",
       "...                                      ...               ...  ...   \n",
       "3228                                0.008000          0.002000  ...   \n",
       "3229                                0.010000          0.018000  ...   \n",
       "3230                                0.000476          0.026667  ...   \n",
       "3231                                0.008000          0.002000  ...   \n",
       "3232                                0.000909          0.003636  ...   \n",
       "\n",
       "      Avg_PercentOfIndividualsBelow200PctFederalPovertyLine  \\\n",
       "0                                             27.000000       \n",
       "1                                             25.000000       \n",
       "2                                             50.000000       \n",
       "3                                             31.112676       \n",
       "4                                             35.500000       \n",
       "...                                                 ...       \n",
       "3228                                          36.600000       \n",
       "3229                                          24.600000       \n",
       "3230                                          26.476190       \n",
       "3231                                          37.700000       \n",
       "3232                                          46.454545       \n",
       "\n",
       "      Avg_PercentOfIndividualsLt10oPctFederalPovertyLinePctile  \\\n",
       "0                                             29.000000          \n",
       "1                                             30.000000          \n",
       "2                                             64.000000          \n",
       "3                                             46.774648          \n",
       "4                                             45.500000          \n",
       "...                                                 ...          \n",
       "3228                                          62.200000          \n",
       "3229                                          34.200000          \n",
       "3230                                          34.666667          \n",
       "3231                                          59.600000          \n",
       "3232                                          78.545455          \n",
       "\n",
       "      Avg_PercentOfIndividualsLt100PctFederalPovertyLine  \\\n",
       "0                                              6.666667    \n",
       "1                                              7.000000    \n",
       "2                                             16.000000    \n",
       "3                                             13.929577    \n",
       "4                                             10.500000    \n",
       "...                                                 ...    \n",
       "3228                                          15.800000    \n",
       "3229                                           7.800000    \n",
       "3230                                           9.761905    \n",
       "3231                                          18.100000    \n",
       "3232                                          23.636364    \n",
       "\n",
       "      Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegreePct  \\\n",
       "0                                             49.000000                  \n",
       "1                                             40.000000                  \n",
       "2                                             70.000000                  \n",
       "3                                             30.729730                  \n",
       "4                                             39.500000                  \n",
       "...                                                 ...                  \n",
       "3228                                          73.400000                  \n",
       "3229                                          34.400000                  \n",
       "3230                                          38.428571                  \n",
       "3231                                          73.600000                  \n",
       "3232                                          73.636364                  \n",
       "\n",
       "      Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegree  \\\n",
       "0                                             10.333333               \n",
       "1                                              7.000000               \n",
       "2                                             15.000000               \n",
       "3                                              6.662162               \n",
       "4                                              7.500000               \n",
       "...                                                 ...               \n",
       "3228                                          17.200000               \n",
       "3229                                           6.400000               \n",
       "3230                                           9.000000               \n",
       "3231                                          19.100000               \n",
       "3232                                          19.909091               \n",
       "\n",
       "      Avg_PercentOfResidentsWhoAreNotCurrentlyEnrolledInHigherEd  \\\n",
       "0                                             95.333333            \n",
       "1                                             96.000000            \n",
       "2                                             96.000000            \n",
       "3                                             84.608108            \n",
       "4                                             96.500000            \n",
       "...                                                 ...            \n",
       "3228                                          96.400000            \n",
       "3229                                          96.400000            \n",
       "3230                                          91.238095            \n",
       "3231                                          95.400000            \n",
       "3232                                          94.181818            \n",
       "\n",
       "      Avg_UnemploymentPctIn2009IslandAnd2010StatesAndPR  \\\n",
       "0                                              1.333333   \n",
       "1                                              7.000000   \n",
       "2                                              0.000000   \n",
       "3                                              4.041667   \n",
       "4                                              2.500000   \n",
       "...                                                 ...   \n",
       "3228                                           6.000000   \n",
       "3229                                           3.000000   \n",
       "3230                                           4.619048   \n",
       "3231                                           5.700000   \n",
       "3232                                           4.818182   \n",
       "\n",
       "      Avg_PercentageHouseholdsBelow100PctOfFederalPovertyLineIn2009Isl  \\\n",
       "0                                             11.333333                  \n",
       "1                                             12.000000                  \n",
       "2                                             21.000000                  \n",
       "3                                             15.876712                  \n",
       "4                                             17.000000                  \n",
       "...                                                 ...                  \n",
       "3228                                          16.400000                  \n",
       "3229                                           9.600000                  \n",
       "3230                                          13.857143                  \n",
       "3231                                          17.700000                  \n",
       "3232                                          19.636364                  \n",
       "\n",
       "           mean  median  \n",
       "0           NaN     NaN  \n",
       "1           NaN     NaN  \n",
       "2           NaN     NaN  \n",
       "3           NaN     NaN  \n",
       "4           NaN     NaN  \n",
       "...         ...     ...  \n",
       "3228        NaN     NaN  \n",
       "3229        NaN     NaN  \n",
       "3230  39.541667    32.0  \n",
       "3231        NaN     NaN  \n",
       "3232  20.260417     8.5  \n",
       "\n",
       "[3233 rows x 89 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>OBJECTID</th>\n",
       "      <th>STATEFP</th>\n",
       "      <th>COUNTYFP</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>NAME</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "      <th>Avg_PercentBlackorAfricanAmericanalone</th>\n",
       "      <th>Avg_PercentAmericanIndian_AlaskaNative</th>\n",
       "      <th>Avg_PercentAsian</th>\n",
       "      <th>...</th>\n",
       "      <th>Avg_PercentOfIndividualsBelow200PctFederalPovertyLine</th>\n",
       "      <th>Avg_PercentOfIndividualsLt10oPctFederalPovertyLinePctile</th>\n",
       "      <th>Avg_PercentOfIndividualsLt100PctFederalPovertyLine</th>\n",
       "      <th>Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegreePct</th>\n",
       "      <th>Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegree</th>\n",
       "      <th>Avg_PercentOfResidentsWhoAreNotCurrentlyEnrolledInHigherEd</th>\n",
       "      <th>Avg_UnemploymentPctIn2009IslandAnd2010StatesAndPR</th>\n",
       "      <th>Avg_PercentageHouseholdsBelow100PctOfFederalPovertyLineIn2009Isl</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1973</th>\n",
       "      <td>1974</td>\n",
       "      <td>13</td>\n",
       "      <td>299</td>\n",
       "      <td>13299</td>\n",
       "      <td>Ware</td>\n",
       "      <td>31.050881</td>\n",
       "      <td>-82.421507</td>\n",
       "      <td>0.350000</td>\n",
       "      <td>0.003333</td>\n",
       "      <td>0.011111</td>\n",
       "      <td>...</td>\n",
       "      <td>54.666667</td>\n",
       "      <td>76.666667</td>\n",
       "      <td>25.777778</td>\n",
       "      <td>72.222222</td>\n",
       "      <td>18.333333</td>\n",
       "      <td>95.444444</td>\n",
       "      <td>6.222222</td>\n",
       "      <td>21.666667</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2301</th>\n",
       "      <td>2302</td>\n",
       "      <td>31</td>\n",
       "      <td>19</td>\n",
       "      <td>31019</td>\n",
       "      <td>Buffalo</td>\n",
       "      <td>40.855226</td>\n",
       "      <td>-99.074983</td>\n",
       "      <td>0.006364</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.011818</td>\n",
       "      <td>...</td>\n",
       "      <td>33.181818</td>\n",
       "      <td>49.454545</td>\n",
       "      <td>14.181818</td>\n",
       "      <td>34.545455</td>\n",
       "      <td>6.636364</td>\n",
       "      <td>86.363636</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>13.636364</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>973</th>\n",
       "      <td>974</td>\n",
       "      <td>55</td>\n",
       "      <td>89</td>\n",
       "      <td>55089</td>\n",
       "      <td>Ozaukee</td>\n",
       "      <td>43.360752</td>\n",
       "      <td>-87.499304</td>\n",
       "      <td>0.008333</td>\n",
       "      <td>0.000556</td>\n",
       "      <td>0.017222</td>\n",
       "      <td>...</td>\n",
       "      <td>14.611111</td>\n",
       "      <td>19.944444</td>\n",
       "      <td>4.944444</td>\n",
       "      <td>13.666667</td>\n",
       "      <td>2.722222</td>\n",
       "      <td>91.833333</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.277778</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>519</td>\n",
       "      <td>36</td>\n",
       "      <td>41</td>\n",
       "      <td>36041</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>43.657879</td>\n",
       "      <td>-74.502456</td>\n",
       "      <td>0.007500</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>8.750000</td>\n",
       "      <td>70.750000</td>\n",
       "      <td>20.250000</td>\n",
       "      <td>96.250000</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>9.250000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2310</th>\n",
       "      <td>2311</td>\n",
       "      <td>37</td>\n",
       "      <td>197</td>\n",
       "      <td>37197</td>\n",
       "      <td>Yadkin</td>\n",
       "      <td>36.158765</td>\n",
       "      <td>-80.665164</td>\n",
       "      <td>0.031429</td>\n",
       "      <td>0.002857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>37.428571</td>\n",
       "      <td>59.571429</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>74.857143</td>\n",
       "      <td>17.571429</td>\n",
       "      <td>95.571429</td>\n",
       "      <td>4.571429</td>\n",
       "      <td>13.428571</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3225</th>\n",
       "      <td>3226</td>\n",
       "      <td>40</td>\n",
       "      <td>47</td>\n",
       "      <td>40047</td>\n",
       "      <td>Garfield</td>\n",
       "      <td>36.378273</td>\n",
       "      <td>-97.787729</td>\n",
       "      <td>0.019167</td>\n",
       "      <td>0.015833</td>\n",
       "      <td>0.007500</td>\n",
       "      <td>...</td>\n",
       "      <td>33.750000</td>\n",
       "      <td>46.083333</td>\n",
       "      <td>12.083333</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>10.833333</td>\n",
       "      <td>93.750000</td>\n",
       "      <td>2.583333</td>\n",
       "      <td>15.333333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3226</th>\n",
       "      <td>3227</td>\n",
       "      <td>53</td>\n",
       "      <td>59</td>\n",
       "      <td>53059</td>\n",
       "      <td>Skamania</td>\n",
       "      <td>46.024785</td>\n",
       "      <td>-121.953232</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>0.012000</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>...</td>\n",
       "      <td>25.200000</td>\n",
       "      <td>54.800000</td>\n",
       "      <td>12.800000</td>\n",
       "      <td>42.400000</td>\n",
       "      <td>8.200000</td>\n",
       "      <td>96.000000</td>\n",
       "      <td>4.400000</td>\n",
       "      <td>7.800000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3228</th>\n",
       "      <td>3229</td>\n",
       "      <td>13</td>\n",
       "      <td>123</td>\n",
       "      <td>13123</td>\n",
       "      <td>Gilmer</td>\n",
       "      <td>34.690506</td>\n",
       "      <td>-84.454624</td>\n",
       "      <td>0.004000</td>\n",
       "      <td>0.008000</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>...</td>\n",
       "      <td>36.600000</td>\n",
       "      <td>62.200000</td>\n",
       "      <td>15.800000</td>\n",
       "      <td>73.400000</td>\n",
       "      <td>17.200000</td>\n",
       "      <td>96.400000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>16.400000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3229</th>\n",
       "      <td>3230</td>\n",
       "      <td>27</td>\n",
       "      <td>135</td>\n",
       "      <td>27135</td>\n",
       "      <td>Roseau</td>\n",
       "      <td>48.761068</td>\n",
       "      <td>-95.821504</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.018000</td>\n",
       "      <td>...</td>\n",
       "      <td>24.600000</td>\n",
       "      <td>34.200000</td>\n",
       "      <td>7.800000</td>\n",
       "      <td>34.400000</td>\n",
       "      <td>6.400000</td>\n",
       "      <td>96.400000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>9.600000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3231</th>\n",
       "      <td>3232</td>\n",
       "      <td>48</td>\n",
       "      <td>227</td>\n",
       "      <td>48227</td>\n",
       "      <td>Howard</td>\n",
       "      <td>32.303471</td>\n",
       "      <td>-101.438772</td>\n",
       "      <td>0.045000</td>\n",
       "      <td>0.008000</td>\n",
       "      <td>0.002000</td>\n",
       "      <td>...</td>\n",
       "      <td>37.700000</td>\n",
       "      <td>59.600000</td>\n",
       "      <td>18.100000</td>\n",
       "      <td>73.600000</td>\n",
       "      <td>19.100000</td>\n",
       "      <td>95.400000</td>\n",
       "      <td>5.700000</td>\n",
       "      <td>17.700000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3233 rows × 89 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      OBJECTID  STATEFP  COUNTYFP  GEOID      NAME        Lat         Lon  \\\n",
       "1973      1974       13       299  13299      Ware  31.050881  -82.421507   \n",
       "2301      2302       31        19  31019   Buffalo  40.855226  -99.074983   \n",
       "973        974       55        89  55089   Ozaukee  43.360752  -87.499304   \n",
       "518        519       36        41  36041  Hamilton  43.657879  -74.502456   \n",
       "2310      2311       37       197  37197    Yadkin  36.158765  -80.665164   \n",
       "...        ...      ...       ...    ...       ...        ...         ...   \n",
       "3225      3226       40        47  40047  Garfield  36.378273  -97.787729   \n",
       "3226      3227       53        59  53059  Skamania  46.024785 -121.953232   \n",
       "3228      3229       13       123  13123    Gilmer  34.690506  -84.454624   \n",
       "3229      3230       27       135  27135    Roseau  48.761068  -95.821504   \n",
       "3231      3232       48       227  48227    Howard  32.303471 -101.438772   \n",
       "\n",
       "      Avg_PercentBlackorAfricanAmericanalone  \\\n",
       "1973                                0.350000   \n",
       "2301                                0.006364   \n",
       "973                                 0.008333   \n",
       "518                                 0.007500   \n",
       "2310                                0.031429   \n",
       "...                                      ...   \n",
       "3225                                0.019167   \n",
       "3226                                0.002000   \n",
       "3228                                0.004000   \n",
       "3229                                0.010000   \n",
       "3231                                0.045000   \n",
       "\n",
       "      Avg_PercentAmericanIndian_AlaskaNative  Avg_PercentAsian  ...  \\\n",
       "1973                                0.003333          0.011111  ...   \n",
       "2301                                0.000000          0.011818  ...   \n",
       "973                                 0.000556          0.017222  ...   \n",
       "518                                 0.000000          0.000000  ...   \n",
       "2310                                0.002857          0.000000  ...   \n",
       "...                                      ...               ...  ...   \n",
       "3225                                0.015833          0.007500  ...   \n",
       "3226                                0.012000          0.002000  ...   \n",
       "3228                                0.008000          0.002000  ...   \n",
       "3229                                0.010000          0.018000  ...   \n",
       "3231                                0.008000          0.002000  ...   \n",
       "\n",
       "      Avg_PercentOfIndividualsBelow200PctFederalPovertyLine  \\\n",
       "1973                                          54.666667       \n",
       "2301                                          33.181818       \n",
       "973                                           14.611111       \n",
       "518                                           25.000000       \n",
       "2310                                          37.428571       \n",
       "...                                                 ...       \n",
       "3225                                          33.750000       \n",
       "3226                                          25.200000       \n",
       "3228                                          36.600000       \n",
       "3229                                          24.600000       \n",
       "3231                                          37.700000       \n",
       "\n",
       "      Avg_PercentOfIndividualsLt10oPctFederalPovertyLinePctile  \\\n",
       "1973                                          76.666667          \n",
       "2301                                          49.454545          \n",
       "973                                           19.944444          \n",
       "518                                           39.000000          \n",
       "2310                                          59.571429          \n",
       "...                                                 ...          \n",
       "3225                                          46.083333          \n",
       "3226                                          54.800000          \n",
       "3228                                          62.200000          \n",
       "3229                                          34.200000          \n",
       "3231                                          59.600000          \n",
       "\n",
       "      Avg_PercentOfIndividualsLt100PctFederalPovertyLine  \\\n",
       "1973                                          25.777778    \n",
       "2301                                          14.181818    \n",
       "973                                            4.944444    \n",
       "518                                            8.750000    \n",
       "2310                                          15.000000    \n",
       "...                                                 ...    \n",
       "3225                                          12.083333    \n",
       "3226                                          12.800000    \n",
       "3228                                          15.800000    \n",
       "3229                                           7.800000    \n",
       "3231                                          18.100000    \n",
       "\n",
       "      Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegreePct  \\\n",
       "1973                                          72.222222                  \n",
       "2301                                          34.545455                  \n",
       "973                                           13.666667                  \n",
       "518                                           70.750000                  \n",
       "2310                                          74.857143                  \n",
       "...                                                 ...                  \n",
       "3225                                          50.000000                  \n",
       "3226                                          42.400000                  \n",
       "3228                                          73.400000                  \n",
       "3229                                          34.400000                  \n",
       "3231                                          73.600000                  \n",
       "\n",
       "      Avg_PercentIndividualsAge25OrOverWithLessThanHighSchoolDegree  \\\n",
       "1973                                          18.333333               \n",
       "2301                                           6.636364               \n",
       "973                                            2.722222               \n",
       "518                                           20.250000               \n",
       "2310                                          17.571429               \n",
       "...                                                 ...               \n",
       "3225                                          10.833333               \n",
       "3226                                           8.200000               \n",
       "3228                                          17.200000               \n",
       "3229                                           6.400000               \n",
       "3231                                          19.100000               \n",
       "\n",
       "      Avg_PercentOfResidentsWhoAreNotCurrentlyEnrolledInHigherEd  \\\n",
       "1973                                          95.444444            \n",
       "2301                                          86.363636            \n",
       "973                                           91.833333            \n",
       "518                                           96.250000            \n",
       "2310                                          95.571429            \n",
       "...                                                 ...            \n",
       "3225                                          93.750000            \n",
       "3226                                          96.000000            \n",
       "3228                                          96.400000            \n",
       "3229                                          96.400000            \n",
       "3231                                          95.400000            \n",
       "\n",
       "      Avg_UnemploymentPctIn2009IslandAnd2010StatesAndPR  \\\n",
       "1973                                           6.222222   \n",
       "2301                                           2.000000   \n",
       "973                                            3.000000   \n",
       "518                                            1.250000   \n",
       "2310                                           4.571429   \n",
       "...                                                 ...   \n",
       "3225                                           2.583333   \n",
       "3226                                           4.400000   \n",
       "3228                                           6.000000   \n",
       "3229                                           3.000000   \n",
       "3231                                           5.700000   \n",
       "\n",
       "      Avg_PercentageHouseholdsBelow100PctOfFederalPovertyLineIn2009Isl  mean  \\\n",
       "1973                                          21.666667                  1.0   \n",
       "2301                                          13.636364                  1.0   \n",
       "973                                            4.277778                  1.0   \n",
       "518                                            9.250000                  1.0   \n",
       "2310                                          13.428571                  1.0   \n",
       "...                                                 ...                  ...   \n",
       "3225                                          15.333333                  NaN   \n",
       "3226                                           7.800000                  NaN   \n",
       "3228                                          16.400000                  NaN   \n",
       "3229                                           9.600000                  NaN   \n",
       "3231                                          17.700000                  NaN   \n",
       "\n",
       "      median  \n",
       "1973     1.0  \n",
       "2301     1.0  \n",
       "973      1.0  \n",
       "518      1.0  \n",
       "2310     1.0  \n",
       "...      ...  \n",
       "3225     NaN  \n",
       "3226     NaN  \n",
       "3228     NaN  \n",
       "3229     NaN  \n",
       "3231     NaN  \n",
       "\n",
       "[3233 rows x 89 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View data sorted by mean\n",
    "all_df.sort_values(by=['mean'], inplace=True)\n",
    "\n",
    "all_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "OBJECTID                                                            3233\n",
       "STATEFP                                                             3233\n",
       "COUNTYFP                                                            3233\n",
       "GEOID                                                               3233\n",
       "NAME                                                                3233\n",
       "                                                                    ... \n",
       "Avg_PercentOfResidentsWhoAreNotCurrentlyEnrolledInHigherEd          3233\n",
       "Avg_UnemploymentPctIn2009IslandAnd2010StatesAndPR                   3233\n",
       "Avg_PercentageHouseholdsBelow100PctOfFederalPovertyLineIn2009Isl    3233\n",
       "mean                                                                1399\n",
       "median                                                              1399\n",
       "Length: 89, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View number of non-empty records in each column\n",
    "all_df.count(axis = 0)"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}