pandas_tips_and_tricks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas tips and tricks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame([['Chandler Bing','party','2017-08-04 08:00:00',51],\n",
    " ['Chandler Bing','party','2017-08-04 13:00:00',60],\n",
    " ['Chandler Bing','party','2017-08-04 15:00:00',59],\n",
    " ['Harry Kane','football','2017-08-04 13:00:00',80],\n",
    " ['Harry Kane','party','2017-08-04 11:00:00',90],\n",
    " ['Harry Kane','party','2017-08-04 07:00:00',68],\n",
    " ['John Doe','beach','2017-08-04 07:00:00',63],\n",
    " ['John Doe','beach','2017-08-04 12:00:00',61],\n",
    " ['John Doe','beach','2017-08-04 14:00:00',65],\n",
    " ['Joey Tribbiani','party','2017-08-04 09:00:00',54],\n",
    " ['Joey Tribbiani','party','2017-08-04 10:00:00',67],\n",
    " ['Joey Tribbiani','football','2017-08-04 08:00:00',84],\n",
    " ['Monica Geller','travel','2017-08-04 07:00:00',90],\n",
    " ['Monica Geller','travel','2017-08-04 08:00:00',96],\n",
    " ['Monica Geller','travel','2017-08-04 09:00:00',74],\n",
    " ['Phoebe Buffey','travel','2017-08-04 10:00:00',52],\n",
    " ['Phoebe Buffey','travel','2017-08-04 12:00:00',84],\n",
    " ['Phoebe Buffey','football','2017-08-04 15:00:00',58],\n",
    " ['Ross Geller','party','2017-08-04 09:00:00',96],\n",
    " ['Ross Geller','party','2017-08-04 11:00:00',81],\n",
    " ['Ross Geller','travel','2017-08-04 14:00:00',60]],\n",
    " columns=['name','activity','timestamp','money_spent'])\n",
    "\n",
    "df['timestamp'] = pd.to_datetime(df['timestamp'])\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Ufuncs and why they are so much better than apply command\n",
    "Pandas has an apply function which let you apply just about any function on all the values in a column. Note that apply is just a little bit faster than a python for loop! That’s why it is most recommended using pandas builtin ufuncs for applying preprocessing tasks on columns (if a suitable ufunc is available for your task). Ufuncs, are special functions (based on numpy library) implemented in C and that’s why they are highly efficient. Among the useful ufuncs we will mention are: .diff, .shift, .cumsum, .cumcount, .str commands (works on strings), .dt commands (works on dates) and many more."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>football</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name  activity           timestamp  money_spent\n",
       "0  Chandler Bing     party 2017-08-04 08:00:00           51\n",
       "1  Chandler Bing     party 2017-08-04 13:00:00           60\n",
       "2  Chandler Bing     party 2017-08-04 15:00:00           59\n",
       "3     Harry Kane  football 2017-08-04 13:00:00           80\n",
       "4     Harry Kane     party 2017-08-04 11:00:00           90"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## String commands\n",
    "For string manipulations it is most recommended to use the Pandas string commands (which are Ufuncs).\n",
    "For example, you can split a column which includes the full name of a person into two columns with the first and last name using .str.split and expand=True."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    [Chandler, Bing]\n",
       "1    [Chandler, Bing]\n",
       "2    [Chandler, Bing]\n",
       "3       [Harry, Kane]\n",
       "4       [Harry, Kane]\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.name.str.split(\" \").head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler</td>\n",
       "      <td>Bing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler</td>\n",
       "      <td>Bing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler</td>\n",
       "      <td>Bing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry</td>\n",
       "      <td>Kane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry</td>\n",
       "      <td>Kane</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0     1\n",
       "0  Chandler  Bing\n",
       "1  Chandler  Bing\n",
       "2  Chandler  Bing\n",
       "3     Harry  Kane\n",
       "4     Harry  Kane"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.name.str.split(\" \", expand=True).head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition you can clean any string column efficiently using .str.replace and a suitable regex."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group by and value_counts\n",
    "Groupby is a very powerful pandas method. You can group by one column and count the values of another column per this column value using value_counts. Using groupby and value_counts we can count the number of activities each person did.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name            activity\n",
       "Chandler Bing   party       3\n",
       "Harry Kane      party       2\n",
       "                football    1\n",
       "Joey Tribbiani  party       2\n",
       "                football    1\n",
       "John Doe        beach       3\n",
       "Monica Geller   travel      3\n",
       "Phoebe Buffey   travel      2\n",
       "                football    1\n",
       "Ross Geller     party       2\n",
       "                travel      1\n",
       "Name: activity, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('name')['activity'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is multi index, a valuable trick in pandas dataframe which allows us to have a few levels of index hierarchy in our dataframe. In this case the person name is the level 0 of the index and the activity is on level 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unstack\n",
    "We can also create features for the summer activities counts per person, by applying unstack on the above code. Unstack switches the rows to columns to get the activity counts as features. By doing unstack we are transforming the last level of the index to the columns. All the activities values will now be the columns of a the dataframe and when a person has not done a certain activity this feature will get Nan value. Fillna fills all these missing values (activities which were not visited by the person) with 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>activity</th>\n",
       "      <th>beach</th>\n",
       "      <th>football</th>\n",
       "      <th>party</th>\n",
       "      <th>travel</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Chandler Bing</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harry Kane</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Joey Tribbiani</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Doe</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monica Geller</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Phoebe Buffey</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ross Geller</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "activity        beach  football  party  travel\n",
       "name                                          \n",
       "Chandler Bing     0.0       0.0    3.0     0.0\n",
       "Harry Kane        0.0       1.0    2.0     0.0\n",
       "Joey Tribbiani    0.0       1.0    2.0     0.0\n",
       "John Doe          3.0       0.0    0.0     0.0\n",
       "Monica Geller     0.0       0.0    0.0     3.0\n",
       "Phoebe Buffey     0.0       1.0    0.0     2.0\n",
       "Ross Geller       0.0       0.0    2.0     1.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('name')['activity'].value_counts().unstack().fillna(0)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## groupby, diff, shift, and loc + A great tip for efficiency\n",
    "Knowing the time differences between person activities can be quite interesting for predicting who is the most fun person. How long did a person hang out in a party? how long did he/she hang out at the the beach? This might be useful for us as a feature, depends on the activity.\n",
    "\n",
    "The most straight forward way to calculate the time differences would be to groupby the person name and them calculate the difference on the timestamp field using diff():"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.sort_values(by=['name','timestamp'])\n",
    "df['time_diff'] = df.groupby('name')['timestamp'].diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>05:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "      <td>02:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>68</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name activity           timestamp  money_spent time_diff\n",
       "0  Chandler Bing    party 2017-08-04 08:00:00           51       NaT\n",
       "1  Chandler Bing    party 2017-08-04 13:00:00           60  05:00:00\n",
       "2  Chandler Bing    party 2017-08-04 15:00:00           59  02:00:00\n",
       "5     Harry Kane    party 2017-08-04 07:00:00           68       NaT\n",
       "4     Harry Kane    party 2017-08-04 11:00:00           90  04:00:00"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you have a lot of data and you want to save some time (this can be about 10 times faster depends on your data size) you can skip the groupby and just do the diff after sorting the data and then deleting the first row of each person which is not relevant.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.sort_values(by=['name','timestamp'])\n",
    "df['time_diff'] = df['timestamp'].diff()\n",
    "df.loc[df.name != df.name.shift(), 'time_diff'] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>05:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "      <td>02:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>68</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name activity           timestamp  money_spent time_diff\n",
       "0  Chandler Bing    party 2017-08-04 08:00:00           51       NaT\n",
       "1  Chandler Bing    party 2017-08-04 13:00:00           60  05:00:00\n",
       "2  Chandler Bing    party 2017-08-04 15:00:00           59  02:00:00\n",
       "5     Harry Kane    party 2017-08-04 07:00:00           68       NaT\n",
       "4     Harry Kane    party 2017-08-04 11:00:00           90  04:00:00"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "BTW — the useful .Shift command shift all the column down per one space, so we can see on which row this column is changing by doing this: df.name!=df.name.shift().\n",
    "\n",
    "And .loc command is the most recommended way to set values for a column for specific indices.\n",
    "\n",
    "To change the time_diff to seconds units:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['time_diff'] = df.time_diff.dt.total_seconds()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>18000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "      <td>7200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>14400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name activity           timestamp  money_spent  time_diff\n",
       "0  Chandler Bing    party 2017-08-04 08:00:00           51        NaN\n",
       "1  Chandler Bing    party 2017-08-04 13:00:00           60    18000.0\n",
       "2  Chandler Bing    party 2017-08-04 15:00:00           59     7200.0\n",
       "5     Harry Kane    party 2017-08-04 07:00:00           68        NaN\n",
       "4     Harry Kane    party 2017-08-04 11:00:00           90    14400.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get the duration per row:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['row_duration'] = df.time_diff.shift(-1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "      <th>row_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>7200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>7200.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name activity           timestamp  money_spent  time_diff  \\\n",
       "0  Chandler Bing    party 2017-08-04 08:00:00           51        NaN   \n",
       "1  Chandler Bing    party 2017-08-04 13:00:00           60    18000.0   \n",
       "2  Chandler Bing    party 2017-08-04 15:00:00           59     7200.0   \n",
       "5     Harry Kane    party 2017-08-04 07:00:00           68        NaN   \n",
       "4     Harry Kane    party 2017-08-04 11:00:00           90    14400.0   \n",
       "\n",
       "   row_duration  \n",
       "0       18000.0  \n",
       "1        7200.0  \n",
       "2           NaN  \n",
       "5       14400.0  \n",
       "4        7200.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cumcount and Cumsum\n",
    "This are two really cool Ufuncs which can help you with many things. Cumcount create a cumulative count. For example we can take only the second activity for each person by grouping by the person name and then applying cumcount. This will just give a count for the activities by their order. Than we can take only the second activity for each person by doing ==1 (or the third activity by doing ==2) and applying the indices on the original sorted dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.sort_values(by=['name','timestamp'])\n",
    "df2 = df[df.groupby('name').cumcount()==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "      <th>row_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>7200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>7200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 09:00:00</td>\n",
       "      <td>54</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>3600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>John Doe</td>\n",
       "      <td>beach</td>\n",
       "      <td>2017-08-04 12:00:00</td>\n",
       "      <td>61</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>7200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>96</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>3600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Phoebe Buffey</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 12:00:00</td>\n",
       "      <td>84</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>10800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>81</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>10800.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name activity           timestamp  money_spent  time_diff  \\\n",
       "1    Chandler Bing    party 2017-08-04 13:00:00           60    18000.0   \n",
       "4       Harry Kane    party 2017-08-04 11:00:00           90    14400.0   \n",
       "9   Joey Tribbiani    party 2017-08-04 09:00:00           54     3600.0   \n",
       "7         John Doe    beach 2017-08-04 12:00:00           61    18000.0   \n",
       "13   Monica Geller   travel 2017-08-04 08:00:00           96     3600.0   \n",
       "16   Phoebe Buffey   travel 2017-08-04 12:00:00           84     7200.0   \n",
       "19     Ross Geller    party 2017-08-04 11:00:00           81     7200.0   \n",
       "\n",
       "    row_duration  \n",
       "1         7200.0  \n",
       "4         7200.0  \n",
       "9         3600.0  \n",
       "7         7200.0  \n",
       "13        3600.0  \n",
       "16       10800.0  \n",
       "19       10800.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.sort_values(by=['name','timestamp'])\n",
    "df2 = df[df.groupby('name').cumcount()==2]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "      <th>row_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>football</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>80</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 10:00:00</td>\n",
       "      <td>67</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>John Doe</td>\n",
       "      <td>beach</td>\n",
       "      <td>2017-08-04 14:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 09:00:00</td>\n",
       "      <td>74</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Phoebe Buffey</td>\n",
       "      <td>football</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>58</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 14:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  activity           timestamp  money_spent  time_diff  \\\n",
       "2    Chandler Bing     party 2017-08-04 15:00:00           59     7200.0   \n",
       "3       Harry Kane  football 2017-08-04 13:00:00           80     7200.0   \n",
       "10  Joey Tribbiani     party 2017-08-04 10:00:00           67     3600.0   \n",
       "8         John Doe     beach 2017-08-04 14:00:00           65     7200.0   \n",
       "14   Monica Geller    travel 2017-08-04 09:00:00           74     3600.0   \n",
       "17   Phoebe Buffey  football 2017-08-04 15:00:00           58    10800.0   \n",
       "20     Ross Geller    travel 2017-08-04 14:00:00           60    10800.0   \n",
       "\n",
       "    row_duration  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "10           NaN  \n",
       "8            NaN  \n",
       "14           NaN  \n",
       "17           NaN  \n",
       "20           NaN  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cumsum is just a cummulative summary of a numeric cell. For example you can add the money the person spend in each activity as an additional cell and then summarize the money spent by a person at each time of the day using:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.sort_values(by=['name','timestamp'])\n",
    "df['money_spent_so_far'] = df.groupby('name')['money_spent'].cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "      <th>row_duration</th>\n",
       "      <th>money_spent_so_far</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name activity           timestamp  money_spent  time_diff  \\\n",
       "0  Chandler Bing    party 2017-08-04 08:00:00           51        NaN   \n",
       "1  Chandler Bing    party 2017-08-04 13:00:00           60    18000.0   \n",
       "2  Chandler Bing    party 2017-08-04 15:00:00           59     7200.0   \n",
       "5     Harry Kane    party 2017-08-04 07:00:00           68        NaN   \n",
       "4     Harry Kane    party 2017-08-04 11:00:00           90    14400.0   \n",
       "\n",
       "   row_duration  money_spent_so_far  \n",
       "0       18000.0                  51  \n",
       "1        7200.0                 111  \n",
       "2           NaN                 170  \n",
       "5       14400.0                  68  \n",
       "4        7200.0                 158  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## groupby, max, min for measuring the duration of activities\n",
    "In section 3 we wanted to know how much time each person spent in each activity. But we overlooked that sometimes we get multiple records for an acitivity which is actually the continuance of the same activities. So to get the actual activity duration we should measure the time from the first consecutive activity appearance to the last. For that we need to mark the change in activities and mark each row with the activity number. We would do this using the .shift command and the .cumsum command we saw before. A new activity is when the activity changes or the person changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['activity_change'] = (df.activity!=df.activity.shift()) | (df.name!=df.name.shift())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we will calculate the activity number for each row by grouping per user and applying the glorious .cumsum:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['activity_num'] = df.groupby('name')['activity_change'].cumsum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>name</th>\n",
       "      <th>activity</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>money_spent</th>\n",
       "      <th>time_diff</th>\n",
       "      <th>row_duration</th>\n",
       "      <th>money_spent_so_far</th>\n",
       "      <th>activity_change</th>\n",
       "      <th>activity_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>51</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>111</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>59</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>68</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>14400.0</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>158</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>football</td>\n",
       "      <td>2017-08-04 13:00:00</td>\n",
       "      <td>80</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>238</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>football</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>84</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 09:00:00</td>\n",
       "      <td>54</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>138</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 10:00:00</td>\n",
       "      <td>67</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>205</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>John Doe</td>\n",
       "      <td>beach</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>63</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>John Doe</td>\n",
       "      <td>beach</td>\n",
       "      <td>2017-08-04 12:00:00</td>\n",
       "      <td>61</td>\n",
       "      <td>18000.0</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>124</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>John Doe</td>\n",
       "      <td>beach</td>\n",
       "      <td>2017-08-04 14:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>189</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 07:00:00</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>90</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 08:00:00</td>\n",
       "      <td>96</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>186</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 09:00:00</td>\n",
       "      <td>74</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>260</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Phoebe Buffey</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 10:00:00</td>\n",
       "      <td>52</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>52</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Phoebe Buffey</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 12:00:00</td>\n",
       "      <td>84</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>136</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Phoebe Buffey</td>\n",
       "      <td>football</td>\n",
       "      <td>2017-08-04 15:00:00</td>\n",
       "      <td>58</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>194</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 09:00:00</td>\n",
       "      <td>96</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>96</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>party</td>\n",
       "      <td>2017-08-04 11:00:00</td>\n",
       "      <td>81</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>177</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>travel</td>\n",
       "      <td>2017-08-04 14:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>10800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>237</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  activity           timestamp  money_spent  time_diff  \\\n",
       "0    Chandler Bing     party 2017-08-04 08:00:00           51        NaN   \n",
       "1    Chandler Bing     party 2017-08-04 13:00:00           60    18000.0   \n",
       "2    Chandler Bing     party 2017-08-04 15:00:00           59     7200.0   \n",
       "5       Harry Kane     party 2017-08-04 07:00:00           68        NaN   \n",
       "4       Harry Kane     party 2017-08-04 11:00:00           90    14400.0   \n",
       "3       Harry Kane  football 2017-08-04 13:00:00           80     7200.0   \n",
       "11  Joey Tribbiani  football 2017-08-04 08:00:00           84        NaN   \n",
       "9   Joey Tribbiani     party 2017-08-04 09:00:00           54     3600.0   \n",
       "10  Joey Tribbiani     party 2017-08-04 10:00:00           67     3600.0   \n",
       "6         John Doe     beach 2017-08-04 07:00:00           63        NaN   \n",
       "7         John Doe     beach 2017-08-04 12:00:00           61    18000.0   \n",
       "8         John Doe     beach 2017-08-04 14:00:00           65     7200.0   \n",
       "12   Monica Geller    travel 2017-08-04 07:00:00           90        NaN   \n",
       "13   Monica Geller    travel 2017-08-04 08:00:00           96     3600.0   \n",
       "14   Monica Geller    travel 2017-08-04 09:00:00           74     3600.0   \n",
       "15   Phoebe Buffey    travel 2017-08-04 10:00:00           52        NaN   \n",
       "16   Phoebe Buffey    travel 2017-08-04 12:00:00           84     7200.0   \n",
       "17   Phoebe Buffey  football 2017-08-04 15:00:00           58    10800.0   \n",
       "18     Ross Geller     party 2017-08-04 09:00:00           96        NaN   \n",
       "19     Ross Geller     party 2017-08-04 11:00:00           81     7200.0   \n",
       "20     Ross Geller    travel 2017-08-04 14:00:00           60    10800.0   \n",
       "\n",
       "    row_duration  money_spent_so_far  activity_change  activity_num  \n",
       "0        18000.0                  51             True           1.0  \n",
       "1         7200.0                 111            False           1.0  \n",
       "2            NaN                 170            False           1.0  \n",
       "5        14400.0                  68             True           1.0  \n",
       "4         7200.0                 158            False           1.0  \n",
       "3            NaN                 238             True           2.0  \n",
       "11        3600.0                  84             True           1.0  \n",
       "9         3600.0                 138             True           2.0  \n",
       "10           NaN                 205            False           2.0  \n",
       "6        18000.0                  63             True           1.0  \n",
       "7         7200.0                 124            False           1.0  \n",
       "8            NaN                 189            False           1.0  \n",
       "12        3600.0                  90             True           1.0  \n",
       "13        3600.0                 186            False           1.0  \n",
       "14           NaN                 260            False           1.0  \n",
       "15        7200.0                  52             True           1.0  \n",
       "16       10800.0                 136            False           1.0  \n",
       "17           NaN                 194             True           2.0  \n",
       "18        7200.0                  96             True           1.0  \n",
       "19       10800.0                 177            False           1.0  \n",
       "20           NaN                 237             True           2.0  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can calculate the duration of each activity as follows by grouping per name and activity num (and activity — which doesn’t really change the grouping but we need it to have the activity name) and calculating the sum of activity duration per row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name            activity_num  activity\n",
       "Chandler Bing   1.0           party       25200.0\n",
       "Harry Kane      1.0           party       21600.0\n",
       "                2.0           football        NaN\n",
       "Joey Tribbiani  1.0           football     3600.0\n",
       "                2.0           party        3600.0\n",
       "John Doe        1.0           beach       25200.0\n",
       "Monica Geller   1.0           travel       7200.0\n",
       "Phoebe Buffey   1.0           travel      18000.0\n",
       "                2.0           football        NaN\n",
       "Ross Geller     1.0           party       18000.0\n",
       "                2.0           travel          NaN\n",
       "Name: row_duration, dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activity_duration = df.groupby(['name','activity_num','activity'])['row_duration'].sum()\n",
    "activity_duration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then you can the maximal/minimal activity duration for each person (or median or mean) using a command like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "activity_duration_reset_index = activity_duration.reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>name</th>\n",
       "      <th>activity_num</th>\n",
       "      <th>activity</th>\n",
       "      <th>max_activity_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chandler Bing</td>\n",
       "      <td>1.0</td>\n",
       "      <td>party</td>\n",
       "      <td>25200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>1.0</td>\n",
       "      <td>party</td>\n",
       "      <td>21600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Joey Tribbiani</td>\n",
       "      <td>1.0</td>\n",
       "      <td>football</td>\n",
       "      <td>3600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>John Doe</td>\n",
       "      <td>1.0</td>\n",
       "      <td>beach</td>\n",
       "      <td>25200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Monica Geller</td>\n",
       "      <td>1.0</td>\n",
       "      <td>travel</td>\n",
       "      <td>7200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Phoebe Buffey</td>\n",
       "      <td>1.0</td>\n",
       "      <td>travel</td>\n",
       "      <td>18000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ross Geller</td>\n",
       "      <td>1.0</td>\n",
       "      <td>party</td>\n",
       "      <td>18000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             name  activity_num  activity  max_activity_duration\n",
       "0   Chandler Bing           1.0     party                25200.0\n",
       "1      Harry Kane           1.0     party                21600.0\n",
       "3  Joey Tribbiani           1.0  football                 3600.0\n",
       "5        John Doe           1.0     beach                25200.0\n",
       "6   Monica Geller           1.0    travel                 7200.0\n",
       "7   Phoebe Buffey           1.0    travel                18000.0\n",
       "9     Ross Geller           1.0     party                18000.0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activity_duration_reset_index.loc[activity_duration_reset_index.groupby('name')['row_duration'].idxmax()].rename(columns={\"row_duration\":'max_activity_duration'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "This was a short Pandas tour using a summer activities made-up dataset. Hope you’ve learned and enjoy it. Good luck with your next Pandas project and enjoy the summer!"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}