{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3136fdb8-97c4-47d7-9b0a-61529c747e4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "#set up strings\n",
    "carnum = 'carnum'\n",
    "start = 'start'\n",
    "end = 'end'\n",
    "columns = [\n",
    "    carnum,\n",
    "    start,\n",
    "    end,\n",
    "    'distance',\n",
    "    'gas'\n",
    "]\n",
    "mileage = 'mileage'\n",
    "triptime = 'time'\n",
    "speed = 'speed'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1e7a8579-893c-42d2-94fc-73c2ada83d74",
   "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>carnum</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>distance</th>\n",
       "      <th>gas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 04:57:52</td>\n",
       "      <td>2022-08-22 05:48:15</td>\n",
       "      <td>98.863620</td>\n",
       "      <td>4.842427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 05:00:25</td>\n",
       "      <td>2022-08-22 06:48:40</td>\n",
       "      <td>60.952214</td>\n",
       "      <td>10.008699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 11:58:03</td>\n",
       "      <td>2022-08-22 12:21:11</td>\n",
       "      <td>44.861855</td>\n",
       "      <td>6.668674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 19:55:54</td>\n",
       "      <td>2022-08-22 22:37:06</td>\n",
       "      <td>26.679683</td>\n",
       "      <td>5.725053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 10:55:57</td>\n",
       "      <td>2022-08-22 13:03:49</td>\n",
       "      <td>56.449703</td>\n",
       "      <td>3.550615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 05:41:40</td>\n",
       "      <td>2022-08-22 06:52:23</td>\n",
       "      <td>29.205601</td>\n",
       "      <td>8.796899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 00:34:24</td>\n",
       "      <td>2022-08-22 02:53:00</td>\n",
       "      <td>62.360221</td>\n",
       "      <td>5.515841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 19:02:12</td>\n",
       "      <td>2022-08-22 20:34:31</td>\n",
       "      <td>111.759254</td>\n",
       "      <td>8.484224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 02:01:12</td>\n",
       "      <td>2022-08-22 02:41:58</td>\n",
       "      <td>45.395829</td>\n",
       "      <td>6.123383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 07:17:09</td>\n",
       "      <td>2022-08-22 07:48:40</td>\n",
       "      <td>20.969945</td>\n",
       "      <td>7.003565</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>685 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     carnum               start                 end    distance        gas\n",
       "0        73 2022-08-22 04:57:52 2022-08-22 05:48:15   98.863620   4.842427\n",
       "1        73 2022-08-22 05:00:25 2022-08-22 06:48:40   60.952214  10.008699\n",
       "2        73 2022-08-22 11:58:03 2022-08-22 12:21:11   44.861855   6.668674\n",
       "3        73 2022-08-22 19:55:54 2022-08-22 22:37:06   26.679683   5.725053\n",
       "4        73 2022-08-22 10:55:57 2022-08-22 13:03:49   56.449703   3.550615\n",
       "..      ...                 ...                 ...         ...        ...\n",
       "680      56 2022-08-22 05:41:40 2022-08-22 06:52:23   29.205601   8.796899\n",
       "681      56 2022-08-22 00:34:24 2022-08-22 02:53:00   62.360221   5.515841\n",
       "682      56 2022-08-22 19:02:12 2022-08-22 20:34:31  111.759254   8.484224\n",
       "683      56 2022-08-22 02:01:12 2022-08-22 02:41:58   45.395829   6.123383\n",
       "684      56 2022-08-22 07:17:09 2022-08-22 07:48:40   20.969945   7.003565\n",
       "\n",
       "[685 rows x 5 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read file into dataframe\n",
    "filename = os.path.join('files','cardata.csv')\n",
    "df = pd.read_csv(filename,names=columns,parse_dates=['start','end'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "efcec18b-cdc9-4676-ace6-c982f3eafcac",
   "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>carnum</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>distance</th>\n",
       "      <th>gas</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 04:57:52</td>\n",
       "      <td>2022-08-22 05:48:15</td>\n",
       "      <td>98.863620</td>\n",
       "      <td>4.842427</td>\n",
       "      <td>0 days 00:50:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 05:00:25</td>\n",
       "      <td>2022-08-22 06:48:40</td>\n",
       "      <td>60.952214</td>\n",
       "      <td>10.008699</td>\n",
       "      <td>0 days 01:48:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 11:58:03</td>\n",
       "      <td>2022-08-22 12:21:11</td>\n",
       "      <td>44.861855</td>\n",
       "      <td>6.668674</td>\n",
       "      <td>0 days 00:23:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 19:55:54</td>\n",
       "      <td>2022-08-22 22:37:06</td>\n",
       "      <td>26.679683</td>\n",
       "      <td>5.725053</td>\n",
       "      <td>0 days 02:41:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>73</td>\n",
       "      <td>2022-08-22 10:55:57</td>\n",
       "      <td>2022-08-22 13:03:49</td>\n",
       "      <td>56.449703</td>\n",
       "      <td>3.550615</td>\n",
       "      <td>0 days 02:07:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 05:41:40</td>\n",
       "      <td>2022-08-22 06:52:23</td>\n",
       "      <td>29.205601</td>\n",
       "      <td>8.796899</td>\n",
       "      <td>0 days 01:10:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 00:34:24</td>\n",
       "      <td>2022-08-22 02:53:00</td>\n",
       "      <td>62.360221</td>\n",
       "      <td>5.515841</td>\n",
       "      <td>0 days 02:18:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 19:02:12</td>\n",
       "      <td>2022-08-22 20:34:31</td>\n",
       "      <td>111.759254</td>\n",
       "      <td>8.484224</td>\n",
       "      <td>0 days 01:32:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 02:01:12</td>\n",
       "      <td>2022-08-22 02:41:58</td>\n",
       "      <td>45.395829</td>\n",
       "      <td>6.123383</td>\n",
       "      <td>0 days 00:40:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>56</td>\n",
       "      <td>2022-08-22 07:17:09</td>\n",
       "      <td>2022-08-22 07:48:40</td>\n",
       "      <td>20.969945</td>\n",
       "      <td>7.003565</td>\n",
       "      <td>0 days 00:31:31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>685 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     carnum               start                 end    distance        gas  \\\n",
       "0        73 2022-08-22 04:57:52 2022-08-22 05:48:15   98.863620   4.842427   \n",
       "1        73 2022-08-22 05:00:25 2022-08-22 06:48:40   60.952214  10.008699   \n",
       "2        73 2022-08-22 11:58:03 2022-08-22 12:21:11   44.861855   6.668674   \n",
       "3        73 2022-08-22 19:55:54 2022-08-22 22:37:06   26.679683   5.725053   \n",
       "4        73 2022-08-22 10:55:57 2022-08-22 13:03:49   56.449703   3.550615   \n",
       "..      ...                 ...                 ...         ...        ...   \n",
       "680      56 2022-08-22 05:41:40 2022-08-22 06:52:23   29.205601   8.796899   \n",
       "681      56 2022-08-22 00:34:24 2022-08-22 02:53:00   62.360221   5.515841   \n",
       "682      56 2022-08-22 19:02:12 2022-08-22 20:34:31  111.759254   8.484224   \n",
       "683      56 2022-08-22 02:01:12 2022-08-22 02:41:58   45.395829   6.123383   \n",
       "684      56 2022-08-22 07:17:09 2022-08-22 07:48:40   20.969945   7.003565   \n",
       "\n",
       "               time  \n",
       "0   0 days 00:50:23  \n",
       "1   0 days 01:48:15  \n",
       "2   0 days 00:23:08  \n",
       "3   0 days 02:41:12  \n",
       "4   0 days 02:07:52  \n",
       "..              ...  \n",
       "680 0 days 01:10:43  \n",
       "681 0 days 02:18:36  \n",
       "682 0 days 01:32:19  \n",
       "683 0 days 00:40:46  \n",
       "684 0 days 00:31:31  \n",
       "\n",
       "[685 rows x 6 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#compute trip times\n",
    "df[triptime] = df.end - df.start\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6a1526cd-c9b6-431e-9b89-508cb44e53f8",
   "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>carnum</th>\n",
       "      <th>distance</th>\n",
       "      <th>gas</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>73</td>\n",
       "      <td>98.863620</td>\n",
       "      <td>4.842427</td>\n",
       "      <td>0 days 00:50:23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>73</td>\n",
       "      <td>60.952214</td>\n",
       "      <td>10.008699</td>\n",
       "      <td>0 days 01:48:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>73</td>\n",
       "      <td>44.861855</td>\n",
       "      <td>6.668674</td>\n",
       "      <td>0 days 00:23:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>73</td>\n",
       "      <td>26.679683</td>\n",
       "      <td>5.725053</td>\n",
       "      <td>0 days 02:41:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>73</td>\n",
       "      <td>56.449703</td>\n",
       "      <td>3.550615</td>\n",
       "      <td>0 days 02:07:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680</th>\n",
       "      <td>56</td>\n",
       "      <td>29.205601</td>\n",
       "      <td>8.796899</td>\n",
       "      <td>0 days 01:10:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>56</td>\n",
       "      <td>62.360221</td>\n",
       "      <td>5.515841</td>\n",
       "      <td>0 days 02:18:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>682</th>\n",
       "      <td>56</td>\n",
       "      <td>111.759254</td>\n",
       "      <td>8.484224</td>\n",
       "      <td>0 days 01:32:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>56</td>\n",
       "      <td>45.395829</td>\n",
       "      <td>6.123383</td>\n",
       "      <td>0 days 00:40:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>56</td>\n",
       "      <td>20.969945</td>\n",
       "      <td>7.003565</td>\n",
       "      <td>0 days 00:31:31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>685 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     carnum    distance        gas            time\n",
       "0        73   98.863620   4.842427 0 days 00:50:23\n",
       "1        73   60.952214  10.008699 0 days 01:48:15\n",
       "2        73   44.861855   6.668674 0 days 00:23:08\n",
       "3        73   26.679683   5.725053 0 days 02:41:12\n",
       "4        73   56.449703   3.550615 0 days 02:07:52\n",
       "..      ...         ...        ...             ...\n",
       "680      56   29.205601   8.796899 0 days 01:10:43\n",
       "681      56   62.360221   5.515841 0 days 02:18:36\n",
       "682      56  111.759254   8.484224 0 days 01:32:19\n",
       "683      56   45.395829   6.123383 0 days 00:40:46\n",
       "684      56   20.969945   7.003565 0 days 00:31:31\n",
       "\n",
       "[685 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get rid of start and end - no longer needed\n",
    "df.drop(columns=[start,end],inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a1083c0e-8af9-4a73-869d-6ab18c27c222",
   "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>distance</th>\n",
       "      <th>gas</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carnum</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>693.396753</td>\n",
       "      <td>78.110310</td>\n",
       "      <td>0 days 12:52:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>981.044936</td>\n",
       "      <td>100.654307</td>\n",
       "      <td>0 days 18:20:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>456.638354</td>\n",
       "      <td>46.209141</td>\n",
       "      <td>0 days 08:14:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>392.063016</td>\n",
       "      <td>44.488116</td>\n",
       "      <td>0 days 10:02:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>777.309219</td>\n",
       "      <td>94.453047</td>\n",
       "      <td>0 days 19:19:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>577.798733</td>\n",
       "      <td>75.810127</td>\n",
       "      <td>0 days 16:47:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>1172.460418</td>\n",
       "      <td>107.900125</td>\n",
       "      <td>1 days 02:12:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>1581.524745</td>\n",
       "      <td>130.386475</td>\n",
       "      <td>1 days 05:07:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>1086.025420</td>\n",
       "      <td>121.220634</td>\n",
       "      <td>0 days 13:33:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>394.130102</td>\n",
       "      <td>35.015068</td>\n",
       "      <td>0 days 09:46:12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           distance         gas            time\n",
       "carnum                                         \n",
       "1        693.396753   78.110310 0 days 12:52:05\n",
       "2        981.044936  100.654307 0 days 18:20:12\n",
       "3        456.638354   46.209141 0 days 08:14:21\n",
       "4        392.063016   44.488116 0 days 10:02:49\n",
       "5        777.309219   94.453047 0 days 19:19:18\n",
       "...             ...         ...             ...\n",
       "93       577.798733   75.810127 0 days 16:47:56\n",
       "94      1172.460418  107.900125 1 days 02:12:58\n",
       "97      1581.524745  130.386475 1 days 05:07:45\n",
       "98      1086.025420  121.220634 0 days 13:33:35\n",
       "99       394.130102   35.015068 0 days 09:46:12\n",
       "\n",
       "[64 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get totals by car\n",
    "tots = df.groupby(carnum).sum(numeric_only=False) # need numeric_only=False so the time column is included\n",
    "tots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e2e20634-6e9d-4fdd-858a-f99dd2e1c252",
   "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>carnum</th>\n",
       "      <th>distance</th>\n",
       "      <th>gas</th>\n",
       "      <th>time</th>\n",
       "      <th>mileage</th>\n",
       "      <th>speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>693.396753</td>\n",
       "      <td>78.110310</td>\n",
       "      <td>0 days 12:52:05</td>\n",
       "      <td>8.877148</td>\n",
       "      <td>53.885123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>981.044936</td>\n",
       "      <td>100.654307</td>\n",
       "      <td>0 days 18:20:12</td>\n",
       "      <td>9.746676</td>\n",
       "      <td>53.501814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>456.638354</td>\n",
       "      <td>46.209141</td>\n",
       "      <td>0 days 08:14:21</td>\n",
       "      <td>9.881992</td>\n",
       "      <td>55.422881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>392.063016</td>\n",
       "      <td>44.488116</td>\n",
       "      <td>0 days 10:02:49</td>\n",
       "      <td>8.812758</td>\n",
       "      <td>39.023110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>777.309219</td>\n",
       "      <td>94.453047</td>\n",
       "      <td>0 days 19:19:18</td>\n",
       "      <td>8.229583</td>\n",
       "      <td>40.229926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>93</td>\n",
       "      <td>577.798733</td>\n",
       "      <td>75.810127</td>\n",
       "      <td>0 days 16:47:56</td>\n",
       "      <td>7.621656</td>\n",
       "      <td>34.395057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>94</td>\n",
       "      <td>1172.460418</td>\n",
       "      <td>107.900125</td>\n",
       "      <td>1 days 02:12:58</td>\n",
       "      <td>10.866164</td>\n",
       "      <td>44.722896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>97</td>\n",
       "      <td>1581.524745</td>\n",
       "      <td>130.386475</td>\n",
       "      <td>1 days 05:07:45</td>\n",
       "      <td>12.129515</td>\n",
       "      <td>54.293512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>98</td>\n",
       "      <td>1086.025420</td>\n",
       "      <td>121.220634</td>\n",
       "      <td>0 days 13:33:35</td>\n",
       "      <td>8.959081</td>\n",
       "      <td>80.092011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>99</td>\n",
       "      <td>394.130102</td>\n",
       "      <td>35.015068</td>\n",
       "      <td>0 days 09:46:12</td>\n",
       "      <td>11.256014</td>\n",
       "      <td>40.340850</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    carnum     distance         gas            time    mileage      speed\n",
       "0        1   693.396753   78.110310 0 days 12:52:05   8.877148  53.885123\n",
       "1        2   981.044936  100.654307 0 days 18:20:12   9.746676  53.501814\n",
       "2        3   456.638354   46.209141 0 days 08:14:21   9.881992  55.422881\n",
       "3        4   392.063016   44.488116 0 days 10:02:49   8.812758  39.023110\n",
       "4        5   777.309219   94.453047 0 days 19:19:18   8.229583  40.229926\n",
       "..     ...          ...         ...             ...        ...        ...\n",
       "59      93   577.798733   75.810127 0 days 16:47:56   7.621656  34.395057\n",
       "60      94  1172.460418  107.900125 1 days 02:12:58  10.866164  44.722896\n",
       "61      97  1581.524745  130.386475 1 days 05:07:45  12.129515  54.293512\n",
       "62      98  1086.025420  121.220634 0 days 13:33:35   8.959081  80.092011\n",
       "63      99   394.130102   35.015068 0 days 09:46:12  11.256014  40.340850\n",
       "\n",
       "[64 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#compute mileage and speed\n",
    "tots[mileage] = tots.distance / tots.gas\n",
    "tots[speed] = tots.distance / tots.time.dt.total_seconds() * 3600\n",
    "tots.reset_index(inplace=True)\n",
    "tots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1cfc9b34-5902-415f-ba0c-cab4d120a738",
   "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>carnum</th>\n",
       "      <th>distance</th>\n",
       "      <th>gas</th>\n",
       "      <th>time</th>\n",
       "      <th>mileage</th>\n",
       "      <th>speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>26</td>\n",
       "      <td>554.406954</td>\n",
       "      <td>33.596612</td>\n",
       "      <td>0 days 04:06:56</td>\n",
       "      <td>16.501871</td>\n",
       "      <td>134.710113</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    carnum    distance        gas            time    mileage       speed\n",
       "16      26  554.406954  33.596612 0 days 04:06:56  16.501871  134.710113"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#show car with best mileage\n",
    "tots[tots.mileage == tots.mileage.max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "21280939-c8f8-46d0-9cdc-e4487534a9f9",
   "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>carnum</th>\n",
       "      <th>distance</th>\n",
       "      <th>gas</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>93</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>94</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>97</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>98</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>99</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    carnum  distance  gas  time\n",
       "0        1         9    9     9\n",
       "1        2        14   14    14\n",
       "2        3         5    5     5\n",
       "3        4         7    7     7\n",
       "4        5        14   14    14\n",
       "..     ...       ...  ...   ...\n",
       "59      93        10   10    10\n",
       "60      94        16   16    16\n",
       "61      97        20   20    20\n",
       "62      98        15   15    15\n",
       "63      99         6    6     6\n",
       "\n",
       "[64 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#show car with the most trips\n",
    "trips = df.groupby(carnum).count().reset_index()\n",
    "trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ed60416d-a1b3-494d-8b1d-21e0d424c15e",
   "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>carnum</th>\n",
       "      <th>distance</th>\n",
       "      <th>gas</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>49</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    carnum  distance  gas  time\n",
       "28      49        30   30    30"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trips[trips.distance == trips.distance.max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3e6b964d-a051-4167-bca0-a53ab778748c",
   "metadata": {},
   "outputs": [],
   "source": [
    "tots[[carnum,speed,mileage]].to_excel(os.path.join('files','car_results.xlsx'),index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e0c2ff2-ddb1-4bc5-bc0d-b3de22eb1c9d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}