1_Notebook_DataCollection & Anonymization.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Collection, Anonymization and Initial Data Cleaning\n",
    "\n",
    "The present notebook is part of a series of notebooks related to the MSc. thesis: **Sentiment analysis on generative language models    based on Social Media commentary of industry participants**\n",
    "\n",
    "The MSc. thesis research was conducted based on tweets about ChatGPT. These were collected, processed and analyzed with the scope of answering the following research question:\n",
    "\n",
    "**How are generative language models perceived by participants of different industries based on social media commentary?**\n",
    "\n",
    "As part of the process of data handling, this notebook presents the data collection process, the anonymization of the data and its initial cleaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import cell of necessary packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from string import digits\n",
    "\n",
    "#Set pandas options to display 500 rows, for ease of cleaning\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.options.mode.chained_assignment = None\n",
    "\n",
    "#sweet_viz package is used for initial Exploratory Data Analysis(EDA)\n",
    "import sweetviz as sv\n",
    "#Surpass long future warnings\n",
    "import warnings\n",
    "warnings.simplefilter(action='ignore')\n",
    "\n",
    "#Scraper module to get twitter data\n",
    "import snscrape.modules.twitter as sntwitter"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Collection ##"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section of the notebook the methods used in collecting the tweets are described. \n",
    "The data was collected with the help of *snscrape* package and its specific module handling tweets, *snscrape.modules.twitter*.\n",
    "\n",
    "A function *data_collection* was created to ease the scraping process. Due to computational limitations, the function was then run multiple times until all the desired tweets were collected. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Query statement to be used in tweets scraping\n",
    "query=\"ChatGpt lang:en since:2022-11-30\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Tweet 1622448321863487488 contains an app icon medium key '4_1644860781903900672' on app 'android_app'/'com.leh.app', but the corresponding medium is missing; dropping\n"
     ]
    }
   ],
   "source": [
    "#Scraping function \n",
    "def data_collection(query, max_tweets_int, name_csv):\n",
    "\n",
    "    #Initialize empty dataframe\n",
    "    tweets_df = pd.DataFrame()\n",
    "    #Get tweets scraped\n",
    "    for i, tweet in enumerate(sntwitter.TwitterSearchScraper(query).get_items()):  \n",
    "        if i > max_tweets_int:\n",
    "            break\n",
    "\n",
    "        #Create tweetDict to ease data storage\n",
    "        tweetDict = {'tweet_id':'',\n",
    "                    'tweet_date': '',\n",
    "                    'tweet_content':'',\n",
    "                    'tweet_source':'',\n",
    "                    'tweet_replycount':'',\n",
    "                    'tweet_retweetcount':'',\n",
    "                    'tweet_likecount':'',\n",
    "                    'tweet_inreplytotweetid':'',\n",
    "                    'tweet_inreplytouser':'',\n",
    "                    'tweet_hashtags':'',\n",
    "                    'tweet_media':'',\n",
    "                    'usr_description': '',\n",
    "                    'usr_username':'',\n",
    "                    'tweet_vibe':'',\n",
    "                    'usr_verified':'',\n",
    "                    'usr_follower_count':'',\n",
    "                    'usr_location':'',\n",
    "                    'usr_userid':'',\n",
    "                    'usr_created':''\n",
    "                    }\n",
    "        tweetDict['tweet_id'] = [tweet.id]  \n",
    "        tweetDict['tweet_date'] = [tweet.date]\n",
    "        tweetDict['tweet_content'] = [tweet.rawContent]\n",
    "        tweetDict['tweet_source'] = [tweet.source]\n",
    "        tweetDict['tweet_replycount'] = [tweet.replyCount]\n",
    "        tweetDict['tweet_retweetcount'] = [tweet.retweetCount]\n",
    "        tweetDict['tweet_likecount'] = [tweet.likeCount]\n",
    "        tweetDict['tweet_media'] = [tweet.media]\n",
    "        tweetDict['tweet_vibe'] = [tweet.vibe]\n",
    "        tweetDict['tweet_inreplytotweetid'] = [tweet.inReplyToTweetId]\n",
    "        tweetDict['tweet_inreplytouser'] = [tweet.inReplyToUser]\n",
    "        tweetDict['tweet_hashtags'] = [tweet.hashtags]\n",
    "        tweetDict['usr_description'] = [tweet.user.description]\n",
    "        tweetDict['usr_username'] = [tweet.user.username]\n",
    "        tweetDict['usr_created'] = [tweet.user.created]\n",
    "        tweetDict['usr_userid'] = [tweet.user.id]\n",
    "        tweetDict['usr_verified'] = [tweet.user.verified]\n",
    "        tweetDict['usr_follower_count'] = [tweet.user.followersCount]\n",
    "        tweetDict['usr_location'] = [tweet.user.location]\n",
    "        \n",
    "        #store data as dataframe\n",
    "        row= pd.DataFrame.from_dict(tweetDict)\n",
    "        #append row to tweets dataframe\n",
    "        tweets_df = pd.concat([tweets_df,row], ignore_index=True)\n",
    "    #return csv file wit tweets    \n",
    "    return tweets_df.to_csv(f'{name_csv}.csv', index=False)\n",
    "\n",
    "#TODO: when scraping remove comment of row below\n",
    "#data_collection(query, 1000000, '2023Feb25_toNA')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section , cleaning considereation are made and data is processed based on those. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO: load raw data\n",
    "df_tweet_25FebOnwards= pd.read_csv(\"data_files/scraped_RAW_data/2023Feb25_toNA.csv\")\n",
    "df_tweet_Prior25Feb = pd.read_csv(\"data_files/scraped_RAW_data/data_prior25Feb.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>tweet_id</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>tweet_content</th>\n",
       "      <th>tweet_source</th>\n",
       "      <th>tweet_replycount</th>\n",
       "      <th>tweet_retweetcount</th>\n",
       "      <th>tweet_likecount</th>\n",
       "      <th>tweet_inreplytotweetid</th>\n",
       "      <th>tweet_inreplytouser</th>\n",
       "      <th>tweet_hashtags</th>\n",
       "      <th>tweet_media</th>\n",
       "      <th>usr_description</th>\n",
       "      <th>usr_username</th>\n",
       "      <th>tweet_vibe</th>\n",
       "      <th>usr_verified</th>\n",
       "      <th>usr_follower_count</th>\n",
       "      <th>usr_location</th>\n",
       "      <th>usr_userid</th>\n",
       "      <th>usr_created</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1644449184060252160</td>\n",
       "      <td>2023-04-07 21:16:52+00:00</td>\n",
       "      <td>I am thinking of writing a plugin for ChatGPT ...</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['ChatGPTto']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I say things. Things so important that they ma...</td>\n",
       "      <td>jscix</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>70</td>\n",
       "      <td>A small outpost on Pluto</td>\n",
       "      <td>17632837</td>\n",
       "      <td>2008-11-25 21:50:18+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1644449176942530561</td>\n",
       "      <td>2023-04-07 21:16:50+00:00</td>\n",
       "      <td>$20 a month for ChatGPT? https://t.co/acRmNWghBl</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Photo(previewUrl='https://pbs.twimg.com/media...</td>\n",
       "      <td>CTO @mach_49</td>\n",
       "      <td>mdscaff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1541</td>\n",
       "      <td>Redwood City, CA</td>\n",
       "      <td>14456867</td>\n",
       "      <td>2008-04-21 01:11:15+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1644449166775529474</td>\n",
       "      <td>2023-04-07 21:16:48+00:00</td>\n",
       "      <td>COVID : TREATMENT IDEAS\\n\\n#CHATGPT</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/android\" ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['CHATGPT']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Oxford DPhil in Zoology, Environmental Rating ...</td>\n",
       "      <td>mattprescott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>24743</td>\n",
       "      <td>Little England</td>\n",
       "      <td>21855179</td>\n",
       "      <td>2009-02-25 11:07:48+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id                 tweet_date  \\\n",
       "0  1644449184060252160  2023-04-07 21:16:52+00:00   \n",
       "1  1644449176942530561  2023-04-07 21:16:50+00:00   \n",
       "2  1644449166775529474  2023-04-07 21:16:48+00:00   \n",
       "\n",
       "                                       tweet_content  \\\n",
       "0  I am thinking of writing a plugin for ChatGPT ...   \n",
       "1   $20 a month for ChatGPT? https://t.co/acRmNWghBl   \n",
       "2                COVID : TREATMENT IDEAS\\n\\n#CHATGPT   \n",
       "\n",
       "                                        tweet_source  tweet_replycount  \\\n",
       "0  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "1  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "2  <a href=\"http://twitter.com/download/android\" ...                 0   \n",
       "\n",
       "   tweet_retweetcount  tweet_likecount  tweet_inreplytotweetid  \\\n",
       "0                   0                0                     NaN   \n",
       "1                   0                0                     NaN   \n",
       "2                   0                0                     NaN   \n",
       "\n",
       "  tweet_inreplytouser tweet_hashtags  \\\n",
       "0                 NaN  ['ChatGPTto']   \n",
       "1                 NaN            NaN   \n",
       "2                 NaN    ['CHATGPT']   \n",
       "\n",
       "                                         tweet_media  \\\n",
       "0                                                NaN   \n",
       "1  [Photo(previewUrl='https://pbs.twimg.com/media...   \n",
       "2                                                NaN   \n",
       "\n",
       "                                     usr_description  usr_username tweet_vibe  \\\n",
       "0  I say things. Things so important that they ma...         jscix        NaN   \n",
       "1                                       CTO @mach_49       mdscaff        NaN   \n",
       "2  Oxford DPhil in Zoology, Environmental Rating ...  mattprescott        NaN   \n",
       "\n",
       "   usr_verified  usr_follower_count              usr_location  usr_userid  \\\n",
       "0         False                  70  A small outpost on Pluto    17632837   \n",
       "1         False                1541          Redwood City, CA    14456867   \n",
       "2         False               24743            Little England    21855179   \n",
       "\n",
       "                 usr_created  \n",
       "0  2008-11-25 21:50:18+00:00  \n",
       "1  2008-04-21 01:11:15+00:00  \n",
       "2  2009-02-25 11:07:48+00:00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Inspect how raw data csv files look like\n",
    "df_tweet_25FebOnwards.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>tweet_id</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>tweet_content</th>\n",
       "      <th>tweet_source</th>\n",
       "      <th>tweet_replycount</th>\n",
       "      <th>tweet_retweetcount</th>\n",
       "      <th>tweet_likecount</th>\n",
       "      <th>tweet_inreplytotweetid</th>\n",
       "      <th>tweet_inreplytouser</th>\n",
       "      <th>tweet_hashtags</th>\n",
       "      <th>tweet_media</th>\n",
       "      <th>usr_description</th>\n",
       "      <th>usr_username</th>\n",
       "      <th>tweet_vibe</th>\n",
       "      <th>usr_verified</th>\n",
       "      <th>usr_follower_count</th>\n",
       "      <th>usr_location</th>\n",
       "      <th>usr_userid</th>\n",
       "      <th>usr_created</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1609685677528023043</td>\n",
       "      <td>2023-01-01 22:59:06+00:00</td>\n",
       "      <td>Playing with ChatGPT: Very Fun</td>\n",
       "      <td>&lt;a href=\"https://about.twitter.com/products/tw...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Participant, #NBATwitter; Dean, #PistonsTwitte...</td>\n",
       "      <td>lazchance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>5645</td>\n",
       "      <td>Raleigh, NC</td>\n",
       "      <td>25218618</td>\n",
       "      <td>2009-03-19 02:33:31+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1609685668292366337</td>\n",
       "      <td>2023-01-01 22:59:04+00:00</td>\n",
       "      <td>#power #dax What the F*K, ChatGPT know Power-B...</td>\n",
       "      <td>&lt;a href=\"https://dlvrit.com/\" rel=\"nofollow\"&gt;d...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['power', 'dax']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>🕵🏻‍♂️Consultant |👨🏻‍💼Managing Director |✍🏼Auth...</td>\n",
       "      <td>PhilippeJB_PJB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1015</td>\n",
       "      <td>World</td>\n",
       "      <td>1167839853184258049</td>\n",
       "      <td>2019-08-31 16:41:45+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1609685651762589697</td>\n",
       "      <td>2023-01-01 22:59:00+00:00</td>\n",
       "      <td>@gdb chatGPT was a flop in 2022.</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/iphone\" r...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.609245e+18</td>\n",
       "      <td>https://twitter.com/gdb</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Interface Manager - Midstream projects | Energ...</td>\n",
       "      <td>amitarunk</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>652</td>\n",
       "      <td>USA/India</td>\n",
       "      <td>907077838351847426</td>\n",
       "      <td>2017-09-11 03:06:24+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id                 tweet_date  \\\n",
       "0  1609685677528023043  2023-01-01 22:59:06+00:00   \n",
       "1  1609685668292366337  2023-01-01 22:59:04+00:00   \n",
       "2  1609685651762589697  2023-01-01 22:59:00+00:00   \n",
       "\n",
       "                                       tweet_content  \\\n",
       "0                     Playing with ChatGPT: Very Fun   \n",
       "1  #power #dax What the F*K, ChatGPT know Power-B...   \n",
       "2                   @gdb chatGPT was a flop in 2022.   \n",
       "\n",
       "                                        tweet_source  tweet_replycount  \\\n",
       "0  <a href=\"https://about.twitter.com/products/tw...                 0   \n",
       "1  <a href=\"https://dlvrit.com/\" rel=\"nofollow\">d...                 0   \n",
       "2  <a href=\"http://twitter.com/download/iphone\" r...                 1   \n",
       "\n",
       "   tweet_retweetcount  tweet_likecount  tweet_inreplytotweetid  \\\n",
       "0                   0                1                     NaN   \n",
       "1                   0                0                     NaN   \n",
       "2                   0                3            1.609245e+18   \n",
       "\n",
       "       tweet_inreplytouser    tweet_hashtags tweet_media  \\\n",
       "0                      NaN               NaN         NaN   \n",
       "1                      NaN  ['power', 'dax']         NaN   \n",
       "2  https://twitter.com/gdb               NaN         NaN   \n",
       "\n",
       "                                     usr_description    usr_username  \\\n",
       "0  Participant, #NBATwitter; Dean, #PistonsTwitte...       lazchance   \n",
       "1  🕵🏻‍♂️Consultant |👨🏻‍💼Managing Director |✍🏼Auth...  PhilippeJB_PJB   \n",
       "2  Interface Manager - Midstream projects | Energ...       amitarunk   \n",
       "\n",
       "  tweet_vibe  usr_verified  usr_follower_count usr_location  \\\n",
       "0        NaN         False                5645  Raleigh, NC   \n",
       "1        NaN         False                1015        World   \n",
       "2        NaN         False                 652    USA/India   \n",
       "\n",
       "            usr_userid                usr_created  \n",
       "0             25218618  2009-03-19 02:33:31+00:00  \n",
       "1  1167839853184258049  2019-08-31 16:41:45+00:00  \n",
       "2   907077838351847426  2017-09-11 03:06:24+00:00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tweet_Prior25Feb.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>tweet_id</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>tweet_content</th>\n",
       "      <th>tweet_source</th>\n",
       "      <th>tweet_replycount</th>\n",
       "      <th>tweet_retweetcount</th>\n",
       "      <th>tweet_likecount</th>\n",
       "      <th>tweet_inreplytotweetid</th>\n",
       "      <th>tweet_inreplytouser</th>\n",
       "      <th>tweet_hashtags</th>\n",
       "      <th>tweet_media</th>\n",
       "      <th>usr_description</th>\n",
       "      <th>usr_username</th>\n",
       "      <th>tweet_vibe</th>\n",
       "      <th>usr_verified</th>\n",
       "      <th>usr_follower_count</th>\n",
       "      <th>usr_location</th>\n",
       "      <th>usr_userid</th>\n",
       "      <th>usr_created</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1644449184060252160</td>\n",
       "      <td>2023-04-07 21:16:52+00:00</td>\n",
       "      <td>I am thinking of writing a plugin for ChatGPT ...</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['ChatGPTto']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I say things. Things so important that they ma...</td>\n",
       "      <td>jscix</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>70</td>\n",
       "      <td>A small outpost on Pluto</td>\n",
       "      <td>17632837</td>\n",
       "      <td>2008-11-25 21:50:18+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1644449176942530561</td>\n",
       "      <td>2023-04-07 21:16:50+00:00</td>\n",
       "      <td>$20 a month for ChatGPT? https://t.co/acRmNWghBl</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Photo(previewUrl='https://pbs.twimg.com/media...</td>\n",
       "      <td>CTO @mach_49</td>\n",
       "      <td>mdscaff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1541</td>\n",
       "      <td>Redwood City, CA</td>\n",
       "      <td>14456867</td>\n",
       "      <td>2008-04-21 01:11:15+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1644449166775529474</td>\n",
       "      <td>2023-04-07 21:16:48+00:00</td>\n",
       "      <td>COVID : TREATMENT IDEAS\\n\\n#CHATGPT</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/android\" ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['CHATGPT']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Oxford DPhil in Zoology, Environmental Rating ...</td>\n",
       "      <td>mattprescott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>24743</td>\n",
       "      <td>Little England</td>\n",
       "      <td>21855179</td>\n",
       "      <td>2009-02-25 11:07:48+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id                 tweet_date  \\\n",
       "0  1644449184060252160  2023-04-07 21:16:52+00:00   \n",
       "1  1644449176942530561  2023-04-07 21:16:50+00:00   \n",
       "2  1644449166775529474  2023-04-07 21:16:48+00:00   \n",
       "\n",
       "                                       tweet_content  \\\n",
       "0  I am thinking of writing a plugin for ChatGPT ...   \n",
       "1   $20 a month for ChatGPT? https://t.co/acRmNWghBl   \n",
       "2                COVID : TREATMENT IDEAS\\n\\n#CHATGPT   \n",
       "\n",
       "                                        tweet_source  tweet_replycount  \\\n",
       "0  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "1  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "2  <a href=\"http://twitter.com/download/android\" ...                 0   \n",
       "\n",
       "   tweet_retweetcount  tweet_likecount  tweet_inreplytotweetid  \\\n",
       "0                   0                0                     NaN   \n",
       "1                   0                0                     NaN   \n",
       "2                   0                0                     NaN   \n",
       "\n",
       "  tweet_inreplytouser tweet_hashtags  \\\n",
       "0                 NaN  ['ChatGPTto']   \n",
       "1                 NaN            NaN   \n",
       "2                 NaN    ['CHATGPT']   \n",
       "\n",
       "                                         tweet_media  \\\n",
       "0                                                NaN   \n",
       "1  [Photo(previewUrl='https://pbs.twimg.com/media...   \n",
       "2                                                NaN   \n",
       "\n",
       "                                     usr_description  usr_username tweet_vibe  \\\n",
       "0  I say things. Things so important that they ma...         jscix        NaN   \n",
       "1                                       CTO @mach_49       mdscaff        NaN   \n",
       "2  Oxford DPhil in Zoology, Environmental Rating ...  mattprescott        NaN   \n",
       "\n",
       "   usr_verified  usr_follower_count              usr_location  usr_userid  \\\n",
       "0         False                  70  A small outpost on Pluto    17632837   \n",
       "1         False                1541          Redwood City, CA    14456867   \n",
       "2         False               24743            Little England    21855179   \n",
       "\n",
       "                 usr_created  \n",
       "0  2008-11-25 21:50:18+00:00  \n",
       "1  2008-04-21 01:11:15+00:00  \n",
       "2  2009-02-25 11:07:48+00:00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Concatenate the two dataframes of raw data into one and inspect how they look \n",
    "df_tweet = pd.concat([df_tweet_25FebOnwards, df_tweet_Prior25Feb], ignore_index=True)\n",
    "df_tweet.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2147019, 19)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check how many tweets were scraped and how many columns the dataframe has\n",
    "df_tweet.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO: if changes need to be done, remove comment below to save the concatenated data \n",
    "#df_tweet.to_csv(\"raw_data_30Nov_07Apr.csv\",index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To make sure the data was saved correctly the data is loaded again from the newly saved file. \n",
    "<br>Another worth mentioning point is that since the scraping process was conducted in multiple phases, it was expected that some tweets were scraped more than once, thus duplicated may exist in the raw data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Loading raw data\n",
    "df_tweet= pd.read_csv(\"data_files/scraped_RAW_data/raw_data_30Nov_07Apr.csv\", index_col=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>tweet_id</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>tweet_content</th>\n",
       "      <th>tweet_source</th>\n",
       "      <th>tweet_replycount</th>\n",
       "      <th>tweet_retweetcount</th>\n",
       "      <th>tweet_likecount</th>\n",
       "      <th>tweet_inreplytotweetid</th>\n",
       "      <th>tweet_inreplytouser</th>\n",
       "      <th>tweet_hashtags</th>\n",
       "      <th>tweet_media</th>\n",
       "      <th>usr_description</th>\n",
       "      <th>usr_username</th>\n",
       "      <th>tweet_vibe</th>\n",
       "      <th>usr_verified</th>\n",
       "      <th>usr_follower_count</th>\n",
       "      <th>usr_location</th>\n",
       "      <th>usr_userid</th>\n",
       "      <th>usr_created</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1644449184060252160</td>\n",
       "      <td>2023-04-07 21:16:52+00:00</td>\n",
       "      <td>I am thinking of writing a plugin for ChatGPT ...</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['ChatGPTto']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>I say things. Things so important that they ma...</td>\n",
       "      <td>jscix</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>70</td>\n",
       "      <td>A small outpost on Pluto</td>\n",
       "      <td>17632837</td>\n",
       "      <td>2008-11-25 21:50:18+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1644449176942530561</td>\n",
       "      <td>2023-04-07 21:16:50+00:00</td>\n",
       "      <td>$20 a month for ChatGPT? https://t.co/acRmNWghBl</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Photo(previewUrl='https://pbs.twimg.com/media...</td>\n",
       "      <td>CTO @mach_49</td>\n",
       "      <td>mdscaff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1541</td>\n",
       "      <td>Redwood City, CA</td>\n",
       "      <td>14456867</td>\n",
       "      <td>2008-04-21 01:11:15+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1644449166775529474</td>\n",
       "      <td>2023-04-07 21:16:48+00:00</td>\n",
       "      <td>COVID : TREATMENT IDEAS\\n\\n#CHATGPT</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/android\" ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['CHATGPT']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Oxford DPhil in Zoology, Environmental Rating ...</td>\n",
       "      <td>mattprescott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>24743</td>\n",
       "      <td>Little England</td>\n",
       "      <td>21855179</td>\n",
       "      <td>2009-02-25 11:07:48+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id                 tweet_date  \\\n",
       "0  1644449184060252160  2023-04-07 21:16:52+00:00   \n",
       "1  1644449176942530561  2023-04-07 21:16:50+00:00   \n",
       "2  1644449166775529474  2023-04-07 21:16:48+00:00   \n",
       "\n",
       "                                       tweet_content  \\\n",
       "0  I am thinking of writing a plugin for ChatGPT ...   \n",
       "1   $20 a month for ChatGPT? https://t.co/acRmNWghBl   \n",
       "2                COVID : TREATMENT IDEAS\\n\\n#CHATGPT   \n",
       "\n",
       "                                        tweet_source  tweet_replycount  \\\n",
       "0  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "1  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "2  <a href=\"http://twitter.com/download/android\" ...                 0   \n",
       "\n",
       "   tweet_retweetcount  tweet_likecount  tweet_inreplytotweetid  \\\n",
       "0                   0                0                     NaN   \n",
       "1                   0                0                     NaN   \n",
       "2                   0                0                     NaN   \n",
       "\n",
       "  tweet_inreplytouser tweet_hashtags  \\\n",
       "0                 NaN  ['ChatGPTto']   \n",
       "1                 NaN            NaN   \n",
       "2                 NaN    ['CHATGPT']   \n",
       "\n",
       "                                         tweet_media  \\\n",
       "0                                                NaN   \n",
       "1  [Photo(previewUrl='https://pbs.twimg.com/media...   \n",
       "2                                                NaN   \n",
       "\n",
       "                                     usr_description  usr_username tweet_vibe  \\\n",
       "0  I say things. Things so important that they ma...         jscix        NaN   \n",
       "1                                       CTO @mach_49       mdscaff        NaN   \n",
       "2  Oxford DPhil in Zoology, Environmental Rating ...  mattprescott        NaN   \n",
       "\n",
       "   usr_verified  usr_follower_count              usr_location  usr_userid  \\\n",
       "0         False                  70  A small outpost on Pluto    17632837   \n",
       "1         False                1541          Redwood City, CA    14456867   \n",
       "2         False               24743            Little England    21855179   \n",
       "\n",
       "                 usr_created  \n",
       "0  2008-11-25 21:50:18+00:00  \n",
       "1  2008-04-21 01:11:15+00:00  \n",
       "2  2009-02-25 11:07:48+00:00  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Inspect thea first 3 rows of the dataframe\n",
    "df_tweet.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "13b9c82eefe94a43881e4d8cefa833b4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "                                             |          | [  0%]   00:00 -> (? left)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Report SWEETVIZ_REPORT.html was generated! NOTEBOOK/COLAB USERS: the web browser MAY not pop up, regardless, the report IS saved in your notebook/colab files.\n"
     ]
    }
   ],
   "source": [
    "#Generate sweetviz report of the raw data. this provides information on datatypes, duplicates and more on column level of the dataframe\n",
    "my_report = sv.analyze(df_tweet)\n",
    "my_report.show_html()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tweet_id                  1640146\n",
       "tweet_date                1477065\n",
       "tweet_content             1578182\n",
       "tweet_source                 3068\n",
       "tweet_replycount              482\n",
       "tweet_retweetcount            879\n",
       "tweet_likecount              2147\n",
       "tweet_inreplytotweetid     512393\n",
       "tweet_inreplytouser        237494\n",
       "tweet_hashtags             160644\n",
       "tweet_media                     0\n",
       "usr_description            638981\n",
       "usr_username               700578\n",
       "tweet_vibe                     27\n",
       "usr_verified                    2\n",
       "usr_follower_count          64664\n",
       "usr_location               145630\n",
       "usr_userid                 698722\n",
       "usr_created                800752\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check how remaining with the tweets with no media would influence the dataframe\n",
    "df_tweet[df_tweet['tweet_media'].isna()].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2113264"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check how many tweets have missing values as tweet vibe\n",
    "df_tweet[df_tweet['tweet_vibe'].isna()]['tweet_id'].nunique()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the previous steps it can be noticed that aproximately 20% of the tweets include media attachements. These tweets were not in the scope of the MSc. thesis, thus they were discarded.<br>\n",
    "Additionally, since the *tweet_vibe* column is consisting of mainly null values, this will be later discarded. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tweet=df_tweet[df_tweet['tweet_media'].isna()].drop_duplicates(subset='tweet_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1640146, 19)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tweet.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Anonymization\n",
    "\n",
    "The following section focuses on anonymizing the data based on *usr_userid* instead of *usr_username*. The scope of this section is to result in a dataframe where usernames of twitter users are not stored.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Transform usr_userid column into string type\n",
    "df_tweet['usr_userid']= df_tweet['usr_userid'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Handle usernames appearing the column that shows in reply to which user a tweet was made\n",
    "#Create new dataframe consisting only of user ids and user names \n",
    "twitter_users = df_tweet[['usr_userid','usr_username']].drop_duplicates(subset='usr_userid')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO:Code below saves user mapping data\n",
    "#twitter_users.to_csv('Users_Map.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698722, 2)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_users.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Rename columns so that it will be easier to merge the ids of users in the main dataframe\n",
    "twitter_users.rename(columns={\"usr_userid\":\"usr_inreply_id\",\"usr_username\":\"tweet_inreplytouser\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>usr_inreply_id</th>\n",
       "      <th>tweet_inreplytouser</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17632837</td>\n",
       "      <td>jscix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14456867</td>\n",
       "      <td>mdscaff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21855179</td>\n",
       "      <td>mattprescott</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  usr_inreply_id tweet_inreplytouser\n",
       "0       17632837               jscix\n",
       "1       14456867             mdscaff\n",
       "2       21855179        mattprescott"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Inspect new users only dataframe\n",
    "twitter_users.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove noise in the column tweet_inreplytouser of the main dataframe so that only the name of the users remain and not the link to their profile\n",
    "df_tweet['tweet_inreplytouser']=df_tweet['tweet_inreplytouser'].str.replace(\"https://twitter.com/\",\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['tweet_id', 'tweet_date', 'tweet_content', 'tweet_source',\n",
       "       'tweet_replycount', 'tweet_retweetcount', 'tweet_likecount',\n",
       "       'tweet_inreplytotweetid', 'tweet_inreplytouser', 'tweet_hashtags',\n",
       "       'tweet_media', 'usr_description', 'usr_username', 'tweet_vibe',\n",
       "       'usr_verified', 'usr_follower_count', 'usr_location', 'usr_userid',\n",
       "       'usr_created', 'usr_inreply_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Add to the main dataframe the user id of users in reply to whom the tweets were made \n",
    "df_tweet = pd.merge(df_tweet, twitter_users, on='tweet_inreplytouser', how='left')\n",
    "#Inspect the names of the columns of the main dataframe after merger\n",
    "df_tweet.columns"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initial Data Cleaning\n",
    "\n",
    "The next section of the notebook handles the initial data cleaning of the raw data. This involves removing redundant columns and rows. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Function to clean raw data\n",
    "def clean_dataframe(df_tweets):\n",
    "    # remove all tweets with media\n",
    "    df_tweets = df_tweets[df_tweets['tweet_media'].isna()] \n",
    "    #sort dataframe\n",
    "    df_tweets=df_tweets.sort_values(by=['tweet_date'],ascending=False)\n",
    "    # remove duplicates\n",
    "    df_tweets.drop_duplicates(['tweet_id'],keep='first',inplace=True)\n",
    "    #remove unused cols\n",
    "    df_tweets=df_tweets.drop(['tweet_vibe', 'tweet_media','usr_username','tweet_inreplytouser'], axis=1)\n",
    "    df_tweets=df_tweets.reset_index()\n",
    "\n",
    "    return df_tweets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Clean dataframe with the clean_dataframe function\n",
    "df_tweet=clean_dataframe(df_tweet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1640146, 17)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check shape of the resulting dataframe\n",
    "df_tweet.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save again the cleaned dataframe\n",
    "#df_tweet.to_csv(\"1_raw_data_30Nov_07Apr_CLEAN.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Load cleaned data from saved file\n",
    "df_tweet= pd.read_csv(\"data_files/raw_data_30Nov_07Apr_CLEAN.csv\", index_col=False).drop(columns=['index'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>tweet_id</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>tweet_content</th>\n",
       "      <th>tweet_source</th>\n",
       "      <th>tweet_replycount</th>\n",
       "      <th>tweet_retweetcount</th>\n",
       "      <th>tweet_likecount</th>\n",
       "      <th>tweet_inreplytotweetid</th>\n",
       "      <th>tweet_hashtags</th>\n",
       "      <th>usr_description</th>\n",
       "      <th>usr_verified</th>\n",
       "      <th>usr_follower_count</th>\n",
       "      <th>usr_location</th>\n",
       "      <th>usr_userid</th>\n",
       "      <th>usr_created</th>\n",
       "      <th>usr_inreply_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1644449184060252160</td>\n",
       "      <td>2023-04-07 21:16:52+00:00</td>\n",
       "      <td>I am thinking of writing a plugin for ChatGPT ...</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['ChatGPTto']</td>\n",
       "      <td>I say things. Things so important that they ma...</td>\n",
       "      <td>False</td>\n",
       "      <td>70</td>\n",
       "      <td>A small outpost on Pluto</td>\n",
       "      <td>17632837</td>\n",
       "      <td>2008-11-25 21:50:18+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1644449166775529474</td>\n",
       "      <td>2023-04-07 21:16:48+00:00</td>\n",
       "      <td>COVID : TREATMENT IDEAS\\n\\n#CHATGPT</td>\n",
       "      <td>&lt;a href=\"http://twitter.com/download/android\" ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>['CHATGPT']</td>\n",
       "      <td>Oxford DPhil in Zoology, Environmental Rating ...</td>\n",
       "      <td>False</td>\n",
       "      <td>24743</td>\n",
       "      <td>Little England</td>\n",
       "      <td>21855179</td>\n",
       "      <td>2009-02-25 11:07:48+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1644449134425022470</td>\n",
       "      <td>2023-04-07 21:16:40+00:00</td>\n",
       "      <td>any cool viruses to give the chat gpt AI?</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>enjoyer of long walks in the dungeon.</td>\n",
       "      <td>False</td>\n",
       "      <td>228</td>\n",
       "      <td>home.</td>\n",
       "      <td>1207075000164864000</td>\n",
       "      <td>2019-12-17 23:08:33+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id                 tweet_date  \\\n",
       "0  1644449184060252160  2023-04-07 21:16:52+00:00   \n",
       "1  1644449166775529474  2023-04-07 21:16:48+00:00   \n",
       "2  1644449134425022470  2023-04-07 21:16:40+00:00   \n",
       "\n",
       "                                       tweet_content  \\\n",
       "0  I am thinking of writing a plugin for ChatGPT ...   \n",
       "1                COVID : TREATMENT IDEAS\\n\\n#CHATGPT   \n",
       "2          any cool viruses to give the chat gpt AI?   \n",
       "\n",
       "                                        tweet_source  tweet_replycount  \\\n",
       "0  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "1  <a href=\"http://twitter.com/download/android\" ...                 0   \n",
       "2  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                 0   \n",
       "\n",
       "   tweet_retweetcount  tweet_likecount  tweet_inreplytotweetid tweet_hashtags  \\\n",
       "0                   0                0                     NaN  ['ChatGPTto']   \n",
       "1                   0                0                     NaN    ['CHATGPT']   \n",
       "2                   0                0                     NaN            NaN   \n",
       "\n",
       "                                     usr_description  usr_verified  \\\n",
       "0  I say things. Things so important that they ma...         False   \n",
       "1  Oxford DPhil in Zoology, Environmental Rating ...         False   \n",
       "2              enjoyer of long walks in the dungeon.         False   \n",
       "\n",
       "   usr_follower_count              usr_location           usr_userid  \\\n",
       "0                  70  A small outpost on Pluto             17632837   \n",
       "1               24743            Little England             21855179   \n",
       "2                 228                     home.  1207075000164864000   \n",
       "\n",
       "                 usr_created  usr_inreply_id  \n",
       "0  2008-11-25 21:50:18+00:00             NaN  \n",
       "1  2009-02-25 11:07:48+00:00             NaN  \n",
       "2  2019-12-17 23:08:33+00:00             NaN  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Inspect first 3 rows of dataframe\n",
    "df_tweet.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>tweet_id</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>tweet_content</th>\n",
       "      <th>tweet_source</th>\n",
       "      <th>tweet_replycount</th>\n",
       "      <th>tweet_retweetcount</th>\n",
       "      <th>tweet_likecount</th>\n",
       "      <th>tweet_inreplytotweetid</th>\n",
       "      <th>tweet_hashtags</th>\n",
       "      <th>usr_description</th>\n",
       "      <th>usr_verified</th>\n",
       "      <th>usr_follower_count</th>\n",
       "      <th>usr_location</th>\n",
       "      <th>usr_userid</th>\n",
       "      <th>usr_created</th>\n",
       "      <th>usr_inreply_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1640143</th>\n",
       "      <td>1598015627540635648</td>\n",
       "      <td>2022-11-30 18:06:29+00:00</td>\n",
       "      <td>Just launched ChatGPT, our new AI system which...</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>84</td>\n",
       "      <td>369</td>\n",
       "      <td>2370</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>President &amp; Co-Founder @OpenAI</td>\n",
       "      <td>True</td>\n",
       "      <td>215048</td>\n",
       "      <td>NaN</td>\n",
       "      <td>162124540</td>\n",
       "      <td>2010-07-02 19:38:09+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640144</th>\n",
       "      <td>1598014522098208769</td>\n",
       "      <td>2022-11-30 18:02:06+00:00</td>\n",
       "      <td>Try talking with ChatGPT, our new AI system wh...</td>\n",
       "      <td>&lt;a href=\"https://mobile.twitter.com\" rel=\"nofo...</td>\n",
       "      <td>1320</td>\n",
       "      <td>3518</td>\n",
       "      <td>13703</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OpenAI’s mission is to ensure that artificial ...</td>\n",
       "      <td>True</td>\n",
       "      <td>1575814</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4398626122</td>\n",
       "      <td>2015-12-06 22:51:08+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1640145</th>\n",
       "      <td>1598014056790622225</td>\n",
       "      <td>2022-11-30 18:00:15+00:00</td>\n",
       "      <td>ChatGPT: Optimizing Language Models for Dialog...</td>\n",
       "      <td>&lt;a href=\"http://www.evyware.com/\" rel=\"nofollo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>using A.I. to propel the real estate industry ...</td>\n",
       "      <td>False</td>\n",
       "      <td>10482</td>\n",
       "      <td>Cleveland, OH</td>\n",
       "      <td>354863991</td>\n",
       "      <td>2011-08-14 12:40:43+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    tweet_id                 tweet_date  \\\n",
       "1640143  1598015627540635648  2022-11-30 18:06:29+00:00   \n",
       "1640144  1598014522098208769  2022-11-30 18:02:06+00:00   \n",
       "1640145  1598014056790622225  2022-11-30 18:00:15+00:00   \n",
       "\n",
       "                                             tweet_content  \\\n",
       "1640143  Just launched ChatGPT, our new AI system which...   \n",
       "1640144  Try talking with ChatGPT, our new AI system wh...   \n",
       "1640145  ChatGPT: Optimizing Language Models for Dialog...   \n",
       "\n",
       "                                              tweet_source  tweet_replycount  \\\n",
       "1640143  <a href=\"https://mobile.twitter.com\" rel=\"nofo...                84   \n",
       "1640144  <a href=\"https://mobile.twitter.com\" rel=\"nofo...              1320   \n",
       "1640145  <a href=\"http://www.evyware.com/\" rel=\"nofollo...                 0   \n",
       "\n",
       "         tweet_retweetcount  tweet_likecount  tweet_inreplytotweetid  \\\n",
       "1640143                 369             2370                     NaN   \n",
       "1640144                3518            13703                     NaN   \n",
       "1640145                   0                2                     NaN   \n",
       "\n",
       "        tweet_hashtags                                    usr_description  \\\n",
       "1640143            NaN                     President & Co-Founder @OpenAI   \n",
       "1640144            NaN  OpenAI’s mission is to ensure that artificial ...   \n",
       "1640145            NaN  using A.I. to propel the real estate industry ...   \n",
       "\n",
       "         usr_verified  usr_follower_count   usr_location  usr_userid  \\\n",
       "1640143          True              215048            NaN   162124540   \n",
       "1640144          True             1575814            NaN  4398626122   \n",
       "1640145         False               10482  Cleveland, OH   354863991   \n",
       "\n",
       "                       usr_created  usr_inreply_id  \n",
       "1640143  2010-07-02 19:38:09+00:00             NaN  \n",
       "1640144  2015-12-06 22:51:08+00:00             NaN  \n",
       "1640145  2011-08-14 12:40:43+00:00             NaN  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Inspect last 3 rows of the dataframe\n",
    "df_tweet.tail(3)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To ease the further data handling which involves applying models such as LDA (for topic modelling) and Vader (for sentiment analysis), two new dataframes were created and separately saved: one related to users' descriptions and one related to tweets content. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tweet['usr_userid']=df_tweet['usr_userid'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(698722, 2)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create dataframe of users descriptions. \n",
    "df_users = df_tweet[['usr_userid','usr_description']].drop_duplicates(subset='usr_userid')\n",
    "df_users.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1640146, 2)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create dataframe of tweets contents\n",
    "df_tweets_content = df_tweet[['tweet_id','tweet_content']].drop_duplicates()\n",
    "df_tweets_content.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save the two new files\n",
    "#df_users.reset_index().drop(columns=\"index\").to_csv(\"UsrData_PreLDA.csv\", index=False)\n",
    "#df_tweets_content.reset_index().drop(columns=\"index\").to_csv(\"TweetsContent_CLEAN.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "CodeBase",
   "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.10.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2b626e21ead191b3db0e87a4603b3e94e198395ed9547c98ae8f08e151842829"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}