YouTube_Popular_Videos_Analysis_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "---\r\n",
    "# 1. Collecting data using YouTube api.\r\n",
    "---"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1. a) Importing required libraries:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "source": [
    "import os\r\n",
    "import random\r\n",
    "import requests\r\n",
    "import numpy as np\r\n",
    "import pandas as pd\r\n",
    "import psycopg2 as spg\r\n",
    "from io import StringIO\r\n",
    "from pprint import pprint\r\n",
    "from dotenv import load_dotenv"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1. b) Requirements for API call:-\n",
    "1. Loading api key (credential) stored as envirnment variable.\n",
    "1. Creating efficient HTTP request following the official YouTube API V3 Documentation."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "- Loading api key"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "source": [
    "# If there is/will be no privacy issue, you may assign the API key normally in this file like: api_key = \"[YOUR API KEY]\".\r\n",
    "\r\n",
    "load_dotenv(dotenv_path=\".env\") # in case of multiple environments, it's better to mention the path\r\n",
    "api_key = os.getenv(\"API_KEY\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "- Building the URL for the api request. (PS: <u>[Official Documentation](developers.google.com/youtube/v3/docs \"YouTube Data API Reference\")</u>)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "source": [
    "# Using multiline representation of very long single-line URL (without page token) for better readability of parameters.\r\n",
    "region_code = \"IN\"   # As per documentation, follow ISO 3166-1 alpha-2 country code. For India, it is \"IN\".\r\n",
    "url1 = (\"https://youtube.googleapis.com/youtube/v3/videos?\"\r\n",
    "       \"key=\"+api_key+\r\n",
    "       \"&part=snippet%2CcontentDetails%2Cstatistics%2Cstatus%2CliveStreamingDetails%2CtopicDetails\"\r\n",
    "       \"&chart=mostPopular\"\r\n",
    "       \"&maxResults=50\"\r\n",
    "#      \"&pageToken=\"  This token value is not required for the 1st call, we will update it later for each subsequent api calls.\r\n",
    "       \"&regionCode=\"+region_code+\r\n",
    "       \"&fields=\"\r\n",
    "                \"items(\"\r\n",
    "                       \"id%2C%20\"\r\n",
    "                       \"snippet(publishedAt%2C%20channelId%2C%20title%2C%20channelTitle%2C%20tags%2C%20categoryId%2C%20defaultLanguage%2C%20defaultAudioLanguage)%2C%20\"\r\n",
    "                       \"contentDetails(duration%2C%20definition%2C%20caption)%2C%20\"\r\n",
    "                       \"status(embeddable%2C%20madeForKids)%2C%20\"\r\n",
    "                       \"statistics(viewCount%2C%20likeCount%2C%20dislikeCount%2C%20commentCount)%2C%20\"\r\n",
    "                       \"topicDetails%2FtopicCategories%2C%20\"\r\n",
    "                       \"liveStreamingDetails(actualStartTime%2CactualEndTime%2CscheduledStartTime%2CscheduledEndTime%2CconcurrentViewers))%2C%20\"\r\n",
    "                \"nextPageToken%2C%20\"\r\n",
    "                \"pageInfo\"\r\n",
    ")\r\n",
    "# Print out \"url\" to get the single-line URL for the api call (usable in any web browsers like chrome, etc.)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1. c) Calling the api, storing the response and updating the url to call more data subsequently & storing the timestamp of data collection:-\r\n",
    "<u>**Note:-**</u> There is a **limitation** of max. no. of videos per page, fetched by the HTTP request (currently **50** out of total results of around 200 *[this can be obtained from the **\"pageInfo\"** property of the result]*). So to get more videos, we need to update the ***\"pageToken\"*** parameter of the HTTP request URL and set its value same as the value of ***\"nextPageToken\"*** property of the previous result data as mentioned in the <u>[Official Pagination Guide](https://developers.google.com/youtube/v3/guides/implementation/pagination \"Pagination Implementation\")</u>. Then we need to call the api again using updated URL. Please refer to the <u>[Official Documentation](https://developers.google.com/youtube/v3/docs \"YouTube Data API Reference\")</u> for more information."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "source": [
    "data1 = requests.get(url1).json()\r\n",
    "page2_token = data1[\"nextPageToken\"]\r\n",
    "url2 = url1+\"&pageToken=\"+page2_token\r\n",
    "data2 = requests.get(url2).json()\r\n",
    "# We are stopping here as we already got 100 results.\r\n",
    "# But if anyone wants more data, they can continue updating \"ulr1\" to \"url[no. of page]\" with the \"nextPageToken\" property of the last result.\r\n",
    "# one can ideally create a loop where results can be fetched in chunks of 50 until it reaches the value of \"totalResults\" of \"pageInfo\" property.\r\n",
    "\r\n",
    "# Creating a variable that stores the time zone aware timestamp of the data collection:-\r\n",
    "current_timestamp = pd.Timestamp.utcnow()\r\n",
    "current_timestamp"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Timestamp('2021-09-25 22:23:22.831696+0000', tz='UTC')"
      ]
     },
     "metadata": {},
     "execution_count": 86
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1. d) Merging the data obtained from each page:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "source": [
    "data = data1[\"items\"]+data2[\"items\"]\r\n",
    "print(type(data))\r\n",
    "print(len(data))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'list'>\n",
      "100\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1. e) Inspecting a sample data to better understand the schema of entire result:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "source": [
    "# Using \"Pretty Print\" for better readability.\r\n",
    "pprint(data[random.randint(0,99)])"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "{'contentDetails': {'caption': 'false',\n",
      "                    'definition': 'hd',\n",
      "                    'duration': 'PT12M17S'},\n",
      " 'id': 'polPnzsvlRQ',\n",
      " 'snippet': {'categoryId': '24',\n",
      "             'channelId': 'UC9I8DWhqm5q7U_4fhfDwZIw',\n",
      "             'channelTitle': 'Arjyou',\n",
      "             'defaultAudioLanguage': 'en-IN',\n",
      "             'publishedAt': '2021-09-23T13:30:49Z',\n",
      "             'tags': ['Arjyou', 'Arjyou reaction', 'Arjyoulive'],\n",
      "             'title': \"Instagram 'Influencers' Roasted Me !!\"},\n",
      " 'statistics': {'commentCount': '17266',\n",
      "                'dislikeCount': '3972',\n",
      "                'likeCount': '288653',\n",
      "                'viewCount': '1367974'},\n",
      " 'status': {'embeddable': True, 'madeForKids': False},\n",
      " 'topicDetails': {'topicCategories': ['https://en.wikipedia.org/wiki/Lifestyle_(sociology)',\n",
      "                                      'https://en.wikipedia.org/wiki/Technology']}}\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "---\r\n",
    "# 2. Storing Data into Pandas DataFrame object for easier processing.\r\n",
    "---"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 2. a) Using ***`pd.json_normalize()`*** [ <u>[Documentation-1](https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.io.json.json_normalize.html \"pandas 0.22.0\")</u> , <u>[Documentation-2](https://pandas.pydata.org/docs/user_guide/io.html?#normalization \"pandas 1.3.1\")</u> ] to create a Pandas DataFrame from the nested data of api response:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "source": [
    "temp_df = pd.json_normalize(data)\r\n",
    "temp_df.head(2)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>id</th>\n",
       "      <th>snippet.publishedAt</th>\n",
       "      <th>snippet.channelId</th>\n",
       "      <th>snippet.title</th>\n",
       "      <th>snippet.channelTitle</th>\n",
       "      <th>snippet.tags</th>\n",
       "      <th>snippet.categoryId</th>\n",
       "      <th>snippet.defaultAudioLanguage</th>\n",
       "      <th>contentDetails.duration</th>\n",
       "      <th>contentDetails.definition</th>\n",
       "      <th>...</th>\n",
       "      <th>status.madeForKids</th>\n",
       "      <th>statistics.viewCount</th>\n",
       "      <th>statistics.likeCount</th>\n",
       "      <th>statistics.dislikeCount</th>\n",
       "      <th>statistics.commentCount</th>\n",
       "      <th>topicDetails.topicCategories</th>\n",
       "      <th>liveStreamingDetails.actualStartTime</th>\n",
       "      <th>liveStreamingDetails.actualEndTime</th>\n",
       "      <th>liveStreamingDetails.scheduledStartTime</th>\n",
       "      <th>snippet.defaultLanguage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1--qqQrimMA</td>\n",
       "      <td>2021-09-25T05:30:10Z</td>\n",
       "      <td>UCq-Fj5jknLsUf-MWSy4_brA</td>\n",
       "      <td>Dil Galti Kar Baitha Hai | Meet Bros Ft. Jubin...</td>\n",
       "      <td>T-Series</td>\n",
       "      <td>[hindi songs, 2021 hindi songs, new hindi song...</td>\n",
       "      <td>10</td>\n",
       "      <td>hi</td>\n",
       "      <td>PT5M1S</td>\n",
       "      <td>hd</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>12062420</td>\n",
       "      <td>841849</td>\n",
       "      <td>19668</td>\n",
       "      <td>53322</td>\n",
       "      <td>[https://en.wikipedia.org/wiki/Music, https://...</td>\n",
       "      <td>2021-09-25T05:30:10Z</td>\n",
       "      <td>2021-09-25T05:37:05Z</td>\n",
       "      <td>2021-09-25T05:30:00Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Llk4_F5b4aU</td>\n",
       "      <td>2021-09-23T13:00:03Z</td>\n",
       "      <td>UCn4rEMqKtwBQ6-oEwbd4PcA</td>\n",
       "      <td>Glimpses of Valimai | Ajith Kumar | Yuvan Shan...</td>\n",
       "      <td>Sony Music South</td>\n",
       "      <td>[Sony Music South, Sony Music, Latest Song, Ta...</td>\n",
       "      <td>10</td>\n",
       "      <td>en-IN</td>\n",
       "      <td>PT1M27S</td>\n",
       "      <td>hd</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8471882</td>\n",
       "      <td>839242</td>\n",
       "      <td>18684</td>\n",
       "      <td>66937</td>\n",
       "      <td>[https://en.wikipedia.org/wiki/Entertainment, ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>en-GB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            id   snippet.publishedAt         snippet.channelId  \\\n",
       "0  1--qqQrimMA  2021-09-25T05:30:10Z  UCq-Fj5jknLsUf-MWSy4_brA   \n",
       "1  Llk4_F5b4aU  2021-09-23T13:00:03Z  UCn4rEMqKtwBQ6-oEwbd4PcA   \n",
       "\n",
       "                                       snippet.title snippet.channelTitle  \\\n",
       "0  Dil Galti Kar Baitha Hai | Meet Bros Ft. Jubin...             T-Series   \n",
       "1  Glimpses of Valimai | Ajith Kumar | Yuvan Shan...     Sony Music South   \n",
       "\n",
       "                                        snippet.tags snippet.categoryId  \\\n",
       "0  [hindi songs, 2021 hindi songs, new hindi song...                 10   \n",
       "1  [Sony Music South, Sony Music, Latest Song, Ta...                 10   \n",
       "\n",
       "  snippet.defaultAudioLanguage contentDetails.duration  \\\n",
       "0                           hi                  PT5M1S   \n",
       "1                        en-IN                 PT1M27S   \n",
       "\n",
       "  contentDetails.definition  ... status.madeForKids  statistics.viewCount  \\\n",
       "0                        hd  ...              False              12062420   \n",
       "1                        hd  ...              False               8471882   \n",
       "\n",
       "   statistics.likeCount statistics.dislikeCount statistics.commentCount  \\\n",
       "0                841849                   19668                   53322   \n",
       "1                839242                   18684                   66937   \n",
       "\n",
       "                        topicDetails.topicCategories  \\\n",
       "0  [https://en.wikipedia.org/wiki/Music, https://...   \n",
       "1  [https://en.wikipedia.org/wiki/Entertainment, ...   \n",
       "\n",
       "  liveStreamingDetails.actualStartTime liveStreamingDetails.actualEndTime  \\\n",
       "0                 2021-09-25T05:30:10Z               2021-09-25T05:37:05Z   \n",
       "1                                  NaN                                NaN   \n",
       "\n",
       "  liveStreamingDetails.scheduledStartTime snippet.defaultLanguage  \n",
       "0                    2021-09-25T05:30:00Z                     NaN  \n",
       "1                                     NaN                   en-GB  \n",
       "\n",
       "[2 rows x 22 columns]"
      ]
     },
     "metadata": {},
     "execution_count": 89
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 2. b) Defining an empty DataFrame to ensure compatibility with the structure of all possible instances of collected data:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "source": [
    "df = pd.DataFrame(columns=[\"Entry_Timestamp\",\"Video_ID\",\"Title\",\"Channel_Name\",\"Channel_ID\",\"Published_At\",\r\n",
    "                           \"Title_Language\",\"Audio_Language\",\"Duration\",\"Quality\",\"Views\",\"Likes\",\"Dislikes\",\r\n",
    "                           \"Comments\",\"Live_Start_Real\",\"Live_End_Real\",\"Live_Start_Scheduled\",\"Live_End_Scheduled\",\r\n",
    "                           \"Live_Viewers\",\"CC\",\"Tags\",\"Category_ID\",\"Embeddable\",\"Made_for_Kids\",\"Topic_Links\"])\r\n",
    "\r\n",
    "pprint(df)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Empty DataFrame\n",
      "Columns: [Entry_Timestamp, Video_ID, Title, Channel_Name, Channel_ID, Published_At, Title_Language, Audio_Language, Duration, Quality, Views, Likes, Dislikes, Comments, Live_Start_Real, Live_End_Real, Live_Start_Scheduled, Live_End_Scheduled, Live_Viewers, CC, Tags, Category_ID, Embeddable, Made_for_Kids, Topic_Links]\n",
      "Index: []\n",
      "\n",
      "[0 rows x 25 columns]\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 2. c) Renaming the columns of temporary DataFrame to properly append it to the actual empty DataFrame:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "source": [
    "print(temp_df.columns,df.columns,sep=\"\\n\")"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Index(['id', 'snippet.publishedAt', 'snippet.channelId', 'snippet.title',\n",
      "       'snippet.channelTitle', 'snippet.tags', 'snippet.categoryId',\n",
      "       'snippet.defaultAudioLanguage', 'contentDetails.duration',\n",
      "       'contentDetails.definition', 'contentDetails.caption',\n",
      "       'status.embeddable', 'status.madeForKids', 'statistics.viewCount',\n",
      "       'statistics.likeCount', 'statistics.dislikeCount',\n",
      "       'statistics.commentCount', 'topicDetails.topicCategories',\n",
      "       'liveStreamingDetails.actualStartTime',\n",
      "       'liveStreamingDetails.actualEndTime',\n",
      "       'liveStreamingDetails.scheduledStartTime', 'snippet.defaultLanguage'],\n",
      "      dtype='object')\n",
      "Index(['Entry_Timestamp', 'Video_ID', 'Title', 'Channel_Name', 'Channel_ID',\n",
      "       'Published_At', 'Title_Language', 'Audio_Language', 'Duration',\n",
      "       'Quality', 'Views', 'Likes', 'Dislikes', 'Comments', 'Live_Start_Real',\n",
      "       'Live_End_Real', 'Live_Start_Scheduled', 'Live_End_Scheduled',\n",
      "       'Live_Viewers', 'CC', 'Tags', 'Category_ID', 'Embeddable',\n",
      "       'Made_for_Kids', 'Topic_Links'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "source": [
    "temp_df = temp_df.rename(columns={\r\n",
    "                                    'id':'Video_ID',\r\n",
    "                                    'snippet.publishedAt':'Published_At','snippet.channelId':'Channel_ID','snippet.title':'Title',\r\n",
    "                                    'snippet.channelTitle':'Channel_Name', 'snippet.tags':'Tags', 'snippet.categoryId':'Category_ID',\r\n",
    "                                    'snippet.defaultAudioLanguage':'Audio_Language', 'contentDetails.duration':'Duration',\r\n",
    "                                    'contentDetails.definition':'Quality', 'contentDetails.caption':'CC',\r\n",
    "                                    'status.embeddable':'Embeddable', 'status.madeForKids':'Made_for_Kids', 'statistics.viewCount':'Views',\r\n",
    "                                    'statistics.likeCount':'Likes', 'statistics.dislikeCount':'Dislikes',\r\n",
    "                                    'statistics.commentCount':'Comments', 'topicDetails.topicCategories':'Topic_Links',\r\n",
    "                                    'liveStreamingDetails.actualStartTime':'Live_Start_Real',\r\n",
    "                                    'liveStreamingDetails.actualEndTime':'Live_End_Real',\r\n",
    "                                    'liveStreamingDetails.scheduledStartTime':'Live_Start_Scheduled', 'snippet.defaultLanguage':'Title_Language'\r\n",
    "})"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "source": [
    "temp_df.head(1)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Video_ID</th>\n",
       "      <th>Published_At</th>\n",
       "      <th>Channel_ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Channel_Name</th>\n",
       "      <th>Tags</th>\n",
       "      <th>Category_ID</th>\n",
       "      <th>Audio_Language</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Quality</th>\n",
       "      <th>...</th>\n",
       "      <th>Made_for_Kids</th>\n",
       "      <th>Views</th>\n",
       "      <th>Likes</th>\n",
       "      <th>Dislikes</th>\n",
       "      <th>Comments</th>\n",
       "      <th>Topic_Links</th>\n",
       "      <th>Live_Start_Real</th>\n",
       "      <th>Live_End_Real</th>\n",
       "      <th>Live_Start_Scheduled</th>\n",
       "      <th>Title_Language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1--qqQrimMA</td>\n",
       "      <td>2021-09-25T05:30:10Z</td>\n",
       "      <td>UCq-Fj5jknLsUf-MWSy4_brA</td>\n",
       "      <td>Dil Galti Kar Baitha Hai | Meet Bros Ft. Jubin...</td>\n",
       "      <td>T-Series</td>\n",
       "      <td>[hindi songs, 2021 hindi songs, new hindi song...</td>\n",
       "      <td>10</td>\n",
       "      <td>hi</td>\n",
       "      <td>PT5M1S</td>\n",
       "      <td>hd</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>12062420</td>\n",
       "      <td>841849</td>\n",
       "      <td>19668</td>\n",
       "      <td>53322</td>\n",
       "      <td>[https://en.wikipedia.org/wiki/Music, https://...</td>\n",
       "      <td>2021-09-25T05:30:10Z</td>\n",
       "      <td>2021-09-25T05:37:05Z</td>\n",
       "      <td>2021-09-25T05:30:00Z</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Video_ID          Published_At                Channel_ID  \\\n",
       "0  1--qqQrimMA  2021-09-25T05:30:10Z  UCq-Fj5jknLsUf-MWSy4_brA   \n",
       "\n",
       "                                               Title Channel_Name  \\\n",
       "0  Dil Galti Kar Baitha Hai | Meet Bros Ft. Jubin...     T-Series   \n",
       "\n",
       "                                                Tags Category_ID  \\\n",
       "0  [hindi songs, 2021 hindi songs, new hindi song...          10   \n",
       "\n",
       "  Audio_Language Duration Quality  ... Made_for_Kids     Views   Likes  \\\n",
       "0             hi   PT5M1S      hd  ...         False  12062420  841849   \n",
       "\n",
       "  Dislikes Comments                                        Topic_Links  \\\n",
       "0    19668    53322  [https://en.wikipedia.org/wiki/Music, https://...   \n",
       "\n",
       "        Live_Start_Real         Live_End_Real  Live_Start_Scheduled  \\\n",
       "0  2021-09-25T05:30:10Z  2021-09-25T05:37:05Z  2021-09-25T05:30:00Z   \n",
       "\n",
       "  Title_Language  \n",
       "0            NaN  \n",
       "\n",
       "[1 rows x 22 columns]"
      ]
     },
     "metadata": {},
     "execution_count": 93
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 2. d) Appending temporary DataFrame to main, structured and well-defined DataFrame ( **to handle rare cases of missing values of an entire column** ):-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "source": [
    "df = df.append(temp_df,ignore_index=True)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 2. e) Adding the value for **Entry_Timestamp** column stored in the variable called ***current_timestamp*** :-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "source": [
    "df[\"Entry_Timestamp\"] = current_timestamp\r\n",
    "print(df.iloc[0,:])"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Entry_Timestamp                          2021-09-25 22:23:22.831696+00:00\n",
      "Video_ID                                                      1--qqQrimMA\n",
      "Title                   Dil Galti Kar Baitha Hai | Meet Bros Ft. Jubin...\n",
      "Channel_Name                                                     T-Series\n",
      "Channel_ID                                       UCq-Fj5jknLsUf-MWSy4_brA\n",
      "Published_At                                         2021-09-25T05:30:10Z\n",
      "Title_Language                                                        NaN\n",
      "Audio_Language                                                         hi\n",
      "Duration                                                           PT5M1S\n",
      "Quality                                                                hd\n",
      "Views                                                            12062420\n",
      "Likes                                                              841849\n",
      "Dislikes                                                            19668\n",
      "Comments                                                            53322\n",
      "Live_Start_Real                                      2021-09-25T05:30:10Z\n",
      "Live_End_Real                                        2021-09-25T05:37:05Z\n",
      "Live_Start_Scheduled                                 2021-09-25T05:30:00Z\n",
      "Live_End_Scheduled                                                    NaN\n",
      "Live_Viewers                                                          NaN\n",
      "CC                                                                  false\n",
      "Tags                    [hindi songs, 2021 hindi songs, new hindi song...\n",
      "Category_ID                                                            10\n",
      "Embeddable                                                           True\n",
      "Made_for_Kids                                                       False\n",
      "Topic_Links             [https://en.wikipedia.org/wiki/Music, https://...\n",
      "Name: 0, dtype: object\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "---\r\n",
    "# 3. Data Cleansing and Data Wrangling.\r\n",
    "---"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### <li>Saving a ***`deep`*** copy of actual DataFrame to another variable *df1* [ Optional ] and **Inspecting** our data.</li>"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "source": [
    "df1 = df.copy()\r\n",
    "df.head(2)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "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>Entry_Timestamp</th>\n",
       "      <th>Video_ID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Channel_Name</th>\n",
       "      <th>Channel_ID</th>\n",
       "      <th>Published_At</th>\n",
       "      <th>Title_Language</th>\n",
       "      <th>Audio_Language</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Quality</th>\n",
       "      <th>...</th>\n",
       "      <th>Live_End_Real</th>\n",
       "      <th>Live_Start_Scheduled</th>\n",
       "      <th>Live_End_Scheduled</th>\n",
       "      <th>Live_Viewers</th>\n",
       "      <th>CC</th>\n",
       "      <th>Tags</th>\n",
       "      <th>Category_ID</th>\n",
       "      <th>Embeddable</th>\n",
       "      <th>Made_for_Kids</th>\n",
       "      <th>Topic_Links</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-09-25 22:23:22.831696+00:00</td>\n",
       "      <td>1--qqQrimMA</td>\n",
       "      <td>Dil Galti Kar Baitha Hai | Meet Bros Ft. Jubin...</td>\n",
       "      <td>T-Series</td>\n",
       "      <td>UCq-Fj5jknLsUf-MWSy4_brA</td>\n",
       "      <td>2021-09-25T05:30:10Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>hi</td>\n",
       "      <td>PT5M1S</td>\n",
       "      <td>hd</td>\n",
       "      <td>...</td>\n",
       "      <td>2021-09-25T05:37:05Z</td>\n",
       "      <td>2021-09-25T05:30:00Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>false</td>\n",
       "      <td>[hindi songs, 2021 hindi songs, new hindi song...</td>\n",
       "      <td>10</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>[https://en.wikipedia.org/wiki/Music, https://...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-09-25 22:23:22.831696+00:00</td>\n",
       "      <td>Llk4_F5b4aU</td>\n",
       "      <td>Glimpses of Valimai | Ajith Kumar | Yuvan Shan...</td>\n",
       "      <td>Sony Music South</td>\n",
       "      <td>UCn4rEMqKtwBQ6-oEwbd4PcA</td>\n",
       "      <td>2021-09-23T13:00:03Z</td>\n",
       "      <td>en-GB</td>\n",
       "      <td>en-IN</td>\n",
       "      <td>PT1M27S</td>\n",
       "      <td>hd</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>true</td>\n",
       "      <td>[Sony Music South, Sony Music, Latest Song, Ta...</td>\n",
       "      <td>10</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>[https://en.wikipedia.org/wiki/Entertainment, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Entry_Timestamp     Video_ID  \\\n",
       "0 2021-09-25 22:23:22.831696+00:00  1--qqQrimMA   \n",
       "1 2021-09-25 22:23:22.831696+00:00  Llk4_F5b4aU   \n",
       "\n",
       "                                               Title      Channel_Name  \\\n",
       "0  Dil Galti Kar Baitha Hai | Meet Bros Ft. Jubin...          T-Series   \n",
       "1  Glimpses of Valimai | Ajith Kumar | Yuvan Shan...  Sony Music South   \n",
       "\n",
       "                 Channel_ID          Published_At Title_Language  \\\n",
       "0  UCq-Fj5jknLsUf-MWSy4_brA  2021-09-25T05:30:10Z            NaN   \n",
       "1  UCn4rEMqKtwBQ6-oEwbd4PcA  2021-09-23T13:00:03Z          en-GB   \n",
       "\n",
       "  Audio_Language Duration Quality  ...         Live_End_Real  \\\n",
       "0             hi   PT5M1S      hd  ...  2021-09-25T05:37:05Z   \n",
       "1          en-IN  PT1M27S      hd  ...                   NaN   \n",
       "\n",
       "   Live_Start_Scheduled Live_End_Scheduled Live_Viewers     CC  \\\n",
       "0  2021-09-25T05:30:00Z                NaN          NaN  false   \n",
       "1                   NaN                NaN          NaN   true   \n",
       "\n",
       "                                                Tags Category_ID Embeddable  \\\n",
       "0  [hindi songs, 2021 hindi songs, new hindi song...          10       True   \n",
       "1  [Sony Music South, Sony Music, Latest Song, Ta...          10       True   \n",
       "\n",
       "  Made_for_Kids                                        Topic_Links  \n",
       "0         False  [https://en.wikipedia.org/wiki/Music, https://...  \n",
       "1         False  [https://en.wikipedia.org/wiki/Entertainment, ...  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "metadata": {},
     "execution_count": 96
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "source": [
    "df.info()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100 entries, 0 to 99\n",
      "Data columns (total 25 columns):\n",
      " #   Column                Non-Null Count  Dtype              \n",
      "---  ------                --------------  -----              \n",
      " 0   Entry_Timestamp       100 non-null    datetime64[ns, UTC]\n",
      " 1   Video_ID              100 non-null    object             \n",
      " 2   Title                 100 non-null    object             \n",
      " 3   Channel_Name          100 non-null    object             \n",
      " 4   Channel_ID            100 non-null    object             \n",
      " 5   Published_At          100 non-null    object             \n",
      " 6   Title_Language        21 non-null     object             \n",
      " 7   Audio_Language        72 non-null     object             \n",
      " 8   Duration              100 non-null    object             \n",
      " 9   Quality               100 non-null    object             \n",
      " 10  Views                 100 non-null    object             \n",
      " 11  Likes                 100 non-null    object             \n",
      " 12  Dislikes              100 non-null    object             \n",
      " 13  Comments              99 non-null     object             \n",
      " 14  Live_Start_Real       23 non-null     object             \n",
      " 15  Live_End_Real         23 non-null     object             \n",
      " 16  Live_Start_Scheduled  22 non-null     object             \n",
      " 17  Live_End_Scheduled    0 non-null      object             \n",
      " 18  Live_Viewers          0 non-null      object             \n",
      " 19  CC                    100 non-null    object             \n",
      " 20  Tags                  83 non-null     object             \n",
      " 21  Category_ID           100 non-null    object             \n",
      " 22  Embeddable            100 non-null    object             \n",
      " 23  Made_for_Kids         100 non-null    object             \n",
      " 24  Topic_Links           98 non-null     object             \n",
      "dtypes: datetime64[ns, UTC](1), object(24)\n",
      "memory usage: 19.7+ KB\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 3. a) Adding calculated columns from the existing columns that contains ***`list` elements*** ( e.g.- **Tags** , **Topic_Links** ):-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "1. Handling **Tags** column ( problematic ***`NaN`*** values with ***`float`*** datatype + ***List*** items with ***`list`*** datatype )."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "source": [
    "# Defining a function to overcome errors caused by NaN values as they don't have len() attribute.\r\n",
    "def count_tags(lst):\r\n",
    "    if type(lst) is list:\r\n",
    "        return len(lst)\r\n",
    "    elif lst is np.nan:\r\n",
    "        return pd.NA\r\n",
    "    else:\r\n",
    "        print(lst,type(lst),'This Tag item is not a list or NaN', sep='\\n')\r\n",
    "\r\n",
    "# Applying the function on \"Tags\" column to create \"No_of_Tags\" column.\r\n",
    "df.insert(df.columns.get_loc(\"Tags\")+1,\"No_of_Tags\", df[\"Tags\"].apply(count_tags))\r\n",
    "df[\"No_of_Tags\"]"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0       19\n",
       "1       25\n",
       "2     <NA>\n",
       "3       28\n",
       "4     <NA>\n",
       "      ... \n",
       "95       5\n",
       "96    <NA>\n",
       "97    <NA>\n",
       "98      16\n",
       "99    <NA>\n",
       "Name: No_of_Tags, Length: 100, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 98
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "source": [
    "# Defining a function to overcome errors caused by \"[\" symbol of List items.\r\n",
    "def list_to_text(lst):\r\n",
    "    if type(lst) is list:\r\n",
    "        return (\"{\"+\"}, {\".join([i for i in lst])+\"}\")\r\n",
    "    elif lst is np.nan:\r\n",
    "        return pd.NA\r\n",
    "    else:\r\n",
    "        print(lst,type(lst),'This Tag item is not a list or NaN', sep='\\n')\r\n",
    "\r\n",
    "# Applying the function on \"Tags\" column to convert its items from List to Text.\r\n",
    "# This is done to handle incompatibility between syntax of the Database, insert method chosen and python list representation with \"[\" symbol.\r\n",
    "df[\"Tags\"] = df[\"Tags\"].apply(list_to_text)\r\n",
    "df[\"Tags\"]"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0     {hindi songs}, {2021 hindi songs}, {new hindi ...\n",
       "1     {Sony Music South}, {Sony Music}, {Latest Song...\n",
       "2                                                  <NA>\n",
       "3     {otv}, {otv news}, {odisha tv}, {odishatv}, {o...\n",
       "4                                                  <NA>\n",
       "                            ...                        \n",
       "95    {Australia}, {Beth Mooney}, {India}, {Mackay},...\n",
       "96                                                 <NA>\n",
       "97                                                 <NA>\n",
       "98    {buying all biscuits from supermarket}, {buyin...\n",
       "99                                                 <NA>\n",
       "Name: Tags, Length: 100, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 99
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. Handling **Topic_Links** column ( problematic ***`NaN`*** values with ***`float`*** datatype + ***List*** items with ***`list`*** datatype and unwanted *URL* parts)."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "source": [
    "# Defining a function to overcome errors caused by NaN values.\r\n",
    "def get_links(lst):\r\n",
    "    if type(lst) is list:\r\n",
    "        return (\", \".join([i.replace(\"https://en.wikipedia.org/wiki/\",\"\") for i in lst]))\r\n",
    "    elif lst is np.nan:\r\n",
    "        return pd.NA\r\n",
    "    else:\r\n",
    "        print(lst,type(lst),'This Topic_Links item is not a list or NaN', sep='\\n')\r\n",
    "\r\n",
    "# Applying the function on \"Topic_Links\" column to create \"Topics\" column.\r\n",
    "df[\"Topics\"] = df[\"Topic_Links\"].apply(get_links)\r\n",
    "df[\"Topics\"]"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0                                  Music, Music_of_Asia\n",
       "1                                   Entertainment, Film\n",
       "2                                 Lifestyle_(sociology)\n",
       "3                                               Society\n",
       "4                                 Lifestyle_(sociology)\n",
       "                            ...                        \n",
       "95                                       Cricket, Sport\n",
       "96                          Food, Lifestyle_(sociology)\n",
       "97                                   Video_game_culture\n",
       "98                          Food, Lifestyle_(sociology)\n",
       "99    Action-adventure_game, Action_game, Role-playi...\n",
       "Name: Topics, Length: 100, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 100
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "3. Adding the **Rank** column to later perform **detailed** analysis of popular videos of different datetimes."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "source": [
    "df.insert(0,\"Rank\",range(1,101))"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "4. Dropping **unwanted** / **redundant** columns ( e.g.- **Topic_Links** ) that was earlier required to get the calculated columns."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "source": [
    "df.drop(columns=[\"Topic_Links\"],inplace=True)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "5. Noting down the mean **memory usage** per column of our DataFrame."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "source": [
    "memory_1 = round(df.memory_usage(deep=True).sum()/df.columns.size)\r\n",
    "memory_1"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "7083"
      ]
     },
     "metadata": {},
     "execution_count": 103
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 3. b) Transforming data-formats ( e.g.- ***time-format***, ***duration-format***, ***language-format*** ) and data-types ( e.g.- ***`strings`*** ) to appropriate and interpretable format:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "1. Parsing columns containing Date-Time information, as timezone-aware ***`datetime.datetime`*** object for easier access to its details of *date*, *month*, *year*, *hour*, *minute*, *second* and *time-zone*."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "source": [
    "df[\"Published_At\"]          = pd.to_datetime(df[\"Published_At\"],errors=\"coerce\",utc=True)\r\n",
    "df[\"Live_Start_Real\"]       = pd.to_datetime(df[\"Live_Start_Real\"],errors=\"coerce\",utc=True)\r\n",
    "df[\"Live_End_Real\"]         = pd.to_datetime(df[\"Live_End_Real\"],errors=\"coerce\",utc=True)\r\n",
    "df[\"Live_Start_Scheduled\"]  = pd.to_datetime(df[\"Live_Start_Scheduled\"],errors=\"coerce\",utc=True)\r\n",
    "df[\"Live_End_Scheduled\"]    = pd.to_datetime(df[\"Live_End_Scheduled\"],errors=\"coerce\",utc=True)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. Decoding the YouTube language codes [ [i18nLanguages Documentation](https://developers.google.com/youtube/v3/docs/i18nLanguages \"YouTube Supported Codes for Languages\") ] of **Title_Language** and **Audio_Language** columns."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. \r\n",
    "    1. Preparing the reference table / DataFrame for Language codes using different YouTube api."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "source": [
    "lang_url = \"https://youtube.googleapis.com/youtube/v3/i18nLanguages?part=snippet&prettyPrint=true&fields=items%2Fsnippet&key=\"+api_key\r\n",
    "lang_data = requests.get(lang_url).json()\r\n",
    "lang_df = pd.json_normalize(lang_data[\"items\"])\r\n",
    "lang_df.columns = [\"Language_Code\",\"Language\"]\r\n",
    "lang_df = lang_df.convert_dtypes()\r\n",
    "print(lang_df.nunique(),sep='\\n')\r\n",
    "lang_df.head()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Language_Code    83\n",
      "Language         83\n",
      "dtype: int64\n"
     ]
    },
    {
     "output_type": "execute_result",
     "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>Language_Code</th>\n",
       "      <th>Language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>af</td>\n",
       "      <td>Afrikaans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>am</td>\n",
       "      <td>Amharic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ar</td>\n",
       "      <td>Arabic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>as</td>\n",
       "      <td>Assamese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>az</td>\n",
       "      <td>Azerbaijani</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Language_Code     Language\n",
       "0            af    Afrikaans\n",
       "1            am      Amharic\n",
       "2            ar       Arabic\n",
       "3            as     Assamese\n",
       "4            az  Azerbaijani"
      ]
     },
     "metadata": {},
     "execution_count": 105
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. \r\n",
    "    2. Decoding columns using refence DataFrame."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "source": [
    "temp_df = df[[\"Title_Language\"]].merge(lang_df,how=\"left\",left_on=\"Title_Language\",right_on=\"Language_Code\")[\"Language\"]\r\n",
    "df.insert(df.columns.get_loc(\"Title_Language\")+1,\"Title_Language_Name\",temp_df)\r\n",
    "temp_df = df[[\"Audio_Language\"]].merge(lang_df,how=\"left\",left_on=\"Audio_Language\",right_on=\"Language_Code\")[\"Language\"]\r\n",
    "df.insert(df.columns.get_loc(\"Audio_Language\")+1,\"Audio_Language_Name\",temp_df)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. \r\n",
    "    3. Assigning fixed Language code ***\"zxx\"*** to all **out of reference** language codes ( [ISO639 Documentation](https://iso639-3.sil.org/code/zxx \"ISO 639-3 Identifier Documentation: zxx\") )."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "source": [
    "df.loc[(df[\"Title_Language\"].notnull()) & (df[\"Title_Language_Name\"].isnull()),[\"Title_Language\"]] = \"zxx\"\r\n",
    "df.loc[(df[\"Title_Language\"].notnull()) & (df[\"Title_Language_Name\"].isnull()),[\"Title_Language_Name\"]] = \"Not Applicable\"\r\n",
    "df.loc[(df[\"Audio_Language\"].notnull()) & (df[\"Audio_Language_Name\"].isnull()),[\"Audio_Language\"]] = \"zxx\"\r\n",
    "df.loc[(df[\"Audio_Language\"].notnull()) & (df[\"Audio_Language_Name\"].isnull()),[\"Audio_Language_Name\"]] = \"Not Applicable\""
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "3. Inspecting the structure of the DataFrame to start converting datatypes if preferrable and possible."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "source": [
    "df.info()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100 entries, 0 to 99\n",
      "Data columns (total 29 columns):\n",
      " #   Column                Non-Null Count  Dtype              \n",
      "---  ------                --------------  -----              \n",
      " 0   Rank                  100 non-null    int32              \n",
      " 1   Entry_Timestamp       100 non-null    datetime64[ns, UTC]\n",
      " 2   Video_ID              100 non-null    object             \n",
      " 3   Title                 100 non-null    object             \n",
      " 4   Channel_Name          100 non-null    object             \n",
      " 5   Channel_ID            100 non-null    object             \n",
      " 6   Published_At          100 non-null    datetime64[ns, UTC]\n",
      " 7   Title_Language        21 non-null     object             \n",
      " 8   Title_Language_Name   21 non-null     string             \n",
      " 9   Audio_Language        72 non-null     object             \n",
      " 10  Audio_Language_Name   72 non-null     string             \n",
      " 11  Duration              100 non-null    object             \n",
      " 12  Quality               100 non-null    object             \n",
      " 13  Views                 100 non-null    object             \n",
      " 14  Likes                 100 non-null    object             \n",
      " 15  Dislikes              100 non-null    object             \n",
      " 16  Comments              99 non-null     object             \n",
      " 17  Live_Start_Real       23 non-null     datetime64[ns, UTC]\n",
      " 18  Live_End_Real         23 non-null     datetime64[ns, UTC]\n",
      " 19  Live_Start_Scheduled  22 non-null     datetime64[ns, UTC]\n",
      " 20  Live_End_Scheduled    0 non-null      datetime64[ns, UTC]\n",
      " 21  Live_Viewers          0 non-null      object             \n",
      " 22  CC                    100 non-null    object             \n",
      " 23  Tags                  83 non-null     object             \n",
      " 24  No_of_Tags            83 non-null     object             \n",
      " 25  Category_ID           100 non-null    object             \n",
      " 26  Embeddable            100 non-null    object             \n",
      " 27  Made_for_Kids         100 non-null    object             \n",
      " 28  Topics                98 non-null     object             \n",
      "dtypes: datetime64[ns, UTC](6), int32(1), object(20), string(2)\n",
      "memory usage: 22.4+ KB\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "4. Automatically converting column datatypes to best possible datatypes, supporting ***`pd.NA `***."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "source": [
    "df = df.convert_dtypes()\r\n",
    "df.info()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100 entries, 0 to 99\n",
      "Data columns (total 29 columns):\n",
      " #   Column                Non-Null Count  Dtype              \n",
      "---  ------                --------------  -----              \n",
      " 0   Rank                  100 non-null    Int32              \n",
      " 1   Entry_Timestamp       100 non-null    datetime64[ns, UTC]\n",
      " 2   Video_ID              100 non-null    string             \n",
      " 3   Title                 100 non-null    string             \n",
      " 4   Channel_Name          100 non-null    string             \n",
      " 5   Channel_ID            100 non-null    string             \n",
      " 6   Published_At          100 non-null    datetime64[ns, UTC]\n",
      " 7   Title_Language        21 non-null     string             \n",
      " 8   Title_Language_Name   21 non-null     string             \n",
      " 9   Audio_Language        72 non-null     string             \n",
      " 10  Audio_Language_Name   72 non-null     string             \n",
      " 11  Duration              100 non-null    string             \n",
      " 12  Quality               100 non-null    string             \n",
      " 13  Views                 100 non-null    string             \n",
      " 14  Likes                 100 non-null    string             \n",
      " 15  Dislikes              100 non-null    string             \n",
      " 16  Comments              99 non-null     string             \n",
      " 17  Live_Start_Real       23 non-null     datetime64[ns, UTC]\n",
      " 18  Live_End_Real         23 non-null     datetime64[ns, UTC]\n",
      " 19  Live_Start_Scheduled  22 non-null     datetime64[ns, UTC]\n",
      " 20  Live_End_Scheduled    0 non-null      datetime64[ns, UTC]\n",
      " 21  Live_Viewers          0 non-null      Int64              \n",
      " 22  CC                    100 non-null    string             \n",
      " 23  Tags                  83 non-null     string             \n",
      " 24  No_of_Tags            83 non-null     object             \n",
      " 25  Category_ID           100 non-null    string             \n",
      " 26  Embeddable            100 non-null    boolean            \n",
      " 27  Made_for_Kids         100 non-null    boolean            \n",
      " 28  Topics                98 non-null     string             \n",
      "dtypes: Int32(1), Int64(1), boolean(2), datetime64[ns, UTC](6), object(1), string(18)\n",
      "memory usage: 21.4+ KB\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "5. Parsing **Duration** column as python ***`datetime.timedelta`*** object type.</br>\r\n",
    "( This is intentionally done after using ***`convert_dtypes`***. Otherwise, the ***`timedelta64[ns]`*** datatype will also be converted to unwanted, very large ( timedelta as in ***nano-second*** scale ) ***`float64`*** datatype. )"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "source": [
    "df[\"Duration\"] = df[\"Duration\"].apply(pd.Timedelta)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "6. Changing the ***`string`*** datatype of ***boolean*** column ( e.g.- **CC** ) to ***`bool`*** datatype:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "source": [
    "df[\"CC\"] = df[\"CC\"].map({\"true\":True,\"false\":False})"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "7. Changing the ***`string`*** datatype of ***numeric*** columns ( e.g.- **Views** , **Likes** ) to ***`integer`*** datatype:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "source": [
    "df.loc[:,\"Views\":\"Comments\"] = df.loc[:,\"Views\":\"Comments\"].astype(\"Int64\")\r\n",
    "df[\"No_of_Tags\"] = df[\"No_of_Tags\"].astype(\"UInt16\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "8. Modifying the ***`Integer`*** datatypes to **memory-efficient** integer datatypes."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "source": [
    "df[\"Live_Viewers\"] = df[\"Live_Viewers\"].astype(\"UInt32\")\r\n",
    "df[\"Rank\"] = df[\"Rank\"].astype(\"UInt8\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "9. Converting suitable columns ( e.g.- **Quality**, **Audio_Language** ) to memory efficient ***`categorical`*** datatype."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "source": [
    "df[\"Quality\"] = df[\"Quality\"].astype('category')\r\n",
    "df.loc[:,\"Title_Language\":\"Audio_Language_Name\"] = df.loc[:,\"Title_Language\":\"Audio_Language_Name\"].astype('category')\r\n",
    "df[\"Category_ID\"] = df[\"Category_ID\"].astype('category')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "10. Noting down the mean **data usage** per column of the final, processed DataFrame."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "source": [
    "memory_2 = round(df.memory_usage(deep=True).sum()/df.columns.size)\r\n",
    "memory_2"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "3831"
      ]
     },
     "metadata": {},
     "execution_count": 115
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 3. c) Calculating **memory efficiency** obtained by processing the DataFrame:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "source": [
    "memory_efficiency = round((memory_1-memory_2)*100/memory_1)\r\n",
    "print(f\"----- Total memory usage is reduced by {memory_efficiency}% -----\")"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "----- Total memory usage is reduced by 46% -----\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "---\r\n",
    "# 4. Sending Data to remote PostgreSQL database on Compute Engine of Google Cloud Platform ( *Using only free tier products* ).\r\n",
    "---"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "<p>In all practical cases, it is common to store the collected data after cleaning as historical data in a database like a Data-Warehouse. And then fetch the required data directly from the database anytime for analysis purposes. This allows us to analize data that were collected at different timestamps and discover trends and patterns in our data. Now, as our data is already cleaned, it is ready and waiting to be sent to the Data-Warehouse.</br>\r\n",
    "The reason for choosing a <b>Cloud Database</b> over local database is that, due to increasing volume of data, it is better to shift to cloud for easier handling of all loads and also running local server <b>24*7</b> is not possible for most of the individuals.</p>"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4. a) Loading Database credentials from ***`.env `*** file:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "source": [
    "host_name = os.getenv(\"HOST_NAME\")\r\n",
    "db_name = os.getenv(\"DB_NAME\")\r\n",
    "port = os.getenv(\"PORT\")\r\n",
    "user_name = os.getenv(\"USER_NAME\")\r\n",
    "password = os.getenv(\"PASSWORD\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4. b) Connecting to **Cloud Database** using ***`psycopg2 `*** and creating the ***`cursor()`*** for executing **SQL** queries:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "source": [
    "# Establishing connection.\r\n",
    "try:\r\n",
    "    connection = spg.connect(host=host_name, database=db_name, port=port, user=user_name, password=password)\r\n",
    "except spg.OperationalError as error:\r\n",
    "    raise error\r\n",
    "else:\r\n",
    "    print(\"Successfully connected to the PostgreSQL database!\")\r\n",
    "\r\n",
    "# Creating cursor.\r\n",
    "cursor = connection.cursor()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Successfully connected to the PostgreSQL database!\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4. c) Creating table in the database:-"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "1. Creating PostgreSQL **`ENUM DATATYPE` s;** required ( [See Documentation](https://www.postgresql.org/docs/12/datatype-enum.html \"PostgreSQL Official Documentation\") ) for assigning **Enumerated** datatypes to table columns."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "source": [
    "%%script echo 'Skipping executing codes of this cell as it is to be run only once while other cells are to be run everytime to collect data from API.'\r\n",
    "# Please comment out above line to execute all following codes of this cell.\r\n",
    "# But again uncomment it to stop executing below codes as otherwise the database will throw an error.\r\n",
    "\r\n",
    "# Creating tuples required to define enum columns.\r\n",
    "lang_enum      = tuple(lang_df.Language_Code.values)\r\n",
    "quality_enum   = (\"sd\",\"hd\")\r\n",
    "lang_name_enum = tuple(lang_df.Language.values)\r\n",
    "\r\n",
    "# Writing SQL Queries.\r\n",
    "create_type_lang      = (f\"CREATE TYPE LANG AS ENUM {lang_enum};\"\r\n",
    "                          \"ALTER TYPE LANG ADD VALUE 'zxx'\")                    # Adding the ISO639 specified code \"zxx\" in our enum type.\r\n",
    "create_type_lang_name = (f\"CREATE TYPE LANG_NAME AS ENUM {lang_name_enum};\"\r\n",
    "                          \"ALTER TYPE LANG_NAME ADD VALUE 'Not Applicable'\")    # Adding the interpretation of code \"zxx\" in our enum type.\r\n",
    "create_type_quality   = (f\"CREATE TYPE QUALITY AS ENUM {quality_enum}\")\r\n",
    "\r\n",
    "# Executing Queries in the database.\r\n",
    "try:\r\n",
    "    cursor.execute(create_type_lang)\r\n",
    "    cursor.execute(create_type_lang_name)\r\n",
    "    cursor.execute(create_type_quality)\r\n",
    "    cursor.execute(\"COMMIT\")\r\n",
    "except (Exception, spg.DatabaseError) as error:\r\n",
    "    print(f\"Following error(s) occured : {error}\")\r\n",
    "    cursor.execute(\"ROLLBACK\")"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Skipping executing codes of this cell as it is to be run only once while other cells are to be run everytime to collect data from API.\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. Creating empty database table with columns assigned proper datatypes."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "source": [
    "# Writing SQL command.\r\n",
    "create_table = (\"\"\"CREATE TABLE IF NOT EXISTS \r\n",
    "                   YT_POPULAR_VIDEOS (\r\n",
    "                      \r\n",
    "                      RANK                   SMALLINT     NOT NULL,\r\n",
    "                      ENTRY_TIMESTAMP        TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n",
    "                      VIDEO_ID               VARCHAR(255) NOT NULL,\r\n",
    "                      TITLE                  VARCHAR(255) NOT NULL,\r\n",
    "                      CHANNEL_NAME           VARCHAR(255) NOT NULL,\r\n",
    "                      CHANNEL_ID             VARCHAR(255) NOT NULL,\r\n",
    "                      PUBLISHED_AT           TIMESTAMPTZ  NOT NULL,\r\n",
    "                      TITLE_LANGUAGE         LANG,\r\n",
    "                      TITLE_LANGUAGE_NAME    LANG_NAME,\r\n",
    "                      AUDIO_LANGUAGE         LANG,\r\n",
    "                      AUDIO_LANGUAGE_NAME    LANG_NAME,\r\n",
    "                      DURATION               INTERVAL,\r\n",
    "                      QUALITY                QUALITY      NOT NULL,\r\n",
    "                      VIEWS                  BIGINT,\r\n",
    "                      LIKES                  BIGINT,\r\n",
    "                      DISLIKES               BIGINT,\r\n",
    "                      COMMENTS               BIGINT,\r\n",
    "                      LIVE_START_REAL        TIMESTAMPTZ,\r\n",
    "                      LIVE_END_REAL          TIMESTAMPTZ,\r\n",
    "                      LIVE_START_SCHEDULED   TIMESTAMPTZ,\r\n",
    "                      LIVE_END_SCHEDULED     TIMESTAMPTZ,\r\n",
    "                      LIVE_VIEWERS           INTEGER,\r\n",
    "                      CC                     BOOLEAN,\r\n",
    "                      TAGS                   TEXT,\r\n",
    "                      NO_OF_TAGS             INTEGER,\r\n",
    "                      CATEGORY_ID            VARCHAR(255),\r\n",
    "                      EMBEDDABLE             BOOLEAN,\r\n",
    "                      MADE_FOR_KIDS          BOOLEAN,\r\n",
    "                      TOPICS                 VARCHAR(255),\r\n",
    "\r\n",
    "                      CONSTRAINT PK_VID_ENTRY PRIMARY KEY (VIDEO_ID,ENTRY_TIMESTAMP)\r\n",
    "                   );\r\n",
    "                \r\n",
    "                \"\"\")\r\n",
    "# Executing the command.\r\n",
    "try:\r\n",
    "   cursor.execute(create_table)\r\n",
    "   cursor.execute(\"COMMIT\")\r\n",
    "except (Exception, spg.DatabaseError) as error:\r\n",
    "    print(f\"Following error(s) occured : {error}\")\r\n",
    "    cursor.execute(\"ROLLBACK\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4. d) Using the fastest method ***`copy_from()`*** using ***`StringIO`*** to insert data into database table from our cleaned DataFrame."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "source": [
    "buffer = StringIO()\r\n",
    "\r\n",
    "# Storing the DataFrame to StringIO in CSV format.\r\n",
    "df.to_csv(buffer, sep=\"\\t\", header=False, index=False) \r\n",
    "\r\n",
    "# seek(0) will position the pointer to the start of the data, 0th position/byte.\r\n",
    "buffer.seek(0)\r\n",
    "\r\n",
    "# Executing the copy to load DataFrame data to the table in database.\r\n",
    "try:\r\n",
    "    cursor.copy_from(buffer,'yt_popular_videos',sep=\"\\t\",null=\"\")\r\n",
    "    cursor.execute(\"COMMIT\")\r\n",
    "except (Exception, spg.DatabaseError) as error:\r\n",
    "    print(f\"Following error(s) occured : {error}\")\r\n",
    "    cursor.execute(\"ROLLBACK\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4. e) **Closing** the ***`cursor()`*** and ***`connection()`*** to the database."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "cursor.close()\r\n",
    "connection.close()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4. f) **Clearing** all the user defined variables of the namespace."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "%reset -f"
   ],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "68e6f762b01821c724edd32c8c28c1d5e0a3b08ea85eec78b3942b03350556a9"
  },
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.2 64-bit"
  },
  "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}