{
 "cells": [
  {
   "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>Patron Type Code</th>\n",
       "      <th>Patron Type Definition</th>\n",
       "      <th>Total Checkouts</th>\n",
       "      <th>Total Renewals</th>\n",
       "      <th>Age Range</th>\n",
       "      <th>Home Library Code</th>\n",
       "      <th>Home Library Definition</th>\n",
       "      <th>Circulation Active Month</th>\n",
       "      <th>Circulation Active Year</th>\n",
       "      <th>Notice Preference Code</th>\n",
       "      <th>Notice Preference Definition</th>\n",
       "      <th>Provided Email Address</th>\n",
       "      <th>Year Patron Registered</th>\n",
       "      <th>Outside of County</th>\n",
       "      <th>Supervisor District</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>1092</td>\n",
       "      <td>761</td>\n",
       "      <td>60 to 64 years</td>\n",
       "      <td>M6</td>\n",
       "      <td>Mission</td>\n",
       "      <td>July</td>\n",
       "      <td>2016</td>\n",
       "      <td>p</td>\n",
       "      <td>phone</td>\n",
       "      <td>False</td>\n",
       "      <td>2003</td>\n",
       "      <td>False</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20 to 24 years</td>\n",
       "      <td>P1</td>\n",
       "      <td>Park</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>z</td>\n",
       "      <td>email</td>\n",
       "      <td>True</td>\n",
       "      <td>2014</td>\n",
       "      <td>False</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>31</td>\n",
       "      <td>22</td>\n",
       "      <td>25 to 34 years</td>\n",
       "      <td>S7</td>\n",
       "      <td>Sunset</td>\n",
       "      <td>April</td>\n",
       "      <td>2016</td>\n",
       "      <td>z</td>\n",
       "      <td>email</td>\n",
       "      <td>True</td>\n",
       "      <td>2010</td>\n",
       "      <td>False</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45 to 54 years</td>\n",
       "      <td>P1</td>\n",
       "      <td>Park</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>a</td>\n",
       "      <td>print</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25 to 34 years</td>\n",
       "      <td>X</td>\n",
       "      <td>Main Library</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>z</td>\n",
       "      <td>email</td>\n",
       "      <td>True</td>\n",
       "      <td>2015</td>\n",
       "      <td>False</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Patron Type Code Patron Type Definition  Total Checkouts  Total Renewals  \\\n",
       "0                 0                  ADULT             1092             761   \n",
       "1                 0                  ADULT                0               0   \n",
       "2                 0                  ADULT               31              22   \n",
       "3                 0                  ADULT                0               0   \n",
       "4                 0                  ADULT                0               0   \n",
       "\n",
       "        Age Range Home Library Code Home Library Definition  \\\n",
       "0  60 to 64 years                M6                 Mission   \n",
       "1  20 to 24 years                P1                    Park   \n",
       "2  25 to 34 years                S7                  Sunset   \n",
       "3  45 to 54 years                P1                    Park   \n",
       "4  25 to 34 years                 X            Main Library   \n",
       "\n",
       "  Circulation Active Month Circulation Active Year Notice Preference Code  \\\n",
       "0                     July                    2016                      p   \n",
       "1                     None                    None                      z   \n",
       "2                    April                    2016                      z   \n",
       "3                     None                    None                      a   \n",
       "4                     None                    None                      z   \n",
       "\n",
       "  Notice Preference Definition  Provided Email Address  \\\n",
       "0                        phone                   False   \n",
       "1                        email                    True   \n",
       "2                        email                    True   \n",
       "3                        print                   False   \n",
       "4                        email                    True   \n",
       "\n",
       "   Year Patron Registered  Outside of County  Supervisor District  \n",
       "0                    2003              False                  9.0  \n",
       "1                    2014              False                  5.0  \n",
       "2                    2010              False                  4.0  \n",
       "3                    2016               True                  NaN  \n",
       "4                    2015              False                  3.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"../data/Library_Usage.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         2016.0\n",
       "1            NaN\n",
       "2         2016.0\n",
       "3            NaN\n",
       "4            NaN\n",
       "           ...  \n",
       "423443    2015.0\n",
       "423444    2016.0\n",
       "423445    2016.0\n",
       "423446    2015.0\n",
       "423447       NaN\n",
       "Name: Circulation Active Year, Length: 423448, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Circulation Active Year'] = pd.to_numeric(\n",
    "  df['Circulation Active Year'], errors='coerce'\n",
    ")\n",
    "df['Circulation Active Year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        1900-07-01\n",
       "1               NaT\n",
       "2        1900-04-01\n",
       "3               NaT\n",
       "4               NaT\n",
       "            ...    \n",
       "423443   1900-03-01\n",
       "423444   1900-07-01\n",
       "423445   1900-07-01\n",
       "423446   1900-04-01\n",
       "423447          NaT\n",
       "Name: Circulation Active Month, Length: 423448, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Circulation Active Month'] = pd.to_datetime(\n",
    "    df['Circulation Active Month'],\n",
    "    errors='coerce',\n",
    "    format=\"%B\"\n",
    ")\n",
    "df['Circulation Active Month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         7.0\n",
       "1         NaN\n",
       "2         4.0\n",
       "3         NaN\n",
       "4         NaN\n",
       "         ... \n",
       "423443    3.0\n",
       "423444    7.0\n",
       "423445    7.0\n",
       "423446    4.0\n",
       "423447    NaN\n",
       "Name: Circulation Active Month, Length: 423448, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Circulation Active Month'] = df['Circulation Active Month'].dt.month\n",
    "df['Circulation Active Month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Membership Duration'] = (\n",
    "    df['Circulation Active Year'] - df['Year Patron Registered']\n",
    ")*12 + df['Circulation Active Month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         163.0\n",
       "1           0.0\n",
       "2          76.0\n",
       "3           0.0\n",
       "4           0.0\n",
       "          ...  \n",
       "423443     75.0\n",
       "423444      7.0\n",
       "423445     31.0\n",
       "423446    148.0\n",
       "423447      0.0\n",
       "Name: Membership Duration, Length: 423448, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Membership Duration'] = df['Membership Duration'].fillna(0)\n",
    "df['Membership Duration']"
   ]
  },
  {
   "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>Patron Type Code</th>\n",
       "      <th>Patron Type Definition</th>\n",
       "      <th>Total Checkouts</th>\n",
       "      <th>Total Renewals</th>\n",
       "      <th>Age Range</th>\n",
       "      <th>Home Library Code</th>\n",
       "      <th>Home Library Definition</th>\n",
       "      <th>Circulation Active Month</th>\n",
       "      <th>Circulation Active Year</th>\n",
       "      <th>Notice Preference Code</th>\n",
       "      <th>Notice Preference Definition</th>\n",
       "      <th>Provided Email Address</th>\n",
       "      <th>Year Patron Registered</th>\n",
       "      <th>Outside of County</th>\n",
       "      <th>Supervisor District</th>\n",
       "      <th>Membership Duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>1092</td>\n",
       "      <td>761</td>\n",
       "      <td>60 to 64 years</td>\n",
       "      <td>M6</td>\n",
       "      <td>Mission</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>p</td>\n",
       "      <td>phone</td>\n",
       "      <td>False</td>\n",
       "      <td>2003</td>\n",
       "      <td>False</td>\n",
       "      <td>9.0</td>\n",
       "      <td>163.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20 to 24 years</td>\n",
       "      <td>P1</td>\n",
       "      <td>Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>z</td>\n",
       "      <td>email</td>\n",
       "      <td>True</td>\n",
       "      <td>2014</td>\n",
       "      <td>False</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>31</td>\n",
       "      <td>22</td>\n",
       "      <td>25 to 34 years</td>\n",
       "      <td>S7</td>\n",
       "      <td>Sunset</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>z</td>\n",
       "      <td>email</td>\n",
       "      <td>True</td>\n",
       "      <td>2010</td>\n",
       "      <td>False</td>\n",
       "      <td>4.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45 to 54 years</td>\n",
       "      <td>P1</td>\n",
       "      <td>Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a</td>\n",
       "      <td>print</td>\n",
       "      <td>False</td>\n",
       "      <td>2016</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>ADULT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25 to 34 years</td>\n",
       "      <td>X</td>\n",
       "      <td>Main Library</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>z</td>\n",
       "      <td>email</td>\n",
       "      <td>True</td>\n",
       "      <td>2015</td>\n",
       "      <td>False</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Patron Type Code Patron Type Definition  Total Checkouts  Total Renewals  \\\n",
       "0                 0                  ADULT             1092             761   \n",
       "1                 0                  ADULT                0               0   \n",
       "2                 0                  ADULT               31              22   \n",
       "3                 0                  ADULT                0               0   \n",
       "4                 0                  ADULT                0               0   \n",
       "\n",
       "        Age Range Home Library Code Home Library Definition  \\\n",
       "0  60 to 64 years                M6                 Mission   \n",
       "1  20 to 24 years                P1                    Park   \n",
       "2  25 to 34 years                S7                  Sunset   \n",
       "3  45 to 54 years                P1                    Park   \n",
       "4  25 to 34 years                 X            Main Library   \n",
       "\n",
       "   Circulation Active Month  Circulation Active Year Notice Preference Code  \\\n",
       "0                       7.0                   2016.0                      p   \n",
       "1                       NaN                      NaN                      z   \n",
       "2                       4.0                   2016.0                      z   \n",
       "3                       NaN                      NaN                      a   \n",
       "4                       NaN                      NaN                      z   \n",
       "\n",
       "  Notice Preference Definition  Provided Email Address  \\\n",
       "0                        phone                   False   \n",
       "1                        email                    True   \n",
       "2                        email                    True   \n",
       "3                        print                   False   \n",
       "4                        email                    True   \n",
       "\n",
       "   Year Patron Registered  Outside of County  Supervisor District  \\\n",
       "0                    2003              False                  9.0   \n",
       "1                    2014              False                  5.0   \n",
       "2                    2010              False                  4.0   \n",
       "3                    2016               True                  NaN   \n",
       "4                    2015              False                  3.0   \n",
       "\n",
       "   Membership Duration  \n",
       "0                163.0  \n",
       "1                  0.0  \n",
       "2                 76.0  \n",
       "3                  0.0  \n",
       "4                  0.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}