youtube-trending/Code/youtube_trending_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**This is the final pre-processing code for the video features in terms of foundational cleaning and merging**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import re\n",
    "from datetime import datetime as dt\n",
    "import scipy.stats as st\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import requests\n",
    "from api_keys import (gkey, gkey2, gkey3)\n",
    "import time\n",
    "import random\n",
    "import pickle\n",
    "import json\n",
    "\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = 'all'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging the Data to a single DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_all_files(path, ty='csv', Name='Merged_DF.csv'):\n",
    "    #Iteratively appends all files with ty extention to list_of_files\n",
    "    for root,dirs,files in os.walk(path):\n",
    "        [list_of_files.append(file) for file in files if (file.endswith(f\".{ty}\") and (file!=Name))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "Name = 'Merged_DF.csv' #Name of Final DF\n",
    "list_of_files = []\n",
    "data_path = os.path.join('..', 'Data')\n",
    "find_all_files(data_path, Name=Name)\n",
    "\n",
    "Total_DF = pd.DataFrame()\n",
    "for file in list_of_files:\n",
    "    try:\n",
    "        DF = pd.read_csv(os.path.join('..', 'Data', file), encoding='utf-8')\n",
    "    except:\n",
    "        DF = pd.read_csv(os.path.join('..', 'Data', file), encoding='latin1')\n",
    "    DF['country'] = file[:2]\n",
    "    Total_DF = (DF if Total_DF.empty else pd.concat([Total_DF, DF]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Total DF Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Total_DF['video_id'].map(lambda x: type(x)!=str).sum() #All values are string\n",
    "#Total_DF['trending_date'].map(lambda x: type(x)!=str).sum() #All values are string\n",
    "#Convert the 'trending_date' to date format\n",
    "Total_DF['trending_date'] = Total_DF['trending_date'].map(lambda x: dt.strptime(x, \"%y.%d.%m\"))\n",
    "\n",
    "#Total_DF['title'].map(lambda x: type(x)!=str).sum() #All values are string\n",
    "#Total_DF['channel_title'].map(lambda x: type(x)!=str).sum() #All values are string\n",
    "#Total_DF['category_id'].map(lambda x: type(x)!=int).sum() #All values are int\n",
    "#Total_DF['publish_time'].map(lambda x: type(x)!=str).sum() #All values are string\n",
    "#Convert the 'publish_time' to date format\n",
    "Total_DF['publish_time'] = pd.to_datetime(Total_DF['publish_time'], format='%Y-%m-%dT%H:%M:%S.%fZ') #%f means microsecond which means 6 digits. This works here as it is always 0 microseconds\n",
    "#Total_DF['tags'].map(lambda x: type(x)!=str).sum() #All values are string\n",
    "#Total_DF['views'].map(lambda x: type(x)!=int).sum() #All values are int64\n",
    "#Total_DF['likes'].map(lambda x: type(x)!=int).sum() #All values are int64\n",
    "#Total_DF['dislikes'].map(lambda x: type(x)!=int).sum() #All values are int64\n",
    "#Total_DF['comment_count'].map(lambda x: type(x)!=int).sum() #All values are int64\n",
    "#Total_DF['likes'].isnull().sum()\n",
    "#Total_DF['dislikes'].isnull().sum()\n",
    "#Total_DF['comment_count'].isnull().sum()\n",
    "#Total_DF['thumbnail_link'].isnull().sum()\n",
    "#Total_DF['comments_disabled'].map(lambda x: type(x)!=bool).sum() #All values are boolean\n",
    "#Total_DF['ratings_disabled'].map(lambda x: type(x)!=bool).sum() #All values are boolean\n",
    "#Total_DF['video_error_or_removed'].map(lambda x: type(x)!=bool).sum() #All values are boolean\n",
    "#Total_DF['comments_disabled'].isnull().sum()\n",
    "#Total_DF['ratings_disabled'].isnull().sum()\n",
    "#Total_DF['video_error_or_removed'].isnull().sum()\n",
    "\n",
    "#Convert NaN values in 'description' to ''\n",
    "Total_DF['description'].fillna(value='', inplace=True)\n",
    "#Total_DF['description'].isna().sum()\n",
    "#Total_DF[Total_DF['description'] == ''].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing for duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(360578, 17)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Drop the duplicate rows\n",
    "Total_DF.drop_duplicates(subset=['video_id', 'trending_date', 'country'], keep='last', inplace=True)#NEED TO CHANGE\n",
    "Total_DF.reset_index(drop=True, inplace=True)\n",
    "to_drop = Total_DF[(Total_DF['video_id']=='#NAME?') | (Total_DF['video_id']=='#VALUE!')].index\n",
    "Total_DF.drop(to_drop, inplace=True)\n",
    "Total_DF.reset_index(drop=True, inplace=True)\n",
    "Total_DF.shape\n",
    "#375942 - 14518 - 846  = 360578"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing Videos with multiple publish Times and videos with 'video_error_or_removed'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(360432, 17)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(360217, 17)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(360217, 16)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Publish time is supposed to be unique. Remove the videos with More than 1 publish time \n",
    "Temp_TF = Total_DF.groupby('video_id').aggregate(Publish_Time_Unique_Count = ('publish_time', lambda x : len(set(x.to_list()))))           \n",
    "#Drop these 29 videos, total 146 corresponding rows\n",
    "Total_DF.drop(Total_DF[Total_DF['video_id'].isin(Temp_TF[Temp_TF['Publish_Time_Unique_Count']>1].index)].index, inplace=True)\n",
    "Total_DF.reset_index(drop=True, inplace=True)\n",
    "Total_DF.shape\n",
    "#360578 - 146 = 360432\n",
    "\n",
    "#Some Videos are removed after some time; Let's exclude these videos from the analysis as there is a manual intervention or environment issue \n",
    "#50 videos had error (atleast once); Total 215 rows\n",
    "#Both the below codes give exactly same results; MEANS 'video_error_or_removed' really means that atleast once 'video_error_or_removed'\n",
    "#Total_DF[Total_DF['video_id'].isin(Total_DF[Total_DF['video_error_or_removed']]['video_id'].unique())]\n",
    "#Total_DF[Total_DF['video_error_or_removed']]\n",
    "Total_DF.drop(Total_DF[Total_DF['video_id'].isin(Total_DF[Total_DF['video_error_or_removed']]['video_id'].unique())].index, inplace=True)\n",
    "Total_DF.reset_index(drop=True, inplace=True)\n",
    "Total_DF.shape\n",
    "#360432-215 = 360217\n",
    "\n",
    "#Remove 'video_error_or_removed' as it doesn't carry any relevant info now\n",
    "Total_DF.drop('video_error_or_removed', inplace=True, axis=1)\n",
    "Total_DF.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save the DataFrame as pickle "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Total_DF.to_pickle(\"../Data/VideoDF.pkl\") #Commenting to avoid overwritting the existing file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read from pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "Video_DF = pd.read_pickle(\"../Data/VideoDF.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "video_id                     object\n",
       "trending_date        datetime64[ns]\n",
       "title                        object\n",
       "channel_title                object\n",
       "category_id                   int64\n",
       "publish_time         datetime64[ns]\n",
       "tags                         object\n",
       "views                         int64\n",
       "likes                         int64\n",
       "dislikes                      int64\n",
       "comment_count                 int64\n",
       "thumbnail_link               object\n",
       "comments_disabled              bool\n",
       "ratings_disabled               bool\n",
       "description                  object\n",
       "country                      object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Checking Sanity\n",
    "Video_DF.dtypes\n",
    "Video_DF.duplicated(subset=['video_id', 'trending_date', 'country'], keep='last').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Introducing the Notion of Popularity. What makes trending videos popular ?\n",
    "\n",
    "**Popularity score (Longevity): Total days of trending for a video**\n",
    "\n",
    "**Populrity score of a trending video depends on what ?**\n",
    "\n",
    "**Useful metric to consider from the dataset**\n",
    "- Views of Trend Day 1\n",
    "- Likes of Trend Day 1\n",
    "- Dislikes of Trend Day 1\n",
    "- Comment_Count of Trend Day 1\n",
    "- Words in Title\n",
    "- Channel Title\n",
    "- Category ID\n",
    "- Tags\n",
    "- comments_disabled\n",
    "- ratings_disabled\n",
    "- video_error_or_removed\n",
    "- description\n",
    "\n",
    "**Extract the info outside dataset**\n",
    "- Publish time of the day based on the timezone of the channel (Need outside info)\n",
    "- Country\n",
    "- Language\n",
    "\n",
    "**Extract info outside news**\n",
    "- News effect on popularity (Can be speific to a category)\n",
    "- Google trending effect on popularity\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# New Video Features via API call"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample API call\n",
    "\n",
    "base = 'https://www.googleapis.com/youtube/v3/videos'\n",
    "\n",
    "\n",
    "params = {\n",
    "            'part' : 'snippet,contentDetails',\n",
    "            #'part' :'localizations',\n",
    "            #'part' : 'player',\n",
    "            #'part' : 'recordingDetails',\n",
    "            #'part' : 'statistics',\n",
    "            #'part' : 'status',\n",
    "            'id': <List of Videos>,\n",
    "            'key':gkey\n",
    "}\n",
    "\n",
    "response = requests.get(base, params)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "#Video_ID_List = list(set(Video_DF['video_id'])) #COMMENTING NOT TO ACCEDENTALLY RUN THE ENTIRE BATCH\n",
    "#Video_ID_List = pickle.load(open( \"To_scrape.p\", \"rb\" )) #THIS IS USED TO SCRAPE MISSED VIDEOS\n",
    "\n",
    "Total = []\n",
    "len(Video_ID_List)\n",
    "Video_features = dict()\n",
    "base = 'https://www.googleapis.com/youtube/v3/videos'\n",
    "Total_Videos = len(Video_ID_List)\n",
    "count = 0\n",
    "for iteration in range(((len(Video_ID_List)//50 + 1) if len(Video_ID_List)%50 else len(Video_ID_List)//50)):\n",
    "    list_of_videos = (Video_ID_List[iteration*50:iteration*50+50] if (iteration+1)*50<=Total_Videos else Video_ID_List[iteration*50:])\n",
    "    Total = Total + list_of_videos\n",
    "    params = {\n",
    "            'part' : 'snippet,contentDetails',\n",
    "            'id': list_of_videos,\n",
    "            'key':gkey\n",
    "    }\n",
    "    \n",
    "    response = requests.get(base, params)\n",
    "    \n",
    "    try:\n",
    "        content = response.json()\n",
    "        \n",
    "        for item in content['items']:\n",
    "            count+=1\n",
    "            ID = item['id']\n",
    "\n",
    "            Video_features[ID] = {'ChannelID' : (item['snippet']['channelId'] if 'channelId' in item['snippet'] else None)}\n",
    "\n",
    "            Video_features[ID].update({'ChannelTitle':(item['snippet']['channelTitle'] if 'channelTitle' in item['snippet'] else None)})\n",
    "\n",
    "            Video_features[ID].update({'DefaultLanguage' : (item['snippet']['defaultLanguage'] if 'defaultLanguage' in item['snippet'] else None)})\n",
    "\n",
    "            Video_features[ID].update({'DefaultAudioLanguage' : (item['snippet']['defaultAudioLanguage'] if 'defaultAudioLanguage' in item['snippet'] else None)})\n",
    "\n",
    "            Video_features[ID].update({'Duration' : (item['contentDetails']['duration'] if 'duration'in item['contentDetails'] else None)})\n",
    "\n",
    "            Video_features[ID].update({'Caption' : (item['contentDetails']['caption'] if 'caption'in item['contentDetails'] else None)})\n",
    "\n",
    "            Video_features[ID].update({'RegionRestriction_Blocked' : (((item['contentDetails']['regionRestriction']['blocked']  if  ('blocked' in item['contentDetails']['regionRestriction']) else None)) if ('regionRestriction' in item['contentDetails']) else None)})   \n",
    "\n",
    "            Video_features[ID].update({'RegionRestriction_Allowed' : (((item['contentDetails']['regionRestriction']['allowed']  if  ('allowed' in item['contentDetails']['regionRestriction']) else None)) if ('regionRestriction' in item['contentDetails']) else None)})   \n",
    "            \n",
    "    except:\n",
    "        print(response.url)\n",
    "        print(response)\n",
    "        print(f\"Didn't get response for iteration {iteration}\")\n",
    "        \n",
    "    time.sleep(2)\n",
    "    print(count)\n",
    "time_taken_in_min=(time.time()-start_time)//60 \n",
    "#Before I start,\n",
    "#YouTube Data API v3\t74\t\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving the scraped Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124848, 9)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Scraped_DF = pd.DataFrame(Video_features).transpose()\n",
    "#Remove duplicates if any\n",
    "#Scraped_DF=Scraped_DF[~Scraped_DF.duplicated(['video_id'], keep='first')]\n",
    "#Scraped_DF.to_pickle(\"../Data/API_RETRIEVED_DATA.pkl\")\n",
    "Scraped_DF = pd.read_pickle('../Data/API_RETRIEVED_DATA.pkl')\n",
    "#Scraped_DF.columns = ['video_id'] + Scraped_DF.columns[1:].to_list()\n",
    "Scraped_DF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Scraped_DF.to_csv('../Output/API_RETRIEVED_DATA.csv', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge the Video Data with Scraped Data (Inner)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(360217, 16)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(124848, 9)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Index(['video_id', 'trending_date', 'title', 'channel_title', 'category_id',\n",
       "       'publish_time', 'tags', 'views', 'likes', 'dislikes', 'comment_count',\n",
       "       'thumbnail_link', 'comments_disabled', 'ratings_disabled',\n",
       "       'description', 'country'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Index(['video_id', 'ChannelID', 'ChannelTitle', 'DefaultLanguage',\n",
       "       'DefaultAudioLanguage', 'Duration', 'Caption',\n",
       "       'RegionRestriction_Blocked', 'RegionRestriction_Allowed'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Index(['video_id', 'trending_date', 'title', 'channel_title', 'category_id',\n",
       "       'publish_time', 'tags', 'views', 'likes', 'dislikes', 'comment_count',\n",
       "       'thumbnail_link', 'comments_disabled', 'ratings_disabled',\n",
       "       'description', 'country', 'ChannelID', 'ChannelTitle',\n",
       "       'DefaultLanguage', 'DefaultAudioLanguage', 'Duration', 'Caption',\n",
       "       'RegionRestriction_Blocked', 'RegionRestriction_Allowed'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(266934, 24)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Video_DF.shape\n",
    "Scraped_DF.shape\n",
    "\n",
    "Video_DF.columns\n",
    "Scraped_DF.columns\n",
    "\n",
    "New_Video_DF = Video_DF.merge(Scraped_DF, how='inner', on='video_id')\n",
    "New_Video_DF.columns\n",
    "New_Video_DF.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Channel Information through API call"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample API call\n",
    "\n",
    "base = 'https://www.googleapis.com/youtube/v3/channels'\n",
    "\n",
    "params = {\n",
    "            'part' : 'snippet,brandingSettings,topicDetails',\n",
    "            #'part' :'localizations',\n",
    "            #'part' : 'player',\n",
    "            #'part' : 'recordingDetails',\n",
    "            #'part' : 'statistics',\n",
    "            #'part' : 'status',\n",
    "            'id': Channel_ID_list[1000:1050],\n",
    "            'key':gkey3\n",
    "}\n",
    "\n",
    "response = requests.get(base, params)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "#Channel_ID_list = list(set(New_Video_DF['ChannelID'])) #COMMENTING NOT TO ACCEDENTALLY RUN THE ENTIRE BATCH\n",
    "#len(Channel_ID_list) = 23159\n",
    "#Channel_ID_list = Remaining #THIS IS USED TO SCRAPE MISSED VIDEOS\n",
    "\n",
    "Total = []\n",
    "len(Channel_ID_list)\n",
    "Channel_features = dict()\n",
    "base = 'https://www.googleapis.com/youtube/v3/channels'\n",
    "Total_Channels = len(Channel_ID_list)\n",
    "count = 0\n",
    "for iteration in range(((len(Channel_ID_list)//50 + 1) if len(Channel_ID_list)%50 else len(Channel_ID_list)//50)):\n",
    "    list_of_channels = (Channel_ID_list[iteration*50:iteration*50+50] if (iteration+1)*50<=Total_Channels else Channel_ID_list[iteration*50:])\n",
    "    Total = Total + list_of_channels\n",
    "    params = {\n",
    "            'part' : 'snippet,brandingSettings,topicDetails',\n",
    "            'id': list_of_channels,\n",
    "            'key':gkey\n",
    "            }\n",
    "    \n",
    "    response = requests.get(base, params)\n",
    " \n",
    "\n",
    "\n",
    "    try:\n",
    "        content = response.json()\n",
    "\n",
    "        for item in content['items']:\n",
    "            count+=1\n",
    "            ID = item['id']\n",
    "\n",
    "            Channel_features[ID] = {'Channel_Title' : (item['snippet']['title'] if 'title' in item['snippet'] else None)}\n",
    "\n",
    "            Channel_features[ID].update({'Channel_Description' : (item['snippet']['description'] if 'description' in item['snippet'] else None)})\n",
    "\n",
    "            Channel_features[ID].update({'Channel_PublishedAt' : (item['snippet']['publishedAt'] if 'publishedAt' in item['snippet'] else None)})\n",
    "\n",
    "            Channel_features[ID].update({'country' : (item['snippet']['country'] if 'country' in item['snippet'] else None)})\n",
    "\n",
    "            Channel_features[ID].update({'defaultLanguage' : (item['snippet']['defaultLanguage'] if 'defaultLanguage' in item['snippet'] else None)})\n",
    "\n",
    "            Channel_features[ID].update({'keywords' : (((item['brandingSettings']['channel']['keywords']  if  ('keywords' in item['brandingSettings']['channel']) else None)) if ('channel' in item['brandingSettings']) else None)})\n",
    "\n",
    "            Channel_features[ID].update({'GoogleAnalytics' : (((True  if  ('trackingAnalyticsAccountId' in item['brandingSettings']['channel']) else False)) if ('channel' in item['brandingSettings']) else False)})\n",
    "\n",
    "            Channel_features[ID].update({'moderateComments' : (((item['brandingSettings']['channel']['moderateComments']  if  ('moderateComments' in item['brandingSettings']['channel']) else False)) if ('channel' in item['brandingSettings']) else False)})\n",
    "\n",
    "            Channel_features[ID].update({'topicCategories' : ((' | '.join([category.split('/')[-1] for category in item['topicDetails']['topicCategories']]) if 'topicCategories' in item['topicDetails'] else None)) if 'topicDetails' in item else None})\n",
    "\n",
    "    except:\n",
    "        print(response.url)\n",
    "        print(response)\n",
    "        print(f\"Didn't get response for iteration {iteration}\")\n",
    "        \n",
    "    time.sleep(2)\n",
    "    print(count)\n",
    "time_taken_in_min=(time.time()-start_time)//60 \n",
    "#Before I start,\n",
    "#YouTube Data API v3\t5,210\t   \n",
    "            "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving the scraped Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Channel_features_Scraped_DF = pd.DataFrame(Channel_features).transpose()\n",
    "#Channel_features_Scraped_DF.reset_index(inplace=True)\n",
    "#Channel_features_Scraped_DF.columns = ['ChannelID'] + Channel_features_DF.columns[1:].to_list()\n",
    "#Channel_features_Scraped_DF.to_pickle(\"../Data/Channel_API_RETRIEVED_DATA.pkl\")\n",
    "\n",
    "Channel_features_DF = pd.read_pickle(\"../Data/Channel_API_RETRIEVED_DATA.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 23155 entries, 0 to 23154\n",
      "Data columns (total 10 columns):\n",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   ChannelID            23155 non-null  object\n",
      " 1   Channel_Title        23155 non-null  object\n",
      " 2   Channel_Description  23155 non-null  object\n",
      " 3   Channel_PublishedAt  23154 non-null  object\n",
      " 4   country              16525 non-null  object\n",
      " 5   defaultLanguage      2084 non-null   object\n",
      " 6   keywords             17390 non-null  object\n",
      " 7   GoogleAnalytics      23155 non-null  object\n",
      " 8   moderateComments     23155 non-null  object\n",
      " 9   topicCategories      23099 non-null  object\n",
      "dtypes: object(10)\n",
      "memory usage: 1.8+ MB\n"
     ]
    }
   ],
   "source": [
    "Channel_features_DF.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Channel_features_DF.to_csv(\"../Output/Channel_API_RETRIEVED_DATA.csv\", encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>ChannelID</th>\n",
       "      <th>Channel_Title</th>\n",
       "      <th>Channel_Description</th>\n",
       "      <th>Channel_PublishedAt</th>\n",
       "      <th>country</th>\n",
       "      <th>defaultLanguage</th>\n",
       "      <th>keywords</th>\n",
       "      <th>GoogleAnalytics</th>\n",
       "      <th>moderateComments</th>\n",
       "      <th>topicCategories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UCcADqTjMyMol8B8mWm9n6rA</td>\n",
       "      <td>SECHSKIES</td>\n",
       "      <td>SECHSKIES Official YouTube Channel\\n젝스키스 공식 유튜...</td>\n",
       "      <td>2016-08-12T04:43:25Z</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>\"YG Entertainment\" YG 와이지 K-pop 젝스키스 젝키 SECHSK...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>Music | Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UClmXPfaYhXOYsNn_QUyheWQ</td>\n",
       "      <td>Ed Sheeran - Topic</td>\n",
       "      <td>Edward Christopher \"Ed\" Sheeran is an English ...</td>\n",
       "      <td>2013-07-03T16:09:35Z</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Pop_music | Music | Electronic_music | Hip_hop...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UCknYpLMv_eQQJn5u8zQ8IJQ</td>\n",
       "      <td>RadioEvropaLire</td>\n",
       "      <td>Misioni i Radios Evropa e Lirë është plasimi i...</td>\n",
       "      <td>2011-02-15T11:30:09Z</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>Society</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>UCBhtXZI7_FxRw-6V19oloBw</td>\n",
       "      <td>Olga Astrology</td>\n",
       "      <td>Канал посвящен Астрологии - делам земным и неб...</td>\n",
       "      <td>2011-03-02T14:05:32Z</td>\n",
       "      <td>BE</td>\n",
       "      <td>None</td>\n",
       "      <td>астрология</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Hobby | Lifestyle_(sociology) | Society</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>UCvMu4ihpZCShKdbjjMkSxQA</td>\n",
       "      <td>La Jefa Ingrid Ramos</td>\n",
       "      <td>¡Hola, soy La Jefa Ingrid Ramos! En este canal...</td>\n",
       "      <td>2017-12-10T18:52:47Z</td>\n",
       "      <td>MX</td>\n",
       "      <td>None</td>\n",
       "      <td>cocina recetas tips \"cocineros mexicanos\" \"la ...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Food | Lifestyle_(sociology)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ChannelID         Channel_Title  \\\n",
       "0  UCcADqTjMyMol8B8mWm9n6rA             SECHSKIES   \n",
       "1  UClmXPfaYhXOYsNn_QUyheWQ    Ed Sheeran - Topic   \n",
       "2  UCknYpLMv_eQQJn5u8zQ8IJQ       RadioEvropaLire   \n",
       "3  UCBhtXZI7_FxRw-6V19oloBw        Olga Astrology   \n",
       "4  UCvMu4ihpZCShKdbjjMkSxQA  La Jefa Ingrid Ramos   \n",
       "\n",
       "                                 Channel_Description   Channel_PublishedAt  \\\n",
       "0  SECHSKIES Official YouTube Channel\\n젝스키스 공식 유튜...  2016-08-12T04:43:25Z   \n",
       "1  Edward Christopher \"Ed\" Sheeran is an English ...  2013-07-03T16:09:35Z   \n",
       "2  Misioni i Radios Evropa e Lirë është plasimi i...  2011-02-15T11:30:09Z   \n",
       "3  Канал посвящен Астрологии - делам земным и неб...  2011-03-02T14:05:32Z   \n",
       "4  ¡Hola, soy La Jefa Ingrid Ramos! En este canal...  2017-12-10T18:52:47Z   \n",
       "\n",
       "  country defaultLanguage                                           keywords  \\\n",
       "0    None            None  \"YG Entertainment\" YG 와이지 K-pop 젝스키스 젝키 SECHSK...   \n",
       "1    None            None                                               None   \n",
       "2    None            None                                               None   \n",
       "3      BE            None                                         астрология   \n",
       "4      MX            None  cocina recetas tips \"cocineros mexicanos\" \"la ...   \n",
       "\n",
       "  GoogleAnalytics moderateComments  \\\n",
       "0            True            False   \n",
       "1           False            False   \n",
       "2            True            False   \n",
       "3           False            False   \n",
       "4           False            False   \n",
       "\n",
       "                                     topicCategories  \n",
       "0                              Music | Entertainment  \n",
       "1  Pop_music | Music | Electronic_music | Hip_hop...  \n",
       "2                                            Society  \n",
       "3            Hobby | Lifestyle_(sociology) | Society  \n",
       "4                       Food | Lifestyle_(sociology)  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "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>trending_date</th>\n",
       "      <th>title</th>\n",
       "      <th>channel_title</th>\n",
       "      <th>category_id</th>\n",
       "      <th>publish_time</th>\n",
       "      <th>tags</th>\n",
       "      <th>views</th>\n",
       "      <th>likes</th>\n",
       "      <th>dislikes</th>\n",
       "      <th>...</th>\n",
       "      <th>description</th>\n",
       "      <th>country</th>\n",
       "      <th>ChannelID</th>\n",
       "      <th>ChannelTitle</th>\n",
       "      <th>DefaultLanguage</th>\n",
       "      <th>DefaultAudioLanguage</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Caption</th>\n",
       "      <th>RegionRestriction_Blocked</th>\n",
       "      <th>RegionRestriction_Allowed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SbOwzAl9ZfQ</td>\n",
       "      <td>2017-11-14</td>\n",
       "      <td>CapÃ­tulo 12 | MasterChef 2017</td>\n",
       "      <td>MasterChef 2017</td>\n",
       "      <td>24</td>\n",
       "      <td>2017-11-13 06:06:22</td>\n",
       "      <td>MasterChef Junior 2017|\"TV Azteca\"|\"recetas\"|\"...</td>\n",
       "      <td>310130</td>\n",
       "      <td>4182</td>\n",
       "      <td>361</td>\n",
       "      <td>...</td>\n",
       "      <td>Disfruta la presencia del Chef Torreblanca en ...</td>\n",
       "      <td>MX</td>\n",
       "      <td>UCDYetMc6gOLkhIiNzFyrJPA</td>\n",
       "      <td>MasterChef México</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>PT1H48M8S</td>\n",
       "      <td>false</td>\n",
       "      <td>None</td>\n",
       "      <td>[MX]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SbOwzAl9ZfQ</td>\n",
       "      <td>2017-11-15</td>\n",
       "      <td>CapÃ­tulo 12 | MasterChef 2017</td>\n",
       "      <td>MasterChef 2017</td>\n",
       "      <td>24</td>\n",
       "      <td>2017-11-13 06:06:22</td>\n",
       "      <td>MasterChef Junior 2017|\"TV Azteca\"|\"recetas\"|\"...</td>\n",
       "      <td>684302</td>\n",
       "      <td>5891</td>\n",
       "      <td>553</td>\n",
       "      <td>...</td>\n",
       "      <td>Disfruta la presencia del Chef Torreblanca en ...</td>\n",
       "      <td>MX</td>\n",
       "      <td>UCDYetMc6gOLkhIiNzFyrJPA</td>\n",
       "      <td>MasterChef México</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>PT1H48M8S</td>\n",
       "      <td>false</td>\n",
       "      <td>None</td>\n",
       "      <td>[MX]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>klOV6Xh-DnI</td>\n",
       "      <td>2017-11-14</td>\n",
       "      <td>ALEXA EX-INTEGRANTE DEL GRUPO TIMBIRICHE RENUN...</td>\n",
       "      <td>Micky Contreras Martinez</td>\n",
       "      <td>22</td>\n",
       "      <td>2017-11-13 05:11:58</td>\n",
       "      <td>La Voz Mexico 7</td>\n",
       "      <td>104972</td>\n",
       "      <td>271</td>\n",
       "      <td>174</td>\n",
       "      <td>...</td>\n",
       "      <td>ALEXA EX-INTEGRANTE DEL GRUPO TIMBIRICHE RENUN...</td>\n",
       "      <td>MX</td>\n",
       "      <td>UCZYbxoZhCltabKhvgwLHnig</td>\n",
       "      <td>Micky Contreras Martinez</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>PT9M13S</td>\n",
       "      <td>false</td>\n",
       "      <td>[SD, UA, TL, NZ, US, NP, FI, FJ, FK, UY, FM, F...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6L2ZF7Qzsbk</td>\n",
       "      <td>2017-11-14</td>\n",
       "      <td>LOUIS CKAGÃ - EL PULSO DE LA REPÃBLICA</td>\n",
       "      <td>El Pulso De La RepÃºblica</td>\n",
       "      <td>25</td>\n",
       "      <td>2017-11-13 17:00:02</td>\n",
       "      <td>Chumel Torres|\"El Pulso de la Republica\"|\"noti...</td>\n",
       "      <td>136064</td>\n",
       "      <td>10105</td>\n",
       "      <td>266</td>\n",
       "      <td>...</td>\n",
       "      <td>La canciÃ³n del principio se llama âEste esp...</td>\n",
       "      <td>MX</td>\n",
       "      <td>UCK0_zBeybLuyXbOcHp7wmJA</td>\n",
       "      <td>El Pulso De La República</td>\n",
       "      <td>es-419</td>\n",
       "      <td>es</td>\n",
       "      <td>PT22M34S</td>\n",
       "      <td>false</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6L2ZF7Qzsbk</td>\n",
       "      <td>2017-11-15</td>\n",
       "      <td>LOUIS CKAGÃ - EL PULSO DE LA REPÃBLICA</td>\n",
       "      <td>El Pulso De La RepÃºblica</td>\n",
       "      <td>25</td>\n",
       "      <td>2017-11-13 17:00:02</td>\n",
       "      <td>Chumel Torres|\"El Pulso de la Republica\"|\"noti...</td>\n",
       "      <td>449540</td>\n",
       "      <td>18377</td>\n",
       "      <td>586</td>\n",
       "      <td>...</td>\n",
       "      <td>La canciÃ³n del principio se llama âEste esp...</td>\n",
       "      <td>MX</td>\n",
       "      <td>UCK0_zBeybLuyXbOcHp7wmJA</td>\n",
       "      <td>El Pulso De La República</td>\n",
       "      <td>es-419</td>\n",
       "      <td>es</td>\n",
       "      <td>PT22M34S</td>\n",
       "      <td>false</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      video_id trending_date  \\\n",
       "0  SbOwzAl9ZfQ    2017-11-14   \n",
       "1  SbOwzAl9ZfQ    2017-11-15   \n",
       "2  klOV6Xh-DnI    2017-11-14   \n",
       "3  6L2ZF7Qzsbk    2017-11-14   \n",
       "4  6L2ZF7Qzsbk    2017-11-15   \n",
       "\n",
       "                                               title  \\\n",
       "0                     CapÃ­tulo 12 | MasterChef 2017   \n",
       "1                     CapÃ­tulo 12 | MasterChef 2017   \n",
       "2  ALEXA EX-INTEGRANTE DEL GRUPO TIMBIRICHE RENUN...   \n",
       "3           LOUIS CKAGÃ - EL PULSO DE LA REPÃBLICA   \n",
       "4           LOUIS CKAGÃ - EL PULSO DE LA REPÃBLICA   \n",
       "\n",
       "               channel_title  category_id        publish_time  \\\n",
       "0            MasterChef 2017           24 2017-11-13 06:06:22   \n",
       "1            MasterChef 2017           24 2017-11-13 06:06:22   \n",
       "2   Micky Contreras Martinez           22 2017-11-13 05:11:58   \n",
       "3  El Pulso De La RepÃºblica           25 2017-11-13 17:00:02   \n",
       "4  El Pulso De La RepÃºblica           25 2017-11-13 17:00:02   \n",
       "\n",
       "                                                tags   views  likes  dislikes  \\\n",
       "0  MasterChef Junior 2017|\"TV Azteca\"|\"recetas\"|\"...  310130   4182       361   \n",
       "1  MasterChef Junior 2017|\"TV Azteca\"|\"recetas\"|\"...  684302   5891       553   \n",
       "2                                    La Voz Mexico 7  104972    271       174   \n",
       "3  Chumel Torres|\"El Pulso de la Republica\"|\"noti...  136064  10105       266   \n",
       "4  Chumel Torres|\"El Pulso de la Republica\"|\"noti...  449540  18377       586   \n",
       "\n",
       "   ...                                        description country  \\\n",
       "0  ...  Disfruta la presencia del Chef Torreblanca en ...      MX   \n",
       "1  ...  Disfruta la presencia del Chef Torreblanca en ...      MX   \n",
       "2  ...  ALEXA EX-INTEGRANTE DEL GRUPO TIMBIRICHE RENUN...      MX   \n",
       "3  ...  La canciÃ³n del principio se llama âEste esp...      MX   \n",
       "4  ...  La canciÃ³n del principio se llama âEste esp...      MX   \n",
       "\n",
       "                  ChannelID              ChannelTitle DefaultLanguage  \\\n",
       "0  UCDYetMc6gOLkhIiNzFyrJPA         MasterChef México            None   \n",
       "1  UCDYetMc6gOLkhIiNzFyrJPA         MasterChef México            None   \n",
       "2  UCZYbxoZhCltabKhvgwLHnig  Micky Contreras Martinez            None   \n",
       "3  UCK0_zBeybLuyXbOcHp7wmJA  El Pulso De La República          es-419   \n",
       "4  UCK0_zBeybLuyXbOcHp7wmJA  El Pulso De La República          es-419   \n",
       "\n",
       "  DefaultAudioLanguage   Duration Caption  \\\n",
       "0                 None  PT1H48M8S   false   \n",
       "1                 None  PT1H48M8S   false   \n",
       "2                 None    PT9M13S   false   \n",
       "3                   es   PT22M34S   false   \n",
       "4                   es   PT22M34S   false   \n",
       "\n",
       "                           RegionRestriction_Blocked RegionRestriction_Allowed  \n",
       "0                                               None                      [MX]  \n",
       "1                                               None                      [MX]  \n",
       "2  [SD, UA, TL, NZ, US, NP, FI, FJ, FK, UY, FM, F...                      None  \n",
       "3                                               None                      None  \n",
       "4                                               None                      None  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(23155, 10)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(266934, 24)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Channel_features_DF.head()\n",
    "New_Video_DF.head()\n",
    "Channel_features_DF.shape\n",
    "New_Video_DF.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge the channel features to the video features (Inner)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(266928, 33)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "New_Video_Channel_DF = New_Video_DF.merge(Channel_features_DF, how='inner', on='ChannelID', suffixes=('', '_CH'))\n",
    "New_Video_Channel_DF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "video_id                                                           xL_qpDkF5A8\n",
       "trending_date                                              2017-11-18 00:00:00\n",
       "title                        American Crime Story Season 2: The Assassinati...\n",
       "channel_title                                                        TV Promos\n",
       "category_id                                                                 24\n",
       "publish_time                                               2017-11-15 01:25:18\n",
       "tags                         Donatella Versace|\"Antonio D' Amico\"|\"Penelope...\n",
       "views                                                                   408821\n",
       "likes                                                                     3912\n",
       "dislikes                                                                   152\n",
       "comment_count                                                              466\n",
       "thumbnail_link                  https://i.ytimg.com/vi/xL_qpDkF5A8/default.jpg\n",
       "comments_disabled                                                        False\n",
       "ratings_disabled                                                         False\n",
       "description                  The Assassination of Gianni Versace: American ...\n",
       "country                                                                     US\n",
       "ChannelID                                             UCDR8cvjALazMm2j9hOar8_g\n",
       "ChannelTitle                                                         TV Promos\n",
       "DefaultLanguage                                                             en\n",
       "DefaultAudioLanguage                                                        en\n",
       "Duration                                                               PT1M15S\n",
       "Caption                                                                   true\n",
       "RegionRestriction_Blocked                                                 None\n",
       "RegionRestriction_Allowed                                                 None\n",
       "Channel_Title                                                        TV Promos\n",
       "Channel_Description          Here you'll find only officially released tele...\n",
       "Channel_PublishedAt                                       2013-04-22T00:33:36Z\n",
       "country_CH                                                                  US\n",
       "defaultLanguage                                                           None\n",
       "keywords                     TV promos television tvpromosdb televisionprom...\n",
       "GoogleAnalytics                                                           True\n",
       "moderateComments                                                         False\n",
       "topicCategories                      Entertainment | Film | Television_program\n",
       "Name: 10000, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "New_Video_Channel_DF.iloc[10000,:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process and  find what is missing "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remove**\n",
    "- Remove Channel_Title as channel_title has the same content\n",
    "- Remove thumbnail_link as we do not cover any computer vision techniques here\n",
    "\n",
    "**Rename**\n",
    "- Rename country as viewing_country\n",
    "- Rename country_CH as origin_country\n",
    "- Rename Caption as Caption_Enabled\n",
    "- Rename GoogleAnalytics as GoogleAnalyticsUsed\n",
    "- Rename moderateComments as IsmoderatingComments\n",
    "- Rename topicCategories as channelTopicCategories\n",
    "\n",
    "**Mapping**\n",
    "- Map category_id based on category mapping in each countries in the json file\n",
    "- Combine defaultLanguage with DefaultLanguage and DefaultAudioLanguage. If None of them are present, then google \n",
    "- Combine DefaultAudioLanguage(pref 1)  with DefaultLanguage(pref 2) and defaultLanguage(pref 3 - channel) - Lang\n",
    "- Map Lang to human readable\n",
    "\n",
    "**Type Conversion**\n",
    "- Convert Duration to seconds\n",
    "- Convert Channel_PublishedAt to Datetime\n",
    "\n",
    "**Derived Variable**\n",
    "- Derive a new variable :- PublishedAfter(Days) - > in days (publish_time - Channel_PublishedAt approximated to nearest number of days)\n",
    "\n",
    "**translate(future)**\n",
    "- description to identify the language\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "Cleaned_Video_Channel_DF = New_Video_Channel_DF.copy()\n",
    "Cleaned_Video_Channel_DF.drop(['Channel_Title','ChannelTitle','thumbnail_link'], axis=1, inplace=True)\n",
    "#Cleaned_Video_Channel_DF.columns\n",
    "Cleaned_Video_Channel_DF.rename(columns={\n",
    "   'country': 'viewing_country',\n",
    "   'country_CH': 'origin_country',\n",
    "   'Caption' : 'Caption_Enabled',\n",
    "   'GoogleAnalytics' : 'GoogleAnalyticsUsed',\n",
    "   'moderateComments' : 'IsmoderatingComments',\n",
    "   'topicCategories' : 'channelTopicCategories'\n",
    "}, inplace=True)\n",
    "#Cleaned_Video_Channel_DF.columns\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**All the category JSON files contain the same info with an exception that, in US, there is an additional category called '29': 'Nonprofits & Activism'. Hence, taking US json file for categories for all countries**\n",
    "\n",
    "\n",
    "\n",
    "List_of_JSON = [(file, file.split('_')[0]) for file in list(os.walk(os.path.join('..', 'Data')))[0][-1] if (file.endswith(\"category_id.json\"))]   \n",
    "Dict_Country_Catogory = dict()\n",
    "for file, country in List_of_JSON:\n",
    "    with open(os.path.join('..', 'Data', file)) as f:\n",
    "        data = json.load(f)\n",
    "        Dict_Country_Catogory.update({country:{item['id']: item['snippet']['title'] for item in data['items']}})\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "List_of_JSON = [(file, file.split('_')[0]) for file in list(os.walk(os.path.join('..', 'Data')))[0][-1] if (file.endswith(\"category_id.json\"))]   \n",
    "Dict_Catogory = dict()\n",
    "file = 'US_category_id.json'\n",
    "with open(os.path.join('..', 'Data', file)) as f:\n",
    "    data = json.load(f)\n",
    "    Dict_Catogory.update({int(item['id']): item['snippet']['title'] for item in data['items']})\n",
    "#Dict_Catogory\n",
    "\n",
    "Cleaned_Video_Channel_DF['category'] = Cleaned_Video_Channel_DF['category_id'].map(Dict_Catogory)\n",
    "Cleaned_Video_Channel_DF.drop('category_id', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "234913"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "95787"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "199903"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Cleaned_Video_Channel_DF['defaultLanguage'].isnull().sum() #Channel\n",
    "Cleaned_Video_Channel_DF['DefaultAudioLanguage'].isnull().sum() #Video\n",
    "Cleaned_Video_Channel_DF['DefaultLanguage'].isnull().sum() #Video\n",
    "\n",
    "#API call to get Language Mapping\n",
    "params = {'part':'snippet', 'key':gkey}\n",
    "response = requests.get('https://www.googleapis.com/youtube/v3/i18nLanguages', params)\n",
    "content = response.json()\n",
    "code_language_dict = dict()\n",
    "for item in content['items']:\n",
    "    code_language_dict.update({item['snippet']['hl']:item['snippet']['name']})\n",
    "    \n",
    "#Create Lang as a combination all 3 columns related to language    \n",
    "Cleaned_Video_Channel_DF['Lang'] = Cleaned_Video_Channel_DF.apply(lambda x: (x['DefaultAudioLanguage'] if ['DefaultAudioLanguage'] else (x['DefaultLanguage'] if x['DefaultLanguage'] else x['defaultLanguage'])), axis=1)                           \n",
    "Cleaned_Video_Channel_DF['Lang'] = Cleaned_Video_Channel_DF['Lang'].map(lambda x: code_language_dict.get(x,None) if x else x)\n",
    "\n",
    "#Drop redundant language columns\n",
    "Cleaned_Video_Channel_DF.drop(['DefaultAudioLanguage', 'DefaultLanguage', 'defaultLanguage'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def time_to_seconds(x):\n",
    "    hours = (int(re.findall('\\d+(?=H)', x)[0]) if re.findall('\\d+(?=H)', x) else 0)\n",
    "    minutes = (int(re.findall('\\d+(?=M)', x)[0]) if re.findall('\\d+(?=M)', x) else 0)\n",
    "    seconds = (int(re.findall('\\d+(?=S)', x)[0]) if re.findall('\\d+(?=S)', x) else 0)\n",
    "    return hours*60*60 + minutes*60 + seconds\n",
    "Cleaned_Video_Channel_DF['Duration'] = Cleaned_Video_Channel_DF['Duration'].map(lambda x: time_to_seconds(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "Cleaned_Video_Channel_DF['Channel_PublishedAt'] = pd.to_datetime(Cleaned_Video_Channel_DF['Channel_PublishedAt'], format='%Y-%m-%dT%H:%M:%SZ')  \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove Data without channel published At is NaN (only 89)\n",
    "Cleaned_Video_Channel_DF.drop(Cleaned_Video_Channel_DF[Cleaned_Video_Channel_DF['Channel_PublishedAt'].isna()].index, inplace=True)  \n",
    "\n",
    "#Remove Data with publish_time < Channel_PublishedAt (Some channels get public after publishing some content); Only 293\n",
    "Cleaned_Video_Channel_DF.drop(Cleaned_Video_Channel_DF[Cleaned_Video_Channel_DF['publish_time']<=Cleaned_Video_Channel_DF['Channel_PublishedAt']].index,inplace=True)    \n",
    "                              \n",
    "                              "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def indays(x,y):\n",
    "    z=x-y\n",
    "    return int(z.days + (1 if z.seconds/3600 >= 12 else 0))\n",
    "Cleaned_Video_Channel_DF['PublishedAfter(Days)'] = Cleaned_Video_Channel_DF.apply(lambda x: indays(x['publish_time'],x['Channel_PublishedAt']), axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(266546, 29)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Cleaned_Video_Channel_DF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['video_id', 'trending_date', 'title', 'channel_title', 'publish_time',\n",
       "       'tags', 'views', 'likes', 'dislikes', 'comment_count',\n",
       "       'comments_disabled', 'ratings_disabled', 'description',\n",
       "       'viewing_country', 'ChannelID', 'Duration', 'Caption_Enabled',\n",
       "       'RegionRestriction_Blocked', 'RegionRestriction_Allowed',\n",
       "       'Channel_Description', 'Channel_PublishedAt', 'origin_country',\n",
       "       'keywords', 'GoogleAnalyticsUsed', 'IsmoderatingComments',\n",
       "       'channelTopicCategories', 'category', 'Lang', 'PublishedAfter(Days)'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Cleaned_Video_Channel_DF.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "Sorted_Cleaned_Vid_Ch_DF = Cleaned_Video_Channel_DF.sort_values(by=['video_id', 'Lang']) #To make Na position last\n",
    "Description_DF = Sorted_Cleaned_Vid_Ch_DF.groupby('video_id')[['description','Lang']].first()\n",
    "Need_To_Translate = Description_DF[Description_DF['Lang'].isna()].copy()\n",
    "Need_To_Translate.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Language Detection through API Call\n",
    "\n",
    "**Out of 124737 unique videos, 51935 don't have Lang. Hence, we use description to understand the Lang**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample API call\n",
    "\n",
    "base = 'https://translation.googleapis.com/language/translate/v2/detect'\n",
    "\n",
    "params = {\n",
    "            'q': first_sentence(Need_To_Translate['description'][2378]),\n",
    "            'key':gkey\n",
    "}\n",
    "\n",
    "response = requests.get(base, params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def first_sentences(x, sent=2):\n",
    "    split = re.split('(?<=[.!?]) ', x)\n",
    "    return ' '.join(split[:sent]) if len(split)>sent else ' '.join(split)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def first_100_letters(x, letters=100):\n",
    "    return (x[:100] if len(x)>100 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#PLEASE DON'T RUN ACCEDENTALLY\n",
    "start_time = time.time()\n",
    "\n",
    "\n",
    "base =  'https://translation.googleapis.com/language/translate/v2/detect'\n",
    "\n",
    "\n",
    "BS = 5\n",
    "\n",
    "for iteration in range(((Need_To_Translate.shape[0]//BS + 1) if Need_To_Translate.shape[0]%BS else Need_To_Translate.shape[0]//BS)):\n",
    "    Batch = (Need_To_Translate['description'][iteration*BS:iteration*BS+BS] if (iteration+1)*BS<=Need_To_Translate.shape[0] else Need_To_Translate['description'][iteration*BS:])\n",
    "    #Batch = Batch.map(lambda x: first_sentences(x)).to_list()\n",
    "    Batch = Batch.map(lambda x: first_100_letters(x)).to_list()\n",
    "    \n",
    "    params = {\n",
    "            'q': Batch,\n",
    "            'key':gkey\n",
    "            }\n",
    "    \n",
    "    response = requests.get(base, params)\n",
    " \n",
    "    count=0\n",
    "    try:\n",
    "        content = response.json()\n",
    "        for item in content['data']['detections']:\n",
    "            Need_To_Translate.loc[iteration*BS + count, 'Lang']=item[0]['language']\n",
    "            count+=1\n",
    "    except:\n",
    "        print(response.url)\n",
    "        print(response)\n",
    "        print(f\"Didn't get response for iteration {iteration}\")\n",
    "        \n",
    "    time.sleep(1)\n",
    "    print(iteration*BS)\n",
    "    print(Need_To_Translate.loc[iteration*BS:iteration*BS+count, 'Lang'])\n",
    "time_taken_in_min=(time.time()-start_time)//60 \n",
    "#Before I start,\n",
    "  \n",
    "            \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving the scraped data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Need_To_Translate.to_pickle(\"../Data/Lang_Detect_TILL_NOW.pkl\")\n",
    "Translated = pd.read_pickle(\"../Data/Lang_Detect_TILL_NOW.pkl\")\n",
    "Dict1 = Description_DF[~Description_DF['Lang'].isnull()]['Lang'].to_dict()\n",
    "Translated.set_index('video_id',inplace=True)\n",
    "Dict2 = Translated[~Translated['Lang'].map(code_language_dict).isnull()]['Lang'].map(code_language_dict).to_dict() #Some languages are undetermined, not in the list etc\n",
    "Dict1.update(Dict2)#VideoID - Language Mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Translated.to_csv(\"../Output/Lang_Detect_TILL_NOW.csv\", encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add the language to the Content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "Cleaned_Video_Channel_DF.loc[:,'Lang'] = Cleaned_Video_Channel_DF['video_id'].map(Dict1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 266546 entries, 0 to 266545\n",
      "Data columns (total 29 columns):\n",
      " #   Column                     Non-Null Count   Dtype         \n",
      "---  ------                     --------------   -----         \n",
      " 0   video_id                   266546 non-null  object        \n",
      " 1   trending_date              266546 non-null  datetime64[ns]\n",
      " 2   title                      266546 non-null  object        \n",
      " 3   channel_title              266546 non-null  object        \n",
      " 4   publish_time               266546 non-null  datetime64[ns]\n",
      " 5   tags                       266546 non-null  object        \n",
      " 6   views                      266546 non-null  int64         \n",
      " 7   likes                      266546 non-null  int64         \n",
      " 8   dislikes                   266546 non-null  int64         \n",
      " 9   comment_count              266546 non-null  int64         \n",
      " 10  comments_disabled          266546 non-null  bool          \n",
      " 11  ratings_disabled           266546 non-null  bool          \n",
      " 12  description                266546 non-null  object        \n",
      " 13  viewing_country            266546 non-null  object        \n",
      " 14  ChannelID                  266546 non-null  object        \n",
      " 15  Duration                   266546 non-null  int64         \n",
      " 16  Caption_Enabled            266546 non-null  object        \n",
      " 17  RegionRestriction_Blocked  10294 non-null   object        \n",
      " 18  RegionRestriction_Allowed  10554 non-null   object        \n",
      " 19  Channel_Description        266546 non-null  object        \n",
      " 20  Channel_PublishedAt        266546 non-null  datetime64[ns]\n",
      " 21  origin_country             204605 non-null  object        \n",
      " 22  keywords                   230751 non-null  object        \n",
      " 23  GoogleAnalyticsUsed        266546 non-null  object        \n",
      " 24  IsmoderatingComments       266546 non-null  object        \n",
      " 25  channelTopicCategories     266358 non-null  object        \n",
      " 26  category                   266546 non-null  object        \n",
      " 27  Lang                       254406 non-null  object        \n",
      " 28  PublishedAfter(Days)       266546 non-null  int64         \n",
      "dtypes: bool(2), datetime64[ns](3), int64(6), object(18)\n",
      "memory usage: 55.4+ MB\n"
     ]
    }
   ],
   "source": [
    "Cleaned_Video_Channel_DF.reset_index(drop=True, inplace=True)\n",
    "Cleaned_Video_Channel_DF.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Cleaned_Video_Channel_DF.to_pickle(\"../Data/Cleaned_Data(No_Agg).pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a popularity DF (by Aggregating the Cleaned_Video_Channel_DF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(266546, 29)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Cleaned_Video_Channel_DF = Cleaned_Video_Channel_DF.sort_values(by=['video_id', 'viewing_country', 'trending_date'])\n",
    "Cleaned_Video_Channel_DF.reset_index(drop=True, inplace=True)\n",
    "Cleaned_Video_Channel_DF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Cleaned_Video_Channel_DF.to_pickle(\"../Data/Cleaned_Data(No_Agg).pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "Popularity_DF = Cleaned_Video_Channel_DF.groupby(['video_id', 'viewing_country']).aggregate(Total_Trend_Days=('trending_date', 'count'), Video_Title=('title', 'first'),\n",
    "                                                                                            First_Trending=('trending_date', 'min'),\n",
    "                                                                                            Publish_Time=('publish_time', 'first'), \n",
    "                                                                                            PublishedAfter_in_Days = ('PublishedAfter(Days)', 'first'),\n",
    "                                                                                            Origin_Country = ('origin_country', 'first'),\n",
    "                                                                                            Category = ('category', 'first'),\n",
    "                                                                                            Tags = ('tags', 'first'),\n",
    "                                                                                            Duration = ('Duration', 'first'), Language = ('Lang', 'first'),\n",
    "                                                                                            Views = ('views', 'first'), Likes=('likes', 'first'),\n",
    "                                                                                            Dislikes = ('dislikes', 'first'), Comment_Count = ('comment_count', 'first'),\n",
    "                                                                                            Comments_Disabled=('comments_disabled', 'first'),\n",
    "                                                                                            Ratings_Disabled=('ratings_disabled', 'first'),\n",
    "                                                                                            Caption_Enabled = ('Caption_Enabled', 'first'),\n",
    "                                                                                            Video_Description = ('description', 'first'),\n",
    "                                                                                            Blocked = ('RegionRestriction_Blocked', 'first'),\n",
    "                                                                                            Allowed = ('RegionRestriction_Allowed', 'first'),\n",
    "                                                                                            Channel_Title = ('channel_title', 'first'),\n",
    "                                                                                            Channel_Description = ('Channel_Description', 'first'),\n",
    "                                                                                            Channel_PublishedAt = ('Channel_PublishedAt', 'first'),\n",
    "                                                                                            Channel_Keywords = ('keywords', 'first'),\n",
    "                                                                                            ChannelTopicCategories = ('channelTopicCategories', 'first'),\n",
    "                                                                                            GoogleAnalyticsUsed = ('GoogleAnalyticsUsed', 'first'), #Should not use in prediction as it is the current info\n",
    "                                                                                            IsmoderatingComments = ('IsmoderatingComments', 'first'),) #Should not use in prediction as it is the current info\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "Popularity_DF.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(142846, 29)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Popularity_DF.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save the PopularityDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Popularity_DF.to_pickle(\"../Data/Popularity_DF.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "DF = pd.read_pickle(\"../Data/Popularity_DF.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 142846 entries, 0 to 142845\n",
      "Data columns (total 29 columns):\n",
      " #   Column                  Non-Null Count   Dtype         \n",
      "---  ------                  --------------   -----         \n",
      " 0   video_id                142846 non-null  object        \n",
      " 1   viewing_country         142846 non-null  object        \n",
      " 2   Total_Trend_Days        142846 non-null  int64         \n",
      " 3   Video_Title             142846 non-null  object        \n",
      " 4   First_Trending          142846 non-null  datetime64[ns]\n",
      " 5   Publish_Time            142846 non-null  datetime64[ns]\n",
      " 6   PublishedAfter_in_Days  142846 non-null  int64         \n",
      " 7   Origin_Country          115154 non-null  object        \n",
      " 8   Category                142846 non-null  object        \n",
      " 9   Tags                    142846 non-null  object        \n",
      " 10  Duration                142846 non-null  int64         \n",
      " 11  Language                134847 non-null  object        \n",
      " 12  Views                   142846 non-null  int64         \n",
      " 13  Likes                   142846 non-null  int64         \n",
      " 14  Dislikes                142846 non-null  int64         \n",
      " 15  Comment_Count           142846 non-null  int64         \n",
      " 16  Comments_Disabled       142846 non-null  bool          \n",
      " 17  Ratings_Disabled        142846 non-null  bool          \n",
      " 18  Caption_Enabled         142846 non-null  object        \n",
      " 19  Video_Description       142846 non-null  object        \n",
      " 20  Blocked                 6609 non-null    object        \n",
      " 21  Allowed                 3681 non-null    object        \n",
      " 22  Channel_Title           142846 non-null  object        \n",
      " 23  Channel_Description     142846 non-null  object        \n",
      " 24  Channel_PublishedAt     142846 non-null  datetime64[ns]\n",
      " 25  Channel_Keywords        122934 non-null  object        \n",
      " 26  ChannelTopicCategories  142731 non-null  object        \n",
      " 27  GoogleAnalyticsUsed     142846 non-null  bool          \n",
      " 28  IsmoderatingComments    142846 non-null  bool          \n",
      "dtypes: bool(4), datetime64[ns](3), int64(7), object(15)\n",
      "memory usage: 27.8+ MB\n"
     ]
    }
   ],
   "source": [
    "DF.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Popularity_FS_DF which includes final states of views-likes-dislikes-comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "Popularity_FS_DF = Cleaned_Video_Channel_DF.groupby(['video_id', 'viewing_country']).aggregate(Total_Trend_Days=('trending_date', 'count'), Video_Title=('title', 'first'),\n",
    "                                                                                            First_Trending=('trending_date', 'min'),\n",
    "                                                                                            Publish_Time=('publish_time', 'first'), \n",
    "                                                                                            PublishedAfter_in_Days = ('PublishedAfter(Days)', 'first'),\n",
    "                                                                                            Origin_Country = ('origin_country', 'first'),\n",
    "                                                                                            Category = ('category', 'first'),\n",
    "                                                                                            Tags = ('tags', 'first'),\n",
    "                                                                                            Duration = ('Duration', 'first'), Language = ('Lang', 'first'),\n",
    "                                                                                            Views_I = ('views', 'first'), Views_F = ('views', 'last'),\n",
    "                                                                                            Likes_I=('likes', 'first'), Likes_F=('likes', 'last'),\n",
    "                                                                                            Dislikes_I = ('dislikes', 'first'), Dislikes_F = ('dislikes', 'last'),\n",
    "                                                                                            Comment_Count_I = ('comment_count', 'first'), Comment_Count_F = ('comment_count', 'last'),\n",
    "                                                                                            Comments_Disabled=('comments_disabled', 'first'),\n",
    "                                                                                            Ratings_Disabled=('ratings_disabled', 'first'),\n",
    "                                                                                            Caption_Enabled = ('Caption_Enabled', 'first'),\n",
    "                                                                                            Video_Description = ('description', 'first'),\n",
    "                                                                                            Blocked = ('RegionRestriction_Blocked', 'first'),\n",
    "                                                                                            Allowed = ('RegionRestriction_Allowed', 'first'),\n",
    "                                                                                            Channel_Title = ('channel_title', 'first'),\n",
    "                                                                                            Channel_Description = ('Channel_Description', 'first'),\n",
    "                                                                                            Channel_PublishedAt = ('Channel_PublishedAt', 'first'),\n",
    "                                                                                            Channel_Keywords = ('keywords', 'first'),\n",
    "                                                                                            ChannelTopicCategories = ('channelTopicCategories', 'first'),\n",
    "                                                                                            GoogleAnalyticsUsed = ('GoogleAnalyticsUsed', 'first'), #Should not use in prediction as it is the current info\n",
    "                                                                                            IsmoderatingComments = ('IsmoderatingComments', 'first'),) #Should not use in prediction as it is the current info\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Popularity_FS_DF.to_pickle(\"../Data/Popularity_FS_DF.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataframe to be used for regionwise insights"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**For region wise analysis, we can use the full version of data as the viewing country and trending information doesn't have any NaNs**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['video_id', 'trending_date', 'title', 'channel_title', 'category_id',\n",
       "       'publish_time', 'tags', 'views', 'likes', 'dislikes', 'comment_count',\n",
       "       'thumbnail_link', 'comments_disabled', 'ratings_disabled',\n",
       "       'description', 'country', 'ChannelID', 'ChannelTitle',\n",
       "       'DefaultLanguage', 'DefaultAudioLanguage', 'Duration', 'Caption',\n",
       "       'RegionRestriction_Blocked', 'RegionRestriction_Allowed'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(360217, 24)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Merge the Video Data with Scraped Data (left)\n",
    "New_Video_DF = Video_DF.merge(Scraped_DF, how='left', on='video_id')\n",
    "New_Video_DF.columns\n",
    "New_Video_DF.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#New_Video_DF.to_pickle(\"../Data/New_Video_DF_360217.pkl\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}