0-dump-csv-from-youtube-crawl.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import re\n",
    "import pandas as pd\n",
    "import glob\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !unzip 'input/youtube-crawler.zip' -d 'input/youtube/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_file_paths = 'input/youtube/'\n",
    "output1_file_path = 'output/0-youtube-paragraph.csv'\n",
    "output2_file_path = 'output/0-youtube-sentences.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Query</th>\n",
       "      <th>Channel Name</th>\n",
       "      <th>Video Title</th>\n",
       "      <th>Video Description</th>\n",
       "      <th>Video ID</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Comment ID</th>\n",
       "      <th>Comment Reply</th>\n",
       "      <th>Comment Like</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>কৃষ্ণাঙ্গ</td>\n",
       "      <td>Jamuna TV</td>\n",
       "      <td>যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...</td>\n",
       "      <td>নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...</td>\n",
       "      <td>APRpSExQl7Y</td>\n",
       "      <td>এই পুলিশকে ও এবাবে মেরে ফেলে উচিৎ</td>\n",
       "      <td>UgxyQWOAsyvXxVvxp-Z4AaABAg</td>\n",
       "      <td>14</td>\n",
       "      <td>328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>কৃষ্ণাঙ্গ</td>\n",
       "      <td>Jamuna TV</td>\n",
       "      <td>যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...</td>\n",
       "      <td>নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...</td>\n",
       "      <td>APRpSExQl7Y</td>\n",
       "      <td>অমেরিকা উন্নত দেশ হলে..অমরিকান লোক সবচেয়ে বেশি...</td>\n",
       "      <td>UgxXKy8QwTW7ER91jgR4AaABAg</td>\n",
       "      <td>6</td>\n",
       "      <td>173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>কৃষ্ণাঙ্গ</td>\n",
       "      <td>Jamuna TV</td>\n",
       "      <td>যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...</td>\n",
       "      <td>নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...</td>\n",
       "      <td>APRpSExQl7Y</td>\n",
       "      <td>কুত্তার জাতী আমেরিকা</td>\n",
       "      <td>UgzaZ-h6vU-Exk4ApIt4AaABAg</td>\n",
       "      <td>2</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>কৃষ্ণাঙ্গ</td>\n",
       "      <td>Jamuna TV</td>\n",
       "      <td>যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...</td>\n",
       "      <td>নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...</td>\n",
       "      <td>APRpSExQl7Y</td>\n",
       "      <td>বাংলাদেশের পুলিশের চেয়েও খারাপ</td>\n",
       "      <td>UgzHzIQGAmvmrzedamx4AaABAg</td>\n",
       "      <td>8</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>কৃষ্ণাঙ্গ</td>\n",
       "      <td>Jamuna TV</td>\n",
       "      <td>যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...</td>\n",
       "      <td>নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...</td>\n",
       "      <td>APRpSExQl7Y</td>\n",
       "      <td>মানবাধিকার সংগঠনগুলো এখন কোথায় 🤔🤔🤔🤔🤔🤔</td>\n",
       "      <td>Ugz1L1YA3i3-3igEWhh4AaABAg</td>\n",
       "      <td>2</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Query Channel Name                                        Video Title  \\\n",
       "0  কৃষ্ণাঙ্গ    Jamuna TV  যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...   \n",
       "1  কৃষ্ণাঙ্গ    Jamuna TV  যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...   \n",
       "2  কৃষ্ণাঙ্গ    Jamuna TV  যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...   \n",
       "3  কৃষ্ণাঙ্গ    Jamuna TV  যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...   \n",
       "4  কৃষ্ণাঙ্গ    Jamuna TV  যুক্তরাষ্ট্রে কৃষ্ণাঙ্গ ব্যক্তিকে গলায় পাড়া দি...   \n",
       "\n",
       "                                   Video Description     Video ID  \\\n",
       "0  নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...  APRpSExQl7Y   \n",
       "1  নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...  APRpSExQl7Y   \n",
       "2  নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...  APRpSExQl7Y   \n",
       "3  নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...  APRpSExQl7Y   \n",
       "4  নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...  APRpSExQl7Y   \n",
       "\n",
       "                                             Comment  \\\n",
       "0                  এই পুলিশকে ও এবাবে মেরে ফেলে উচিৎ   \n",
       "1  অমেরিকা উন্নত দেশ হলে..অমরিকান লোক সবচেয়ে বেশি...   \n",
       "2                               কুত্তার জাতী আমেরিকা   \n",
       "3                     বাংলাদেশের পুলিশের চেয়েও খারাপ   \n",
       "4             মানবাধিকার সংগঠনগুলো এখন কোথায় 🤔🤔🤔🤔🤔🤔   \n",
       "\n",
       "                   Comment ID  Comment Reply  Comment Like  \n",
       "0  UgxyQWOAsyvXxVvxp-Z4AaABAg             14           328  \n",
       "1  UgxXKy8QwTW7ER91jgR4AaABAg              6           173  \n",
       "2  UgzaZ-h6vU-Exk4ApIt4AaABAg              2           150  \n",
       "3  UgzHzIQGAmvmrzedamx4AaABAg              8           139  \n",
       "4  Ugz1L1YA3i3-3igEWhh4AaABAg              2            88  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "text_list=[]\n",
    "\n",
    "files = glob.glob(os.path.join(input_file_paths, '*.csv'))\n",
    "big_csv= pd.concat([pd.read_csv(file) for file in files])\n",
    "big_csv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Video Description</th>\n",
       "      <th>Video ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...</td>\n",
       "      <td>APRpSExQl7Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>কৃষ্ণাঙ্গ নাগরিক হত্যার ঘটনায় ফের উত্তাল যুক্...</td>\n",
       "      <td>rpPpVDQ7Yog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>কৃষ্ণাঙ্গ হত্যার ঘটনায়, প্রতিবাদের আগুন ছড়িয...</td>\n",
       "      <td>5V856IvE6SE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>কৃষ্ণাঙ্গ নাগরিক জর্জ ফ্লয়েডের খুনের ঘটনায় উ...</td>\n",
       "      <td>R97ZhCfm0N4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>আমেরিকায় এত বিপুল সংখ্যক কালো চামড়ার সেলিব্র...</td>\n",
       "      <td>kNq1xK_HCZg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Video Description     Video ID\n",
       "0    নিছক সন্দেহের বশে আটকের পর, প্রকাশ্যে গলায় পা...  APRpSExQl7Y\n",
       "100  কৃষ্ণাঙ্গ নাগরিক হত্যার ঘটনায় ফের উত্তাল যুক্...  rpPpVDQ7Yog\n",
       "200  কৃষ্ণাঙ্গ হত্যার ঘটনায়, প্রতিবাদের আগুন ছড়িয...  5V856IvE6SE\n",
       "215  কৃষ্ণাঙ্গ নাগরিক জর্জ ফ্লয়েডের খুনের ঘটনায় উ...  R97ZhCfm0N4\n",
       "315  আমেরিকায় এত বিপুল সংখ্যক কালো চামড়ার সেলিব্র...  kNq1xK_HCZg"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "youtube_paragraph= big_csv[[\"Video Description\",\"Video ID\"]]\n",
    "youtube_paragraph= youtube_paragraph.drop_duplicates()\n",
    "youtube_paragraph.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Comment</th>\n",
       "      <th>Comment ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>এই পুলিশকে ও এবাবে মেরে ফেলে উচিৎ</td>\n",
       "      <td>UgxyQWOAsyvXxVvxp-Z4AaABAg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>অমেরিকা উন্নত দেশ হলে..অমরিকান লোক সবচেয়ে বেশি...</td>\n",
       "      <td>UgxXKy8QwTW7ER91jgR4AaABAg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>কুত্তার জাতী আমেরিকা</td>\n",
       "      <td>UgzaZ-h6vU-Exk4ApIt4AaABAg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>বাংলাদেশের পুলিশের চেয়েও খারাপ</td>\n",
       "      <td>UgzHzIQGAmvmrzedamx4AaABAg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>মানবাধিকার সংগঠনগুলো এখন কোথায় 🤔🤔🤔🤔🤔🤔</td>\n",
       "      <td>Ugz1L1YA3i3-3igEWhh4AaABAg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Comment  \\\n",
       "0                  এই পুলিশকে ও এবাবে মেরে ফেলে উচিৎ   \n",
       "1  অমেরিকা উন্নত দেশ হলে..অমরিকান লোক সবচেয়ে বেশি...   \n",
       "2                               কুত্তার জাতী আমেরিকা   \n",
       "3                     বাংলাদেশের পুলিশের চেয়েও খারাপ   \n",
       "4             মানবাধিকার সংগঠনগুলো এখন কোথায় 🤔🤔🤔🤔🤔🤔   \n",
       "\n",
       "                   Comment ID  \n",
       "0  UgxyQWOAsyvXxVvxp-Z4AaABAg  \n",
       "1  UgxXKy8QwTW7ER91jgR4AaABAg  \n",
       "2  UgzaZ-h6vU-Exk4ApIt4AaABAg  \n",
       "3  UgzHzIQGAmvmrzedamx4AaABAg  \n",
       "4  Ugz1L1YA3i3-3igEWhh4AaABAg  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "youtube_sentence= big_csv[[\"Comment\",\"Comment ID\"]]\n",
    "youtube_sentence= youtube_sentence.drop_duplicates()\n",
    "youtube_sentence.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# youtube_paragraph.to_csv(output1_file_path, index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# youtube_sentence.to_csv(output2_file_path, index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}