{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(exercise-solutions-pandas2)=\n",
    "# From \"Table reshaping and joins\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last updated: 2022-04-30 03:29:03\n"
     ]
    }
   ],
   "source": [
    "!echo Last updated: `date +\"%Y-%m-%d %H:%M:%S\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [],
   "source": [
    "from myst_nb import glue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "9be78d54",
   "metadata": {
    "tags": [
     "remove-input"
    ]
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "pd.options.display.max_rows = 10\n",
    "pd.options.display.max_columns = 10\n",
    "pd.options.display.max_colwidth = 35\n",
    "plt.rcParams[\"figure.figsize\"] = (6, 6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*****"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 06-b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.006632501451200857"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# Read data\n",
    "dat = pd.read_csv(\"data/ZonAnn.Ts+dSST.csv\")\n",
    "cols = [\"90S-64S\", \"64S-44S\", \"44S-24S\", \"24S-EQU\", \"EQU-24N\", \"24N-44N\", \"44N-64N\", \"64N-90N\"]\n",
    "regions = dat[cols]\n",
    "\n",
    "# Function to calculate linear slope\n",
    "def f(x, y): \n",
    "    return np.polyfit(x, y, 1)[0]\n",
    "f(dat[\"Year\"], regions[\"44S-24S\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90S-64S    0.006063\n",
       "64S-44S    0.005141\n",
       "44S-24S    0.006633\n",
       "24S-EQU    0.006420\n",
       "EQU-24N    0.006456\n",
       "24N-44N    0.008097\n",
       "44N-64N    0.011755\n",
       "64N-90N    0.018612\n",
       "dtype: float64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate slopes per region\n",
    "regions.apply(lambda i: f(dat[\"Year\"], i), axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 06-d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>route_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "      <th>trip_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>2256</td>\n",
       "      <td>4</td>\n",
       "      <td>מסוף הטייסים-תל אביב יפו&lt;-&gt;מסוף...</td>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>677</th>\n",
       "      <td>2255</td>\n",
       "      <td>4</td>\n",
       "      <td>מסוף רידינג/רציפים-תל אביב יפו&lt;...</td>\n",
       "      <td>934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3170</th>\n",
       "      <td>11525</td>\n",
       "      <td>6</td>\n",
       "      <td>אבי עזרי א-מודיעין עילית&lt;-&gt;רמב'...</td>\n",
       "      <td>840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3171</th>\n",
       "      <td>11526</td>\n",
       "      <td>6</td>\n",
       "      <td>שדרות בית שמאי/שדרות בית הלל-מו...</td>\n",
       "      <td>830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2746</th>\n",
       "      <td>10509</td>\n",
       "      <td>1</td>\n",
       "      <td>קדושת לוי/שלום רב-ביתר עילית&lt;-&gt;...</td>\n",
       "      <td>748</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>2291</th>\n",
       "      <td>9539</td>\n",
       "      <td>24</td>\n",
       "      <td>חמת גדר-עמק הירדן&lt;-&gt;ת. מרכזית ט...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1190</th>\n",
       "      <td>4402</td>\n",
       "      <td>251</td>\n",
       "      <td>ת. מרכזית המפרץ/רציפים בינעירונ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1199</th>\n",
       "      <td>4443</td>\n",
       "      <td>271</td>\n",
       "      <td>ת. מרכזית נהריה/רציפים-נהריה&lt;-&gt;...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1204</th>\n",
       "      <td>4450</td>\n",
       "      <td>273</td>\n",
       "      <td>אוניברסיטה/רב תכליתי-חיפה&lt;-&gt;ת. ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7250</th>\n",
       "      <td>29368</td>\n",
       "      <td>21</td>\n",
       "      <td>נהריה-נהריה&lt;-&gt;תל אביב ההגנה-תל ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7251 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      route_id route_short_name                     route_long_name  trip_id\n",
       "678       2256                4  מסוף הטייסים-תל אביב יפו<->מסוף...     1001\n",
       "677       2255                4  מסוף רידינג/רציפים-תל אביב יפו<...      934\n",
       "3170     11525                6  אבי עזרי א-מודיעין עילית<->רמב'...      840\n",
       "3171     11526                6  שדרות בית שמאי/שדרות בית הלל-מו...      830\n",
       "2746     10509                1  קדושת לוי/שלום רב-ביתר עילית<->...      748\n",
       "...        ...              ...                                 ...      ...\n",
       "2291      9539               24  חמת גדר-עמק הירדן<->ת. מרכזית ט...        1\n",
       "1190      4402              251  ת. מרכזית המפרץ/רציפים בינעירונ...        1\n",
       "1199      4443              271  ת. מרכזית נהריה/רציפים-נהריה<->...        1\n",
       "1204      4450              273  אוניברסיטה/רב תכליתי-חיפה<->ת. ...        1\n",
       "7250     29368               21  נהריה-נהריה<->תל אביב ההגנה-תל ...        1\n",
       "\n",
       "[7251 rows x 4 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read\n",
    "routes = pd.read_csv(\"data/gtfs/routes.txt\")\n",
    "trips = pd.read_csv(\"data/gtfs/trips.txt\")\n",
    "\n",
    "# Join\n",
    "dat = pd.merge(trips, routes, on=\"route_id\", how=\"left\")\n",
    "\n",
    "# Calculate number of unique trips per route\n",
    "dat = dat.groupby([\"route_id\", \"route_short_name\", \"route_long_name\"]).nunique()[\"trip_id\"].reset_index()\n",
    "\n",
    "# Sort\n",
    "dat = dat.sort_values(\"trip_id\", ascending=False)\n",
    "\n",
    "# Print\n",
    "dat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "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>route_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "      <th>trip_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>2256</td>\n",
       "      <td>4</td>\n",
       "      <td>מסוף הטייסים-תל אביב יפו&lt;-&gt;מסוף...</td>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>677</th>\n",
       "      <td>2255</td>\n",
       "      <td>4</td>\n",
       "      <td>מסוף רידינג/רציפים-תל אביב יפו&lt;...</td>\n",
       "      <td>934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3170</th>\n",
       "      <td>11525</td>\n",
       "      <td>6</td>\n",
       "      <td>אבי עזרי א-מודיעין עילית&lt;-&gt;רמב'...</td>\n",
       "      <td>840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3171</th>\n",
       "      <td>11526</td>\n",
       "      <td>6</td>\n",
       "      <td>שדרות בית שמאי/שדרות בית הלל-מו...</td>\n",
       "      <td>830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2746</th>\n",
       "      <td>10509</td>\n",
       "      <td>1</td>\n",
       "      <td>קדושת לוי/שלום רב-ביתר עילית&lt;-&gt;...</td>\n",
       "      <td>748</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>2291</th>\n",
       "      <td>9539</td>\n",
       "      <td>24</td>\n",
       "      <td>חמת גדר-עמק הירדן&lt;-&gt;ת. מרכזית ט...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1190</th>\n",
       "      <td>4402</td>\n",
       "      <td>251</td>\n",
       "      <td>ת. מרכזית המפרץ/רציפים בינעירונ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1199</th>\n",
       "      <td>4443</td>\n",
       "      <td>271</td>\n",
       "      <td>ת. מרכזית נהריה/רציפים-נהריה&lt;-&gt;...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1204</th>\n",
       "      <td>4450</td>\n",
       "      <td>273</td>\n",
       "      <td>אוניברסיטה/רב תכליתי-חיפה&lt;-&gt;ת. ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7250</th>\n",
       "      <td>29368</td>\n",
       "      <td>21</td>\n",
       "      <td>נהריה-נהריה&lt;-&gt;תל אביב ההגנה-תל ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7251 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      route_id route_short_name                     route_long_name  trip_id\n",
       "678       2256                4  מסוף הטייסים-תל אביב יפו<->מסוף...     1001\n",
       "677       2255                4  מסוף רידינג/רציפים-תל אביב יפו<...      934\n",
       "3170     11525                6  אבי עזרי א-מודיעין עילית<->רמב'...      840\n",
       "3171     11526                6  שדרות בית שמאי/שדרות בית הלל-מו...      830\n",
       "2746     10509                1  קדושת לוי/שלום רב-ביתר עילית<->...      748\n",
       "...        ...              ...                                 ...      ...\n",
       "2291      9539               24  חמת גדר-עמק הירדן<->ת. מרכזית ט...        1\n",
       "1190      4402              251  ת. מרכזית המפרץ/רציפים בינעירונ...        1\n",
       "1199      4443              271  ת. מרכזית נהריה/רציפים-נהריה<->...        1\n",
       "1204      4450              273  אוניברסיטה/רב תכליתי-חיפה<->ת. ...        1\n",
       "7250     29368               21  נהריה-נהריה<->תל אביב ההגנה-תל ...        1\n",
       "\n",
       "[7251 rows x 4 columns]"
      ]
     },
     "metadata": {
      "scrapbook": {
       "mime_prefix": "",
       "name": "exercise-06-c"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "glue(\"exercise-06-c\", dat)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 06-e"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    7135\n",
       "2     112\n",
       "0       4\n",
       "Name: shape_id, dtype: int64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "trips = pd.read_csv(\"data/gtfs/trips.txt\")\n",
    "trips[[\"route_id\", \"shape_id\"]].groupby(\"route_id\").nunique().reset_index()[\"shape_id\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 06-f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read\n",
    "stops = pd.read_csv(\"data/gtfs/stops.txt\")\n",
    "stop_times = pd.read_csv(\"data/gtfs/stop_times.txt\")\n",
    "\n",
    "# Subset columns\n",
    "stops = stops[[\"stop_id\", \"stop_name\", \"stop_lon\", \"stop_lat\"]]\n",
    "stop_times = stop_times[[\"trip_id\", \"arrival_time\", \"stop_id\", \"stop_sequence\", \"shape_dist_traveled\"]]\n",
    "\n",
    "# Subset specific trip\n",
    "sel = stop_times[\"trip_id\"] == \"55745843_180421\"\n",
    "stop_times = stop_times[sel]\n",
    "\n",
    "# How many stops in the trip?\n",
    "stop_times.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'08:37:21'"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join stops to stop times\n",
    "stop_times = pd.merge(stop_times, stops)\n",
    "\n",
    "# At what time there is a stop at \"מרכז הספורט אוניברסיטת בן גוריון\"\n",
    "sel = stop_times[\"stop_name\"] == \"מרכז הספורט אוניברסיטת בן גוריון\"\n",
    "stop_times[sel][\"arrival_time\"].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12767"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total distance traveled in trip\n",
    "sel = stop_times[\"stop_sequence\"] == max(stop_times[\"stop_sequence\"])\n",
    "stop_times[sel][\"shape_dist_traveled\"].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21    מרכז רפואי סורוקה/אוניברסיטת בן...\n",
       "22            מרכז רפואי סורוקה/יצחק רגר\n",
       "Name: stop_name, dtype: object"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pair of stations closest to each other\n",
    "id = stop_times[\"shape_dist_traveled\"].diff().idxmin()\n",
    "stop_times[\"stop_name\"].iloc[[id-1, id]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                  מסוף רמות\n",
       "1    בי''ס יפה נוף/נחום שריג\n",
       "Name: stop_name, dtype: object"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pair of stations farthest from each other\n",
    "id = stop_times[\"shape_dist_traveled\"].diff().idxmax()\n",
    "stop_times[\"stop_name\"].iloc[[id-1, id]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 06-g"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Get total distance traveled per 'trip_id'\n",
    "stop_times = pd.read_csv(\"data/gtfs/stop_times.txt\")\n",
    "stop_times = stop_times[[\"trip_id\", \"shape_dist_traveled\"]].groupby(\"trip_id\").max()\n",
    "stop_times = stop_times.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "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>trip_id</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10021427_240421</td>\n",
       "      <td>49074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10021428_240421</td>\n",
       "      <td>49074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10021429_240421</td>\n",
       "      <td>49074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10021430_240421</td>\n",
       "      <td>49074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10033656_180421</td>\n",
       "      <td>2874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287659</th>\n",
       "      <td>9957863_180421</td>\n",
       "      <td>33468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287660</th>\n",
       "      <td>9967071_180421</td>\n",
       "      <td>22341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287661</th>\n",
       "      <td>9967462_180421</td>\n",
       "      <td>39061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287662</th>\n",
       "      <td>9967467_180421</td>\n",
       "      <td>39061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287663</th>\n",
       "      <td>9967472_180421</td>\n",
       "      <td>39061</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>287664 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                trip_id  shape_dist_traveled\n",
       "0       10021427_240421                49074\n",
       "1       10021428_240421                49074\n",
       "2       10021429_240421                49074\n",
       "3       10021430_240421                49074\n",
       "4       10033656_180421                 2874\n",
       "...                 ...                  ...\n",
       "287659   9957863_180421                33468\n",
       "287660   9967071_180421                22341\n",
       "287661   9967462_180421                39061\n",
       "287662   9967467_180421                39061\n",
       "287663   9967472_180421                39061\n",
       "\n",
       "[287664 rows x 2 columns]"
      ]
     },
     "metadata": {
      "scrapbook": {
       "mime_prefix": "",
       "name": "exercise-06-f1"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "glue(\"exercise-06-f1\", stop_times)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join with trips to get 'route_id' per trip\n",
    "trips = pd.read_csv(\"data/gtfs/trips.txt\")\n",
    "trips = trips[[\"trip_id\", \"route_id\"]]\n",
    "trips = pd.merge(trips, stop_times, on=\"trip_id\")\n",
    "trips = trips.groupby(\"route_id\").first()\n",
    "trips = trips.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "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>route_id</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>28876224_180421</td>\n",
       "      <td>7072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>25448344_180421</td>\n",
       "      <td>6870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>56334623_180421</td>\n",
       "      <td>9855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>56335137_180421</td>\n",
       "      <td>9751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>37173391_180421</td>\n",
       "      <td>7794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7245</th>\n",
       "      <td>29363</td>\n",
       "      <td>56622661_230421</td>\n",
       "      <td>33173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7246</th>\n",
       "      <td>29364</td>\n",
       "      <td>56460152_180421</td>\n",
       "      <td>4300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7247</th>\n",
       "      <td>29365</td>\n",
       "      <td>56458677_180421</td>\n",
       "      <td>16100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7248</th>\n",
       "      <td>29366</td>\n",
       "      <td>56458667_180421</td>\n",
       "      <td>54800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7249</th>\n",
       "      <td>29368</td>\n",
       "      <td>56458657_180421</td>\n",
       "      <td>81600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7250 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      route_id          trip_id  shape_dist_traveled\n",
       "0            1  28876224_180421                 7072\n",
       "1            2  25448344_180421                 6870\n",
       "2            3  56334623_180421                 9855\n",
       "3            5  56335137_180421                 9751\n",
       "4            7  37173391_180421                 7794\n",
       "...        ...              ...                  ...\n",
       "7245     29363  56622661_230421                33173\n",
       "7246     29364  56460152_180421                 4300\n",
       "7247     29365  56458677_180421                16100\n",
       "7248     29366  56458667_180421                54800\n",
       "7249     29368  56458657_180421                81600\n",
       "\n",
       "[7250 rows x 3 columns]"
      ]
     },
     "metadata": {
      "scrapbook": {
       "mime_prefix": "",
       "name": "exercise-06-f2"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "glue(\"exercise-06-f2\", trips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join with routes to get route short/long names\n",
    "routes = pd.read_csv(\"data/gtfs/routes.txt\")\n",
    "routes = routes[[\"route_id\", \"route_short_name\", \"route_long_name\"]]\n",
    "routes = pd.merge(routes, trips, on=\"route_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "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>route_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ת. רכבת יבנה מערב-יבנה&lt;-&gt;ת. רכב...</td>\n",
       "      <td>28876224_180421</td>\n",
       "      <td>7072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>ת. רכבת יבנה מזרח-יבנה&lt;-&gt;ת. רכב...</td>\n",
       "      <td>25448344_180421</td>\n",
       "      <td>6870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>ת. רכבת יבנה מערב-יבנה&lt;-&gt;ת. רכב...</td>\n",
       "      <td>56334623_180421</td>\n",
       "      <td>9855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>ת. רכבת יבנה מזרח-יבנה&lt;-&gt;ת. רכב...</td>\n",
       "      <td>56335137_180421</td>\n",
       "      <td>9751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>ת. רכבת יבנה מערב-יבנה&lt;-&gt;ת. רכב...</td>\n",
       "      <td>37173391_180421</td>\n",
       "      <td>7794</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>7245</th>\n",
       "      <td>29363</td>\n",
       "      <td>450</td>\n",
       "      <td>מסוף האוניברסיטה-תל אביב יפו&lt;-&gt;...</td>\n",
       "      <td>56622661_230421</td>\n",
       "      <td>33173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7246</th>\n",
       "      <td>29364</td>\n",
       "      <td>125</td>\n",
       "      <td>תל אביב ההגנה-תל אביב יפו&lt;-&gt;הרצ...</td>\n",
       "      <td>56460152_180421</td>\n",
       "      <td>4300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7247</th>\n",
       "      <td>29365</td>\n",
       "      <td>69</td>\n",
       "      <td>רחובות-רחובות&lt;-&gt;תל אביב מרכז-תל...</td>\n",
       "      <td>56458677_180421</td>\n",
       "      <td>16100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7248</th>\n",
       "      <td>29366</td>\n",
       "      <td>60</td>\n",
       "      <td>נתניה-נתניה&lt;-&gt;אשקלון-אשקלון</td>\n",
       "      <td>56458667_180421</td>\n",
       "      <td>54800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7249</th>\n",
       "      <td>29368</td>\n",
       "      <td>21</td>\n",
       "      <td>נהריה-נהריה&lt;-&gt;תל אביב ההגנה-תל ...</td>\n",
       "      <td>56458657_180421</td>\n",
       "      <td>81600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7250 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      route_id route_short_name                     route_long_name  \\\n",
       "0            1                1  ת. רכבת יבנה מערב-יבנה<->ת. רכב...   \n",
       "1            2                1  ת. רכבת יבנה מזרח-יבנה<->ת. רכב...   \n",
       "2            3                2  ת. רכבת יבנה מערב-יבנה<->ת. רכב...   \n",
       "3            5                2  ת. רכבת יבנה מזרח-יבנה<->ת. רכב...   \n",
       "4            7                3  ת. רכבת יבנה מערב-יבנה<->ת. רכב...   \n",
       "...        ...              ...                                 ...   \n",
       "7245     29363              450  מסוף האוניברסיטה-תל אביב יפו<->...   \n",
       "7246     29364              125  תל אביב ההגנה-תל אביב יפו<->הרצ...   \n",
       "7247     29365               69  רחובות-רחובות<->תל אביב מרכז-תל...   \n",
       "7248     29366               60         נתניה-נתניה<->אשקלון-אשקלון   \n",
       "7249     29368               21  נהריה-נהריה<->תל אביב ההגנה-תל ...   \n",
       "\n",
       "              trip_id  shape_dist_traveled  \n",
       "0     28876224_180421                 7072  \n",
       "1     25448344_180421                 6870  \n",
       "2     56334623_180421                 9855  \n",
       "3     56335137_180421                 9751  \n",
       "4     37173391_180421                 7794  \n",
       "...               ...                  ...  \n",
       "7245  56622661_230421                33173  \n",
       "7246  56460152_180421                 4300  \n",
       "7247  56458677_180421                16100  \n",
       "7248  56458667_180421                54800  \n",
       "7249  56458657_180421                81600  \n",
       "\n",
       "[7250 rows x 5 columns]"
      ]
     },
     "metadata": {
      "scrapbook": {
       "mime_prefix": "",
       "name": "exercise-06-f3"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "glue(\"exercise-06-f3\", routes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort accordinge to distance traveled\n",
    "routes = routes.sort_values(\"shape_dist_traveled\", ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "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>route_id</th>\n",
       "      <th>route_short_name</th>\n",
       "      <th>route_long_name</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>shape_dist_traveled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5856</th>\n",
       "      <td>19964</td>\n",
       "      <td>991</td>\n",
       "      <td>ת. מרכזית חוף הכרמל/רציפים בינע...</td>\n",
       "      <td>30526419_230421</td>\n",
       "      <td>446174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1668</th>\n",
       "      <td>7295</td>\n",
       "      <td>990</td>\n",
       "      <td>ת. מרכזית אילת/רציפים-אילת&lt;-&gt;ת....</td>\n",
       "      <td>56505849_180421</td>\n",
       "      <td>445508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6199</th>\n",
       "      <td>21726</td>\n",
       "      <td>991</td>\n",
       "      <td>ת. מרכזית אילת/רציפים-אילת&lt;-&gt;ת....</td>\n",
       "      <td>56505908_180421</td>\n",
       "      <td>445472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1669</th>\n",
       "      <td>7297</td>\n",
       "      <td>990</td>\n",
       "      <td>ת. מרכזית חוף הכרמל/רציפים בינע...</td>\n",
       "      <td>30526431_230421</td>\n",
       "      <td>443968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1671</th>\n",
       "      <td>7307</td>\n",
       "      <td>993</td>\n",
       "      <td>ת. מרכזית המפרץ/רציפים בינעירונ...</td>\n",
       "      <td>56505854_180421</td>\n",
       "      <td>432200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      route_id route_short_name                     route_long_name  \\\n",
       "5856     19964              991  ת. מרכזית חוף הכרמל/רציפים בינע...   \n",
       "1668      7295              990  ת. מרכזית אילת/רציפים-אילת<->ת....   \n",
       "6199     21726              991  ת. מרכזית אילת/רציפים-אילת<->ת....   \n",
       "1669      7297              990  ת. מרכזית חוף הכרמל/רציפים בינע...   \n",
       "1671      7307              993  ת. מרכזית המפרץ/רציפים בינעירונ...   \n",
       "\n",
       "              trip_id  shape_dist_traveled  \n",
       "5856  30526419_230421               446174  \n",
       "1668  56505849_180421               445508  \n",
       "6199  56505908_180421               445472  \n",
       "1669  30526431_230421               443968  \n",
       "1671  56505854_180421               432200  "
      ]
     },
     "metadata": {
      "scrapbook": {
       "mime_prefix": "",
       "name": "exercise-06-f4"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "glue(\"exercise-06-f4\", routes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ת. מרכזית חוף הכרמל/רציפים בינעירוני-חיפה<->ת. מרכזית אילת/הורדה-אילת-2#',\n",
       " 'ת. מרכזית אילת/רציפים-אילת<->ת. מרכזית חוף הכרמל/הורדה-חיפה-1#',\n",
       " 'ת. מרכזית אילת/רציפים-אילת<->ת. מרכזית חוף הכרמל/הורדה-חיפה-1#',\n",
       " 'ת. מרכזית חוף הכרמל/רציפים בינעירוני-חיפה<->ת. מרכזית אילת/הורדה-אילת-2#',\n",
       " 'ת. מרכזית המפרץ/רציפים בינעירוני-חיפה<->ת. מרכזית אילת/הורדה-אילת-2#']"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "routes.head()[\"route_long_name\"].to_list()"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Edit Metadata",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}