A. KIROS_data_setup_Weather-Copy1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Project 4: Group exercise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from sklearn.model_selection import train_test_split\n",
    "np.random.seed(42)\n",
    "\n",
    "from matplotlib import rcParams\n",
    "rcParams.update({'figure.autolayout':True})\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "prediction_data=pd.read_csv('./assets/test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "working_data=pd.read_csv('./assets/train.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "NOAA=pd.read_csv('./assets/weather.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "spray=pd.read_csv('./assets/spray.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "train, test=train_test_split(working_data, test_size=.20, random_state=523)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Station</th>\n",
       "      <th>Date</th>\n",
       "      <th>Tmax</th>\n",
       "      <th>Tmin</th>\n",
       "      <th>Tavg</th>\n",
       "      <th>Depart</th>\n",
       "      <th>DewPoint</th>\n",
       "      <th>WetBulb</th>\n",
       "      <th>Heat</th>\n",
       "      <th>Cool</th>\n",
       "      <th>...</th>\n",
       "      <th>CodeSum</th>\n",
       "      <th>Depth</th>\n",
       "      <th>Water1</th>\n",
       "      <th>SnowFall</th>\n",
       "      <th>PrecipTotal</th>\n",
       "      <th>StnPressure</th>\n",
       "      <th>SeaLevel</th>\n",
       "      <th>ResultSpeed</th>\n",
       "      <th>ResultDir</th>\n",
       "      <th>AvgSpeed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2007-05-01</td>\n",
       "      <td>83</td>\n",
       "      <td>50</td>\n",
       "      <td>67</td>\n",
       "      <td>14</td>\n",
       "      <td>51</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>29.10</td>\n",
       "      <td>29.82</td>\n",
       "      <td>1.7</td>\n",
       "      <td>27</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2007-05-01</td>\n",
       "      <td>84</td>\n",
       "      <td>52</td>\n",
       "      <td>68</td>\n",
       "      <td>M</td>\n",
       "      <td>51</td>\n",
       "      <td>57</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>0.00</td>\n",
       "      <td>29.18</td>\n",
       "      <td>29.82</td>\n",
       "      <td>2.7</td>\n",
       "      <td>25</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2007-05-02</td>\n",
       "      <td>59</td>\n",
       "      <td>42</td>\n",
       "      <td>51</td>\n",
       "      <td>-3</td>\n",
       "      <td>42</td>\n",
       "      <td>47</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>BR</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>29.38</td>\n",
       "      <td>30.09</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2007-05-02</td>\n",
       "      <td>60</td>\n",
       "      <td>43</td>\n",
       "      <td>52</td>\n",
       "      <td>M</td>\n",
       "      <td>42</td>\n",
       "      <td>47</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>BR HZ</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>0.00</td>\n",
       "      <td>29.44</td>\n",
       "      <td>30.08</td>\n",
       "      <td>13.3</td>\n",
       "      <td>2</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2007-05-03</td>\n",
       "      <td>66</td>\n",
       "      <td>46</td>\n",
       "      <td>56</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>48</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>29.39</td>\n",
       "      <td>30.12</td>\n",
       "      <td>11.7</td>\n",
       "      <td>7</td>\n",
       "      <td>11.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Station        Date  Tmax  Tmin Tavg Depart  DewPoint WetBulb Heat Cool  \\\n",
       "0        1  2007-05-01    83    50   67     14        51      56    0    2   \n",
       "1        2  2007-05-01    84    52   68      M        51      57    0    3   \n",
       "2        1  2007-05-02    59    42   51     -3        42      47   14    0   \n",
       "3        2  2007-05-02    60    43   52      M        42      47   13    0   \n",
       "4        1  2007-05-03    66    46   56      2        40      48    9    0   \n",
       "\n",
       "     ...    CodeSum Depth Water1 SnowFall PrecipTotal StnPressure SeaLevel  \\\n",
       "0    ...                0      M      0.0        0.00       29.10    29.82   \n",
       "1    ...                M      M        M        0.00       29.18    29.82   \n",
       "2    ...         BR     0      M      0.0        0.00       29.38    30.09   \n",
       "3    ...      BR HZ     M      M        M        0.00       29.44    30.08   \n",
       "4    ...                0      M      0.0        0.00       29.39    30.12   \n",
       "\n",
       "  ResultSpeed ResultDir  AvgSpeed  \n",
       "0         1.7        27       9.2  \n",
       "1         2.7        25       9.6  \n",
       "2        13.0         4      13.4  \n",
       "3        13.3         2      13.4  \n",
       "4        11.7         7      11.9  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NOAA.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Station', 'Date', 'Tmax', 'Tmin', 'Tavg', 'Depart', 'DewPoint',\n",
       "       'WetBulb', 'Heat', 'Cool', 'Sunrise', 'Sunset', 'CodeSum', 'Depth',\n",
       "       'Water1', 'SnowFall', 'PrecipTotal', 'StnPressure', 'SeaLevel',\n",
       "       'ResultSpeed', 'ResultDir', 'AvgSpeed'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NOAA.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the dates to datetime format\n",
    "NOAA['Date'] = pd.to_datetime(NOAA['Date'])\n",
    "working_data['Date'] = pd.to_datetime(working_data['Date'])\n",
    "prediction_data['Date'] = pd.to_datetime(prediction_data['Date'])\n",
    "spray['Date'] = pd.to_datetime(spray['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "NOAA.at[2410, 'StnPressure'] = 29.34\n",
    "NOAA.at[2411, 'StnPressure'] = 29.34\n",
    "NOAA.at[2943, 'Depart'] = -5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get rid of all station 2 input\n",
    "NOAA = NOAA[NOAA['Station'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#changing the values 'M' values to the straight average in 'Tavg'\n",
    "new_vals = []\n",
    "\n",
    "for i, x in enumerate(NOAA['Tavg']):\n",
    "    if x == 'M':\n",
    "        avg = (NOAA['Tmax'].loc[i] + NOAA['Tmin'].loc[i]) / 2\n",
    "        new_vals.append(avg)\n",
    "    else:\n",
    "        new_vals.append(x)\n",
    "        \n",
    "NOAA['Tavg'] = new_vals\n",
    "NOAA['Tavg'] = NOAA['Tavg'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Forward filling missing values in wetbulb\n",
    "NOAA['WetBulb'] = NOAA['WetBulb'].replace('M', np.nan).fillna(method='ffill').astype(int)\n",
    "\n",
    "#Forward filling missing values in cool/heat/stnPressure/Avgspeed\n",
    "NOAA['Heat'] = NOAA['Heat'].replace('M', np.nan).fillna(method='ffill').astype(float)\n",
    "NOAA['Cool'] = NOAA['Cool'].replace('M', np.nan).fillna(method='ffill').astype(float)\n",
    "NOAA['StnPressure'] = NOAA['StnPressure'].replace('M', np.nan).fillna(method='ffill').astype(float)\n",
    "NOAA['AvgSpeed'] = NOAA['AvgSpeed'].replace('M', np.nan).fillna(method='ffill').astype(float)\n",
    "\n",
    "\n",
    "#dropping columns\n",
    "\n",
    "NOAA =NOAA.drop(['Sunrise', 'Sunset', 'CodeSum', 'Depth', 'Water1', \n",
    "                        'SnowFall', 'SeaLevel'], axis = 1)\n",
    "NOAA = NOAA.drop('Depart', axis=1)\n",
    "\n",
    "#Changing the 'T' for trace rain to a quantity of 0.001, forward filling 'M' values\n",
    "NOAA['PrecipTotal'] = NOAA['PrecipTotal'].apply(lambda x: 0.001 if x == '  T' \n",
    "                                                      else x).replace('M', np.nan).fillna(method='ffill').astype(float)\n",
    "\n",
    "#Replacing missing sealevel values with the mean sealevel\n",
    "# NOAA['SeaLevel'] = NOAA['SeaLevel'].replace('M', np.nan).astype(float)\n",
    "# mean_sea = NOAA['SeaLevel'].mean()\n",
    "# NOAA['SeaLevel'] = NOAA['SeaLevel'].fillna(mean_sea)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(working_data, NOAA, on='Date', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Address</th>\n",
       "      <th>Species</th>\n",
       "      <th>Block</th>\n",
       "      <th>Street</th>\n",
       "      <th>Trap</th>\n",
       "      <th>AddressNumberAndStreet</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>AddressAccuracy</th>\n",
       "      <th>...</th>\n",
       "      <th>Tavg</th>\n",
       "      <th>DewPoint</th>\n",
       "      <th>WetBulb</th>\n",
       "      <th>Heat</th>\n",
       "      <th>Cool</th>\n",
       "      <th>PrecipTotal</th>\n",
       "      <th>StnPressure</th>\n",
       "      <th>ResultSpeed</th>\n",
       "      <th>ResultDir</th>\n",
       "      <th>AvgSpeed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>4100 North Oak Park Avenue, Chicago, IL 60634,...</td>\n",
       "      <td>CULEX PIPIENS/RESTUANS</td>\n",
       "      <td>41</td>\n",
       "      <td>N OAK PARK AVE</td>\n",
       "      <td>T002</td>\n",
       "      <td>4100  N OAK PARK AVE, Chicago, IL</td>\n",
       "      <td>41.95469</td>\n",
       "      <td>-87.800991</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>74</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date                                            Address  \\\n",
       "0 2007-05-29  4100 North Oak Park Avenue, Chicago, IL 60634,...   \n",
       "\n",
       "                  Species  Block           Street  Trap  \\\n",
       "0  CULEX PIPIENS/RESTUANS     41   N OAK PARK AVE  T002   \n",
       "\n",
       "              AddressNumberAndStreet  Latitude  Longitude  AddressAccuracy  \\\n",
       "0  4100  N OAK PARK AVE, Chicago, IL  41.95469 -87.800991                9   \n",
       "\n",
       "     ...     Tavg  DewPoint  WetBulb  Heat  Cool  PrecipTotal  StnPressure  \\\n",
       "0    ...       74        58       65   0.0   9.0          0.0        29.39   \n",
       "\n",
       "   ResultSpeed  ResultDir  AvgSpeed  \n",
       "0          5.8         18       6.5  \n",
       "\n",
       "[1 rows x 25 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# df['PrecipTotal'].value_counts(normalize=True)\n",
    "# df['Tavg'].value_counts(normalize=True).plot('bar') \n",
    "# if you normalize them then significantly the Tavg would be only the top three in the first rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x16dac01a358>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kiraz\\AppData\\Local\\conda\\conda\\envs\\dsi\\lib\\site-packages\\matplotlib\\figure.py:2267: UserWarning: This figure includes Axes that are not compatible with tight_layout, so results might be incorrect.\n",
      "  warnings.warn(\"This figure includes Axes that are not compatible \"\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAagAAAEYCAYAAAAJeGK1AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvhp/UCwAAFqVJREFUeJzt3XuQHWWZx/HvAxEEL4HAcDEJBjEKlqwKWWBVvCQuC3iB2oJC3cVIgVkV71gad90iq9Zu2FLxsojFAhq8AeIFxCsmuIu7goSLBAxKRCCz3KJAtLyUou/+0W/keNJnZvqcOZ13Zr6fqq7p8/Zzut/uPqd/0316zkRKCUmSSrPdtu6AJEl1DChJUpEMKElSkQwoSVKRDChJUpEMKElSkQwoSVKRDChJUpEMKElSkWZt6w6MZffdd08LFizY1t2QJE2i66677mcppZHx6ooOqAULFrB27dpt3Q1J0iSKiDsnUuclPklSkQwoSVKRDChJUpEMKElSkQwoSVKRDChJUpHGDaiIOD8i7o+Imzva5kTEFRFxW/65a26PiPhIRGyIiJsi4qCO5yzN9bdFxNLhrI4kabqYyBnUJ4Eju9qWA6tTSguB1fkxwFHAwjwsA86GKtCA04FDgUOA07eEmiRJdcYNqJTSfwMPdDUfA6zK46uAYzvaL0iVq4FdImJv4G+AK1JKD6SUHgSuYOvQkyTpT/r9Jok9U0r3AKSU7omIPXL7XGBjR91obuvVvpWIWEZ19sU+++zzp/YFy79a25E7Vr64rxWQJJVtsm+SiJq2NEb71o0pnZNSWpRSWjQyMu5XNUmSpql+A+q+fOmO/PP+3D4KzO+omwfcPUa7JEm1+g2oy4Atd+ItBS7taH9VvpvvMGBzvhT4TeCIiNg13xxxRG6TJKnWuJ9BRcTngBcAu0fEKNXdeCuBiyPiZOAu4Phc/jXgaGAD8GvgJICU0gMR8V7g2lz3npRS940XkiT9ybgBlVJ6RY9JS2pqE3Bqj/mcD5zfqHeSpBnLb5KQJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFanfbzMv34rZPdo3t9sPSVJfPIOSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBVp1iBPjoi3AqcACVgHnATsDVwIzAGuB05MKf0uInYELgAOBn4OnJBSumOQ5U+2A1cdWNu+bum6lnsiSer7DCoi5gJvAhallJ4ObA+8HDgDODOltBB4EDg5P+Vk4MGU0pOBM3OdJEm1Br3ENwvYKSJmATsD9wCLgUvy9FXAsXn8mPyYPH1JRMSAy5ckTVN9X+JLKf1fRLwfuAv4DfAt4DrgoZTSw7lsFJibx+cCG/NzH46IzcBuwM865xsRy4BlAPvss0+/3WvF+v0PqG0/4Nb1LfdEkqafQS7x7Up1VrQv8ATgMcBRNaVpy1PGmPZIQ0rnpJQWpZQWjYyM9Ns9SdIUN8glvhcBP00pbUop/R74IvBsYJd8yQ9gHnB3Hh8F5gPk6bOBBwZYviRpGhskoO4CDouInfNnSUuAHwJXAsflmqXApXn8svyYPH1NSmmrMyhJkmCAgEopXUN1s8P1VLeYbwecA7wTeFtEbKD6jOm8/JTzgN1y+9uA5QP0W5I0zQ30d1AppdOB07uabwcOqan9LXD8IMuTJM0cfpOEJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlIs7Z1B2aSs167prb91I8vbrknklQ+z6AkSUUyoCRJRTKgJElFMqAkSUUyoCRJRTKgJElF8jbzgn3ghJfUtp920eUt90SS2ucZlCSpSAMFVETsEhGXRMStEbE+Iv4qIuZExBURcVv+uWuujYj4SERsiIibIuKgyVkFSdJ0NOgZ1IeBb6SU9geeAawHlgOrU0oLgdX5McBRwMI8LAPOHnDZkqRprO+AiojHA88DzgNIKf0upfQQcAywKpetAo7N48cAF6TK1cAuEbF33z2XJE1rg5xBPQnYBHwiIm6IiHMj4jHAnimlewDyzz1y/VxgY8fzR3ObJElbGSSgZgEHAWenlJ4F/IpHLufViZq2tFVRxLKIWBsRazdt2jRA9yRJU9kgATUKjKaUrsmPL6EKrPu2XLrLP+/vqJ/f8fx5wN3dM00pnZNSWpRSWjQyMjJA9yRJU1nfAZVSuhfYGBFPzU1LgB8ClwFLc9tS4NI8fhnwqnw332HA5i2XAiVJ6jboH+q+EfhMROwA3A6cRBV6F0fEycBdwPG59mvA0cAG4Ne5VpKkWgMFVErpRmBRzaQlNbUJOHWQ5UmSZg6/SUKSVCQDSpJUJANKklQkA0qSVCQDSpJUJP8f1DQyuvyq2vZ5Kw9vuSeSNDjPoCRJRTKgJElFMqAkSUUyoCRJRTKgJElFMqAkSUUyoCRJRfLvoGawFStWNGqXpDZ5BiVJKpIBJUkqkgElSSqSASVJKpIBJUkqkgElSSqSASVJKpIBJUkqkgElSSqSASVJKpIBJUkqkgElSSqSASVJKpIBJUkqkgElSSqSASVJKpL/sFATtnrNfrXtSxb/pOWeSJoJPIOSJBXJgJIkFcmAkiQVyYCSJBXJgJIkFcmAkiQVyYCSJBVp4ICKiO0j4oaIuDw/3jciromI2yLioojYIbfvmB9vyNMXDLpsSdL0NRlnUG8G1nc8PgM4M6W0EHgQODm3nww8mFJ6MnBmrpMkqdZAARUR84AXA+fmxwEsBi7JJauAY/P4MfkxefqSXC9J0lYGPYP6EPAO4I/58W7AQymlh/PjUWBuHp8LbATI0zfn+j8TEcsiYm1ErN20adOA3ZMkTVV9B1REvAS4P6V0XWdzTWmawLRHGlI6J6W0KKW0aGRkpN/uSZKmuEG+LPY5wMsi4mjg0cDjqc6odomIWfksaR5wd64fBeYDoxExC5gNPDDA8iVJ01jfAZVSehfwLoCIeAHw9pTS30XE54HjgAuBpcCl+SmX5cffy9PXpJS2OoPS9LHXlTfWtt/7wme23BNJU9Ew/t3GO4ELI+J9wA3Aebn9POBTEbGB6szp5UNYtqawBcu/Wtt+x8oXt9wTSSWYlIBKKX0H+E4evx04pKbmt8Dxk7E8SdL05zdJSJKKZEBJkopkQEmSimRASZKKZEBJkopkQEmSimRASZKKZEBJkopkQEmSijSMrzqSWuFXI0nTm2dQkqQiGVCSpCIZUJKkIhlQkqQiGVCSpCJ5F59mjhWze7RvbrcfkibEMyhJUpEMKElSkQwoSVKRDChJUpEMKElSkbyLTxrDgasOrG1ft3Rdyz2RZh4DSppE6/c/oLb9gFvXt9wTaerzEp8kqUgGlCSpSAaUJKlIBpQkqUgGlCSpSAaUJKlI3mYubUNnvXZNbfupH1/cck+k8hhQ0hTygRNeUtt+2kWXt9wTafi8xCdJKpIBJUkqkpf4pGlsdPlVte3zVh7eck+k5jyDkiQVyYCSJBXJgJIkFanvgIqI+RFxZUSsj4hbIuLNuX1ORFwREbfln7vm9oiIj0TEhoi4KSIOmqyVkCRNP4OcQT0MnJZSOgA4DDg1Ip4GLAdWp5QWAqvzY4CjgIV5WAacPcCyJUnTXN8BlVK6J6V0fR7/JbAemAscA6zKZauAY/P4McAFqXI1sEtE7N13zyVJ09qkfAYVEQuAZwHXAHumlO6BKsSAPXLZXGBjx9NGc5skSVsZOKAi4rHAF4C3pJR+MVZpTVuqmd+yiFgbEWs3bdo0aPckSVPUQAEVEY+iCqfPpJS+mJvv23LpLv+8P7ePAvM7nj4PuLt7nimlc1JKi1JKi0ZGRgbpniRpChvkLr4AzgPWp5Q+2DHpMmBpHl8KXNrR/qp8N99hwOYtlwIlSeo2yFcdPQc4EVgXETfmtn8EVgIXR8TJwF3A8Xna14CjgQ3Ar4GTBli2JGma6zugUkrfpf5zJYAlNfUJOLXf5UmSZha/SUKSVCQDSpJUJP/dhqQ/WbFiRaN2aZgMKEl9W71mv9r2JYt/0nJPNB15iU+SVCQDSpJUJANKklQkA0qSVCQDSpJUJO/ik9Sava68sbb93hc+s+WeaCowoCQVa8Hyr9a237HyxZNSr7IZUJJmrhWze7RvbrcfqmVASdIEHbjqwJ7T1i1d12JPZgZvkpAkFcmAkiQVyYCSJBXJz6AkaYjW739AbfsBt65vuSdTj2dQkqQiGVCSpCJ5iU+SCnLWa9fUtp/68cUt92Tb8wxKklQkA0qSVCQDSpJUJANKklQkA0qSVCQDSpJUJG8zl6Qp7AMnvKS2/bSLLm+5J5PPgJKkGWR0+VW17fNWHl7bvmLFikbtq9fsV9u+ZPFPxu1bNy/xSZKKZEBJkopkQEmSimRASZKKZEBJkopkQEmSimRASZKKZEBJkopkQEmSimRASZKK1HpARcSREfGjiNgQEcvbXr4kaWpoNaAiYnvgLOAo4GnAKyLiaW32QZI0NbR9BnUIsCGldHtK6XfAhcAxLfdBkjQFREqpvYVFHAccmVI6JT8+ETg0pfSGjpplwLL88KnAj2pmtTvwswaLblrfxjKsn1r1bSzD+qlV38Yypmv9E1NKI+M+O6XU2gAcD5zb8fhE4KN9zGftMOvbWIb1U6u+xD5Z72tiutV3D21f4hsF5nc8ngfc3XIfJElTQNsBdS2wMCL2jYgdgJcDl7XcB0nSFNDqf9RNKT0cEW8AvglsD5yfUrqlj1mdM+T6NpZh/dSqb2MZ1k+t+jaWMdPq/0yrN0lIkjRRfpOEJKlIBpQkqUgGlCSpSAaUZqSI2GNb96FbiX3StjXTXxMzKqAiYkJ3lETEbmNMWxQRV0bEpyNifkRcERGbI+LaiHjW5PV2eCJir4g4OyLOiojdImJFRKyLiIsjYu+a+tkRsTIibo2In+dhfW7bZRL6Mysi/iEivhERN0XEDyLi6xHx2oh41CTMf07XsBvw/YjYNSLmDDr/YfcpIrbP2+e9EfGcrmnvbrDMH09S94t7DU2Gsd73LS2/0et0W2zT1o+Ng/yV77YegK/XtM3pMewGjNbUrwR2z+OLgNuBDcCdwPNr6r9P9WW3rwA2Asfl9iXA93r087HAe4BbgM3AJuBq4NU96o/sGJ8NnAfcBHwW2LOm/vHAvwGfAl7ZNe1jNfXfAN4ILM/zfSewT267tKb+m7lmr462vXLbFTX11wPvBvab4H78HHA2cBjVH2/Py+NnAxdNwvz/CPy0a/h9/nl72/uraZ+Ac/O83gJcB3ywc1v0mP8vgV/k4Zd5+MOW9mn4Gmra/0bv+3GWXXccGuprop9t2sc6DP3YOG6/+nlSmwNwUI/hYOCemvo/5A3ZuZO3PP5dTf26jvErgb/M40+h5ms6gBs6xu/qNa2r/VLg1VQH3rcB/wwsBFYB/1pTf33H+LnA+4AnAm8FvlxT/4X8YjqW6g+fvwDs2D2vCa7DjTX1Pxpj/2w1LW/r9wN35RftW4EnNJlHx7QfT8L83051QD2wcx5j1A91fzXtE3BTx/gsqr8t+SKw4xivuY8CF9BxMBxnnaf6a6hp/5u+75seh4b6muhzmzZdh6EfG8cbGj+h7YEqcNbkDdQ9/Kam/jZgnx7z2ljTdiswK49f3WsHdbR9DziC6nsF7wSOze3Pr9tpedoPuh5fm39uB9xaU9/54r6xa1rdm7+75p+A/6E6a6x7c/6gY/x9XdNuqqn/FvAO/vxgtyfVb2rfHqf/hwMfA+7N+2xZTf3VeXtu19G2HXACcM2g889184DPAx8EHkfNb6Rt7a+mfeqxzNPzPr5tjPkfnN87b8p9H2udp/prqGn/m77vmx6Hhvqa6HObNl2HoR8bxxsaP6HtAbgZWNhjWl3gnAo8o0f9G+va8o5eDKwAPgQ8D/gX4FM19c+gOrX+OrA/8GHgIarLQc/usdz/BZ6bx18KfLNjWt1vOqNUv7mfRnX2Fx3T6t786+k4uOe2pblPd9bUvwd4bE37k4FLatp3Bc7IL9gHgQfyMs8A5tTUb/XbEtU3hxwJfKJm2gLgIuB+4Md5uD+37Tvo/LvqXkoViPeOUTPU/dW0T8Cn6bhk1NF+CvD7cea9HVVAXQXcPUZdaa+hulAZ6zXUtP9N3/dNj0NDfU30uU2brkPTbfRMtj42Ppj3wXPGW+fafvXzpDYH4DjgqT2mHVvTtiPwKuBF+fErgf+gCq5H9ZjPC6kOhjcA64CvUf3Lj63q85t9fsN1+AuqyxSbge8CT8ntI8CbaupP7xpGcvtewAU19f++ZX272o+k5jfspuvQR/2FDbfPDvlg8tdUv/H+PdVvzLX7rI/5Hwo8Po/vnLfXt/MbefYY++uhYeyvum0K7AQ8vUftWK/pHcbYpp3POZHqQPn6Htu06Wuoc5vuRHXQ+soY2/TQLe15H7wHuHyM+qb7uFH/87QXMPH3fdPjUOPXRM08Dqf6HO6IBvWn9apvug5Nt1HT/kxkKP6rjiLiTcCXUkobJ1j/Garr9DtTHWAeS3W9fgnVbzFLB5z/ZuBXwE+oPtz/fEpp0ySvw7DrG61DV/1nc33P/wkzwD7biSrEHwN8id777FBgfUrpFxGxE/Au4FnAD6k+I9rcVX8L1Vn1w/lOzl9RfUaxJLf/bc38b00pbY6InakumRw0xvy765eP1Z/8nO59cHGvbTrOa5qU0qsn8JyJbNMt69DPNv01cMkY27TRPqhZn+dS/cPTm1NK3xqrdiL1Xa+hLfvsIKrf9nvt4wm/5vrpf0R8P6V0SB5/DdUvE1+mumz2lZTSyjHqT6H6haVnfT996qo/PNev67FNG/V/QvpNtrYGqgPW3VSXKF5P/k1kjPqb8s9ZwH3A9vlxUH95rHP+ryPftTLG/G+gumxyBNWdOZuoPthcCjxuAuvwugmsQ9N1blrfaB36qB/2PruFR66Nn0N16eG5VL+pfrGmfn3H+PVd0+o+02s6/0b1Tbdp0+1T6DZtWv/9jvHXADfyyOduyyehvnt9z5zMfdzVn1PG68+W10TH+LU8ctb1GOo/82laP8g2PSW/Zseqb9SfiQyNn9D2QPOD481Ulzd2pbqldk5uf3Tnm2SA+Xe/uR4FvIzqt+BNk7QOw65vtA591A97nzU92H0eOCmPfwJYlMefQr4BYsD5N6pvuk2bbp9Ct2nT+mEfrIcdsI0P1sAP8v7aja6bCqj/3LVp/bC3aaP+TGRo/IS2hyZv5Dz9rVTX2u+kus6/GvhPquunp0/C/HtuaGCnSVqHoQdIk3Xoo37Y+6zpwW428Emqy2nXUP1tye3Af1FzQ00f829U33SbNt0+hW7TpvXDPlgPO2AbH6yBO3jkT2JuJ/99E9Ul3boQbFo/7G3aqD8TGRo/oe2h187M03oFwhPIfzMB7EL14eAhkzF/8gfmw1yHFuobrUMf9cPeZ40Odh3PexzVXZgH0+OPJfuZfz/96WObTnj7lLhN+9gHjQ52fdQPO2Ab9WecbbYzNXezNq0f9jadrP53DlPhJomnpJQm7Sta2p5/P8sYdv2wtdWfiHgc8CSqz1lGU0r3bcv5D7s/bSh9HfINDXumlH46GfVt7+Om/W/DZG/TyVR8QEmSZqYZ9WWxkqSpw4CSJBXJgJIkFcmAkiQV6f8BOE9lvqc77kIAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['Tavg'].value_counts().plot('bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dropping some columns for now\n",
    "df = df.drop(['Address', 'AddressNumberAndStreet', 'Block', 'Street'], axis = 1)\n",
    "df = df.drop('AddressAccuracy', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Species</th>\n",
       "      <th>Trap</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>NumMosquitos</th>\n",
       "      <th>WnvPresent</th>\n",
       "      <th>Station</th>\n",
       "      <th>Tmax</th>\n",
       "      <th>Tmin</th>\n",
       "      <th>Tavg</th>\n",
       "      <th>DewPoint</th>\n",
       "      <th>WetBulb</th>\n",
       "      <th>Heat</th>\n",
       "      <th>Cool</th>\n",
       "      <th>PrecipTotal</th>\n",
       "      <th>StnPressure</th>\n",
       "      <th>ResultSpeed</th>\n",
       "      <th>ResultDir</th>\n",
       "      <th>AvgSpeed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>CULEX PIPIENS/RESTUANS</td>\n",
       "      <td>T002</td>\n",
       "      <td>41.95469</td>\n",
       "      <td>-87.800991</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date                 Species  Trap  Latitude  Longitude  NumMosquitos  \\\n",
       "0 2007-05-29  CULEX PIPIENS/RESTUANS  T002  41.95469 -87.800991             1   \n",
       "\n",
       "   WnvPresent  Station  Tmax  Tmin  Tavg  DewPoint  WetBulb  Heat  Cool  \\\n",
       "0           0        1    88    60    74        58       65   0.0   9.0   \n",
       "\n",
       "   PrecipTotal  StnPressure  ResultSpeed  ResultDir  AvgSpeed  \n",
       "0          0.0        29.39          5.8         18       6.5  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(spray, how = 'left', on = 'Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Species</th>\n",
       "      <th>Trap</th>\n",
       "      <th>Latitude_x</th>\n",
       "      <th>Longitude_x</th>\n",
       "      <th>NumMosquitos</th>\n",
       "      <th>WnvPresent</th>\n",
       "      <th>Station</th>\n",
       "      <th>Tmax</th>\n",
       "      <th>Tmin</th>\n",
       "      <th>...</th>\n",
       "      <th>Heat</th>\n",
       "      <th>Cool</th>\n",
       "      <th>PrecipTotal</th>\n",
       "      <th>StnPressure</th>\n",
       "      <th>ResultSpeed</th>\n",
       "      <th>ResultDir</th>\n",
       "      <th>AvgSpeed</th>\n",
       "      <th>Time</th>\n",
       "      <th>Latitude_y</th>\n",
       "      <th>Longitude_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>CULEX PIPIENS/RESTUANS</td>\n",
       "      <td>T002</td>\n",
       "      <td>41.95469</td>\n",
       "      <td>-87.800991</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date                 Species  Trap  Latitude_x  Longitude_x  \\\n",
       "0 2007-05-29  CULEX PIPIENS/RESTUANS  T002    41.95469   -87.800991   \n",
       "\n",
       "   NumMosquitos  WnvPresent  Station  Tmax  Tmin     ...       Heat  Cool  \\\n",
       "0             1           0        1    88    60     ...        0.0   9.0   \n",
       "\n",
       "   PrecipTotal  StnPressure  ResultSpeed  ResultDir  AvgSpeed  Time  \\\n",
       "0          0.0        29.39          5.8         18       6.5   NaN   \n",
       "\n",
       "   Latitude_y  Longitude_y  \n",
       "0         NaN          NaN  \n",
       "\n",
       "[1 rows x 23 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['Latitude_y', 'Longitude_y'], axis = 1,inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'Latitude_x': 'Latitude', 'Longitude_x':'Longitude'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Species</th>\n",
       "      <th>Trap</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>NumMosquitos</th>\n",
       "      <th>WnvPresent</th>\n",
       "      <th>Station</th>\n",
       "      <th>Tmax</th>\n",
       "      <th>Tmin</th>\n",
       "      <th>...</th>\n",
       "      <th>DewPoint</th>\n",
       "      <th>WetBulb</th>\n",
       "      <th>Heat</th>\n",
       "      <th>Cool</th>\n",
       "      <th>PrecipTotal</th>\n",
       "      <th>StnPressure</th>\n",
       "      <th>ResultSpeed</th>\n",
       "      <th>ResultDir</th>\n",
       "      <th>AvgSpeed</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>CULEX PIPIENS/RESTUANS</td>\n",
       "      <td>T002</td>\n",
       "      <td>41.954690</td>\n",
       "      <td>-87.800991</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>CULEX RESTUANS</td>\n",
       "      <td>T002</td>\n",
       "      <td>41.954690</td>\n",
       "      <td>-87.800991</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>CULEX RESTUANS</td>\n",
       "      <td>T007</td>\n",
       "      <td>41.994991</td>\n",
       "      <td>-87.769279</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>CULEX PIPIENS/RESTUANS</td>\n",
       "      <td>T015</td>\n",
       "      <td>41.974089</td>\n",
       "      <td>-87.824812</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-05-29</td>\n",
       "      <td>CULEX RESTUANS</td>\n",
       "      <td>T015</td>\n",
       "      <td>41.974089</td>\n",
       "      <td>-87.824812</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>58</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.39</td>\n",
       "      <td>5.8</td>\n",
       "      <td>18</td>\n",
       "      <td>6.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date                 Species  Trap   Latitude  Longitude  \\\n",
       "0 2007-05-29  CULEX PIPIENS/RESTUANS  T002  41.954690 -87.800991   \n",
       "1 2007-05-29          CULEX RESTUANS  T002  41.954690 -87.800991   \n",
       "2 2007-05-29          CULEX RESTUANS  T007  41.994991 -87.769279   \n",
       "3 2007-05-29  CULEX PIPIENS/RESTUANS  T015  41.974089 -87.824812   \n",
       "4 2007-05-29          CULEX RESTUANS  T015  41.974089 -87.824812   \n",
       "\n",
       "   NumMosquitos  WnvPresent  Station  Tmax  Tmin  ...   DewPoint  WetBulb  \\\n",
       "0             1           0        1    88    60  ...         58       65   \n",
       "1             1           0        1    88    60  ...         58       65   \n",
       "2             1           0        1    88    60  ...         58       65   \n",
       "3             1           0        1    88    60  ...         58       65   \n",
       "4             4           0        1    88    60  ...         58       65   \n",
       "\n",
       "   Heat  Cool  PrecipTotal  StnPressure  ResultSpeed  ResultDir  AvgSpeed  \\\n",
       "0   0.0   9.0          0.0        29.39          5.8         18       6.5   \n",
       "1   0.0   9.0          0.0        29.39          5.8         18       6.5   \n",
       "2   0.0   9.0          0.0        29.39          5.8         18       6.5   \n",
       "3   0.0   9.0          0.0        29.39          5.8         18       6.5   \n",
       "4   0.0   9.0          0.0        29.39          5.8         18       6.5   \n",
       "\n",
       "   Time  \n",
       "0   NaN  \n",
       "1   NaN  \n",
       "2   NaN  \n",
       "3   NaN  \n",
       "4   NaN  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NumMosquitos</th>\n",
       "      <th>WnvPresent</th>\n",
       "      <th>Tavg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.738095</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>62.761905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10.552514</td>\n",
       "      <td>0.000637</td>\n",
       "      <td>68.591980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12.916561</td>\n",
       "      <td>0.013120</td>\n",
       "      <td>69.058822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>14.939324</td>\n",
       "      <td>0.186071</td>\n",
       "      <td>71.449347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.481064</td>\n",
       "      <td>0.056357</td>\n",
       "      <td>69.413886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      NumMosquitos  WnvPresent       Tavg\n",
       "Date                                     \n",
       "5         2.738095    0.000000  62.761905\n",
       "6        10.552514    0.000637  68.591980\n",
       "7        12.916561    0.013120  69.058822\n",
       "8        14.939324    0.186071  71.449347\n",
       "9         9.481064    0.056357  69.413886"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1= df.groupby([df['Date'].dt.month])[['Trap','NumMosquitos', 'WnvPresent', 'Tavg']].mean()\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.loc[(df['Date']<'20011-05-29')&(df['Date']>='2007-07-01')&(df['Station']==1),:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def time_calc(i): \n",
    "    if i % 500 == 0:\n",
    "        print('time '+str(i))\n",
    "        \n",
    "    # calculate time since spray\n",
    "    time_since_spray = traps_NOAA.at[i,'Date'] - spray['Date']\n",
    "    time_since_spray = time_since_spray.dt.total_seconds()\n",
    "    time_since_spray = (((time_since_spray/60)/60)/24)\n",
    "    \n",
    "    return time_since_spray"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:dsi]",
   "language": "python",
   "name": "conda-env-dsi-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}