CRISPDM_0_StreamlinedDataIngestionWithPandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Streamlined Data Ingestion with pandas\n",
    "Before you can analyze data, you first have to acquire it. This course teaches you how to build pipelines to import data kept in common storage formats. You’ll use pandas, a major Python library for analytics, to get data from a variety of sources, from spreadsheets of survey responses, to a database of public service requests, to an API for a popular review site. Along the way, you’ll learn how to fine-tune imports to get only what you need and to address issues like incorrect data types. Finally, you’ll assemble a custom dataset from a mix of sources.\n",
    "\n",
    "## Get data from CSVs\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   STATEFIPS STATE  zipcode  agi_stub      N1  mars1  MARS2  MARS4   PREP  \\\n",
      "0         50    VT        0         1  111580  85090  14170  10740  45360   \n",
      "1         50    VT        0         2   82760  51960  18820  11310  35600   \n",
      "2         50    VT        0         3   46270  19540  22650   3620  24140   \n",
      "3         50    VT        0         4   30070   5830  22190    960  16060   \n",
      "4         50    VT        0         5   39530   3900  33800    590  22500   \n",
      "\n",
      "       N2  ...  N10300  A10300  N85530  A85530  N85300  A85300  N11901  \\\n",
      "0  130630  ...   53660   50699       0       0       0       0   10820   \n",
      "1  132950  ...   74340  221146       0       0       0       0   12820   \n",
      "2   91870  ...   44860  266097       0       0       0       0   10810   \n",
      "3   71610  ...   29580  264678       0       0       0       0    7320   \n",
      "4  103710  ...   39170  731963      40      24       0       0   12500   \n",
      "\n",
      "   A11901  N11902  A11902  \n",
      "0    9734   88260  138337  \n",
      "1   20029   68760  151729  \n",
      "2   24499   34600   90583  \n",
      "3   21573   21300   67045  \n",
      "4   67761   23320  103034  \n",
      "\n",
      "[5 rows x 147 columns]\n"
     ]
    }
   ],
   "source": [
    "# Import pandas as pd\n",
    "import pandas  as pd\n",
    "\n",
    "# Read the CSV and assign it to the variable data\n",
    "data = pd.read_csv('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/vt_tax_data_2016.csv')\n",
    "\n",
    "# View the first few lines of data\n",
    "print(data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYoAAAEECAYAAAA2xHO4AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAASNklEQVR4nO3dfdCldV3H8fcnVhlMwUVWYnbRpaQULUm2haImmnV2SZogg2ltkp3CthxKe57FajCdbWCmYmQSHIyVh0ogSsFQaQMfxkJgUZSniE1QNp62dkXQpBa//XF+93j29t7ffe/9dPZe3q+ZM+c63+v6/c7vmn34nOv6Xec6qSokSdqT7xr1ACRJ+zaDQpLUZVBIkroMCklSl0EhSeoyKCRJXYtGPYDZdthhh9Xy5ctHPQxJWlDuuOOO/6qqJROt2++CYvny5WzZsmXUw5CkBSXJl/e0zlNPkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHXtd1+4m67lG26Y1/d76LxT5vX9JGm6PKKQJHUZFJKkLoNCktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdU0aFEmOTPKJJPcluSfJ21v90CSbkzzQnhcPtTknydYk9ydZM1Q/Lsldbd2FSdLqBya5utVvTbJ8qM269h4PJFk3mzsvSZrcVI4odgG/W1WvAk4Azk5yDLABuKmqjgZuaq9p69YCrwZOBi5KckDr62JgPXB0e5zc6mcBO6vqFcAFwPmtr0OBc4HjgZXAucOBJEmae5MGRVU9WlWfa8tPAfcBS4FTgcvbZpcDp7XlU4GrquqZqnoQ2AqsTHIEcHBV3VJVBVwxrs1YX9cCq9rRxhpgc1XtqKqdwGa+HS6SpHmwV3MU7ZTQDwO3AodX1aMwCBPgpW2zpcDDQ822tdrStjy+vlubqtoFPAm8pNOXJGmeTDkokrwQ+Hvgt6rqa71NJ6hVpz7dNsNjW59kS5It27dv7wxNkrS3phQUSZ7HICT+pqr+oZUfb6eTaM9PtPo24Mih5suAR1p92QT13dokWQQcAuzo9LWbqrqkqlZU1YolS5ZMZZckSVM0laueAlwK3FdVfzG06npg7CqkdcB1Q/W17UqmoxhMWt/WTk89leSE1ueZ49qM9XU6cHObx7gRWJ1kcZvEXt1qkqR5smgK25wIvBm4K8mdrfYO4DzgmiRnAV8BzgCoqnuSXAPcy+CKqbOr6tnW7q3AZcBBwMfaAwZBdGWSrQyOJNa2vnYkeTdwe9vuXVW1Y5r7KkmahkmDoqo+w8RzBQCr9tBmI7BxgvoW4DUT1L9JC5oJ1m0CNk02TknS3PCb2ZKkLoNCktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHUZFJKkLoNCktRlUEiSuhaNegCaH8s33DCv7/fQeafM6/tJmjseUUiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklS16RBkWRTkieS3D1Ue2eS/0xyZ3u8YWjdOUm2Jrk/yZqh+nFJ7mrrLkySVj8wydWtfmuS5UNt1iV5oD3WzdZOS5KmbipHFJcBJ09Qv6Cqjm2PjwIkOQZYC7y6tbkoyQFt+4uB9cDR7THW51nAzqp6BXABcH7r61DgXOB4YCVwbpLFe72HkqQZmTQoqurTwI4p9ncqcFVVPVNVDwJbgZVJjgAOrqpbqqqAK4DThtpc3pavBVa1o401wOaq2lFVO4HNTBxYkqQ5NJM5it9I8sV2amrsk/5S4OGhbba12tK2PL6+W5uq2gU8Cbyk09d3SLI+yZYkW7Zv3z6DXZIkjTfdoLgY+D7gWOBR4M9bPRNsW536dNvsXqy6pKpWVNWKJUuW9MYtSdpL0wqKqnq8qp6tqm8B72cwhwCDT/1HDm26DHik1ZdNUN+tTZJFwCEMTnXtqS9J0jyaVlC0OYcxPweMXRF1PbC2Xcl0FINJ69uq6lHgqSQntPmHM4HrhtqMXdF0OnBzm8e4EVidZHE7tbW61SRJ82jSX7hL8kHgJOCwJNsYXIl0UpJjGZwKegj4NYCquifJNcC9wC7g7Kp6tnX1VgZXUB0EfKw9AC4FrkyylcGRxNrW144k7wZub9u9q6qmOqkuSZolkwZFVb1pgvKlne03AhsnqG8BXjNB/ZvAGXvoaxOwabIxSpLmjt/MliR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHUZFJKkLoNCktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUtekQZFkU5Inktw9VDs0yeYkD7TnxUPrzkmyNcn9SdYM1Y9Lcldbd2GStPqBSa5u9VuTLB9qs669xwNJ1s3WTkuSpm4qRxSXASePq20Abqqqo4Gb2muSHAOsBV7d2lyU5IDW5mJgPXB0e4z1eRaws6peAVwAnN/6OhQ4FzgeWAmcOxxIkqT5MWlQVNWngR3jyqcCl7fly4HThupXVdUzVfUgsBVYmeQI4OCquqWqCrhiXJuxvq4FVrWjjTXA5qraUVU7gc18Z2BJkubYdOcoDq+qRwHa80tbfSnw8NB221ptaVseX9+tTVXtAp4EXtLpS5I0j2Z7MjsT1KpTn26b3d80WZ9kS5It27dvn9JAJUlTM92geLydTqI9P9Hq24Ajh7ZbBjzS6ssmqO/WJski4BAGp7r21Nd3qKpLqmpFVa1YsmTJNHdJkjSR6QbF9cDYVUjrgOuG6mvblUxHMZi0vq2dnnoqyQlt/uHMcW3G+joduLnNY9wIrE6yuE1ir241SdI8WjTZBkk+CJwEHJZkG4Mrkc4DrklyFvAV4AyAqronyTXAvcAu4OyqerZ19VYGV1AdBHysPQAuBa5MspXBkcTa1teOJO8Gbm/bvauqxk+qS5Lm2KRBUVVv2sOqVXvYfiOwcYL6FuA1E9S/SQuaCdZtAjZNNkZJ0tzxm9mSpC6DQpLUNempJ2khWL7hhnl9v4fOO2Ve308aJY8oJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdXmvJ0kj57269m0eUUiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6/IU7aQHwF+A0Sh5RSJK6DApJUpdBIUnqMigkSV0zCookDyW5K8mdSba02qFJNid5oD0vHtr+nCRbk9yfZM1Q/bjWz9YkFyZJqx+Y5OpWvzXJ8pmMV5K092bjiOKnqurYqlrRXm8Abqqqo4Gb2muSHAOsBV4NnAxclOSA1uZiYD1wdHuc3OpnATur6hXABcD5szBeSdJemItTT6cCl7fly4HThupXVdUzVfUgsBVYmeQI4OCquqWqCrhiXJuxvq4FVo0dbUiS5sdMg6KAf0pyR5L1rXZ4VT0K0J5f2upLgYeH2m5rtaVteXx9tzZVtQt4EnjJDMcsSdoLM/3C3YlV9UiSlwKbk/xbZ9uJjgSqU++12b3jQUitB3jZy17WH7Ekaa/M6Iiiqh5pz08AHwJWAo+300m05yfa5tuAI4eaLwMeafVlE9R3a5NkEXAIsGOCcVxSVSuqasWSJUtmskuSpHGmHRRJvjvJi8aWgdXA3cD1wLq22TrgurZ8PbC2Xcl0FINJ69va6amnkpzQ5h/OHNdmrK/TgZvbPIYkaZ7M5NTT4cCH2tzyIuBvq+rjSW4HrklyFvAV4AyAqronyTXAvcAu4Oyqerb19VbgMuAg4GPtAXApcGWSrQyOJNbOYLySpGmYdlBU1ZeA105Q/29g1R7abAQ2TlDfArxmgvo3aUEjSRoNv5ktSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHUZFJKkLoNCktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklS16JRD0CS9mfLN9wwr+/30HmnzHqfHlFIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpa0EERZKTk9yfZGuSDaMejyQ9l+zzQZHkAOC9wE8DxwBvSnLMaEclSc8d+3xQACuBrVX1par6X+Aq4NQRj0mSnjMWQlAsBR4eer2t1SRJ8yBVNeoxdCU5A1hTVW9pr98MrKyq3xzaZj2wvr38AeD+eRziYcB/zeP7zTf3b2Fz/xau+d63l1fVkolWLISfQt0GHDn0ehnwyPAGVXUJcMl8DmpMki1VtWIU7z0f3L+Fzf1buPalfVsIp55uB45OclSS5wNrgetHPCZJes7Y548oqmpXkt8AbgQOADZV1T0jHpYkPWfs80EBUFUfBT466nHswUhOec0j929hc/8Wrn1m3/b5yWxJ0mgthDkKSdIIGRSSpC6DQrtJ8sokq5K8cFz95FGNaTYlWZnkR9ryMUl+J8kbRj2uuZDkilGPYa4k+fH2Z7d61GOZDUmOT3JwWz4oyZ8k+UiS85McMvLxOUcxO5L8clV9YNTjmIkkbwPOBu4DjgXeXlXXtXWfq6rXjXJ8M5XkXAb3DFsEbAaOBz4JvB64sao2jm50M5Nk/CXjAX4KuBmgqn523gc1i5LcVlUr2/KvMvh7+iFgNfCRqjpvlOObqST3AK9tV3leAnwDuBZY1epvHOn4DIrZkeQrVfWyUY9jJpLcBfxoVT2dZDmDv6hXVtV7kny+qn54pAOcobZ/xwIHAo8By6rqa0kOAm6tqh8a6QBnIMnngHuBvwKKQVB8kMH3jqiqT41udDM3/Pcvye3AG6pqe5LvBj5bVT842hHOTJL7qupVbXm3D2VJ7qyqY0c3ugVyeey+IskX97QKOHw+xzJHDqiqpwGq6qEkJwHXJnk5g31c6HZV1bPAN5L8R1V9DaCq/ifJt0Y8tplaAbwd+EPg96vqziT/s9ADYsh3JVnM4HR5qmo7QFV9Pcmu0Q5tVtw9dFbiC0lWVNWWJN8P/N+oB2dQ7J3DgTXAznH1AP86/8OZdY8lObaq7gRoRxY/A2wCFvQntuZ/k7ygqr4BHDdWbOeAF3RQVNW3gAuS/F17fpz969/3IcAdDP6tVZLvqarH2lza/vAh5i3Ae5L8EYP7O92S5GEGN0R9y0hHhqee9kqSS4EPVNVnJlj3t1X1iyMY1qxJsozBp+7HJlh3YlX9ywiGNWuSHFhVz0xQPww4oqruGsGw5kSSU4ATq+odox7LXEryAuDwqnpw1GOZDUleBHwvg5DfVlWPj3hIgEEhSZqEl8dKkroMCklSl0EhSeoyKKRZkuTXk5y5l21OSvJjU9zuH6c/Omn69qfL56SRqqr3TaPZScDT7B+XV2s/5RGF1JHkw0nuSHJP+212kpyV5N+TfDLJ+5P8Zau/M8nvdfp6W5J7k3wxyVXt2++/Dvx2kjuT/ESSy5KcPtTm6aEuDk7yodbH+5L471fzwiMKqe9XqmpHu83H7UluAP4YeB3wFIN7KX1hin1tAI6qqmeSvLiqvprkfcDTVfVnMAihTvuVwDHAl4GPA29kcJsVaU75iUTqe1uSLwCfBY4E3gx8qqp2VNX/AX+3F319EfibJL8ETOe2E7dV1ZfabUg+CPz4NPqQ9ppBIe1Bu9fV6xncKPG1wOeB+2fQ5SnAexncPuSOJBMd0e+i/btMEuD5Q+vGfzvWb8tqXhgU0p4dAuysqm8keSVwAvAC4CeTLG7/0f/8VDpq8wlHVtUngD8AXgy8kMHpqxcNbfoQ374P1anA84bWrUxyVOvrF4DvuJWMNBcMCmnPPg4sancNfjeD00//CfwpcCvwzwxu7f3kFPo6APjrdqvzzwMXVNVXgY8APzc2mQ28n0EQ3cbg9zK+PtTHLcB5wN3Agwx+j0Gac97rSdpLSV7Y7qy7iMF/1puqyv+0td/yiELae+9Mciff/mT/4RGPR5pTHlFIsyzJe4ETx5Xfs9B/KlfPXQaFJKnLU0+SpC6DQpLUZVBIkroMCklSl0EhSer6f3enIObmCNZjAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Import pandas with the alias pd\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "# Plot the total number of tax returns by income group\n",
    "counts = data.groupby(\"agi_stub\")['N1'].sum()\n",
    "counts.plot.bar()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Modifying flat file imports\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1476, 5)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          zipcode   mars1  MARS2  NUMDEP\n",
      "agi_stub                                \n",
      "1         1439444  170320  28480   52490\n",
      "2         1439444  104000  37690   64660\n",
      "3         1439444   39160  45390   47330\n",
      "4         1439444   11670  44410   37760\n",
      "5         1439444    7820  67750   60730\n",
      "6         1439444    1210  16340   16300\n"
     ]
    }
   ],
   "source": [
    "# Create list of columns to use\n",
    "cols = ['zipcode','agi_stub','mars1','MARS2','NUMDEP']\n",
    "\n",
    "# Create data frame from csv using only selected columns\n",
    "data = pd.read_csv(\"C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/vt_tax_data_2016.csv\", usecols=cols)\n",
    "\n",
    "# View counts of dependents and tax returns by income level\n",
    "print(data.groupby(\"agi_stub\").sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "==>There is a saying that rows are cheap, but columns are expensive. Limiting datasets to only variables of interest makes them more manageable and streamlines pipelines, but make sure you aren't losing confounding data in the process.\n",
    "\n",
    "# Import a file in chunks\n",
    "When working with large files, it can be easier to load and process the data in pieces. Let's practice this workflow on the Vermont tax data.\n",
    "\n",
    "The first 500 rows have been loaded as vt_data_first500. You'll get the next 500 rows. To do this, you'll employ several keyword arguments: nrows and skiprows to get the correct records, header to tell pandas the data does not have column names, and names to supply the missing column names. You'll also want to use the list() function to get column names from vt_data_first500 to reuse.\n",
    "\n",
    "pandas has been imported as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create data frame of next 500 rows with labeled columns\n",
    "vt_data_next500 = pd.read_csv(\"vt_tax_data_2016.csv\", \n",
    "                       \t\t  nrows=500,\n",
    "                       \t\t  skiprows=500,\n",
    "                       \t\t  header=None,\n",
    "                       \t\t  names=list(vt_data_first500))\n",
    "\n",
    "# View the Vermont data frames to confirm they're different\n",
    "print(vt_data_first500.head())\n",
    "print(vt_data_next500.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling errors and missing data\n",
    "##  Specify data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "zipcode     int64\n",
      "agi_stub    int64\n",
      "mars1       int64\n",
      "MARS2       int64\n",
      "NUMDEP      int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Print the data types\n",
    "print(data.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "zipcode       object\n",
      "agi_stub    category\n",
      "mars1          int64\n",
      "MARS2          int64\n",
      "NUMDEP         int64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Create dict specifying data types for agi_stub and zipcode\n",
    "data_types = {\"agi_stub\": \"category\",\n",
    "\t\t\t  \"zipcode\": str}\n",
    "\n",
    "# Load csv using dtype to set correct data types\n",
    "data = pd.read_csv(\"C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/vt_tax_data_2016.csv\", usecols=cols, dtype=data_types)\n",
    "\n",
    "# Print data types of resulting frame\n",
    "print(data.dtypes.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Set custom NA values\n",
    "Part of data exploration and cleaning consists of checking for missing or NA values and deciding how to account for them. This is easier when missing values are treated as their own data type. and there are pandas functions that specifically target such NA values. pandas automatically treats some values as missing, but we can pass additional NA indicators with the na_values argument. Here, you'll do this to ensure that invalid ZIP codes in the Vermont tax data are coded as NA.\n",
    "\n",
    "pandas has been imported as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   zipcode  agi_stub  mars1  MARS2  NUMDEP\n",
      "0      NaN         1  85090  14170   26200\n",
      "1      NaN         2  51960  18820   32310\n",
      "2      NaN         3  19540  22650   23610\n",
      "3      NaN         4   5830  22190   18860\n",
      "4      NaN         5   3900  33800   30330\n",
      "5      NaN         6    600   8150    8140\n"
     ]
    }
   ],
   "source": [
    "# Create dict specifying that 0s in zipcode are NA values\n",
    "null_values = {\"zipcode\":0}\n",
    "# Load csv using dtype to set correct data types\n",
    "data = pd.read_csv(\"C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/vt_tax_data_2016.csv\", usecols=cols,  na_values=null_values)\n",
    "# View rows with NA ZIP codes\n",
    "print(data[data.zipcode.isna()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "==>Now that NA values are marked as such, it's possible to use NA-specific functions to do things like count missing values, as we did here, or drop records with missing values.\n",
    "# Skip bad data\n",
    "In this exercise you'll use read_csv() parameters to handle files with bad data, like records with more values than columns. By default, trying to import such files triggers a specific error, pandas.io.common.CParserError.\n",
    "\n",
    "Some lines in the Vermont tax data here are corrupted. In order to load the good lines, we need to tell pandas to skip errors. We also want pandas to warn us when it skips a line so we know the scope of data issues.\n",
    "\n",
    "pandas has been imported as pd. The exercise code will try to read the file. If there is a pandas.io.common.CParserError, the code in the except block will run."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   STATEFIPS STATE  zipcode  agi_stub      N1  mars1  MARS2  MARS4   PREP  \\\n",
      "0         50    VT        0         1  111580  85090  14170  10740  45360   \n",
      "1         50    VT        0         2   82760  51960  18820  11310  35600   \n",
      "2         50    VT        0         3   46270  19540  22650   3620  24140   \n",
      "3         50    VT        0         4   30070   5830  22190    960  16060   \n",
      "4         50    VT        0         5   39530   3900  33800    590  22500   \n",
      "\n",
      "       N2  ...  N10300  A10300  N85530  A85530  N85300  A85300  N11901  \\\n",
      "0  130630  ...   53660   50699       0       0       0       0   10820   \n",
      "1  132950  ...   74340  221146       0       0       0       0   12820   \n",
      "2   91870  ...   44860  266097       0       0       0       0   10810   \n",
      "3   71610  ...   29580  264678       0       0       0       0    7320   \n",
      "4  103710  ...   39170  731963      40      24       0       0   12500   \n",
      "\n",
      "   A11901  N11902  A11902  \n",
      "0    9734   88260  138337  \n",
      "1   20029   68760  151729  \n",
      "2   24499   34600   90583  \n",
      "3   21573   21300   67045  \n",
      "4   67761   23320  103034  \n",
      "\n",
      "[5 rows x 147 columns]\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    # Import the CSV without any keyword arguments\n",
    "    data =pd.read_csv(\"C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/vt_tax_data_2016.csv\")\n",
    "    # View first 5 records\n",
    "    print(data.head())\n",
    "except pd.io.common.CParserError:\n",
    "    print(\"Your data contained rows that could not be parsed.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   STATEFIPS STATE  zipcode  agi_stub      N1  mars1  MARS2  MARS4   PREP  \\\n",
      "0         50    VT        0         1  111580  85090  14170  10740  45360   \n",
      "1         50    VT        0         2   82760  51960  18820  11310  35600   \n",
      "\n",
      "       N2  ...  N10300  A10300  N85530  A85530  N85300  A85300  N11901  \\\n",
      "0  130630  ...   53660   50699       0       0       0       0   10820   \n",
      "1  132950  ...   74340  221146       0       0       0       0   12820   \n",
      "\n",
      "   A11901  N11902  A11902  \n",
      "0    9734   88260  138337  \n",
      "1   20029   68760  151729  \n",
      "\n",
      "[2 rows x 147 columns]\n"
     ]
    }
   ],
   "source": [
    "\"\"\"Import vt_tax_data_2016_corrupt.csv with the error_bad_lines parameter set to skip bad records.\n",
    "\n",
    "    Update the import with the warn_bad_lines parameter set to issue a warning whenever a bad record is skipped\"\"\"\n",
    "try:\n",
    "    # Set warn_bad_lines to issue warnings about bad records\n",
    "    data = pd.read_csv(\"C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/vt_tax_data_2016.csv\", \n",
    "                     error_bad_lines=False, \n",
    "                     warn_bad_lines=True)\n",
    "\n",
    "    # View first 5 records\n",
    "    print(data.head(2))\n",
    "except pd.io.common.CParserError:\n",
    "    print(\"Your data contained rows that could not be parsed.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to spreadsheets\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get data from a spreadsheet\n",
    "In this exercise, you'll create a data frame from a \"base case\" Excel file: one with a single sheet of tabular data. The fcc_survey.xlsx file here has a sample of responses from FreeCodeCamp's annual New Developer Survey. This survey asks participants about their demographics, education, work and home life, plus questions about how they're learning to code. Let's load all of it.\n",
    "\n",
    "pandas has not been pre-loaded in this exercise, so you'll need to import it yourself before using read_excel() to load the spreadsheet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   FreeCodeCamp New Developer Survey Responses, 2016        Unnamed: 1  \\\n",
      "0  Source: https://www.kaggle.com/freecodecamp/20...               NaN   \n",
      "1                                                Age  AttendedBootcamp   \n",
      "2                                                 28                 0   \n",
      "\n",
      "       Unnamed: 2         Unnamed: 3    Unnamed: 4         Unnamed: 5  \\\n",
      "0             NaN                NaN           NaN                NaN   \n",
      "1  BootcampFinish  BootcampLoanYesNo  BootcampName  BootcampRecommend   \n",
      "2             NaN                NaN           NaN                NaN   \n",
      "\n",
      "       Unnamed: 6                     Unnamed: 7            Unnamed: 8  \\\n",
      "0             NaN                            NaN                   NaN   \n",
      "1  ChildrenNumber                 CityPopulation  CodeEventConferences   \n",
      "2             NaN  between 100,000 and 1 million                   NaN   \n",
      "\n",
      "             Unnamed: 9  ...          Unnamed: 88         Unnamed: 89  \\\n",
      "0                   NaN  ...                  NaN                 NaN   \n",
      "1  CodeEventDjangoGirls  ...  ResourcePluralSight  ResourceSkillCrush   \n",
      "2                   NaN  ...                  NaN                 NaN   \n",
      "\n",
      "             Unnamed: 90        Unnamed: 91      Unnamed: 92    Unnamed: 93  \\\n",
      "0                    NaN                NaN              NaN            NaN   \n",
      "1  ResourceStackOverflow  ResourceTreehouse  ResourceUdacity  ResourceUdemy   \n",
      "2                    NaN                NaN              NaN            NaN   \n",
      "\n",
      "         Unnamed: 94                     Unnamed: 95  Unnamed: 96  \\\n",
      "0                NaN                             NaN          NaN   \n",
      "1  ResourceW3Schools                    SchoolDegree  SchoolMajor   \n",
      "2                NaN  some college credit, no degree          NaN   \n",
      "\n",
      "      Unnamed: 97  \n",
      "0             NaN  \n",
      "1  StudentDebtOwe  \n",
      "2           20000  \n",
      "\n",
      "[3 rows x 98 columns]\n"
     ]
    }
   ],
   "source": [
    "# Load pandas as pd\n",
    "import pandas as pd\n",
    "# Read spreadsheet and assign it to survey_responses\n",
    "survey_responses = pd.read_excel('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/fcc-new-coder-survey.xlsx')\n",
    "# View the head of the data frame\n",
    "print(survey_responses.head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['ExpectedEarning', 'JobApplyWhen', 'JobPref', 'JobRelocateYesNo',\n",
      "       'JobRoleInterest', 'JobWherePref'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# Create string of lettered columns to load\n",
    "col_string = \"AD, AW:BA\"\n",
    "\n",
    "# Load data with skiprows and usecols set\n",
    "survey_responses = pd.read_excel('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/fcc-new-coder-survey.xlsx', \n",
    "                                 skiprows=2, \n",
    "                                 usecols=col_string)\n",
    "\n",
    "# View the names of the columns selected\n",
    "print(survey_responses.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting data from multiple worksheets\n",
    "## Select a single sheet\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Age', 'AttendedBootcamp', 'BootcampFinish', 'BootcampLoanYesNo',\n",
      "       'BootcampName', 'BootcampRecommend', 'ChildrenNumber', 'CityPopulation',\n",
      "       'CodeEventConferences', 'CodeEventDjangoGirls', 'CodeEventGameJam',\n",
      "       'CodeEventGirlDev', 'CodeEventHackathons', 'CodeEventMeetup',\n",
      "       'CodeEventNodeSchool', 'CodeEventNone', 'CodeEventOther',\n",
      "       'CodeEventRailsBridge', 'CodeEventRailsGirls', 'CodeEventStartUpWknd',\n",
      "       'CodeEventWomenCode', 'CodeEventWorkshop', 'CommuteTime',\n",
      "       'CountryCitizen', 'CountryLive', 'EmploymentField',\n",
      "       'EmploymentFieldOther', 'EmploymentStatus', 'EmploymentStatusOther',\n",
      "       'ExpectedEarning', 'FinanciallySupporting', 'Gender', 'HasChildren',\n",
      "       'HasDebt', 'HasFinancialDependents', 'HasHighSpdInternet',\n",
      "       'HasHomeMortgage', 'HasServedInMilitary', 'HasStudentDebt',\n",
      "       'HomeMortgageOwe', 'HoursLearning', 'ID.x', 'ID.y', 'Income',\n",
      "       'IsEthnicMinority', 'IsReceiveDisabilitiesBenefits', 'IsSoftwareDev',\n",
      "       'IsUnderEmployed', 'JobApplyWhen', 'JobPref', 'JobRelocateYesNo',\n",
      "       'JobRoleInterest', 'JobWherePref', 'LanguageAtHome', 'MaritalStatus',\n",
      "       'MoneyForLearning', 'MonthsProgramming', 'NetworkID', 'Part1EndTime',\n",
      "       'Part1StartTime', 'Part2EndTime', 'Part2StartTime', 'PodcastChangeLog',\n",
      "       'PodcastCodeNewbie', 'PodcastDeveloperTea', 'PodcastDotNetRocks',\n",
      "       'PodcastJsAir', 'PodcastJSJabber', 'PodcastNone', 'PodcastOther',\n",
      "       'PodcastProgrammingThrowDown', 'PodcastRubyRogues', 'PodcastSEDaily',\n",
      "       'PodcastShopTalk', 'PodcastTalkPython', 'PodcastWebAhead',\n",
      "       'ResourceCodecademy', 'ResourceCodeWars', 'ResourceCoursera',\n",
      "       'ResourceEdX', 'ResourceEggHead', 'ResourceFCC', 'ResourceHackerRank',\n",
      "       'ResourceKhanAcademy', 'ResourceLynda', 'ResourceMDN',\n",
      "       'ResourceOdinProj', 'ResourceOther', 'ResourcePluralSight',\n",
      "       'ResourceSkillCrush', 'ResourceStackOverflow', 'ResourceTreehouse',\n",
      "       'ResourceUdacity', 'ResourceUdemy', 'ResourceW3Schools', 'SchoolDegree',\n",
      "       'SchoolMajor', 'StudentDebtOwe'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAiwAAAD4CAYAAADPR735AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAAgAElEQVR4nO3de7xVdZ3/8ddbMFEhynAcctSjhjkWhnLQmEBBeVSmecmMiJkB9Sc/x4qxCRvMX4yXfgXapFljhOYdzcxU1AkxBUFFlDsYkKnHX5lj3sIrpPj5/bG+WxanfTuHc85eR9/Px+M8ztprfS+f9d0b9ud8v2vtrYjAzMzMrMi2aXQAZmZmZrU4YTEzM7PCc8JiZmZmheeExczMzArPCYuZmZkVXs9GB2DWnfXr1y+ampoaHYaZWbeyZMmS5yJi57bUccJithWamppYvHhxo8MwM+tWJD3Z1jpeEjIzM7PCc8JiZmZmheeExczMzArPCYuZmZkVnhMWMzMzKzwnLGZmZlZ4vq3ZbCusemo9TZPvaHQYZu3SMvXIRodgVjfPsJiZmVnhOWExMzOzwnPCYmZmZoXnhMXMzMwKzwmLvU1Si6R+NcqcIGmNpLldFVeu72Ml7deOeiMk/UNnxGRmZl3DCYsBIKlHnUVPBk6LiJF1ttuRd6IdC7QpYUn9jwCcsJiZdWNOWLo5Sd+QNDFtXyjpnrR9uKRr0/YYSaskrZY0LVf3FUnnSloEDM3t317SbEmntOprCjAMmC7pAkm9JF2R2l4maWQqN17SjZJuA+aUifkWSUskPSJpQoXzmirpN5JWSvpemiE5GrhA0nJJe0s6RdLDklZIuknSDqnulZK+n2aBbgBOBb6W6g1Pxz+fH4f0e4Sk+ZJuTn1Pl+R/I2ZmBeDPYen+5gNfBy4GmoHtJG1LllgskPRBYBowGHgRmCPp2Ii4BdgRWB0RUwAkAfQGfgZcHRFX5zuKiHMlHQZMiojFkr6e9g+UtG9qe59UfCiwf0S8UCbmkyLiBUnbAw9Luikini8dlLQTcBywb0SEpPdFxJ8lzQJuj4hfpHJ/johL0/a3yWZ/fpia2QcYFRGbJJ0NvBIR30tlT64yngeRzeI8CcwGPgf8Il8gJVkTAHq8d+cqTZmZWUfxX4/d3xJgsKQ+wEZgIVniMhxYAAwB5kXEsxHxJjATOCTV3QTc1Kq9W4ErWicrFQwDrgGIiLVkb/KlhOWuCskKwERJK4AHgd2AAa2OvwRsAC6T9DngtQrtfFTSAkmrgLHAR3LHboyITXWcQ2sPRcTjqe71ZOe4hYiYERHNEdHcY4e+7ejCzMzayglLNxcRbwAtwInAA2RJykhgb2ANoCrVN5R5U78fOEJpuqWGamVeLVtBGgGMAoZGxMeAZUCvfJmUWB1ElkwdSzbTUc6VwFciYiBwTqt2yvafvEl67afzfE+++1ZlWz82M7MGcMLyzjAfmJR+LyC7ZmN5RASwCDhUUr90Ye0Y4N4qbU0BngcuqbPfsQBpKWh3YF2NOn2BFyPitbSM9PHWBST1BvpGxH8DpwOD0qGXgT65on2Ap9MS2Ngqfbau10K2RAZwDLBt7thBkvZM166MBu6rcT5mZtYFnLC8MywA+gMLI+IZsuWUBQAR8TRwJjAXWAEsjYhba7R3OtBL0vk1yl0C9EhLMjcA4yNiY406s4GeklYC55EtC7XWB7g9lbkX+Fra/zPgjHSB797At8gSsruAtVX6vA04rnTRLXApWRL3EHAwW87GLASmAquBJ4Cba5yPmZl1AWV/hJtZWq6aFBFH1Vtnu/4Dov+4izovKLNO5C8/tEaRtCQimttSxzMsZmZmVni+rdksiYh5wLwGh2FmZmU4YTHbCgN37ctiT6ubmXU6LwmZmZlZ4TlhMTMzs8JzwmJmZmaF54TFzMzMCs8Ji5mZmRWeExYzMzMrPCcsZmZmVnhOWMzMzKzwnLCYmZlZ4TlhMTMzs8JzwmJmZmaF54TFzMzMCs8Ji5mZmRWeExYzMzMrvJ6NDsCsO1v11HqaJt/R6DAKqWXqkY0OwczeQTzDYmZmZoXnhMXMzMwKzwmLmZmZFZ4TFjMzMys8JyzvEpJaJPWrUeYESWskze2quBotf86SmiVdnPaPkPQPjY7PzMwyvkvoXUBSjzqLngycFhF1JSySekbEm+2PrGtI6hERmyocbn3Oi9PvEcArwAOdHJ6ZmdXBMywFJukbkiam7Qsl3ZO2D5d0bdoeI2mVpNWSpuXqviLpXEmLgKG5/dtLmi3plFZ9TQGGAdMlXSCpl6QrUtvLJI1M5cZLulHSbcCcMjHfImmJpEckTahwXi2SzpG0NLW/b9q/U6q/UtKDkvZP+8+WdLmkeZIez41Jk6S1kq5KdX4haYdcH1Mk3QecUG6cypzzCEm3S2oCTgW+Jmm5pOFtfOrMzKyDOWEptvlA6c2yGegtaVuyN9kFkj4ITAMOAwYBQyQdm8rvCKyOiIMj4r60rzdwG3BdRFya7ygiziWbXRgbEWcAX077BwJjgKsk9UrFhwLjIuKwMjGfFBGDU7wTJX2gwrk9FxEHAj8GJqV95wDLImJ/4JvA1bny+wKfAg4C/iONA8CHgRmpzkvAabk6GyJiGNk4/tU4lTnn0li0ANOBCyNiUEQsyAcuaYKkxZIWb3ptfYXTMzOzjuSEpdiWAIMl9QE2AgvJEoHhwAJgCDAvIp5NSzMzgUNS3U3ATa3auxW4IiKuprZhwDUAEbEWeBLYJx27KyJeqFBvoqQVwIPAbsCACuV+mTvHpjJ93gN8QFLfdOyOiNgYEc8BfwJ2Sft/HxH3p+1rUxslN6Tf1capzSJiRkQ0R0Rzjx361q5gZmZbzQlLgUXEG0ALcCLZtRQLgJHA3sAaQFWqbyhz3cb9wBGSqtUrqVbm1bIVpBHAKGBoRHwMWAb0KleWLAGDLLEqXUtVrs9oVb51ndiy+BaPS3HWc75mZlZgTliKbz7Zksl8soTlVGB5RASwCDhUUr90Ye0Y4N4qbU0BngcuqbPfsQCS9gF2B9bVqNMXeDEiXkvXpXy8jn4q9TmCbNnopRp1dpdUukZnDHBfmTJtHSeAl4E+9QZuZmadywlL8S0A+gMLI+IZYEPaR0Q8DZwJzAVWAEsj4tYa7Z0O9JJ0fo1ylwA9JK0iW1oZHxEba9SZDfSUtBI4j2xZqC3OBppT/anAuDrqrAHGpTo7kV0Ts4V2jtNtwHG+6NbMrBiU/aFu1v2ku3luj4iPNiqG7foPiP7jLmpU94XmLz80s0okLYmI5rbU8QyLmZmZFZ4/OM66rXT7ccNmV8zMrOs4YTHbCgN37ctiL32YmXU6LwmZmZlZ4TlhMTMzs8JzwmJmZmaF54TFzMzMCs8Ji5mZmRWeExYzMzMrPCcsZmZmVnhOWMzMzKzwnLCYmZlZ4TlhMTMzs8JzwmJmZmaF54TFzMzMCs8Ji5mZmRWeExYzMzMrvJ6NDsCsO1v11HqaJt/R6DCsg7RMPbLRIZhZBZ5hMTMzs8JzwmJmZmaF54TFzMzMCs8Ji5mZmRVe4RMWSS2S+tUoc4KkNZLmdlVc7SFpnqTmtP3NVsce2Ip2x0v6YO7xZZL2a3+kFfv4UUe22ZkkDZL0mdzjoyVNbmRMZmbWfoVOWCT1qLPoycBpETGyznaLcHfUFglLRPzDVrQ1Hng7YYmI/xURv9mK9rpEG57fSvWrPY+DgLcTloiYFRFTt6Y/MzNrnE5JWCR9Q9LEtH2hpHvS9uGSrk3bYyStkrRa0rRc3VcknStpETA0t397SbMlndKqrynAMGC6pAsk9ZJ0RWp7maSRqdx4STdKug2YUybmWyQtkfSIpAkVzqtF0nckLZS0WNKBku6U9JikU1OZEZJuz9X5kaTxrdqZCmwvabmkmaXzztWfJ+kXktZKmilJpXOV9HAasxnKfB5oBmam9rZvNZNTbZz/r6QVkh6UtEva/1lJi9LY/bq0vxJJvXPjvVLS8W15ftOYTpP0UPr5UCq3h6S7U5t3S9o97b9S0vfTbNo0SQdJeiDF+4CkD0t6D3AuMDqNyej8DFGNti9O7TyextbMzAqgs2ZY5gPD03Yz0FvStmSJxQJlyxfTgMPI/hIeIunYVH5HYHVEHBwR96V9vYHbgOsi4tJ8RxFxLrAYGBsRZwBfTvsHAmOAqyT1SsWHAuMi4rAyMZ8UEYNTvBMlfaDCuf0+IoYCC4Argc8DHyd7g6xLREwGXo+IQRExtkyRA4DTgf2AvYBPpP0/ioghEfFRYHvgqIj4Re78B0XE66VG6hjnByPiY2TPVykRvA/4eEQcAPwM+EaN0/kWsD4iBkbE/sA97Xh+X4qIg4AfAReVzhW4OrU5E7g41+c+wKiI+DqwFjgkxTsF+E5E/CVt35DG5IZWMVdruz/Z6/QooOyMjKQJKWFdvOm19TWGx8zMOkJnJSxLgMGS+gAbgYVkicBwsjf6IcC8iHg2It4ke9M4JNXdBNzUqr1bgSsi4uo6+h4GXAMQEWuBJ8ne4ADuiogXKtSbKGkF8CCwGzCgQrlZ6fcqYFFEvBwRzwIbJL2vjvjq8VBE/CEi3gKWA01p/8g0+7GKLBn4SI12qo3zX4DSTNCSXB9/B9yZ+jijjj5GAf9VehARL9bot9zze33ud2lWbShwXdq+hux5LbkxIjal7b7AjZJWAxfWEW+ttm+JiLfSklrZ2aWImBERzRHR3GOHvnV0Z2ZmW6tTEpaIeANoAU4EHiBLUkYCewNrAFWpviH3ZlRyP3BEaWmkhmplXi1bQRpB9sY7NM04LAN6lStLloABvJXbLj3uCbzJluNaqZ1q8u1uAnqmWaJLgM+n2aNL62i72li8ERGR7yNt/5BsJmcg8L/r7CPK7Kuk3PMbFbYrlck/j+cBc9Os02dp33jn286PfT2vNzMz6wKdedHtfGBS+r0AOBVYnt4kFwGHSuqn7MLLMcC9VdqaAjxP9oZdT79jASTtA+wOrKtRpy/wYkS8JmlfsiWe9noS2E/SdpL6AodXKPdGWiarV+mN+DlJvcmWokpeBvqUqdPWcYZsLJ5K2+PqiGsO8JXSA0nvb0e/o3O/F6btB4Avpu2xZEtVteIdn9tfaUza0raZmRVEZyYsC8iuB1gYEc8AG9I+IuJp4ExgLrACWBoRt9Zo73Sgl6Tza5S7BOiRljRuAMZHxMYadWaTzWKsJPuL/cEa5SuKiN8DPwdWki2FLKtQdAawsnTRbR3t/plsVmUVcAvwcO7wlWQXHS+XtH2uTnvG+WyyJZYFwHN1hPZt4P3p4toVwMh29Ltdugj3X4GvpX0TgRPTc/JP6Vg55wPflXQ/kL/raC5Z4rhc0uhWdept28zMCkKbVwXMup6kFqA5IupJjgpnu/4Dov+4i2oXtG7BX35o1jUkLYmI5rbUKfTnsJiZmZnB5gstzRoiIpoaHYOZmRWfExazrTBw174s9jKCmVmn85KQmZmZFZ4TFjMzMys8JyxmZmZWeE5YzMzMrPCcsJiZmVnhOWExMzOzwquYsEi6Jv32x5abmZlZQ1WbYRksaQ/gJEnvl7RT/qerAjQzMzOr9sFx08m+FHAvYAmg3LFI+83MzMw6XcUZloi4OCL+Hrg8IvaKiD1zP05WzMzMrMvUvOg2Iv5F0jBJJwJI6idpz84PzczMzCxTM2GR9B/AvwNnpl3vAa7tzKDMzMzM8uq5rfk44GjgVYCI+CPQpzODMjMzM8urJ2H5S0QE2YW2SNqxc0MyMzMz21K1u4RKfi7pJ8D7JJ0CnARc2rlhmXUPq55aT9PkOxodhplZl2qZemSX91k1YZEk4AZgX+Al4MPAlIi4qwtiMzMzMwNqJCwREZJuiYjBgJMUMzMza4h6rmF5UNKQTo/EzMzMrIJ6rmEZCZwqqYXsTiGRTb7s35mBmZmZmZXUM8NyBNnH8B8GfBY4Kv0uBEktkvrVKHOCpDWS5nZVXFtL0tmSJqXtcyWN6sK+/1vS+7ayjRGSbu+omMzM7N2t4gyLpL8Bvgl8CFgFfDciXuqqwOohqUedRU8GTouIuhIWST0j4s32R9axImJKF/f3ma7sz8zMrJZqMyxXky0B/RDoDVzcUZ1K+oakiWn7Qkn3pO3DJV2btsdIWiVptaRpubqvpBmHRcDQ3P7tJc1Ot17n+5oCDAOmS7pAUi9JV6S2l0kamcqNl3SjpNuAOWVivkXSEkmPSJpQ4bxaJH1H0kJJiyUdKOlOSY9JOjVX7gxJD0taKemc3P6zJK2T9GuyO7JK+6+U9PlcH/3SdrOkeWn7bElXSZqTynxO0vnpPGdL2rZMvP0lzZe0PI3z8Hwfkk5Nx5ZLeqI0QyXpk+kcl6Yx6532f1rSWkn3AZ+rMEY9JH0vxbVS0lfT/sPT87FK0uWStqt3TNNsznxJN0v6jaTpkrZJx36c6j3SaqxbJJ2TzmGVpH0lbSPpUUk7pzLbSPqdaszgmZlZ56uWsPxtRJwVEXdGxFeBjrxmZT4wPG03A73TG+owYIGkDwLTyJahBgFDJB2byu8IrI6IgyPivrSvN3AbcF1EbPEZMRFxLrAYGBsRZwBfTvsHAmOAqyT1SsWHAuMi4rAyMZ+U7pZqBiZK+kCFc/t9RAwFFgBXAp8HPg6cC9mbPTAAOCid22BJh0gaDHwROIDszb49FzrvDRwJHEP29Qlz03m+nva39iXgzogYBHwMWJ4/GBHT07EhwB+A76c37/8DjIqIA8nG9t/SGF5Ktlw4HPjbCjFOAPYEDkjXQc1Mda8ERqd4ewL/kqtTdUyTg4CvAwPTOJQSprMiopns9XuopPzr+Ll0Dj8GJkXEW2ncxqbjo4AVEfFc/gQkTUhJ0OJNr62vcJpmZtaRqiUskvR+STtJ2gno0erx1lhC9kbdB9gILCRLBIaTvSkNAeZFxLNpaWYmcEiquwm4qVV7twJXRMTVdfQ9DLgGICLWAk8C+6Rjd0XECxXqTZS0AngQ2I0s6ShnVvq9ClgUES9HxLPABmXXhXwy/SwDlpJ9xs2AdO43R8Rraelt1l83XdOvIuKN1HcPYHYulqYy5R8GTpR0NjAwIl6u0O4PgHsi4jayRGE/4H5Jy4FxwB7pPJ6IiEfTJyNX+r6pUcD00pJbGu8Pp7q/TWWuYvPzDbXHFOChiHg8IjYB15M9zwBfkLSUbLw/kmIv+WX6vYTN43M58M9p+yTgitYnEBEzIqI5Ipp77NC3wmmamVlHqpaw9CX7j7z0816yN9glZH9Vt1t6U20BTgQeIEtSRpL9ZbyG7E6kSjakN6W8+4EjJFWrV1KtzKtlK0gjyN5oh0bEx8je/HqVK0uWgAG8ldsuPe6Z+v9uRAxKPx+KiJ+mMlFH/G+y+XlrHcNGgDRT8EZKHN7uW9LBuSWeoyNiPlli8BRwjaR/btUeksaTJSSl5RSRJXal+PeLiJPbEL/KlKv1vNUa03J9h7JvFZ8EHJ5mc+5gyzErtbWp1E5E/B54RtJhwMHAr2rEZmZmXaBiwhIRTRGxV0TsWeZnrw7oez7Zm8l8soTlVGB5epNdRDZ930/ZhbVjgHurtDUFeB64pM5+xwJI2gfYHVhXo05f4MWIeE3SvmSzDO11J3BS7rqPXZVd4DwfOE7ZtTh9qHwnVgswOG0f35aOI2JRLtGYJWkP4E9pGe2nwIH58mmZahLwjykJgmyG6ROSPpTK7JDGcS2wp6S9U7kxFcKYQ3abfM9Uf6dUt6nUJvBPVH++yzlI0p7p2pXRwH1kSfarwHpJu5Dd8VaPy8hmiH5eJjk2M7MGqOe2ZpRdwPl9Sf+Zu5Zkay0A+gMLI+IZYEPaR0Q8DZwJzAVWAEsj4tYa7Z0O9JJ0fo1yl5Atb60i+9qB8RGxsUad2WQzFCuB88jetNslIuYA1wELUwy/APpExNIUz3KyJa8FFZo4B/iBpAVkMwNbYwSwXNIysuTnB62OfwXYCZibZmUuS0sx44Hr03g8COwbERvIrk+5I110+2SFPi8D/h+wMi2xfSnVPRG4MY3JW8D0Np7LQmAqsBp4gmx5bQXZbNgjZEs999fZ1iyy66L+ajnIzMwaQ5tXDSoUkC4hu7X5+rRrNPBYRHy5k2Mzq0taspsUEUd1UHvNwIURMbxW2e36D4j+4y7qiG7NzLqNrf3yQ0lL0g0Rdavnk24PBT5auh5C0lVkFz+aveNImkx2h9LYWmXNzKzr1LMktI7sOo+S3YCVnROOWdtFxLyOml2JiKkRsUfulnkzMyuAap90exvZnRd9gTWSHkqHDiK7s8fsXW/grn1ZvJVTo2ZmVlu1JaHvdVkUZmZmZlVUTFgi4u3bStMtoaVPXn0oIv7U2YGZmZmZldS8hkXSF4CHgBOALwCLlL7XxszMzKwr1HOX0FnAkNKsSvpiuF+TfX6ImZmZWaer5y6hbVotAT1fZz0zMzOzDlHPDMtsSXey+YPjvoi/X8XMzMy6UM2EJSLOkPQ54BNkX1I3PSJu6fTIzMzMzJJqn8PyMpu/ATf/bbqnSNoAPAacFRF3d2J8ZmZmZlVva+5T6Vj6BuWPAjPTbzMzM7NO066LZyNiU/om3B92cDxmZmZmf2Wr7vaJiJ90VCBmZmZmlfj2ZDMzMys8JyxmZmZWeE5YzMzMrPDq+eA4M6tg1VPraZp8R4e11zL1yA5ry8zsncQzLGZmZlZ4TljMzMys8JywmJmZWeE5YTEzM7PCc8LSTUhqkdSvRpkTJK2RNLer4nqnkvTNRsdgZmabOWHpBtJ3N9XjZOC0iBhZZ7uFv0usgTE6YTEzKxAnLJ1I0jckTUzbF0q6J20fLunatD1G0ipJqyVNy9V9RdK5khYBQ3P7t5c0W9IprfqaAgwDpku6QFIvSVektpdJGpnKjZd0o6TbgDllYr5F0hJJj0iaUOG8PiNpraT7JF0s6fa0f6dUf6WkByXtL2mbNDv0vlz930naRdLOkm6S9HD6+UQ6frakGZLmAFenx5dLmifp8dyYNqU4LkvjN1PSKEn3S3pU0kGp3I6p/sNpLI7JjcUv03g+Kun8tH8qsL2k5ZJmtuU5NzOzzuGEpXPNB4an7Wagt6RtyRKLBZI+CEwDDgMGAUMkHZvK7wisjoiDI+K+tK83cBtwXURcmu8oIs4FFgNjI+IM4Mtp/0BgDHCVpF6p+FBgXEQcVibmkyJicIp3oqQP5A+mNn4CHBERw4Cdc4fPAZZFxP5kMxRXR8RbwK3Acan+wUBLRDwD/AC4MCKGAMcDl+XaGgwcExFfSo/3BT4FHAT8RxpHgA+ldvZPZb6UxncSm2dJzgLuSf2MBC6QtGM6NggYDQwERkvaLSImA69HxKCIGNt6gCRNkLRY0uJNr60vM4RmZtbRnLB0riXAYEl9gI3AQrJEYDiwABgCzIuIZyPiTWAmcEiquwm4qVV7twJXRMTVdfQ9DLgGICLWAk8C+6Rjd0XECxXqTZS0AngQ2A0Y0Or4vsDjEfFEenx9hT7vAT4gqS9wA1lSAPDF9BhgFPAjScuBWcB701gBzIqI13Nt3xERGyPiOeBPwC5p/xMRsSolRo8Ad0dEAKuAplTmk8Dk1M88oBewezp2d0Ssj4gNwG+APSqMy9siYkZENEdEc48d+tYqbmZmHaDw1zB0ZxHxhqQW4ETgAWAl2V/4ewNr2JxAlLMhIja12nc/cISk69KbcjWqcuzVshWkEWRJxNCIeE3SPLI393rbLXcsyBK1D0naGTgW+HY6tk3qK5+YIKlcjBtz25vY/NrN738r9/itXBkBx0fEulb9HFylXTMzKxDPsHS++WTLE/PJZlVOBZanhGMRcKikfunC2jHAvVXamgI8D1xSZ79jASTtQzajsK5qDegLvJiSlX2Bj5cpsxbYS1JTejw6dyzf5wjguYh4KZ3rzcD3gTUR8XwqPwf4SqmypEF1nFd73Al8VSkTknRAHXXeyC07mZlZgzlh6XwLgP7AwnTdxoa0j4h4GjgTmAusAJZGxK012jsd6FW6QLSKS4AeklaRLcGMj4iNNerMBnpKWgmcR7YstIU0G3IaMFvSfcAzQOlCjrOB5lR/KjAuV/UG4B/ZvBwEMLFUXtJvyJK5znAesC2wUtLq9LiWGam8L7o1MysA1V5ZMNuSpN4R8Uqasfgv4NGIuLDRcTXCdv0HRP9xF3VYe/7yQzN7N5C0JCKa21LHMyzWHqekC1gfIVtG+kmD4zEzs3c4X2BobZZmU96VMypmZtYYTljMtsLAXfuy2Ms4ZmadzktCZmZmVnhOWMzMzKzwnLCYmZlZ4TlhMTMzs8JzwmJmZmaF54TFzMzMCs8Ji5mZmRWeExYzMzMrPCcsZmZmVnhOWMzMzKzwnLCYmZlZ4TlhMTMzs8JzwmJmZmaF54TFzMzMCq9nowMw685WPbWepsl3NDoMs3eUlqlHNjoEKyDPsJiZmVnhOWExMzOzwnPCYmZmZoXnhMXMzMwKzwlLJ5N0uqQd2lFvvKQPdkZMRSDpSkmf74B2zpU0qiNiMjOz4nLC0vlOB9qUsEjqAYwHuixhkdQt7xiLiCkR8etGx2FmZp3LCUsHkbSjpDskrZC0WtJoSRPJko65kuamcj+WtFjSI5LOydVvkTRF0n3AGKAZmClpuaTtc+X2lrQ093iApCVp+3BJyyStknS5pO1ybfdL282S5qXtsyXNkDQHuLrV+UjSBelcVkkanfZfIunotH2zpMvT9smSvi2pSdIaSZemc5yTj7+VUZIWSPqtpKNSO+Ml/SgXx+2SRkjqkWZlSvF8LR1/e6Ymnec5kpamMvvmnpvLJT2cxueYtP8jkh5KY7wyjeVfPY/1vwrMzKyzOGHpOJ8G/hgRH4uIjwKzI+Ji4I/AyIgYmcqdFRHNwP7AoZL2z7WxISKGRcS1wGJgbEQMiojXSwUi4jFgvaRBadeJwJWSegFXAqMjYiDZZ+z8Sx1xDwaOiYgvtdr/OWAQ8DFgFHCBpP7AfGB4KrMrsF/aHgYsSNsDgP+KiI8AfwaOr9B3E3AocCQwPZ1DJYOAXSPio+n8rqhQ7rmIOBD4MTAp7TsLuCcihgAj07nsCJwK/CAiBpEliH+gzPPYugNJE1LSuXjTa+urhGxmZh3FCUvHWUU2YzBN0pnMWnUAAAqUSURBVPCIqPRO9oU0Q7IM+Aib3/ABbqizr8uAE9PS0WjgOuDDwBMR8dtU5irgkDrampVPiHKGAddHxKaIeAa4FxhClpQMl7Qf8BvgmZTIDAUeSHWfiIjlaXsJWWJSzs8j4q2IeBR4HNi3SpyPA3tJ+qGkTwMvVSj3yzL9fhKYLGk5MA/oBewOLAS+KenfgT3SONR8HiNiRkQ0R0Rzjx36VgnZzMw6ihOWDpIShcFkb3jflTSldRlJe5L91X94ROwP3EH25lnyap3d3QQcARwFLImI5wFVKf8mm5/r1rMYlfos215EPAW8n2wmYj5ZAvMF4JWIeDkV25irsonKn6gcZR7nY3073oh4kWy2Zx7wZbKkrZxS3/l+BRyfZqsGRcTuEbEmIq4DjgZeB+6UdFg9z6OZmXU9JywdJN3R81pazvkecGA69DLQJ22/lyxBWC9pF7Kko5J8vS1ExAbgTrJlj9LSyFqgSdKH0uN/IpsVAWghexOGysszrc0HRqdrR3Ymm615KB1bSHYxcSlhmcTm5aC2OEHSNpL2BvYC1qVYB6X9uwEHAaRrcLaJiJuAb7F5fOtxJ/BVSUptHZB+7wU8npbuZgH7V3kezcysgbrlnSEFNZDs2oi3gDfYfP3IDOBXkp6OiJGSlgGPkC1x3F+lvSvJrut4HRhaZtlmJtl1JnMgS2IknQjcqOyOn4eB6ansOcBPJX0TWFTn+dxMtsyzgmzm4xsR8T/p2ALgkxHxO0lPAjvRvoRlHVlStQtwajqH+4EnyGY4VgOlC4x3Ba6QVEqyz2xDP+cBFwErU9LSQjY7NRr4R0lvAP8DnEu27FXueTQzswZSROtZeesOJE0C+kbEtxody7vZdv0HRP9xFzU6DLN3FH/54TufpCXpBpS6eYalG5J0M7A3cFijYzEzM+sKTli6oYg4rtExmJmZdSUnLGZbYeCufVns6Wszs07nu4TMzMys8JywmJmZWeE5YTEzM7PCc8JiZmZmheeExczMzArPCYuZmZkVnhMWMzMzKzwnLGZmZlZ4TljMzMys8JywmJmZWeE5YTEzM7PCc8JiZmZmheeExczMzArPCYuZmZkVnhMWMzMzK7yejQ7ArDtb9dR6mibf0egwzLqtlqlHNjoE6yY8w2JmZmaF54TFzMzMCs8Ji5mZmRWeExbrUpImSlojaeZWtjNC0u0dFZeZmRWbL7q1rnYacEREPFHaIalnRLzZwJjMzKzgPMNiXUbSdGAvYJak9ZJmSJoDXC1pZ0k3SXo4/Xwi1dlR0uVp3zJJx5Rp9yBJD6TjD0j6cNo/XtIvJc2W9Kik83N1Pi1pqaQVku6uty8zM2sMz7BYl4mIUyV9GhgJfAX4LDAsIl6XdB1wYUTcJ2l34E7g74GzgHsi4iRJ7wMekvTrVk2vBQ6JiDcljQK+Axyfjg0CDgA2Ausk/RDYAFya6jwhaadUtmxfEfFqvjNJE4AJAD3eu3OHjY+ZmVXmhMUaaVZEvJ62RwH7SSode6+kPsAngaMlTUr7ewG7t2qnL3CVpAFAANvmjt0dEesBJP0G2AN4PzC/tCwVES+kspX6WpPvLCJmADMAtus/INpz4mZm1jZOWKyR8jMX2wBDcwkMAMoymOMjYl2r/bvkHp4HzI2I4yQ1AfNyxzbmtjeRveZFlti0VrYvMzNrPF/DYkUxh2yZCABJg9LmncBXU+KCpAPK1O0LPJW2x9fR10LgUEl7pjZLS0L19GVmZg3ghMWKYiLQLGllWro5Ne0/j2yJZ6Wk1elxa+cD35V0P9CjVkcR8SzZNSi/lLQCuKENfZmZWQMowkvwZu21Xf8B0X/cRY0Ow6zb8ncJvTtJWhIRzW2p4xkWMzMzKzwnLGZmZlZ4vkvIbCsM3LUviz2lbWbW6TzDYmZmZoXnhMXMzMwKzwmLmZmZFZ4TFjMzMys8JyxmZmZWeE5YzMzMrPCcsJiZmVnh+aP5zbaCpJeB7vDtzv2A5xodRA3dIUboHnF2hxihe8TZHWKE7hFnPsY9ImLntlT2B8eZbZ11bf0+jEaQtLjocXaHGKF7xNkdYoTuEWd3iBG6R5xbG6OXhMzMzKzwnLCYmZlZ4TlhMds6MxodQJ26Q5zdIUboHnF2hxihe8TZHWKE7hHnVsXoi27NzMys8DzDYmZmZoXnhMXMzMwKzwmLWTtJ+rSkdZJ+J2lyo+MBkLSbpLmS1kh6RNK/pv1nS3pK0vL085kCxNoiaVWKZ3Hat5OkuyQ9mn6/v4HxfTg3XsslvSTp9CKMpaTLJf1J0urcvopjJ+nM9DpdJ+lTDYzxAklrJa2UdLOk96X9TZJez43p9K6IsUqcFZ/jAo3lDbn4WiQtT/sbMpZV/u/puNdlRPjHP/5p4w/QA3gM2At4D7AC2K8AcfUHDkzbfYDfAvsBZwOTGh1fq1hbgH6t9p0PTE7bk4FpjY4z93z/D7BHEcYSOAQ4EFhda+zS878C2A7YM71uezQoxk8CPdP2tFyMTflyBRjLss9xkcay1fH/BKY0ciyr/N/TYa9Lz7CYtc9BwO8i4vGI+AvwM+CYBsdERDwdEUvT9svAGmDXxkbVJscAV6Xtq4BjGxhL3uHAYxHxZKMDAYiI+cALrXZXGrtjgJ9FxMaIeAL4Hdnrt8tjjIg5EfFmevgg8HedHUctFcayksKMZYkkAV8Aru/sOKqp8n9Ph70unbCYtc+uwO9zj/9AwRIDSU3AAcCitOsraSr+8kYuteQEMEfSEkkT0r5dIuJpyP4DBP6mYdFt6Yts+YZQtLGEymNX1NfqScCvco/3lLRM0r2ShjcqqJxyz3ERx3I48ExEPJrb19CxbPV/T4e9Lp2wmLWPyuwrzGcESOoN3AScHhEvAT8G9gYGAU+TTSE32ici4kDgCODLkg5pdEDlSHoPcDRwY9pVxLGspnCvVUlnAW8CM9Oup4HdI+IA4N+A6yS9t1HxUfk5LtxYAmPYMplu6FiW+b+nYtEy+6qOpRMWs/b5A7Bb7vHfAX9sUCxbkLQt2X8YMyPilwAR8UxEbIqIt4BL6YJp7Foi4o/p95+Am8liekZSf4D0+0+Ni/BtRwBLI+IZKOZYJpXGrlCvVUnjgKOAsZEuZkjLAs+n7SVk1zPs06gYqzzHRRvLnsDngBtK+xo5luX+76EDX5dOWMza52FggKQ901/gXwRmNTim0nr2T4E1EfH93P7+uWLHAatb1+1KknaU1Ke0TXYx5mqyMRyXio0Dbm1MhFvY4i/Yoo1lTqWxmwV8UdJ2kvYEBgAPNSA+JH0a+Hfg6Ih4Lbd/Z0k90vZeKcbHGxFjiqHSc1yYsUxGAWsj4g+lHY0ay0r/99CRr8uuvpLYP/55p/wAnyG7Ev4x4KxGx5NiGkY2rboSWJ5+PgNcA6xK+2cB/Rsc515kdwisAB4pjR/wAeBu4NH0e6cGx7kD8DzQN7ev4WNJlkA9DbxB9pfqydXGDjgrvU7XAUc0MMbfkV23UHptTk9lj0+vgxXAUuCzDR7Lis9xUcYy7b8SOLVV2YaMZZX/ezrsdemP5jczM7PC85KQmZmZFZ4TFjMzMys8JyxmZmZWeE5YzMzMrPCcsJiZmVnhOWExMzOzwnPCYmZmZoX3/wFun2SuquF51gAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create df from second worksheet by referencing its position\n",
    "responses_2017 = pd.read_excel('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/fcc-new-coder-survey.xlsx',\n",
    "                               skiprows=2,\n",
    "                               sheet_name=1)\n",
    "print(responses_2017.columns)\n",
    "# Graph where people would like to get a developer job\n",
    "job_prefs = responses_2017.groupby(\"JobPref\").JobPref.count()\n",
    "job_prefs.plot.barh()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'dict'>\n"
     ]
    }
   ],
   "source": [
    "# Load both the 2016 and 2017 sheets by name\n",
    "all_survey_data = pd.read_excel('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/fcc-new-coder-survey.xlsx',\n",
    "                               skiprows=2,\n",
    "                                sheet_name=['2016', '2017'])\n",
    "\n",
    "# View the data type of all_survey_data\n",
    "print(type(all_survey_data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dict_keys(['2016', '2017'])\n"
     ]
    }
   ],
   "source": [
    "# Load all sheets in the Excel file\n",
    "all_survey_data = pd.read_excel('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/fcc-new-coder-survey.xlsx',\n",
    "                               skiprows=2,\n",
    "                                sheet_name=None)\n",
    "\n",
    "# View the sheet names in all_survey_data\n",
    "print(all_survey_data.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Adding 1000 rows\n",
      "Adding 1000 rows\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAi4AAAD4CAYAAADLsm3EAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAAgAElEQVR4nOzde5xd0/3/8dfbUIlbFOGbamtUgyISctGQEEWrqFvThtKiJeXbFu0XjV9bQm+UtupWUiWoW5WQihKXRBKXJJOLTOLWlvgqvlSrIUhI8vn9sdeRnZNzzpyZzMk4mffz8ZjH7LP22mt/1j7D/mStdfZRRGBmZmZWD9bq6ADMzMzMquXExczMzOqGExczMzOrG05czMzMrG44cTEzM7O6sXZHB2C2Jttss82isbGxo8MwM6srM2bMeC0iupfa58TFrIYaGxtpamrq6DDMzOqKpOfL7fNUkZmZmdUNJy5mZmZWN5y4mJmZWd1w4mJmZmZ1w4tzzWqo+cUFNI4Y12K9+ecduBqiMTOrfx5xMTMzs7rhxMVKktQoaW5R2UhJp7WxvSGS7iqzb76kzVrR1qmS1mtLHKtK0mhJQzvi3GZm5sTF6tOpwGpPXCR5atXMrIM5cbE2kTRR0vmSpkl6RtLgVN4oabKkmeln99xhG0kaI+kJSVdIWunvT9LRqc3Zkq6U1FC0/2TgI8AESRNS2ZGSmiXNlXR+iTYHSLo9bR8i6R1JH5LURdKzqbyPpMckzUkxfjjXz59Jegg4pajdH6cRGP93ZGa2mvh/uLYq1o6IAWQjIGensleB/SJiV2AYcHGu/gDgf4BewDbA4fnGJH0qHbNHRPQBlgJH5etExMXAS8DeEbG3pI8A5wOfAfoA/SUdWhTnTGCXtD0YmAv0B3YDpqby64DvR8TOQHOuPwAbR8ReEfHLXKy/ADYHjouIZUX9GC6pSVLT0rcXlLhsZmbWVh76tnKiivLb0+8ZQGPaXge4VFIh8dg2V39aRBRGOG4CBgF/yu3fB+gLTJcE0JUsEaqkPzAxIv6Z2r0B2BO44/2AI5ZI+ltKjAYAv0p1GoDJkrqRJScPpUOuBW7NneOWonP+CJgaEcNLBRQRo4BRAOv26FnuOpqZWRs4cbFy/gV8uKhsE+C53OvF6fdSlv8tfRd4BehNNqK3KFe/+CZe/FrAtRFxZiviVJX1JgOfB94D7gdGkyUu1Sw2fqvo9XSgr6RNIuLfVZ7fzMzagaeKrKSIWAi8LGkfAEmbAPsDU1o4tBvwcpo++SpZclAwQNLWaU3IsBJtPQAMlbR54ZyStipxjjeBDdP2VGAvSZul9TBHAg+VOGYS2ZTWo2l0ZlNge2BeRCwAXi+s00lxl2qj4B7gPGCcpA0r1DMzs3bmERer5GvAZZIKazvOiYi/t3DM5cBtkr4ETGDF0YpHyW74vcgSiTH5AyPiCUk/BMan5OY94FtA8beEjgL+IunltM7lzHQuAXdHxJ0l4poKbJHOCzAHeDUiCqM+xwBXpI9ZPwscV6mTEXFrSlrGSjogIt6pVN/MzNqHlv9/28za27o9ekaPYy5qsZ6fnGtmtpykGRHRr9Q+j7iY1VCvLbvR5KTEzKzdeI2LmZmZ1Q0nLmZmZlY3nLiYmZlZ3XDiYmZmZnXDiYuZmZnVDScuZmZmVjecuJiZmVndcOJiZmZmdcOJi5mZmdUNJy5mZmZWN/zIf7Maan5xAY0jxnV0GBX5e5LMrJ54xMXMzMzqhhOXGpD0A0nzJM2RNFvSbi3UHy1paNoenI6dLanraop3YY3bf79/ReXbp37OkrRNO5xnvqTNVrUdMzP74PJUUTuTNBA4CNg1IhanG+mHWtHEUcCFEXFNTQL8YDkUuDMizs4XShKgiFjWMWG1jqSGiFja0XGYmXUGHnFpfz2A1yJiMUBEvBYRLwFI6ivpIUkzJN0rqUf+QEnHA18GzpJ0Q3HDko6WNC2NUlwpqSGVL5R0fmr3fkkDJE2U9Kykg1OdYyXdKekeSU9LOrtE+5J0gaS5kpolDUvl10s6JFfvBkkHS2pI9aen0aVv5tq5VNITksYBm5c41wHAqcDxkiZIapT0pKTLgZnAxySdnmv7nJauQ1H7d6TrMU/S8Fz5Qkk/lfS4pMckbZHKt5A0JpU/Lmn3Kq75uZKmAgNL/B2YmVkNOHFpf+PJbrrPSLpc0l4AktYBLgGGRkRf4Grgp/kDI+IqYCxwekQcld8n6VPAMGCPiOgDLCUbnQFYH5iY2n0T+AmwH3AYcG6umQHpmD7AlyT1K4r98LSvN7AvcEFKrq4CjktxdAN2B+4GvgEsiIj+QH/gBElbp/NuB/QCTkj1VxARdwNXAL+OiL1T8XbAdRGxS9rumWLuA/SVtGcL1yHv6+l69ANOlrRp7lo9FhG9gUkpPoCLgYdS+a7AvCqu+dyI2C0ipuRPLGm4pCZJTUvfXlAiNDMzaytPFbWziFgoqS8wGNgbuEXSCKAJ2Am4L5sJoQF4uRVN7wP0Baan47sCr6Z97wL3pO1mYHFEvCepGWjMtXFfRPwLQNLtwKAUV8Eg4KY07fGKpIeA/hExVtJlkjYnS25ui4glkj4L7Jxbv9KNLNnYM9fOS5IerLKPz0fEY2n7s+lnVnq9QWp75wrXIe9kSYel7Y+lY/+VrtVdqXwGWYIH8BngawAp7gWSvlrhXEuB20p1IiJGAaMA1u3RM6rrupmZVcOJSw2kG99EYGJKHo4hu0nOi4iqphUkfQz4c3p5BSDg2og4s0T19yKicINcBhSmqZZJyr/HxTfR4teqENL1ZKMNRwBfz9X/TkTcWxT7ASXarsZbRbH8PCKuLGr7O5S/DoU6Q8hGjAZGxNuSJgJd0u78tVpK5f8GKl3zRV7XYma2+nmqqJ1J2k5Sz1xRH+B54Gmgu7LFu0haR9KO5dqJiBciok/6uQJ4ABiaRj2QtImkrVoZ3n7puK5kC2MfLto/CRiW1q50Jxs5mZb2jSZbk0JEzEtl9wInpWkwJG0raf3UzhGpnR5kI0+tdS/wdUkbpLa3TH2v5jp0A15PScv2wKerON8DwEmpzQZJG1V5LjMzW4084tL+NgAukbQxsAT4GzA8It5NUyoXp3UiawMXAfPKN7VcRDwh6YfAeElrAe8B3yJLiqo1hWzk5JPAjRHRVLR/DNlC08fJRkzOiIj/S+d/RdKTwB25+leRTUXNVDaX8k+yhGgM2dRLM/AM8FArYiSdb3xaY/JomqZZCBxd5XW4BzhR0hyyhPExWnYKMErSN8hGYk6KiEfb4ZqbmVk70vJRc1uTSToW6BcR327j8euRJSK7RoRXnFapX79+0dRUnB+amVklkmZERPEHSABPFVkVJO0LPAVc4qTFzMw6kqeKOomIGE22TqUtx94PfLw94zEzM2sLj7iYmZlZ3XDiYmZmZnXDiYuZmZnVDScuZmZmVjecuJiZmVndcOJiZmZmdcOJi5mZmdUNJy5mZmZWN/wAOrMaan5xAY0jxtWk7fnnHViTds3MPsg84mJmZmZ1w4mLmZmZ1Y1Ok7hI+oGkeZLmSJotabcW6o+WNDRtD07HzpbUdTXFu7DG7b/fv6LyiZJKfiNnK9u/StIOq9qOmZlZXqdY4yJpIHAQsGtELJa0GfChVjRxFHBhRFxTkwDXQBFxfEfHsDpIaoiIpR0dh5lZZ9FZRlx6AK9FxGKAiHgtIl4CkNRX0kOSZki6V1KP/IGSjge+DJwl6YbihiUdLWlaGo25UlJDKl8o6fzU7v2SBqTRjGclHZzqHCvpTkn3SHpa0tkl2pekCyTNldQsaVgqv17SIbl6N0g6WFJDqj89jS59M9fOpZKekDQO2LzC9Tpa0iPpnAPS8SMlnZY731xJjZLWlzRO0uOprBDf+yM36Vr8NNV5TNIWqby7pNtSrNMl7ZHK90rXc7akWZI2lNRD0qRUNlfS4BLXap9Uv1nS1ZLWTdf99rT/EEnvSPqQpC6Sns3Fen56H58ptF3hWg6RNEHSjUBzhetoZmbtrLMkLuOBj6Wb0uWS9gKQtA5wCTA0IvoCVwM/zR8YEVcBY4HTI+Ko/D5JnwKGAXtERB9gKdnoDMD6wMTU7pvAT4D9gMOAc3PNDEjH9AG+VGKa5vC0rzewL3BBSq6uAo5LcXQDdgfuBr4BLIiI/kB/4ARJW6fzbgf0Ak5I9ctZPyJ2B/47XZNK9gdeiojeEbETcE+p9oDHIqI3MCmdH+A3wK9TrF9MfQI4DfhWuqaDgXeArwD3prLewOz8CSR1AUYDwyKiF9lo4knATGCXVG0wMDddl92Aqbkm1o6IAcCpQCGBLHctIXvffhARK02HSRouqUlS09K3F5S5bGZm1hadYqooIhZK6kt249obuEXSCKAJ2Am4TxJAA/ByK5reB+gLTE/HdwVeTfveZflNvBlYHBHvSWoGGnNt3BcR/wJIIwODUlwFg4Cb0nTEK5IeAvpHxFhJl0nanCy5uS0ilkj6LLCzlq9f6Qb0BPbMtfOSpAcr9OsmgIiYJGkjSRtXqNsMXCjpfOCuiJhcos67wF1pewZZAgdZIrZDunYAG0naEHgY+JWyEa7bI+IfkqYDV6dk846IWCFxIUvKnouIZ9Lra8mSn4sk/S0lmQOAX6Vr0QDkY709F19j2i53Ld8FpkXEc6UuSESMAkYBrNujZ5SqY2ZmbdMpEheAdMOeCExMycMxZDepeRExsJo2JH0M+HN6eQUg4NqIOLNE9fcionDTWgYUpqmWScpf9+IbW/FrUd71ZKM1RwBfz9X/TkTcWxT7ASXaLqdUTEtYcYSuC0BEPJOSwgOAn0saHxHnFh2fvxZLWf53txYwMCLeKap/XprOOgB4TNK+KYnaEzgQuF7SBRFxXb6LFfozGfg88B5wP9nITAPZyE7B4hLxlbuWQ4C3KpzPzMxqpFNMFUnaTlLPXFEf4HngaaC7ssW7SFpH0o7l2omIFyKiT/q5AngAGJpGPZC0iaStWhnefum4rsChZKMNeZOAYWm9RXey0YJpad9osqkNImJeKrsXOCmNTCBpW0nrp3aOSO30IBt5KqewTmUQ2VTJAmA+sGsq3xXYOm1/BHg7Iv4AXFioU6XxwLcLLyT1Sb+3iYjmiDifbPRp+3RdX42I3wG/L3Gep4BGSZ9Mr78KPJS2J5Fdp0cj4p/ApsD2wDwqK3ctzcysg3SWEZcNgEvSlMcS4G/A8Ih4N00DXJzWiawNXETLNzQAIuIJST8Exktai+xf9N8iS4qqNYVs5OSTwI0R0VS0fwwwEHicbOTjjIj4v3T+VyQ9CdyRq38V2VTHTGVzMP8kS4jGAJ8hm9p5huU39VJel/QIsBHLR3JuA74maTYwPbUB2ZqZCyQtS/0/qRV9Pxm4TNIcsms/CTgROFXS3mSjH08AfyEbVTpd0nvAQuBr+YYiYpGk44Bb04jWdLJRMcjWsmyR2geYQ5YEtTQCVe5amplZB1HL/++2WpF0LNAvIr7dUt0yx69HlojsmkZF7AOmX79+0dRUnIuamVklkmZERMlninWKqaI1kaR9yaZHLnHSYmZmnUVnmSr6QIqI0WTrVNpy7P3Ax9szHjMzsw86j7iYmZlZ3Wh14iJpLUkb1SIYMzMzs0qqSlwk3ZgeRLY+2ac8npZ0em1DMzMzM1tRtSMuO0TEG2QfBb2bbG3FV2sWlZmZmVkJ1SYu66SHcB0K3BkR71H9U1jNzMzM2kW1icuVZE9OXR+YlJ5i+katgjIzMzMrpaqPQ0fExcDFuaLn05NNzczMzFabqhIXSWeV2VX8ZXpmZmZmNVPtA+jy34TbBTgIeLL9wzFbszS/uIDGEeMAmH/egR0cjZlZ/at2quiX+deSLgTG1iQiMzMzszLa+uTc9YBPtGcgZq0laamk2ZLmSvpz+vbvSvUPlbRD7vW56Tuf2nr+IZLuauvxZmbWetU+gK5Z0pz0Mw94mhUX65p1hHciok9E7AT8G/hWC/UPBd5PXCLirPSdTyuQ1NC+YZqZWXupdo3LQbntJcArEbGkBvGYtdWjwM4AkrYBLgO6A28DJwCbAAcDe0n6IfBF4EfAXRHxJ0nzgauBzwKXSvo3cA6wLvB34LiIWChpf+Ai4DVg5urrnpmZQfVTRT+JiOfTz4sRsUTS9TWNzKxKaYRkH5avuxoFfCci+gKnAZdHxCNp/+lplObvJZpaFBGDgPuBHwL7RsSuQBPwPUldgN8BXwAGA/9VJp7hkpokNS19e0H7ddTMzKoecdkx/0LS2kDf9g/HrFW6SpoNNAIzgPskbQDsDtwqqVBv3SrbuyX9/jTZlNLDqY0PkY3obA88FxF/BZD0B2B4cSMRMYoseWLdHj39hGkzs3ZUMXGRdCbw/8huEIUn5Qp4l/Q/ZrMO9E5E9JHUDbiLbI3LaOA/EdGnDe0VPvYv4L6IODK/U1If/FUXZmYdquJUUUT8PCI2BC6IiI3Sz4YRsWlEnLmaYjSrKCIWACeTTQu9Azwn6UsAyvROVd8ENqyiyceAPSR9MrWxnqRtgaeArdMaGoAjyzVgZma1UdUal4g4U9KHJQ2QtGfhp9bBmVUrImYBjwNHAEcB35D0ODAPOCRVuxk4XdKsXPJRqq1/AscCN0maQ5bIbB8Ri8imhsZJmgI8X6v+mJlZadU+8v944BTgo8BssjUAjwKfqV1oZpVFxAZFr7+Qe7l/ifoPk/s4NFlyUtjXWFT3QaB/iTbuIVvrYmZmHaDaxbmnkP1P/LGI2FvS9mQfFTWzCnpt2Y0mP+rfzKzdVPtx6EVpmBxJ60bEU8B2tQvLzMzMbGXVjrj8Iz1O/Q6yj5y+DrxUu7DMzMzMVlbtlyweljZHSpoAdAP+UrOozMzMzEqo9ruK3n9KbkQ8FBFjyR6PbmZmZrbaVLvGpfjJuQ34yblmZma2mlVMXCSdKelNYGdJb6SfN4FXgTtXS4RmZmZmiZ+ca2ZmZnWjpe8q2orse1/OTK/3Bg4F5gOXRcS7NY/QzMzMLGlpjcsfgfXh/S+YuxX4X6APcHltQzMzMzNbUUsfh+4aEYXntRwNXB0Rv5S0Ftmj/83MzMxWm5YSF+W2PwOcCRARyySVPsLM3tf84gIaR4zr6DCsBub7qxzMOkRLicuDkv4IvAx8GHgQQFIPwOtbzMzMbLVqaY3LqcDtwHPAoIh4L5X/F/CDWgZWDUkh6Ze516dJGtnCMYdK2qFSnXaI61hJl5YoP1HS19rpHPMlbdaK+o2SvlJh39w2xrE6+jpY0jxJsyV1bY82VyGWiZL6dWQMZmadWUsfh46IuBnoEREv5spnkU0ddbTFwOGtuYGTfSqqZomLpLKjWBFxRURcV6tzt6ARKJm41EI79/Uo4MKI6BMR77RUOT0gsd3Vql0zM6tetU/O3a9E2efbM5A2WgKMAr5bvEPSVpIekDQn/f64pN2Bg4EL0r/et8nVb5D0rDIbS1omac+0b7KkT0raRNIdqc3HJO2c9o+UNErSeOC6ojgOlPSopM1SvdNS+URJ50uaJukZSYNT+XqS/pjOcYukqRX+hX96On6apE+m40dLGpo7/8K0eR4wOPV7peuVq99F0jWSmiXNSh+BL1te675KOh74MnCWpBvS+3OBpLkplmGp3hBJEyTdCDQXtfFlSb9K26dIejZtbyNpStreJ/WrWdLVktZN5fMlnZXqfSnX5lqSrpX0k3LX0szM2l9Lz3E5Cfhv4BOS5uR2bQg8UsvAWuEyYI6kXxSVXwpcFxHXSvo6cHFEHCppLHBXRPwpXzkilkp6hmw0ZmtgBtmNfirw0Yj4m6RLgFmpnc+QJSl9UhN9yabT3pF0LICkw4DvAQdExOtaeUHz2hExQNIBwNnAvmTX+/WI2FnSTlT+9NYb6fivARcBB1WoOwI4LSIq1QH4VroevSRtD4yXtG2FcmrZ14i4StIg0nsm6Ytk17w3sBkwXdKkVH0AsFNEPFfUzCTg9LQ9GPiXpC2BQcBkSV2A0cA+EfGMpOuAk8iuKcCiiBiU+nki2X83NwBzI+KnxTFLGg4MB2jYqPtKF9jMzNqupRGXG4EvAGPT78JP34g4qsaxVSUi3iBLIE4u2jWQLH6A68luUi2ZDOyZfn6ejukPTE/7B6W2iIgHgU0ldUv7xhZNY+wNfB84MCJeL3O+29PvGWRTOYVz3JzOMReYs/Jh77sp93tgC32rVr6PTwHPA9tWKIfV09d8fDdFxNKIeAV4iOw9AphWImkhIv4P2EDShsDHyP4u9iRLYiYD2wHPRcQz6ZBr0/6CW4qavJIySUs636iI6BcR/RrW61aqipmZtVFLa1wWRMT8iDgS+AfwHhBkN4GPr44Aq3QR8A3Sw/LKiCramUx2MxsA3A1sDAwh+xc7rPjx8OJ23yoqf5ZsZGpbylucfi9l+ehXaz5nHiW2l5DeV2XDHh9qRXuVzl8prtXR12riKH4P8h4FjgOeZvn7PBB4uIo4itt9BNg7jdSYmdlqVNUaF0nfBl4B7gPGpZ+7ahhXq0TEv8me8vuNXPEjwBFp+yhgStp+k+wmW8pUYHdgWUQsIpu6+CbZjQ6yBOYoyNZUAK+lEZ9SngcOB66TtGOZOqVMIVvTgbJPP/WqUHdY7vejaXs+y7+5+xBgnbRdqd95+T5uC3yc7GZfrhxWT1/z8Q1TtiapO9nIyLQqjzst/Z5FNkq0OCIWAE8BjYV1QsBXyUZyyvk9WWJ7qyosxjYzs/ZX7eLcU4HtImLHiOiVfnauZWBt8EuyNQ8FJwPHpbU5XwVOSeU3ky1qnaXc4lyAiFgMvAA8loomk93sC4s9RwL9UpvnAcdUCiginia72d9afK4KLge6p3N8n2z6ZEGZuuumNTinsHyB8u+AvSRNA3Zj+WjBHGCJpMcrLc5N52+Q1Ew2RXJsui7lyldXXwvGpHqPkz1X6Iw0FdSSyWTTRJMiYinZ+zwlxb6IbDTm1tS/ZcAVlRqLiF8BM4HrlT1J2szMVgNFtDyDImkCsF9ELKl9SJ2bso/crhMRi1IC8ACw7Zr4hZadoa/r9ugZPY65qOWKVnf85Fyz2pE0IyJKfqK22mHuZ4GJksaxfK1C4V+d1r7WAyZIWods7cVJa9KNvMga39deW3ajyTc4M7N2U23i8r/p50O0frGntUJEvAl0iiezdqa+mplZ+6gqcYmIcwAkrR8RlT65YWZmZlYz1X6qaKCkJ4An0+veki6vaWRmZmZmRar9NMRFwOeAfwFExOOs+IAuMzMzs5qr+mOcEfFCUdHSdo7FzMzMrKJqF+e+oOwLCkPSh8iekfJk7cIyMzMzW1m1Iy4nkn3J3pZkj/7vk16bmZmZrTbVfqroNdLj3s3MzMw6SlWJi6Stge+Qfavv+8dExMG1CcvMzMxsZdWucbmD7Ivl/kz2PS5mZmZmq121icuiiLi4ppGYrYGaX1xA44hxHR1GWf6+HTOrN9UmLr+RdDYwnhW/q2hmTaIyMzMzK6HaTxX1Ak4AzgN+mX4urPYkkkLSL3OvT5M0soVjDpW0Q7XnaAtJx0q6tET5iZK+VstzV0vSREmr/H0+koakj7SX2lfyOlTZ7mhJQ0uUX9Ve75+kkyU9KemG9mhvFWNZ2NExmJl1ZtWOuBwGfGIVvrl3MXC4pJ+nTyhV41DgLuCJNp6zIkll+x4RV9TinB1sCLAQeGR1nCwijm/H5v4b+HxEPFdNZUlrR8SSdjw/kkT2DdZmZtaBqh1xeRzYeBXOswQYBXy3eIekrSQ9IGlO+v3xNDJwMHCBpNmStsnVb5D0rDIbS1omac+0b7KkT0raRNIdqc3HJO2c9o+UNErSeOC6ojgOlPSopM1SvdNS+URJ50uaJukZSYNT+XqS/pjOcYukqaVGRiSdJWm6pLnp3Gqh3a6Sbi60C3QtdUElzZd0jqSZkpolbZ/KV+q7pEayZ/F8N13PweXeqFLvR6XyomN/nEZg1sqPFElaKOmnkh5PMW2RyrdJr6dLOrfUaIakK4BPAGMlfbeN7+3lkg5O22MkXZ22vyHpJ2n7e+k9mivp1FTWmEZ6LgdmAh/LtblZ+nvxIhEzs9Wo2sRlC+ApSfdKGlv4aeW5LgOOktStqPxS4LqI2Bm4Abg4Ih4BxgKnR0SfiPh7oXJELAWeAXYABgEzgMGS1gU+GhF/A84BZqU2/x8r3sj6AodExFcKBZIOA0YAB5QZEVo7IgYApwJnp7L/Bl5P5/hxareUSyOif0TsRJaEHNRCuycBb6d2f1qhXYDXImJX4LfAaalspb5HxHzgCuDX6XpOrtDmSu9HC+UASPoFsDlwXEQUf/JsfeCxiOgNTCKbdgT4DfCbiOgPvFQqmIg4Me3bOyJ+Xap/ueorvbfJJKCQrG1J9rcD2d/PZEl9geOA3YBPAydI2iXV2S71e5eIeD71dQtgHHBWRKy08lbScElNkpqWvr2gVLfMzKyNqk1cziabLvoZy9e4/LLiEUUi4g2ym8zJRbsGAjem7evJbiYtmUz2JY97Aj9Px/QHpqf9g1JbRMSDwKa5hGlsRLyTa2tv4PvAgRHxepnz3Z5+zyB7lk3hHDenc8wF5pQ5du80GtMMfAbYsYV29wT+kNqdU6HdSnGV63s1yr0fld6nHwEbR8Q3IyJKtPku2bRfcawDgVvT9o1UpzXvbcFksuR2B7Kpx1ck9UjnfyS1OSYi3oqIhWTXtZDoPB8Rj+XaWgd4ADgjIu4rFWBEjIqIfhHRr2G91lx6MzNrSWsW586JiIfyP20430XAN8j+BV5OqRtfsclkN5YBwN1k01hDyP5lDaXXIhTafauo/FlgQ2DbCucrfJJqKcvXBbW43kFSF+ByYGhE9AJ+B3Rpod18rC2pNq5q2yul3LH58ulAX0mblKn7Xi6hKe5ra7Xmvc12RrwIfBjYn+xvZDLwZWBhRLxZps2C4jaXkCVfn2tFzGZm1k6qTVz+C5ie1nTsX1in0VoR8W/gj2TJS8EjwBFp+yhgStp+kyyhKGUqsDuwLCIWAbOBb5LdkCC7OR0F2adpyKZU3ijT1vPA4cB1knYsU6eUKWQ3P9K/5HuVqFNIUl6TtAGw0qdvSsjHvhOwcytiKj5+CMv7Xul65pV7P8qVA9xD9omzcZKqOUfBY8AX0wozLeoAABrpSURBVPYRlSrmtOa9zXuUbEqukLicxop/L4emdUvrk40ulptOC+DrwPaSRlQZs5mZtZOqEpeI+CHQk+zpuccCf5X0M+UWzbbCL4HNcq9PBo6TNAf4KnBKKr8ZOF3SrOLzRMRi4AWyGx9kN5kNgeb0eiTQL7V5HnBMC/17muxmeGsr+nQ50D2d4/tkUzorLGiIiP+QjbI0kz19eHpxIyX8FtggtXsGMK3KeApGUrrvfwYOa2lxLuXfj3LlAETErWR9HSup5ILiEk4FvidpGtCDoutXxkha8d7mTCZbU/Q3soW2m6SywvOIRpNd66nAVRExq1xDaZ3VEWTTgP9d5fnNzKwdqPSShDKVpd5kixj3ByaQLWS8LyLOqE14H1ySGoB1ImJRSnYeALZdhY+MdzqS1gPeiYiQdARwZEQc0tFxtad+/fpFU1NTR4dhZlZXJM2IiJLPMKv2SxZPJvuX7WvAVWSf9nlP0lrAX8lGBjqb9YAJktYhWyNxkpOWVusLXJqmHv9DNgVjZmZWVrWLJDcDDi98HLQgIpZJOqjMMWu0tKhzlZ9o25mlj2X37ug4zMysflSVuETEWZJ6S/pCKpocEY+nfU/WLDozMzOznKoW56apohvIHjC2OfAHSd+pZWBmZmZmxaqdKjoe2C0i3gKQdD7Zx0svqVVgZmZmZsWqfY6LyB4cVrAUf+GcmZmZrWbVjrhcA0yVNCa9PpTsmS5mZmZmq021i3N/JWki2Xe6iOyL9Mo+oMvMzMysFiomLkXfPTM//by/Lz3C38zMzGy1aGnEZQbZd7OU+2K7T7R7RGZmZmZlVExcImLr1RWI2Zqo+cUFNI4YB8D88w7s4GjMzOpftYtzkXQ42RqXIHsA3R01i8rMzMyshGofQHc5cCLZtxzPBU6UdFktAzMzMzMrVu1zXPYCPhcR10TENcABwJCaRWVWgaSQdH3u9dqS/inprvT6YEkj0vZISael7XMl7Zu2T03fTm1mZnWk2sTlaeDjudcfA+a0fzhmVXkL2ElS1/R6P+DFws6IGBsR5xUfFBFnRcT96eWpZN/wXTVJDW2M18zM2km1icumwJOSJqbnuTwBdJc0VtLYmkVnVt5fgMJq1yOBmwo7JB0r6dLiAySNljQ0fffWR4AJkiakfb+V1CRpnqRzcsfMl3SWpCnACEkzc/t6SppRm+6ZmVkp1S7OPaumUZi13s3AWWl6aGfgamBwNQdGxMWSvgfsHRGvpeIfRMS/06jKA5J2jojCqOKiiBgEIGlfSX0iYjZwHDC6uH1Jw4HhAA0bdW97D83MbCXVPjn3IQBJG+WP8QPorKNExBxJjWSjLXe3Q5NfTgnH2kAPYAeWT4fekqt3FXBcSnyGAQNKxDYKGAWwbo+e0Q6xmZlZUlXikv6H/mPgHWAZ2QPp/AA662hjgQvJFopv2tZGJG0NnAb0j4jXJY0GuuSqvJXbvg04G3gQmBER/2rrec3MrPWqnSo6HdgxN6xu9kFwNbAgIpolDWnlsW8CGwKvARuRJScLJG0BfB6YWOqgiFgk6V7gt8A32hi3mZm1UbWJy9+Bt2sZiFlrRcQ/gN+08fBRwF8kvRwRe0uaBcwDngUebuHYG4DDgfFtPLeZmbWRIlqegpe0C3ANMBVYXCiPiJNrF5rZB1N6Lky3iPhRS3X79esXTU1NqyEqM7M1h6QZEdGv1L5qR1yuJJvTbyZb42LWKUkaA2wDfKajYzEz64yqTVyWRMT3ahqJWR2IiMM6OgYzs86s2gfQTZA0XFIPSZsUfmoamZmZmVmRakdcvpJ+n5kr88ehzczMbLWq9gF0W9c6EDMzM7OWVJwqknRGbvtLRft+VqugzMzMzEppaY3LEbntM4v27d/OsZiZmZlV1FLiojLbpV6bmZmZ1VRLiUuU2S712szMzKymWlqc21vSG2SjK13TNul1l/KHmZmZmbW/iolLRDSsrkDM1kTNLy6gccS4jg7DzGy1mn/egTVru9oH0JmZmZl1OCcudUjSUkmzcz8j2qnd+ZI2a4+2yrQ/Mn1BYXF5d0lTJc2SNLhW5zczs/pX7ZNz7YPlnYjo09FBtKN9gKci4phqD5DUEBFLaxiTmZl9AHnEZQ2SRkx+JulRSU2SdpV0r6S/Szox1RkiaZKkMZKekHSFpJX+DiR9T9Lc9HNqKvuxpFNydX4q6eS0fbqk6ZLmSDonV+cHkp6WdD+wXYnz9AF+ARyQRo+6SjpSUnM69/m5ugslnStpKjAwV765pBlpu7ekkPTx9PrvktaT9IXcqM79krZI+7tLuk/STElXSnq+MOok6WhJ01JcV0pqSD+jU2zNkr67Ku+ZmZm1jhOX+tS1aKpoWG7fCxExEJgMjAaGAp8Gzs3VGQD8D9AL2AY4PN+4pL7AccBu6dgTJO0C/B44JtVZi+wBhTdI+izQM7XbB+grac/UzhHALukc/Ys7EhGzgbOAW9Io0oeB84HPpLb6Szo0VV8fmBsRu0XElFwbrwJdJG0EDAaagMGStgJejYi3gSnApyNiF+BmoPBU6LOBByNiV2AMUEh4PgUMA/ZIcS0FjkoxbRkRO0VEL+Ca4j6lLyRtktS09O0FxbvNzGwVeKqoPlWaKhqbfjcDG0TEm8CbkhZJ2jjtmxYRzwJIugkYBPwp18YgYExEvJXq3A4MjoiLJf0rJTFbALMi4l8pcfksMCsdvwFZIrNhauft1M5YWtYfmBgR/0zH3ADsCdxBljzcVua4R4A9Ut2fkT3ZWWQJHMBHgVsk9QA+BDyX6+thABFxj6TXU/k+QF9guiSArsCrwJ+BT0i6BBgHjC8OJCJGAaMA1u3R0887MjNrR05c1jyL0+9lue3C68L73dLDBCs9Ffkq4Fjgv4Crc/V/HhFXrtBINsXU2ht3pXMvqrCuZTLZaMtWwJ3A99O570r7LwF+FRFjJQ0BRrZwPgHXRkTxV10gqTfwOeBbwJeBr1eI2czM2pGnijqnAZK2TtM9w8imUfImAYemtSHrk41IFEYuxpCNZvQH7k1l9wJfl7QBgKQtJW2e2jksrVvZEPhCFbFNBfaStJmkBuBI4KEqjpsEHA38NSKWAf8GDgAeTvu7AS+m7fwi4ClkyQdp5OjDqfwBYGjqB5I2kbRVWv+yVkTcBvwI2LWK2MzMrJ14xKU+dZU0O/f6nohozUeiHwXOI1vjMoksGXlfRMyUNBqYloquiohZad+7kiYA/ymMfkTE+LQm5NE0rbIQODq1cwswG3ie5clPWRHxsqQzgQlkox53R8SdVRw3P517UiqaAnw0IgpTPyOBWyW9CDwGbJ3KzwFuSuuEHgJeBt6MiNck/RAYnxK898hGWN4BrsktaF5pRMbMzGpHEZ6C70zSNMlpEXFQG49fC5gJfCki/tqesXUESesCSyNiiaSBwG/b86Pm6/boGT2Ouai9mjMzqwur+uRcSTMiol+pfR5xsapJ2oFszciYNSFpST4O/DElZO8CJ7Rn47227EZTDR99bWbW2Thx6WQiYiIwsY3HPgF8oj3j6WgpAdulo+MwM7PqeHGumZmZ1Q0nLmZmZlY3nLiYmZlZ3XDiYmZmZnXDiYuZmZnVDScuZmZmVjecuJiZmVndcOJiZmZmdcOJi5mZmdUNPznXrIaaX1xA44hxq9zOqn7vh5nZmsIjLmZmZlY3nLhUSdJSSbMlzZP0uKTvpS/mq3RMP0kXr64YO5qkq9IXMRaXHyvp0hLlB0sa0UKbjZK+0p5xljnPaElDS5R/RNKfan1+MzOrjqeKqvdORPQBkLQ5cCPQDTi73AER0QQ0rZ7wOl5EHN/K+mOBsS1UawS+Qna9qyKpISKWtiaWciLiJWClhMbMzDqGR1zaICJeBYYD31ami6RrJDVLmiVpbwBJQyTdlbZHSrpa0kRJz0o6udCepB9JekrSfZJuknRa8TklfUHS1NT+/ZK2aKndouMX5raHShqdtkdLuljSI+n4obnYJ0kaI+kJSVcURpgk/VZSUxp9OifX7kRJ/dL2cZKekfQQsEeZmN4fiSkXB3AeMDiNdn1XUoOkCyRNlzRH0jdz8U6QdCPQnF5PlPSndG1vkKRU97zUpzmSLsyFtGeJ69AoaW4u3jsl3SPpaUllk1YzM6sNj7i0UUQ8m27kmwNHp7JekrYHxkvatsRh2wN7AxsCT0v6LdAb+CKwC9n7MROYUeLYKcCnIyIkHQ+cAfxPuXYj4r1WdKcHMCi1MxYoTI0MAHYAngfuAQ5P+34QEf+W1AA8IGnniJhTaExSD+AcoC+wAJgAzGpjHCOA0yLioNT2cGBBRPSXtC7wsKTxuXh3iojnJA0hu6Y7Ai8BDwN7SHoCOAzYPl3Ljau4DnkDgJ2At4HpksalkbX3pRiHAzRs1L2KbpuZWbU84rJqlH4PAq4HiIinyG70pRKXcRGxOCJeA14FtkjH3hkR70TEm8Cfy5zro8C9kpqB08luyJXabY07ImJZRDxRdOy0iHg2TbvclGIF+LKkmWTJyI5kyU3ebsDEiPhnRLwL3LKKceR9FviapNnAVGBToGcu3ueK4v9HRCwDZpNNO70BLAKuknQ4WQLSmvPfFxH/ioh3gNtZfk3eFxGjIqJfRPRrWK9bNf02M7MqOXFpI0mfAJaSJQpqoXrB4tz2UrIRlmqPvQS4NCJ6Ad8EurTQbrHIbXcp2pc/Ph9PFNULSVsDpwH7RMTOwLgS7ZU6thrl4qCo/DsR0Sf9bB0RhRGXtyq0txRYOyKWkI2a3AYcSjaS1Jrzr3RNytQzM7MacOLSBpK6A1eQJRIBTAKOSvu2BT4OPF1lc1OAL6R1MhsA5R7Y0Q14MW0f04awX5H0qTS9dViVxwyQtHU6ZliKdSOyBGFBWmfz+RLHTQWGSNpU0jrAl9oQb8GbZFNgBfcCJ6V2kbStpPWrbSxd424RcTdwKtCnlfHsJ2kTSV3JEp+HW3m8mZmtAq9xqV7XND2xDrCEbGroV2nf5cAVaRpnCXBsRCxOa0EriojpksYCj5NNMTWRrQspNhK4VdKLwGPA1q2MfwRwF/ACMBfYoIpjHiVbHNuLLDkbExHLJM0C5gHPUuLGHREvSxqZjn+ZbN1OQyvjLZgDLJH0ODAa+A3ZlM/MtNj2n2QJRLU2BO6U1IVsVOW7rYxnCtl7/0ngxuL1LWZmVlvKBgysI0naICIWSlqPLEEYHhEzOzimIeQWxVr2qSKgX0R8u9pj1u3RM3occ9Eqn9tPzjWzzkTSjIjoV2qfR1w+GEYpe3BbF+Dajk5arP302rIbTU46zMzajROXD4CIqPmTYVsrIiYCEzs4jA+UiBhNNl1lZmYdxItzzczMrG44cTEzM7O64cTFzMzM6oYTFzMzM6sbTlzMzMysbjhxMTMzs7rhxMXMzMzqhhMXMzMzqxt+AJ1ZDTW/uIDGEeOqquvH+puZtcwjLmZmZlY3nLiYmZlZ3ahp4iLpMEkhaftWHndq+qbkmpHUR9IBFfbPl7RZLWP4IGnvay5ptKSh7dVerUmaKKnkN5GamdkHR61HXI4EpgBHtPK4U4GaJi5AH6Bs4lKvJDW08dBVuuarcN661ln7bWbWUWqWuEjaANgD+AZlEhdJ60saJ+lxSXMlDZN0MvARYIKkCanebyU1SZon6ZxUto+kMbm29pN0e4lzDJD0iKRZ6fd2kj4EnAsMkzRb0rAy3fiOpJmSmgujRpI2kXSHpDmSHpO0cyofKelaSePTaM3hkn6Rjr1H0jqpXl9JD0maIeleST1KxDxa0hWSJkt6RtJBqbwxlc1MP7un8iGSJki6EWiW1CDpAknTU5zfzNWbKOlPkp6SdIMyK13zonj2SdevWdLVktZN5fMlnSVpCvClEtdvz3TNny2MvqTzXZDe7+bCtU+xPSTpj6nP50k6StK0VG+bVK+7pNtS36ZL2qOV1/+sdNxcSaMkqaiva6V2ftLCdXz/epf52zEzsxqo5YjLocA9EfEM8G9Ju5aosz/wUkT0joidUv2LgZeAvSNi71TvBxHRD9gZ2CslCw8Cn5LUPdU5DrimxDmeAvaMiF2As4CfRcS7afuWiOgTEbeU6cNrEbEr8FvgtFR2DjArInYG/h9wXa7+NsCBwCHAH4AJEdELeAc4MN08LwGGRkRf4Grgp2XO3Qjsldq7QlIX4FVgvxTTMODiXP0B6TrtQJYsLoiI/kB/4ARJW6d6u5CNruwAfALYo8w1ByCddzQwLPVlbeCkXJVFETEoIm4u0YcewCDgIOC8VHY42WhXb2Bf4IJc8tYbOAXoBXwV2DYiBgBXAd9JdX4D/Dr17YtpX0HF65/qXBoR/dPfW9cUW8HawA3AMxHxwxauY/56r0DScGWJdtPStxeUuCxmZtZWtUxcjgQKN7Ob0+tizcC+ks6XNDgiyv1f/suSZgKzgB2BHSIigOuBoyVtDAwE/lLi2G7ArZLmAr9Ox1erMIIzgyyRgOxGfD1ARDwIbCqpW9r3l4h4L/WrAbgn189GYDtgJ+A+SbOBHwIfLXPuP0bEsoj4K/AssD2wDvA7Sc3ArWTJR8G0iHgubX8W+Fo6x1RgU6Bnrt4/ImIZMDvXr3K2A55LCSjAtcCeuf3lkj6AO1IfngC2SGWDgJsiYmlEvAI8RJYUAEyPiJcjYjHwd2B8Ki9cP8iSnUtT38YCG0naMO1r6foD7C1parqGn2HFv4crgbkRUUgmW7qOz1FCRIyKiH4R0a9hvW6lqpiZWRvV5DkukjYluynsJCnIbiIh6YyUcAAQEc9I6ku21uTnksZHxLlFbW1NNtrRPyJelzQa6JJ2XwP8GVgE3BoRSyR9Czgh7T8A+DHZv7wPk9QITCwT871kN9emiDg+FS9Ov5ey/Fqp+Fig0KfFqV/LJL2X6+uydLyAeRExsFQMZdrMv/4u8ArZyMRaZP0ueCvfHeA7EXFvvgFJQ3J9ghX7VU6p/ua9VWFf/lwq+t1S/WW514XrB1m/B0bEOysEmc34VLz+afTocqBfRLwgaSTL/5YAHiFLbH4ZEYuofB0r9dvMzGqkViMuQ4HrImKriGiMiI8Bz5H9a/t9kj4CvB0RfwAuBArTSW8ChX9Fb0R2k1ggaQvg84XjI+IlsimOH5JNZxARl6Xpnz5pfzfgxXTIsbnT589BRHwuHXM8lU0CjkrxDyGbTnqjhWMKnga6SxqYjl9HUrkRoC+l9RbbkE3pPJ368nIaLfkqWUJYyr3ASbl1HdtKWr+F2Fa4HjlPAY2SPplef5VslKStJpGtLWpI03x7AtNacfx44NuFF5L6tOLYQpLymrI1WMWfevo9cDfZCN3atO06mplZDdUqcTkSGFNUdhvwlaKyXsC0NBT/A+AnqXwU8BdJEyLicbIponlka0IeLmrjBuCFNB1Ryi/IRnMeZsUb/QRgB1VenFvKSKCfpDlk6zaOqfbAtLZmKHC+pMfJpmp2L1P9abIE4S/AiWkE4HLgGEmPAdtS/l/9VwFPADPTFNmVtDyy8v41L4p5Edn6oVvT9Moy4IoW2qpkDDAHeJxsndIZEfF/rTj+ZNL1l/QEcGK1B0bEf4DfkU0d3QFML1HnV8BMsunAtlxHMzOrIeVmbuqSpEvJFsv+vqNjaS9pOuyuiPhTR8diq6Zfv37R1NTU0WGYmdUVSTPSh3JWUtf/epQ0g2zU4X86OhYzMzOrvbpOXNJHitc4EXFsR8dgZmb2QeTvKjIzM7O64cTFzMzM6oYTFzMzM6sbdf+pIrMPMklvkn20vTPaDHito4PoIO575+S+t5+tIqJ7qR11vTjXrA48Xe4jfWs6SU3ue+fjvrvvteapIjMzM6sbTlzMzMysbjhxMautUR0dQAdy3zsn971zWm199+JcMzMzqxsecTEzM7O64cTFzMzM6oYTF7MakbS/pKcl/U3SiI6Opz1J+pikCZKelDRP0impfBNJ90n6a/r94dwxZ6Zr8bSkz3Vc9O1DUoOkWZLuSq87Rd8lbSzpT5KeSu//wE7U9++mv/e5km6S1GVN7bukqyW9KmlurqzVfZXUV1Jz2nexJK1qbE5czGpAUgNwGfB5YAfgSEk7dGxU7WoJ8D8R8Sng08C3Uv9GAA9ERE/ggfSatO8IYEdgf+DydI3q2SnAk7nXnaXvvwHuiYjtgd5k12CN77ukLYGTgX4RsRPQQNa3NbXvo8nizmtLX38LDAd6pp/iNlvNiYtZbQwA/hYRz0bEu8DNwCEdHFO7iYiXI2Jm2n6T7Oa1JVkfr03VrgUOTduHADdHxOKIeA74G9k1qkuSPgocCFyVK17j+y5pI2BP4PcAEfFuRPyHTtD3ZG2gq6S1gfWAl1hD+x4Rk4B/FxW3qq+SegAbRcSjkX0S6LrcMW3mxMWsNrYEXsi9/kcqW+NIagR2AaYCW0TEy5AlN8Dmqdqadj0uAs4AluXKOkPfPwH8E7gmTZNdJWl9OkHfI+JF4ELgf4GXgQURMZ5O0Pec1vZ1y7RdXL5KnLiY1Uapedw17tkDkjYAbgNOjYg3KlUtUVaX10PS/2/v7lmjiOIojD+nUPGltJIIbhGstQqmEWMpWgkW0SBWon4AbWytbK20MgghBEwjNvZRokKIdiq4hcZSrIwci3sDiySSl92E2Tk/WHbmLgP3zA67/517Z/YCsGJ7cbObrNPWyOyUMw6ngUe2TwG/qMMFGxia7HU+xyWgAxwDDkua/N8m67Q1MvsmbJR1IPsghUvEYHSB4z3rI5TTykND0j5K0TJte642f6+nh6nPK7V9mPbHOHBR0hfKEOA5SU9pR/Yu0LW9UNdnKYVMG7KfBz7b/mH7NzAHnKEd2ddsNWu3Lv/bviMpXCIG4w0wKqkjaT9l4tr8Hvepb+qVAY+Bj7Yf9rw0D0zV5SngeU/7FUkHJHUok/Re71Z/+8n2Xdsjtk9Q3tdXtidpR/ZvwFdJJ2vTBPCBFmSnDBGNSTpUj/8JytyuNmRfs6WsdTjpp6Sxus+u9Wyzbfl36IgBsL0q6TbwknL1wRPby3vcrX4aB64CS5Le17Z7wANgRtINygf9ZQDby5JmKF9yq8At2392v9sD1Zbsd4DpWpB/Aq5TfgQPdXbbC5JmgbeULO8ot7k/whBml/QMOAscldQF7rO9Y/wm5Qqlg8CL+thZ33LL/4iIiGiKDBVFREREY6RwiYiIiMZI4RIRERGNkcIlIiIiGiOFS0RERDRGCpeIiIhojBQuERER0Rh/AXON3Hc2sDJ5AAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create an empty data frame\n",
    "all_responses = pd.DataFrame()\n",
    "\n",
    "# Set up for loop to iterate through values in responses\n",
    "for df in all_survey_data.values():\n",
    "  # Print the number of rows being added\n",
    "  print(\"Adding {} rows\".format(df.shape[0]))\n",
    "  # Append df to all_responses, assign result\n",
    "  all_responses = all_responses.append(df)\n",
    "\n",
    "# Graph employment statuses in sample\n",
    "counts = all_responses.groupby(\"EmploymentStatus\").EmploymentStatus.count()\n",
    "counts.plot.barh()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Modifying imports: true/false data\n",
    "## Set Boolean columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the data\n",
    "survey_data = pd.read_excel(\"fcc_survey_subset.xlsx\")\n",
    "\n",
    "# Count NA values in each column\n",
    "print(survey_data.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set dtype to load appropriate column(s) as Boolean data\n",
    "survey_data = pd.read_excel(\"fcc_survey_subset.xlsx\",\n",
    "                            dtype={\"HasDebt\": bool})\n",
    "\n",
    "# View financial burdens by Boolean group\n",
    "print(survey_data.groupby(\"HasDebt\").sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load file with Yes as a True value and No as a False value\n",
    "survey_subset = pd.read_excel(\"fcc_survey_yn_data.xlsx\",\n",
    "                              dtype={\"HasDebt\": bool,\n",
    "                              \"AttendedBootCampYesNo\": bool},\n",
    "                              true_values=[\"Yes\"],\n",
    "                              false_values=[\"No\"])\n",
    "\n",
    "# View the data\n",
    "print(survey_subset.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Modifying imports: parsing dates\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "# Load file, with Part1StartTime parsed as datetime data\n",
    "survey_data = pd.read_excel('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/fcc-new-coder-survey.xlsx',\n",
    "                               skiprows=2,\n",
    "                            parse_dates=['Part1StartTime'])\n",
    "\n",
    "# Print first few values of Part1StartTime\n",
    "print(survey_data.Part1StartTime.dtype)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0   2016-03-29 21:23:13\n",
      "1   2016-03-29 21:24:59\n",
      "2   2016-03-29 21:25:37\n",
      "3   2016-03-29 21:21:37\n",
      "4   2016-03-29 21:26:22\n",
      "Name: Part1StartTime, dtype: datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "# Print first few values of Part1StartTime\n",
    "print(survey_data.Part1StartTime.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dict of columns to combine into new datetime column\n",
    "datetime_cols = {\"Part2Start\": [\"Part2StartDate\",\"Part2StartTime\"]}\n",
    "\n",
    "\n",
    "# Load file, supplying the dict to parse_dates\n",
    "survey_data = pd.read_excel('C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/fcc-new-coder-survey.xlsx',\n",
    "                               skiprows=2,\n",
    "                           parse_dates= datetime_cols)\n",
    "\n",
    "# View summary statistics about Part2Start\n",
    "print(survey_data.Part2Start.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parse non-standard date formats\n",
    "https://strftime.org/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parse datetimes and assign result back to Part2EndTime\n",
    "survey_data[\"Part2EndTime\"] = pd.to_datetime(survey_data[\"Part2EndTime\"], \n",
    "                                             format=\"%m%d%Y %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to databases\n",
    "## Connect to a database\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'sqlalchemy' from 'C:\\\\Users\\\\rzouga\\\\Anaconda3\\\\lib\\\\site-packages\\\\sqlalchemy\\\\__init__.py'>"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlalchemy\n",
    "sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['boro_census', 'hpd311calls', 'weather']\n"
     ]
    }
   ],
   "source": [
    "# Import sqlalchemy's create_engine() function\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Create the database engine\n",
    "engine = create_engine(\"sqlite:///C:/Users/rzouga/Desktop/ALLINHERE/ALLINHERE/NLP/Datacamp/data.db\")\n",
    "\n",
    "# View the tables in the database\n",
    "print(engine.table_names())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "==>Well done! sqlalchemy is a powerful library that can be used with pandas to both query databases for analysis and write results back to database tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  unique_key created_date agency  complaint_type incident_zip  \\\n",
      "0   38070822   01/01/2018    HPD  HEAT/HOT WATER        10468   \n",
      "1   38065299   01/01/2018    HPD        PLUMBING        10003   \n",
      "2   38066653   01/01/2018    HPD  HEAT/HOT WATER        10452   \n",
      "3   38070264   01/01/2018    HPD  HEAT/HOT WATER        10032   \n",
      "4   38072466   01/01/2018    HPD  HEAT/HOT WATER        11213   \n",
      "\n",
      "       incident_address community_board    borough  \n",
      "0    2786 JEROME AVENUE        07 BRONX      BRONX  \n",
      "1  323 EAST   12 STREET    03 MANHATTAN  MANHATTAN  \n",
      "2  1235 GRAND CONCOURSE        04 BRONX      BRONX  \n",
      "3  656 WEST  171 STREET    12 MANHATTAN  MANHATTAN  \n",
      "4       1030 PARK PLACE     08 BROOKLYN   BROOKLYN  \n"
     ]
    }
   ],
   "source": [
    "# Load libraries\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Create the database engine\n",
    "engine = create_engine('sqlite:///data.db')\n",
    "\n",
    "# Load hpd311calls without any SQL\n",
    "hpd_calls = pd.read_sql(\"hpd311calls\", engine)\n",
    "\n",
    "# View the first few rows of data\n",
    "print(hpd_calls.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       station                         name  latitude  longitude  elevation  \\\n",
      "0  USW00094728  NY CITY CENTRAL PARK, NY US  40.77898  -73.96925       42.7   \n",
      "1  USW00094728  NY CITY CENTRAL PARK, NY US  40.77898  -73.96925       42.7   \n",
      "2  USW00094728  NY CITY CENTRAL PARK, NY US  40.77898  -73.96925       42.7   \n",
      "3  USW00094728  NY CITY CENTRAL PARK, NY US  40.77898  -73.96925       42.7   \n",
      "4  USW00094728  NY CITY CENTRAL PARK, NY US  40.77898  -73.96925       42.7   \n",
      "\n",
      "         date     month  awnd  prcp  snow tavg  tmax  tmin  \n",
      "0  12/01/2017  December  5.37  0.00   0.0         52    42  \n",
      "1  12/02/2017  December  3.13  0.00   0.0         48    39  \n",
      "2  12/03/2017  December  2.01  0.00   0.0         48    42  \n",
      "3  12/04/2017  December  3.58  0.00   0.0         51    40  \n",
      "4  12/05/2017  December  6.71  0.75   0.0         61    50  \n"
     ]
    }
   ],
   "source": [
    "# Create the database engine\n",
    "engine = create_engine(\"sqlite:///data.db\")\n",
    "\n",
    "# Create a SQL query to load the entire weather table\n",
    "query = \"\"\"\n",
    "SELECT * \n",
    "  FROM weather;\n",
    "\"\"\"\n",
    "\n",
    "# Load weather with the SQL query\n",
    "weather = pd.read_sql(query, engine)\n",
    "\n",
    "# View the first few rows of data\n",
    "print(weather.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While it's convenient to load tables by name alone, using SQL queries makes it possible to fine-tune imports at the data acquisition phase of an analysis project.\n",
    "# Refining imports with SQL queries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           date  tmax  tmin\n",
      "0    12/01/2017    52    42\n",
      "1    12/02/2017    48    39\n",
      "2    12/03/2017    48    42\n",
      "3    12/04/2017    51    40\n",
      "4    12/05/2017    61    50\n",
      "..          ...   ...   ...\n",
      "116  03/27/2018    47    34\n",
      "117  03/28/2018    52    38\n",
      "118  03/29/2018    53    49\n",
      "119  03/30/2018    62    44\n",
      "120  03/31/2018    58    39\n",
      "\n",
      "[121 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Create database engine for data.db\n",
    "engine = create_engine(\"sqlite:///data.db\")\n",
    "\n",
    "# Write query to get date, tmax, and tmin from weather\n",
    "query = \"\"\"\n",
    "SELECT date, \n",
    "       tmax, \n",
    "       tmin\n",
    "  FROM weather;\n",
    "\"\"\"\n",
    "\n",
    "# Make a data frame by passing query and engine to read_sql()\n",
    "temperatures = pd.read_sql(query,engine)\n",
    "\n",
    "# View the resulting data frame\n",
    "print(temperatures)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "==>Selecting columns is useful when you only want a few columns from a table. If you want most of the columns, it may be easier to load them all and then use pandas to drop unwanted columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAccAAAD4CAYAAACZpy1+AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAAYbUlEQVR4nO3de7hddX3n8feHiIGooNw0BetBZaQomGJURpHiHYFCHR+5WDWx0wFHbdHxBmJbxhkV8YYOSsUWFKYg07HeQK0WYWyrI5NAIICgIkFBRkhbuRQUTb79Y6+Dmx/nsk845+x9kvfrefZz1v6t23f/8iSf/H5rnbVTVUiSpF/batgFSJI0agxHSZIahqMkSQ3DUZKkhuEoSVLjIcMuQIPZaaedamxsbNhlSNKCsnr16vVVtfNM9zMcF4ixsTFWrVo17DIkaUFJcuOm7Oe0qiRJDcNRkqSG4ShJUsNwlCSpYThKktQwHCVJahiOkiQ1DEdJkhqGoyRJDZ+Qs0Csvfl2xo6/cKBt1518yBxXI0mbN0eOkiQ1DEdJkhqGoyRJDcNRkqSG4ShJUsNwlCSpYThKktSYs3BMcmKSq5NcmWRNkmcm+Vy3/IMkt3fLa5I8q9vniiTndcuv6Vt/b5K13fLJSVYmua1v/ZokeyUZS1JJ/qivjtOSrJygvpOSvKVb3i/Jd7rjfDfJSV37yiSnTfL5HpJkfZL3Nu2XJFnV9355kku65QO7z315kuuSfDPJoQ+yqyVJs2xOHgKQ5N8DhwL7VtUvkuwEPLSqXtqtPxB4S1Ud2rfPb9EL6wOSPKyqzgLO6tatA55bVeu79yuB86vqDc15x4BbgeOSfKKq7h2w5E8DR1TVFUkWAU8aYJ8XAdcBRyR5R1VV37pdkrykqr4ywX5/P/65kywDPp/knqq6aMBaJUlzbK5GjkuB9VX1C4CqWl9VP5lmn1cA5wBfAw57EOe+DbgIWDGDfXYBbgGoqg1Vdc0A+xwNfAT4EbBfs+79wDunO0BVrQHeBbxhum0lSfNnrsLxa8Bjk3wvyceT/M4A+xwJnA+cRy94pt2+mVbdtm/dycCbu1HgID4MXNdN+x6bZJupNu7O9Xzggknq/TbwiyTPHeDclwF7TnKeY5KsSrJqw923D3AoSdJsmJNwrKq7gKcBx9AbyZ0/0XW/cUmeDtxWVTfSG/Xtm+RR05zm/Kpa1ve6p+/8NwCX0huNDlLvu4Dl9EL9FcBXp9nlUODiqrob+Czw0gmC+L8zwOgRyBR1nVFVy6tq+aIl2w9wKEnSbJizG3K66clLqurP6E0bvmyKzY8G9uyuLV4PbDfN9oN4D/B2BvyMVXV9VZ1Ob0T41CQ7TrH50cALunpXAzsC9xslVtU3gG144JRr67eB7w5SoyRpfsxJOCZ5UpI9+pqWATdOsu1WwMuBfapqrKrGgMMZbGp1UlV1LXANvVHedPUekmR8BLcHsAH42STbbgfsD/xmX72vn6TedwNvm+K8+wB/AnxsuholSfNnrr6y6uHA/0jySOBXwA/oTbFO5ADg5qq6ua/tm8BeSZZW1S2T7Hdkkv373r8OaG/6eTdw+QD1vgr4cJK7u3p/v6o2dHm5Msnv9W3758A3xm826nwBOCXJ4v6DVtWXk9zWnOs5SS4HltC7s/aPvVNVkkZL7v8bCBpVi5fuUUtXnDrQtn6foyT1JFldVctnup9PyJEkqWE4SpLUMBwlSWoYjpIkNQxHSZIac/WrHJple++6Pau8C1WS5oUjR0mSGoajJEkNw1GSpIbhKElSw3CUJKlhOEqS1DAcJUlqGI6SJDUMR0mSGoajJEkNw1GSpIbhKElSw3CUJKlhOEqS1DAcJUlqGI6SJDUMR0mSGoajJEkNw1GSpIbhKElSw3CUJKnxkGEXoMGsvfl2xo6/cNhlaAStO/mQYZcgbXYcOUqS1DAcJUlqGI6SJDUMR0mSGoajJEkNw1GSpMYWFY5JdkvyhSTfT/LDJKclWZxkZZLTmm0vSbK8W16XZG2SNd3ro137p5Lc0Nf+ra59ZZKNSfbpO95VSca65T/ojndl1374fPWBJGl6W8zvOSYJ8DfA6VV1eJJFwBnAKcDlAxziuVW1foL2t1bV/56g/SbgRODIpo7duvZ9q+r2JA8Hdp7BR5EkzbEtaeT4PODnVXUWQFVtAN4EvBp4+Byc7wLgyUme1LTvAtwJ3NXVcVdV3TAH55ckbaItKRyfDKzub6iqO4B1DDaCvrhv+vRNfe3v72v/q772jfRGpe9ojnMF8FPghiRnJfndmX4QSdLc2mKmVYEANUn79pPs07/9TKdVAc4FTkyy+30HrNqQ5CDg6cDzgQ8neVpVnfSAwpJjgGMAFm3nzKskzZctaeR4NbC8vyHJdsCjgcuARzXb7wBMFIYDq6pfAR8E3t60V1VdWlXvBY4CXjbJ/mdU1fKqWr5oyWT5LUmabVtSOF4ELEnyaoDuhpwPAqcBlwLPTvKYbt1yYDHw41k476eAF9DddJPkN5Ls27d+GXDjLJxHkjRLtphp1aqqJC8FPpbkT+iF1flV9W6AJMcBX06yFb2bZY6uqo19h7g4yYZu+cqqenW3/P4k7+zb7hnNee/tfvXjI13T1sAHkvwG8HPgNuC1s/dJJUkPVqomugy3+UvyLOA84D9U1erpth+2xUv3qKUrTh12GRpBfmWVNLkkq6tq+fRb3t8WM3JsVdW3gMcNuw5J0ujZkq45SpI0EMNRkqSG4ShJUsNwlCSpscXekLPQ7L3r9qzyrkRJmheOHCVJahiOkiQ1DEdJkhqGoyRJDcNRkqSG4ShJUsNwlCSpYThKktQwHCVJahiOkiQ1DEdJkhqGoyRJDcNRkqSG4ShJUsNwlCSpMfD3OSZ5FjDWv09VnT0HNUmSNFQDhWOSc4AnAGuADV1zAYajJGmzM+jIcTmwV1XVXBYjSdIoGPSa41XAY+ayEEmSRsWUI8ckX6I3ffoI4JoklwK/GF9fVYfNbXmSJM2/6aZVPzAvVUiSNEKmDMeq+j/zVYimtvbm2xk7/sJhlyGx7uRDhl2CNOcGvVv1TnrTq/1uB1YBb66qH852YZIkDcugd6t+CPgJcC4Q4Ch6N+hcB5wJHDgXxUmSNAyD3q16UFV9oqrurKo7quoM4OCqOh941BzWJ0nSvBs0HDcmOSLJVt3riL51/u6jJGmzMmg4/j7wKuBW4Kfd8iuTbAu8YY5qkyRpKAa65tjdcPO7k6z+h9krR5Kk4Rv0btWzmGD6tKr+YNYrkiRpyAadVr0AuLB7XQRsB9w1V0UBJKnugefj7x+S5LYkFzTbfSHJt5u2k5LcnWSXvra7Jlru3q9MclrTdkWS87rl1yRZ073uTbK2W/5lX/uGvuU/7vb7SJKbk2zVnGtjkn362q5KMrZpPSVJmm2DTqt+tv99Fxp/NycV/dq/Ak9Jsm1V3QO8ELi5qeORwL7AXUl2r6ob+lavB94MvH2mJ07yW/T+43BAkodV1VnAWd26dcBzq2p9s89dVbWs7/1WwEuBHwMHAJf0bX4TcCJw5ExrkyTNvU39suM9gN+czUIm8RVg/HEcRwPnNetfBnwJ+Ay9373sdyZwZJIdNuG8rwDOAb4GbOrzY59L74Htp9Orvd8FwJOTPGkTjy1JmkMDhWOSO5PcMf6TXiDNeES2CT4DHJVkG2Af4DvN+vHAPI8HBtBd9ALyuAmOu23fFOga4F3N+iOB8yc57qDGa/sccGiSrfvWbQROAd4x1QGSHJNkVZJVG+6+fRPLkCTN1EDhWFWPqKrt+n7+u3aqdS5U1ZXAGL2g+XL/uiSPBp4I/ENVfQ/4VZKnNIf4KLAiyXZN+z1VtWz8Bfxp33GfDtxWVTfSu766b5IZPeggyUOBg4HPV9Ud9EL9Rc1m5wL7Jdl9suNU1RlVtbyqli9asv1MSpAkPQiDPj6OJIfRu3YGcElVXTDV9rPoi/S+HeRAYMe+9iPpPZ3nhiTQu0noKOCd4xtU1c+SnAu8bgbnOxrYs7u2OH7clwF/MYNjHARsD6ztalsC3E3vhqbx2n6V5IPMzwhckjQDg06rnkxvevKa7nVckvfOZWF9zgTeVVVrm/aj6T3WbqyqxoCn8cDrjtB7LuyxDPAfge4mmpcD+/Qd93BmPrV6NPCHfcfYHXhRkiXNdp8CXgDsPMPjS5Lm0KA35BwMvLCqzqyqM+mNjOble2uq6qaq+kh/W/drD78J/N++7W4A7kjyzGb/9fSu+y0e4HQHADdXVf9dsd8E9kqydJB6uwB8MfcfJf4rvYcl3O9BClV1L72p312QJI2MVE3/aNQkVwIHVtU/d+93oDe1us/Ue2q2LF66Ry1dceqwy5D8PkctKElWV9Xyme436DXH9wCXJ7mY3ldWHQCcMNOTSZK0EAx6HW4jsB/wdHrh+Paq+v9zXJskSUMxbThW1cYkb6iq/0XvzlFJkjZrg96Q8/Ukb0ny2CQ7jL/mtDJJkoZk0GuO49++8fq+tgIeP7vlSJI0fIM+eHzSp7hofuy96/as8i5BSZoXg36f49bAf6bvCTnAJ6rql3NUlyRJQzPotOrpwNbAx7v3r+ra/nAuipIkaZgGDcenV9VT+95/I8kVc1GQJEnDNujdqhuSPGH8TZLHAxvmpiRJkoZr0JHjW4GLk/ywez8GvGZOKpIkacgGHTn+I/AJek/K2dgtf3uuipIkaZgGHTmeDdwB/Lfu/dHAOfS+3kmSpM3KoOH4pOaGnIu9IUeStLkadFr18iT7jb/pvjPxH+emJEmShmvKkWOStfQeE7c18OokP+rePw64Zu7LkyRp/k03rXrovFQhSdIImTIcq+rG+SpEkqRRMeg1R0mSthiGoyRJDcNRkqSG4ShJUsNwlCSpYThKktQwHCVJahiOkiQ1DEdJkhqDfiuHhmztzbczdvyFwy5DkubVupMPGcp5HTlKktQwHCVJahiOkiQ1DEdJkhqGoyRJDcNRkqTGgg3HJBuSrElyRZLLkjyrax9Lck+37pokZyfZum+//ZNcmuTa7nVMc9xj+tZdmmT/vnWXJFned57vJ3lxkgOTXNC1b9Ptu3fffm9L8ufdPpXkj/rWnZZk5Zx1lCRpxhZsOAL3VNWyqnoqcALw3r5111fVMmBvYDfgCIAkjwHOBV5bVXsC+wPHJjmkW38ocCywf7f+tcC53X73SbIb8LfAm6vqb/vXVdXPgTcCH0/Prt0xT+g2uRU4LslDZ6sjJEmzayGHY7/tgH9pG6tqA3ApsGvX9HrgU1V1Wbd+PfA24Phu/duBt3btdNt9uttv3GOArwHvrKovTlRMVX0VuAV4NfBh4KSqGq/vNuAiYMUmfVJJ0pxbyE/I2TbJGmAbYCnwvHaDJNsAzwSO65qeTC/s+q3q2sfXr55gfX+QnU0vGP96mvreSC+Yv19V5zTrTga+kuTMaY4hSRqChTxyHJ9W3RM4CDg7Sbp1T+iC85+AH1XVlV17gJrgWBO1jWv3+TvgVUmWTFVcVf0E+AZw+gTrbqAXnK+Y6hjd9c9VSVZtuPv2qTaVJM2ihRyO96mqbwM7ATt3TePXHJ8I7JfksK79amB5s/vTgGu65Wu69/327VsPcArwHeCvk0w38t7YvSbyHnrTuJP+GVTVGVW1vKqWL1qy/TSnkiTNls0iHJPsCSyiN1K8T1XdQu964vjNMB8DViZZ1u23I/A+eoFH9/N9XTvddiuBjzenfBNwB/CXfaPVGamqa+mF7qGbsr8kae5sDtccoTf1uaKqNkyQVZ8HTkrynKr6+ySvBD6Z5BHdfqdW1ZcAquqL3d2l30pSwJ3AK7uQvU9VVZIVwAX0AvVC4PlJburb7OUDfIZ3A5fP5ENLkuZeqqa63KZRsXjpHrV0xanDLkOS5tWD/cqqJKurqr2cNq3NYlpVkqTZZDhKktQwHCVJahiOkiQ1DEdJkhoL+Vc5tih777o9qx7kXVuSpME4cpQkqWE4SpLUMBwlSWoYjpIkNQxHSZIahqMkSQ3DUZKkhuEoSVLDcJQkqWE4SpLUMBwlSWoYjpIkNQxHSZIahqMkSQ3DUZKkhuEoSVLDcJQkqWE4SpLUMBwlSWoYjpIkNQxHSZIaDxl2ARrM2ptvZ+z4C4ddhqRNsO7kQ4ZdgmbIkaMkSQ3DUZKkhuEoSVLDcJQkqWE4SpLUMBwlSWoYjpIkNbb4cEyyIcmaJFckuSzJs7r2sST3dOuuSXJ2kq379ts/yaVJru1ex/StOynJ3Ul26Wu7q/v52CQ3JNmhe/+o7v3j5u9TS5KmssWHI3BPVS2rqqcCJwDv7Vt3fVUtA/YGdgOOAEjyGOBc4LVVtSewP3Bskv7f9F0PvLk9WVX9GDgdOLlrOhk4o6punN2PJUnaVIbj/W0H/EvbWFUbgEuBXbum1wOfqqrLuvXrgbcBx/ftdiZw5PgIsfFhYL8kb6QXrB+ctU8gSXrQfHwcbJtkDbANsBR4XrtBkm2AZwLHdU1PBj7dbLaqax93F72APA74s/4Nq+qXSd4KfBV4UVXdO1Fh3VTtMQCLttt5Zp9KkrTJHDn+elp1T+Ag4Owk6dY9oQvOfwJ+VFVXdu0BaoJjtW0fBVYk2W6CbV8C3AI8ZbLCquqMqlpeVcsXLdl+Bh9JkvRgGI59qurbwE7A+DBt/JrjE+lNgx7WtV8NLG92fxpwTXO8n9G7Nvm6/vYky4AXAvsBb0qydDY/hyTpwTEc+yTZE1hEb6R4n6q6hd71xBO6po8BK7uQI8mOwPuAUyY47IeAY+mmsLtR6enAG6vqR8D7gQ/M+oeRJG0yw7G75thNn54PrOhuwGl9HliS5DldWL4S+GSSa4FvAWdW1ZfanbqbdT4HLO6a/hO9Kdqvd+8/DuyZ5Hdm92NJkjZVqia6dKZRs3jpHrV0xanDLkPSJvD7HIcnyeqqai+DTcuRoyRJDcNRkqSG4ShJUsNwlCSpYThKktTw8XELxN67bs8q73iTpHnhyFGSpIbhKElSw3CUJKlhOEqS1DAcJUlqGI6SJDUMR0mSGoajJEkNw1GSpIbhKElSwy87XiCS3AlcN+w6prETsH7YRQzAOmfPQqgRFkadC6FGWHh1Pq6qdp7pzj5bdeG4blO+zXo+JVk16jWCdc6mhVAjLIw6F0KNsOXU6bSqJEkNw1GSpIbhuHCcMewCBrAQagTrnE0LoUZYGHUuhBphC6nTG3IkSWo4cpQkqWE4SpLUMBxHXJKDklyX5AdJjh92Pf2SrEuyNsmaJKu6th2SfD3J97ufjxpCXWcmuTXJVX1tk9aV5ISuf69L8uIh1nhSkpu7/lyT5OAh1/jYJBcn+W6Sq5Mc17WPWl9OVueo9ec2SS5NckVX53/t2kemP6eocaT6su/ci5JcnuSC7v3s9WVV+RrRF7AIuB54PPBQ4Apgr2HX1VffOmCnpu0U4Phu+XjgfUOo6wBgX+Cq6eoC9ur6dTGwe9ffi4ZU40nAWybYdlg1LgX27ZYfAXyvq2XU+nKyOketPwM8vFveGvgOsN8o9ecUNY5UX/ad/78A5wIXdO9nrS8dOY62ZwA/qKofVtW9wGeAw4dc03QOBz7dLX8a+L35LqCqvgn8c9M8WV2HA5+pql9U1Q3AD+j1+zBqnMywarylqi7rlu8Evgvsyuj15WR1TmZYdVZV3dW93bp7FSPUn1PUOJmh9CVAkt2AQ4C/aOqZlb40HEfbrsCP+97fxNR/6edbAV9LsjrJMV3bo6vqFuj9owXsMrTq7m+yukatj9+Q5Mpu2nV8SmjoNSYZA36b3khiZPuyqRNGrD+7acA1wK3A16tq5PpzkhphxPoSOBV4G7Cxr23W+tJwHG2ZoG2Ufvfm2VW1L/AS4PVJDhh2QZtglPr4dOAJwDLgFuCDXftQa0zycOCzwBur6o6pNp2gbZh1jlx/VtWGqloG7AY8I8lTpth8KHVOUuNI9WWSQ4Fbq2r1oLtM0DZlnYbjaLsJeGzf+92Anwyplgeoqp90P28FPkdvmuKnSZYCdD9vHV6F9zNZXSPTx1X10+4fpo3AJ/n1tM/QakyyNb3A+auq+puueeT6cqI6R7E/x1XVz4BLgIMYwf5saxzBvnw2cFiSdfQuNz0vyf9kFvvScBxt/w/YI8nuSR4KHAV8ccg1AZDkYUkeMb4MvAi4il59K7rNVgBfGE6FDzBZXV8EjkqyOMnuwB7ApUOob/wv87iX0utPGFKNSQL8JfDdqvpQ36qR6svJ6hzB/tw5ySO75W2BFwDXMkL9OVmNo9aXVXVCVe1WVWP0/l38RlW9ktnsy/m6q8jXJt+NdTC9u++uB04cdj19dT2e3t1fVwBXj9cG7AhcBHy/+7nDEGo7j97Uzy/p/Y/xP05VF3Bi17/XAS8ZYo3nAGuBK7u/zEuHXOP+9KaergTWdK+DR7AvJ6tz1PpzH+Dyrp6rgD/t2kemP6eocaT6sqn5QH59t+qs9aWPj5MkqeG0qiRJDcNRkqSG4ShJUsNwlCSpYThKktQwHCVJahiOkiQ1/g1z5SzhzayS5wAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create query to get hpd311calls records about safety\n",
    "query = \"\"\"SELECT *\n",
    "\t\t     FROM hpd311calls\n",
    "            WHERE complaint_type = 'SAFETY';\"\"\"\n",
    "\n",
    "# Query the database and assign result to safety_calls\n",
    "safety_calls = pd.read_sql(query, engine)\n",
    "\n",
    "# Graph the number of safety calls by borough\n",
    "call_counts = safety_calls.groupby('borough').unique_key.count()\n",
    "call_counts.plot.barh()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering on multiple conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           latitude     longitude     elevation       awnd      prcp  \\\n",
      "count  2.500000e+01  2.500000e+01  2.500000e+01  25.000000  25.00000   \n",
      "mean   4.077898e+01 -7.396925e+01  4.270000e+01   7.739600   0.17600   \n",
      "std    2.175584e-14  4.351168e-14  1.450389e-14   2.621778   0.36947   \n",
      "min    4.077898e+01 -7.396925e+01  4.270000e+01   3.130000   0.00000   \n",
      "25%    4.077898e+01 -7.396925e+01  4.270000e+01   5.820000   0.00000   \n",
      "50%    4.077898e+01 -7.396925e+01  4.270000e+01   7.830000   0.00000   \n",
      "75%    4.077898e+01 -7.396925e+01  4.270000e+01   9.170000   0.09000   \n",
      "max    4.077898e+01 -7.396925e+01  4.270000e+01  12.970000   1.41000   \n",
      "\n",
      "            snow       tmax       tmin  \n",
      "count  25.000000  25.000000  25.000000  \n",
      "mean    1.332000  27.320000  17.160000  \n",
      "std     2.685256   7.122266   7.673982  \n",
      "min     0.000000  13.000000   5.000000  \n",
      "25%     0.000000  22.000000  11.000000  \n",
      "50%     0.000000  28.000000  17.000000  \n",
      "75%     1.200000  31.000000  20.000000  \n",
      "max     9.800000  40.000000  33.000000  \n"
     ]
    }
   ],
   "source": [
    "# Create query for records with max temps <= 32 or snow >= 1\n",
    "query = \"\"\"\n",
    "SELECT * \n",
    "  FROM weather\n",
    " WHERE tmax <= 32\n",
    "    OR snow >= 1;\n",
    "\"\"\"\n",
    "\n",
    "# Query database and assign result to wintry_days\n",
    "wintry_days = pd.read_sql(query, engine)\n",
    "\n",
    "# View summary stats about the temperatures\n",
    "print(wintry_days.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting distinct values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          borough    complaint_type\n",
      "0           BRONX    HEAT/HOT WATER\n",
      "1       MANHATTAN          PLUMBING\n",
      "2       MANHATTAN    HEAT/HOT WATER\n",
      "3        BROOKLYN    HEAT/HOT WATER\n",
      "4          QUEENS    HEAT/HOT WATER\n",
      "..            ...               ...\n",
      "60      MANHATTAN  OUTSIDE BUILDING\n",
      "61      MANHATTAN          ELEVATOR\n",
      "62       BROOKLYN  OUTSIDE BUILDING\n",
      "63  STATEN ISLAND            SAFETY\n",
      "64  STATEN ISLAND  OUTSIDE BUILDING\n",
      "\n",
      "[65 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Create query for unique combinations of borough and complaint_type\n",
    "query = \"\"\"\n",
    "SELECT DISTINCT borough, \n",
    "       complaint_type\n",
    "  FROM hpd311calls;\n",
    "\"\"\"\n",
    "\n",
    "# Load results of query to a data frame\n",
    "issues_and_boros = pd.read_sql(query, engine )\n",
    "# Check assumption about issues and boroughs\n",
    "print(issues_and_boros)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Counting in groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAe0AAAD4CAYAAADB2L5nAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAAgAElEQVR4nO3dd7xcVbn/8c+XEEhi6D0JcCgBJJQY4qVzCSACooDSIl5AroA/QQWkBILeqDQhVGnSRJAqRZpSk1wFIhAgpNBLhNCLl5aAITy/P9aaZJ/JzDkzyZmczOH7fr3mdWbW3nvttSbKM2vtvdejiMDMzMwWfAt1dgPMzMysNg7aZmZmTcJB28zMrEk4aJuZmTUJB20zM7MmsXBnN8C6tmWXXTZaWlo6uxlmZk3l0UcffScilisvd9C2hmppaWHcuHGd3Qwzs6Yi6Z+Vyj09bmZm1iQctM3MzJqEp8etoSa++j4tw+7osPqmnPKNDqvLzKzZOGh3EElnAv+MiLPy57uAVyLiB/nz6cCrEXGGpIWBN4CLI+JYScOBPXJV6wMT8/vLgKWBA4G3C6fbGhgI3AK8CPQEbo+IIyu0a2vgyIjYuax8DLASMD0XPR8Ruxe2PwE8GRFDC2WX5/PcIGlp4D7gnIj4fa3fk5k1vxkzZjB16lQ++eSTzm5K0+vRowf9+vWje/fuNe3voN1xHiQF3rMkLQQsCyxe2L4ZcFh+vz3wDLCnpOMi4kTgRABJH0XEwNJBkkYAZ0bEyOLJJAH8PSJ2ltQTeFzSzRHxQB1t3ici5rhLTNKXSZdOtpL0pYj4uGz7EsBdwEUO2GZfPFOnTmWxxRajpaWl9N8imwsRwbvvvsvUqVNZbbXVajrG17Q7zgOkwAwwAJgEfChpKUmLAl8GHs/bhwJnAy8Dm8zriSNiOjAe6DuvdWXfBa4E7ga+VbatN/BX4OqIuKCDzmdmTeSTTz5hmWWWccCeR5JYZpll6pqxcNDuIBHxGvCZpFVIwXss8BCwKTAYmBAR/86j4m2B24FrSAG8PYdLGp9fo8s3SloK6A/8rc5mX1Wo97RC+V7AdVXadwZwf0ScWa1SSQdJGidp3Mxp79fZJDNrBg7YHaPe79FBu2OVRtuloD228PnBvM/OwOiImAbcCOwmqVs79Z4ZEQPza0ihfEtJE0jXx2+PiDfqbO8+hXqPApD0VeDtiPgn6Zr1oPyjoGQUsIuk5atVGhEXRcTgiBjcrdcSdTbJzMyq8TXtjvUgKUCvT5oefwX4GfAB6aYySCPXzSVNyZ+XAYYA987F+UrXtNcC7s/XtMfPQ/tL7Vun0L7Fge8Al+TP1wL3A3+RNCQiPpzH85lZk+vIJ0TAT4m0xSPtjvUAaST9XkTMjIj3gCVJU+RjJS0ObAGsEhEtEdECHEJtU+RVRcSzwMnAMfNST76Bbg9gg0L7dilvX75D/j7gZkmLzMs5zczmxhtvvMHee+/NGmuswbrrrstOO+3Es88+y+TJk9lmm21Ya6216N+/P7/+9a+JCABGjBjByJGt7umlpaWFd955B0hT1T/72c9mbRs5ciQjRozgxBNPZODAgQwcOJBu3brNen/OOecAcNZZZ3HFFVfMOu7yyy9nypQps8575JFHMmrUqA7pt0faHWsi6a7xq8vKekfEO5L2B0ZFxKeF7bcAp0patKy86HBJ3yt83rXCPhcCR0paLSJeKtu2raSphc+lx8uuklR65Osd4ATSY2mvFvb9G7CupJWKFUbEMZJ+D1wpaWhEfF6p4ev3XYJx/tVsZh0oIthtt93Yb7/9uPbaawEYP348b775Jvvvvz8XXHAB22+/PdOmTeM73/kO559/Pocccki79S666KLcdNNNHHvssSy77LKzyocPH87w4cMB6N27N+PHz57Q/Oyzz7jssst47LHHePXVV/nFL37Bqquuyv3338/JJ5/M7373O3784x9z4IEHss0228xz3x20O1BEzKT1Y15ExP6F95cDl5dtfw9YrvC5d9n2EcCICqebAowp7DedCnePR8QY0nPc5bauUAZld7PnPpUC9v5l275fpQ4zs4YZPXo03bt354c//OGssoEDB3LppZey+eabs/322wPQq1cvzj33XLbeeuuagvbCCy/MQQcdxJlnnsmJJ55YU1tGjRrFoEGDWHjhhenbty8nnXQSG2+8Meuttx633norAKuuuirvvvsub7zxBiuuuOJc9Hg2T4+bmVlTmTRpEhtttNEc5ZMnT56jfI011uCjjz7igw8+qKnuQw45hKuuuor336/tyZcHHnhg1jlfe+01jj/+eA444AD22muvVj8UBg0axAMP1LOMRmUO2mZm1iVERNVHqCS1ua1k8cUXZ9999511vbo9r7/+OsstlyZL+/Tpw8UXX8wqq6zClltuyfnnnz9rv+WXX57XXnut1q5U5aBtZmZNZcCAATz66KMVy8tTAb/44ov07t2bxRZbjGWWWYZ//etfrbZ/+OGHLLnkkq3KDjvsMC699FI+/rjVYpAV9ezZc47FUfbff/85Vov75JNP6Nmz0pXK+viatpmZzZP5/YjWNttsw3HHHcfFF1/MgQceCMAjjzxC//79Oemkk7j33nvZbrvtmD59Oj/5yU84+uijAdhqq63YZ599GDZsGIstthg33XQTG264Id26tV4qY+mll2bPPffk0ksv5YADDmizLV/+8pd5/vnn223zs88+yx577NHufu3xSNvMzJqKJG6++Wbuuece1lhjDQYMGMCIESPo06cPt9xyCyeccAJrr70266+/Pl/96lc59NBDAdhggw049NBD2WKLLRg4cCAXXnghl1xyScVz/OxnP5v1KFhbdtxxR/72t7YXo5wxYwbPP/88gwcPrr+zZTzSNjOzptOnTx+uv/76itvGjBlT9biDDz6Ygw8+uOK2jz76aNb7FVZYgWnTprW5D6Q7w5dZZhmee+45+vfvX7He22+/nd13352FF573kNuwoC2phbS05nqFshHARxExMqd5/BqwekR8KmlZYFxEtORFPs4CtgEC+ATYs/T8saTlgNeAQyPid4X6pwCPRsR38ufdgZ0jYv/8jPRg4HWqpMGMiHMknQ3sDqxcevY4H3sa8CrQA/gdcAEpScceETEx73d07s/s5xBS+Yq5P18FPiU9rnVYRDwraQDwW6AfIOAK4ISIiHzey4CBETEh1zUp92lK7m9pRbJuwE3Ar/P32UJa3/xnwG/yPmvmPkwHJuS6Z6XtlLQr8CtgEWAG8POI+HPeVvXfizZ0dD7tjuIVl8yso5xyyim8/vrrVYP2Z5991mrRlnnR2dPjM4FKFwz2AvqQVuZaH9gN+L/C9j2Af1B5JbHBORBWFBEnltbbBqYX1t4+J/9Y2I20/OhWZYdel4/ZHBhOerb6MOB8JX2Bg4Fjiwcp3YlwMzAmItaIiHWB44AVcvKQW4FTImItYEPSMqg/KlQxNZ+vmiH5O/oPYHXgorL+3lXo7zhmrze+b1k7NwRGArtExDqk7F4jJW1Q2K3av5eZfcGUVvsyWHvttdlqq/KQMdsee+wxx81uJfV+j50dtM8irfZVPuJfCXi9NNKNiKkRUbzlbyhpBNkvB8uikaSgODeGkNYMv4AqS4tGxLvA88BKEXEnaeS+L3AmMKKsnaU6Z0TEhYU6xkfE30kpMB+IiLtz+TTgUGBY4fjbgQGS1m6r4RHxEfBDYFdJS9fY36IjgZNKsxn578nAUYV9qv17mdkXSI8ePXj33XcduOdRKZ92jx49aj6ms//j+zIp+cR/AbcVyq8nJcDYkrTG9R8j4nEASSsDK0bEw5KuJ43Kzyg79keS1pyL9gwlpaO8BThJUveImFHcIafe7EGaXoY02n4YeC4irqxQ53rAnM8mJAPKt0XEC5J653XKAT4HTiX9ENmvrcZHxAeSXiKl6XyzrX2rtGVkWdk40troJdX+vczsC6Rfv35MnTqVt99+u7Ob0vR69OhBv379at6/kUG72k+w8vKTSFPEsy58RsTUPLLcJr/uk7RHRNwH7E0KzJAyTl1K66A9k3T9+Vjgr7U2Nie+2Ak4PCI+lPQQsH2hXXtJGgKsDRwYEZ/ktr4maRRpRFwvUdv3dDUwXNJqNdY5Nyq1pVLZHP9ec1QkHQQcBNBt8eWq7WZmTap79+6stlot/zmyjtbI6fF3gaXKypYmJaaYJSKeJ93QtWdZ+acR8dec5/kkZifJGArsn2/CuhXYUFL51f8rSdekV6mjvTsASwATc91b0HqK/LqIGABsCZyeby4r+Ty/KpkMzLne3uxtrZ4BkLQ66Wa9WSkvI+Iz4HTayeIlaTGgBXi2rf1qbQswCHiyWFDt36tsH+fTNjNrgIYF7XyN9XVJ2wLk66w7kKZXy51IuqZK3neQpD75/ULABsA/8+j7SxHRt5A68mTS6Lt47hmka8yH1dHkocAPCvWuBmwvqVdZ3WNJPwp+WmO9o4BFJR1Y6N9XJf0ncBWwhaTtcnlP4BzSdHi5y4HtKCQXKZLUGzgf+HOF6+q1GAkcm+86L939fxzpx0K5Vv9eZmY2fzT6RrR9geMljScFr19GxAvlO0XEZOCxQtHywG358aYJwGfAuaTAenPZ4TdS+aaxS6lx+j8H5q/Teor+Y9IPjG9WOOQ3wPfzyLZNke7U2A34mqQXJE0mZe16LWfm2oX0HT1DevzsEVJfy+v5NymgL1+2aXT+nh4mXXOu/ABi++0cTxrJ3ybpadI166Nzefm+5f9eZmY2H8h3/1kjDR48OMrXAjYzs7ZJejQi5lhCrbMf+TIzM7MaOWibmZk1CQdtMzOzJuGgbWZm1iQctM3MzJqEg7aZmVmT6Oy1x62Lq5aa06kxzczq56DdhCQNJ2UIm0laPvXgiHgoZ996A7g4Io4t7D+GlDltei46ISJukDST2fnEIa3lvjFpNbjepNXXXsrb3gMeiYhjcp2rAqOBQRFRTJtqZmYN4qDdZCRtCuxMCpafSloWWCRv3h54BthT0nHReuWcfSKifJWT6TnPdqXzbA0cGRE75889gcclXR4RTwFnAz93wDYzm398Tbv5rAS8ExGfAkTEOxHxWt42lBRMXwY26ciT5iVXjwDOl7QjsFhEXNWR5zAzs7Y5aDefu4GVJT0r6fyceKQ0Et6WlCL0GuZcj/0qSePza5lc1rNQNl7SXm2dOCL+QpomvwL4UbX9JB0kaZykcTOnvT93vTQzszl4erzJRMRHkjYipQgdAlwnaRjwMTA6IqZJuhH4uaTDI2JmPrSu6fE2nAf0jIhn2mjjRcBFAIuu1N+L25uZdRAH7SaUA/EYYIykicB+wAxg85wLHGAZUlC/t4NP31bucDMzayBPjzcZSWtL6l8oGgi8DWwBrFLIB34IlVOWmplZk/JIu/n0Bn4raUlSnvHngQeBXqWb07JbgFMlLdpGXT1zrvOSOyNiWEc2dv2+SzDOz2SbmXUI59O2hnI+bTOz+jmftpmZWZNz0DYzM2sSDtpmZmZNwkHbzMysSThom5mZNQkHbTMzsybh57Stoarl015QOK+3mTUTj7Q7iaSZOUnHJEl/ktQrl39Utl+LpEllZSMkHZnfXy5pmqTFCtvPlhQ5bWfxXE9IekzSZuV1S9o6H/PNQj235xSdSFpY0kmSniskGBnekC/HzMwqctDuPNMjYmBErAf8G/jhPNT1PLALgKSFSGuOv1rhXBsCxwInV6lnKlAtEJ8A9AHWz0lGtgS6z0ObzcysTg7aC4a/A2vOw/HXAKW0mlsDD5CWOK1kceBfVbY9Abwv6WvFwjwLcCDw44j4BCAiPoyIEfPQZjMzq5ODdieTtDCwIzBxHqp5DlhO0lKkJCHXlm0v5c1+GrgE+HUbdZ0AHF9WtibwckR8WEtjnE/bzKwxHLQ7TylZxzjgZeDSKvtVWxy+vPwmYG9gY9LIvag0Pb4OsANwhSRVrDTi7wCStqzWcEnfzz8CXpG0coU6LoqIwRExuFuvJapVY2ZmdfLd451ner423J53gaXKypYGXioruxZ4DPhDRHxeJSYTEWPzDWrLtXHOE0nXtktT7M8Dq0haLE+L/x74fb6JrVsNfTAzsw7gkfYCLiI+Al6XtC2ApKVJo+X7y/Z7mRRoz2+rPknrkALtu22c827SD4UN8+dppJmAcyX1yPV0AxaZu16Zmdnc8Eh7wdNL0tTC5zOAfYHzJJ2ey34ZES+UHxgRv6tSZzFvtoD9ImJmtdF4diIpJ3fJcNK18EmSPgSmA38AXmurEufTNjPrOM6nbQ3lfNpmZvVzPm0zM7Mm56BtZmbWJBy0zczMmoSDtpmZWZNw0DYzM2sSfuTLGqqzU3M69aaZdSUeaVfRRurMhSW9I+nksv3HSBqc30+RdGNh2+45hWZp+c/xkv4taWJ+f0rer7ukR9s5f6vUnWVtOFvSqznTV6lshZxi8wlJT0r6i6T1C+14T9JL+f29OV3n9ML28ZL2LfRroqQJkv5X0qod942bmVl7HLSrq5Y6c3vgGWDPaut3Z4MlDSgWRMTvc50DSYuSDMmfh+VdtgAebOf8FeVAvRvwCrBVYdOvgHsiYsOIWBcYFhETC+24FTgqf94uH/NCaXt+XVGob0hEbACMYc7EImZm1kAO2rUpps4cCpxNSvKxSRvHjASOq/M8OwB/bef81QwBJgEX5DaWrETKkw1AREyos03VjAX6dlBdZmZWAwftdhRTZ0rqCWwL3E7KYT20jUOvBwZJqidP9hDSCLbi+ds5dmhu083AzpK65/LzgEsljZY0XFKfGtqxRtn0eKWMXzsAf650sFNzmpk1hoN2dZVSZ+4MjM4JNG4EdsuJMyqZCZwGHFvLyXIwfS/XXe381Y5dBNgJ+HNEfAA8RJrGJyLuAlYHLgbWAR6X1FaGL5hzeryY6nO0pLeA7YCrKx3s1JxmZo3hu8ermyN1pqShwOaSpuSiZUij43ur1HElKWhPruF8OwJ3tXX+NuwALEGaDQDoBUwD7gCIiPdIAfZqSbeTrnnfWLmqdg0BPgYuJ10vP2Iu6zEzszrVPdKW9KVGNGRBJ2lx0o1iq0RES0S0AIfQxhR5RMwAzgQOq+EU1a5n12Io8INCu1YDtpfUS9I2hTvPFwPWII3c51pETCf1ad+cKtTMzOaDmkfakjYDLgF6A6tI2hA4OCJ+1KjGLWC+DYyKiE8LZbcAp0patI3jLqWdu6zzFHv/iHi6hnaUp+48H/g6cHCpICI+lnQ/8E1gFVIe7M9IP9IuiYhH2jnHGoVUngCXRcQ5xR0i4nVJ15B+uPy6WkVOzWlm1nFqTs0p6SFgd+DWiPhKLpuUH0myeSBpC+B7EdHmY13NyKk5zczqVy01Z13XtCPilbJHk2fOa8MMIuJ+4P7OboeZmS3Y6gnar+Qp8sh3K/8EeKoxzTIzM7Ny9dyI9kPS9cu+wKvAwPzZzMzM5oOaR9oR8Q6wTwPbYmZmZm2oeaQtaXVJt0l6W9Jbkm6RtHojG2dmZmaz1TM9fjVpac6VgD7An0jLZpqZmdl8UE/QVkRcGRGf5dcfgdqeFzMzM7N5Vk/QHi1pWM63vKqko4E7JC29IKyKJalfnrJ/TtILObf0Innb/pLOLdt/jKTBkh7KSTFezlP/pSQZLZIOKOSPniRpl3zs5ZJ2L9TzTN7naUnnSlqycJ6Zap18Yxhlcn2lnNZPS/qfwrYpkpYtfN46L0Xaql+SRkg6skLdpfNPVsqpfURO41mprs8lbVA4dpKklvy+t6QL8nf7uKRHJR1Y/7+UmZnNrXoe+dor/z24rPwA0oi7065vKz08fhNwQUTsklcYuwg4ETiqrWMjYuNcx/7A4Ig4NH/uBwwHBkXE+5J6A9USbewTEePyj4STSSul/WfeVusa4kdFxA2SegBPSroiIl6q4bj2zDq/pOVJlzmWAP6nwr5TSX3eq8K2S4AXSSu3fZ6TjhzQAe0zM7Ma1XP3+GqNbMg82gb4JCJ+DxARMyUdDrxUHLXWaXngQ+CjXOdHpffVRMS/8wzE85I2jIgn5uK8PfLfj+fi2DZFxFuSDgIekTSiwi63A1tJWjsinikVSloD+A/guxHxea7rbeA3Hd1GMzOrrp67x8dJ+lFx6ncBMgB4tFiQU1S+DNSTz7roCeBNUuD/vaRv1nJQRMzMx66Ti3qWTY9XGsUCnJbX+54KXBsRb81lu9tr34ukf/flK2z+HDgVOK6sfADwRClgt0eFfNpvv/32PLXXzMxmq+ea9t6khVXGSbpW0tdVtqZpJxKVb4orlVe7Ya7qjXQ5+O5AWm/9WeDMKqPTau0pmV6Wm/q6KscclaexVwS2zavPVWvjvN4A2Na/29XAJpKqzqxIGp5/gLxWaXsxn/Zyy7WXutvMzGpVc9COiOcjYjiwFuk/7JcBL0v65QJwI9pkoNXC6kqpNFcGXgDeBZYqO2Zp4J22Ko3k4Yg4mfSj5TvtNSRfT1+fuVziNU/DjyGlAYU5295uu9tp3+qkNeMrjuQj4jPgdOCYQvGTwIalG9gi4sT8A2PxuW2HmZnVr6582vnO4tOB04AbSaPQD4BRHd+0utxHSlm5L8wKnKcDl0fENOARYHNJK+btg4FFgVeqVSipj6RBhaKBwD/baoSk7qQb0V6JiAlz0xFJCwMbk35sQArg/5W3dQO+B4yey7qXAy4Ezo2207tdDmxHvvEuIp4HxgEn5DaQb5hbUGZazMy+EOrJp/0o8H+k/NDDCnmlH5K0eSMaV6uICEm7AedL+jnpx8hfyNdmI+JNST8F/pJHix8BQ9u5RtsdGCmpD/AJ8DZp/fVKrpL0KemHwL3ALoVtPdU6N/WdETHHY1+ka9rHA4uQfoTclMt/DVwg6QlSkLwT+GOVdhwv6bDSh4joVzh/d+Az4ErgjCrHl477t6RzgLMLxT8g/Vh7XtJ7wHRaj8bNzKzB6smnvXq+ialYtloHPZZkXZTzaZuZ1U9V8mnXMz1+Q41lZmZm1gDtTo9LWof0yM8Skr5d2LQ4s58pNjMzswar5Zr22sDOwJJA8VnlDwEvY2lmZjaftBu0I+IW4BZJm0bE2Gr7STo2PxplZmZmDVDPc9pVA3a2xzy2xczMzNpQ13Pa7fAzuzaHia++T8uwOzq7GWZmXUJHBu2al9aU9FHZ5/IUk6/mZTInSfpWhfLnJN0kad0q9c9KnVko6yPphvx+oKSd6u1gG/1ZUtKPOqq+CvUXU3c+IWnbRp2rxvY0tL9mZlbZgjrSPjMvk7kHcFlp+cxSeUT0B64DRuVVvtoVEa9FRCmQDwTqCtp5pbJqlgTqDmKl1cVqVFqb/DDSqmadaa76a2Zm86aeLF9zrHpWVvanDmlRQUQ8RVrFa9kK264D7ga+W0tdklryyH0R4FfAXqWsW5K+JOkySY9IelzSLvmY/SX9SdJtwN2Seku6T9JjkiaW9gNOAdbI9Z0maWtJtxfOfa5Svm4kTZH0C0n3A3tI2l7S2Fznn5TydrdlLClxC5K65fM9ImmCpINzufI5n5R0h6S/lGYe8vmXze8HSxqT31f7DgZIejj3bYKk/uX9reX7NzOzeVfzMqbAb4FB1coi4qQ66ipf2nNp4NbynSRtTEoXWS2/42PMToFZk7xE5y+AwRFxaD7PScCoiDhAKfXow5LuzYdsCmwQEe/l0fZuEfFBDnz/kHQrMAxYL4+EkbR1O834JCK2yHXcBGwXER9LOgY4gvSjopodgD/n9/8NvB8RX5W0KPCApLuBr5Ae1VsfWIGU8OOydto0vMp38EPg7Ii4Kv/g6Vbe33JKObsPAui2uLN8mZl1lFoWV9kU2AxYTtIRhU2Lk/4DPjemF/+Dn0ehxeXaDpf0PdKz4HvltcUrNm8uz19ue+Bbko7Mn3sAq+T390TEe4XznSRpK9KPib6koFivUnrOTYB1ScEW0rrj1e7SP03SqaQ82JsU2r1B4fr9EkB/YCvgmpxe9DVJtSR0qfYdjAWGS+oH3BQRz1X5t5glIi4CLgJYdKX+85pG1MzMslpG2osAvfO+ixXKPyBl+WqEMyNiZA37fYWUfWpeCfhORDzTqjCN9D8uFO1Dyny1UUTMkDSFyqvCfUbrSw/l+5TqFOlHwdAa2ngUaVT+E+APwEb5+B9HxF1l7d6J6jcGFttWbFfF7wB4StJDwDeAuyT9AHgRMzOb79q9ph0R/xsRvwQ2iYhfFl5nRMRz86GNFUn6Dml0eM1cHP4hrX+A3AX8WHkIKekrVY5bAngrB+whwKpV6vsnsK6kRSUtAVS72/sfpJSha+bz9pK0VrVG56xkZwMLSfp6bvf/U0oJiqS1JH0J+Buwd77mvRIwpFDNFFLAh9b5wSt+B0r5t1+MiHNIlzA2qNBfMzObD+q5e3xRSRdJulvSqNKrYS2r7PB889NzpLzS20REtevdv5M0Nb/Kp5xHk4LqeEl7kdJfdgcmSJqUP1dyFTBY0jjSqPtpgIh4lzTFPUnSaRHxCnA9MCEf83ilynLb9weukTSBFMTbvEaf82CfABwNXEK6Xv1YbvfvSDMiNwPPAROBC4D/LVTxS+BsSX8HZhbKq30HewGT8j0I6wBXlPe3rfau33cJppzyjbZ2MTOzGtWTmvMJ0qNGj1L4j31EPNqYpllHkXQ5cHtEzPesbE7NaWZWP1VJzVnP3eOfRcQFHdgmMzMzq0M9Qfs2pVWwbgY+LRUW7qy2BVRE7N/ZbTAzs3lXT9DeL/89qlAWwOod1xwzMzOrpuagHRGrNbIhZmZm1rZaFlfZJiJGSfp2pe0RcVPHN8vMzMzK1TLS/k9gFPDNCtuCtOCHmZmZNVjNj3yZzY1FV+ofK+13Vmc3w8xsvprX9SmqPfJVV2pOSd+QdHTOUvWLnHijKaj2HN6l15KFfc/O2xeStH5hn/c0O8/1vYX975TUV9IYSYML5S154ZLS5y1yBq2n8+ugXD68cI6Zhfc/KRwrSe9IWip/XklSSNqisM/bkpbJ73fL29fJnx/Kdb6c9yudo0UpE9jEQtk5+ZgFKq+3mdkXTc03okm6EOhFWhLzEtK64w83qF2doeJ650q5vHcDXgG2iogxpHzcFRctkdQTWDoiXlUbiTUkrQhcDewaEY8pZUL/2mEAABilSURBVPy6S9KrEXEicGLe76NK2bRyEpWHSFnI/kJK6vJ4/nu/pLWBd/LqZQBDgfuBvYEREbFxrn9/ChnPchnAkIh4p0LTj4qIG5SWcb2IlKDEzMzmg3pG2ptFxL7Av/Ja5JsCKzemWQuUIcAk0nKgtST22BoYU8N+hwCXR8RjADlAHk1Ke1mrB0hBmvz3DNK/S+nzgwBKObo3J6Xy3LuO+tsyK6+3mZnNH/UE7en57zRJfYAZQDM9BtazOP3NnDmrDy9sH10oH0pKSnIzsLNyco427AjcWfh8VeGcfymUDyAtCVs0LpfX6kFmB+3/IOXZLv2Q2owU1AF2Be6MiGeB9ySV50WvZHTh+zi8wvZiXu9WJB0kaZykcTOnvV9rX8zMrB31LK5ye77OexrwGOnO8Usa0qrGaC+H9xzT45IWAXYCDo+ID/N09PbAHW2cZ3PgyMLnfSJiXK6vBbi9VD2V02fWc2fgw8BXlDJ7dY+IjyS9qJQ1bDPg9LzfUKB0N9i1+fNj7dRdbXq8Ul7v1h1wPm0zs4aoZ3GVUtanGyXdDvSIiK4+jNqBlI5zYr7O2wuYRpWgrZTG8pWI+HcNdU8m/Wi4tVC2ESlrV00iYpqk54EDmB2E/0H6obE88Ey+EW0bYD1JAXQDQtLRMXePDlTK621mZvNBLYurVFxUJW/r6ourDAV+EBHXAOQR7UuSekXEtAr7l0+Nt+U84CFJN0XE+Bxcf8Oc0/bteQA4DBiRP48F/gj8I9+stjspnebBpQMk/S+wBfD3Os8FpLzeks4G9pP09Yi4a27qMTOz+tQy0q60qEpJV1pc5XBJ3yt8/i7wdWBWsIuIjyXdT/pOrqtQxw7Aj2s5WUS8ns93saTFSNPlZ0XEbXW2+wHgp6RgDWnE3Y/Zly6GAqeUHXMjqX9tBe3RkkopWCfkmxCL7Q9JpbzeVYP2+n2XYJzzaZuZdQgvrtJBJC0KPFDpYfgvMufTNjOrn+Z1cRVJy0g6R9Jjkh5VWnBkmY5tZvOKiE8dsM3MrJHqeeTrWuBt4DukhVXepvIUsZmZmTVAPY98LV24gxzgBEm7dnSDzMzMrLJ6RtqjJe2ttP72QpL2pO3nlc3MzKwD1RO0Dyatlf3v/LoWOELSh5I+aETjzMzMbLZ6FldZrJENsa5p4qvv0zJs7iZk5jW1nZlZV1Nvas4NJH1L0rdLr0Y17ItK0gqSrs7LkT4qaWxOq7m1pPfVOn3odvmYkHR6oY4jJY3I7yumHS3U97hSWtDyJVyXkzRD0sFl5VNyRjIzM5vP6knNeRmwAWn5zc9zcVdaXKXTKa2V+mfgDxHx3Vy2KvAt4F/A3yNi5wqHfgp8W9LJVdYLr7SuOqX6cjrRxyXdHBGlJCN7kJZEHQr8rgO6Z2Zm86ieu8c3iYh1G9YSg7RG+L8j4sJSQUT8E/itpK3bOO4zUoKOw4Hh9Z40IqbnLGTFVJtDgZ8BV0vqGxGv1luvmZl1rHqmx8dKctBurAG0nX1ry7Jp7jUK284D9pG0RIXjqqUdBUDSUkB/4G/588rAihHxMHA9sFc9nXBqTjOzxqgnaP+BFLifkTRB0kRJExrVMANJ50l6QtIjuejvETGw8HqhtG9EfABcQcq+Ve7MwjFDCuVb5n/DN4DbI+KNXL43KVjD7FSeNYuIiyJicEQM7tar0m8IMzObG/VMj18G/BcwkdnXtK1jTSatOAdARBySb/qqdfHus0gj9d/XuH/pmvZawP35mvZ4UpBeQdI+eb8+kvpHxHM11mtmZg1Qz0j75Yi4NSJeioh/ll4Na9kX0yigh6T/VyjrVevBEfEeaYT83/WcNCKeBU4GjpG0NvCliOgbES0R0ZK37V1PnWZm1vHqGWk/Lelq4DbS3coAXT2f9nyV013uCpwp6WjS+u4fA8fkXbbMN4yVnBARN5RVczpwaFlZedrRSsvPXggcCRwH3Fy27UbSNHlpGdsJkkqzLddHxBHV+uTUnGZmHafm1JySKk25RkQc0LFNsq7EqTnNzOpXLTVnPSuifb9jm2RmZmb1qCefdj9JN0t6S9Kbkm6U1K+RjTMzM7PZ6rkR7ffArUAf0iIct1H7XcpmZmY2j+oJ2stFxO8j4rP8uhxYrkHtMjMzszL1BO13JH1PUrf8+h7wbqMaZmZmZq3VE7QPAPYkrZ71OrA74JvTzMzM5pN6ntP+NbBfRPwLQNLSwEhSMDerqN582s6hbWZWXT0j7Q1KARtmrb71lY5vUseTNLMs0UZLzid9e4V9B0gaJelZSc9J+nlOmVnavmtee/3pvP76roVtl0t6KZ/jCUnbFraNkTQ4v58i6cbCtt0lXV74vIOkh/M5xku6TtIqhe2bSrpYUi9JV+V2TJJ0v6RVC/18oyyX9iK15MkufF+TJN0maclcvpCkc3L5REmPSFptHv95zMysRvWMtBeStFTZSLue4zvT9IgYWCyQ1FK+U84rfSvw/yLibkm9SKuB/Qg4T9KGpNmFr0XESzlg3SPpxYgoJU85KiJukDSElC6zf5U2DZY0ICIml7VhPeC3wLci4qlc9i2gBXg577YDcCfwU+DNiFg/77c28Eapr5JGAB8Vc2lLqiVP9vRCHX8ADgFOJGX76kP6Afd5fuTv4yp1mJlZB6tnpH068KCkX0v6FfAgcGpjmtVpvgs8EBF3A0TENNKSoMPy9iOBkyLipbz9JdK63EdVqGssrfNTlxtJWjK03DH5HE+VCvKa738r7LMtcC+wEvBqYb9nIuJT2lbKk91PUlvtKyn2YyXg9Yj4PJ9vanH2xczMGqvmoB0RV5AyUL1JWhP72xFxZaMa1sF6FqaIy9fVLhoAPFosyOkve0tavNJ2UgauARXq2gH4cxvnuh4YJGnNCm2omlM7T2HPiIj3SZnXjpE0VtIJkqqN6kvH1pUnW1I30g+EWwtt/mb+Hk+XVPHyiJxP28ysIeoZaRMRT0bEuRHx24h4slGNaoDphXzSu7Wxn4Bqi7FHle3lZadJehH4I3BSG+eaCZwGHFu1MdIyOUA+K+nIXLw9UJoJGA+snutZGnhE0pfbOGetebJ75sQk7+Z678nnmwqsndv8OXBf8bp9ifNpm5k1Rl1B+wtgMtBqgXZJq5OuC39YaTswCCj+gDkKWBM4HvhDO+e7EtgKWKVQNjnXSUS8m68tXwT0ztt3JF3PJu/zUUTcFBE/Iv1Q2KmN8w0F9pc0hTR63rDK6Lx0TXtVYBHSNe3S+T6NiL9GxFGkHyWVMoaZmVkDOGi3dhWwhaTtYNaNaecw+9r9SODY0k1s+e9xpOv9s+RrvmeTbt77erWTRcQM4EzgsELxqcDwshFzr3w+ARsA4/PnzSUtld8vAqwLVMxxrrnIk52n4H8CHCmpu6RBkvrk+hbKbXFOdTOz+aRZ7v5ulG0lTS183gPYBfitpPOAbqTR8LmQpqMlHQPcJqk7MAM4Ok9Tt5JzY58AHA3c1UYbLiWNykvHTZT0U+AKSYuRpqhfBv4H2Ah4PGbnU10DuCAH84WAO0h3u1cylPbzZM8hIh6X9AQpuL8NXCxp0bz5YfJ3U43zaZuZdZya82lb55N0PPB8RFzb2W2plfNpm5nVT/OaT9s6X0Sc0NltMDOzzuNr2mZmZk3CQdvMzKxJOGibmZk1CQdtMzOzJuGgbWZm1iQctDuR5kwZOiyXz0rjWdh3a0nvl+2/Xd7362X7Hibp/Py+VSpOSeflY5+UNL1Q1+5KjldKSfqspNGSBhTqnZJTck6Q9L+SVm2vjxNf9drjZmYdxY98da45Uoa24+8RsXOxQNIapIVPigu47M3szGOtUnFGxCH5uBbg9uL5JR0KbAZsGBHTJG0P3JpTiH6SdxsSEe9I+iVpUZgD62i/mZnNA4+0m98NwM6lVcpyMO4D3J+315OK8xjgxzklKTlF6YPAPhX2bS/1qJmZdTAH7c5VTBk6XlKbqTKBLcv2XyMi3iUtJ7pD3mdv4Lq8jGrNqThz6tEv5VSkRXWnHnVqTjOzxvD0eOea5+nx7BpSsL4l/z0gl5en4rwUOKPONpanHh0taQXgLQprphdFxEWkzGQsulJ/r5NrZtZBPNLuGv5MSn4yCOgZEY/l8lpTcRIRHwAf51SkReWpR4eQUnZOBn7VcV0wM7P2OGh3ARHxETAGuIw06p6rVJzAacA5OSUpOUXpFsDVZeebTkonuq+kpTu2N2ZmVo2Dducqv6Z9SmHbHZKm5tefcln5Ne3dC/tfA2xImgaH6qk4h7bRnt8CjwATJT0D/BzYJQfpViLi9XzOQ9rq4Pp9l2hrs5mZ1cGpOa2hnJrTzKx+1VJzeqRtZmbWJBy0zczMmoSDtpmZWZNw0DYzM2sSDtpmZmZNwkHbzMysSXgZU2uoia++T8uwO+b6+CmnfKMDW2Nm1tw80l5AdERu7bxtRUnXSnoh58z+i6QNC/u9J+ml/P5eSS2FvNpPSrpCUvfCeW4vnHfHnAjkKUlPSxo5P78jM7MvOo+0FxwdkVtbpFXQ/hARe+eygcDipbolXU7Ko31D/twCvBARAyV1A+4B9gSuKqt7PeBc4BsR8bSkhYGD6u6lmZnNNQftrmUIMCMiLiwVRMT4Wg+OiJmSHqZynuyjgRMj4um872fA+fPYXjMzq4Onxxcc85xbG1gPeHRuGyCpB7AxcGeFzTXX7XzaZmaN4ZH2gqMjpsfn9txrSBoP9AduiIgJc1sROJ+2mVmjeKTdtUwGNpqL417IPxjWBDaR9K0OrNvMzDqIg3bXMgpYVNKBpQJJX5X0n7UcnNNtDgOOrbD5NOA4SWvleheSdEQHtNnMzGrk6fEFR888RV1yZ0QMy+/vkDQjvx8LnEe+pl3Y/4SIuEHSbsBZ+ZGxT4ApwGF1tOPPwAhJWxYLI2KCpMOAayT1AgJo9wHs9fsuwTg/a21m1iGcT9sayvm0zczq53zaZmZmTc5B28zMrEk4aJuZmTUJB20zM7Mm4aBtZmbWJBy0zczMmoSf0y6QNBOYCHQHPgP+AJwVEZ/n7VsAZwCL50POyEt2lo4/CCgtOPIBcERE3J+3jQFWIj07/W/gwGIyD0nHAi8D5wDLRkRI2hR4EFg5IqZKWgJ4CVgWuIycrSvX3bv0eEBO5TkyIraWtDVwC/Ai0At4Ezg1IoopNyu2W9IuwPcjYtdCG/87ItbMn7+Z+1FpBTVg3vNp18I5t83si8Ij7damR8TAiBgAfA3YCfgfSHmqgauBH0bEOsAWwMGSvpG37wwcDGyRt/8QuDofV7JPRGxIyo51Wtm5twfuBt4AvpzLNgMez38BNgEeKv2IKLO8pB2r9OvvEfGViFgb+AlwrqRta2j3g8CmhXo2BT6QtHyhfQ9UOaeZmXUwB+0qIuItUr7oQ3Oe6kOAyyPisbz9HVK6ytKqZccAR+Vy8n5/yMeVG0sh/aWkxYFFIuJtUhAsBenNgDPLPj9YpcmnAcfX0K/xwK+AQ9trd27P+5LWzPv2BW6ssT1mZtbBHLTbEBEvkr6j5YEBzJmaclwup4btRTuQlgst2Q64L79/kNlBcXXgT0BpVZy2RrZjgU8lDamyvegxYJ0a2/0gsJmktYHngH/kzwsDGwCP1HA+MzPrAA7a7VPhb6U1X9taB7b8mKskTSWNbn9bKN8B+Gt+/wApKK4GTImITwBJ6k3KsvVwG+c7gRpG28zuUy3tLo38NyP9MHiYlHP7K8AzuX2tD3Y+bTOzhnDQboOk1YGZwFuk1JTl68BuBDyZ3z/JnKkrBxW2A+wDrEa6Nn5eofw/yME4Ip4DlgK+SQqSkEbC3wdeioiPqrU3IkYBPUjXvtvyFeCpGttdGvlvBoyNiA/zObamyqg/Ii6KiMERMbhbryXaaYqZmdXKQbsKScsBFwLnRsqqch6wv6SBefsywG+AU/MhpwK/yeXk/fYn3XQ2S0TMII2GN5H0ZUkDgKcjYmZht7HAT5kdtMeSMnXVcv34RNK19mr92gD4ObN/NLTX7ieBPsCWpJviAMaTbljz9Wwzs/nIj3y1VkqPWXrk60rSI15ExOuSvgdcLGkx0hTyWRFxW95+q6S+wIOSAvgQ+F7OUd1KREyXdDpwJGnEe2fZLg+Q7lwvpccaS7q+3W6QjIi/SHq7rHhLSY+THvl6C/hJRNxXS7vzo2cPAUvkHxyl9hxUS3ucmtPMrOM4NWcnk3QPsG+l4N4VODWnmVn9qqXm9Ei7k0XE1zq7DWZm1hx8TdvMzKxJOGibmZk1CQdtMzOzJuGgbWZm1iQctM3MzJqE7x63hmorNadTapqZ1ccj7flM0m6SQtI6+XOLpOmSxkt6UtKFkhZqp3xShXovl7R74fNykmZIOrhsvymSbix83l3S5YXPO+Z1w5+S9LSkkbl8hKRXc3tKryUb8BWZmVkVDtrz31DgfmDvQtkLETGQlDVrXWDXdsprsQcpI9fQCtsG5+VTW5G0HnAuaUW0LwPrAS8Wdjkz5xsvvf6vjvaYmdk8ctCej3Kmrs2B/6Z10AYgIj4jLQ26Zi3l7RgK/Azol5cpLRoJHFfhmKOBEyPi6dJ5I+L8CvuZmVkncNCev3YF7oyIZ4H3JA0qbpTUC9gWmFhLeTWSVgZWjIiHgeuBvcp2uR4YJKn8R8B6zJlbu+jwwtT46DbO79ScZmYN4KA9fw0Frs3vr2X21PUaOVHJA8AdEfHXdsrbszcpMJefp2QmcBpwbJ3tL06PD6m2k1Nzmpk1hu8en09y6sttgPVyNq1uQJBSYJauXZerVt6eocAKkvbJn/tI6p9zdZdcSQrakwtlk0m5tZ+Yi3OamVmDeaQ9/+wOXBERq0ZES0SsDLwE9OvIk0haG/hSRPTN52kBTqbsGnpOs3kmKU93yWnAcZLWynUtJOmIjmyfmZnNPY+055+hwCllZTdS+Yaw9qwtaWrh8+Fl57m5wnmuBX5dVn4pcHzpQ0RMkHQYcE2+jh5A8SHrw3NO8ZJdI2JKWw11Pm0zs47jfNrWUM6nbWZWv2r5tD09bmZm1iQctM3MzJqEp8etoSR9CDzT2e1ooGWBdzq7EQ3U1fsHXb+P7l9zWjUilisv9I1o1mjPVLou01VIGuf+Nbeu3kf3r2vx9LiZmVmTcNA2MzNrEg7a1mgXdXYDGsz9a35dvY/uXxfiG9HMzMyahEfaZmZmTcJB28zMrEk4aFtDSNpB0jOSnpc0rLPb0xZJl0l6S9KkQtnSku6R9Fz+u1Rh27G5X89I+nqhfCNJE/O2cyQply8q6bpc/pCklvncv5UljZb0lKTJkn7alfooqYekhyU9kfv3y67Uv0Lbukl6XNLtXbR/U3Lbxksa1xX72CEiwi+/OvRFSjv6ArA6sAgp1ee6nd2uNtq7FTAImFQoOxUYlt8PA36T36+b+7MosFruZ7e87WFgU0DAX4Edc/mPgAvz+72B6+Zz/1YCBuX3iwHP5n50iT7mtvTO77sDDwGbdJX+Ffp5BHA1cHtX+99oPu8UYNmysi7Vxw75njq7AX51vVf+P8xdhc/HAsd2drvaaXMLrYP2M8BK+f1KpEVi5ugLcFfu70rA04XyocDvivvk9wuTVm9SJ/b1FuBrXbGPQC/gMWDjrtQ/Ugrf+4BtmB20u0z/8nmnMGfQ7lJ97IiXp8etEfoCrxQ+T81lzWSFiHgdIP9dPpdX61vf/L68vNUxEfEZ8D6wTMNa3oY8JfgV0mi0y/QxTx2PB94C7omILtU/4CzgaODzQllX6h+kVMB3S3pU0kG5rKv1cZ55GVNrBFUo6yrPFlbrW1t9XiC+D0m9SbnVD4uID/Klvoq7VihboPsYETOBgZKWBG6WtF4buzdV/yTtDLwVEY9K2rqWQyqULbD9K9g8Il6TtDxwj6Sn29i3Wfs4zzzStkaYCqxc+NwPeK2T2jK33pS0EkD++1Yur9a3qfl9eXmrYyQtDCwBvNewllcgqTspYF8VETfl4i7VR4CI+D9gDLADXad/mwPfkjQFuBbYRtIf6Tr9AyAiXst/3wJuBv6DLtbHjuCgbY3wCNBf0mqSFiHd9HFrJ7epXrcC++X3+5GuA5fK9853oq4G9AcezlN3H0raJN+tum/ZMaW6dgdGRb6wNj/k9lwKPBURZxQ2dYk+Slouj7CR1BPYDniaLtK/iDg2IvpFRAvp/0ujIuJ7dJH+AUj6kqTFSu+B7YFJdKE+dpjOvqjuV9d8ATuR7lJ+ARje2e1pp63XAK8DM0i/xv+bdK3rPuC5/Hfpwv7Dc7+eId+ZmssHk/5D8wJwLrNXHOwB/Al4nnRn6+rzuX9bkKYBJwDj82unrtJHYAPg8dy/ScAvcnmX6F9ZX7dm9o1oXaZ/pCdNnsivyaX/ZnSlPnbUy8uYmpmZNQlPj5uZmTUJB20zM7Mm4aBtZmbWJBy0zczMmoSDtpmZWZNw0DYzM2sSDtpmZmZN4v8DOJheNpNNPk0AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create query to get call counts by complaint_type\n",
    "query = \"\"\"\n",
    "SELECT complaint_type, \n",
    "       COUNT(*)\n",
    "  FROM hpd311calls\n",
    " GROUP BY complaint_type;\n",
    "\"\"\"\n",
    "\n",
    "# Create data frame of call counts by issue\n",
    "calls_by_issue = pd.read_sql(query, engine)\n",
    "\n",
    "# Graph the number of calls for each housing issue\n",
    "calls_by_issue.plot.barh(x=\"complaint_type\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with aggregate functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      month  MAX(tmax)  MIN(tmin)  SUM(prcp)\n",
      "0  December         61          9       2.21\n",
      "1  February         78         16       5.83\n",
      "2   January         61          5       2.18\n",
      "3     March         62         27       5.17\n"
     ]
    }
   ],
   "source": [
    "# Create query to get temperature and precipitation by month\n",
    "query = \"\"\"\n",
    "SELECT month, \n",
    "        MAX(tmax), \n",
    "        MIN(tmin),\n",
    "        SUM(prcp)\n",
    "  FROM weather \n",
    " GROUP BY month;\n",
    "\"\"\"\n",
    "\n",
    "# Get data frame of monthly weather stats\n",
    "weather_by_month = pd.read_sql(query, engine)\n",
    "\n",
    "# View weather stats by month\n",
    "print(weather_by_month)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading multiple tables with joins\n",
    "## Joining tables\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  unique_key created_date agency  complaint_type incident_zip  \\\n",
      "0   38070822   01/01/2018    HPD  HEAT/HOT WATER        10468   \n",
      "1   38065299   01/01/2018    HPD        PLUMBING        10003   \n",
      "2   38066653   01/01/2018    HPD  HEAT/HOT WATER        10452   \n",
      "3   38070264   01/01/2018    HPD  HEAT/HOT WATER        10032   \n",
      "4   38072466   01/01/2018    HPD  HEAT/HOT WATER        11213   \n",
      "\n",
      "       incident_address community_board    borough      station  \\\n",
      "0    2786 JEROME AVENUE        07 BRONX      BRONX  USW00094728   \n",
      "1  323 EAST   12 STREET    03 MANHATTAN  MANHATTAN  USW00094728   \n",
      "2  1235 GRAND CONCOURSE        04 BRONX      BRONX  USW00094728   \n",
      "3  656 WEST  171 STREET    12 MANHATTAN  MANHATTAN  USW00094728   \n",
      "4       1030 PARK PLACE     08 BROOKLYN   BROOKLYN  USW00094728   \n",
      "\n",
      "                          name  ...  longitude  elevation        date  \\\n",
      "0  NY CITY CENTRAL PARK, NY US  ...  -73.96925       42.7  01/01/2018   \n",
      "1  NY CITY CENTRAL PARK, NY US  ...  -73.96925       42.7  01/01/2018   \n",
      "2  NY CITY CENTRAL PARK, NY US  ...  -73.96925       42.7  01/01/2018   \n",
      "3  NY CITY CENTRAL PARK, NY US  ...  -73.96925       42.7  01/01/2018   \n",
      "4  NY CITY CENTRAL PARK, NY US  ...  -73.96925       42.7  01/01/2018   \n",
      "\n",
      "     month  awnd  prcp  snow  tavg tmax  tmin  \n",
      "0  January  7.83   0.0   0.0         19     7  \n",
      "1  January  7.83   0.0   0.0         19     7  \n",
      "2  January  7.83   0.0   0.0         19     7  \n",
      "3  January  7.83   0.0   0.0         19     7  \n",
      "4  January  7.83   0.0   0.0         19     7  \n",
      "\n",
      "[5 rows x 21 columns]\n"
     ]
    }
   ],
   "source": [
    "# Query to join weather to call records by date columns\n",
    "query = \"\"\"\n",
    "SELECT * \n",
    "  FROM hpd311calls\n",
    "  JOIN weather \n",
    "  ON hpd311calls.created_date = weather.date;\n",
    "\"\"\"\n",
    "\n",
    "# Create data frame of joined tables\n",
    "calls_with_weather = pd.read_sql(query, engine)\n",
    "\n",
    "# View the data frame to make sure all columns were joined\n",
    "print(calls_with_weather.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining and filtering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  unique_key created_date agency complaint_type incident_zip  \\\n",
      "0   38074305   01/01/2018    HPD     WATER LEAK        11212   \n",
      "1   38078748   01/01/2018    HPD     WATER LEAK        10458   \n",
      "2   38081097   01/01/2018    HPD     WATER LEAK        11221   \n",
      "3   38077874   01/01/2018    HPD     WATER LEAK        11418   \n",
      "4   38081110   01/01/2018    HPD     WATER LEAK        11420   \n",
      "\n",
      "          incident_address community_board   borough  prcp  \n",
      "0     1026 WILLMOHR STREET     17 BROOKLYN  BROOKLYN   0.0  \n",
      "1       2700 MARION AVENUE        07 BRONX     BRONX   0.0  \n",
      "2  192 MALCOLM X BOULEVARD     03 BROOKLYN  BROOKLYN   0.0  \n",
      "3    129-11 JAMAICA AVENUE       09 QUEENS    QUEENS   0.0  \n",
      "4        111-17 133 STREET       10 QUEENS    QUEENS   0.0  \n"
     ]
    }
   ],
   "source": [
    "# Query to get water leak calls and daily precipitation\n",
    "query = \"\"\"\n",
    "SELECT hpd311calls.*, weather.prcp\n",
    "  FROM hpd311calls\n",
    "  JOIN weather\n",
    "    ON hpd311calls.created_date = weather.date\n",
    "  where hpd311calls.complaint_type = 'WATER LEAK';\"\"\"\n",
    "\n",
    "# Load query results into the leak_calls data frame\n",
    "leak_calls = pd.read_sql(query, engine)\n",
    "\n",
    "# View the data frame\n",
    "print(leak_calls.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  created_date  COUNT(*)\n",
      "0   01/01/2018      4597\n",
      "1   01/02/2018      4362\n",
      "2   01/03/2018      3045\n",
      "3   01/04/2018      3374\n",
      "4   01/05/2018      4333\n"
     ]
    }
   ],
   "source": [
    "# Query to get heat/hot water call counts by created_date\n",
    "query = \"\"\"\n",
    "SELECT hpd311calls.created_date, \n",
    "       COUNT(*)\n",
    "  FROM hpd311calls \n",
    " WHERE hpd311calls.complaint_type = 'HEAT/HOT WATER' \n",
    " GROUP BY hpd311calls.created_date;\n",
    "\"\"\"\n",
    "\n",
    "# Query database and save results as df\n",
    "df = pd.read_sql(query, engine)\n",
    "\n",
    "# View first 5 records\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  created_date  COUNT(*)  tmax  tmin\n",
      "0   01/01/2018      4597    19     7\n",
      "1   01/02/2018      4362    26    13\n",
      "2   01/03/2018      3045    30    16\n",
      "3   01/04/2018      3374    29    19\n",
      "4   01/05/2018      4333    19     9\n"
     ]
    }
   ],
   "source": [
    "# Modify query to join tmax and tmin from weather by date\n",
    "query = \"\"\"\n",
    "SELECT hpd311calls.created_date, \n",
    "       COUNT(*), \n",
    "       weather.tmax, \n",
    "       weather.tmin\n",
    "  FROM hpd311calls \n",
    "       JOIN weather \n",
    "       ON hpd311calls.created_date = weather.date\n",
    " WHERE hpd311calls.complaint_type = 'HEAT/HOT WATER' \n",
    " GROUP BY hpd311calls.created_date;\n",
    " \"\"\"\n",
    "\n",
    "# Query database and save results as df\n",
    "df = pd.read_sql(query, engine)\n",
    "\n",
    "# View first 5 records\n",
    "print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to JSON\n",
    "## Load JSON data\n",
    "Many open data portals make available JSONs datasets that are particularly easy to parse. They can be accessed directly via URL. Each object is a record, all objects have the same set of attributes, and none of the values are nested objects that themselves need to be parsed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load pandas as pd\n",
    "import pandas as pd \n",
    "\n",
    "# Load the daily report to a data frame\n",
    "pop_in_shelters = pd.read_json(\"dhs_daily_report.json\")\n",
    "\n",
    "# View summary stats about pop_in_shelters\n",
    "print(pop_in_shelters.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When getting data from a URL, like with open data portals, be mindful of how much data is being pulled and how often you do it. Requesting lots of data can strain shared resources.\n",
    "##  Work with JSON orientations\n",
    "JSON isn't a tabular format, so pandas makes assumptions about its orientation when loading data. Most JSON data you encounter will be in orientations that pandas can automatically transform into a data frame.\n",
    "\n",
    "Sometimes, like in this modified version of the Department of Homeless Services Daily Report, data is oriented differently. To reduce the file size, it has been split formatted. You'll see what happens when you try to load it normally versus with the orient keyword argument. The try/except block will alert you if there are errors loading the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    # Load the JSON without keyword arguments\n",
    "    df = pd.read_json(\"dhs_report_reformatted.json\")\n",
    "    # Plot total population in shelters over time\n",
    "    df[\"date_of_census\"] = pd.to_datetime(df[\"date_of_census\"])\n",
    "    df.plot(x=\"date_of_census\", \n",
    "            y=\"total_individuals_in_shelter\")\n",
    "    plt.show()\n",
    "    \n",
    "except ValueError:\n",
    "    print(\"pandas could not parse the JSON.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    # Load the JSON with orient specified\n",
    "    df = pd.read_json(\"dhs_report_reformatted.json\",\n",
    "                      orient='split')\n",
    "    \n",
    "    # Plot total population in shelters over time\n",
    "    df[\"date_of_census\"] = pd.to_datetime(df[\"date_of_census\"])\n",
    "    df.plot(x=\"date_of_census\", \n",
    "            y=\"total_individuals_in_shelter\")\n",
    "    plt.show()\n",
    "    \n",
    "except ValueError:\n",
    "    print(\"pandas could not parse the JSON.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to APIs\n",
    "\n",
    "1. Introduction to APIs\n",
    "\n",
    "You now know how to load JSON data into pandas, given a path or URL to a JSON file. In this lesson, we'll turn our attention to working with web application programming interfaces, or APIs, the most common source of JSON data.\n",
    "\n",
    "2. Application Programming Interfaces\n",
    "\n",
    "An application programming interface is a defined way for an application to communicate with other programs, and vice versa. They let programmers get data from an application without having to know about that application's database architecture. One caveat -- APIs are shared resources, and often limit how much data you can get in a specified timeframe. Using an API to get data is like using a catalog to order products. The catalog shows what's available and provides order instructions.\n",
    "\n",
    "3. Application Programming Interfaces\n",
    "\n",
    "You send a properly formed order to the right address\n",
    "\n",
    "4. Application Programming Interfaces\n",
    "\n",
    "and get back what you asked for. Similarly, an API provides an endpoint to send requests to, and documentation describes what a request should look like, such as parameters to include.\n",
    "\n",
    "5. Requests\n",
    "\n",
    "While there are Python libraries geared towards popular APIs, we'll use the Requests library in this course. Requests lets users send and get data from any URL, so it's not tied to any particular API. The function to retrieve data from a URL, logically, is requests get.\n",
    "\n",
    "\n",
    "6. requests.get()\n",
    "\n",
    "Requests get takes a string of the URL to get data from, and has optional keyword arguments that are useful for working with APIs. The params keyword lets you pass a dictionary of parameter names and values to customize API requests. The headers keyword also takes a dictionary of names and values. If the API you're using requires a user authentication key, it would be passed in the header. The result is a response object, containing data and metadata. We need to use the response's JSON method to get just the data.\n",
    "\n",
    "7. response.json() and pandas\n",
    "\n",
    "Importantly, the JSON method returns a dictionary, which read JSON can't parse -- it expects a string. To load the data to a data frame, we need to use pd DataFrame instead.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "api_url = \"https://api.yelp.com/v3/businesses/search\"\n",
    "\n",
    "# Get data about NYC cafes from the Yelp API\n",
    "response = requests.get(api_url, \n",
    "                headers=headers, \n",
    "                params=params)\n",
    "\n",
    "# Extract JSON data from the response\n",
    "data = response.json()\n",
    "\n",
    "# Load data to a data frame\n",
    "cafes = pd.DataFrame(data['businesses'])\n",
    "\n",
    "# View the data's dtypes\n",
    "print(cafes.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dictionary to query API for cafes in NYC\n",
    "parameters = {\"term\": \"cafe\",\n",
    "          \t  \"location\": \"NYC\"}\n",
    "\n",
    "# Query the Yelp API with headers and params set\n",
    "response = requests.get(api_url, \n",
    "                        headers=headers, \n",
    "                        params=parameters)\n",
    "\n",
    "# Extract JSON data from response\n",
    "data = response.json()\n",
    "\n",
    "# Load \"businesses\" values to a data frame and print head\n",
    "cafes = pd.DataFrame(data[\"businesses\"])\n",
    "print(cafes.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great work! Notice that some of the values are themselves dictionaries, making them hard to analyze. We'll learn how to unpack those in a later lesson.\n",
    "# Set request headers\n",
    "Many APIs require users provide an API key, obtained by registering for the service. Keys typically are passed in the request header, rather than as parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dictionary that passes Authorization and key string\n",
    "headers = {\"Authorization\": \"Bearer {}\".format(api_key)}\n",
    "\n",
    "# Query the Yelp API with headers and params set\n",
    "response = requests.get(api_url, \n",
    "                        headers=headers, \n",
    "                        params=params)\n",
    "\n",
    "# Extract JSON data from response\n",
    "data = response.json()\n",
    "\n",
    "# Load \"businesses\" values to a data frame and print names\n",
    "cafes = pd.DataFrame(data[\"businesses\"])\n",
    "print(cafes.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with nested JSONs\n",
    "## Flatten nested JSONs\n",
    "A feature of JSON data is that it can be nested: an attribute's value can consist of attribute-value pairs. This nested data is more useful unpacked, or flattened, into its own data frame columns. The pandas.io.json submodule has a function, json_normalize(), that does exactly this.\n",
    "\n",
    "The Yelp API response data is nested. Your job is to flatten out the next level of data in the coordinates and location columns.\n",
    "\n",
    "pandas (as pd) and requests have been imported. The results of the API call are stored as response\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load json_normalize()\n",
    "from pandas.io.json import json_normalize\n",
    "# Isolate the JSON data from the API response\n",
    "data = response.json()\n",
    "# Flatten business data into a data frame, replace separator\n",
    "cafes = json_normalize(data[\"businesses\"],\n",
    "             sep='_')\n",
    "# View data\n",
    "print(cafes.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handle deeply nested data\n",
    "Last exercise, you flattened data nested down one level. Here, you'll unpack more deeply nested data.\n",
    "\n",
    "The categories attribute in the Yelp API response contains lists of objects. To flatten this data, you'll employ json_normalize() arguments to specify the path to categories and pick other attributes to include in the data frame. You should also change the separator to facilitate column selection and prefix the other attributes to prevent column name collisions. We'll work through this in steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load other business attributes and set meta prefix\n",
    "flat_cafes = json_normalize(data[\"businesses\"],\n",
    "                            sep=\"_\",\n",
    "                    \t\trecord_path=\"categories\",\n",
    "                    \t\tmeta=[\"name\", \n",
    "                                  \"alias\",  \n",
    "                                  \"rating\",\n",
    "                          \t\t  [\"coordinates\", \"latitude\"], \n",
    "                          \t\t  [\"coordinates\", \"longitude\"]],\n",
    "                    \t\tmeta_prefix=\"biz_\")\n",
    "# View the data\n",
    "print(flat_cafes.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining multiple datasets\n",
    "## Append data frames\n",
    "In this exercise, you’ll practice appending records by creating a dataset of the 100 highest-rated cafes in New York City according to Yelp.\n",
    "\n",
    "APIs often limit the amount of data returned, since sending large datasets can be time- and resource-intensive. The Yelp Business Search API limits the results returned in a call to 50 records. However, the offset parameter lets a user retrieve results starting after a specified number. By modifying the offset, we can get results 1-50 in one call and 51-100 in another. Then, we can append the data frames.\n",
    "pandas (as pd), requests, and json_normalize() have been imported. The 50 top-rated cafes are already in a data frame, top_50_cafes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add an offset parameter to get cafes 51-100\n",
    "params = {\"term\": \"cafe\", \n",
    "          \"location\": \"NYC\",\n",
    "          \"sort_by\": \"rating\", \n",
    "          \"limit\": 50,\n",
    "          \"offset\": 50}\n",
    "\n",
    "result = requests.get(api_url, headers=headers, params=params)\n",
    "next_50_cafes = json_normalize(result.json()[\"businesses\"])\n",
    "\n",
    "# Append the results, setting ignore_index to renumber rows\n",
    "cafes = top_50_cafes.append(next_50_cafes, ignore_index=True)\n",
    "\n",
    "# Print shape of cafes\n",
    "print(cafes.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge crosswalk into cafes on their zip code fields\n",
    "cafes_with_pumas = cafes.merge(crosswalk, \n",
    "                   \t\t\t   left_on=\"location_zip_code\", \n",
    "                               right_on=\"zipcode\")\n",
    "\n",
    "# Merge pop_data into cafes_with_pumas on puma field\n",
    "cafes_with_pop = cafes_with_pumas.merge(pop_data, on=\"puma\")\n",
    "\n",
    "# View the data\n",
    "print(cafes_with_pop.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}