tutorial_DateTime_Wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Wrangling with datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has datetime objects with many handy attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020\n",
      "10\n",
      "21\n",
      "17\n",
      "23\n",
      "51\n"
     ]
    }
   ],
   "source": [
    "t = pd.Timestamp.now() \n",
    "print(t.year)\n",
    "print(t.month) \n",
    "print(t.day)\n",
    "print(t.hour)\n",
    "print(t.minute)\n",
    "print(t.second)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For demonstration purpose we load a file with datetime information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Recall Date</th>\n",
       "      <th>Recall Number</th>\n",
       "      <th>Recall Class</th>\n",
       "      <th>Product</th>\n",
       "      <th>Reason for Recall</th>\n",
       "      <th>Pounds Recalled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jan 10 2014</td>\n",
       "      <td>001-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Mechanically Separated Chicken Products</td>\n",
       "      <td>Salmonella</td>\n",
       "      <td>33,840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jan 13 2014</td>\n",
       "      <td>002-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Various Beef Products</td>\n",
       "      <td>Other</td>\n",
       "      <td>42,103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jan 15 2014</td>\n",
       "      <td>003-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef Franks</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>2,664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jan 17 2014</td>\n",
       "      <td>004-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Beef and Pork Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>130,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jan 17 2014</td>\n",
       "      <td>005-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Spiral Hams</td>\n",
       "      <td>Listeria monocytogenes</td>\n",
       "      <td>67,113</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recall Date Recall Number Recall Class  \\\n",
       "0  Jan 10 2014      001-2014            I   \n",
       "1  Jan 13 2014      002-2014            I   \n",
       "2  Jan 15 2014      003-2014            I   \n",
       "3  Jan 17 2014      004-2014           II   \n",
       "4  Jan 17 2014      005-2014            I   \n",
       "\n",
       "                                   Product       Reason for Recall  \\\n",
       "0  Mechanically Separated Chicken Products              Salmonella   \n",
       "1                    Various Beef Products                   Other   \n",
       "2                              Beef Franks     Undeclared Allergen   \n",
       "3                   Beef and Pork Products     Undeclared Allergen   \n",
       "4                              Spiral Hams  Listeria monocytogenes   \n",
       "\n",
       "  Pounds Recalled  \n",
       "0          33,840  \n",
       "1          42,103  \n",
       "2           2,664  \n",
       "3         130,000  \n",
       "4          67,113  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = 'FSIS-Recall-Summary-2014'\n",
    "df = pd.read_csv('../data/{}.csv'.format(file), skiprows=1)\n",
    "df.dropna()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `Recall Date` column looks like a Date Time format but it is not. We can simply transform it to a datetime format with `.to_datetime` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Recall Date          object\n",
      "Recall Number        object\n",
      "Recall Class         object\n",
      "Product              object\n",
      "Reason for Recall    object\n",
      "Pounds Recalled      object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df['Recall Date'] = pd.to_datetime(df['Recall Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Recall Date          datetime64[ns]\n",
      "Recall Number                object\n",
      "Recall Class                 object\n",
      "Product                      object\n",
      "Reason for Recall            object\n",
      "Pounds Recalled              object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we would like to calculate with Pounds Recalled down the road we transfer this to a float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#clean Pounds Recalled\n",
    "df['Pounds Recalled'] = df['Pounds Recalled'].astype(str).str.replace(r\",\", \"\", regex=True).astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Recall Date</th>\n",
       "      <th>Recall Number</th>\n",
       "      <th>Recall Class</th>\n",
       "      <th>Product</th>\n",
       "      <th>Reason for Recall</th>\n",
       "      <th>Pounds Recalled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>2014-12-27</td>\n",
       "      <td>091-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Chicken and Beef Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>55813.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>2014-12-29</td>\n",
       "      <td>092-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef and Pork Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>38400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>2014-12-31</td>\n",
       "      <td>093-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Chicken Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>5300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>2014-12-31</td>\n",
       "      <td>094-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Canned Soup Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>4474.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recall Date Recall Number Recall Class                    Product  \\\n",
       "90  2014-12-27      091-2014            I  Chicken and Beef Products   \n",
       "91  2014-12-29      092-2014            I     Beef and Pork Products   \n",
       "92  2014-12-31      093-2014            I           Chicken Products   \n",
       "93  2014-12-31      094-2014            I       Canned Soup Products   \n",
       "94         NaT           NaN          NaN                        NaN   \n",
       "\n",
       "      Reason for Recall  Pounds Recalled  \n",
       "90  Undeclared Allergen          55813.0  \n",
       "91  Undeclared Allergen          38400.0  \n",
       "92  Undeclared Allergen           5300.0  \n",
       "93  Undeclared Allergen           4474.0  \n",
       "94                  NaN              NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us remove the last record since it contains NaN information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[:-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can add the day of year by using the attribure `dayofyear`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Recall Date</th>\n",
       "      <th>Recall Number</th>\n",
       "      <th>Recall Class</th>\n",
       "      <th>Product</th>\n",
       "      <th>Reason for Recall</th>\n",
       "      <th>Pounds Recalled</th>\n",
       "      <th>dayofyear</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-01-10</td>\n",
       "      <td>001-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Mechanically Separated Chicken Products</td>\n",
       "      <td>Salmonella</td>\n",
       "      <td>33840.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-01-13</td>\n",
       "      <td>002-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Various Beef Products</td>\n",
       "      <td>Other</td>\n",
       "      <td>42103.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014-01-15</td>\n",
       "      <td>003-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef Franks</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>2664.0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-01-17</td>\n",
       "      <td>004-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Beef and Pork Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>130000.0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-01-17</td>\n",
       "      <td>005-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Spiral Hams</td>\n",
       "      <td>Listeria monocytogenes</td>\n",
       "      <td>67113.0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Recall Date Recall Number Recall Class  \\\n",
       "0  2014-01-10      001-2014            I   \n",
       "1  2014-01-13      002-2014            I   \n",
       "2  2014-01-15      003-2014            I   \n",
       "3  2014-01-17      004-2014           II   \n",
       "4  2014-01-17      005-2014            I   \n",
       "\n",
       "                                   Product       Reason for Recall  \\\n",
       "0  Mechanically Separated Chicken Products              Salmonella   \n",
       "1                    Various Beef Products                   Other   \n",
       "2                              Beef Franks     Undeclared Allergen   \n",
       "3                   Beef and Pork Products     Undeclared Allergen   \n",
       "4                              Spiral Hams  Listeria monocytogenes   \n",
       "\n",
       "   Pounds Recalled  dayofyear  \n",
       "0          33840.0         10  \n",
       "1          42103.0         13  \n",
       "2           2664.0         15  \n",
       "3         130000.0         17  \n",
       "4          67113.0         17  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['dayofyear'] = df['Recall Date'].dt.dayofyear\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we change the index to the datetime column we can use the `index.month`, `index.day`, `index.year` attributes derived from the datetime object. In the example below we first set the index to ddRecal and than select the month February"
   ]
  },
  {
   "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>Recall Number</th>\n",
       "      <th>Recall Class</th>\n",
       "      <th>Product</th>\n",
       "      <th>Reason for Recall</th>\n",
       "      <th>Pounds Recalled</th>\n",
       "      <th>dayofyear</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddRecal</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>2014-01-10</th>\n",
       "      <td>001-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Mechanically Separated Chicken Products</td>\n",
       "      <td>Salmonella</td>\n",
       "      <td>33840.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-13</th>\n",
       "      <td>002-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Various Beef Products</td>\n",
       "      <td>Other</td>\n",
       "      <td>42103.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-15</th>\n",
       "      <td>003-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef Franks</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>2664.0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-17</th>\n",
       "      <td>004-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Beef and Pork Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>130000.0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-17</th>\n",
       "      <td>005-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Spiral Hams</td>\n",
       "      <td>Listeria monocytogenes</td>\n",
       "      <td>67113.0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Recall Number Recall Class  \\\n",
       "ddRecal                                 \n",
       "2014-01-10      001-2014            I   \n",
       "2014-01-13      002-2014            I   \n",
       "2014-01-15      003-2014            I   \n",
       "2014-01-17      004-2014           II   \n",
       "2014-01-17      005-2014            I   \n",
       "\n",
       "                                            Product       Reason for Recall  \\\n",
       "ddRecal                                                                       \n",
       "2014-01-10  Mechanically Separated Chicken Products              Salmonella   \n",
       "2014-01-13                    Various Beef Products                   Other   \n",
       "2014-01-15                              Beef Franks     Undeclared Allergen   \n",
       "2014-01-17                   Beef and Pork Products     Undeclared Allergen   \n",
       "2014-01-17                              Spiral Hams  Listeria monocytogenes   \n",
       "\n",
       "            Pounds Recalled  dayofyear  \n",
       "ddRecal                                 \n",
       "2014-01-10          33840.0         10  \n",
       "2014-01-13          42103.0         13  \n",
       "2014-01-15           2664.0         15  \n",
       "2014-01-17         130000.0         17  \n",
       "2014-01-17          67113.0         17  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Set data as index\n",
    "df = df.rename(columns = {'Recall Date': 'ddRecal'})\n",
    "df.index = df['ddRecal']\n",
    "df = df.drop(['ddRecal'], axis = 1)\n",
    "df.head()"
   ]
  },
  {
   "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>Recall Number</th>\n",
       "      <th>Recall Class</th>\n",
       "      <th>Product</th>\n",
       "      <th>Reason for Recall</th>\n",
       "      <th>Pounds Recalled</th>\n",
       "      <th>dayofyear</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddRecal</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>2014-02-01</th>\n",
       "      <td>009-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Various Meat and Poultry Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>144000.0</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-04</th>\n",
       "      <td>010-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef Products</td>\n",
       "      <td>E. coli O103, O111, O121, O145, O26, O45</td>\n",
       "      <td>15865.0</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-04</th>\n",
       "      <td>011-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Chicken Noodle Soup</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>54673.0</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-08</th>\n",
       "      <td>012-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef Jerky</td>\n",
       "      <td>Processing Defect</td>\n",
       "      <td>365.0</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-08</th>\n",
       "      <td>013-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Various Beef Products</td>\n",
       "      <td>Other</td>\n",
       "      <td>8742700.0</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-11</th>\n",
       "      <td>014-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Beef Jerky Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-15</th>\n",
       "      <td>015-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Dried Egg Products</td>\n",
       "      <td>Salmonella</td>\n",
       "      <td>309594.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-22</th>\n",
       "      <td>016-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Various Meat Products</td>\n",
       "      <td>Other</td>\n",
       "      <td>8895.0</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-26</th>\n",
       "      <td>017-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Liver Pâté</td>\n",
       "      <td>Other</td>\n",
       "      <td>1282.0</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Recall Number Recall Class                            Product  \\\n",
       "ddRecal                                                                    \n",
       "2014-02-01      009-2014           II  Various Meat and Poultry Products   \n",
       "2014-02-04      010-2014            I                      Beef Products   \n",
       "2014-02-04      011-2014            I                Chicken Noodle Soup   \n",
       "2014-02-08      012-2014            I                         Beef Jerky   \n",
       "2014-02-08      013-2014            I              Various Beef Products   \n",
       "2014-02-11      014-2014           II                Beef Jerky Products   \n",
       "2014-02-15      015-2014            I                 Dried Egg Products   \n",
       "2014-02-22      016-2014           II              Various Meat Products   \n",
       "2014-02-26      017-2014            I                         Liver Pâté   \n",
       "\n",
       "                                   Reason for Recall  Pounds Recalled  \\\n",
       "ddRecal                                                                 \n",
       "2014-02-01                       Undeclared Allergen         144000.0   \n",
       "2014-02-04  E. coli O103, O111, O121, O145, O26, O45          15865.0   \n",
       "2014-02-04                       Undeclared Allergen          54673.0   \n",
       "2014-02-08                         Processing Defect            365.0   \n",
       "2014-02-08                                     Other        8742700.0   \n",
       "2014-02-11                       Undeclared Allergen          90000.0   \n",
       "2014-02-15                                Salmonella         309594.0   \n",
       "2014-02-22                                     Other           8895.0   \n",
       "2014-02-26                                     Other           1282.0   \n",
       "\n",
       "            dayofyear  \n",
       "ddRecal                \n",
       "2014-02-01         32  \n",
       "2014-02-04         35  \n",
       "2014-02-04         35  \n",
       "2014-02-08         39  \n",
       "2014-02-08         39  \n",
       "2014-02-11         42  \n",
       "2014-02-15         46  \n",
       "2014-02-22         53  \n",
       "2014-02-26         57  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#select month 2\n",
    "df_feb = df[(df.index.month == 2)] \n",
    "df_feb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Recall Number</th>\n",
       "      <th>Recall Class</th>\n",
       "      <th>Product</th>\n",
       "      <th>Reason for Recall</th>\n",
       "      <th>Pounds Recalled</th>\n",
       "      <th>dayofyear</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddRecal</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>2014-02-01</th>\n",
       "      <td>009-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Various Meat and Poultry Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>144000.0</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-04-01</th>\n",
       "      <td>019-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Chicken Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>8730.0</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Recall Number Recall Class                            Product  \\\n",
       "ddRecal                                                                    \n",
       "2014-02-01      009-2014           II  Various Meat and Poultry Products   \n",
       "2014-04-01      019-2014            I                   Chicken Products   \n",
       "\n",
       "              Reason for Recall  Pounds Recalled  dayofyear  \n",
       "ddRecal                                                      \n",
       "2014-02-01  Undeclared Allergen         144000.0         32  \n",
       "2014-04-01  Undeclared Allergen           8730.0         91  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#select first of all months\n",
    "df_01 = df[(df.index.day == 1)] \n",
    "df_01"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The month attribute is also handy when we want to find the month minimum value of a column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Recall Number</th>\n",
       "      <th>Recall Class</th>\n",
       "      <th>Product</th>\n",
       "      <th>Reason for Recall</th>\n",
       "      <th>Pounds Recalled</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>min.recall</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddRecal</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>2014-01-10</th>\n",
       "      <td>001-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Mechanically Separated Chicken Products</td>\n",
       "      <td>Salmonella</td>\n",
       "      <td>33840.0</td>\n",
       "      <td>10</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-13</th>\n",
       "      <td>002-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Various Beef Products</td>\n",
       "      <td>Other</td>\n",
       "      <td>42103.0</td>\n",
       "      <td>13</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-15</th>\n",
       "      <td>003-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef Franks</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>2664.0</td>\n",
       "      <td>15</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-17</th>\n",
       "      <td>004-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Beef and Pork Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>130000.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-17</th>\n",
       "      <td>005-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Spiral Hams</td>\n",
       "      <td>Listeria monocytogenes</td>\n",
       "      <td>67113.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-19</th>\n",
       "      <td>006-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Cheeseburger Mac Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>1770000.0</td>\n",
       "      <td>19</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-27</th>\n",
       "      <td>007-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Frozen Chicken Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>1250000.0</td>\n",
       "      <td>27</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-30</th>\n",
       "      <td>008-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Chicken Salad Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>2446.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-01</th>\n",
       "      <td>009-2014</td>\n",
       "      <td>II</td>\n",
       "      <td>Various Meat and Poultry Products</td>\n",
       "      <td>Undeclared Allergen</td>\n",
       "      <td>144000.0</td>\n",
       "      <td>32</td>\n",
       "      <td>365.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-04</th>\n",
       "      <td>010-2014</td>\n",
       "      <td>I</td>\n",
       "      <td>Beef Products</td>\n",
       "      <td>E. coli O103, O111, O121, O145, O26, O45</td>\n",
       "      <td>15865.0</td>\n",
       "      <td>35</td>\n",
       "      <td>365.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Recall Number Recall Class  \\\n",
       "ddRecal                                 \n",
       "2014-01-10      001-2014            I   \n",
       "2014-01-13      002-2014            I   \n",
       "2014-01-15      003-2014            I   \n",
       "2014-01-17      004-2014           II   \n",
       "2014-01-17      005-2014            I   \n",
       "2014-01-19      006-2014           II   \n",
       "2014-01-27      007-2014           II   \n",
       "2014-01-30      008-2014            I   \n",
       "2014-02-01      009-2014           II   \n",
       "2014-02-04      010-2014            I   \n",
       "\n",
       "                                            Product  \\\n",
       "ddRecal                                               \n",
       "2014-01-10  Mechanically Separated Chicken Products   \n",
       "2014-01-13                    Various Beef Products   \n",
       "2014-01-15                              Beef Franks   \n",
       "2014-01-17                   Beef and Pork Products   \n",
       "2014-01-17                              Spiral Hams   \n",
       "2014-01-19                Cheeseburger Mac Products   \n",
       "2014-01-27                  Frozen Chicken Products   \n",
       "2014-01-30                   Chicken Salad Products   \n",
       "2014-02-01        Various Meat and Poultry Products   \n",
       "2014-02-04                            Beef Products   \n",
       "\n",
       "                                   Reason for Recall  Pounds Recalled  \\\n",
       "ddRecal                                                                 \n",
       "2014-01-10                                Salmonella          33840.0   \n",
       "2014-01-13                                     Other          42103.0   \n",
       "2014-01-15                       Undeclared Allergen           2664.0   \n",
       "2014-01-17                       Undeclared Allergen         130000.0   \n",
       "2014-01-17                    Listeria monocytogenes          67113.0   \n",
       "2014-01-19                       Undeclared Allergen        1770000.0   \n",
       "2014-01-27                       Undeclared Allergen        1250000.0   \n",
       "2014-01-30                       Undeclared Allergen           2446.0   \n",
       "2014-02-01                       Undeclared Allergen         144000.0   \n",
       "2014-02-04  E. coli O103, O111, O121, O145, O26, O45          15865.0   \n",
       "\n",
       "            dayofyear  min.recall  \n",
       "ddRecal                            \n",
       "2014-01-10         10      2446.0  \n",
       "2014-01-13         13      2446.0  \n",
       "2014-01-15         15      2446.0  \n",
       "2014-01-17         17      2446.0  \n",
       "2014-01-17         17      2446.0  \n",
       "2014-01-19         19      2446.0  \n",
       "2014-01-27         27      2446.0  \n",
       "2014-01-30         30      2446.0  \n",
       "2014-02-01         32       365.0  \n",
       "2014-02-04         35       365.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#create a new column with month minimum of Pounds Recalled\n",
    "df[\"min.recall\"] = df.groupby([df.index.month])[\"Pounds Recalled\"].transform('min')\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}