Cleaning weather data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning the weather  data#"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "According to the documentation we have a daily weather table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the weather data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather = pd.read_csv('./weather.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is what the five first rows look like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What columns do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Get more info about the weather data frame**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2944 entries, 0 to 2943\n",
      "Data columns (total 22 columns):\n",
      "Station        2944 non-null int64\n",
      "Date           2944 non-null object\n",
      "Tmax           2944 non-null int64\n",
      "Tmin           2944 non-null int64\n",
      "Tavg           2944 non-null object\n",
      "Depart         2944 non-null object\n",
      "DewPoint       2944 non-null int64\n",
      "WetBulb        2944 non-null object\n",
      "Heat           2944 non-null object\n",
      "Cool           2944 non-null object\n",
      "Sunrise        2944 non-null object\n",
      "Sunset         2944 non-null object\n",
      "CodeSum        2944 non-null object\n",
      "Depth          2944 non-null object\n",
      "Water1         2944 non-null object\n",
      "SnowFall       2944 non-null object\n",
      "PrecipTotal    2944 non-null object\n",
      "StnPressure    2944 non-null object\n",
      "SeaLevel       2944 non-null object\n",
      "ResultSpeed    2944 non-null float64\n",
      "ResultDir      2944 non-null int64\n",
      "AvgSpeed       2944 non-null object\n",
      "dtypes: float64(1), int64(5), object(16)\n",
      "memory usage: 506.1+ KB\n"
     ]
    }
   ],
   "source": [
    "weather.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**About the data**<BR />\n",
    "2944 rows<BR />\n",
    "22 columns<BR />\n",
    "0 NULL values in the entire data set<BR />\n",
    "Different data types in the columns<BR />\n",
    "The columns need to be investigated closer.<BR />\n",
    "The pdf that belongs to this data can be very usefull when we are going to use the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Outlines for cleaning the data:**\n",
    "- If more than 5% of row in a column are missing values -> drop the rows.\n",
    "- If a column only consist of missing data, drop the column.\n",
    "- For thre M/- that are left, convert into np.NaN (works as a float)\n",
    "- Turn T into 0.1\n",
    "- All numeric values should be a float (use round, if needed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Station** - OK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Station'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is ok and do not need to be changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 2])"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Station'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Seems like the data only comes from 2 different station.<BR />\n",
    "This is good, this means that this column is binary.<BR />\n",
    "This column is no described in the documentation. I can also be that 1 and 2 stand for:<BR />\n",
    "AO1; automated station without a precipitation descriminator.<BR />\n",
    "AO2; automated station with precipitation descriminator."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1.0\n",
       "1    2.0\n",
       "2    1.0\n",
       "3    2.0\n",
       "4    1.0\n",
       "Name: Station, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Station'] = weather['Station'].astype(float)\n",
    "weather['Station'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Date** - OK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Date'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is an object.<BR />\n",
    "We want to turn it into datetime-format instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2007-05-01', '2007-05-02', '2007-05-03', ..., '2014-10-29',\n",
       "       '2014-10-30', '2014-10-31'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Look at the format of the date, looks like YYYY-mm-dd\n",
    "weather['Date'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Set a big Y so that pandas know that the the year is 4 and not 2 digits \n",
    "weather['Date'] = pd.to_datetime(weather['Date'], format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2007-05-01T00:00:00.000000000', '2007-05-02T00:00:00.000000000',\n",
       "       '2007-05-03T00:00:00.000000000', ...,\n",
       "       '2014-10-29T00:00:00.000000000', '2014-10-30T00:00:00.000000000',\n",
       "       '2014-10-31T00:00:00.000000000'], dtype='datetime64[ns]')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check and see that the data type changed\n",
    "weather['Date'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make new columns in data frame for Year, Month, Day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['Year'] = weather['Date'].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['Month'] = weather['Date'].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['Day'] = weather['Date'].dt.day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# The new columns were added as the last columns in the data frame\n",
    "# All 3 columns are the data type int64"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Over what time period is the data collected?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2007-05-01 00:00:00\n",
      "2014-10-31 00:00:00\n"
     ]
    }
   ],
   "source": [
    "# Earliest collection\n",
    "print(weather['Date'].min())\n",
    "# Latest collection\n",
    "print(weather['Date'].max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comment: datetime can not be converted into floats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Tmax** - OK<BR />\n",
    "Maximum degrees Fahrenheit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Tmax'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is ok and do not need to be changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 41,  42,  44,  45,  46,  47,  48,  49,  50,  51,  52,  53,  54,\n",
       "        55,  56,  57,  58,  59,  60,  61,  62,  63,  64,  65,  66,  67,\n",
       "        68,  69,  70,  71,  72,  73,  74,  75,  76,  77,  78,  79,  80,\n",
       "        81,  82,  83,  84,  85,  86,  87,  88,  89,  90,  91,  92,  93,\n",
       "        94,  95,  96,  97,  98,  99, 100, 101, 102, 103, 104])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Tmax'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Takes on values from 41 to 104 F."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No NULL values, M or -"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    83.0\n",
       "1    84.0\n",
       "2    59.0\n",
       "3    60.0\n",
       "4    66.0\n",
       "Name: Tmax, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Tmax'] = weather['Tmax'].astype(float)\n",
    "weather['Tmax'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Tmin** - OK<BR />\n",
    "Minimum degrees Fahrenheit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Tmin'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is ok and do not need to be changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([29, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46,\n",
       "       47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63,\n",
       "       64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80,\n",
       "       81, 82, 83])"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Tmin'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Takes on values from 29 to 83 F."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No NULL values, M or -"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    50.0\n",
       "1    52.0\n",
       "2    42.0\n",
       "3    43.0\n",
       "4    46.0\n",
       "Name: Tmin, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Tmin'] = weather['Tmin'].astype(float)\n",
    "weather['Tmin'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Tavg** - OK<BR />\n",
    "Average degrees Fahrenheit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Tavg'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is object, we need to find out why. We want it to be numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46',\n",
       "       '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57',\n",
       "       '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68',\n",
       "       '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79',\n",
       "       '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90',\n",
       "       '91', '92', '93', '94', 'M'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Tavg'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hava a M instead of a number in our data.<BR />\n",
    "According to the documentation M = Missing Data, or data that are not normally reported for the station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73    138\n",
       "77    117\n",
       "70    117\n",
       "75    110\n",
       "71    109\n",
       "74    107\n",
       "72    104\n",
       "69    103\n",
       "78    102\n",
       "76    100\n",
       "68     99\n",
       "79     98\n",
       "66     93\n",
       "67     89\n",
       "61     88\n",
       "64     86\n",
       "65     84\n",
       "80     84\n",
       "63     81\n",
       "57     67\n",
       "62     66\n",
       "60     61\n",
       "50     57\n",
       "81     55\n",
       "58     49\n",
       "53     49\n",
       "54     48\n",
       "55     48\n",
       "82     48\n",
       "56     46\n",
       "52     46\n",
       "59     45\n",
       "51     36\n",
       "83     34\n",
       "49     29\n",
       "45     28\n",
       "46     24\n",
       "47     24\n",
       "84     21\n",
       "44     19\n",
       "48     17\n",
       "85     16\n",
       "86     16\n",
       "42     15\n",
       "43     12\n",
       "M      11\n",
       "87      9\n",
       "41      7\n",
       "40      5\n",
       "88      4\n",
       "39      4\n",
       "91      4\n",
       "89      4\n",
       "90      2\n",
       "37      2\n",
       "38      2\n",
       "36      2\n",
       "93      1\n",
       "92      1\n",
       "94      1\n",
       "Name: Tavg, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Tavg'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 11 rows out of 2944 rows with missing values for the average temperature.\n",
    "\n",
    "Drop the rows with missing data (by making new dataframe only containing the rows that were not M for Tavg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather = weather[weather['Tavg'] != 'M']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    67.0\n",
       "1    68.0\n",
       "2    51.0\n",
       "3    52.0\n",
       "4    56.0\n",
       "Name: Tavg, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Tavg'] = weather['Tavg'].astype(float)\n",
    "weather['Tavg'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Depart** - OK<BR />\n",
    "This column indicates DEPARTURE FROM NORMAL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Depart'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is object, we need to find out why. We want it to be numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([' 0', ' 1', ' 2', ' 3', ' 4', ' 5', ' 6', ' 7', ' 8', ' 9', '-1',\n",
       "       '-10', '-11', '-12', '-13', '-14', '-15', '-16', '-17', '-2', '-3',\n",
       "       '-4', '-5', '-6', '-7', '-8', '-9', '10', '11', '12', '13', '14',\n",
       "       '15', '16', '17', '18', '19', '20', '21', '22', '23', 'M'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Depart'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hava a M instead of a number in our data.<BR />\n",
    "According to the documentation M = Missing Data, or data that are not normally reported for the station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "M      1461\n",
       " 2       93\n",
       "-1       84\n",
       "-2       80\n",
       " 5       77\n",
       " 7       76\n",
       " 1       76\n",
       " 3       75\n",
       " 0       74\n",
       "-3       72\n",
       " 4       71\n",
       " 6       67\n",
       " 8       59\n",
       "-5       57\n",
       "-4       56\n",
       "-6       50\n",
       " 9       47\n",
       "10       46\n",
       "-8       43\n",
       "-7       30\n",
       "12       28\n",
       "11       28\n",
       "-9       25\n",
       "13       23\n",
       "14       22\n",
       "-10      22\n",
       "15       15\n",
       "16       12\n",
       "-11      10\n",
       "-12       8\n",
       "17        7\n",
       "18        6\n",
       "-14       6\n",
       "-13       5\n",
       "19        4\n",
       "20        4\n",
       "22        3\n",
       "-16       3\n",
       "-15       3\n",
       "21        2\n",
       "-17       2\n",
       "23        1\n",
       "Name: Depart, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Depart'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 1472 rows out of 2944 rows with missing values for the depart column.\n",
    "\n",
    "Replace all the M with np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['Depart'] = weather['Depart'].replace(['M'], np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    14.0\n",
       "1     NaN\n",
       "2    -3.0\n",
       "3     NaN\n",
       "4     2.0\n",
       "Name: Depart, dtype: float64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Depart'] = weather['Depart'].astype(float)\n",
    "weather['Depart'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**DewPoint**<BR />\n",
    "This column give us the AVERAGE DEW POINT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['DewPoint'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is ok and do not need to be changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38,\n",
       "       39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55,\n",
       "       56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72,\n",
       "       73, 74, 75])"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['DewPoint'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Takes on values from 22 to 75 F."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No NULL values, M or -"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    51.0\n",
       "1    51.0\n",
       "2    42.0\n",
       "3    42.0\n",
       "4    40.0\n",
       "Name: DewPoint, dtype: float64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['DewPoint'] = weather['DewPoint'].astype(float)\n",
    "weather['DewPoint'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**WetBulb** - OK<BR />\n",
    "This column give us the AVERAGE WET BULB<BR />\n",
    "Explenation to Wet-Bulb here https://en.wikipedia.org/wiki/Wet-bulb_temperature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['WetBulb'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is object, we need to find out why. We want it to be numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42',\n",
       "       '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53',\n",
       "       '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64',\n",
       "       '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75',\n",
       "       '76', '77', '78', 'M'], dtype=object)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['WetBulb'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hava a M instead of a number in our data.<BR />\n",
    "According to the documentation M = Missing Data, or data that are not normally reported for the station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "63    135\n",
       "65    131\n",
       "59    129\n",
       "61    122\n",
       "64    121\n",
       "62    118\n",
       "67    117\n",
       "66    113\n",
       "60    111\n",
       "69    107\n",
       "57    105\n",
       "70    102\n",
       "55     99\n",
       "68     98\n",
       "71     97\n",
       "58     95\n",
       "56     95\n",
       "52     78\n",
       "46     72\n",
       "72     71\n",
       "53     69\n",
       "47     59\n",
       "51     57\n",
       "73     56\n",
       "54     54\n",
       "50     53\n",
       "45     52\n",
       "48     51\n",
       "49     50\n",
       "74     49\n",
       "44     45\n",
       "42     35\n",
       "43     31\n",
       "40     31\n",
       "75     20\n",
       "41     19\n",
       "38     17\n",
       "39     15\n",
       "36      9\n",
       "77      8\n",
       "76      8\n",
       "35      8\n",
       "34      5\n",
       "37      5\n",
       "33      5\n",
       "M       4\n",
       "78      1\n",
       "32      1\n",
       "Name: WetBulb, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['WetBulb'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 4 rows out of 2944 rows with missing values for the WetBulb column.\n",
    "\n",
    "Drop the rows with missing data (by making new dataframe only containing the rows that were not M for WetBulb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather = weather[weather['WetBulb'] != 'M']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    56.0\n",
       "1    57.0\n",
       "2    47.0\n",
       "3    47.0\n",
       "4    48.0\n",
       "Name: WetBulb, dtype: float64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['WetBulb'] = weather['WetBulb'].astype(float)\n",
    "weather['WetBulb'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2929 entries, 0 to 2943\n",
      "Data columns (total 25 columns):\n",
      "Station        2929 non-null float64\n",
      "Date           2929 non-null datetime64[ns]\n",
      "Tmax           2929 non-null float64\n",
      "Tmin           2929 non-null float64\n",
      "Tavg           2929 non-null float64\n",
      "Depart         1469 non-null float64\n",
      "DewPoint       2929 non-null float64\n",
      "WetBulb        2929 non-null float64\n",
      "Heat           2929 non-null object\n",
      "Cool           2929 non-null object\n",
      "Sunrise        2929 non-null object\n",
      "Sunset         2929 non-null object\n",
      "CodeSum        2929 non-null object\n",
      "Depth          2929 non-null object\n",
      "Water1         2929 non-null object\n",
      "SnowFall       2929 non-null object\n",
      "PrecipTotal    2929 non-null object\n",
      "StnPressure    2929 non-null object\n",
      "SeaLevel       2929 non-null object\n",
      "ResultSpeed    2929 non-null float64\n",
      "ResultDir      2929 non-null int64\n",
      "AvgSpeed       2929 non-null object\n",
      "Year           2929 non-null int64\n",
      "Month          2929 non-null int64\n",
      "Day            2929 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(8), int64(4), object(12)\n",
      "memory usage: 595.0+ KB\n"
     ]
    }
   ],
   "source": [
    "# ignoring the error message since the data type was successfully changed.\n",
    "weather.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Heat** - OK<BR />\n",
    "According to the documentation:<BR />\n",
    "Degree Days: Base 65 F<BR />\n",
    "HEATING (SEASON BEGINS WITH JULY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Heat'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is object, we need to find out why. We want it to be numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0', '1', '10', '11', '12', '13', '14', '15', '16', '17', '18',\n",
       "       '19', '2', '20', '21', '22', '23', '24', '25', '26', '27', '28',\n",
       "       '29', '3', '4', '5', '6', '7', '8', '9'], dtype=object)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Heat'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hava a M instead of a number in our data.<BR />\n",
    "According to the documentation M = Missing Data, or data that are not normally reported for the station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1866\n",
       "4       88\n",
       "1       86\n",
       "2       81\n",
       "8       67\n",
       "3       66\n",
       "5       61\n",
       "15      57\n",
       "7       49\n",
       "12      49\n",
       "10      48\n",
       "11      48\n",
       "9       46\n",
       "13      46\n",
       "6       45\n",
       "14      36\n",
       "16      29\n",
       "20      28\n",
       "19      24\n",
       "18      24\n",
       "21      19\n",
       "17      17\n",
       "23      15\n",
       "22      12\n",
       "24       7\n",
       "25       5\n",
       "26       4\n",
       "27       2\n",
       "29       2\n",
       "28       2\n",
       "Name: Heat, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Heat'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 11 rows out of 2944 rows with missing values for the Heat column.\n",
    "\n",
    "Drop the rows with missing data (by making new dataframe only containing the rows that were not M for Heat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather = weather[weather['Heat'] != 'M']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     0.0\n",
       "1     0.0\n",
       "2    14.0\n",
       "3    13.0\n",
       "4     9.0\n",
       "Name: Heat, dtype: float64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Heat'] = weather['Heat'].astype(float)\n",
    "weather['Heat'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Cool** - OK<BR />\n",
    "According to the documentation:<BR />\n",
    "Degree Days: Base 65 F<BR />\n",
    "COOLING (SEASON BEGINS WITH JANUARY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Cool'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is object, we need to find out why. We want it to be numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([' 0', ' 1', ' 2', ' 3', ' 4', ' 5', ' 6', ' 7', ' 8', ' 9', '10',\n",
       "       '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21',\n",
       "       '22', '23', '24', '25', '26', '27', '28', '29'], dtype=object)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Cool'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hava a M instead of a number in our data.<BR />\n",
    "According to the documentation M = Missing Data, or data that are not normally reported for the station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       " 0    1147\n",
       " 8     137\n",
       " 5     117\n",
       "12     116\n",
       "10     110\n",
       " 6     108\n",
       " 9     107\n",
       " 7     104\n",
       " 4     103\n",
       "13     101\n",
       "11     100\n",
       " 3      99\n",
       "14      98\n",
       " 1      93\n",
       " 2      89\n",
       "15      84\n",
       "16      55\n",
       "17      48\n",
       "18      34\n",
       "19      21\n",
       "21      16\n",
       "20      16\n",
       "22       9\n",
       "23       4\n",
       "24       4\n",
       "26       4\n",
       "25       2\n",
       "27       1\n",
       "29       1\n",
       "28       1\n",
       "Name: Cool, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Cool'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 11 rows out of 2944 rows with missing values for the Heat column.\n",
    "\n",
    "Drop the rows with missing data (by making new dataframe only containing the rows that were not M for Cool)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather = weather[weather['Cool'] != 'M']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2.0\n",
       "1    3.0\n",
       "2    0.0\n",
       "3    0.0\n",
       "4    0.0\n",
       "Name: Cool, dtype: float64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Cool'] = weather['Cool'].astype(float)\n",
    "weather['Cool'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Sunrise** - OK<BR />\n",
    "According to the documentation:<BR />\n",
    "SUNRISE  (Calculated, not observed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Sunrise'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is object, we need to find out why. We want it to be numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['-', '0416', '0417', '0418', '0419', '0420', '0421', '0422',\n",
       "       '0423', '0424', '0425', '0426', '0427', '0428', '0429', '0430',\n",
       "       '0431', '0432', '0433', '0434', '0435', '0436', '0437', '0438',\n",
       "       '0439', '0440', '0441', '0442', '0443', '0444', '0445', '0446',\n",
       "       '0447', '0448', '0449', '0450', '0451', '0452', '0453', '0454',\n",
       "       '0455', '0456', '0457', '0458', '0459', '0500', '0501', '0502',\n",
       "       '0503', '0504', '0505', '0506', '0508', '0509', '0510', '0511',\n",
       "       '0512', '0513', '0514', '0515', '0516', '0517', '0518', '0519',\n",
       "       '0520', '0521', '0522', '0523', '0524', '0525', '0526', '0527',\n",
       "       '0528', '0529', '0530', '0531', '0532', '0533', '0535', '0536',\n",
       "       '0537', '0538', '0539', '0540', '0541', '0542', '0543', '0544',\n",
       "       '0545', '0546', '0547', '0548', '0549', '0550', '0552', '0553',\n",
       "       '0554', '0555', '0556', '0557', '0558', '0559', '0600', '0602',\n",
       "       '0603', '0604', '0605', '0606', '0607', '0608', '0610', '0611',\n",
       "       '0612', '0613', '0614', '0616', '0617', '0618', '0619', '0620',\n",
       "       '0622', '0623'], dtype=object)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Sunrise'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hava a - instead of a number in our data.<BR />\n",
    "According to the documentation - = values not available for sunrise/sunset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-       1460\n",
       "0416     104\n",
       "0417      64\n",
       "0419      40\n",
       "0425      32\n",
       "0422      32\n",
       "0420      32\n",
       "0418      31\n",
       "0423      24\n",
       "0433      24\n",
       "0421      24\n",
       "0428      24\n",
       "0427      16\n",
       "0431      16\n",
       "0439      16\n",
       "0446      16\n",
       "0437      16\n",
       "0438      16\n",
       "0426      16\n",
       "0444      16\n",
       "0441      16\n",
       "0447      16\n",
       "0424      16\n",
       "0430      16\n",
       "0443      16\n",
       "0432      16\n",
       "0429      16\n",
       "0434      16\n",
       "0448      16\n",
       "0442      16\n",
       "        ... \n",
       "0501       8\n",
       "0535       8\n",
       "0611       8\n",
       "0451       8\n",
       "0559       8\n",
       "0504       8\n",
       "0458       8\n",
       "0541       8\n",
       "0521       8\n",
       "0600       8\n",
       "0604       8\n",
       "0620       8\n",
       "0556       8\n",
       "0543       8\n",
       "0452       8\n",
       "0537       8\n",
       "0616       8\n",
       "0456       8\n",
       "0554       8\n",
       "0548       8\n",
       "0533       8\n",
       "0440       8\n",
       "0503       8\n",
       "0538       8\n",
       "0514       8\n",
       "0607       8\n",
       "0517       8\n",
       "0540       8\n",
       "0454       7\n",
       "0455       7\n",
       "Name: Sunrise, Length: 122, dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Sunrise'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 1472 rows out of 2944 rows with missing values for the Sunrise column.\n",
    "#### Keep the column but do not do anything with it. ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# I case we want to use it, we are going to handle the format and the -"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Sunset** - OK<BR />\n",
    "According to the documentation:<BR />\n",
    "SUNSET   (Calculated, not observed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Sunset'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is object, we need to find out why. We want it to be numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['-', '1647', '1649', '1650', '1651', '1653', '1654', '1656',\n",
       "       '1657', '1658', '1660', '1701', '1703', '1704', '1706', '1707',\n",
       "       '1709', '1711', '1712', '1714', '1715', '1717', '1719', '1720',\n",
       "       '1722', '1724', '1725', '1727', '1729', '1730', '1732', '1734',\n",
       "       '1735', '1737', '1739', '1741', '1742', '1744', '1746', '1748',\n",
       "       '1749', '1751', '1753', '1755', '1756', '1758', '1760', '1803',\n",
       "       '1805', '1807', '1809', '1810', '1812', '1814', '1815', '1817',\n",
       "       '1819', '1821', '1822', '1824', '1826', '1827', '1829', '1831',\n",
       "       '1832', '1834', '1835', '1837', '1839', '1840', '1842', '1843',\n",
       "       '1845', '1846', '1848', '1849', '1850', '1851', '1852', '1853',\n",
       "       '1854', '1855', '1856', '1857', '1858', '1859', '1860', '1900',\n",
       "       '1901', '1902', '1903', '1904', '1905', '1906', '1907', '1908',\n",
       "       '1909', '1910', '1911', '1912', '1913', '1914', '1915', '1916',\n",
       "       '1917', '1918', '1919', '1920', '1921', '1922', '1923', '1924',\n",
       "       '1925', '1926', '1927', '1928', '1929', '1930', '1931'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['Sunset'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hava a - instead of a number in our data.<BR />\n",
    "According to the documentation - = values not available for sunrise/sunset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-       1460\n",
       "1931      95\n",
       "1930      56\n",
       "1929      48\n",
       "1923      32\n",
       "1928      32\n",
       "1925      32\n",
       "1927      32\n",
       "1920      24\n",
       "1918      24\n",
       "1916      24\n",
       "1926      24\n",
       "1849      16\n",
       "1902      16\n",
       "1913      16\n",
       "1910      16\n",
       "1858      16\n",
       "1852      16\n",
       "1851      16\n",
       "1907      16\n",
       "1924      16\n",
       "1915      16\n",
       "1909      16\n",
       "1856      16\n",
       "1908      16\n",
       "1917      16\n",
       "1903      16\n",
       "1904      16\n",
       "1855      16\n",
       "1922      16\n",
       "        ... \n",
       "1842       8\n",
       "1753       8\n",
       "1821       8\n",
       "1748       8\n",
       "1756       8\n",
       "1826       8\n",
       "1730       8\n",
       "1840       8\n",
       "1727       8\n",
       "1831       8\n",
       "1857       8\n",
       "1724       8\n",
       "1741       8\n",
       "1839       8\n",
       "1734       8\n",
       "1658       8\n",
       "1812       8\n",
       "1824       8\n",
       "1714       8\n",
       "1701       8\n",
       "1746       8\n",
       "1843       8\n",
       "1845       8\n",
       "1656       8\n",
       "1649       8\n",
       "1846       8\n",
       "1850       8\n",
       "1715       8\n",
       "1744       8\n",
       "1900       7\n",
       "Name: Sunset, Length: 119, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Sunset'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 1472 rows out of 2944 rows with missing values for the Sunset column.\n",
    "#### Keep the column but do not do anything with it. ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# I case we want to use it, we are going to handle the format and the -"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**CodeSum**<BR />\n",
    "Significant Weather Types (Weather Phenomena)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get unique values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([' ', 'BCFG BR', 'BR', 'BR HZ', 'BR HZ FU', 'BR HZ VCFG', 'BR VCTS',\n",
       "       'DZ', 'DZ BR', 'DZ BR HZ', 'FG BR HZ', 'FG+', 'FG+ BCFG BR',\n",
       "       'FG+ BR', 'FG+ BR HZ', 'FG+ FG BR', 'FG+ FG BR HZ', 'FG+ MIFG BR',\n",
       "       'FU', 'HZ', 'HZ FU', 'HZ VCTS', 'MIFG BCFG BR', 'RA', 'RA BCFG BR',\n",
       "       'RA BR', 'RA BR FU', 'RA BR HZ', 'RA BR HZ FU', 'RA BR HZ VCFG',\n",
       "       'RA BR HZ VCTS', 'RA BR SQ', 'RA BR VCFG', 'RA BR VCTS', 'RA DZ',\n",
       "       'RA DZ BR', 'RA DZ BR HZ', 'RA DZ FG+ BCFG BR', 'RA DZ FG+ BR',\n",
       "       'RA DZ FG+ BR HZ', 'RA DZ FG+ FG BR', 'RA DZ SN', 'RA FG BR',\n",
       "       'RA FG+ BR', 'RA FG+ MIFG BR', 'RA HZ', 'RA SN', 'RA SN BR',\n",
       "       'RA VCTS', 'TS', 'TS BR', 'TS BR HZ', 'TS HZ', 'TS RA', 'TS RA BR',\n",
       "       'TS RA BR HZ', 'TS RA FG+ FG BR', 'TS TSRA', 'TS TSRA BR',\n",
       "       'TS TSRA BR HZ', 'TS TSRA GR RA BR', 'TS TSRA HZ', 'TS TSRA RA',\n",
       "       'TS TSRA RA BR', 'TS TSRA RA BR HZ', 'TS TSRA RA BR HZ VCTS',\n",
       "       'TS TSRA RA BR VCTS', 'TS TSRA RA FG BR', 'TS TSRA RA FG BR HZ',\n",
       "       'TS TSRA RA HZ', 'TS TSRA RA VCTS', 'TS TSRA VCFG', 'TSRA',\n",
       "       'TSRA BR', 'TSRA BR HZ', 'TSRA BR HZ FU', 'TSRA BR HZ VCTS',\n",
       "       'TSRA BR SQ', 'TSRA DZ BR HZ', 'TSRA DZ FG+ FG BR HZ',\n",
       "       'TSRA FG+ BR', 'TSRA FG+ BR HZ', 'TSRA HZ', 'TSRA RA',\n",
       "       'TSRA RA BR', 'TSRA RA BR HZ', 'TSRA RA BR HZ VCTS',\n",
       "       'TSRA RA BR VCTS', 'TSRA RA DZ BR', 'TSRA RA DZ BR HZ',\n",
       "       'TSRA RA FG BR', 'TSRA RA FG+ BR', 'TSRA RA FG+ FG BR',\n",
       "       'TSRA RA FG+ FG BR HZ', 'TSRA RA HZ', 'TSRA RA HZ FU',\n",
       "       'TSRA RA VCTS', 'VCTS'], dtype=object)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['CodeSum'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By hand turn it into dummies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "col_list = ['','BCFG','BR','DZ','FG','FG+','FU','GR','HZ','MIFG','RA','SN','SQ','TS','TSRA','VCFG','VCTS'] #unique values\n",
    "df_temp = pd.DataFrame(columns = col_list) # take unique values and put as headers in new df\n",
    "weather = pd.concat([weather, df_temp], axis = 1) # concat weather and new df\n",
    "\n",
    "weather[col_list] = 0 # set all the new column to 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i, _ in weather.iterrows(): # use iterrows to get correct index (did not work with len(weather) bc index were missing row numbers since we removed rows)\n",
    "    codesum_split = weather['CodeSum'][i].split(' ') # look at codesum and split at ' '\n",
    "    for item in codesum_split:\n",
    "        weather.set_value(i, item, 1) # put 1 instead of 0 in the right column if the string of letters existed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>GR</th>\n",
       "      <th>HZ</th>\n",
       "      <th>MIFG</th>\n",
       "      <th>RA</th>\n",
       "      <th>SN</th>\n",
       "      <th>SQ</th>\n",
       "      <th>TS</th>\n",
       "      <th>TSRA</th>\n",
       "      <th>VCFG</th>\n",
       "      <th>VCTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2007-05-01</td>\n",
       "      <td>83.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2007-05-01</td>\n",
       "      <td>84.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2007-05-02</td>\n",
       "      <td>59.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2007-05-02</td>\n",
       "      <td>60.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2007-05-03</td>\n",
       "      <td>66.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Station       Date  Tmax  Tmin  Tavg  Depart  DewPoint  WetBulb  Heat  \\\n",
       "0      1.0 2007-05-01  83.0  50.0  67.0    14.0      51.0     56.0   0.0   \n",
       "1      2.0 2007-05-01  84.0  52.0  68.0     NaN      51.0     57.0   0.0   \n",
       "2      1.0 2007-05-02  59.0  42.0  51.0    -3.0      42.0     47.0  14.0   \n",
       "3      2.0 2007-05-02  60.0  43.0  52.0     NaN      42.0     47.0  13.0   \n",
       "4      1.0 2007-05-03  66.0  46.0  56.0     2.0      40.0     48.0   9.0   \n",
       "\n",
       "   Cool  ...  GR HZ MIFG RA SN SQ TS TSRA VCFG  VCTS  \n",
       "0   2.0  ...   0  0    0  0  0  0  0    0    0     0  \n",
       "1   3.0  ...   0  0    0  0  0  0  0    0    0     0  \n",
       "2   0.0  ...   0  0    0  0  0  0  0    0    0     0  \n",
       "3   0.0  ...   0  1    0  0  0  0  0    0    0     0  \n",
       "4   0.0  ...   0  0    0  0  0  0  0    0    0     0  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Depth** - OK<BR />\n",
    "INCHES<BR />\n",
    "I can not understand from the documentation what this is.<BR />\n",
    "But I guess depth of snow/ice or rain maybe..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0', 'M'], dtype=object)"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Depth'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We only have the data 0 inches and missing data. \n",
    "\n",
    "Drop this column from dataframe!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Station', 'Date', 'Tmax', 'Tmin', 'Tavg', 'Depart', 'DewPoint',\n",
       "       'WetBulb', 'Heat', 'Cool', 'Sunrise', 'Sunset', 'CodeSum', 'Water1',\n",
       "       'SnowFall', 'PrecipTotal', 'StnPressure', 'SeaLevel', 'ResultSpeed',\n",
       "       'ResultDir', 'AvgSpeed', 'Year', 'Month', 'Day', '', 'BCFG', 'BR', 'DZ',\n",
       "       'FG', 'FG+', 'FU', 'GR', 'HZ', 'MIFG', 'RA', 'SN', 'SQ', 'TS', 'TSRA',\n",
       "       'VCFG', 'VCTS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather = weather.drop(['Depth'], axis=1)\n",
    "\n",
    "# make sure the column is gone\n",
    "weather.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Water1** - OK<BR />\n",
    "WATER EQUIVALENT\n",
    "I can not understand from the documentation what this is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['M'], dtype=object)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['Water1'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This column only contains missing data. \n",
    "\n",
    "Drop this column from dataframe!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Station', 'Date', 'Tmax', 'Tmin', 'Tavg', 'Depart', 'DewPoint',\n",
       "       'WetBulb', 'Heat', 'Cool', 'Sunrise', 'Sunset', 'CodeSum', 'SnowFall',\n",
       "       'PrecipTotal', 'StnPressure', 'SeaLevel', 'ResultSpeed', 'ResultDir',\n",
       "       'AvgSpeed', 'Year', 'Month', 'Day', '', 'BCFG', 'BR', 'DZ', 'FG', 'FG+',\n",
       "       'FU', 'GR', 'HZ', 'MIFG', 'RA', 'SN', 'SQ', 'TS', 'TSRA', 'VCFG',\n",
       "       'VCTS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather = weather.drop(['Water1'], axis=1)\n",
    "\n",
    "# make sure the column is gone\n",
    "weather.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**SnowFall** - OK<BR />\n",
    "INCHES (24-HR PERIOD ENDING AT INDICATED LOCAL STANDARD TIME)<BR />\n",
    "SNOWFALL (INCHES AND TENTHS)(2400 LST)- Not all stations report snow totals<BR />\n",
    "T = TRACE<BR />\n",
    "M = MISSING DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0.0', 'M', '  T', '0.1'], dtype=object)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['SnowFall'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "M      1460\n",
       "0.0    1456\n",
       "  T      12\n",
       "0.1       1\n",
       "Name: SnowFall, dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['SnowFall'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 1472 rows out of 2944 rows with missing values for the SnowFall column.\n",
    "\n",
    "Replace '  T' with 0.1 and 'M' with np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['SnowFall'] = weather['SnowFall'].replace(['  T'], '0.1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['SnowFall'] = weather['SnowFall'].replace(['M'], np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0.0', nan, '0.1'], dtype=object)"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['SnowFall'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.0\n",
       "1    NaN\n",
       "2    0.0\n",
       "3    NaN\n",
       "4    0.0\n",
       "Name: SnowFall, dtype: float64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['SnowFall'] = weather['SnowFall'].astype(float)\n",
    "weather['SnowFall'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**PrecipTotal** - OK<BR />\n",
    "INCHES (24-HR PERIOD ENDING AT INDICATED LOCAL STANDARD TIME)<BR />\n",
    "SNOWFALL (INCHES AND TENTHS)(2400 LST)- Not all stations report snow totals<BR />\n",
    "AND<BR />\n",
    "WATER EQUIVALENT(INCHES & HUNDREDTHS(2400 LST) RAINFALL & MELTED SNOW<BR />\n",
    "T = TRACE<BR />\n",
    "M = MISSING DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0.00', '  T', '0.13', '0.02', '0.38', '0.60', '0.14', '0.07',\n",
       "       '0.11', '0.09', '1.01', '0.28', '0.04', '0.08', '0.01', '0.53',\n",
       "       '0.19', '0.21', '0.32', '0.39', '0.31', '0.42', '0.27', '0.16',\n",
       "       '0.58', '0.93', '0.05', '0.34', '0.15', '0.35', 'M', '0.40',\n",
       "       '0.66', '0.30', '0.24', '0.43', '1.55', '0.92', '0.89', '0.17',\n",
       "       '0.03', '1.43', '0.97', '0.26', '1.31', '0.06', '0.46', '0.29',\n",
       "       '0.23', '0.41', '0.45', '0.83', '1.33', '0.91', '0.48', '0.37',\n",
       "       '0.88', '2.35', '1.96', '0.20', '0.25', '0.18', '0.67', '0.36',\n",
       "       '0.33', '1.28', '0.74', '0.76', '0.71', '0.95', '1.46', '0.12',\n",
       "       '0.52', '0.64', '0.22', '1.24', '0.72', '0.73', '0.65', '1.61',\n",
       "       '1.22', '0.50', '1.05', '2.43', '0.59', '2.90', '2.68', '1.23',\n",
       "       '0.62', '6.64', '3.07', '1.44', '1.75', '0.82', '0.80', '0.86',\n",
       "       '0.63', '0.55', '1.03', '0.70', '1.73', '1.38', '0.44', '1.14',\n",
       "       '1.07', '3.97', '0.87', '0.78', '1.12', '0.68', '0.10', '0.61',\n",
       "       '0.54', '1.19', '0.75', '0.84', '1.29', '0.81', '1.15', '1.49',\n",
       "       '1.57', '1.02', '1.30', '0.51', '0.85', '0.79', '0.77', '2.79',\n",
       "       '3.15', '3.64', '4.73', '1.60', '0.57', '2.24', '1.90', '1.06',\n",
       "       '1.88', '0.69', '2.17', '6.86', '0.49', '1.11', '1.21', '0.90',\n",
       "       '1.34', '0.47', '0.56', '3.66', '1.09', '1.25', '1.35', '2.20',\n",
       "       '1.48', '0.96', '1.16', '1.04', '2.03', '2.09', '1.26', '2.60',\n",
       "       '2.06', '3.31', '2.76', '1.82', '0.98', '1.58', '3.17', '1.18'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['PrecipTotal'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.00    1570\n",
       "  T      317\n",
       "0.01     126\n",
       "0.02      63\n",
       "0.03      46\n",
       "0.04      35\n",
       "0.05      32\n",
       "0.12      28\n",
       "0.08      28\n",
       "0.06      26\n",
       "0.07      23\n",
       "0.09      21\n",
       "0.16      21\n",
       "0.11      20\n",
       "0.14      19\n",
       "0.17      17\n",
       "0.18      14\n",
       "0.19      14\n",
       "0.13      14\n",
       "0.28      14\n",
       "0.15      13\n",
       "0.20      13\n",
       "0.26      11\n",
       "0.25      11\n",
       "0.23      11\n",
       "0.24      10\n",
       "0.10      10\n",
       "0.29       9\n",
       "0.31       9\n",
       "0.21       9\n",
       "        ... \n",
       "2.90       1\n",
       "1.82       1\n",
       "1.75       1\n",
       "3.17       1\n",
       "2.06       1\n",
       "1.35       1\n",
       "1.18       1\n",
       "3.66       1\n",
       "1.11       1\n",
       "2.60       1\n",
       "1.38       1\n",
       "1.57       1\n",
       "1.04       1\n",
       "3.97       1\n",
       "1.58       1\n",
       "2.17       1\n",
       "1.26       1\n",
       "3.31       1\n",
       "1.46       1\n",
       "1.30       1\n",
       "1.24       1\n",
       "2.76       1\n",
       "1.90       1\n",
       "1.96       1\n",
       "1.34       1\n",
       "1.44       1\n",
       "3.64       1\n",
       "2.79       1\n",
       "1.19       1\n",
       "1.49       1\n",
       "Name: PrecipTotal, Length: 168, dtype: int64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['PrecipTotal'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have to handle 318 T and some smaller number of M.\n",
    "Since T stands for trace, that can be replaces by a small number."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace '  T' with 0.1 and 'M' with np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['PrecipTotal'] = weather['PrecipTotal'].replace(['  T'], '0.1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather['PrecipTotal'] = weather['PrecipTotal'].replace(['M'], np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.0\n",
       "1    0.0\n",
       "2    0.0\n",
       "3    0.0\n",
       "4    0.0\n",
       "Name: PrecipTotal, dtype: float64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['PrecipTotal'] = weather['PrecipTotal'].astype(float)\n",
    "weather['PrecipTotal'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**StnPressure** - OK<BR />\n",
    "Pressure, INCHES OF HG<BR />\n",
    "AVERAGE STATION PRESSURE<BR />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['29.10', '29.18', '29.38', '29.44', '29.39', '29.46', '29.31',\n",
       "       '29.40', '29.57', '29.62', '29.29', '29.36', '29.21', '29.28',\n",
       "       '29.20', '29.26', '29.33', '29.49', '29.54', '29.55', '29.23',\n",
       "       '29.13', '29.19', '29.53', '29.60', '29.34', '29.41', '29.47',\n",
       "       '29.51', '29.42', '29.43', '29.25', '29.03', '28.82', '28.87',\n",
       "       '28.88', '29.16', '29.07', '28.84', '28.91', '29.24', 'M', '29.30',\n",
       "       '29.12', '29.45', '29.56', '29.32', '29.05', '29.11', '29.06',\n",
       "       '29.22', '29.08', '29.14', '29.37', '29.35', '29.15', '29.17',\n",
       "       '29.48', '29.52', '29.27', '29.50', '28.59', '28.67', '28.75',\n",
       "       '29.02', '29.79', '29.86', '29.63', '29.70', '28.95', '29.01',\n",
       "       '28.79', '28.85', '28.97', '28.89', '28.94', '28.93', '28.98',\n",
       "       '28.96', '29.00', '29.66', '29.09', '28.90', '29.04', '29.59',\n",
       "       '29.65', '29.58', '29.61', '29.64', '29.71', '29.67', '28.80',\n",
       "       '28.73', '29.68', '28.74', '28.55', '28.63', '28.92', '28.99',\n",
       "       '28.81', '28.71', '28.69', '28.77', '28.83'], dtype=object)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['StnPressure'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29.34    128\n",
       "29.28    124\n",
       "29.26    123\n",
       "29.21    107\n",
       "29.31    106\n",
       "29.23    103\n",
       "29.36     94\n",
       "29.41     90\n",
       "29.39     88\n",
       "29.18     86\n",
       "29.29     86\n",
       "29.24     82\n",
       "29.33     80\n",
       "29.44     70\n",
       "29.38     64\n",
       "29.13     64\n",
       "29.15     62\n",
       "29.20     60\n",
       "29.47     57\n",
       "29.16     54\n",
       "29.25     52\n",
       "29.10     47\n",
       "29.35     47\n",
       "29.49     46\n",
       "29.42     41\n",
       "29.08     41\n",
       "29.19     39\n",
       "29.40     37\n",
       "29.32     36\n",
       "29.30     36\n",
       "        ... \n",
       "29.67      4\n",
       "28.91      4\n",
       "29.66      4\n",
       "29.61      4\n",
       "28.82      3\n",
       "28.94      3\n",
       "28.80      3\n",
       "28.93      3\n",
       "28.99      3\n",
       "29.68      3\n",
       "28.96      3\n",
       "28.84      2\n",
       "28.85      2\n",
       "28.81      2\n",
       "28.71      2\n",
       "M          2\n",
       "29.71      1\n",
       "28.55      1\n",
       "28.74      1\n",
       "28.75      1\n",
       "29.79      1\n",
       "28.69      1\n",
       "28.88      1\n",
       "28.67      1\n",
       "28.73      1\n",
       "28.59      1\n",
       "28.83      1\n",
       "28.63      1\n",
       "29.86      1\n",
       "28.77      1\n",
       "Name: StnPressure, Length: 104, dtype: int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['StnPressure'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only 4 missing values.\n",
    "\n",
    "Drop the rows with missing data (by making new dataframe only containing the rows that were not M for StnPressure)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather = weather[weather['StnPressure'] != 'M']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    29.10\n",
       "1    29.18\n",
       "2    29.38\n",
       "3    29.44\n",
       "4    29.39\n",
       "Name: StnPressure, dtype: float64"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['StnPressure'] = weather['StnPressure'].astype(float)\n",
    "weather['StnPressure'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**SeaLevel** - OK<BR />\n",
    "Pressure, INCHES OF HG<BR />\n",
    "AVERAGE SEA LEVEL PRESSURE<BR />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['29.82', '30.09', '30.08', '30.12', '30.05', '30.10', '30.29',\n",
       "       '30.28', '30.03', '30.02', '29.94', '29.93', '29.92', '29.91',\n",
       "       '30.04', '30.20', '30.19', '30.24', '29.97', '29.98', '29.84',\n",
       "       '29.83', '30.27', '30.25', '30.26', '30.11', '30.06', '30.23',\n",
       "       '30.15', '30.14', '30.00', '29.99', '29.90', '29.77', '29.76',\n",
       "       '29.56', '29.54', '29.52', '29.51', '29.79', '29.78', '29.81',\n",
       "       '29.55', '29.85', '30.07', '30.16', '29.96', '29.95', '30.13',\n",
       "       '30.21', '30.22', '29.88', '30.01', '29.80', '29.89', '29.74',\n",
       "       '29.87', '29.86', '30.18', '30.17', '29.34', '29.44', '29.45',\n",
       "       '29.71', '29.72', '30.52', '30.53', '30.40', '30.41', '29.67',\n",
       "       '29.53', '29.69', '29.61', '29.64', '29.63', '29.66', '29.70',\n",
       "       '30.34', '30.33', '29.62', '29.60', '29.75', '29.68', '29.73',\n",
       "       '30.31', '30.30', '30.32', '30.37', '30.39', 'M', '29.59', '29.65',\n",
       "       '30.35', '30.36', '29.48', '30.38', '29.50', '29.25', '29.23',\n",
       "       '29.46', '29.43', '29.47'], dtype=object)"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['SeaLevel'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30.00    95\n",
       "29.98    85\n",
       "29.94    85\n",
       "29.89    82\n",
       "29.92    82\n",
       "30.05    81\n",
       "30.02    80\n",
       "29.95    80\n",
       "29.91    80\n",
       "29.93    79\n",
       "29.96    76\n",
       "29.99    73\n",
       "29.97    73\n",
       "30.03    72\n",
       "30.01    71\n",
       "30.04    70\n",
       "30.06    69\n",
       "29.87    62\n",
       "29.90    60\n",
       "29.86    58\n",
       "29.88    56\n",
       "29.85    56\n",
       "30.08    54\n",
       "30.10    53\n",
       "29.83    53\n",
       "30.11    52\n",
       "30.09    50\n",
       "30.07    50\n",
       "29.84    47\n",
       "29.82    44\n",
       "         ..\n",
       "30.32     7\n",
       "29.55     6\n",
       "29.62     6\n",
       "30.31     6\n",
       "30.37     4\n",
       "29.63     4\n",
       "30.36     4\n",
       "29.45     4\n",
       "29.53     4\n",
       "30.34     3\n",
       "29.54     3\n",
       "29.64     3\n",
       "29.56     3\n",
       "29.34     2\n",
       "29.48     2\n",
       "29.50     2\n",
       "29.52     2\n",
       "29.51     2\n",
       "30.40     2\n",
       "30.38     2\n",
       "29.44     1\n",
       "30.52     1\n",
       "29.43     1\n",
       "29.23     1\n",
       "29.47     1\n",
       "30.41     1\n",
       "29.46     1\n",
       "29.25     1\n",
       "30.53     1\n",
       "30.39     1\n",
       "Name: SeaLevel, Length: 102, dtype: int64"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['SeaLevel'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Btw 7 and 44 missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop the rows with missing data (by making new dataframe only containing the rows that were not M for SeaLevel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather = weather[weather['SeaLevel'] != 'M']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    29.82\n",
       "1    29.82\n",
       "2    30.09\n",
       "3    30.08\n",
       "4    30.12\n",
       "Name: SeaLevel, dtype: float64"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['SeaLevel'] = weather['SeaLevel'].astype(float)\n",
    "weather['SeaLevel'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**ResultSpeed** - OK<BR />\n",
    "RESULTANT WIND SPEED"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('float64')"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['ResultSpeed'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type is ok and do not need to be changed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.1,  0.2,  0.3,  0.4,  0.5,  0.6,  0.7,  0.8,  0.9,  1. ,  1.1,\n",
       "        1.2,  1.3,  1.4,  1.5,  1.6,  1.7,  1.8,  1.9,  2. ,  2.1,  2.2,\n",
       "        2.3,  2.4,  2.5,  2.6,  2.7,  2.8,  2.9,  3. ,  3.1,  3.2,  3.3,\n",
       "        3.4,  3.5,  3.6,  3.7,  3.8,  3.9,  4. ,  4.1,  4.2,  4.3,  4.4,\n",
       "        4.5,  4.6,  4.7,  4.8,  4.9,  5. ,  5.1,  5.2,  5.3,  5.4,  5.5,\n",
       "        5.6,  5.7,  5.8,  5.9,  6. ,  6.1,  6.2,  6.3,  6.4,  6.5,  6.6,\n",
       "        6.7,  6.8,  6.9,  7. ,  7.1,  7.2,  7.3,  7.4,  7.5,  7.6,  7.7,\n",
       "        7.8,  7.9,  8. ,  8.1,  8.2,  8.3,  8.4,  8.5,  8.6,  8.7,  8.8,\n",
       "        8.9,  9. ,  9.1,  9.2,  9.3,  9.4,  9.5,  9.6,  9.7,  9.8,  9.9,\n",
       "       10. , 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 11. ,\n",
       "       11.1, 11.2, 11.3, 11.4, 11.5, 11.6, 11.7, 11.8, 11.9, 12. , 12.1,\n",
       "       12.2, 12.3, 12.4, 12.5, 12.6, 12.7, 12.8, 12.9, 13. , 13.1, 13.2,\n",
       "       13.3, 13.4, 13.5, 13.6, 13.7, 13.8, 13.9, 14. , 14.1, 14.2, 14.3,\n",
       "       14.4, 14.5, 14.6, 14.7, 14.8, 14.9, 15. , 15.1, 15.2, 15.3, 15.4,\n",
       "       15.5, 15.6, 15.7, 15.8, 15.9, 16. , 16.1, 16.2, 16.6, 16.7, 16.8,\n",
       "       16.9, 17. , 17.1, 17.2, 17.4, 17.5, 17.7, 17.9, 18. , 18.8, 19.2,\n",
       "       19.3, 19.4, 19.6, 19.8, 20.3, 20.8, 21.1, 21.4, 21.7, 21.8, 22.6,\n",
       "       22.7, 24.1])"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['ResultSpeed'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**ResultDir** - OK<BR />\n",
    "RESULTANT DIRECTION - (WHOLE DEGREES)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['ResultDir'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type need to be changed into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11, 12, 13, 14, 15, 16, 17,\n",
       "       18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34,\n",
       "       35, 36])"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['ResultDir'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Turn into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    27.0\n",
       "1    25.0\n",
       "2     4.0\n",
       "3     2.0\n",
       "4     7.0\n",
       "Name: ResultDir, dtype: float64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['ResultDir'] = weather['ResultDir'].astype(float)\n",
    "weather['ResultDir'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**AvgSpeed** - OK<BR />\n",
    "I guess we are talking windspeed here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['AvgSpeed'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data type need to be changed into floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     9.2\n",
       "1     9.6\n",
       "2    13.4\n",
       "3    13.4\n",
       "4    11.9\n",
       "Name: AvgSpeed, dtype: float64"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather['AvgSpeed'] = weather['AvgSpeed'].astype(float)\n",
    "weather['AvgSpeed'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1.7,  1.9,  2. ,  2.1,  2.3,  2.4,  2.6,  2.7,  2.8,  2.9,  3. ,\n",
       "        3.1,  3.2,  3.3,  3.4,  3.5,  3.6,  3.7,  3.8,  3.9,  4. ,  4.1,\n",
       "        4.2,  4.3,  4.4,  4.5,  4.6,  4.7,  4.8,  4.9,  5. ,  5.1,  5.2,\n",
       "        5.3,  5.4,  5.5,  5.6,  5.7,  5.8,  5.9,  6. ,  6.1,  6.2,  6.3,\n",
       "        6.4,  6.5,  6.6,  6.7,  6.8,  6.9,  7. ,  7.1,  7.2,  7.3,  7.4,\n",
       "        7.5,  7.6,  7.7,  7.8,  7.9,  8. ,  8.1,  8.2,  8.3,  8.4,  8.5,\n",
       "        8.6,  8.7,  8.8,  8.9,  9. ,  9.1,  9.2,  9.3,  9.4,  9.5,  9.6,\n",
       "        9.7,  9.8,  9.9, 10. , 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7,\n",
       "       10.8, 10.9, 11. , 11.1, 11.2, 11.3, 11.4, 11.5, 11.6, 11.7, 11.8,\n",
       "       11.9, 12. , 12.1, 12.2, 12.3, 12.4, 12.5, 12.6, 12.7, 12.8, 12.9,\n",
       "       13. , 13.1, 13.2, 13.3, 13.4, 13.5, 13.6, 13.7, 13.8, 13.9, 14. ,\n",
       "       14.1, 14.2, 14.3, 14.4, 14.5, 14.6, 14.7, 14.8, 14.9, 15. , 15.1,\n",
       "       15.2, 15.3, 15.4, 15.5, 15.6, 15.7, 15.8, 15.9, 16. , 16.1, 16.2,\n",
       "       16.3, 16.4, 16.6, 16.7, 16.8, 16.9, 17.1, 17.2, 17.3, 17.4, 17.6,\n",
       "       17.7, 17.8, 17.9, 18. , 18.1, 18.2, 18.4, 18.5, 18.8, 18.9, 19.3,\n",
       "       19.5, 19.9, 20.2, 20.7, 21.4, 21.5, 22.1, 22.6, 22.9, 23.1, 23.2,\n",
       "       26.3])"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sort(weather['AvgSpeed'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No null, M or - (missing values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Turn all new columns from CodeSum into floats**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "new_colums = ['','BCFG','BR','DZ','FG','FG+','FU','GR','HZ','MIFG','RA','SN','SQ','TS','TSRA','VCFG','VCTS']\n",
    "\n",
    "for i in new_colums:\n",
    "    weather[i] = weather[i].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Station</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>SnowFall</th>\n",
       "      <th>...</th>\n",
       "      <th>GR</th>\n",
       "      <th>HZ</th>\n",
       "      <th>MIFG</th>\n",
       "      <th>RA</th>\n",
       "      <th>SN</th>\n",
       "      <th>SQ</th>\n",
       "      <th>TS</th>\n",
       "      <th>TSRA</th>\n",
       "      <th>VCFG</th>\n",
       "      <th>VCTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>1464.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>1464.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "      <td>2920.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.498630</td>\n",
       "      <td>76.131849</td>\n",
       "      <td>57.811644</td>\n",
       "      <td>67.223630</td>\n",
       "      <td>1.952186</td>\n",
       "      <td>53.448288</td>\n",
       "      <td>59.313014</td>\n",
       "      <td>3.417466</td>\n",
       "      <td>5.641096</td>\n",
       "      <td>0.000888</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000342</td>\n",
       "      <td>0.089726</td>\n",
       "      <td>0.000685</td>\n",
       "      <td>0.317123</td>\n",
       "      <td>0.002055</td>\n",
       "      <td>0.001027</td>\n",
       "      <td>0.034247</td>\n",
       "      <td>0.104110</td>\n",
       "      <td>0.001370</td>\n",
       "      <td>0.006507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.500084</td>\n",
       "      <td>11.477434</td>\n",
       "      <td>10.388355</td>\n",
       "      <td>10.555859</td>\n",
       "      <td>6.852047</td>\n",
       "      <td>10.684779</td>\n",
       "      <td>9.289451</td>\n",
       "      <td>5.961108</td>\n",
       "      <td>6.109144</td>\n",
       "      <td>0.009385</td>\n",
       "      <td>...</td>\n",
       "      <td>0.018506</td>\n",
       "      <td>0.285838</td>\n",
       "      <td>0.026167</td>\n",
       "      <td>0.465436</td>\n",
       "      <td>0.045291</td>\n",
       "      <td>0.032042</td>\n",
       "      <td>0.181893</td>\n",
       "      <td>0.305455</td>\n",
       "      <td>0.036993</td>\n",
       "      <td>0.080416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>-17.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>69.000000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>-3.000000</td>\n",
       "      <td>46.000000</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>78.000000</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>69.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>61.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>85.000000</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>62.000000</td>\n",
       "      <td>67.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>104.000000</td>\n",
       "      <td>83.000000</td>\n",
       "      <td>94.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>78.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Station         Tmax         Tmin         Tavg       Depart  \\\n",
       "count  2920.000000  2920.000000  2920.000000  2920.000000  1464.000000   \n",
       "mean      1.498630    76.131849    57.811644    67.223630     1.952186   \n",
       "std       0.500084    11.477434    10.388355    10.555859     6.852047   \n",
       "min       1.000000    41.000000    29.000000    36.000000   -17.000000   \n",
       "25%       1.000000    69.000000    50.000000    60.000000    -3.000000   \n",
       "50%       1.000000    78.000000    59.000000    69.000000     2.000000   \n",
       "75%       2.000000    85.000000    66.000000    75.000000     7.000000   \n",
       "max       2.000000   104.000000    83.000000    94.000000    23.000000   \n",
       "\n",
       "          DewPoint      WetBulb         Heat         Cool     SnowFall  \\\n",
       "count  2920.000000  2920.000000  2920.000000  2920.000000  1464.000000   \n",
       "mean     53.448288    59.313014     3.417466     5.641096     0.000888   \n",
       "std      10.684779     9.289451     5.961108     6.109144     0.009385   \n",
       "min      22.000000    32.000000     0.000000     0.000000     0.000000   \n",
       "25%      46.000000    53.000000     0.000000     0.000000     0.000000   \n",
       "50%      54.000000    61.000000     0.000000     4.000000     0.000000   \n",
       "75%      62.000000    67.000000     5.000000    10.000000     0.000000   \n",
       "max      75.000000    78.000000    29.000000    29.000000     0.100000   \n",
       "\n",
       "          ...                GR           HZ         MIFG           RA  \\\n",
       "count     ...       2920.000000  2920.000000  2920.000000  2920.000000   \n",
       "mean      ...          0.000342     0.089726     0.000685     0.317123   \n",
       "std       ...          0.018506     0.285838     0.026167     0.465436   \n",
       "min       ...          0.000000     0.000000     0.000000     0.000000   \n",
       "25%       ...          0.000000     0.000000     0.000000     0.000000   \n",
       "50%       ...          0.000000     0.000000     0.000000     0.000000   \n",
       "75%       ...          0.000000     0.000000     0.000000     1.000000   \n",
       "max       ...          1.000000     1.000000     1.000000     1.000000   \n",
       "\n",
       "                SN           SQ           TS         TSRA         VCFG  \\\n",
       "count  2920.000000  2920.000000  2920.000000  2920.000000  2920.000000   \n",
       "mean      0.002055     0.001027     0.034247     0.104110     0.001370   \n",
       "std       0.045291     0.032042     0.181893     0.305455     0.036993   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "50%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "75%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "max       1.000000     1.000000     1.000000     1.000000     1.000000   \n",
       "\n",
       "              VCTS  \n",
       "count  2920.000000  \n",
       "mean      0.006507  \n",
       "std       0.080416  \n",
       "min       0.000000  \n",
       "25%       0.000000  \n",
       "50%       0.000000  \n",
       "75%       0.000000  \n",
       "max       1.000000  \n",
       "\n",
       "[8 rows x 36 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rerun this cell when all the data types are correct and we know what columns we want to keep in the df.\n",
    "weather.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2920 entries, 0 to 2943\n",
      "Data columns (total 40 columns):\n",
      "Station        2920 non-null float64\n",
      "Date           2920 non-null datetime64[ns]\n",
      "Tmax           2920 non-null float64\n",
      "Tmin           2920 non-null float64\n",
      "Tavg           2920 non-null float64\n",
      "Depart         1464 non-null float64\n",
      "DewPoint       2920 non-null float64\n",
      "WetBulb        2920 non-null float64\n",
      "Heat           2920 non-null float64\n",
      "Cool           2920 non-null float64\n",
      "Sunrise        2920 non-null object\n",
      "Sunset         2920 non-null object\n",
      "CodeSum        2920 non-null object\n",
      "SnowFall       1464 non-null float64\n",
      "PrecipTotal    2918 non-null float64\n",
      "StnPressure    2920 non-null float64\n",
      "SeaLevel       2920 non-null float64\n",
      "ResultSpeed    2920 non-null float64\n",
      "ResultDir      2920 non-null float64\n",
      "AvgSpeed       2920 non-null float64\n",
      "Year           2920 non-null int64\n",
      "Month          2920 non-null int64\n",
      "Day            2920 non-null int64\n",
      "               2920 non-null float64\n",
      "BCFG           2920 non-null float64\n",
      "BR             2920 non-null float64\n",
      "DZ             2920 non-null float64\n",
      "FG             2920 non-null float64\n",
      "FG+            2920 non-null float64\n",
      "FU             2920 non-null float64\n",
      "GR             2920 non-null float64\n",
      "HZ             2920 non-null float64\n",
      "MIFG           2920 non-null float64\n",
      "RA             2920 non-null float64\n",
      "SN             2920 non-null float64\n",
      "SQ             2920 non-null float64\n",
      "TS             2920 non-null float64\n",
      "TSRA           2920 non-null float64\n",
      "VCFG           2920 non-null float64\n",
      "VCTS           2920 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(33), int64(3), object(3)\n",
      "memory usage: 935.3+ KB\n"
     ]
    }
   ],
   "source": [
    "weather.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "**Summary**\n",
    "\n",
    "- The columns Depth and Water1 were removed from dataframe\n",
    "- The Date column is datetime\n",
    "- The columns Sunset and Sunrise are still strings and contain -. We will handle if we are going to use.\n",
    "- We need to drop the CodeSum column later (kept it for now if something with the handmade dummies is  wrong), it's still strings\n",
    "- Year, month, day are still int, do we want them to be floats? (really strange to have them as floats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "**Load the data to CSV**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "weather.to_csv('weather_clean.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}