04. Pandas/tutorial/04. grouping-and-sorting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "80ba977a",
   "metadata": {
    "papermill": {
     "duration": 0.017473,
     "end_time": "2022-02-08T16:47:58.719091",
     "exception": false,
     "start_time": "2022-02-08T16:47:58.701618",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "Maps allow us to transform data in a DataFrame or Series one value at a time for an entire column. However, often we want to group our data, and then do something specific to the group the data is in. \n",
    "\n",
    "As you'll learn, we do this with the `groupby()` operation.  We'll also cover some additional topics, such as more complex ways to index your DataFrames, along with how to sort your data.\n",
    "\n",
    "**To start the exercise for this topic, please click [here](https://www.kaggle.com/kernels/fork/598715).**\n",
    "\n",
    "# Groupwise analysis\n",
    "\n",
    "One function we've been using heavily thus far is the `value_counts()` function. We can replicate what `value_counts()` does by doing the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8c6786de",
   "metadata": {
    "_kg_hide-input": true,
    "execution": {
     "iopub.execute_input": "2022-02-08T16:47:58.757143Z",
     "iopub.status.busy": "2022-02-08T16:47:58.755399Z",
     "iopub.status.idle": "2022-02-08T16:48:00.453519Z",
     "shell.execute_reply": "2022-02-08T16:48:00.452664Z"
    },
    "papermill": {
     "duration": 1.718353,
     "end_time": "2022-02-08T16:48:00.453714",
     "exception": false,
     "start_time": "2022-02-08T16:47:58.735361",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "import pandas as pd\n",
    "reviews = pd.read_csv(\"../input/wine-reviews/winemag-data-130k-v2.csv\", index_col=0)\n",
    "pd.set_option(\"display.max_rows\", 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "77453fea",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:00.494308Z",
     "iopub.status.busy": "2022-02-08T16:48:00.493680Z",
     "iopub.status.idle": "2022-02-08T16:48:00.505195Z",
     "shell.execute_reply": "2022-02-08T16:48:00.505693Z"
    },
    "papermill": {
     "duration": 0.035616,
     "end_time": "2022-02-08T16:48:00.505882",
     "exception": false,
     "start_time": "2022-02-08T16:48:00.470266",
     "status": "completed"
    },
    "scrolled": false,
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "points\n",
       "80     397\n",
       "81     692\n",
       "      ... \n",
       "99      33\n",
       "100     19\n",
       "Name: points, Length: 21, dtype: int64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.groupby('points').points.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce0fd17c",
   "metadata": {
    "papermill": {
     "duration": 0.018592,
     "end_time": "2022-02-08T16:48:00.542218",
     "exception": false,
     "start_time": "2022-02-08T16:48:00.523626",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "`groupby()` created a group of reviews which allotted the same point values to the given wines. Then, for each of these groups, we grabbed the `points()` column and counted how many times it appeared.  `value_counts()` is just a shortcut to this `groupby()` operation. \n",
    "\n",
    "We can use any of the summary functions we've used before with this data. For example, to get the cheapest wine in each point value category, we can do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "88a4c666",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:00.579727Z",
     "iopub.status.busy": "2022-02-08T16:48:00.579039Z",
     "iopub.status.idle": "2022-02-08T16:48:00.591931Z",
     "shell.execute_reply": "2022-02-08T16:48:00.592530Z"
    },
    "papermill": {
     "duration": 0.033529,
     "end_time": "2022-02-08T16:48:00.592752",
     "exception": false,
     "start_time": "2022-02-08T16:48:00.559223",
     "status": "completed"
    },
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "points\n",
       "80      5.0\n",
       "81      5.0\n",
       "       ... \n",
       "99     44.0\n",
       "100    80.0\n",
       "Name: price, Length: 21, dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.groupby('points').price.min()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbc96f52",
   "metadata": {
    "papermill": {
     "duration": 0.017098,
     "end_time": "2022-02-08T16:48:00.627093",
     "exception": false,
     "start_time": "2022-02-08T16:48:00.609995",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "You can think of each group we generate as being a slice of our DataFrame containing only data with values that match. This DataFrame is accessible to us directly using the `apply()` method, and we can then manipulate the data in any way we see fit. For example, here's one way of selecting the name of the first wine reviewed from each winery in the dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0e1fa4b2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:00.664744Z",
     "iopub.status.busy": "2022-02-08T16:48:00.664108Z",
     "iopub.status.idle": "2022-02-08T16:48:01.688898Z",
     "shell.execute_reply": "2022-02-08T16:48:01.689441Z"
    },
    "papermill": {
     "duration": 1.04532,
     "end_time": "2022-02-08T16:48:01.689670",
     "exception": false,
     "start_time": "2022-02-08T16:48:00.644350",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "winery\n",
       "1+1=3                          1+1=3 NV Rosé Sparkling (Cava)\n",
       "10 Knots                 10 Knots 2010 Viognier (Paso Robles)\n",
       "                                  ...                        \n",
       "àMaurice    àMaurice 2013 Fred Estate Syrah (Walla Walla V...\n",
       "Štoka                         Štoka 2009 Izbrani Teran (Kras)\n",
       "Length: 16757, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.groupby('winery').apply(lambda df: df.title.iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "050f14ab",
   "metadata": {
    "papermill": {
     "duration": 0.017417,
     "end_time": "2022-02-08T16:48:01.724862",
     "exception": false,
     "start_time": "2022-02-08T16:48:01.707445",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "For even more fine-grained control, you can also group by more than one column. For an example, here's how we would pick out the best wine by country _and_ province:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "65a99f2c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:01.763899Z",
     "iopub.status.busy": "2022-02-08T16:48:01.763235Z",
     "iopub.status.idle": "2022-02-08T16:48:02.074053Z",
     "shell.execute_reply": "2022-02-08T16:48:02.073311Z"
    },
    "papermill": {
     "duration": 0.331435,
     "end_time": "2022-02-08T16:48:02.074227",
     "exception": false,
     "start_time": "2022-02-08T16:48:01.742792",
     "status": "completed"
    },
    "scrolled": false,
    "tags": []
   },
   "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></th>\n",
       "      <th>country</th>\n",
       "      <th>description</th>\n",
       "      <th>designation</th>\n",
       "      <th>points</th>\n",
       "      <th>price</th>\n",
       "      <th>province</th>\n",
       "      <th>region_1</th>\n",
       "      <th>region_2</th>\n",
       "      <th>taster_name</th>\n",
       "      <th>taster_twitter_handle</th>\n",
       "      <th>title</th>\n",
       "      <th>variety</th>\n",
       "      <th>winery</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>province</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Argentina</th>\n",
       "      <th>Mendoza Province</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>If the color doesn't tell the full story, the ...</td>\n",
       "      <td>Nicasia Vineyard</td>\n",
       "      <td>97</td>\n",
       "      <td>120.0</td>\n",
       "      <td>Mendoza Province</td>\n",
       "      <td>Mendoza</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Michael Schachner</td>\n",
       "      <td>@wineschach</td>\n",
       "      <td>Bodega Catena Zapata 2006 Nicasia Vineyard Mal...</td>\n",
       "      <td>Malbec</td>\n",
       "      <td>Bodega Catena Zapata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>Take note, this could be the best wine Colomé ...</td>\n",
       "      <td>Reserva</td>\n",
       "      <td>95</td>\n",
       "      <td>90.0</td>\n",
       "      <td>Other</td>\n",
       "      <td>Salta</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Michael Schachner</td>\n",
       "      <td>@wineschach</td>\n",
       "      <td>Colomé 2010 Reserva Malbec (Salta)</td>\n",
       "      <td>Malbec</td>\n",
       "      <td>Colomé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Uruguay</th>\n",
       "      <th>San Jose</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Baked, sweet, heavy aromas turn earthy with ti...</td>\n",
       "      <td>El Preciado Gran Reserva</td>\n",
       "      <td>87</td>\n",
       "      <td>50.0</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Michael Schachner</td>\n",
       "      <td>@wineschach</td>\n",
       "      <td>Castillo Viejo 2005 El Preciado Gran Reserva R...</td>\n",
       "      <td>Red Blend</td>\n",
       "      <td>Castillo Viejo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uruguay</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Cherry and berry aromas are ripe, healthy and ...</td>\n",
       "      <td>Blend 002 Limited Edition</td>\n",
       "      <td>91</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Michael Schachner</td>\n",
       "      <td>@wineschach</td>\n",
       "      <td>Narbona NV Blend 002 Limited Edition Tannat-Ca...</td>\n",
       "      <td>Tannat-Cabernet Franc</td>\n",
       "      <td>Narbona</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              country  \\\n",
       "country   province                      \n",
       "Argentina Mendoza Province  Argentina   \n",
       "          Other             Argentina   \n",
       "...                               ...   \n",
       "Uruguay   San Jose            Uruguay   \n",
       "          Uruguay             Uruguay   \n",
       "\n",
       "                                                                  description  \\\n",
       "country   province                                                              \n",
       "Argentina Mendoza Province  If the color doesn't tell the full story, the ...   \n",
       "          Other             Take note, this could be the best wine Colomé ...   \n",
       "...                                                                       ...   \n",
       "Uruguay   San Jose          Baked, sweet, heavy aromas turn earthy with ti...   \n",
       "          Uruguay           Cherry and berry aromas are ripe, healthy and ...   \n",
       "\n",
       "                                          designation  points  price  \\\n",
       "country   province                                                     \n",
       "Argentina Mendoza Province           Nicasia Vineyard      97  120.0   \n",
       "          Other                               Reserva      95   90.0   \n",
       "...                                               ...     ...    ...   \n",
       "Uruguay   San Jose           El Preciado Gran Reserva      87   50.0   \n",
       "          Uruguay           Blend 002 Limited Edition      91   22.0   \n",
       "\n",
       "                                    province region_1 region_2  \\\n",
       "country   province                                               \n",
       "Argentina Mendoza Province  Mendoza Province  Mendoza      NaN   \n",
       "          Other                        Other    Salta      NaN   \n",
       "...                                      ...      ...      ...   \n",
       "Uruguay   San Jose                  San Jose      NaN      NaN   \n",
       "          Uruguay                    Uruguay      NaN      NaN   \n",
       "\n",
       "                                  taster_name taster_twitter_handle  \\\n",
       "country   province                                                    \n",
       "Argentina Mendoza Province  Michael Schachner           @wineschach   \n",
       "          Other             Michael Schachner           @wineschach   \n",
       "...                                       ...                   ...   \n",
       "Uruguay   San Jose          Michael Schachner           @wineschach   \n",
       "          Uruguay           Michael Schachner           @wineschach   \n",
       "\n",
       "                                                                        title  \\\n",
       "country   province                                                              \n",
       "Argentina Mendoza Province  Bodega Catena Zapata 2006 Nicasia Vineyard Mal...   \n",
       "          Other                            Colomé 2010 Reserva Malbec (Salta)   \n",
       "...                                                                       ...   \n",
       "Uruguay   San Jose          Castillo Viejo 2005 El Preciado Gran Reserva R...   \n",
       "          Uruguay           Narbona NV Blend 002 Limited Edition Tannat-Ca...   \n",
       "\n",
       "                                          variety                winery  \n",
       "country   province                                                       \n",
       "Argentina Mendoza Province                 Malbec  Bodega Catena Zapata  \n",
       "          Other                            Malbec                Colomé  \n",
       "...                                           ...                   ...  \n",
       "Uruguay   San Jose                      Red Blend        Castillo Viejo  \n",
       "          Uruguay           Tannat-Cabernet Franc               Narbona  \n",
       "\n",
       "[425 rows x 13 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.groupby(['country', 'province']).apply(lambda df: df.loc[df.points.idxmax()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5ff5b95",
   "metadata": {
    "papermill": {
     "duration": 0.018392,
     "end_time": "2022-02-08T16:48:02.111998",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.093606",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Another `groupby()` method worth mentioning is `agg()`, which lets you run a bunch of different functions on your DataFrame simultaneously. For example, we can generate a simple statistical summary of the dataset as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0a243d3e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.171232Z",
     "iopub.status.busy": "2022-02-08T16:48:02.152927Z",
     "iopub.status.idle": "2022-02-08T16:48:02.190432Z",
     "shell.execute_reply": "2022-02-08T16:48:02.189901Z"
    },
    "papermill": {
     "duration": 0.059203,
     "end_time": "2022-02-08T16:48:02.190588",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.131385",
     "status": "completed"
    },
    "tags": []
   },
   "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>len</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Argentina</th>\n",
       "      <td>3800</td>\n",
       "      <td>4.0</td>\n",
       "      <td>230.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Armenia</th>\n",
       "      <td>2</td>\n",
       "      <td>14.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ukraine</th>\n",
       "      <td>14</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uruguay</th>\n",
       "      <td>109</td>\n",
       "      <td>10.0</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>43 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            len   min    max\n",
       "country                     \n",
       "Argentina  3800   4.0  230.0\n",
       "Armenia       2  14.0   15.0\n",
       "...         ...   ...    ...\n",
       "Ukraine      14   6.0   13.0\n",
       "Uruguay     109  10.0  130.0\n",
       "\n",
       "[43 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.groupby(['country']).price.agg([len, min, max])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "181163a3",
   "metadata": {
    "papermill": {
     "duration": 0.019255,
     "end_time": "2022-02-08T16:48:02.229544",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.210289",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Effective use of `groupby()` will allow you to do lots of really powerful things with your dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f30aaed",
   "metadata": {
    "papermill": {
     "duration": 0.019408,
     "end_time": "2022-02-08T16:48:02.268267",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.248859",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Multi-indexes\n",
    "\n",
    "In all of the examples we've seen thus far we've been working with DataFrame or Series objects with a single-label index. `groupby()` is slightly different in the fact that, depending on the operation we run, it will sometimes result in what is called a multi-index.\n",
    "\n",
    "A multi-index differs from a regular index in that it has multiple levels. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6c7d4c17",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.316538Z",
     "iopub.status.busy": "2022-02-08T16:48:02.315572Z",
     "iopub.status.idle": "2022-02-08T16:48:02.383232Z",
     "shell.execute_reply": "2022-02-08T16:48:02.382660Z"
    },
    "papermill": {
     "duration": 0.095908,
     "end_time": "2022-02-08T16:48:02.383393",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.287485",
     "status": "completed"
    },
    "scrolled": true,
    "tags": []
   },
   "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></th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>province</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Argentina</th>\n",
       "      <th>Mendoza Province</th>\n",
       "      <td>3264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other</th>\n",
       "      <td>536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Uruguay</th>\n",
       "      <th>San Jose</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uruguay</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             len\n",
       "country   province              \n",
       "Argentina Mendoza Province  3264\n",
       "          Other              536\n",
       "...                          ...\n",
       "Uruguay   San Jose             3\n",
       "          Uruguay             24\n",
       "\n",
       "[425 rows x 1 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_reviewed = reviews.groupby(['country', 'province']).description.agg([len])\n",
    "countries_reviewed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "052eeae0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.430341Z",
     "iopub.status.busy": "2022-02-08T16:48:02.429333Z",
     "iopub.status.idle": "2022-02-08T16:48:02.435107Z",
     "shell.execute_reply": "2022-02-08T16:48:02.435742Z"
    },
    "papermill": {
     "duration": 0.030722,
     "end_time": "2022-02-08T16:48:02.435934",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.405212",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.indexes.multi.MultiIndex"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mi = countries_reviewed.index\n",
    "type(mi)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edfdc583",
   "metadata": {
    "papermill": {
     "duration": 0.019942,
     "end_time": "2022-02-08T16:48:02.476364",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.456422",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Multi-indices have several methods for dealing with their tiered structure which are absent for single-level indices. They also require two levels of labels to retrieve a value. Dealing with multi-index output is a common \"gotcha\" for users new to pandas.\n",
    "\n",
    "The use cases for a multi-index are detailed alongside instructions on using them in the [MultiIndex / Advanced Selection](https://pandas.pydata.org/pandas-docs/stable/advanced.html) section of the pandas documentation.\n",
    "\n",
    "However, in general the multi-index method you will use most often is the one for converting back to a regular index, the `reset_index()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "590acace",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.523258Z",
     "iopub.status.busy": "2022-02-08T16:48:02.522323Z",
     "iopub.status.idle": "2022-02-08T16:48:02.535765Z",
     "shell.execute_reply": "2022-02-08T16:48:02.536384Z"
    },
    "papermill": {
     "duration": 0.038016,
     "end_time": "2022-02-08T16:48:02.536577",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.498561",
     "status": "completed"
    },
    "tags": []
   },
   "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>country</th>\n",
       "      <th>province</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>Mendoza Province</td>\n",
       "      <td>3264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>Other</td>\n",
       "      <td>536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country          province   len\n",
       "0    Argentina  Mendoza Province  3264\n",
       "1    Argentina             Other   536\n",
       "..         ...               ...   ...\n",
       "423    Uruguay          San Jose     3\n",
       "424    Uruguay           Uruguay    24\n",
       "\n",
       "[425 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_reviewed.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e71c46a",
   "metadata": {
    "papermill": {
     "duration": 0.020665,
     "end_time": "2022-02-08T16:48:02.578361",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.557696",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Sorting\n",
    "\n",
    "Looking again at `countries_reviewed` we can see that grouping returns data in index order, not in value order. That is to say, when outputting the result of a `groupby`, the order of the rows is dependent on the values in the index, not in the data.\n",
    "\n",
    "To get data in the order want it in we can sort it ourselves.  The `sort_values()` method is handy for this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3eaaf488",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.624214Z",
     "iopub.status.busy": "2022-02-08T16:48:02.623252Z",
     "iopub.status.idle": "2022-02-08T16:48:02.637413Z",
     "shell.execute_reply": "2022-02-08T16:48:02.638075Z"
    },
    "papermill": {
     "duration": 0.039049,
     "end_time": "2022-02-08T16:48:02.638285",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.599236",
     "status": "completed"
    },
    "tags": []
   },
   "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>country</th>\n",
       "      <th>province</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Greece</td>\n",
       "      <td>Muscat of Kefallonian</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>Greece</td>\n",
       "      <td>Sterea Ellada</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>415</th>\n",
       "      <td>US</td>\n",
       "      <td>Washington</td>\n",
       "      <td>8639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>US</td>\n",
       "      <td>California</td>\n",
       "      <td>36247</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country               province    len\n",
       "179  Greece  Muscat of Kefallonian      1\n",
       "192  Greece          Sterea Ellada      1\n",
       "..      ...                    ...    ...\n",
       "415      US             Washington   8639\n",
       "392      US             California  36247\n",
       "\n",
       "[425 rows x 3 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_reviewed = countries_reviewed.reset_index()\n",
    "countries_reviewed.sort_values(by='len')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9917f7e5",
   "metadata": {
    "papermill": {
     "duration": 0.021387,
     "end_time": "2022-02-08T16:48:02.681391",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.660004",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "`sort_values()` defaults to an ascending sort, where the lowest values go first. However, most of the time we want a descending sort, where the higher numbers go first. That goes thusly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "44b5b75d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.728363Z",
     "iopub.status.busy": "2022-02-08T16:48:02.727428Z",
     "iopub.status.idle": "2022-02-08T16:48:02.739979Z",
     "shell.execute_reply": "2022-02-08T16:48:02.740554Z"
    },
    "papermill": {
     "duration": 0.037747,
     "end_time": "2022-02-08T16:48:02.740767",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.703020",
     "status": "completed"
    },
    "tags": []
   },
   "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>country</th>\n",
       "      <th>province</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>US</td>\n",
       "      <td>California</td>\n",
       "      <td>36247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>415</th>\n",
       "      <td>US</td>\n",
       "      <td>Washington</td>\n",
       "      <td>8639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Chile</td>\n",
       "      <td>Coelemu</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Greece</td>\n",
       "      <td>Beotia</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country    province    len\n",
       "392      US  California  36247\n",
       "415      US  Washington   8639\n",
       "..      ...         ...    ...\n",
       "63    Chile     Coelemu      1\n",
       "149  Greece      Beotia      1\n",
       "\n",
       "[425 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_reviewed.sort_values(by='len', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa951626",
   "metadata": {
    "papermill": {
     "duration": 0.022512,
     "end_time": "2022-02-08T16:48:02.785348",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.762836",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "To sort by index values, use the companion method `sort_index()`. This method has the same arguments and default order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a4022421",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.834626Z",
     "iopub.status.busy": "2022-02-08T16:48:02.833666Z",
     "iopub.status.idle": "2022-02-08T16:48:02.846562Z",
     "shell.execute_reply": "2022-02-08T16:48:02.847110Z"
    },
    "papermill": {
     "duration": 0.039553,
     "end_time": "2022-02-08T16:48:02.847318",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.807765",
     "status": "completed"
    },
    "tags": []
   },
   "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>country</th>\n",
       "      <th>province</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>Mendoza Province</td>\n",
       "      <td>3264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>Other</td>\n",
       "      <td>536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country          province   len\n",
       "0    Argentina  Mendoza Province  3264\n",
       "1    Argentina             Other   536\n",
       "..         ...               ...   ...\n",
       "423    Uruguay          San Jose     3\n",
       "424    Uruguay           Uruguay    24\n",
       "\n",
       "[425 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_reviewed.sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0b702a0",
   "metadata": {
    "papermill": {
     "duration": 0.022819,
     "end_time": "2022-02-08T16:48:02.893338",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.870519",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Finally, know that you can sort by more than one column at a time:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b00f8f9b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-08T16:48:02.943267Z",
     "iopub.status.busy": "2022-02-08T16:48:02.942577Z",
     "iopub.status.idle": "2022-02-08T16:48:02.956545Z",
     "shell.execute_reply": "2022-02-08T16:48:02.957010Z"
    },
    "papermill": {
     "duration": 0.040975,
     "end_time": "2022-02-08T16:48:02.957210",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.916235",
     "status": "completed"
    },
    "tags": []
   },
   "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>country</th>\n",
       "      <th>province</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>Other</td>\n",
       "      <td>536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>Mendoza Province</td>\n",
       "      <td>3264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>Canelones</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>425 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country          province   len\n",
       "1    Argentina             Other   536\n",
       "0    Argentina  Mendoza Province  3264\n",
       "..         ...               ...   ...\n",
       "424    Uruguay           Uruguay    24\n",
       "419    Uruguay         Canelones    43\n",
       "\n",
       "[425 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_reviewed.sort_values(by=['country', 'len'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f8e9e2b",
   "metadata": {
    "papermill": {
     "duration": 0.02312,
     "end_time": "2022-02-08T16:48:03.003878",
     "exception": false,
     "start_time": "2022-02-08T16:48:02.980758",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Your turn\n",
    "\n",
    "If you haven't started the exercise, you can **[get started here](https://www.kaggle.com/kernels/fork/598715)**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d00249a4",
   "metadata": {
    "papermill": {
     "duration": 0.022894,
     "end_time": "2022-02-08T16:48:03.050683",
     "exception": false,
     "start_time": "2022-02-08T16:48:03.027789",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/pandas/discussion) to chat with other learners.*"
   ]
  }
 ],
 "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.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 15.514261,
   "end_time": "2022-02-08T16:48:03.886622",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2022-02-08T16:47:48.372361",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}