Code/1-Support-Lists.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4d96d7a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standard library imports\n",
    "import os\n",
    "import glob\n",
    "from datetime import datetime, date\n",
    "from dateutil import parser\n",
    "\n",
    "# Third-party imports\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "baa1861c",
   "metadata": {},
   "source": [
    "# 1. Samsung"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "49742627",
   "metadata": {},
   "outputs": [],
   "source": [
    "def excel_to_dataframe(excel_path):\n",
    "    df1 = pd.read_excel(excel_path, sheet_name = 'Monthly', usecols = 'A', names=['device'],header=None)\n",
    "    df1['support_type'] = 'Monthly'\n",
    "    df1['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "\n",
    "    df2 = pd.read_excel(excel_path, sheet_name = 'Quarterly', usecols = 'A', names=['device'],header=None)\n",
    "    df2['support_type'] = 'Quarterly'\n",
    "    df2['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "\n",
    "    try:\n",
    "        df3 = pd.read_excel(excel_path, sheet_name = 'Other', usecols = 'A', names=['device'],header=None)\n",
    "        df3['support_type'] = 'Biannually'\n",
    "        df3['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "    except ValueError:\n",
    "        try: \n",
    "            df3 = pd.read_excel(excel_path, sheet_name = 'Biannual', usecols = 'A', names=['device'],header=None)\n",
    "            df3['support_type'] = 'Biannually'\n",
    "            df3['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "        except ValueError:\n",
    "            df3 = pd.DataFrame([])\n",
    "        \n",
    "    try:\n",
    "        df4 = pd.read_excel(excel_path, sheet_name = 'Wearable', usecols = 'A', names=['device'],header=None)\n",
    "        df4['support_type'] = 'Wearable'\n",
    "        df4['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "    except ValueError:\n",
    "        df4 = pd.DataFrame([])\n",
    "\n",
    "    df = pd.concat([df1,df2,df3,df4])\n",
    "    return df\n",
    "\n",
    "\n",
    "def get_support_timeline(device,dataset):\n",
    "\n",
    "    df = dataset\n",
    "    \n",
    "    #print('Getting support timeline for {}!'.format(device))\n",
    "    \n",
    "    df_device = df[df.device == device].sort_values(by='date')\n",
    "    \n",
    "    try:\n",
    "        monthly_start = df_device[df_device.support_type == 'Monthly'].iloc[0]['date']\n",
    "        monthly_end = df_device[df_device.support_type == 'Monthly'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        monthly_start = None\n",
    "        monthly_end = None\n",
    "        \n",
    "    try: \n",
    "        quarterly_start = df_device[df_device.support_type == 'Quarterly'].iloc[0]['date']\n",
    "        quarterly_end = df_device[df_device.support_type == 'Quarterly'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        quarterly_start = None\n",
    "        quarterly_end = None\n",
    "        \n",
    "    try:\n",
    "        biannually_start = df_device[df_device.support_type == 'Biannually'].iloc[0]['date']\n",
    "        biannually_end = df_device[df_device.support_type == 'Biannually'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        biannually_start = None\n",
    "        biannually_end = None\n",
    "    \n",
    "    try:\n",
    "        wearable_start = df_device[df_device.support_type == 'Wearable'].iloc[0]['date']\n",
    "        wearable_end = df_device[df_device.support_type == 'Wearable'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        wearable_start = None\n",
    "        wearable_end = None\n",
    "    result = [monthly_start,monthly_end,quarterly_start,quarterly_end,biannually_start,biannually_end,wearable_start,wearable_end]\n",
    "    \n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a6b18b50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting lists from ../Data/Samsung/samsung-support-lists/\n",
      "Converting excel files to DataFrame is done!\n"
     ]
    },
    {
     "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>device</th>\n",
       "      <th>monthly_start</th>\n",
       "      <th>monthly_end</th>\n",
       "      <th>quarterly_start</th>\n",
       "      <th>quarterly_end</th>\n",
       "      <th>biannually_start</th>\n",
       "      <th>biannually_end</th>\n",
       "      <th>wearable_start</th>\n",
       "      <th>wearable_end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Galaxy Fold</td>\n",
       "      <td>2019-10-08 22:18:41</td>\n",
       "      <td>2022-11-04 17:25:38</td>\n",
       "      <td>2022-11-08 08:46:24</td>\n",
       "      <td>2023-04-04 00:30:35</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Galaxy Z Flip</td>\n",
       "      <td>2020-03-03 11:31:08</td>\n",
       "      <td>2023-04-04 00:30:35</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Galaxy S8</td>\n",
       "      <td>2017-10-03 06:11:44</td>\n",
       "      <td>2020-05-03 21:44:01</td>\n",
       "      <td>2020-05-25 22:54:03</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Galaxy S8+</td>\n",
       "      <td>2017-10-03 06:11:44</td>\n",
       "      <td>2020-05-03 21:44:01</td>\n",
       "      <td>2020-05-25 22:54:03</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Galaxy S8 Active</td>\n",
       "      <td>2017-10-03 06:11:44</td>\n",
       "      <td>2020-08-06 10:02:12</td>\n",
       "      <td>2020-09-14 17:19:27</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             device        monthly_start          monthly_end  \\\n",
       "0       Galaxy Fold  2019-10-08 22:18:41  2022-11-04 17:25:38   \n",
       "1     Galaxy Z Flip  2020-03-03 11:31:08  2023-04-04 00:30:35   \n",
       "2         Galaxy S8  2017-10-03 06:11:44  2020-05-03 21:44:01   \n",
       "3        Galaxy S8+  2017-10-03 06:11:44  2020-05-03 21:44:01   \n",
       "4  Galaxy S8 Active  2017-10-03 06:11:44  2020-08-06 10:02:12   \n",
       "\n",
       "       quarterly_start        quarterly_end biannually_start biannually_end  \\\n",
       "0  2022-11-08 08:46:24  2023-04-04 00:30:35              NaT            NaT   \n",
       "1                 None                 None              NaT            NaT   \n",
       "2  2020-05-25 22:54:03  2020-12-10 23:59:35              NaT            NaT   \n",
       "3  2020-05-25 22:54:03  2020-12-10 23:59:35              NaT            NaT   \n",
       "4  2020-09-14 17:19:27  2020-12-10 23:59:35              NaT            NaT   \n",
       "\n",
       "  wearable_start wearable_end  \n",
       "0            NaT          NaT  \n",
       "1            NaT          NaT  \n",
       "2            NaT          NaT  \n",
       "3            NaT          NaT  \n",
       "4            NaT          NaT  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir_path = '../Data/Samsung/samsung-support-lists/'\n",
    "\n",
    "print('Getting lists from {}'.format(dir_path))\n",
    "\n",
    "excel_files = glob.glob(dir_path + '*.xlsx')\n",
    "df_final_samsung = pd.DataFrame([])\n",
    "for file in excel_files[:]:\n",
    "    df_new = excel_to_dataframe(file)\n",
    "    df_final_samsung = pd.concat([df_final_samsung,df_new],ignore_index=True)\n",
    "\n",
    "df_final_samsung.to_csv('../Data/Samsung/support_lists.csv',index=False)\n",
    "\n",
    "print('Converting excel files to DataFrame is done!')\n",
    "\n",
    "df_timelines_samsung = pd.DataFrame()\n",
    "for device in df_final_samsung['device'].unique():\n",
    "    \n",
    "    support_timeline = get_support_timeline(device,df_final_samsung)\n",
    "    \n",
    "    df_new = pd.DataFrame({\"device\": device, \n",
    "                           \"monthly_start\": support_timeline[0],\n",
    "                           \"monthly_end\": support_timeline[1],\n",
    "                           \"quarterly_start\": support_timeline[2],\n",
    "                           \"quarterly_end\": support_timeline[3],\n",
    "                           \"biannually_start\": support_timeline[4],\n",
    "                           \"biannually_end\": support_timeline[5],\n",
    "                           \"wearable_start\": support_timeline[6],\n",
    "                           \"wearable_end\": support_timeline[7]},\n",
    "                          index=[0])\n",
    " \n",
    "    \n",
    "    df_timelines_samsung = pd.concat([df_timelines_samsung,df_new],ignore_index=True)\n",
    "\n",
    "df_timelines_samsung.to_csv('../Data/Samsung/timelines_samsung_devices_v1.csv',index=False)\n",
    "\n",
    "df_timelines_samsung.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bab097f",
   "metadata": {},
   "source": [
    "## Samsung Support List Stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d121d71d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First snapshot: 2017-10-03 06:11:44\n",
      "Last snapshot: 2023-04-04 00:30:35\n",
      "All snapshots: 312\n",
      "Last snapshot: 2023-04-04 00:30:35\n",
      "Avg snapshot: 6.435897435897436\n",
      "Unique Devices (Total): 257\n",
      "Unique Devices (Last list): 158\n"
     ]
    }
   ],
   "source": [
    "print('First snapshot: {}'.format(df_final_samsung['date'].min()))\n",
    "print('Last snapshot: {}'.format(df_final_samsung['date'].max()))\n",
    "print('All snapshots: {}'.format(len(df_final_samsung['date'].unique())))\n",
    "print('Last snapshot: {}'.format(df_final_samsung['date'].max()))\n",
    "print('Avg snapshot: {}'.format((df_final_samsung['date'].max()-df_final_samsung['date'].min()).days/len(df_final_samsung['date'].unique())))\n",
    "print('Unique Devices (Total): {}'.format(len(df_timelines_samsung.index)))\n",
    "print('Unique Devices (Last list): {}'.format(len(df_final_samsung[df_final_samsung['date'] == df_final_samsung['date'].max()])))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a476db85",
   "metadata": {},
   "source": [
    "### Filtering the incomplete periods\n",
    "\n",
    "Removing first and last snaphot from the beginning and end of the period because:\n",
    "- as devices in the last snapshot are likely to continue to receive security updates, their end date cannot be marked yet.\n",
    "- similarly, when the support lists started to be published, the devices given in the first published support list were probably getting the support before that date, and the start of their support period cannot be marked as we have no earlier data than the first snapshot.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6e0470a0",
   "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>device</th>\n",
       "      <th>monthly_start</th>\n",
       "      <th>monthly_end</th>\n",
       "      <th>quarterly_start</th>\n",
       "      <th>quarterly_end</th>\n",
       "      <th>biannually_start</th>\n",
       "      <th>biannually_end</th>\n",
       "      <th>wearable_start</th>\n",
       "      <th>wearable_end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Galaxy Fold</td>\n",
       "      <td>2019-10-08 22:18:41</td>\n",
       "      <td>2022-11-04 17:25:38</td>\n",
       "      <td>2022-11-08 08:46:24</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Galaxy Z Flip</td>\n",
       "      <td>2020-03-03 11:31:08</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Galaxy S8</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2020-05-03 21:44:01</td>\n",
       "      <td>2020-05-25 22:54:03</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Galaxy S8+</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2020-05-03 21:44:01</td>\n",
       "      <td>2020-05-25 22:54:03</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Galaxy S8 Active</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2020-08-06 10:02:12</td>\n",
       "      <td>2020-09-14 17:19:27</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             device        monthly_start          monthly_end  \\\n",
       "0       Galaxy Fold  2019-10-08 22:18:41  2022-11-04 17:25:38   \n",
       "1     Galaxy Z Flip  2020-03-03 11:31:08                  NaT   \n",
       "2         Galaxy S8                  NaT  2020-05-03 21:44:01   \n",
       "3        Galaxy S8+                  NaT  2020-05-03 21:44:01   \n",
       "4  Galaxy S8 Active                  NaT  2020-08-06 10:02:12   \n",
       "\n",
       "       quarterly_start        quarterly_end biannually_start biannually_end  \\\n",
       "0  2022-11-08 08:46:24                  NaT              NaN            NaN   \n",
       "1                  NaN                  NaN              NaN            NaN   \n",
       "2  2020-05-25 22:54:03  2020-12-10 23:59:35              NaN            NaN   \n",
       "3  2020-05-25 22:54:03  2020-12-10 23:59:35              NaN            NaN   \n",
       "4  2020-09-14 17:19:27  2020-12-10 23:59:35              NaN            NaN   \n",
       "\n",
       "  wearable_start wearable_end  \n",
       "0            NaN          NaN  \n",
       "1            NaN          NaN  \n",
       "2            NaN          NaN  \n",
       "3            NaN          NaN  \n",
       "4            NaN          NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('../Data/Samsung/timelines_samsung_devices_v1.csv')\n",
    "\n",
    "date_first_list = '2017-10-03 06:11:44' # remove the first because maybe the device was already receving before\n",
    "date_last_list = '2023-04-04 00:30:35' # remove the last because maybe the device will continue to receive \n",
    "date_nat = pd.Timestamp('NaT').to_pydatetime()\n",
    "\n",
    "for column in df.columns:\n",
    "    df.at[(df[column] == date_first_list) | (df[column] == date_last_list), column] = date_nat\n",
    "    \n",
    "df.to_csv('../Data/Samsung/timelines_samsung_devices_v2.csv',index=False)\n",
    "\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e81aea7",
   "metadata": {},
   "source": [
    "## Calculating average durations in the support lists"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4aa34af6",
   "metadata": {},
   "source": [
    "- Also filter out if only one time appeared in the list since duration can not be calculated with a single date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1743cc0f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Avg monthly support duration: 1017 days 22:24:10.500000\n",
      "Monthly completed periods for: 20 devices\n",
      "Avg quarterly support duration: 493 days 18:35:53.678899080\n",
      "Quarterly completed periods for: 109 devices\n",
      "Avg binannually support duration: 519 days 01:23:47.617021280\n",
      "Biannually completed periods for: 47 devices\n"
     ]
    }
   ],
   "source": [
    "df_timelines = pd.read_csv('../Data/Samsung/timelines_samsung_devices_v2.csv')\n",
    "    \n",
    "df_timelines[['monthly_start','monthly_end','quarterly_start','quarterly_end','biannually_start','biannually_end']] = df_timelines[['monthly_start','monthly_end','quarterly_start','quarterly_end','biannually_start','biannually_end']].apply(pd.to_datetime)\n",
    "df_timelines['monthly_duration'] = df_timelines['monthly_end']-df_timelines['monthly_start']\n",
    "df_timelines['quarterly_duration'] = df_timelines['quarterly_end']-df_timelines['quarterly_start']\n",
    "df_timelines['biannually_duration'] = df_timelines['biannually_end']-df_timelines['biannually_start']\n",
    "\n",
    "df_timelines.to_csv('../Data/Samsung/timelines_samsung_devices_v3.csv',index=False)\n",
    "\n",
    "cond1 = ~(df_timelines['monthly_duration'].dt.days==0) #filter out if only one time appeared in the list\n",
    "cond2 = ~df_timelines['monthly_duration'].isnull() #filter out as no monthly duration can be calculated\n",
    "\n",
    "df_timelines_monthly = df_timelines[cond1&cond2].reset_index(drop=True)\n",
    "\n",
    "cond3 = ~(df_timelines['quarterly_duration'].dt.days==0) #filter out if only one time appeared in the list\n",
    "cond4 = ~df_timelines['quarterly_duration'].isnull() #filter out as no monthly duration can be calculated\n",
    "\n",
    "df_timelines_quarterly = df_timelines[cond3&cond4].reset_index(drop=True)\n",
    "\n",
    "\n",
    "cond5 = ~(df_timelines['biannually_duration'].dt.days==0) #filter out if only one time appeared in the list\n",
    "cond6 = ~df_timelines['biannually_duration'].isnull() #filter out as no monthly duration can be calculated\n",
    "\n",
    "df_timelines_biannual = df_timelines[cond5&cond6].reset_index(drop=True)\n",
    "\n",
    "\n",
    "print(\"Avg monthly support duration: {}\".format(df_timelines_monthly['monthly_duration'].mean(skipna=True)))\n",
    "print('Monthly completed periods for: {} devices'.format(len(df_timelines_monthly['device'].unique())))\n",
    "\n",
    "print(\"Avg quarterly support duration: {}\".format(df_timelines_quarterly['quarterly_duration'].mean(skipna=True)))\n",
    "print('Quarterly completed periods for: {} devices'.format(len(df_timelines_quarterly['device'].unique())))\n",
    "\n",
    "print(\"Avg binannually support duration: {}\".format(df_timelines_biannual['biannually_duration'].mean(skipna=True)))\n",
    "print('Biannually completed periods for: {} devices'.format(len(df_timelines_biannual['device'].unique())))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "117341bf",
   "metadata": {},
   "source": [
    "## Tablets-only"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c68d764",
   "metadata": {},
   "source": [
    "- Noticed that no tablets is given in monthly support list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8c6f7b3f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>device</th>\n",
       "      <th>monthly_start</th>\n",
       "      <th>monthly_end</th>\n",
       "      <th>quarterly_start</th>\n",
       "      <th>quarterly_end</th>\n",
       "      <th>biannually_start</th>\n",
       "      <th>biannually_end</th>\n",
       "      <th>wearable_start</th>\n",
       "      <th>wearable_end</th>\n",
       "      <th>monthly_duration</th>\n",
       "      <th>quarterly_duration</th>\n",
       "      <th>biannually_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Galaxy Tab A 10.5 (2018)</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2019-05-20 07:22:25</td>\n",
       "      <td>2020-08-06 10:02:12</td>\n",
       "      <td>2020-09-14 17:19:27</td>\n",
       "      <td>2022-09-06 03:13:18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>444 days 02:39:47</td>\n",
       "      <td>721 days 09:53:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Galaxy Tab A 10.1 (2019)</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2019-04-05 15:35:10</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>2022-01-05 00:27:42</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>615 days 08:24:25</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Galaxy Tab A 8 (2019)</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2019-10-08 22:18:41</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>2022-01-05 00:27:42</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>429 days 01:40:54</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Galaxy Tab A 8 Plus (2019)</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2019-05-20 07:22:25</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>570 days 16:37:10</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Galaxy Tab Active2</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2018-03-08 21:26:30</td>\n",
       "      <td>2020-12-10 23:59:35</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1008 days 02:33:05</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        device monthly_start monthly_end     quarterly_start  \\\n",
       "77    Galaxy Tab A 10.5 (2018)           NaT         NaT 2019-05-20 07:22:25   \n",
       "78    Galaxy Tab A 10.1 (2019)           NaT         NaT 2019-04-05 15:35:10   \n",
       "79       Galaxy Tab A 8 (2019)           NaT         NaT 2019-10-08 22:18:41   \n",
       "80  Galaxy Tab A 8 Plus (2019)           NaT         NaT 2019-05-20 07:22:25   \n",
       "81          Galaxy Tab Active2           NaT         NaT 2018-03-08 21:26:30   \n",
       "\n",
       "         quarterly_end    biannually_start      biannually_end wearable_start  \\\n",
       "77 2020-08-06 10:02:12 2020-09-14 17:19:27 2022-09-06 03:13:18            NaN   \n",
       "78 2020-12-10 23:59:35 2022-01-05 00:27:42                 NaT            NaN   \n",
       "79 2020-12-10 23:59:35 2022-01-05 00:27:42                 NaT            NaN   \n",
       "80 2020-12-10 23:59:35                 NaT                 NaT            NaN   \n",
       "81 2020-12-10 23:59:35                 NaT                 NaT            NaN   \n",
       "\n",
       "    wearable_end monthly_duration quarterly_duration biannually_duration  \n",
       "77           NaN              NaT  444 days 02:39:47   721 days 09:53:51  \n",
       "78           NaN              NaT  615 days 08:24:25                 NaT  \n",
       "79           NaN              NaT  429 days 01:40:54                 NaT  \n",
       "80           NaN              NaT  570 days 16:37:10                 NaT  \n",
       "81           NaN              NaT 1008 days 02:33:05                 NaT  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_timelines_tablets = df_timelines[df_timelines['device'].str.contains('Tab')]\n",
    "\n",
    "df_timelines_tablets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c70b555d",
   "metadata": {},
   "source": [
    "# 2. Xiaomi"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "899dd6ee",
   "metadata": {},
   "source": [
    "## Xiaomi Support Lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a79b1b7c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting lists from ../Data/Xiaomi/Xiaomi-device-support-lists/\n",
      "Converting excel files to DataFrame is done!\n"
     ]
    },
    {
     "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>phone</th>\n",
       "      <th>monthly_start</th>\n",
       "      <th>monthly_end</th>\n",
       "      <th>quarterly_start</th>\n",
       "      <th>quarterly_end</th>\n",
       "      <th>biannually_start</th>\n",
       "      <th>biannually_end</th>\n",
       "      <th>wearable_start</th>\n",
       "      <th>wearable_end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mi A3</td>\n",
       "      <td>2021-01-27 17:45:16</td>\n",
       "      <td>2022-10-31 17:52:22</td>\n",
       "      <td>2021-01-27 17:45:16</td>\n",
       "      <td>2022-10-31 17:52:22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mi A2</td>\n",
       "      <td>2021-01-27 17:45:16</td>\n",
       "      <td>2022-10-31 17:52:22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mi A2 Lite</td>\n",
       "      <td>2021-01-27 17:45:16</td>\n",
       "      <td>2022-10-31 17:52:22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mi 10T Pro</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-27 17:45:16</td>\n",
       "      <td>2022-10-31 17:52:22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mi 10 T</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-27 17:45:16</td>\n",
       "      <td>2022-10-31 17:52:22</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        phone        monthly_start          monthly_end      quarterly_start  \\\n",
       "0       Mi A3  2021-01-27 17:45:16  2022-10-31 17:52:22  2021-01-27 17:45:16   \n",
       "1       Mi A2  2021-01-27 17:45:16  2022-10-31 17:52:22                 None   \n",
       "2  Mi A2 Lite  2021-01-27 17:45:16  2022-10-31 17:52:22                 None   \n",
       "3  Mi 10T Pro                 None                 None  2021-01-27 17:45:16   \n",
       "4     Mi 10 T                 None                 None  2021-01-27 17:45:16   \n",
       "\n",
       "         quarterly_end biannually_start biannually_end wearable_start  \\\n",
       "0  2022-10-31 17:52:22             None           None           None   \n",
       "1                 None             None           None           None   \n",
       "2                 None             None           None           None   \n",
       "3  2022-10-31 17:52:22             None           None           None   \n",
       "4  2022-10-31 17:52:22             None           None           None   \n",
       "\n",
       "  wearable_end  \n",
       "0         None  \n",
       "1         None  \n",
       "2         None  \n",
       "3         None  \n",
       "4         None  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def excel_to_dataframe(excel_path):\n",
    "    df1 = pd.read_excel(excel_path, sheet_name = 'Monthly', usecols = 'A', names=['phone_model'],header=None)\n",
    "    df1['support'] = 'Monthly'\n",
    "    df1['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "\n",
    "    df2 = pd.read_excel(excel_path, sheet_name = 'Quarterly', usecols = 'A', names=['phone_model'],header=None)\n",
    "    df2['support'] = 'Quarterly'\n",
    "    df2['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "\n",
    "    try:\n",
    "        df3 = pd.read_excel(excel_path, sheet_name = 'Other', usecols = 'A', names=['phone_model'],header=None)\n",
    "        df3['support'] = 'Biannually'\n",
    "        df3['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "    except ValueError:\n",
    "        try: \n",
    "            df3 = pd.read_excel(excel_path, sheet_name = 'Biannual', usecols = 'A', names=['phone_model'],header=None)\n",
    "            df3['support'] = 'Biannually'\n",
    "            df3['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "        except ValueError:\n",
    "            df3 = pd.DataFrame([])\n",
    "            \n",
    "    try:\n",
    "        df4 = pd.read_excel(excel_path, sheet_name = 'Wearable', usecols = 'A', names=['phone_model'],header=None)\n",
    "        df4['support'] = 'Wearable'\n",
    "        df4['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "    except ValueError:\n",
    "        df4 = pd.DataFrame([])\n",
    "\n",
    "    df = pd.concat([df1,df2,df3,df4])\n",
    "    return df\n",
    "\n",
    "\n",
    "def get_support_timeline(phone,dataset):\n",
    "\n",
    "    df = dataset\n",
    "    \n",
    "    #print('Getting support timeline for {}!'.format(phone))\n",
    "    \n",
    "    df_phone = df[df.phone_model == phone].sort_values(by='date')\n",
    "    \n",
    "    try:\n",
    "        monthly_start = df_phone[df_phone.support == 'Monthly'].iloc[0]['date']\n",
    "        monthly_end = df_phone[df_phone.support == 'Monthly'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        monthly_start = None\n",
    "        monthly_end = None\n",
    "        \n",
    "    try: \n",
    "        quarterly_start = df_phone[df_phone.support == 'Quarterly'].iloc[0]['date']\n",
    "        quarterly_end = df_phone[df_phone.support == 'Quarterly'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        quarterly_start = None\n",
    "        quarterly_end = None\n",
    "        \n",
    "    try:\n",
    "        biannually_start = df_phone[df_phone.support == 'Biannually'].iloc[0]['date']\n",
    "        biannually_end = df_phone[df_phone.support == 'Biannually'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        biannually_start = None\n",
    "        biannually_end = None\n",
    "    \n",
    "    try:\n",
    "        wearable_start = df_phone[df_phone.support == 'Wearable'].iloc[0]['date']\n",
    "        wearable_end = df_phone[df_phone.support == 'Wearable'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        wearable_start = None\n",
    "        wearable_end = None\n",
    "    result = [monthly_start,monthly_end,quarterly_start,quarterly_end,biannually_start,biannually_end,wearable_start,wearable_end]\n",
    "    \n",
    "    return result\n",
    "\n",
    "dir_path = '../Data/Xiaomi/Xiaomi-device-support-lists/'\n",
    "\n",
    "print('Getting lists from {}'.format(dir_path))\n",
    "\n",
    "excel_files = glob.glob(dir_path + '*.xlsx')\n",
    "df_final_xiaomi = pd.DataFrame([])\n",
    "for file in excel_files[:]:\n",
    "    df_new = excel_to_dataframe(file)\n",
    "    df_final_xiaomi = pd.concat([df_final_xiaomi,df_new],ignore_index=True)\n",
    "\n",
    "print('Converting excel files to DataFrame is done!')\n",
    "\n",
    "df_timelines_xiaomi = pd.DataFrame([])\n",
    "for phone in df_final_xiaomi['phone_model'].unique():\n",
    "    \n",
    "    support_timeline = get_support_timeline(phone,df_final_xiaomi)\n",
    "    \n",
    "    df_new = pd.DataFrame({\"phone\": phone, \n",
    "                           \"monthly_start\": support_timeline[0],\n",
    "                           \"monthly_end\": support_timeline[1],\n",
    "                           \"quarterly_start\": support_timeline[2],\n",
    "                           \"quarterly_end\": support_timeline[3],\n",
    "                           \"biannually_start\": support_timeline[4],\n",
    "                           \"biannually_end\": support_timeline[5],\n",
    "                           \"wearable_start\": support_timeline[6],\n",
    "                           \"wearable_end\": support_timeline[7]},\n",
    "                          index=[0])\n",
    " \n",
    "    \n",
    "    df_timelines_xiaomi = pd.concat([df_timelines_xiaomi,df_new],ignore_index=True)\n",
    "    \n",
    "df_timelines_xiaomi.to_csv('../Data/Xiaomi/timelines_Xiaomi.csv',index=False)\n",
    "\n",
    "df_timelines_xiaomi.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8e5f4dd",
   "metadata": {},
   "source": [
    "## Xiaomi EOL lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "803e49a3",
   "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>Date</th>\n",
       "      <th>Brand</th>\n",
       "      <th>Source URL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2741</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>MI MAX</td>\n",
       "      <td>https://web.archive.org/web/20211012105859/htt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2779</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>Redmi S2</td>\n",
       "      <td>https://web.archive.org/web/20211012105859/htt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2780</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>Redmi Note 5A</td>\n",
       "      <td>https://web.archive.org/web/20211012105859/htt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2781</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>Redmi Note 2 Pro</td>\n",
       "      <td>https://web.archive.org/web/20211012105859/htt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2782</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>MI MIX 2S</td>\n",
       "      <td>https://web.archive.org/web/20211012105859/htt...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date             Brand  \\\n",
       "2741  2021-10-12            MI MAX   \n",
       "2779  2021-10-12          Redmi S2   \n",
       "2780  2021-10-12     Redmi Note 5A   \n",
       "2781  2021-10-12  Redmi Note 2 Pro   \n",
       "2782  2021-10-12         MI MIX 2S   \n",
       "\n",
       "                                             Source URL  \n",
       "2741  https://web.archive.org/web/20211012105859/htt...  \n",
       "2779  https://web.archive.org/web/20211012105859/htt...  \n",
       "2780  https://web.archive.org/web/20211012105859/htt...  \n",
       "2781  https://web.archive.org/web/20211012105859/htt...  \n",
       "2782  https://web.archive.org/web/20211012105859/htt...  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Folder path\n",
    "folder_path = '../Data/Xiaomi/xiaomi_archieve_data_eol/'\n",
    "\n",
    "# Initialize empty DataFrame\n",
    "df_xiaomi_eol = pd.DataFrame(columns=['Date', 'Brand', 'Source URL'])\n",
    "\n",
    "# Go through each file in the folder\n",
    "for filename in os.listdir(folder_path):\n",
    "    if filename.endswith('.xlsx'):\n",
    "        # Get timestamp from the filename\n",
    "        timestamp = filename.split('.')[0]\n",
    "        date = datetime.strptime(timestamp, '%Y%m%d%H%M%S').date() \n",
    "\n",
    "        # Read excel file\n",
    "        excel_data = pd.read_excel(os.path.join(folder_path, filename))\n",
    "\n",
    "        # Check if dataframe is empty\n",
    "        if not excel_data.empty:\n",
    "            # Source URL is the first element of 'Source URL' column\n",
    "            source_url = excel_data['Source URL'].iloc[0]\n",
    "            \n",
    "            my_list = list(set(excel_data['Mi'].to_list()+excel_data['Redmi'].to_list()+excel_data['POCO'].to_list()))\n",
    "\n",
    "            cleanedList = [x for x in my_list if str(x) != 'nan']\n",
    "\n",
    "            # Create a temporary DataFrame from cleanedList and append it to df\n",
    "            temp_df = pd.DataFrame({\n",
    "                'Date': [date]*len(cleanedList), \n",
    "                'Brand': cleanedList, \n",
    "                'Source URL': [source_url]*len(cleanedList)\n",
    "            })\n",
    "            df_xiaomi_eol = df_xiaomi_eol.append(temp_df, ignore_index=True)\n",
    "\n",
    "\n",
    "df_xiaomi_eol = df_xiaomi_eol.sort_values('Date')\n",
    "df_xiaomi_eol.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "587c0bca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique devices (last support list): 52\n"
     ]
    }
   ],
   "source": [
    "# Assuming you already have df_timelines_oppo defined somewhere above\n",
    "\n",
    "# Convert the string columns to datetime format\n",
    "df_timelines_xiaomi['monthly_end'] = pd.to_datetime(df_timelines_xiaomi['monthly_end'])\n",
    "df_timelines_xiaomi['quarterly_end'] = pd.to_datetime(df_timelines_xiaomi['quarterly_end'])\n",
    "df_timelines_xiaomi['biannually_end'] = pd.to_datetime(df_timelines_xiaomi['biannually_end'])\n",
    "\n",
    "# Get the maximum date from the three columns\n",
    "max_date = df_timelines_xiaomi[['monthly_end', 'quarterly_end', 'biannually_end']].max().max()\n",
    "\n",
    "# Filter the rows where the date in any of the three columns matches the max_date\n",
    "matching_rows = df_timelines_xiaomi[\n",
    "    (df_timelines_xiaomi['monthly_end'] == max_date) |\n",
    "    (df_timelines_xiaomi['quarterly_end'] == max_date) |\n",
    "    (df_timelines_xiaomi['biannually_end'] == max_date)\n",
    "]\n",
    "\n",
    "print('Unique devices (last support list): {}'.format(len(matching_rows)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6b6b6da8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique devices (all support lists): 52\n"
     ]
    }
   ],
   "source": [
    "print('Unique devices (all support lists): {}'.format(len(df_timelines_xiaomi['phone'].unique())))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f984e8ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique devices (last EOL list): 58\n"
     ]
    }
   ],
   "source": [
    "latest_date = df_xiaomi_eol['Date'].max()\n",
    "rows_matching_latest_date = df_xiaomi_eol[df_xiaomi_eol['Date'] == latest_date]\n",
    "\n",
    "print('Unique devices (last EOL list): {}'.format(len(rows_matching_latest_date)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "54c282c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique devices (all EOL lists): 95\n"
     ]
    }
   ],
   "source": [
    "print('Unique devices (all EOL lists): {}'.format(len(df_xiaomi_eol['Brand'].unique())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "eca1339b",
   "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>First Appearance</th>\n",
       "      <th>Last Appearance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brand</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>MI 1</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>2022-08-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIX 2</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>2023-03-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIX 2S</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>2023-03-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIX 3</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>2023-03-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Redmi 1</th>\n",
       "      <td>2021-10-12</td>\n",
       "      <td>2023-03-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        First Appearance Last Appearance\n",
       "Brand                                   \n",
       "MI 1          2021-10-12      2022-08-24\n",
       "MIX 2         2021-10-12      2023-03-06\n",
       "MIX 2S        2021-10-12      2023-03-06\n",
       "MIX 3         2021-10-12      2023-03-06\n",
       "Redmi 1       2021-10-12      2023-03-06"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "appearance_df = pd.DataFrame({\n",
    "    'First Appearance': df_xiaomi_eol.groupby('Brand')['Date'].min(),\n",
    "    'Last Appearance': df_xiaomi_eol.groupby('Brand')['Date'].max()\n",
    "})\n",
    "\n",
    "# Sort appearance_df by 'First Appearance'\n",
    "appearance_df = appearance_df.sort_values('First Appearance')\n",
    "\n",
    "appearance_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0661f4a5",
   "metadata": {},
   "source": [
    "# 3. Oppo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2035c344",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting lists from ../Data/Oppo/oppo-support-lists/\n",
      "Converting excel files to DataFrame is done!\n"
     ]
    },
    {
     "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>phone</th>\n",
       "      <th>monthly_start</th>\n",
       "      <th>monthly_end</th>\n",
       "      <th>quarterly_start</th>\n",
       "      <th>quarterly_end</th>\n",
       "      <th>others_start</th>\n",
       "      <th>others_end</th>\n",
       "      <th>model_name</th>\n",
       "      <th>model_number</th>\n",
       "      <th>model_name_v2</th>\n",
       "      <th>monthly_duration</th>\n",
       "      <th>quarterly_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Find X2（CPH2023、CPH1921）</td>\n",
       "      <td>2022-10-11 18:51:30</td>\n",
       "      <td>2023-03-07 19:24:15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Find X2</td>\n",
       "      <td>CPH2023、CPH1921</td>\n",
       "      <td>Find X2</td>\n",
       "      <td>147.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Find X2 Pro（CPH2025）</td>\n",
       "      <td>2022-10-11 18:51:30</td>\n",
       "      <td>2023-03-07 19:24:15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Find X2 Pro</td>\n",
       "      <td>CPH2025</td>\n",
       "      <td>Find X2 Pro</td>\n",
       "      <td>147.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Find X3（PEDM00）</td>\n",
       "      <td>2022-10-11 18:51:30</td>\n",
       "      <td>2023-03-07 19:24:15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2021-01-21 17:16:59</td>\n",
       "      <td>2022-06-09 07:23:31</td>\n",
       "      <td>Find X3</td>\n",
       "      <td>PEDM00</td>\n",
       "      <td>Find X3</td>\n",
       "      <td>147.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Find X3 Neo（CPH2207）</td>\n",
       "      <td>2022-10-11 18:51:30</td>\n",
       "      <td>2023-03-07 19:24:15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2021-01-21 17:16:59</td>\n",
       "      <td>2022-06-09 07:23:31</td>\n",
       "      <td>Find X3 Neo</td>\n",
       "      <td>CPH2207</td>\n",
       "      <td>Find X3 Neo</td>\n",
       "      <td>147.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Find X3 Pro（PEEM00、CPH2173、CPH2305）</td>\n",
       "      <td>2022-10-11 18:51:30</td>\n",
       "      <td>2023-03-07 19:24:15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Find X3 Pro</td>\n",
       "      <td>PEEM00、CPH2173、CPH2305</td>\n",
       "      <td>Find X3 Pro</td>\n",
       "      <td>147.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 phone        monthly_start  \\\n",
       "0             Find X2（CPH2023、CPH1921）  2022-10-11 18:51:30   \n",
       "1                 Find X2 Pro（CPH2025）  2022-10-11 18:51:30   \n",
       "2                      Find X3（PEDM00）  2022-10-11 18:51:30   \n",
       "3                 Find X3 Neo（CPH2207）  2022-10-11 18:51:30   \n",
       "4  Find X3 Pro（PEEM00、CPH2173、CPH2305）  2022-10-11 18:51:30   \n",
       "\n",
       "           monthly_end quarterly_start quarterly_end         others_start  \\\n",
       "0  2023-03-07 19:24:15             NaT           NaT                  NaT   \n",
       "1  2023-03-07 19:24:15             NaT           NaT                  NaT   \n",
       "2  2023-03-07 19:24:15             NaT           NaT  2021-01-21 17:16:59   \n",
       "3  2023-03-07 19:24:15             NaT           NaT  2021-01-21 17:16:59   \n",
       "4  2023-03-07 19:24:15             NaT           NaT                 None   \n",
       "\n",
       "            others_end   model_name            model_number model_name_v2  \\\n",
       "0                  NaT      Find X2         CPH2023、CPH1921       Find X2   \n",
       "1                  NaT  Find X2 Pro                 CPH2025   Find X2 Pro   \n",
       "2  2022-06-09 07:23:31      Find X3                  PEDM00       Find X3   \n",
       "3  2022-06-09 07:23:31  Find X3 Neo                 CPH2207   Find X3 Neo   \n",
       "4                 None  Find X3 Pro  PEEM00、CPH2173、CPH2305   Find X3 Pro   \n",
       "\n",
       "   monthly_duration  quarterly_duration  \n",
       "0             147.0                 NaN  \n",
       "1             147.0                 NaN  \n",
       "2             147.0                 NaN  \n",
       "3             147.0                 NaN  \n",
       "4             147.0                 NaN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def excel_to_dataframe(excel_path):\n",
    "    df1 = pd.read_excel(excel_path, sheet_name = 'monthly', usecols = 'A', names=['phone_model'],header=None)\n",
    "    df1['support'] = 'monthly'\n",
    "    df1['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "\n",
    "    df2 = pd.read_excel(excel_path, sheet_name = 'quarterly', usecols = 'A', names=['phone_model'],header=None)\n",
    "    df2['support'] = 'quarterly'\n",
    "    df2['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "    \n",
    "    df3 = pd.read_excel(excel_path, sheet_name = 'others', usecols = 'A', names=['phone_model'],header=None)\n",
    "    df3['support'] = 'others'\n",
    "    df3['date'] = datetime.strptime(os.path.splitext(os.path.basename(excel_path))[0],'%Y%m%d%H%M%S')\n",
    "\n",
    "    df = pd.concat([df1,df2,df3])\n",
    "    return df\n",
    "\n",
    "\n",
    "def get_support_timeline(phone,dataset):\n",
    "\n",
    "    df = dataset\n",
    "    \n",
    "    #print('Getting support timeline for {}!'.format(phone))\n",
    "    \n",
    "    df_phone = df[df.phone_model == phone].sort_values(by='date')\n",
    "    \n",
    "    try:\n",
    "        monthly_start = df_phone[df_phone.support == 'monthly'].iloc[0]['date']\n",
    "        monthly_end = df_phone[df_phone.support == 'monthly'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        monthly_start = None\n",
    "        monthly_end = None\n",
    "        \n",
    "    try: \n",
    "        quarterly_start = df_phone[df_phone.support == 'quarterly'].iloc[0]['date']\n",
    "        quarterly_end = df_phone[df_phone.support == 'quarterly'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        quarterly_start = None\n",
    "        quarterly_end = None\n",
    "        \n",
    "    try: \n",
    "        others_start = df_phone[df_phone.support == 'others'].iloc[0]['date']\n",
    "        others_end = df_phone[df_phone.support == 'others'].iloc[-1]['date']\n",
    "    except IndexError:\n",
    "        others_start = None\n",
    "        others_end = None\n",
    "        \n",
    "\n",
    "    result = [monthly_start,monthly_end,quarterly_start,quarterly_end,others_start,others_end]\n",
    "    \n",
    "    return result\n",
    "\n",
    "dir_path = '../Data/Oppo/oppo-support-lists/'\n",
    "\n",
    "print('Getting lists from {}'.format(dir_path))\n",
    "\n",
    "excel_files = glob.glob(dir_path + '*.xlsx')\n",
    "df_final_oppo = pd.DataFrame([])\n",
    "for file in excel_files[:]:\n",
    "    df_new = excel_to_dataframe(file)\n",
    "    df_final_oppo = pd.concat([df_final_oppo,df_new],ignore_index=True)\n",
    "\n",
    "print('Converting excel files to DataFrame is done!')\n",
    "\n",
    "df_timelines_oppo = pd.DataFrame([])\n",
    "for phone in df_final_oppo['phone_model'].unique():\n",
    "    \n",
    "    support_timeline = get_support_timeline(phone,df_final_oppo)\n",
    "    \n",
    "    df_new = pd.DataFrame({\"phone\": phone, \n",
    "                           \"monthly_start\": support_timeline[0],\n",
    "                           \"monthly_end\": support_timeline[1],\n",
    "                           \"quarterly_start\": support_timeline[2],\n",
    "                           \"quarterly_end\": support_timeline[3],\n",
    "                           \"others_start\": support_timeline[4],\n",
    "                           \"others_end\": support_timeline[5]},\n",
    "                          index=[0])\n",
    " \n",
    "    \n",
    "    df_timelines_oppo = pd.concat([df_timelines_oppo,df_new],ignore_index=True)\n",
    "    \n",
    "\n",
    "# split the column values into two columns\n",
    "df_timelines_oppo[['model_name', 'model_number']] = df_timelines_oppo['phone'].str.split('（', expand=True)\n",
    "df_timelines_oppo['model_number'] = df_timelines_oppo['model_number'].str.replace('）', '')\n",
    "df_timelines_oppo['model_name_v2'] = df_timelines_oppo['model_name'].str.split('(', expand=True)[0]\n",
    "\n",
    "df_timelines_oppo['monthly_duration'] = (df_timelines_oppo['monthly_end']-df_timelines_oppo['monthly_start']).dt.days\n",
    "df_timelines_oppo['quarterly_duration'] = (df_timelines_oppo['quarterly_end']-df_timelines_oppo['quarterly_start']).dt.days\n",
    "\n",
    "df_timelines_oppo.to_csv('../Data/Oppo/timelines_Oppo.csv',index=False,encoding=\"utf-8\")\n",
    "\n",
    "df_timelines_oppo.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8f2ab440",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Devices (all support lists): 208\n"
     ]
    }
   ],
   "source": [
    "print('Unique Devices (all support lists): {}'.format(len(df_timelines_oppo['model_name_v2'].unique())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "605384b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Devices (last support list): 147\n"
     ]
    }
   ],
   "source": [
    "# Convert the string columns to datetime format\n",
    "df_timelines_oppo['monthly_end'] = pd.to_datetime(df_timelines_oppo['monthly_end'])\n",
    "df_timelines_oppo['quarterly_end'] = pd.to_datetime(df_timelines_oppo['quarterly_end'])\n",
    "df_timelines_oppo['others_end'] = pd.to_datetime(df_timelines_oppo['others_end'])\n",
    "\n",
    "# Get the maximum date from the three columns\n",
    "max_date = df_timelines_oppo[['monthly_end', 'quarterly_end', 'others_end']].max().max()\n",
    "\n",
    "# Filter the rows where the date in any of the three columns matches the max_date\n",
    "matching_rows = df_timelines_oppo[\n",
    "    (df_timelines_oppo['monthly_end'] == max_date) |\n",
    "    (df_timelines_oppo['quarterly_end'] == max_date) |\n",
    "    (df_timelines_oppo['others_end'] == max_date)\n",
    "]\n",
    "\n",
    "print('Unique Devices (last support list): {}'.format(len(matching_rows['model_name_v2'].unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7188585",
   "metadata": {},
   "source": [
    "# 4. Google"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "eba4e218",
   "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>Phone</th>\n",
       "      <th>Guaranteed Android version updates until at least:</th>\n",
       "      <th>Guaranteed security updates until at least:</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pixel 7 &amp; Pixel 7 Pro</td>\n",
       "      <td>2025-10-01</td>\n",
       "      <td>2027-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pixel 6 &amp; Pixel 6 Pro</td>\n",
       "      <td>2024-10-01</td>\n",
       "      <td>2026-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*Nexus devices get security updates for at lea...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pixel 6a</td>\n",
       "      <td>2025-07-01</td>\n",
       "      <td>2027-07-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pixel 5a with 5G</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>2024-08-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pixel 5</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*Pixel phones get security updates for at leas...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Phone Guaranteed Android version updates until at least:  \\\n",
       "0  Pixel 7 & Pixel 7 Pro                                         2025-10-01   \n",
       "1  Pixel 6 & Pixel 6 Pro                                         2024-10-01   \n",
       "2               Pixel 6a                                         2025-07-01   \n",
       "3       Pixel 5a with 5G                                         2024-08-01   \n",
       "4                Pixel 5                                         2023-10-01   \n",
       "\n",
       "  Guaranteed security updates until at least:  Unnamed: 3  \\\n",
       "0                                  2027-10-01         NaN   \n",
       "1                                  2026-10-01         NaN   \n",
       "2                                  2027-07-01         NaN   \n",
       "3                                  2024-08-01         NaN   \n",
       "4                                  2023-10-01         NaN   \n",
       "\n",
       "                                          Unnamed: 4  \n",
       "0                                                NaN  \n",
       "1  *Nexus devices get security updates for at lea...  \n",
       "2                                                NaN  \n",
       "3                                                NaN  \n",
       "4  *Pixel phones get security updates for at leas...  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_timeline_google = pd.read_excel('../Data/Google/Google-support-list.xlsx')\n",
    "\n",
    "df_timeline_google.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6da5c573",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique Pixel devices: 16\n",
      "Unique Nexus devices: 9\n"
     ]
    }
   ],
   "source": [
    "df_timeline_pixel = df_timeline_google[df_timeline_google['Phone'].str.contains('Pixel')]\n",
    "df_timeline_nexus = df_timeline_google[df_timeline_google['Phone'].str.contains('Nexus')]\n",
    "\n",
    "print('Unique Pixel devices: {}'.format(len(df_timeline_pixel['Phone'].unique())))\n",
    "print('Unique Nexus devices: {}'.format(len(df_timeline_nexus['Phone'].unique())))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "185e1b55",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Supported Pixel devices as of submission: 7\n"
     ]
    }
   ],
   "source": [
    "#Devices support as of submission\n",
    "df_timeline_pixel_supported = df_timeline_pixel[df_timeline_pixel['Guaranteed security updates until at least:']>'2023-06-28']\n",
    "\n",
    "print('Supported Pixel devices as of submission: {}'.format(len(df_timeline_pixel_supported)))"
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}