preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=pd.read_excel(\"data/twscrape1_original.xlsx\")\n",
    "df2=pd.read_excel(\"data/twscrape2_original.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>id_str</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1951.000000</td>\n",
       "      <td>1.951000e+03</td>\n",
       "      <td>1951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>975.000000</td>\n",
       "      <td>9.629803e+17</td>\n",
       "      <td>2018-02-09 13:01:37.796002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.749834e+09</td>\n",
       "      <td>2009-07-21 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>487.500000</td>\n",
       "      <td>5.496666e+17</td>\n",
       "      <td>2014-12-29 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>975.000000</td>\n",
       "      <td>1.011518e+18</td>\n",
       "      <td>2018-06-26 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1462.500000</td>\n",
       "      <td>1.390966e+18</td>\n",
       "      <td>2021-05-08 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1950.000000</td>\n",
       "      <td>1.698514e+18</td>\n",
       "      <td>2023-09-04 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>563.349507</td>\n",
       "      <td>5.052775e+17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0        id_str                           date\n",
       "count  1951.000000  1.951000e+03                           1951\n",
       "mean    975.000000  9.629803e+17  2018-02-09 13:01:37.796002048\n",
       "min       0.000000  2.749834e+09            2009-07-21 00:00:00\n",
       "25%     487.500000  5.496666e+17            2014-12-29 00:00:00\n",
       "50%     975.000000  1.011518e+18            2018-06-26 00:00:00\n",
       "75%    1462.500000  1.390966e+18            2021-05-08 00:00:00\n",
       "max    1950.000000  1.698514e+18            2023-09-04 00:00:00\n",
       "std     563.349507  5.052775e+17                            NaN"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.describe()"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>id_str</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5072.000000</td>\n",
       "      <td>5.072000e+03</td>\n",
       "      <td>5072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2535.500000</td>\n",
       "      <td>8.906494e+17</td>\n",
       "      <td>2017-07-26 17:04:21.198738176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.569163e+10</td>\n",
       "      <td>2010-06-08 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1267.750000</td>\n",
       "      <td>5.348924e+17</td>\n",
       "      <td>2014-11-18 18:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2535.500000</td>\n",
       "      <td>8.406019e+17</td>\n",
       "      <td>2017-03-11 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3803.250000</td>\n",
       "      <td>1.242107e+18</td>\n",
       "      <td>2020-03-23 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>5071.000000</td>\n",
       "      <td>1.698546e+18</td>\n",
       "      <td>2023-09-04 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1464.304613</td>\n",
       "      <td>4.916756e+17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0        id_str                           date\n",
       "count  5072.000000  5.072000e+03                           5072\n",
       "mean   2535.500000  8.906494e+17  2017-07-26 17:04:21.198738176\n",
       "min       0.000000  1.569163e+10            2010-06-08 00:00:00\n",
       "25%    1267.750000  5.348924e+17            2014-11-18 18:00:00\n",
       "50%    2535.500000  8.406019e+17            2017-03-11 12:00:00\n",
       "75%    3803.250000  1.242107e+18            2020-03-23 00:00:00\n",
       "max    5071.000000  1.698546e+18            2023-09-04 00:00:00\n",
       "std    1464.304613  4.916756e+17                            NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7011, 10)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combine DataFrames vertically using concat\n",
    "combined_df = pd.concat([df1, df2], ignore_index=True)\n",
    "\n",
    "# Drop duplicates based on the \"id_str\" and \"date\" columns, keeping the first occurrence\n",
    "result_df = combined_df.drop_duplicates(subset=[\"id_str\",\"date\"], keep=\"first\")\n",
    "\n",
    "result_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Unnamed: 0</th>\n",
       "      <th>id_str</th>\n",
       "      <th>date</th>\n",
       "      <th>timezone</th>\n",
       "      <th>lang</th>\n",
       "      <th>raw_content</th>\n",
       "      <th>url</th>\n",
       "      <th>hashtages</th>\n",
       "      <th>mentioned_users</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1696067031303356632</td>\n",
       "      <td>2023-09-04</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>[CLIENT COVERAGE] @SuncorpGroup tech almost re...</td>\n",
       "      <td>https://twitter.com/SpherePR/status/1696067031...</td>\n",
       "      <td>['suncorp', 'ANZ', 'anzbank', 'insurtech']</td>\n",
       "      <td>[UserRef(id=1192212818, username='SuncorpGroup...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1691880597374042237</td>\n",
       "      <td>2023-09-03</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>\"Discover how ANZ Bank is revolutionizing bank...</td>\n",
       "      <td>https://twitter.com/cjco_australia/status/1691...</td>\n",
       "      <td>['ANZBank', 'DevOps', 'Innovation', 'GlobalBan...</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1687140253277306880</td>\n",
       "      <td>2023-09-03</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>This bank signed me up for #credit #card witho...</td>\n",
       "      <td>https://twitter.com/this_is_me_Jo/status/16871...</td>\n",
       "      <td>['credit', 'card', 'anzbank', 'unethical', 'cu...</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1686660034753871872</td>\n",
       "      <td>2023-09-03</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>@CommBank CBA have really dropped the ball on ...</td>\n",
       "      <td>https://twitter.com/ESC_Investor/status/168666...</td>\n",
       "      <td>['anzbank']</td>\n",
       "      <td>[UserRef(id=14787749, username='CommBank', dis...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1686659916847886341</td>\n",
       "      <td>2023-09-02</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>@CommBank CBA have really dropped the ball on ...</td>\n",
       "      <td>https://twitter.com/ESC_Investor/status/168665...</td>\n",
       "      <td>['anzbank']</td>\n",
       "      <td>[UserRef(id=14787749, username='CommBank', dis...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0               id_str       date timezone lang   \n",
       "0           0  1696067031303356632 2023-09-04      UTC   en  \\\n",
       "1           1  1691880597374042237 2023-09-03      UTC   en   \n",
       "2           2  1687140253277306880 2023-09-03      UTC   en   \n",
       "3           3  1686660034753871872 2023-09-03      UTC   en   \n",
       "4           4  1686659916847886341 2023-09-02      UTC   en   \n",
       "\n",
       "                                         raw_content   \n",
       "0  [CLIENT COVERAGE] @SuncorpGroup tech almost re...  \\\n",
       "1  \"Discover how ANZ Bank is revolutionizing bank...   \n",
       "2  This bank signed me up for #credit #card witho...   \n",
       "3  @CommBank CBA have really dropped the ball on ...   \n",
       "4  @CommBank CBA have really dropped the ball on ...   \n",
       "\n",
       "                                                 url   \n",
       "0  https://twitter.com/SpherePR/status/1696067031...  \\\n",
       "1  https://twitter.com/cjco_australia/status/1691...   \n",
       "2  https://twitter.com/this_is_me_Jo/status/16871...   \n",
       "3  https://twitter.com/ESC_Investor/status/168666...   \n",
       "4  https://twitter.com/ESC_Investor/status/168665...   \n",
       "\n",
       "                                           hashtages   \n",
       "0         ['suncorp', 'ANZ', 'anzbank', 'insurtech']  \\\n",
       "1  ['ANZBank', 'DevOps', 'Innovation', 'GlobalBan...   \n",
       "2  ['credit', 'card', 'anzbank', 'unethical', 'cu...   \n",
       "3                                        ['anzbank']   \n",
       "4                                        ['anzbank']   \n",
       "\n",
       "                                     mentioned_users place  \n",
       "0  [UserRef(id=1192212818, username='SuncorpGroup...   NaN  \n",
       "1                                                 []   NaN  \n",
       "2                                                 []   NaN  \n",
       "3  [UserRef(id=14787749, username='CommBank', dis...   NaN  \n",
       "4  [UserRef(id=14787749, username='CommBank', dis...   NaN  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Unnamed: 0</th>\n",
       "      <th>id_str</th>\n",
       "      <th>date</th>\n",
       "      <th>timezone</th>\n",
       "      <th>lang</th>\n",
       "      <th>raw_content</th>\n",
       "      <th>url</th>\n",
       "      <th>hashtages</th>\n",
       "      <th>mentioned_users</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>1693289575248994461</td>\n",
       "      <td>2023-08-30</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>Solopreneurs are taking over...</td>\n",
       "      <td>https://twitter.com/bowlescapital/status/16932...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>1633821048625037313</td>\n",
       "      <td>2023-08-30</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>Thank you @SenatorRennick @MRobertsQLD @Senato...</td>\n",
       "      <td>https://twitter.com/MarkNeugebaue13/status/163...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[UserRef(id=1529982588575977473, username='sen...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>1695062879479767449</td>\n",
       "      <td>2023-08-29</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>💪 Personalized fasting = Personalized results....</td>\n",
       "      <td>https://twitter.com/dofastingapp/status/169506...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>44</td>\n",
       "      <td>1691144675833315328</td>\n",
       "      <td>2023-08-28</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>Here are some geography facts about Earth that...</td>\n",
       "      <td>https://twitter.com/Quizscape_/status/16911446...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>49</td>\n",
       "      <td>1641694860171431936</td>\n",
       "      <td>2023-08-25</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>@pushntime @Russputin2 @GayeFreeman8 Country t...</td>\n",
       "      <td>https://twitter.com/_Liz_S_/status/16416948601...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[UserRef(id=1185399980825051142, username='pus...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6963</th>\n",
       "      <td>5012</td>\n",
       "      <td>1667590198174048256</td>\n",
       "      <td>2010-11-16</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>Hustlers, the Matrix, and Education : \\n\\nIs t...</td>\n",
       "      <td>https://twitter.com/_AhmadHijazi/status/166759...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6973</th>\n",
       "      <td>5022</td>\n",
       "      <td>1698360872437997834</td>\n",
       "      <td>2010-10-29</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>My hikes are different these days.\\n\\nWhile I ...</td>\n",
       "      <td>https://twitter.com/cwilstrup/status/169836087...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6986</th>\n",
       "      <td>5035</td>\n",
       "      <td>1676994509228343303</td>\n",
       "      <td>2010-10-05</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>Advent Leadership Academy class of 2023! 🥳🎓\\n\\...</td>\n",
       "      <td>https://twitter.com/adventintl/status/16769945...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[UserRef(id=39588192, username='HBSExecEd', di...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7005</th>\n",
       "      <td>5054</td>\n",
       "      <td>1671242507043377152</td>\n",
       "      <td>2010-08-02</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>New to Diablo 4? Get started with some coachin...</td>\n",
       "      <td>https://twitter.com/carrysquadcom/status/16712...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7014</th>\n",
       "      <td>5063</td>\n",
       "      <td>1650445477207212032</td>\n",
       "      <td>2010-07-14</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>UICC's Let's Talk Cancer podcast breaks down c...</td>\n",
       "      <td>https://twitter.com/uicc/status/16504454772072...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>364 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0               id_str       date timezone lang   \n",
       "18            18  1693289575248994461 2023-08-30      UTC   en  \\\n",
       "23            23  1633821048625037313 2023-08-30      UTC   en   \n",
       "27            27  1695062879479767449 2023-08-29      UTC   en   \n",
       "44            44  1691144675833315328 2023-08-28      UTC   en   \n",
       "49            49  1641694860171431936 2023-08-25      UTC   en   \n",
       "...          ...                  ...        ...      ...  ...   \n",
       "6963        5012  1667590198174048256 2010-11-16      UTC   en   \n",
       "6973        5022  1698360872437997834 2010-10-29      UTC   en   \n",
       "6986        5035  1676994509228343303 2010-10-05      UTC   en   \n",
       "7005        5054  1671242507043377152 2010-08-02      UTC   en   \n",
       "7014        5063  1650445477207212032 2010-07-14      UTC   en   \n",
       "\n",
       "                                            raw_content   \n",
       "18                      Solopreneurs are taking over...  \\\n",
       "23    Thank you @SenatorRennick @MRobertsQLD @Senato...   \n",
       "27    💪 Personalized fasting = Personalized results....   \n",
       "44    Here are some geography facts about Earth that...   \n",
       "49    @pushntime @Russputin2 @GayeFreeman8 Country t...   \n",
       "...                                                 ...   \n",
       "6963  Hustlers, the Matrix, and Education : \\n\\nIs t...   \n",
       "6973  My hikes are different these days.\\n\\nWhile I ...   \n",
       "6986  Advent Leadership Academy class of 2023! 🥳🎓\\n\\...   \n",
       "7005  New to Diablo 4? Get started with some coachin...   \n",
       "7014  UICC's Let's Talk Cancer podcast breaks down c...   \n",
       "\n",
       "                                                    url hashtages   \n",
       "18    https://twitter.com/bowlescapital/status/16932...        []  \\\n",
       "23    https://twitter.com/MarkNeugebaue13/status/163...        []   \n",
       "27    https://twitter.com/dofastingapp/status/169506...        []   \n",
       "44    https://twitter.com/Quizscape_/status/16911446...        []   \n",
       "49    https://twitter.com/_Liz_S_/status/16416948601...        []   \n",
       "...                                                 ...       ...   \n",
       "6963  https://twitter.com/_AhmadHijazi/status/166759...        []   \n",
       "6973  https://twitter.com/cwilstrup/status/169836087...        []   \n",
       "6986  https://twitter.com/adventintl/status/16769945...        []   \n",
       "7005  https://twitter.com/carrysquadcom/status/16712...        []   \n",
       "7014  https://twitter.com/uicc/status/16504454772072...        []   \n",
       "\n",
       "                                        mentioned_users place  \n",
       "18                                                   []   NaN  \n",
       "23    [UserRef(id=1529982588575977473, username='sen...   NaN  \n",
       "27                                                   []   NaN  \n",
       "44                                                   []   NaN  \n",
       "49    [UserRef(id=1185399980825051142, username='pus...   NaN  \n",
       "...                                                 ...   ...  \n",
       "6963                                                 []   NaN  \n",
       "6973                                                 []   NaN  \n",
       "6986  [UserRef(id=39588192, username='HBSExecEd', di...   NaN  \n",
       "7005                                                 []   NaN  \n",
       "7014                                                 []   NaN  \n",
       "\n",
       "[364 rows x 10 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df[result_df['hashtages'] == '[]']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6647, 10)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drop_ad = result_df[result_df['hashtages'] != '[]']\n",
    "# Reset the index \n",
    "drop_ad.reset_index(drop=True, inplace=True)\n",
    "drop_ad.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Unnamed: 0</th>\n",
       "      <th>id_str</th>\n",
       "      <th>date</th>\n",
       "      <th>timezone</th>\n",
       "      <th>lang</th>\n",
       "      <th>raw_content</th>\n",
       "      <th>url</th>\n",
       "      <th>hashtages</th>\n",
       "      <th>mentioned_users</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1696067031303356632</td>\n",
       "      <td>2023-09-04</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>[CLIENT COVERAGE] @SuncorpGroup tech almost re...</td>\n",
       "      <td>https://twitter.com/SpherePR/status/1696067031...</td>\n",
       "      <td>['suncorp', 'ANZ', 'anzbank', 'insurtech']</td>\n",
       "      <td>[UserRef(id=1192212818, username='SuncorpGroup...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1691880597374042237</td>\n",
       "      <td>2023-09-03</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>\"Discover how ANZ Bank is revolutionizing bank...</td>\n",
       "      <td>https://twitter.com/cjco_australia/status/1691...</td>\n",
       "      <td>['ANZBank', 'DevOps', 'Innovation', 'GlobalBan...</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1687140253277306880</td>\n",
       "      <td>2023-09-03</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>This bank signed me up for #credit #card witho...</td>\n",
       "      <td>https://twitter.com/this_is_me_Jo/status/16871...</td>\n",
       "      <td>['credit', 'card', 'anzbank', 'unethical', 'cu...</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1686660034753871872</td>\n",
       "      <td>2023-09-03</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>@CommBank CBA have really dropped the ball on ...</td>\n",
       "      <td>https://twitter.com/ESC_Investor/status/168666...</td>\n",
       "      <td>['anzbank']</td>\n",
       "      <td>[UserRef(id=14787749, username='CommBank', dis...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1686659916847886341</td>\n",
       "      <td>2023-09-02</td>\n",
       "      <td>UTC</td>\n",
       "      <td>en</td>\n",
       "      <td>@CommBank CBA have really dropped the ball on ...</td>\n",
       "      <td>https://twitter.com/ESC_Investor/status/168665...</td>\n",
       "      <td>['anzbank']</td>\n",
       "      <td>[UserRef(id=14787749, username='CommBank', dis...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0               id_str       date timezone lang   \n",
       "0           0  1696067031303356632 2023-09-04      UTC   en  \\\n",
       "1           1  1691880597374042237 2023-09-03      UTC   en   \n",
       "2           2  1687140253277306880 2023-09-03      UTC   en   \n",
       "3           3  1686660034753871872 2023-09-03      UTC   en   \n",
       "4           4  1686659916847886341 2023-09-02      UTC   en   \n",
       "\n",
       "                                         raw_content   \n",
       "0  [CLIENT COVERAGE] @SuncorpGroup tech almost re...  \\\n",
       "1  \"Discover how ANZ Bank is revolutionizing bank...   \n",
       "2  This bank signed me up for #credit #card witho...   \n",
       "3  @CommBank CBA have really dropped the ball on ...   \n",
       "4  @CommBank CBA have really dropped the ball on ...   \n",
       "\n",
       "                                                 url   \n",
       "0  https://twitter.com/SpherePR/status/1696067031...  \\\n",
       "1  https://twitter.com/cjco_australia/status/1691...   \n",
       "2  https://twitter.com/this_is_me_Jo/status/16871...   \n",
       "3  https://twitter.com/ESC_Investor/status/168666...   \n",
       "4  https://twitter.com/ESC_Investor/status/168665...   \n",
       "\n",
       "                                           hashtages   \n",
       "0         ['suncorp', 'ANZ', 'anzbank', 'insurtech']  \\\n",
       "1  ['ANZBank', 'DevOps', 'Innovation', 'GlobalBan...   \n",
       "2  ['credit', 'card', 'anzbank', 'unethical', 'cu...   \n",
       "3                                        ['anzbank']   \n",
       "4                                        ['anzbank']   \n",
       "\n",
       "                                     mentioned_users place  \n",
       "0  [UserRef(id=1192212818, username='SuncorpGroup...   NaN  \n",
       "1                                                 []   NaN  \n",
       "2                                                 []   NaN  \n",
       "3  [UserRef(id=14787749, username='CommBank', dis...   NaN  \n",
       "4  [UserRef(id=14787749, username='CommBank', dis...   NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drop_ad.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Unnamed: 0</th>\n",
       "      <th>id_str</th>\n",
       "      <th>date</th>\n",
       "      <th>timezone</th>\n",
       "      <th>lang</th>\n",
       "      <th>raw_content</th>\n",
       "      <th>url</th>\n",
       "      <th>hashtages</th>\n",
       "      <th>mentioned_users</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Unnamed: 0, id_str, date, timezone, lang, raw_content, url, hashtages, mentioned_users, place]\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drop_ad[drop_ad['hashtages'] == '[]']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Unnamed: 0</th>\n",
       "      <th>id_str</th>\n",
       "      <th>date</th>\n",
       "      <th>timezone</th>\n",
       "      <th>lang</th>\n",
       "      <th>raw_content</th>\n",
       "      <th>url</th>\n",
       "      <th>hashtages</th>\n",
       "      <th>mentioned_users</th>\n",
       "      <th>place</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>1684688136926904321</td>\n",
       "      <td>2023-09-02</td>\n",
       "      <td>UTC</td>\n",
       "      <td>in</td>\n",
       "      <td>Shankar bhagwan ball ♣︎9TaikaWatiti xo🔟 #ANZba...</td>\n",
       "      <td>https://twitter.com/DattReshmiN/status/1684688...</td>\n",
       "      <td>['ANZbank']</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>1673610865122365440</td>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>UTC</td>\n",
       "      <td>tr</td>\n",
       "      <td>Öte yandan Fireblocks şimdiye kadar #BNYMellon...</td>\n",
       "      <td>https://twitter.com/Blocknitytr/status/1673610...</td>\n",
       "      <td>['BNYMellon', 'BNPParibas', 'ANZBank']</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>42</td>\n",
       "      <td>1643826915441258496</td>\n",
       "      <td>2023-08-28</td>\n",
       "      <td>UTC</td>\n",
       "      <td>in</td>\n",
       "      <td>Globally #Crypto ko lekar positivity badh rah ...</td>\n",
       "      <td>https://twitter.com/CoinliveIndia/status/16438...</td>\n",
       "      <td>['Crypto', 'ANZbank', 'CBDC', 'cryptocurrency'...</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>43</td>\n",
       "      <td>1643292401606311942</td>\n",
       "      <td>2023-08-28</td>\n",
       "      <td>UTC</td>\n",
       "      <td>pt</td>\n",
       "      <td>Banco ANZ abandona o dinheiro em espécie; Dóla...</td>\n",
       "      <td>https://twitter.com/RintarouFX/status/16432924...</td>\n",
       "      <td>['FXOpen', 'Notícias', 'ANZBank', 'Australia']</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>48</td>\n",
       "      <td>1641696166160236544</td>\n",
       "      <td>2023-08-25</td>\n",
       "      <td>UTC</td>\n",
       "      <td>qht</td>\n",
       "      <td>#ANZbank #auspol</td>\n",
       "      <td>https://twitter.com/coupdegraz7/status/1641696...</td>\n",
       "      <td>['ANZbank', 'auspol']</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6524</th>\n",
       "      <td>4942</td>\n",
       "      <td>47970276998250497</td>\n",
       "      <td>2011-04-09</td>\n",
       "      <td>UTC</td>\n",
       "      <td>nl</td>\n",
       "      <td>#persbericht Ogone vergroot europeese betaalmo...</td>\n",
       "      <td>https://twitter.com/persberichtenNL/status/479...</td>\n",
       "      <td>['persbericht', 'anz', 'azi', 'bank']</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6545</th>\n",
       "      <td>4963</td>\n",
       "      <td>42105495602864128</td>\n",
       "      <td>2011-03-04</td>\n",
       "      <td>UTC</td>\n",
       "      <td>de</td>\n",
       "      <td>ANZ Banking GroupANZ National Bank General Dis...</td>\n",
       "      <td>https://twitter.com/ozsharemarket/status/42105...</td>\n",
       "      <td>['ANZ', 'stocks']</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6546</th>\n",
       "      <td>4964</td>\n",
       "      <td>42101876002398208</td>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>UTC</td>\n",
       "      <td>de</td>\n",
       "      <td>ANZ Banking GroupANZ National Bank SDS 31 Dec ...</td>\n",
       "      <td>https://twitter.com/ozsharemarket/status/42101...</td>\n",
       "      <td>['ANZ', 'stocks']</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6629</th>\n",
       "      <td>5052</td>\n",
       "      <td>20130698441</td>\n",
       "      <td>2010-08-06</td>\n",
       "      <td>UTC</td>\n",
       "      <td>nl</td>\n",
       "      <td>@BankingDay - Van Der Wielen heads #wealth at ...</td>\n",
       "      <td>https://twitter.com/renemichau/status/20130698441</td>\n",
       "      <td>['wealth', 'ANZ', 'bank', 'yam']</td>\n",
       "      <td>[UserRef(id=105025119, username='bankingday', ...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6642</th>\n",
       "      <td>5067</td>\n",
       "      <td>16827803674</td>\n",
       "      <td>2010-06-23</td>\n",
       "      <td>UTC</td>\n",
       "      <td>qme</td>\n",
       "      <td>http://euraeka.com/articles/6549304-ANZ-s-inte...</td>\n",
       "      <td>https://twitter.com/the_world_news/status/1682...</td>\n",
       "      <td>['anz', 'bank', 'internet', 'spokesperson', 'g...</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>302 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0               id_str       date timezone lang   \n",
       "6              6  1684688136926904321 2023-09-02      UTC   in  \\\n",
       "13            13  1673610865122365440 2023-09-01      UTC   tr   \n",
       "39            42  1643826915441258496 2023-08-28      UTC   in   \n",
       "40            43  1643292401606311942 2023-08-28      UTC   pt   \n",
       "44            48  1641696166160236544 2023-08-25      UTC  qht   \n",
       "...          ...                  ...        ...      ...  ...   \n",
       "6524        4942    47970276998250497 2011-04-09      UTC   nl   \n",
       "6545        4963    42105495602864128 2011-03-04      UTC   de   \n",
       "6546        4964    42101876002398208 2011-03-03      UTC   de   \n",
       "6629        5052          20130698441 2010-08-06      UTC   nl   \n",
       "6642        5067          16827803674 2010-06-23      UTC  qme   \n",
       "\n",
       "                                            raw_content   \n",
       "6     Shankar bhagwan ball ♣︎9TaikaWatiti xo🔟 #ANZba...  \\\n",
       "13    Öte yandan Fireblocks şimdiye kadar #BNYMellon...   \n",
       "39    Globally #Crypto ko lekar positivity badh rah ...   \n",
       "40    Banco ANZ abandona o dinheiro em espécie; Dóla...   \n",
       "44                                     #ANZbank #auspol   \n",
       "...                                                 ...   \n",
       "6524  #persbericht Ogone vergroot europeese betaalmo...   \n",
       "6545  ANZ Banking GroupANZ National Bank General Dis...   \n",
       "6546  ANZ Banking GroupANZ National Bank SDS 31 Dec ...   \n",
       "6629  @BankingDay - Van Der Wielen heads #wealth at ...   \n",
       "6642  http://euraeka.com/articles/6549304-ANZ-s-inte...   \n",
       "\n",
       "                                                    url   \n",
       "6     https://twitter.com/DattReshmiN/status/1684688...  \\\n",
       "13    https://twitter.com/Blocknitytr/status/1673610...   \n",
       "39    https://twitter.com/CoinliveIndia/status/16438...   \n",
       "40    https://twitter.com/RintarouFX/status/16432924...   \n",
       "44    https://twitter.com/coupdegraz7/status/1641696...   \n",
       "...                                                 ...   \n",
       "6524  https://twitter.com/persberichtenNL/status/479...   \n",
       "6545  https://twitter.com/ozsharemarket/status/42105...   \n",
       "6546  https://twitter.com/ozsharemarket/status/42101...   \n",
       "6629  https://twitter.com/renemichau/status/20130698441   \n",
       "6642  https://twitter.com/the_world_news/status/1682...   \n",
       "\n",
       "                                              hashtages   \n",
       "6                                           ['ANZbank']  \\\n",
       "13               ['BNYMellon', 'BNPParibas', 'ANZBank']   \n",
       "39    ['Crypto', 'ANZbank', 'CBDC', 'cryptocurrency'...   \n",
       "40       ['FXOpen', 'Notícias', 'ANZBank', 'Australia']   \n",
       "44                                ['ANZbank', 'auspol']   \n",
       "...                                                 ...   \n",
       "6524              ['persbericht', 'anz', 'azi', 'bank']   \n",
       "6545                                  ['ANZ', 'stocks']   \n",
       "6546                                  ['ANZ', 'stocks']   \n",
       "6629                   ['wealth', 'ANZ', 'bank', 'yam']   \n",
       "6642  ['anz', 'bank', 'internet', 'spokesperson', 'g...   \n",
       "\n",
       "                                        mentioned_users place  \n",
       "6                                                    []   NaN  \n",
       "13                                                   []   NaN  \n",
       "39                                                   []   NaN  \n",
       "40                                                   []   NaN  \n",
       "44                                                   []   NaN  \n",
       "...                                                 ...   ...  \n",
       "6524                                                 []   NaN  \n",
       "6545                                                 []   NaN  \n",
       "6546                                                 []   NaN  \n",
       "6629  [UserRef(id=105025119, username='bankingday', ...   NaN  \n",
       "6642                                                 []   NaN  \n",
       "\n",
       "[302 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drop_ad[drop_ad['lang'] != 'en']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6345, 10)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# only keep lang='en'\n",
    "en_df = drop_ad[drop_ad['lang'] == 'en']\n",
    "en_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "en_df.to_excel(\"./data/preprocessed.xlsx\")"
   ]
  }
 ],
 "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.11.2"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}