feature_engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "xxx\n",
    "\n",
    "Index:\n",
    "\n",
    "* xxx\n",
    "\n",
    "Recommended books\n",
    "\n",
    "* Python Data Analysis\n",
    "* \n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dates and times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Imports\n",
    "import pandas as pd\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>benfica_goals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-05 20:23:02</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-02-04 19:52:32</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-03-07 19:47:08</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-04-03 21:02:31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              datetime  benfica_goals\n",
       "0  2018-01-05 20:23:02              3\n",
       "1  2018-02-04 19:52:32              2\n",
       "2  2018-03-07 19:47:08              5\n",
       "3  2018-04-03 21:02:31              1"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create toy dataset\n",
    "data = {'datetime': ['2018-01-05 20:23:02', '2018-02-04 19:52:32', '2018-03-07 19:47:08', '2018-04-03 21:02:31'], \n",
    "        'benfica_goals': [3, 2, 5, 1]}\n",
    "df = pd.DataFrame(data, columns = ['datetime', 'benfica_goals'])  # Convert dict into dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime         object\n",
       "benfica_goals     int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check data types\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* *date* is an object\n",
    "* To manipulate it, we need to use string operations\n",
    "* That's not [cool(io)](https://youtu.be/fPO76Jlnz6c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Convert to to_datetime\n",
    "df['datetime'] = pd.to_datetime(df['datetime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime         datetime64[ns]\n",
       "benfica_goals             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check data types\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* It changed to *datetime*\n",
    "* Let's see what we can do now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2018\n",
       "1    2018\n",
       "2    2018\n",
       "3    2018\n",
       "Name: datetime, dtype: int64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get year\n",
    "df['datetime'].dt.year  # Use dt to access datetimelike properties of the Series values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* It also works for month, minute, second, etc.\n",
    "* See [here](https://pandas.pydata.org/pandas-docs/stable/api.html#datetimelike-properties) the list of datetime properties that you can access "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2018-04-03 21:02:31')"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get maximum date\n",
    "df['datetime'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* It also works for minimum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>benfica_goals</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-05 20:23:02</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>20:23:02</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-02-04 19:52:32</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-02-04</td>\n",
       "      <td>19:52:32</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-03-07 19:47:08</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-03-07</td>\n",
       "      <td>19:47:08</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-04-03 21:02:31</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>21:02:31</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             datetime  benfica_goals        date      time  year  month  day\n",
       "0 2018-01-05 20:23:02              3  2018-01-05  20:23:02  2018      1    5\n",
       "1 2018-02-04 19:52:32              2  2018-02-04  19:52:32  2018      2    4\n",
       "2 2018-03-07 19:47:08              5  2018-03-07  19:47:08  2018      3    7\n",
       "3 2018-04-03 21:02:31              1  2018-04-03  21:02:31  2018      4    3"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Parse date and time\n",
    "df['date'] = df['datetime'].dt.date\n",
    "df['time'] = df['datetime'].dt.time\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>benfica_goals</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-05 20:23:02</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>20:23:02</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-02-04 19:52:32</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-02-04</td>\n",
       "      <td>19:52:32</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-03-07 19:47:08</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-03-07</td>\n",
       "      <td>19:47:08</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-04-03 21:02:31</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>21:02:31</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             datetime  benfica_goals       date      time  year  month  day\n",
       "0 2018-01-05 20:23:02              3 2018-01-05  20:23:02  2018      1    5\n",
       "1 2018-02-04 19:52:32              2 2018-02-04  19:52:32  2018      2    4\n",
       "2 2018-03-07 19:47:08              5 2018-03-07  19:47:08  2018      3    7\n",
       "3 2018-04-03 21:02:31              1 2018-04-03  21:02:31  2018      4    3"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Parse date into year, month, day\n",
    "df['date'] = pd.to_datetime(df['date'])\n",
    "\n",
    "df['year'] = df['date'].dt.year\n",
    "df['month'] = df['date'].dt.month\n",
    "df['day'] = df['date'].dt.day\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* You can do a similar exercise for *time*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>benfica_goals</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>prev_datetime</th>\n",
       "      <th>elapsed_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-05 20:23:02</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>20:23:02</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-02-04 19:52:32</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-02-04</td>\n",
       "      <td>19:52:32</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2018-01-05 20:23:02</td>\n",
       "      <td>29 days 23:29:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-03-07 19:47:08</td>\n",
       "      <td>5</td>\n",
       "      <td>2018-03-07</td>\n",
       "      <td>19:47:08</td>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>2018-02-04 19:52:32</td>\n",
       "      <td>30 days 23:54:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-04-03 21:02:31</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-03</td>\n",
       "      <td>21:02:31</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-03-07 19:47:08</td>\n",
       "      <td>27 days 01:15:23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             datetime  benfica_goals        date      time  year  month  day  \\\n",
       "0 2018-01-05 20:23:02              3  2018-01-05  20:23:02  2018      1    5   \n",
       "1 2018-02-04 19:52:32              2  2018-02-04  19:52:32  2018      2    4   \n",
       "2 2018-03-07 19:47:08              5  2018-03-07  19:47:08  2018      3    7   \n",
       "3 2018-04-03 21:02:31              1  2018-04-03  21:02:31  2018      4    3   \n",
       "\n",
       "        prev_datetime     elapsed_time  \n",
       "0                 NaT              NaT  \n",
       "1 2018-01-05 20:23:02 29 days 23:29:30  \n",
       "2 2018-02-04 19:52:32 30 days 23:54:36  \n",
       "3 2018-03-07 19:47:08 27 days 01:15:23  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Time elapsed between dates\n",
    "df['prev_datetime'] = df['datetime'].shift()  # Auxiliary column\n",
    "df['elapsed_time'] = df['datetime'] - df['prev_datetime']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Coordinates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* https://stackoverflow.com/questions/29545704/fast-haversine-approximation-python-pandas\n",
    "* https://stackoverflow.com/questions/40452759/pandas-latitude-longitude-to-distance-between-successive-rows\n",
    "* https://gist.github.com/rochacbruno/2883505"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({ 'DAY': np.random.choice(['Monday','Tuesday','Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'], 10000),\n",
    "                    'LATITUDE': np.random.uniform(low=40.5, high=41, size=10000),\n",
    "                    'LONGITUDE': np.random.uniform(low=-74.5, high=-75, size=10000)})\n",
    "coords = df[['LATITUDE', 'LONGITUDE']]\n",
    "coords1 = coords.values[:, None].astype(float)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}