notebooks/handling_data_issues.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling duplicate, missing, or invalid data\n",
    "\n",
    "## About the data\n",
    "In this notebook, we will using daily weather data that was taken from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2) and altered to introduce many common problems faced when working with data. \n",
    "\n",
    "*Note: The NCEI is part of the National Oceanic and Atmospheric Administration (NOAA) and, as you can see from the URL for the API, this resource was created when the NCEI was called the NCDC. Should the URL for this resource change in the future, you can search for \"NCEI weather API\" to find the updated one.*\n",
    "\n",
    "## Background on the data\n",
    "\n",
    "Data meanings:\n",
    "- `PRCP`: precipitation in millimeters\n",
    "- `SNOW`: snowfall in millimeters\n",
    "- `SNWD`: snow depth in millimeters\n",
    "- `TMAX`: maximum daily temperature in Celsius\n",
    "- `TMIN`: minimum daily temperature in Celsius\n",
    "- `TOBS`: temperature at time of observation in Celsius\n",
    "- `WESF`: water equivalent of snow in millimeters\n",
    "\n",
    "Some important facts to get our bearings:\n",
    "- According to the National Weather Service, the coldest temperature ever recorded in Central Park was -15°F (-26.1°C) on February 9, 1934: [source](https://www.weather.gov/media/okx/Climate/CentralPark/extremes.pdf) \n",
    "- The temperature of the Sun's photosphere is approximately 5,505°C: [source](https://en.wikipedia.org/wiki/Sun)\n",
    "\n",
    "## Setup\n",
    "We need to import `pandas` and read in the dirty data to get started:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../data/dirty_data.csv')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding problematic data\n",
    "A good first step is to look at some rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date            station  PRCP  SNOW  SNWD    TMAX  TMIN  \\\n",
       "0  2018-01-01T00:00:00                  ?   0.0   0.0  -inf  5505.0 -40.0   \n",
       "1  2018-01-01T00:00:00                  ?   0.0   0.0  -inf  5505.0 -40.0   \n",
       "2  2018-01-01T00:00:00                  ?   0.0   0.0  -inf  5505.0 -40.0   \n",
       "3  2018-01-02T00:00:00  GHCND:USC00280907   0.0   0.0  -inf    -8.3 -16.1   \n",
       "4  2018-01-03T00:00:00  GHCND:USC00280907   0.0   0.0  -inf    -4.4 -13.9   \n",
       "\n",
       "   TOBS  WESF inclement_weather  \n",
       "0   NaN   NaN               NaN  \n",
       "1   NaN   NaN               NaN  \n",
       "2   NaN   NaN               NaN  \n",
       "3 -12.2   NaN             False  \n",
       "4 -13.3   NaN             False  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at summary statistics can reveal strange or missing values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\gpower\\AppData\\Local\\mambaforge\\envs\\cary_dev\\Lib\\site-packages\\numpy\\lib\\function_base.py:4573: RuntimeWarning: invalid value encountered in subtract\n",
      "  diff_b_a = subtract(b, a)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>765.000000</td>\n",
       "      <td>577.000000</td>\n",
       "      <td>577.0</td>\n",
       "      <td>765.000000</td>\n",
       "      <td>765.000000</td>\n",
       "      <td>398.000000</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.360392</td>\n",
       "      <td>4.202773</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2649.175294</td>\n",
       "      <td>-15.914379</td>\n",
       "      <td>8.632161</td>\n",
       "      <td>16.290909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>10.002138</td>\n",
       "      <td>25.086077</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2744.156281</td>\n",
       "      <td>24.242849</td>\n",
       "      <td>9.815054</td>\n",
       "      <td>9.489832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-11.700000</td>\n",
       "      <td>-40.000000</td>\n",
       "      <td>-16.100000</td>\n",
       "      <td>1.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.300000</td>\n",
       "      <td>-40.000000</td>\n",
       "      <td>0.150000</td>\n",
       "      <td>8.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.800000</td>\n",
       "      <td>-11.100000</td>\n",
       "      <td>8.300000</td>\n",
       "      <td>19.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>5.800000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5505.000000</td>\n",
       "      <td>6.700000</td>\n",
       "      <td>18.300000</td>\n",
       "      <td>24.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>61.700000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.000000</td>\n",
       "      <td>23.900000</td>\n",
       "      <td>26.100000</td>\n",
       "      <td>28.700000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             PRCP        SNOW   SNWD         TMAX        TMIN        TOBS  \\\n",
       "count  765.000000  577.000000  577.0   765.000000  765.000000  398.000000   \n",
       "mean     5.360392    4.202773    NaN  2649.175294  -15.914379    8.632161   \n",
       "std     10.002138   25.086077    NaN  2744.156281   24.242849    9.815054   \n",
       "min      0.000000    0.000000   -inf   -11.700000  -40.000000  -16.100000   \n",
       "25%      0.000000    0.000000    NaN    13.300000  -40.000000    0.150000   \n",
       "50%      0.000000    0.000000    NaN    32.800000  -11.100000    8.300000   \n",
       "75%      5.800000    0.000000    NaN  5505.000000    6.700000   18.300000   \n",
       "max     61.700000  229.000000    inf  5505.000000   23.900000   26.100000   \n",
       "\n",
       "            WESF  \n",
       "count  11.000000  \n",
       "mean   16.290909  \n",
       "std     9.489832  \n",
       "min     1.800000  \n",
       "25%     8.600000  \n",
       "50%    19.300000  \n",
       "75%    24.900000  \n",
       "max    28.700000  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `info()` method can pinpoint missing values and wrong data types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 765 entries, 0 to 764\n",
      "Data columns (total 10 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   date               765 non-null    object \n",
      " 1   station            765 non-null    object \n",
      " 2   PRCP               765 non-null    float64\n",
      " 3   SNOW               577 non-null    float64\n",
      " 4   SNWD               577 non-null    float64\n",
      " 5   TMAX               765 non-null    float64\n",
      " 6   TMIN               765 non-null    float64\n",
      " 7   TOBS               398 non-null    float64\n",
      " 8   WESF               11 non-null     float64\n",
      " 9   inclement_weather  408 non-null    object \n",
      "dtypes: float64(7), object(3)\n",
      "memory usage: 59.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `isna()`/`isnull()` method of the series to find nulls:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "765"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contain_nulls = df[\n",
    "    df.SNOW.isna() | df.SNWD.isna() | df.TOBS.isna()\n",
    "    | df.WESF.isna() | df.inclement_weather.isna()\n",
    "]\n",
    "contain_nulls.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-01-05T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date            station  PRCP   SNOW  SNWD    TMAX  TMIN  \\\n",
       "0  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "1  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "2  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "3  2018-01-02T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -8.3 -16.1   \n",
       "4  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "5  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "6  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "7  2018-01-04T00:00:00                  ?  20.6  229.0   inf  5505.0 -40.0   \n",
       "8  2018-01-04T00:00:00                  ?  20.6  229.0   inf  5505.0 -40.0   \n",
       "9  2018-01-05T00:00:00                  ?   0.3    NaN   NaN  5505.0 -40.0   \n",
       "\n",
       "   TOBS  WESF inclement_weather  \n",
       "0   NaN   NaN               NaN  \n",
       "1   NaN   NaN               NaN  \n",
       "2   NaN   NaN               NaN  \n",
       "3 -12.2   NaN             False  \n",
       "4 -13.3   NaN             False  \n",
       "5 -13.3   NaN             False  \n",
       "6 -13.3   NaN             False  \n",
       "7   NaN  19.3              True  \n",
       "8   NaN  19.3              True  \n",
       "9   NaN   NaN               NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contain_nulls.head(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can't check if we have `NaN` like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.inclement_weather == 'NaN'].shape[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is because it is actually `np.nan`. However, notice this also doesn't work:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "df[df.inclement_weather == np.nan].shape[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have to use one of the methods discussed earlier for this to work:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "357"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.inclement_weather.isna()].shape[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can find `-inf`/`inf` by comparing to `-np.inf`/`np.inf`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "577"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.SNWD.isin([-np.inf, np.inf])].shape[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rather than do this for each column, we can write a function that will use a [dictionary comprehension](https://www.python.org/dev/peps/pep-0274/) to check all the columns for us:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'date': 0,\n",
       " 'station': 0,\n",
       " 'PRCP': 0,\n",
       " 'SNOW': 0,\n",
       " 'SNWD': 577,\n",
       " 'TMAX': 0,\n",
       " 'TMIN': 0,\n",
       " 'TOBS': 0,\n",
       " 'WESF': 0,\n",
       " 'inclement_weather': 0}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_inf_count(df):\n",
    "    \"\"\"Find the number of inf/-inf values per column in the dataframe\"\"\"\n",
    "    return {\n",
    "        col: df[df[col].isin([np.inf, -np.inf])].shape[0] for col in df.columns\n",
    "    }\n",
    "\n",
    "get_inf_count(df)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can decide how to handle the infinite values of snow depth, we should look at the summary statistics for snowfall, which forms a big part in determining the snow depth:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>np.inf Snow Depth</th>\n",
       "      <td>24.0</td>\n",
       "      <td>101.041667</td>\n",
       "      <td>74.498018</td>\n",
       "      <td>13.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>120.5</td>\n",
       "      <td>152.0</td>\n",
       "      <td>229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-np.inf Snow Depth</th>\n",
       "      <td>553.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    count        mean        std   min   25%    50%    75%  \\\n",
       "np.inf Snow Depth    24.0  101.041667  74.498018  13.0  25.0  120.5  152.0   \n",
       "-np.inf Snow Depth  553.0    0.000000   0.000000   0.0   0.0    0.0    0.0   \n",
       "\n",
       "                      max  \n",
       "np.inf Snow Depth   229.0  \n",
       "-np.inf Snow Depth    0.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame({\n",
    "    'np.inf Snow Depth': df[df.SNWD == np.inf].SNOW.describe(),\n",
    "    '-np.inf Snow Depth': df[df.SNWD == -np.inf].SNOW.describe()\n",
    "}).T"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now look into the `date` and `station` columns. We saw the `?` for station earlier, so we know that was the other unique value. However, we see that some dates are present 8 times in the data and we only have 324 days meaning we are also missing days:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>765</td>\n",
       "      <td>765</td>\n",
       "      <td>408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>324</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>2018-07-05T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>8</td>\n",
       "      <td>398</td>\n",
       "      <td>384</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       date            station inclement_weather\n",
       "count                   765                765               408\n",
       "unique                  324                  2                 2\n",
       "top     2018-07-05T00:00:00  GHCND:USC00280907             False\n",
       "freq                      8                398               384"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include='object')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `duplicated()` method to find duplicate rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "284"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated()].shape[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default for `keep` is `'first'` meaning it won't show the first row that the duplicated data was seen in; we can pass in `False` to see it though:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "482"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated(keep=False)].shape[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also specify the columns to use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "284"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated(['date', 'station'])].shape[0]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at a few duplicates. Just in the few values we see here, we know that the top 4 are actually in the data 6 times because by default we aren't seeing their first occurrence:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date            station  PRCP   SNOW  SNWD    TMAX  TMIN  \\\n",
       "1  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "2  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "5  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "6  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "8  2018-01-04T00:00:00                  ?  20.6  229.0   inf  5505.0 -40.0   \n",
       "\n",
       "   TOBS  WESF inclement_weather  \n",
       "1   NaN   NaN               NaN  \n",
       "2   NaN   NaN               NaN  \n",
       "5 -13.3   NaN             False  \n",
       "6 -13.3   NaN             False  \n",
       "8   NaN  19.3              True  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated()].head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mitigating Issues\n",
    "\n",
    "### Handling duplicated data\n",
    "Since we know we have NY weather data and noticed we only had two entries for `station`, we may decide to drop the `station` column because we are only interested in the weather data. However, when dealing with duplicate data, we need to think of the ramifications of removing it. Notice we only have data for the `WESF` column when the station is `?`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['?'], dtype=object)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.WESF.notna()].station.unique()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we determine it won't impact our analysis, we can use `drop_duplicates()` to remove them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(324, 8)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. make the date a datetime\n",
    "df.date = pd.to_datetime(df.date)\n",
    "\n",
    "# 2. save this information for later\n",
    "station_qm_wesf = df[df.station == '?'].drop_duplicates('date').set_index('date').WESF\n",
    "\n",
    "# 3. sort ? to the bottom\n",
    "df.sort_values('station', ascending=False, inplace=True)\n",
    "\n",
    "# 4. drop duplicates based on the date column keeping the first occurrence \n",
    "# which will be the valid station if it has data\n",
    "df_deduped = df.drop_duplicates('date')\n",
    "\n",
    "# 5. remove the station column because we are done with it\n",
    "df_deduped = df_deduped.drop(columns='station').set_index('date').sort_index()\n",
    "\n",
    "# 6. take valid station's WESF and fall back on station ? if it is null\n",
    "df_deduped = df_deduped.assign(\n",
    "    WESF=lambda x: x.WESF.combine_first(station_qm_wesf)\n",
    ")\n",
    "\n",
    "df_deduped.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we used the `combine_first()` method to coalesce the values to the first non-null entry; this means that if we had data from both stations, we would first take the value provided by the named station and if (and only if) that station was null would we take the value from the station named `?`. The following table contains some examples of how this would play out:\n",
    "\n",
    "| station GHCND:USC00280907 | station ? | result of `combine_first()` |\n",
    "| :---: | :---: | :---: |\n",
    "| 1 | 17 | 1 |\n",
    "| 1 | `NaN` | 1 |\n",
    "| `NaN` | 17 | 17 |\n",
    "| `NaN` | `NaN` | `NaN` |\n",
    "\n",
    "Check out the 4th row&mdash;we have `WESF` in the correct spot thanks to the index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW  SNWD    TMAX  TMIN  TOBS  WESF inclement_weather\n",
       "date                                                                     \n",
       "2018-01-01   0.0    0.0  -inf  5505.0 -40.0   NaN   NaN               NaN\n",
       "2018-01-02   0.0    0.0  -inf    -8.3 -16.1 -12.2   NaN             False\n",
       "2018-01-03   0.0    0.0  -inf    -4.4 -13.9 -13.3   NaN             False\n",
       "2018-01-04  20.6  229.0   inf  5505.0 -40.0   NaN  19.3              True\n",
       "2018-01-05  14.2  127.0   inf    -4.4 -13.9 -13.9   NaN              True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dealing with nulls\n",
    "We could drop nulls, replace them with some arbitrary value, or impute them using the surrounding data. Each of these options may have ramifications, so we must choose wisely.\n",
    "\n",
    "We can use `dropna()` to drop rows where any column has a null value. The default options leave us hardly any data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4, 8)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna().shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we pass `how='all'`, we can choose to only drop rows where everything is null, but this removes nothing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(324, 8)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna(how='all').shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use just a subset of columns to determine what to drop with the `subset` argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(293, 8)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna(\n",
    "    how='all', subset=['inclement_weather', 'SNOW', 'SNWD']\n",
    ").shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This can also be performed along columns, and we can also require a certain number of null values before we drop the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PRCP', 'SNOW', 'SNWD', 'TMAX', 'TMIN', 'TOBS', 'inclement_weather'], dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna(axis='columns', thresh=df_deduped.shape[0] * .75).columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can choose to fill in the null values instead with `fillna()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW  SNWD    TMAX  TMIN  TOBS  WESF inclement_weather\n",
       "date                                                                     \n",
       "2018-01-01   0.0    0.0  -inf  5505.0 -40.0   NaN   0.0               NaN\n",
       "2018-01-02   0.0    0.0  -inf    -8.3 -16.1 -12.2   0.0             False\n",
       "2018-01-03   0.0    0.0  -inf    -4.4 -13.9 -13.3   0.0             False\n",
       "2018-01-04  20.6  229.0   inf  5505.0 -40.0   NaN  19.3              True\n",
       "2018-01-05  14.2  127.0   inf    -4.4 -13.9 -13.9   0.0              True"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.loc[:,'WESF'].fillna(0, inplace=True)\n",
    "df_deduped.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point we have done everything we can without distorting the data. We know that we are missing dates, but if we reindex, we don't know how to fill in the `NaN` data. With the weather data, we can't assume because it snowed one day that it will snow the next or that the temperature will be the same. For this reason, note that the next few examples are just for illustrative purposes only—just because we can do something doesn't mean we should.\n",
    "\n",
    "That being said, let's try to address some of remaining issues with the temperature data. We know that when `TMAX` is the temperature of the Sun, it must be because there was no measured value, so let's replace it with `NaN`. We will also do so for `TMIN` which currently uses -40°C for its placeholder when we know that the coldest temperature ever recorded in NYC was -15°F (-26.1°C) on February 9, 1934:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_deduped = df_deduped.assign(\n",
    "    TMAX=lambda x: x.TMAX.replace(5505, np.nan),\n",
    "    TMIN=lambda x: x.TMIN.replace(-40, np.nan),\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will also make an assumption that the temperature won't change drastically day-to-day. Note that this is actually a big assumption, but it will allow us to understand how `fillna()` works when we provide a strategy through the `method` parameter. The `fillna()` method gives us 2 options for the `method` parameter:\n",
    "- `'ffill'` to forward-fill\n",
    "- `'bfill'` to back-fill\n",
    "\n",
    "*Note that `'nearest'` is missing because we are not reindexing.*\n",
    "\n",
    "Here, we will use `'ffill'` to show how this works:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW  SNWD  TMAX  TMIN  TOBS  WESF inclement_weather\n",
       "date                                                                   \n",
       "2018-01-01   0.0    0.0  -inf   NaN   NaN   NaN   0.0               NaN\n",
       "2018-01-02   0.0    0.0  -inf  -8.3 -16.1 -12.2   0.0             False\n",
       "2018-01-03   0.0    0.0  -inf  -4.4 -13.9 -13.3   0.0             False\n",
       "2018-01-04  20.6  229.0   inf  -4.4 -13.9   NaN  19.3              True\n",
       "2018-01-05  14.2  127.0   inf  -4.4 -13.9 -13.9   0.0              True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    TMAX=lambda x: x.TMAX.fillna(method='ffill'),\n",
    "    TMIN=lambda x: x.TMIN.fillna(method='ffill')\n",
    ").head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use `np.nan_to_num()` to turn `np.nan` into 0 and `-np.inf`/`np.inf` into large negative or positive finite numbers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.797693e+308</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.797693e+308</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.797693e+308</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>1.797693e+308</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>1.797693e+308</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW           SNWD  TMAX  TMIN  TOBS  WESF  \\\n",
       "date                                                             \n",
       "2018-01-01   0.0    0.0 -1.797693e+308   NaN   NaN   NaN   0.0   \n",
       "2018-01-02   0.0    0.0 -1.797693e+308  -8.3 -16.1 -12.2   0.0   \n",
       "2018-01-03   0.0    0.0 -1.797693e+308  -4.4 -13.9 -13.3   0.0   \n",
       "2018-01-04  20.6  229.0  1.797693e+308   NaN   NaN   NaN  19.3   \n",
       "2018-01-05  14.2  127.0  1.797693e+308  -4.4 -13.9 -13.9   0.0   \n",
       "\n",
       "           inclement_weather  \n",
       "date                          \n",
       "2018-01-01               NaN  \n",
       "2018-01-02             False  \n",
       "2018-01-03             False  \n",
       "2018-01-04              True  \n",
       "2018-01-05              True  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    SNWD=lambda x: np.nan_to_num(x.SNWD)\n",
    ").head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Depending on the data we are working with, we can use the `clip()` method as an alternative to `np.nan_to_num()`. The `clip()` method makes it possible to cap values at a specific minimum and/or maximum threshold. Since `SNWD` can't be negative, let's use `clip()` to enforce a lower bound of zero. To show how the upper bound works, let's use the value of `SNOW`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW   SNWD  TMAX  TMIN  TOBS  WESF inclement_weather\n",
       "date                                                                    \n",
       "2018-01-01   0.0    0.0    0.0   NaN   NaN   NaN   0.0               NaN\n",
       "2018-01-02   0.0    0.0    0.0  -8.3 -16.1 -12.2   0.0             False\n",
       "2018-01-03   0.0    0.0    0.0  -4.4 -13.9 -13.3   0.0             False\n",
       "2018-01-04  20.6  229.0  229.0   NaN   NaN   NaN  19.3              True\n",
       "2018-01-05  14.2  127.0  127.0  -4.4 -13.9 -13.9   0.0              True"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    SNWD=lambda x: x.SNWD.clip(0, x.SNOW)\n",
    ").head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can couple `fillna()` with other types of calculations. Here we replace missing values of `TMAX` with the median of all `TMAX` values, `TMIN` with the median of all `TMIN` values, and `TOBS` to the average of the `TMAX` and `TMIN` values. Since we place `TOBS` last, we have access to the imputed values for `TMIN` and `TMAX` in the calculation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>14.4</td>\n",
       "      <td>5.6</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>14.4</td>\n",
       "      <td>5.6</td>\n",
       "      <td>10.0</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW  SNWD  TMAX  TMIN  TOBS  WESF inclement_weather\n",
       "date                                                                   \n",
       "2018-01-01   0.0    0.0  -inf  14.4   5.6  10.0   0.0               NaN\n",
       "2018-01-02   0.0    0.0  -inf  -8.3 -16.1 -12.2   0.0             False\n",
       "2018-01-03   0.0    0.0  -inf  -4.4 -13.9 -13.3   0.0             False\n",
       "2018-01-04  20.6  229.0   inf  14.4   5.6  10.0  19.3              True\n",
       "2018-01-05  14.2  127.0   inf  -4.4 -13.9 -13.9   0.0              True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    TMAX=lambda x: x.TMAX.fillna(x.TMAX.median()),\n",
    "    TMIN=lambda x: x.TMIN.fillna(x.TMIN.median()),\n",
    "    # average of TMAX and TMIN\n",
    "    TOBS=lambda x: x.TOBS.fillna((x.TMAX + x.TMIN) / 2)\n",
    ").head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use `apply()` for running the same calculation across columns. For example, let's fill all missing values with their rolling 7-day median of their values, setting the number of periods required for the calculation to 0 to ensure we don't introduce more extra `NaN` values. Rolling calculations will be covered later on, so this is a preview:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.30</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.40</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-6.35</td>\n",
       "      <td>-15.0</td>\n",
       "      <td>-12.75</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.40</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-06</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-10.00</td>\n",
       "      <td>-15.6</td>\n",
       "      <td>-15.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-07</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-11.70</td>\n",
       "      <td>-17.2</td>\n",
       "      <td>-16.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>-16.7</td>\n",
       "      <td>-8.30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-10</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5.00</td>\n",
       "      <td>-7.8</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-11</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>4.40</td>\n",
       "      <td>-7.8</td>\n",
       "      <td>1.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW  SNWD   TMAX  TMIN   TOBS  WESF inclement_weather\n",
       "date                                                                     \n",
       "2018-01-01   0.0    0.0  -inf    NaN   NaN    NaN   0.0               NaN\n",
       "2018-01-02   0.0    0.0  -inf  -8.30 -16.1 -12.20   0.0             False\n",
       "2018-01-03   0.0    0.0  -inf  -4.40 -13.9 -13.30   0.0             False\n",
       "2018-01-04  20.6  229.0   inf  -6.35 -15.0 -12.75  19.3              True\n",
       "2018-01-05  14.2  127.0   inf  -4.40 -13.9 -13.90   0.0              True\n",
       "2018-01-06   0.0    0.0  -inf -10.00 -15.6 -15.00   0.0             False\n",
       "2018-01-07   0.0    0.0  -inf -11.70 -17.2 -16.10   0.0             False\n",
       "2018-01-08   0.0    0.0  -inf  -7.80 -16.7  -8.30   0.0             False\n",
       "2018-01-10   0.0    0.0  -inf   5.00  -7.8  -7.80   0.0             False\n",
       "2018-01-11   0.0    0.0  -inf   4.40  -7.8   1.10   0.0             False"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.apply(\n",
    "    # rolling calculations will be covered later on, this is a rolling 7-day median\n",
    "    # we set min_periods (# of periods required for calculation) to 0 so we always get a result \n",
    "    lambda x: x.fillna(x.rolling(7, min_periods=0).median())\n",
    ").head(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last strategy we could try is interpolation with the `interpolate()` method. We specify the `method` parameter with the interpolation strategy to use. There are many options, but we will stick with the default of `'linear'`, which will treat values as evenly spaced and place missing values in the middle of existing ones. We have some missing data, so we will reindex first. Look at January 9th, which we didn't have before—the values for `TMAX`, `TMIN`, and `TOBS` are the average of values the day prior (January 8th) and the day after (January 10th):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.10</td>\n",
       "      <td>-12.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>-13.30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>-13.60</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-06</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-15.60</td>\n",
       "      <td>-15.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-07</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-11.7</td>\n",
       "      <td>-17.20</td>\n",
       "      <td>-16.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-7.8</td>\n",
       "      <td>-16.70</td>\n",
       "      <td>-8.30</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-09</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-1.4</td>\n",
       "      <td>-12.25</td>\n",
       "      <td>-8.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-10</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5.0</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW  SNWD  TMAX   TMIN   TOBS  WESF inclement_weather\n",
       "2018-01-01   0.0    0.0  -inf   NaN    NaN    NaN   0.0               NaN\n",
       "2018-01-02   0.0    0.0  -inf  -8.3 -16.10 -12.20   0.0             False\n",
       "2018-01-03   0.0    0.0  -inf  -4.4 -13.90 -13.30   0.0             False\n",
       "2018-01-04  20.6  229.0   inf  -4.4 -13.90 -13.60  19.3              True\n",
       "2018-01-05  14.2  127.0   inf  -4.4 -13.90 -13.90   0.0              True\n",
       "2018-01-06   0.0    0.0  -inf -10.0 -15.60 -15.00   0.0             False\n",
       "2018-01-07   0.0    0.0  -inf -11.7 -17.20 -16.10   0.0             False\n",
       "2018-01-08   0.0    0.0  -inf  -7.8 -16.70  -8.30   0.0             False\n",
       "2018-01-09   0.0    0.0  -inf  -1.4 -12.25  -8.05   0.0               NaN\n",
       "2018-01-10   0.0    0.0  -inf   5.0  -7.80  -7.80   0.0             False"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped\\\n",
    "    .reindex(pd.date_range('2018-01-01', '2018-12-31', freq='D'))\\\n",
    "    .apply(lambda x: x.interpolate())\\\n",
    "    .head(10)"
   ]
  }
 ],
 "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}