longest-charting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "76813616-8d09-4709-8008-71896f8189a6",
   "metadata": {},
   "source": [
    "### Longest-charting film on Netflix's Top 10 list\n",
    "\n",
    "This Jupyter notebook sorts and filters through [Netflix's global Top 10 list](https://www.netflix.com/tudum/top10/), which covers exactly three years from June 28, 2021 (when the data was first collected) to June 28, 2024 (when the data was downloaded)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "236115b1-c1c0-4559-8931-e7a3790499c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "f75a3c33-b82e-4f1e-87f6-e33b58cda588",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "week                           object\n",
       "category                       object\n",
       "weekly_rank                     int64\n",
       "show_title                     object\n",
       "season_title                   object\n",
       "weekly_hours_viewed             int64\n",
       "runtime                       float64\n",
       "weekly_views                  float64\n",
       "cumulative_weeks_in_top_10      int64\n",
       "is_staggered_launch              bool\n",
       "episode_launch_details         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# imports tsv file, and identifies tab as separator\n",
    "df = pd.read_csv(\"all-weeks-global.tsv\", sep='\\t')\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "ecbe5f9f-7698-46cd-8857-6b7a61b0eee7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category\n",
       "Films (English)        677\n",
       "Films (Non-English)    637\n",
       "TV (English)           370\n",
       "TV (Non-English)       362\n",
       "Name: show_title, dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sorts according to `category` and counts unique `show titles`\n",
    "df.groupby('category')['show_title'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "bf735f42-f942-4dfd-84f6-042a2df79bf8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# excludes non-films by using conditionals\n",
    "filtered_df = df[(df['category'] != 'TV (English)') & (df['category'] != 'TV (Non-English)')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "6c42c00b-94fd-4782-95e4-df7074de8390",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category\n",
       "Films (English)        677\n",
       "Films (Non-English)    637\n",
       "Name: show_title, dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirms if the exclusions worked\n",
    "filtered_df.groupby('category')['show_title'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "d4d49af2-f87a-4870-88be-3f800340ef1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "show_title\n",
       "All Quiet on the Western Front    23\n",
       "Through My Window                 18\n",
       "RRR (Hindi)                       18\n",
       "Blood Red Sky                     16\n",
       "The Super Mario Bros. Movie       15\n",
       "Nowhere                           15\n",
       "Red Notice                        14\n",
       "The Boss Baby                     14\n",
       "Despicable Me 2                   13\n",
       "Sing 2                            12\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# counts values = shows how many weeks a films charted in the top 10\n",
    "filtered_df['show_title'].value_counts().nlargest(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "0dee41ad-e469-44d6-8ccb-b370270f0488",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category             show_title                    \n",
       "Films (Non-English)  All Quiet on the Western Front    23\n",
       "                     RRR (Hindi)                       18\n",
       "                     Through My Window                 18\n",
       "                     Blood Red Sky                     16\n",
       "Films (English)      The Super Mario Bros. Movie       15\n",
       "Films (Non-English)  Nowhere                           15\n",
       "Films (English)      Red Notice                        14\n",
       "                     The Boss Baby                     14\n",
       "                     Despicable Me 2                   13\n",
       "                     Sing 2                            12\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# but I want them filtered between English and non-English films, so...\n",
    "\n",
    "# this gives me the top 10 overall\n",
    "filtered_df.groupby('category')['show_title'].value_counts().nlargest(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "ec336a55-76dd-433b-8c7a-1ae91a98da2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save to csv for DataWrapper\n",
    "filtered_df.groupby('category')['show_title'].value_counts().nlargest(10).to_csv('top-10-overall.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "118f97e0-2e0a-4fd5-a6f0-b4579f157da9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# this gives me the top 10 per category (English or Non-English films)\n",
    "dataviz = filtered_df.groupby('category')['show_title'].value_counts().groupby(level=0, group_keys=False).nlargest(10)\n",
    "\n",
    "# save to csv for DataWrapper\n",
    "dataviz.to_csv('top-10-categorized.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "95ca2654-8d2d-4f6e-81ad-390d3e101ce8",
   "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>week</th>\n",
       "      <th>category</th>\n",
       "      <th>weekly_rank</th>\n",
       "      <th>show_title</th>\n",
       "      <th>season_title</th>\n",
       "      <th>weekly_hours_viewed</th>\n",
       "      <th>runtime</th>\n",
       "      <th>weekly_views</th>\n",
       "      <th>cumulative_weeks_in_top_10</th>\n",
       "      <th>is_staggered_launch</th>\n",
       "      <th>episode_launch_details</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2338</th>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>9</td>\n",
       "      <td>All Quiet on the Western Front</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1280000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2459</th>\n",
       "      <td>2023-04-23</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>10</td>\n",
       "      <td>All Quiet on the Western Front</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1340000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2498</th>\n",
       "      <td>2023-04-16</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>9</td>\n",
       "      <td>All Quiet on the Western Front</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1660000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2536</th>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>7</td>\n",
       "      <td>All Quiet on the Western Front</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2310000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2575</th>\n",
       "      <td>2023-04-02</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>6</td>\n",
       "      <td>All Quiet on the Western Front</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3420000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2656</th>\n",
       "      <td>2023-03-19</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>7</td>\n",
       "      <td>RRR (Hindi)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2670000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>2024-03-03</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>5</td>\n",
       "      <td>Through My Window</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4400000</td>\n",
       "      <td>1.9</td>\n",
       "      <td>2300000.0</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2614</th>\n",
       "      <td>2023-03-26</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>5</td>\n",
       "      <td>All Quiet on the Western Front</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6850000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2975</th>\n",
       "      <td>2023-01-22</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>6</td>\n",
       "      <td>RRR (Hindi)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2200000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>698</th>\n",
       "      <td>2024-02-25</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>9</td>\n",
       "      <td>Through My Window</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3600000</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1900000.0</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            week             category  weekly_rank  \\\n",
       "2338  2023-05-14  Films (Non-English)            9   \n",
       "2459  2023-04-23  Films (Non-English)           10   \n",
       "2498  2023-04-16  Films (Non-English)            9   \n",
       "2536  2023-04-09  Films (Non-English)            7   \n",
       "2575  2023-04-02  Films (Non-English)            6   \n",
       "2656  2023-03-19  Films (Non-English)            7   \n",
       "654   2024-03-03  Films (Non-English)            5   \n",
       "2614  2023-03-26  Films (Non-English)            5   \n",
       "2975  2023-01-22  Films (Non-English)            6   \n",
       "698   2024-02-25  Films (Non-English)            9   \n",
       "\n",
       "                          show_title season_title  weekly_hours_viewed  \\\n",
       "2338  All Quiet on the Western Front          NaN              1280000   \n",
       "2459  All Quiet on the Western Front          NaN              1340000   \n",
       "2498  All Quiet on the Western Front          NaN              1660000   \n",
       "2536  All Quiet on the Western Front          NaN              2310000   \n",
       "2575  All Quiet on the Western Front          NaN              3420000   \n",
       "2656                     RRR (Hindi)          NaN              2670000   \n",
       "654                Through My Window          NaN              4400000   \n",
       "2614  All Quiet on the Western Front          NaN              6850000   \n",
       "2975                     RRR (Hindi)          NaN              2200000   \n",
       "698                Through My Window          NaN              3600000   \n",
       "\n",
       "      runtime  weekly_views  cumulative_weeks_in_top_10  is_staggered_launch  \\\n",
       "2338      NaN           NaN                          23                False   \n",
       "2459      NaN           NaN                          22                False   \n",
       "2498      NaN           NaN                          21                False   \n",
       "2536      NaN           NaN                          20                False   \n",
       "2575      NaN           NaN                          19                False   \n",
       "2656      NaN           NaN                          18                False   \n",
       "654       1.9     2300000.0                          18                False   \n",
       "2614      NaN           NaN                          18                False   \n",
       "2975      NaN           NaN                          17                False   \n",
       "698       1.9     1900000.0                          17                False   \n",
       "\n",
       "     episode_launch_details  \n",
       "2338                    NaN  \n",
       "2459                    NaN  \n",
       "2498                    NaN  \n",
       "2536                    NaN  \n",
       "2575                    NaN  \n",
       "2656                    NaN  \n",
       "654                     NaN  \n",
       "2614                    NaN  \n",
       "2975                    NaN  \n",
       "698                     NaN  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# then, I realized that there's a column called `cumulative_weeks_in_top_10`\n",
    "# but it `sort_values` returns literally a countdown\n",
    "\n",
    "filtered_df.sort_values('cumulative_weeks_in_top_10', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "ff7133ac-7862-41ca-ae27-2c2e3d23a09d",
   "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>week</th>\n",
       "      <th>category</th>\n",
       "      <th>weekly_rank</th>\n",
       "      <th>show_title</th>\n",
       "      <th>season_title</th>\n",
       "      <th>weekly_hours_viewed</th>\n",
       "      <th>runtime</th>\n",
       "      <th>weekly_views</th>\n",
       "      <th>cumulative_weeks_in_top_10</th>\n",
       "      <th>is_staggered_launch</th>\n",
       "      <th>episode_launch_details</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2338</th>\n",
       "      <td>2023-05-14</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>9</td>\n",
       "      <td>All Quiet on the Western Front</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1280000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2656</th>\n",
       "      <td>2023-03-19</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>7</td>\n",
       "      <td>RRR (Hindi)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2670000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>2024-03-03</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>5</td>\n",
       "      <td>Through My Window</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4400000</td>\n",
       "      <td>1.9000</td>\n",
       "      <td>2300000.0</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3498</th>\n",
       "      <td>2022-10-23</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>9</td>\n",
       "      <td>Blood Red Sky</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1510000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>2024-04-21</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>10</td>\n",
       "      <td>The Super Mario Bros. Movie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3800000</td>\n",
       "      <td>1.5333</td>\n",
       "      <td>2500000.0</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>819</th>\n",
       "      <td>2024-02-04</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>10</td>\n",
       "      <td>Nowhere</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1300000</td>\n",
       "      <td>1.8167</td>\n",
       "      <td>700000.0</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3969</th>\n",
       "      <td>2022-07-31</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>10</td>\n",
       "      <td>Red Notice</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4090000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>2024-05-19</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>8</td>\n",
       "      <td>The Boss Baby</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4100000</td>\n",
       "      <td>1.6333</td>\n",
       "      <td>2500000.0</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>726</th>\n",
       "      <td>2024-02-18</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>7</td>\n",
       "      <td>Despicable Me 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000000</td>\n",
       "      <td>1.6333</td>\n",
       "      <td>6100000.0</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2024-06-09</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>7</td>\n",
       "      <td>Sing 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6200000</td>\n",
       "      <td>1.8333</td>\n",
       "      <td>3400000.0</td>\n",
       "      <td>12</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            week             category  weekly_rank  \\\n",
       "2338  2023-05-14  Films (Non-English)            9   \n",
       "2656  2023-03-19  Films (Non-English)            7   \n",
       "654   2024-03-03  Films (Non-English)            5   \n",
       "3498  2022-10-23  Films (Non-English)            9   \n",
       "369   2024-04-21      Films (English)           10   \n",
       "819   2024-02-04  Films (Non-English)           10   \n",
       "3969  2022-07-31      Films (English)           10   \n",
       "207   2024-05-19      Films (English)            8   \n",
       "726   2024-02-18      Films (English)            7   \n",
       "86    2024-06-09      Films (English)            7   \n",
       "\n",
       "                          show_title season_title  weekly_hours_viewed  \\\n",
       "2338  All Quiet on the Western Front          NaN              1280000   \n",
       "2656                     RRR (Hindi)          NaN              2670000   \n",
       "654                Through My Window          NaN              4400000   \n",
       "3498                   Blood Red Sky          NaN              1510000   \n",
       "369      The Super Mario Bros. Movie          NaN              3800000   \n",
       "819                          Nowhere          NaN              1300000   \n",
       "3969                      Red Notice          NaN              4090000   \n",
       "207                    The Boss Baby          NaN              4100000   \n",
       "726                  Despicable Me 2          NaN             10000000   \n",
       "86                            Sing 2          NaN              6200000   \n",
       "\n",
       "      runtime  weekly_views  cumulative_weeks_in_top_10  is_staggered_launch  \\\n",
       "2338      NaN           NaN                          23                False   \n",
       "2656      NaN           NaN                          18                False   \n",
       "654    1.9000     2300000.0                          18                False   \n",
       "3498      NaN           NaN                          16                False   \n",
       "369    1.5333     2500000.0                          15                False   \n",
       "819    1.8167      700000.0                          15                False   \n",
       "3969      NaN           NaN                          14                False   \n",
       "207    1.6333     2500000.0                          14                False   \n",
       "726    1.6333     6100000.0                          13                False   \n",
       "86     1.8333     3400000.0                          12                False   \n",
       "\n",
       "     episode_launch_details  \n",
       "2338                    NaN  \n",
       "2656                    NaN  \n",
       "654                     NaN  \n",
       "3498                    NaN  \n",
       "369                     NaN  \n",
       "819                     NaN  \n",
       "3969                    NaN  \n",
       "207                     NaN  \n",
       "726                     NaN  \n",
       "86                      NaN  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this shows my same results, dropping duplicates to keep only the first item (which was sorted already so it's the highest value)\n",
    "filtered_df.sort_values('cumulative_weeks_in_top_10', ascending=False).drop_duplicates(subset='show_title', keep='first').head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "47900e96-b2e9-458c-b166-a2747afe9176",
   "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>week</th>\n",
       "      <th>category</th>\n",
       "      <th>weekly_rank</th>\n",
       "      <th>show_title</th>\n",
       "      <th>season_title</th>\n",
       "      <th>weekly_hours_viewed</th>\n",
       "      <th>runtime</th>\n",
       "      <th>weekly_views</th>\n",
       "      <th>cumulative_weeks_in_top_10</th>\n",
       "      <th>is_staggered_launch</th>\n",
       "      <th>episode_launch_details</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4640</th>\n",
       "      <td>2022-04-03</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>1</td>\n",
       "      <td>The Adam Project</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17720000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4250</th>\n",
       "      <td>2022-06-12</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>1</td>\n",
       "      <td>RRR (Hindi)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5230000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2200</th>\n",
       "      <td>2023-06-04</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>1</td>\n",
       "      <td>The Mother</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16480000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4290</th>\n",
       "      <td>2022-06-05</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>1</td>\n",
       "      <td>RRR (Hindi)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13940000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>840</th>\n",
       "      <td>2024-01-28</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>1</td>\n",
       "      <td>Lift</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31000000</td>\n",
       "      <td>1.7833</td>\n",
       "      <td>17400000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4880</th>\n",
       "      <td>2022-02-20</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>1</td>\n",
       "      <td>The Tinder Swindler</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34450000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2040</th>\n",
       "      <td>2023-07-02</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>1</td>\n",
       "      <td>Extraction 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36200000</td>\n",
       "      <td>2.0667</td>\n",
       "      <td>17500000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>2023-07-09</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>1</td>\n",
       "      <td>Make Me Believe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6500000</td>\n",
       "      <td>1.7333</td>\n",
       "      <td>3800000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1440</th>\n",
       "      <td>2023-10-15</td>\n",
       "      <td>Films (English)</td>\n",
       "      <td>1</td>\n",
       "      <td>Reptile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32300000</td>\n",
       "      <td>2.2667</td>\n",
       "      <td>14200000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>770</th>\n",
       "      <td>2024-02-11</td>\n",
       "      <td>Films (Non-English)</td>\n",
       "      <td>1</td>\n",
       "      <td>Badland Hunters</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11100000</td>\n",
       "      <td>1.8167</td>\n",
       "      <td>6100000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            week             category  weekly_rank           show_title  \\\n",
       "4640  2022-04-03      Films (English)            1     The Adam Project   \n",
       "4250  2022-06-12  Films (Non-English)            1          RRR (Hindi)   \n",
       "2200  2023-06-04      Films (English)            1           The Mother   \n",
       "4290  2022-06-05  Films (Non-English)            1          RRR (Hindi)   \n",
       "840   2024-01-28      Films (English)            1                 Lift   \n",
       "4880  2022-02-20      Films (English)            1  The Tinder Swindler   \n",
       "2040  2023-07-02      Films (English)            1         Extraction 2   \n",
       "2010  2023-07-09  Films (Non-English)            1      Make Me Believe   \n",
       "1440  2023-10-15      Films (English)            1              Reptile   \n",
       "770   2024-02-11  Films (Non-English)            1      Badland Hunters   \n",
       "\n",
       "     season_title  weekly_hours_viewed  runtime  weekly_views  \\\n",
       "4640          NaN             17720000      NaN           NaN   \n",
       "4250          NaN              5230000      NaN           NaN   \n",
       "2200          NaN             16480000      NaN           NaN   \n",
       "4290          NaN             13940000      NaN           NaN   \n",
       "840           NaN             31000000   1.7833    17400000.0   \n",
       "4880          NaN             34450000      NaN           NaN   \n",
       "2040          NaN             36200000   2.0667    17500000.0   \n",
       "2010          NaN              6500000   1.7333     3800000.0   \n",
       "1440          NaN             32300000   2.2667    14200000.0   \n",
       "770           NaN             11100000   1.8167     6100000.0   \n",
       "\n",
       "      cumulative_weeks_in_top_10  is_staggered_launch episode_launch_details  \n",
       "4640                           4                False                    NaN  \n",
       "4250                           4                False                    NaN  \n",
       "2200                           4                False                    NaN  \n",
       "4290                           3                False                    NaN  \n",
       "840                            3                False                    NaN  \n",
       "4880                           3                False                    NaN  \n",
       "2040                           3                False                    NaN  \n",
       "2010                           3                False                    NaN  \n",
       "1440                           3                False                    NaN  \n",
       "770                            3                False                    NaN  "
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# here, I'll try to find out which films had the most weeks at No. 1\n",
    "\n",
    "# filtering only the No. 1s\n",
    "filtered_df[(filtered_df['weekly_rank'] == 1)].sort_values('cumulative_weeks_in_top_10', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "2c332af9-a1ce-497d-b6b2-55b85cce26f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category             show_title                                 \n",
       "Films (English)      The Adam Project                               4\n",
       "                     The Mother                                     4\n",
       "                     Don't Look Up                                  3\n",
       "                     Extraction 2                                   3\n",
       "                     Glass Onion: A Knives Out Mystery              3\n",
       "                     Lift                                           3\n",
       "                     Luther: The Fallen Sun                         3\n",
       "                     Red Notice                                     3\n",
       "                     Reptile                                        3\n",
       "                     The Out-Laws                                   3\n",
       "Films (Non-English)  Badland Hunters                                3\n",
       "                     Paradise                                       3\n",
       "                     RRR (Hindi)                                    3\n",
       "                     Society of the Snow                            3\n",
       "                     A Life Too Short: The Isabella Nardoni Case    2\n",
       "                     AKA                                            2\n",
       "                     All Quiet on the Western Front                 2\n",
       "                     Bird Box Barcelona                             2\n",
       "                     Black Crab                                     2\n",
       "                     Black Island                                   2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# grouping by category, then filtering only the No. 1s\n",
    "new = filtered_df[(filtered_df['weekly_rank'] == 1)]\n",
    "new.groupby('category')['show_title'].value_counts().groupby(level=0, group_keys=False).nlargest(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "9953e571-c715-4387-8e13-06aa71f5a1f6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category         show_title                       \n",
       "Films (English)  The Adam Project                     4\n",
       "                 The Mother                           4\n",
       "                 Don't Look Up                        3\n",
       "                 Extraction 2                         3\n",
       "                 Glass Onion: A Knives Out Mystery    3\n",
       "                 Lift                                 3\n",
       "                 Luther: The Fallen Sun               3\n",
       "                 Red Notice                           3\n",
       "                 Reptile                              3\n",
       "                 The Out-Laws                         3\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# what a long list... I guess I'll just keep the overall version\n",
    "new.groupby('category')['show_title'].value_counts().nlargest(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "8ba6fa9c-bf26-4246-b81a-61923aad9759",
   "metadata": {},
   "outputs": [],
   "source": [
    "# saving these to csv files\n",
    "new.groupby('category')['show_title'].value_counts().groupby(level=0, group_keys=False).nlargest(10).to_csv('top-1-categorized.csv')\n",
    "new.groupby('category')['show_title'].value_counts().nlargest(10).to_csv('top-1-overall.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f77363ed-9f76-4a68-a484-1cb8c6b4d930",
   "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}