a_introduction/2_data_management_and_relational_databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e607ca26",
   "metadata": {},
   "source": [
    "<img src='../images/header.png' style='height: 50px; float: left'>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b70bc56f",
   "metadata": {},
   "source": [
    "## Introduction to Computational Social Science methods with Python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71218328",
   "metadata": {},
   "source": [
    "# Session A2: Data management and relational databases\n",
    "\n",
    "**Data** has two components: content and structure. Plain text data is unstructured, but its content can also be represented in a structured way. Data representations reside in a continuum of structuration. The rectangular table (also called dataframe or spreadsheet) is the most frequent data format in the social sciences because it is perfectly suited to manage the highly structured (survey) data that social scientists have typically worked with. Hierarchical data formats like JSON and HTML are semi-structured, and text data is unstructured (<a href='#weidmann_data_2023'>Weidmann 2023</a>, ch. 3). The practice of Computational Social Science primarly revolves around **Digital Behavioral Data**, the traces of behavior left by uses of, or harnessed by, digital technology. In a data life cycle, data changes its face from a raw state to a state in which it is ready for analysis. **Data processing** subsumes the steps in which this transformation takes place (<a href='#weidmann_data_2023'>Weidmann 2023</a>, ch. 1).\n",
    "\n",
    "**Data management** refers to the practices by which we stay in control of data as a resource. Data is best managed with a focus on practical questions. Since data processing is about bringing data into a form that permits answering those questions, it is strategically advantageous also to focus data management on data processing. Computational data processing workflows are beneficial because they fully document the many steps from data collection to data analysis, they are convenient (like your favorite spreadsheet software could never be), they are reproducible (nowadays in high demand), they can be scaled up (necessary for [big data](https://en.wikipedia.org/wiki/Big_data)), and they offer the needed flexibility in the face of semi-structured or unstructured data sources (<a href='#weidmann_data_2023'>Weidmann 2023</a>, p. 7–9).\n",
    "\n",
    "<img src='../images/pandas.png' style='height: 100px; float: right; margin-left: 10px'>\n",
    "\n",
    "[Pandas](https://pandas.pydata.org/), self-described as a \"fast, powerful, flexible and easy to use open source data analysis and manipulation tool\", is Python's package for data management and processing using 2-dimensional tables (<a href='#mclevey_doing_2022'>McLevey, 2022</a>, ch. 6). It allows you to work with any kind of observational or statistical data set, including matrices. Columns can contain text, numerical values, or even lists, but not mixtures of these. Pandas can be used in a way that mimics the functionality of **relational databases**. These are systems where the columns of a table are split into multiple tables such that redundancies are eliminated. Relational databases are often used in research when the data is either large in volume or rich in content because they ensure consistency and speed up data processing (<a href='#weidmann_data_2023'>Weidmann 2023</a>, part 3).\n",
    "\n",
    "The public [TweetsKB](https://data.gesis.org/tweetskb/) corpus of annotated tweets (<a href='fafalios_tweetskb_2018'>Fafalios *et al.*, 2018</a>), as well as its offspring, the [TweetsCOV19](https://data.gesis.org/tweetscov19/) corpus (<a href='dimitrov_tweetscov19_2020'>Dimitrov *et al.*, 2020</a>), are examples where the data is explicitly modeled relationally and can serve as illustrations of meaningful data management. Pandas is also well-equipped to handle time series data, as we will see.\n",
    "\n",
    "<div class='alert alert-block alert-success'>\n",
    "<b>In this session</b>, \n",
    "\n",
    "you will learn how to manage your data and keep it tidy while keeping a focus on your research questions. We will have a deep look at the 2-dimensional table as the fundamental data structure we will work with throughout all sessions. In subsession **A2.1**, we start with some illustrative toy examples about important dataframe properties. In subsession **A2.2**, we enter the almost-big-data world using the TweetsCOV19 dataset. You will experience how you can use Pandas to handle tables and mimic a relational database in such a way that your data gets ready for analysis. You will see what it means that relational databases eliminate redundancy and ensure consistency. The TweetsCOV19 dataset will function as an example that will shine up repeatedly in this and subsequent sessions. In subsession **A2.3**, you will see how you can save processed data to multiple files, an SQL database, or Excel. Subsession **A2.4** is dedicated to how data can be retrieved from the relational data structure we have created.\n",
    "</div>\n",
    "\n",
    "<div class='alert alert-block alert-danger'>\n",
    "<b>Caution</b>\n",
    "\n",
    "This Jupyter Notebook demonstrates a workflow that consists of a **sequence of processing steps**. In this process, tables are created, changed, and deleted. Hence, the notebook must be executed from top to bottom. Going back up from a certain code cell and trying to execute a cell that precedes it may not work.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "043ff2af",
   "metadata": {},
   "source": [
    "## Learning objectives\n",
    "\n",
    "By the end of this session, you should be able to\n",
    "- use Pandas to manage and process data,\n",
    "- understand its 2-dimensional dataframe structure incl. how to address rows and columns,\n",
    "- combine and filter dataframes,\n",
    "- understand what relational databases are,\n",
    "- understand what it means that they eliminate redundancy and ensure consistency,\n",
    "- transfer and save your data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e94fdc35",
   "metadata": {},
   "source": [
    "## A2.1. Toy examples\n",
    "\n",
    "#### Data and structure\n",
    "\n",
    "In subsections 3.2 to 3.4, <a href='#weidmann_data_2023'>Weidmann 2023</a> discusses data, data processing, and the benefit of relational databases using toy examples and the R language. Here, we adapt these examples to Python. Keep in mind that data = content + structure. Furthermore,  consider the following two pieces of data. They have (almost) the same content but a different structure. `sdb` represents unstructured data, `tdb` is structured:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "01ef5241",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Switzerland is a country with 8.3 million inhabitants, and its capital is Bern. Another country is Austria; its capital is Vienna and the population is 8.7 million.'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdb = 'Switzerland is a country with 8.3 million inhabitants, and its capital is Bern. Another country is Austria; its capital is Vienna and the population is 8.7 million.'\n",
    "sdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b4e92be6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.4.4'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9c59cf4b",
   "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>country</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8.3</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>8.7</td>\n",
       "      <td>Vienna</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  population capital\n",
       "0  Switzerland         8.3    Bern\n",
       "1      Austria         8.7  Vienna"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb = pd.DataFrame(data=[['Switzerland', 8.3, 'Bern'], ['Austria', 8.7, 'Vienna']], columns=['country', 'population', 'capital'])\n",
    "tdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb194bbc",
   "metadata": {},
   "source": [
    "`tdb` is a Pandas table called a [DataFrame](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html). The columns of a DataFrame are called [Series](https://pandas.pydata.org/docs/reference/api/pandas.Series.html). A DataFrame contains labeled axes (rows and columns). Axis 0 (the rows) is called the **index**, and its labels consist of integers from $0$ to $n-1$ by default, where $n$ is the number of rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1af92f11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=2, step=1)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb.index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "067bbf8a",
   "metadata": {},
   "source": [
    "When no names are given, the **columns** (axis 1) are also labeled in such a way, but using text labels makes the table much more readable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1e4bc142",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['country', 'population', 'capital'], dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6e6728e",
   "metadata": {},
   "source": [
    "Similarly the index can be a list of text labels or unordered integers.\n",
    "\n",
    "Rows, columns, or cells can be extracted by specifying their [`loc`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.loc.html)ations (labels). For example, to extract the capital of the second row (Austria):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4a408a28",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Vienna'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb.loc[1, 'capital']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ac8cb80",
   "metadata": {},
   "source": [
    "Filtering data. For example, selecting all rows where the country is Switzerland:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "60e4105f",
   "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>country</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8.3</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  population capital\n",
       "0  Switzerland         8.3    Bern"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb[tdb['country'] == 'Switzerland']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "467b88a8",
   "metadata": {},
   "source": [
    "To add a new column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7f0cd947",
   "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>country</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8.3</td>\n",
       "      <td>Bern</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>8.7</td>\n",
       "      <td>Vienna</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  population capital  area\n",
       "0  Switzerland         8.3    Bern    41\n",
       "1      Austria         8.7  Vienna    83"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb['area'] = [41, 83]\n",
    "tdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "948eecd4",
   "metadata": {},
   "source": [
    "The values in the 'area' column are of the integer data type:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e5584cff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb['area'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3dd0e97",
   "metadata": {},
   "source": [
    "To add a new row, we must first create a DataFrame containing the new row. Then we can [`concat`](https://pandas.pydata.org/docs/reference/api/pandas.concat.html)enate the two dataframes on the index axis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ac3950f9",
   "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>country</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8.300</td>\n",
       "      <td>Bern</td>\n",
       "      <td>41.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>8.700</td>\n",
       "      <td>Vienna</td>\n",
       "      <td>83.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Liechtenstein</td>\n",
       "      <td>0.038</td>\n",
       "      <td>Vaduz</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         country  population capital   area\n",
       "0    Switzerland       8.300    Bern  41.00\n",
       "1        Austria       8.700  Vienna  83.00\n",
       "0  Liechtenstein       0.038   Vaduz   0.16"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_row = ['Liechtenstein', 0.038 , 'Vaduz', 0.16]\n",
    "tdb_new_row = pd.DataFrame(data=[new_row], columns=tdb.columns)\n",
    "tdb = pd.concat(objs=[tdb, tdb_new_row], axis=0)\n",
    "tdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86c4597e",
   "metadata": {},
   "source": [
    "Note that the 'area' column is now a continuous numerical variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6f06b909",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('float64')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb['area'].dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae726ffc",
   "metadata": {},
   "source": [
    "But also note that concatenation results in the old indexes being used (the third row also has index 0). To reset the index and drop the old values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b24c270c",
   "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>country</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8.300</td>\n",
       "      <td>Bern</td>\n",
       "      <td>41.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>8.700</td>\n",
       "      <td>Vienna</td>\n",
       "      <td>83.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Liechtenstein</td>\n",
       "      <td>0.038</td>\n",
       "      <td>Vaduz</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         country  population capital   area\n",
       "0    Switzerland       8.300    Bern  41.00\n",
       "1        Austria       8.700  Vienna  83.00\n",
       "2  Liechtenstein       0.038   Vaduz   0.16"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb = tdb.reset_index(drop=True)\n",
    "tdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9a5048d",
   "metadata": {},
   "source": [
    "To remove the column 'area'  we can [`drop`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop.html) it (use `axis=1`) and put the result `inplace` of the original table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "dc95766e",
   "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>country</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8.300</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>8.700</td>\n",
       "      <td>Vienna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Liechtenstein</td>\n",
       "      <td>0.038</td>\n",
       "      <td>Vaduz</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         country  population capital\n",
       "0    Switzerland       8.300    Bern\n",
       "1        Austria       8.700  Vienna\n",
       "2  Liechtenstein       0.038   Vaduz"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb.drop(labels=['area'], axis=1, inplace=True)\n",
    "tdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2419fc7",
   "metadata": {},
   "source": [
    "Alternatively, you could have used `del` which also works for whole dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3f2ba9e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# del tdb['area']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "665cb0bc",
   "metadata": {},
   "source": [
    "Rows can only be removed with the `drop()` method by using `axis=0`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4401bfc9",
   "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>country</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>8.3</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>8.7</td>\n",
       "      <td>Vienna</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  population capital\n",
       "0  Switzerland         8.3    Bern\n",
       "1      Austria         8.7  Vienna"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tdb.drop(labels=[2], axis=0, inplace=True)\n",
    "tdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "636974db",
   "metadata": {},
   "source": [
    "#### Wide vs. long structure\n",
    "\n",
    "A rule in data management states that tables should grow long, not wide. Consider this `bad_table` of two countries' population sizes in three years:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3418a9b9",
   "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>country</th>\n",
       "      <th>pop1950</th>\n",
       "      <th>pop1960</th>\n",
       "      <th>pop1970</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>4.7</td>\n",
       "      <td>5.3</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>6.9</td>\n",
       "      <td>7.1</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  pop1950  pop1960  pop1970\n",
       "0  Switzerland      4.7      5.3      6.2\n",
       "1      Austria      6.9      7.1      7.5"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_table = pd.DataFrame(data=[['Switzerland', 4.7, 5.3, 6.2], ['Austria', 6.9, 7.1, 7.5]], columns=['country', 'pop1950', 'pop1960', 'pop1970'])\n",
    "bad_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10eda0ab",
   "metadata": {},
   "source": [
    "Though appealing to the eye, this table is computationally bad, as can be demonstrated by trying to compute the average population size over all countries and years. It is relatively easy to compute the mean for each year by selecting the corresponding columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "40c9eea2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pop1950    5.80\n",
       "pop1960    6.20\n",
       "pop1970    6.85\n",
       "dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_table[['pop1950', 'pop1960', 'pop1970']].mean(axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d77aa666",
   "metadata": {},
   "source": [
    "But computing the overall mean requires selecting columns and taking the mean of the year means:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7a0de912",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.283333333333334"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_table[['pop1950', 'pop1960', 'pop1970']].mean().mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb85ce79",
   "metadata": {},
   "source": [
    "A `good_table` is long, not wide:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d7f2b0b5",
   "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>country</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1950</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1960</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1970</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1950</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1960</td>\n",
       "      <td>7.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1970</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  year  population\n",
       "0  Switzerland  1950         4.7\n",
       "1  Switzerland  1960         5.3\n",
       "2  Switzerland  1970         6.2\n",
       "3      Austria  1950         6.9\n",
       "4      Austria  1960         7.1\n",
       "5      Austria  1970         7.5"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "good_table = pd.DataFrame(data=[['Switzerland', 1950, 4.7], ['Switzerland', 1960, 5.3], ['Switzerland', 1970, 6.2], ['Austria', 1950, 6.9], ['Austria', 1960, 7.1], ['Austria', 1970, 7.5]], columns=['country', 'year', 'population'])\n",
    "good_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15e7b90d",
   "metadata": {},
   "source": [
    "Computing the overall mean is a simple operation on one column..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "10716fd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.283333333333334"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "good_table['population'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b57ef65d",
   "metadata": {},
   "source": [
    "and the year means can be obtained via aggregation (using the [`groupby()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html) method) without having to specify any year columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2bfc26fd",
   "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>country</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1950</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1960</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1970</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1950</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1960</td>\n",
       "      <td>7.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1970</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  year  population\n",
       "0  Switzerland  1950         4.7\n",
       "1  Switzerland  1960         5.3\n",
       "2  Switzerland  1970         6.2\n",
       "3      Austria  1950         6.9\n",
       "4      Austria  1960         7.1\n",
       "5      Austria  1970         7.5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "good_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "20e8cd8f",
   "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>year</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1950</td>\n",
       "      <td>5.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1960</td>\n",
       "      <td>6.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1970</td>\n",
       "      <td>6.85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  population\n",
       "0  1950        5.80\n",
       "1  1960        6.20\n",
       "2  1970        6.85"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "good_table[['year', 'population']].groupby('year').mean().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5011168",
   "metadata": {},
   "source": [
    "#### Multiple tables\n",
    "\n",
    "What does it mean that relational databases eliminate redundancies and ensure consistency? Consider a copy of the `good_table` with an additional column that contains a country's capital (copying makes sure that any changes made to `good_table2` do not affect the original `good_table`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8eeb4364",
   "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>country</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1950</td>\n",
       "      <td>4.7</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1960</td>\n",
       "      <td>5.3</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1970</td>\n",
       "      <td>6.2</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1950</td>\n",
       "      <td>6.9</td>\n",
       "      <td>Vienna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1960</td>\n",
       "      <td>7.1</td>\n",
       "      <td>Vienna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1970</td>\n",
       "      <td>7.5</td>\n",
       "      <td>Vienna</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  year  population capital\n",
       "0  Switzerland  1950         4.7    Bern\n",
       "1  Switzerland  1960         5.3    Bern\n",
       "2  Switzerland  1970         6.2    Bern\n",
       "3      Austria  1950         6.9  Vienna\n",
       "4      Austria  1960         7.1  Vienna\n",
       "5      Austria  1970         7.5  Vienna"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "good_table2 = good_table.copy()\n",
    "good_table2.loc[0:2, 'capital'] = 'Bern'\n",
    "good_table2.loc[3:5, 'capital'] = 'Vienna'\n",
    "good_table2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32d5309c",
   "metadata": {},
   "source": [
    "Clearly, this table contains redundant information because country-capital pairs are always the same. This data format also potentially yields a consistency problem. If, for example, one wants to refer to capital names not in English but in the respective national language, one must replace each occurrence of 'Vienna' (English) with 'Wien' (German). But if you miss a single occurrence, your table becomes inconsistent. You can evade both problems if you split `good_table2` into two tables: one containing the population sizes..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5a25da9c",
   "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>country</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1950</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1960</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>1970</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1950</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1960</td>\n",
       "      <td>7.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Austria</td>\n",
       "      <td>1970</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  year  population\n",
       "0  Switzerland  1950         4.7\n",
       "1  Switzerland  1960         5.3\n",
       "2  Switzerland  1970         6.2\n",
       "3      Austria  1950         6.9\n",
       "4      Austria  1960         7.1\n",
       "5      Austria  1970         7.5"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "populations = good_table2[['country', 'year', 'population']].copy()\n",
    "populations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d962ea1b",
   "metadata": {},
   "source": [
    "and one containing the capitals:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "c939946c",
   "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>country</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>Bern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austria</td>\n",
       "      <td>Vienna</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country capital\n",
       "0  Switzerland    Bern\n",
       "1      Austria  Vienna"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "capitals = good_table2[['country', 'capital']].drop_duplicates().reset_index(drop=True)\n",
    "capitals"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dd3e190",
   "metadata": {},
   "source": [
    "This way, you have eliminated all redundancies and ensured consistency because you need to replace 'Vienna' with 'Wien' in one place only.\n",
    "\n",
    "Next, we will take Pandas and relational database thinking to the next level."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bdb8705",
   "metadata": {},
   "source": [
    "## A2.2. The TweetsCOV19 dataset\n",
    "\n",
    "[Twitter](https://en.wikipedia.org/wiki/Twitter) is a microblogging service that is very influential among politicians and journalists. Though stagnating over the past years, the number of monthly active users was 238 million in the second quarter of 2022 (<a href='#wikipedia_twitter_2022'>Wikipedia, 2022</a>). Since January 2013, researchers at [L3S](https://www.l3s.de/) and [GESIS](https://www.gesis.org/) have been collecting a 1% random sample of all Twitter transactions (tweets), detecting sentiments, and extracting named entities, user mentions, hashtags, as well as URLs, and making those publicly available as the [TweetsKB](https://data.gesis.org/tweetskb/) corpus (<a href='#fafalios_tweetskb_2018'>Fafalios *et al.*, 2018</a>). By August 2022, the corpus had grown to about 3 billion tweets. In the following, we will store the content of a small fraction of those tweets, one month of the [TweertsCOV19](https://data.gesis.org/tweetscov19/) corpus (<a href='#dimitrov_tweetscov19_2020'>Dimitrov *et al.*, 2020</a>), in multiple Pandas dataframes that make reference to the TweetsKB data structure."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92d6cf1a",
   "metadata": {},
   "source": [
    "### A2.2.1. Ontologies in practice\n",
    "\n",
    "|<img src='../images/model.png' style='float: none; width: 640px'>|\n",
    "|:--|\n",
    "|<em style='float: center'>**Figure 1**: Data structure used to build the TweetsKB corpus ([source](https://data.gesis.org/tweetskb/#Data-model))</em>|\n",
    "\n",
    "The data structure depicted in ***figure 1*** used to build the TweetsKB corpus is relational and uses several standardized ontologies. **Relational** means that each piece of content belongs to a class, and classes have properties that can either describe a class attribute or link to another class. We will shortly see that classes are candidates for tables. Classes and properties are drawn from **ontologies** which are vocabularies for modeling data and, in our particular tweets case, online community data. These vocabularies are developed and maintained by the [Semantic Web](https://en.wikipedia.org/wiki/Semantic_Web) research community, aiming to make internet data machine-readable.\n",
    "\n",
    "|<img src='../images/model_example.png' style='float: none; width: 640px'>|\n",
    "|:--|\n",
    "|<em style='float: center'>**Figure 2**: Example of how a tweet is encoded using the data structure</em>|\n",
    "\n",
    "***Figure 2*** is an example of how a tweet is encoded using this abstract data structure. In other words, the figure depicts how the content of a tweet is modeled as machine-readable data. Starting with the central element, a **tweet** is modeled as belonging to the [Post](https://www.w3.org/Submission/sioc-spec/#term_Post) class, which is defined as an \"article or message that can be posted to a Forum\" in the [SIOC](http://sioc-project.org/) ontology. A tweet has a [has_creator](https://www.w3.org/Submission/sioc-spec/#term_has_creator) property which relates a tweet to a user. A **user** is modeled as belonging to the [User](https://www.w3.org/Submission/sioc-spec/#term_User) class (in figure 1, the class is called \"UserAccount\"), which is defined as a \"User account in an online community site.\" The \"tweet1\" instance of Post as well as the \"usr1\" instance of User have [id](http://rdfs.org/sioc/spec/#term_id) properties that link to the actual [literal](https://www.w3.org/TR/rdf-schema/#ch_literal) values of the **tweet id** (<span style='font-family:Courier'>9565121266</span>) and (encrypted) **user name** (<span style='font-family:Courier'>2356912</span>) variables. Below, we will create separate Pandas tables for the Post and User classes, among others. Just like in the above example of populations and capitals, this is how redundancy is eliminated.\n",
    "\n",
    "Starting from such an understanding of separate tables for tweets and users, we can discuss which one some of the other variables belong to which come with the data. The **timestamp**, **number of retweets** (number of users that forward the tweet), and **number of favorites** (number of users that like the tweet) clearly are attributes of tweets. In the example of *figure 1*, \"tweet1\" is liked by $12$ users, a statistic that is modeled using the [InteractionCounter](https://schema.org/InteractionCounter) for the [LikeAction](https://schema.org/LikeAction) of the [Schema.org](https://schema.org/) vocabulary. The **number of followers** (number of other users that follow a user) and **number of friends** (number of other users a user follows) seem to be attributes of users at first glance. But since they are measured at the time of tweet creation, they are better also attributed to tweets. While the Twitter API delivers these variables, the following variables have been obtained by the corpus creators by processing the tweet content. The sentiment or emotional content of a tweet is modeled by using the [Onyx](https://www.gsi.upm.es/ontologies/onyx/) ontology, which is \"designed to annotate and describe the emotions expressed by user-generated content\". The [SentiStrength](http://sentistrength.wlv.ac.uk/) algorithm results in **positive sentiment** (1 means low and 5 means high) and **negative sentiment** (-1 means low and -5 means high) variables. Though the sentiment expresses the mind state of a user, it is expressed in language and is, hence, a tweet attribute.\n",
    "\n",
    "The dataset producers have also annotated tweets by extracting four different kinds of **facts** (communicative symbols) from tweet texts: named entities (universally recognized semantic concepts), user **mentions** (words starting with <span style='font-family:Courier'>@</span>), **hashtags** (words starting with <span style='font-family:Courier'>#</span>), and **URLs** (addresses of web pages). Since URLs are often too detailed, we will also extract the **TLDs** (top-level domains) from URLs. To identify **named entities**, the [FEL](https://github.com/yahoo/FEL) algorithm matches parts of the tweet **text** to Wikipedia pages as universally identifiable resources and provides a **confidence** score to what extent the match is trustworthy (0 means high and -3 means low confidence). In the example of *figure 2*, the text snippet \"<span style='font-family:Courier'>Federer</span>\" has been matched to the Wikipedia resource [Roger_Federer](https://de.wikipedia.org/wiki/Roger_Federer) with an average confidence of $-1.54$.\n",
    "\n",
    "|<img src='../images/erd.png' style='float: none; width: 640px'>|\n",
    "|:--|\n",
    "|<em style='float: center'>**Figure 3**: Entity relationship diagram to organize Pandas tables</em>|\n",
    "\n",
    "It is clear that named entities, mentions, hashtags, URLs, and TLDs cannot be tweet attributes, as that would create an immense amount of redundancy. Hence, each of these five facts gets its own table. ***Figure 3*** shows the entity relationship diagram into which we will transform the tweets data. The diagram in *figure 3* mirrors the TweetsKB data structure.We will construct the tables shown in the figure by following the rules of [database normalization](https://en.wikipedia.org/wiki/Database_normalization), which we have introduced in section 2.1.1, in the most basic way. Entities are classes in the above sense and are not to be confused with named entities. We will create **entity tables** for the seven entities discussed so far: `tweets`, `users`, `named_entities`, `mentions`, `hashtags`, `utls`, and `tlds`. Each table has a primary key (PK) that uniquely identifies the entity instances in a table. We will use the index of Pandas dataframes as primary keys. Six of those tables have a column called 'tweets', which is the number of tweets a user has created or the number of times a fact has been selected in a tweet.\n",
    "\n",
    "In addition, we will create five **relationship tables** that put tweets into a relationship to facts (named entities, mentions, hashtags, URLs, and TLDs). Relationship tables are depicted using dashed lines in *figure 3*. They just contain entity identifiers (indices) that are now called foreign keys (FK). We will shortly see that relationship tables can be directly used in data analysis. One of the five tables is an exception: The `tweets_named_entities` table has two more columns – the text that was used to name the named entity and the confidence score – because these are true attributes of the relationship between tweets and named entities. Finally, users and tweets are linked in the tweets table via the 'user_idx' column because a tweet is created by one and only one user."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41bb1b12",
   "metadata": {},
   "source": [
    "### A2.2.2. Structuring TweertsCOV19\n",
    "\n",
    "We will be working with the May 2020 dump of the TweertsCOV19 corpus. Download this [file](https://zenodo.org/record/4593502/files/TweetsCOV19_052020.tsv.gz) and put it into the '../data/TweetsCOV19' folder. The [description](https://data.gesis.org/tweetscov19/#Dataset) of the dataset says that each row contains variables of a tweet instance, there are twelve variables (columns), and variables are separated by a tab character ('\\t'). In other words, the data is delivered as a table. Furthermore, the description says that sentiment scores, named entity metadata, etc. are concatenated. In other words, the delivered table is wide in selected columns. Our job will be to transform this table into the multiple tables of *figure 3*. Before reading the full data, it is a good idea to look at the first rows to check if the file contains column names and if there are any peculiarities. Using UTF-8 encoding is recommended since it allows for coding all the different characters used in tweets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "ad2b011e",
   "metadata": {
    "scrolled": false
   },
   "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>1255980348229529601</th>\n",
       "      <th>fa5fd446e778da0acba3504aeab23da5</th>\n",
       "      <th>Thu Apr 30 22:00:24 +0000 2020</th>\n",
       "      <th>29697</th>\n",
       "      <th>24040</th>\n",
       "      <th>0</th>\n",
       "      <th>0.1</th>\n",
       "      <th>null;</th>\n",
       "      <th>1 -1</th>\n",
       "      <th>null;.1</th>\n",
       "      <th>Opinion Next2blowafrica thoughts</th>\n",
       "      <th>null;.2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1255981220640546816</td>\n",
       "      <td>547501e9cc84b8148ae1b8bde04157a4</td>\n",
       "      <td>Thu Apr 30 22:03:52 +0000 2020</td>\n",
       "      <td>799</td>\n",
       "      <td>1278</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>null;</td>\n",
       "      <td>1 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1255981244560683008</td>\n",
       "      <td>840ac60dab55f6b212dc02dcbe5dfbd6</td>\n",
       "      <td>Thu Apr 30 22:03:58 +0000 2020</td>\n",
       "      <td>586</td>\n",
       "      <td>378</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>null;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://www.bbc.com/news/uk-england-beds-bucks...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1255981472285986816</td>\n",
       "      <td>37c68a001198b5efd4a21e2b68a0c9bc</td>\n",
       "      <td>Thu Apr 30 22:04:52 +0000 2020</td>\n",
       "      <td>237</td>\n",
       "      <td>168</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>null;</td>\n",
       "      <td>1 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://lockdownsceptics.org/2020/04/30/latest...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1255981581354905600</td>\n",
       "      <td>8c3620bdfb9d2a1acfdf2412c9b34e06</td>\n",
       "      <td>Thu Apr 30 22:05:18 +0000 2020</td>\n",
       "      <td>423</td>\n",
       "      <td>427</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>i hate u:I_Hate_U:-1.8786140035817729;quaranti...</td>\n",
       "      <td>1 -4</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1255982235662024704</td>\n",
       "      <td>491a98bbc105806cb67f46f5e3f3d888</td>\n",
       "      <td>Thu Apr 30 22:07:54 +0000 2020</td>\n",
       "      <td>52</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>god forbid:God_Forbid:-1.2640735877261988;covi...</td>\n",
       "      <td>2 -4</td>\n",
       "      <td>Danartman BishopStika</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://www.dailymail.co.uk/health/article-826...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1255980348229529601  fa5fd446e778da0acba3504aeab23da5  \\\n",
       "0  1255981220640546816  547501e9cc84b8148ae1b8bde04157a4   \n",
       "1  1255981244560683008  840ac60dab55f6b212dc02dcbe5dfbd6   \n",
       "2  1255981472285986816  37c68a001198b5efd4a21e2b68a0c9bc   \n",
       "3  1255981581354905600  8c3620bdfb9d2a1acfdf2412c9b34e06   \n",
       "4  1255982235662024704  491a98bbc105806cb67f46f5e3f3d888   \n",
       "\n",
       "   Thu Apr 30 22:00:24 +0000 2020  29697  24040  0  0.1  \\\n",
       "0  Thu Apr 30 22:03:52 +0000 2020    799   1278  4    6   \n",
       "1  Thu Apr 30 22:03:58 +0000 2020    586    378  1    2   \n",
       "2  Thu Apr 30 22:04:52 +0000 2020    237    168  0    0   \n",
       "3  Thu Apr 30 22:05:18 +0000 2020    423    427  0    0   \n",
       "4  Thu Apr 30 22:07:54 +0000 2020     52     46  0    0   \n",
       "\n",
       "                                               null;  1 -1  \\\n",
       "0                                              null;  1 -1   \n",
       "1                                              null;  2 -1   \n",
       "2                                              null;  1 -1   \n",
       "3  i hate u:I_Hate_U:-1.8786140035817729;quaranti...  1 -4   \n",
       "4  god forbid:God_Forbid:-1.2640735877261988;covi...  2 -4   \n",
       "\n",
       "                 null;.1 Opinion Next2blowafrica thoughts  \\\n",
       "0                  null;                            null;   \n",
       "1                  null;                            null;   \n",
       "2                  null;                            null;   \n",
       "3                  null;                            null;   \n",
       "4  Danartman BishopStika                            null;   \n",
       "\n",
       "                                             null;.2  \n",
       "0                                              null;  \n",
       "1  https://www.bbc.com/news/uk-england-beds-bucks...  \n",
       "2  https://lockdownsceptics.org/2020/04/30/latest...  \n",
       "3                                              null;  \n",
       "4  https://www.dailymail.co.uk/health/article-826...  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "head = pd.read_csv(\n",
    "    filepath_or_buffer = '../data/TweetsCOV19/TweetsCOV19_052020.tsv.gz', \n",
    "    sep = '\\t', \n",
    "    nrows = 5, \n",
    "    encoding = 'utf-8'\n",
    ")\n",
    "head"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a81a88ad",
   "metadata": {},
   "source": [
    "Get the number of rows and columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ed239d6c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 12)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "head.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e337b48",
   "metadata": {},
   "source": [
    "Knowing that the file does not contain column names and that the separator indeed creates twelve columns, we can read the whole file.\n",
    "\n",
    "<div class='alert alert-block alert-danger'>\n",
    "<b>Caution</b>\n",
    "\n",
    "The file we are about to load is almost 200 MB large in compressed format. When loaded into memory as a dataframe, it consumes almost 1 GB. Since we are about to create many new tables from it, which all require significant amounts of memory, you can quickly reach the limits of the machine you are working on. In fact, if we work with the whole file and run the notebook all until the end, it will consume 4.2 GB. This is too much if, for example, you are executing this notebook on mybinder.org, which gives you 2 GB of memory.\n",
    "</div>\n",
    "\n",
    "To reduce the memory load, take a sample from the already-sampled file. Setting the `seed()` of the \"random\" library will create results that are exactly reproducible. `p` is the sample fraction to load. It is set to 25% to use less than 2 GB of memory. Increase it if you have more memory:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "c086303d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5510919a",
   "metadata": {},
   "outputs": [],
   "source": [
    "random.seed(42)\n",
    "p = .25"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b351c8d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets = pd.read_csv(\n",
    "    filepath_or_buffer = '../data/TweetsCOV19/TweetsCOV19_052020.tsv.gz', \n",
    "    sep = '\\t', \n",
    "    header = None, \n",
    "    skiprows = lambda i: i > 0 and random.random() > p, \n",
    "    quoting = 3, \n",
    "    encoding = 'utf-8'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d90db525",
   "metadata": {},
   "source": [
    "<div class='alert alert-block alert-danger'>\n",
    "<b>Caution</b>\n",
    "\n",
    "Setting the `quoting` parameter of the [`read_csv()`](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) function to the value `3` means that no quoting symbols (*e.g.*, quotation marks) are used to enclose the content of cells in columns. This allows that the respective symbol can be a cell content. In the TweetsCOV19 dataset, some hashtags actually contain quotation marks. Not setting the parameter to `3` would result in a wrong reading of the file.\n",
    "</div>\n",
    "\n",
    "Since the table does not have column names, we use those from the data [description](https://data.gesis.org/tweetscov19/#Dataset):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "4d5c583a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets.columns = ['tweet_id', 'user', 'timestamp', 'followers', 'friends', 'retweets', 'favorites', 'named_entities', 'sentiments', 'mentions', 'hashtags', 'urls']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a69d4b3f",
   "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>user</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>named_entities</th>\n",
       "      <th>sentiments</th>\n",
       "      <th>mentions</th>\n",
       "      <th>hashtags</th>\n",
       "      <th>urls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1255980348229529601</td>\n",
       "      <td>fa5fd446e778da0acba3504aeab23da5</td>\n",
       "      <td>Thu Apr 30 22:00:24 +0000 2020</td>\n",
       "      <td>29697</td>\n",
       "      <td>24040</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>null;</td>\n",
       "      <td>1 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>Opinion Next2blowafrica thoughts</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1255981244560683008</td>\n",
       "      <td>840ac60dab55f6b212dc02dcbe5dfbd6</td>\n",
       "      <td>Thu Apr 30 22:03:58 +0000 2020</td>\n",
       "      <td>586</td>\n",
       "      <td>378</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>null;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://www.bbc.com/news/uk-england-beds-bucks...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1255981581354905600</td>\n",
       "      <td>8c3620bdfb9d2a1acfdf2412c9b34e06</td>\n",
       "      <td>Thu Apr 30 22:05:18 +0000 2020</td>\n",
       "      <td>423</td>\n",
       "      <td>427</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>i hate u:I_Hate_U:-1.8786140035817729;quaranti...</td>\n",
       "      <td>1 -4</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1255985637246865410</td>\n",
       "      <td>d6fff22276aad81631affdecb83a8d9a</td>\n",
       "      <td>Thu Apr 30 22:21:25 +0000 2020</td>\n",
       "      <td>101</td>\n",
       "      <td>127</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>stealth:Stealth_game:-2.646174787470186;</td>\n",
       "      <td>1 -3</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>http://www.safetyhealthnews.com/covid-prompted...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1255985760790069251</td>\n",
       "      <td>c8f0b58eb5105e2318e15ff17b9e4250</td>\n",
       "      <td>Thu Apr 30 22:21:55 +0000 2020</td>\n",
       "      <td>722</td>\n",
       "      <td>572</td>\n",
       "      <td>4</td>\n",
       "      <td>33</td>\n",
       "      <td>fred guttenberg:Fred_Guttenberg:-1.35898888441...</td>\n",
       "      <td>1 -1</td>\n",
       "      <td>fred_guttenberg GovWhitmer</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480700</th>\n",
       "      <td>1267178565298241538</td>\n",
       "      <td>29f87bedcbae099aff8588db39666352</td>\n",
       "      <td>Sun May 31 19:38:07 +0000 2020</td>\n",
       "      <td>833</td>\n",
       "      <td>687</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>missouri:Missouri:-2.144181677743898;florida:F...</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480701</th>\n",
       "      <td>1267189109774266369</td>\n",
       "      <td>f67bd1f0c41cb218ad9e143fc8dda6a8</td>\n",
       "      <td>Sun May 31 20:20:01 +0000 2020</td>\n",
       "      <td>2755</td>\n",
       "      <td>3901</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>covid 19:Coronavirus_disease_2019:-1.535776454...</td>\n",
       "      <td>2 -2</td>\n",
       "      <td>standardnews</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480702</th>\n",
       "      <td>1267189273364578304</td>\n",
       "      <td>a9d5231b8f8ca0bd86884d596bc572dd</td>\n",
       "      <td>Sun May 31 20:20:40 +0000 2020</td>\n",
       "      <td>1610</td>\n",
       "      <td>777</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>eat:East_Africa_Time:-2.4654413209752946;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>jotbro77</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480703</th>\n",
       "      <td>1267196986660458499</td>\n",
       "      <td>8e93b969fa0082b6fcba6acc2c9e3e8c</td>\n",
       "      <td>Sun May 31 20:51:19 +0000 2020</td>\n",
       "      <td>52</td>\n",
       "      <td>152</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>skynews:Sky_News:-2.4443176685121135;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>SkyNews</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480704</th>\n",
       "      <td>1267207883487354881</td>\n",
       "      <td>0e4323d01d164b9eb6e33f35564c7e25</td>\n",
       "      <td>Sun May 31 21:34:37 +0000 2020</td>\n",
       "      <td>43</td>\n",
       "      <td>931</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>china:China:-2.113921624336916;death penalty:C...</td>\n",
       "      <td>1 -2</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>480705 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   tweet_id                              user  \\\n",
       "0       1255980348229529601  fa5fd446e778da0acba3504aeab23da5   \n",
       "1       1255981244560683008  840ac60dab55f6b212dc02dcbe5dfbd6   \n",
       "2       1255981581354905600  8c3620bdfb9d2a1acfdf2412c9b34e06   \n",
       "3       1255985637246865410  d6fff22276aad81631affdecb83a8d9a   \n",
       "4       1255985760790069251  c8f0b58eb5105e2318e15ff17b9e4250   \n",
       "...                     ...                               ...   \n",
       "480700  1267178565298241538  29f87bedcbae099aff8588db39666352   \n",
       "480701  1267189109774266369  f67bd1f0c41cb218ad9e143fc8dda6a8   \n",
       "480702  1267189273364578304  a9d5231b8f8ca0bd86884d596bc572dd   \n",
       "480703  1267196986660458499  8e93b969fa0082b6fcba6acc2c9e3e8c   \n",
       "480704  1267207883487354881  0e4323d01d164b9eb6e33f35564c7e25   \n",
       "\n",
       "                             timestamp  followers  friends  retweets  \\\n",
       "0       Thu Apr 30 22:00:24 +0000 2020      29697    24040         0   \n",
       "1       Thu Apr 30 22:03:58 +0000 2020        586      378         1   \n",
       "2       Thu Apr 30 22:05:18 +0000 2020        423      427         0   \n",
       "3       Thu Apr 30 22:21:25 +0000 2020        101      127         0   \n",
       "4       Thu Apr 30 22:21:55 +0000 2020        722      572         4   \n",
       "...                                ...        ...      ...       ...   \n",
       "480700  Sun May 31 19:38:07 +0000 2020        833      687         0   \n",
       "480701  Sun May 31 20:20:01 +0000 2020       2755     3901         0   \n",
       "480702  Sun May 31 20:20:40 +0000 2020       1610      777         0   \n",
       "480703  Sun May 31 20:51:19 +0000 2020         52      152         0   \n",
       "480704  Sun May 31 21:34:37 +0000 2020         43      931         0   \n",
       "\n",
       "        favorites                                     named_entities  \\\n",
       "0               0                                              null;   \n",
       "1               2                                              null;   \n",
       "2               0  i hate u:I_Hate_U:-1.8786140035817729;quaranti...   \n",
       "3               0           stealth:Stealth_game:-2.646174787470186;   \n",
       "4              33  fred guttenberg:Fred_Guttenberg:-1.35898888441...   \n",
       "...           ...                                                ...   \n",
       "480700          0  missouri:Missouri:-2.144181677743898;florida:F...   \n",
       "480701          0  covid 19:Coronavirus_disease_2019:-1.535776454...   \n",
       "480702          0          eat:East_Africa_Time:-2.4654413209752946;   \n",
       "480703          0              skynews:Sky_News:-2.4443176685121135;   \n",
       "480704          0  china:China:-2.113921624336916;death penalty:C...   \n",
       "\n",
       "       sentiments                    mentions  \\\n",
       "0            1 -1                       null;   \n",
       "1            2 -1                       null;   \n",
       "2            1 -4                       null;   \n",
       "3            1 -3                       null;   \n",
       "4            1 -1  fred_guttenberg GovWhitmer   \n",
       "...           ...                         ...   \n",
       "480700       2 -1                       null;   \n",
       "480701       2 -2                standardnews   \n",
       "480702       2 -1                    jotbro77   \n",
       "480703       2 -1                     SkyNews   \n",
       "480704       1 -2                       null;   \n",
       "\n",
       "                                hashtags  \\\n",
       "0       Opinion Next2blowafrica thoughts   \n",
       "1                                  null;   \n",
       "2                                  null;   \n",
       "3                                  null;   \n",
       "4                                  null;   \n",
       "...                                  ...   \n",
       "480700                             null;   \n",
       "480701                             null;   \n",
       "480702                             null;   \n",
       "480703                             null;   \n",
       "480704                             null;   \n",
       "\n",
       "                                                     urls  \n",
       "0                                                   null;  \n",
       "1       https://www.bbc.com/news/uk-england-beds-bucks...  \n",
       "2                                                   null;  \n",
       "3       http://www.safetyhealthnews.com/covid-prompted...  \n",
       "4                                                   null;  \n",
       "...                                                   ...  \n",
       "480700                                              null;  \n",
       "480701                                              null;  \n",
       "480702                                              null;  \n",
       "480703                                              null;  \n",
       "480704                                              null;  \n",
       "\n",
       "[480705 rows x 12 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fedb462d",
   "metadata": {},
   "source": [
    "There are 480 thousand tweets (1.9 million for the full sample). Look at the last five columns to see how multiple annotations are stored in single cells.\n",
    "\n",
    "#### Creating the `users` table\n",
    "\n",
    "Besides the 'user' name, the `users` table should also contain the number of tweets the user has created as well as the maximum number of followers and friends. Aggregate the data using `groupby()` with the `size()` method to count the number of rows (i.e., the number of tweets)..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "5b502ffa",
   "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>user</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000016e54a4dc155432ebad949c2546e</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000037774001c1016af0b621b1299657</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000058bdeaa281c0e1648193b163fa4f</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000088cdedbf61ede06e027bdc0710d3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000a7994a8534f67944738260474cd7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               user  tweets\n",
       "0  000016e54a4dc155432ebad949c2546e       1\n",
       "1  000037774001c1016af0b621b1299657       1\n",
       "2  000058bdeaa281c0e1648193b163fa4f       1\n",
       "3  000088cdedbf61ede06e027bdc0710d3       1\n",
       "4  0000a7994a8534f67944738260474cd7       1"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users = tweets.groupby(by='user').size().reset_index(name='tweets')\n",
    "users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b97c5870",
   "metadata": {},
   "source": [
    "and then with the `.max()` method to get the maximum number of followers and friends:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "b10d1b2f",
   "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>user</th>\n",
       "      <th>followers_max</th>\n",
       "      <th>friends_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000016e54a4dc155432ebad949c2546e</td>\n",
       "      <td>6853</td>\n",
       "      <td>992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000037774001c1016af0b621b1299657</td>\n",
       "      <td>7</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000058bdeaa281c0e1648193b163fa4f</td>\n",
       "      <td>3823</td>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000088cdedbf61ede06e027bdc0710d3</td>\n",
       "      <td>999</td>\n",
       "      <td>2570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000a7994a8534f67944738260474cd7</td>\n",
       "      <td>225</td>\n",
       "      <td>851</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               user  followers_max  friends_max\n",
       "0  000016e54a4dc155432ebad949c2546e           6853          992\n",
       "1  000037774001c1016af0b621b1299657              7           50\n",
       "2  000058bdeaa281c0e1648193b163fa4f           3823          167\n",
       "3  000088cdedbf61ede06e027bdc0710d3            999         2570\n",
       "4  0000a7994a8534f67944738260474cd7            225          851"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_ff = tweets.groupby(by='user')[['followers', 'friends']].max().reset_index()\n",
    "users_ff.columns = ['user', 'followers_max', 'friends_max']\n",
    "users_ff.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8bf5052a",
   "metadata": {},
   "source": [
    "Since these dataframes are both ordered alphabetically and have the same length, we can simply add the two columns from `users_ff` to the `users` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "1dc67fb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "users[['followers_max', 'friends_max']] = users_ff[['followers_max', 'friends_max']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d30db09e",
   "metadata": {},
   "source": [
    "Sort the dataframe descendingly by the number of tweets, maximum number of followers, and maximum number of friends (in that order):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "63b3a109",
   "metadata": {},
   "outputs": [],
   "source": [
    "users = users.sort_values(by=['tweets', 'followers_max', 'friends_max'], ascending=False).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43475042",
   "metadata": {},
   "source": [
    "Finally, reorder the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "1bf44ae9",
   "metadata": {},
   "outputs": [],
   "source": [
    "users = users[['user', 'tweets', 'followers_max', 'friends_max']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc56347d",
   "metadata": {},
   "source": [
    "The index will function as a unique user identifier:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "90409559",
   "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>user</th>\n",
       "      <th>tweets</th>\n",
       "      <th>followers_max</th>\n",
       "      <th>friends_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7513717dba8b208fe06799dcc54e59e2</td>\n",
       "      <td>464</td>\n",
       "      <td>21985195</td>\n",
       "      <td>1116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2435a45b85628172c5a47122144a7c67</td>\n",
       "      <td>357</td>\n",
       "      <td>48295579</td>\n",
       "      <td>1109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4ff35e52034daec0251f7b3370969a1a</td>\n",
       "      <td>353</td>\n",
       "      <td>4149063</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>090264f1888056a96f32ccb7d91ba4e7</td>\n",
       "      <td>321</td>\n",
       "      <td>3788655</td>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bf4571b94429c5b18e0a219c197a56a4</td>\n",
       "      <td>278</td>\n",
       "      <td>6145493</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353369</th>\n",
       "      <td>ff0b29d605ef1707c95bc7e8c8fb6694</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353370</th>\n",
       "      <td>ff6a57fc7c7cd18cc2a293b79493756a</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353371</th>\n",
       "      <td>ffc5ea3bff62a184be9cdcd399ec27dd</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353372</th>\n",
       "      <td>ffeb62654f94d36a4075ca408ecf0089</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353373</th>\n",
       "      <td>fff237115d3d097c0c0d8064cc15048b</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>353374 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    user  tweets  followers_max  friends_max\n",
       "0       7513717dba8b208fe06799dcc54e59e2     464       21985195         1116\n",
       "1       2435a45b85628172c5a47122144a7c67     357       48295579         1109\n",
       "2       4ff35e52034daec0251f7b3370969a1a     353        4149063            0\n",
       "3       090264f1888056a96f32ccb7d91ba4e7     321        3788655          266\n",
       "4       bf4571b94429c5b18e0a219c197a56a4     278        6145493           28\n",
       "...                                  ...     ...            ...          ...\n",
       "353369  ff0b29d605ef1707c95bc7e8c8fb6694       1              0            0\n",
       "353370  ff6a57fc7c7cd18cc2a293b79493756a       1              0            0\n",
       "353371  ffc5ea3bff62a184be9cdcd399ec27dd       1              0            0\n",
       "353372  ffeb62654f94d36a4075ca408ecf0089       1              0            0\n",
       "353373  fff237115d3d097c0c0d8064cc15048b       1              0            0\n",
       "\n",
       "[353374 rows x 4 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af3e58cd",
   "metadata": {},
   "source": [
    "Note that 'user' names are encrypted for privacy reasons in the original dataset. There are 350 thousand distinct users (1.1 million in the full dataset), and the most active one has created 1,989 tweets (in the full dataset). Indeed, it is not an error that some users have tens of millions of followers [and more](https://en.wikipedia.org/wiki/List_of_most-followed_Twitter_accounts). An interesting observation is that the most active users also have many followers.\n",
    "\n",
    "The index values of this table are unique identifiers for the users in the dataset (the primary keys). The effect of sorting is that the most active users have small index values, which aids computational purposes, as you will see. To not waste memory, it is good practice to delete dataframes we do not need anymore:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "cbf106d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "del users_ff"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72fad0b8",
   "metadata": {},
   "source": [
    "#### Creating the `tweets` table\n",
    "\n",
    "We will proceed by refining the existing `tweets` table. Sorting tweets by date and time is straightforward. For handling such data, Pandas provides the 'datetime' data type. It is perfectly suited for handling time series data as it allows many ways to manipulate dates and times. For now, we will simply transform the 'timestamp' values from 'string' [`to_datetime()`](https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html). Set the `format` of the original string to spare Pandas figuring it out itself (and save time):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "971b0335",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets['timestamp'] = pd.to_datetime(tweets['timestamp'], format='%a %b %d %X %z %Y')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "128be749",
   "metadata": {},
   "source": [
    "Knowing that the timezone is Coordinated Universal Time (UTC), remove it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "08d16c0e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "UTC\n"
     ]
    }
   ],
   "source": [
    "print(tweets['timestamp'].dt.tz)\n",
    "tweets['timestamp'] = tweets['timestamp'].dt.tz_localize(tz=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d47269a2",
   "metadata": {},
   "source": [
    "Then remove tweets from April 2020 (since they are not complete) and sort the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "5cc7ebe2",
   "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>user</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>named_entities</th>\n",
       "      <th>sentiments</th>\n",
       "      <th>mentions</th>\n",
       "      <th>hashtags</th>\n",
       "      <th>urls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1256010446827433984</td>\n",
       "      <td>4abcedcab022df9a8ecaecc7399fb4f2</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>7449</td>\n",
       "      <td>636</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>null;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>domesticviolence</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1256010446559039488</td>\n",
       "      <td>9897f9df6e48f764570e5e4535833703</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>85194</td>\n",
       "      <td>1496</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>null;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://www.themarysue.com/yes-i-would-use-thi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1256010445908881410</td>\n",
       "      <td>d67ad51dab16d347d48c819bdd284ea2</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>88212</td>\n",
       "      <td>635</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>sesame street:Sesame_Street:-1.0571497380025907;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://thehardtimes.net/culture/sesame-street...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1256010447377104898</td>\n",
       "      <td>8c3f9d949ad79d3e2023b7486997ff41</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>359853</td>\n",
       "      <td>811</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>coronavirus disease 2019:Coronavirus_disease_2...</td>\n",
       "      <td>1 -3</td>\n",
       "      <td>RitaRubin</td>\n",
       "      <td>coronavirus convalescentplasma COVID19</td>\n",
       "      <td>https://jamanetwork.com/journals/jama/fullarti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1256010459125178369</td>\n",
       "      <td>2c0f39371993cc0b77ead929b4f94cff</td>\n",
       "      <td>2020-05-01 00:00:03</td>\n",
       "      <td>55619</td>\n",
       "      <td>151</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>jeff bezos:Jeff_Bezos:-0.814832966325326;amazo...</td>\n",
       "      <td>3 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>http://i.securitythinkingcap.com/RVnWKF:-:</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id                              user           timestamp  \\\n",
       "0  1256010446827433984  4abcedcab022df9a8ecaecc7399fb4f2 2020-05-01 00:00:00   \n",
       "1  1256010446559039488  9897f9df6e48f764570e5e4535833703 2020-05-01 00:00:00   \n",
       "2  1256010445908881410  d67ad51dab16d347d48c819bdd284ea2 2020-05-01 00:00:00   \n",
       "3  1256010447377104898  8c3f9d949ad79d3e2023b7486997ff41 2020-05-01 00:00:00   \n",
       "4  1256010459125178369  2c0f39371993cc0b77ead929b4f94cff 2020-05-01 00:00:03   \n",
       "\n",
       "   followers  friends  retweets  favorites  \\\n",
       "0       7449      636         3          2   \n",
       "1      85194     1496         0          0   \n",
       "2      88212      635        13         43   \n",
       "3     359853      811         1          0   \n",
       "4      55619      151         0          0   \n",
       "\n",
       "                                      named_entities sentiments   mentions  \\\n",
       "0                                              null;       2 -1      null;   \n",
       "1                                              null;       2 -1      null;   \n",
       "2   sesame street:Sesame_Street:-1.0571497380025907;       2 -1      null;   \n",
       "3  coronavirus disease 2019:Coronavirus_disease_2...       1 -3  RitaRubin   \n",
       "4  jeff bezos:Jeff_Bezos:-0.814832966325326;amazo...       3 -1      null;   \n",
       "\n",
       "                                 hashtags  \\\n",
       "0                        domesticviolence   \n",
       "1                                   null;   \n",
       "2                                   null;   \n",
       "3  coronavirus convalescentplasma COVID19   \n",
       "4                                   null;   \n",
       "\n",
       "                                                urls  \n",
       "0                                              null;  \n",
       "1  https://www.themarysue.com/yes-i-would-use-thi...  \n",
       "2  https://thehardtimes.net/culture/sesame-street...  \n",
       "3  https://jamanetwork.com/journals/jama/fullarti...  \n",
       "4         http://i.securitythinkingcap.com/RVnWKF:-:  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets = tweets[tweets['timestamp'] >= '2020-05-01']\n",
    "tweets = tweets.sort_values(by=['timestamp']).reset_index(drop=True)\n",
    "tweets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b78616f2",
   "metadata": {},
   "source": [
    "After sorting, the index is stable and acts as a unique tweet identifier.\n",
    "\n",
    "The first change the table needs is to replace the 'user' name with the 'user_idx' index from the `users` table. Since we will repeat this operation for other tables, we define an `add_index()` function. Following best Python practice, what it does is described in the function itself:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "adbd11ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_index(source, target, entity):\n",
    "    '''\n",
    "    Inserts the index of a source dataframe into a target dataframe as a column.\n",
    "    \n",
    "    Parameters:\n",
    "        source : Pandas DataFrame\n",
    "            Dataframe whose index is to be inserted.\n",
    "        target : Pandas DataFrame\n",
    "            Dataframe into which the index is inserted.\n",
    "        entity : String\n",
    "            Name of the entity that is identified by the index. Will be given an '_idx' suffix and then inserted into the target dataframe.\n",
    "    \n",
    "    Returns:\n",
    "        The target dataframe with the inserted column.\n",
    "    '''\n",
    "    _ = source.copy()\n",
    "    _[entity + '_idx'] = _.index\n",
    "    df = pd.merge(left=target, right=_[[entity + '_idx', entity]], on=entity)\n",
    "    del df[entity]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "00140e83",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets = add_index(source=users, target=tweets, entity='user')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea744bb6",
   "metadata": {},
   "source": [
    "After reordering the columns, the 'user_idx' column is at the right position:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "18fb95f8",
   "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>user_idx</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>named_entities</th>\n",
       "      <th>sentiments</th>\n",
       "      <th>mentions</th>\n",
       "      <th>hashtags</th>\n",
       "      <th>urls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1256010446827433984</td>\n",
       "      <td>80519</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>7449</td>\n",
       "      <td>636</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>null;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>domesticviolence</td>\n",
       "      <td>null;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1256010446559039488</td>\n",
       "      <td>51478</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>85194</td>\n",
       "      <td>1496</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>null;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://www.themarysue.com/yes-i-would-use-thi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1256010445908881410</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>88212</td>\n",
       "      <td>635</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>sesame street:Sesame_Street:-1.0571497380025907;</td>\n",
       "      <td>2 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://thehardtimes.net/culture/sesame-street...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1257097608738140161</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-04 00:00:00</td>\n",
       "      <td>88395</td>\n",
       "      <td>635</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>cdc:Centers_for_Disease_Control_and_Prevention...</td>\n",
       "      <td>1 -1</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://thehardtimes.net/culture/bald-guy-push...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1259256865126600705</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-09 23:00:07</td>\n",
       "      <td>88644</td>\n",
       "      <td>634</td>\n",
       "      <td>65</td>\n",
       "      <td>201</td>\n",
       "      <td>doomsday preppers:Doomsday_Preppers:-1.5662125...</td>\n",
       "      <td>1 -2</td>\n",
       "      <td>null;</td>\n",
       "      <td>null;</td>\n",
       "      <td>https://thehardtimes.net/music/doomsday-preppe...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id  user_idx           timestamp  followers  friends  \\\n",
       "0  1256010446827433984     80519 2020-05-01 00:00:00       7449      636   \n",
       "1  1256010446559039488     51478 2020-05-01 00:00:00      85194     1496   \n",
       "2  1256010445908881410      4418 2020-05-01 00:00:00      88212      635   \n",
       "3  1257097608738140161      4418 2020-05-04 00:00:00      88395      635   \n",
       "4  1259256865126600705      4418 2020-05-09 23:00:07      88644      634   \n",
       "\n",
       "   retweets  favorites                                     named_entities  \\\n",
       "0         3          2                                              null;   \n",
       "1         0          0                                              null;   \n",
       "2        13         43   sesame street:Sesame_Street:-1.0571497380025907;   \n",
       "3         2          8  cdc:Centers_for_Disease_Control_and_Prevention...   \n",
       "4        65        201  doomsday preppers:Doomsday_Preppers:-1.5662125...   \n",
       "\n",
       "  sentiments mentions          hashtags  \\\n",
       "0       2 -1    null;  domesticviolence   \n",
       "1       2 -1    null;             null;   \n",
       "2       2 -1    null;             null;   \n",
       "3       1 -1    null;             null;   \n",
       "4       1 -2    null;             null;   \n",
       "\n",
       "                                                urls  \n",
       "0                                              null;  \n",
       "1  https://www.themarysue.com/yes-i-would-use-thi...  \n",
       "2  https://thehardtimes.net/culture/sesame-street...  \n",
       "3  https://thehardtimes.net/culture/bald-guy-push...  \n",
       "4  https://thehardtimes.net/music/doomsday-preppe...  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets = tweets[['tweet_id', 'user_idx', 'timestamp', 'followers', 'friends', 'retweets', 'favorites', 'named_entities', 'sentiments', 'mentions', 'hashtags', 'urls']]\n",
    "tweets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b377769d",
   "metadata": {},
   "source": [
    "#### Creating the `named_entities` and `tweets_named_entities` tables\n",
    "\n",
    "Next, we process the facts. In general, we proceed by, first, extracting relationship tables from the `tweets` table and, second, deriving the entity tables from the relationship tables. We start with the most complicated case of **named entities**. The 'named_entities' column of the `tweets` table contains ';'-separated 3-tuples, each of which contains ':'-separated values for 'text', 'named_entity', and 'confidence'. In the process of normalization, the first step is to transform cell content into lists of 3-tuples. Again, we define a custom `to_list()` function..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "24ff4298",
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_list(cell, pat):\n",
    "    '''\n",
    "    Function to be applied to individual cells of a dataframe column. Transforms concatenated cell content into a list.\n",
    "    \n",
    "    Parameters:\n",
    "        pat : String\n",
    "            Pattern that separates the cell values.\n",
    "    \n",
    "    Returns:\n",
    "        The cell will automatically be overwritten by a potentially empty list.\n",
    "    '''\n",
    "    if cell == 'null;' or type(cell) == float:\n",
    "        cell = ['']\n",
    "    else:\n",
    "        cell = cell.split(pat)\n",
    "    return cell"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ba583d2",
   "metadata": {},
   "source": [
    "that we can now `apply` cell by cell to the 'named_entities' column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "0e784dfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets['named_entities'] = tweets['named_entities'].apply(to_list, pat=';')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "279f4546",
   "metadata": {},
   "source": [
    "An example cell with a list of multiple 3-tuples now looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "c64f5c9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['sesame street:Sesame_Street:-1.0571497380025907', '']"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets['named_entities'][2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b01bb7ab",
   "metadata": {},
   "source": [
    "Next, we create the `tweets_named_entities` relationship table. The reason for having created a list of 3-tuples is that a wide table – the subtable with just the 'named_entities' column – can be easily and quickly transformed into a long table using the [`explode()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.explode.html) method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "287af264",
   "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>named_entities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sesame street:Sesame_Street:-1.0571497380025907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cdc:Centers_for_Disease_Control_and_Prevention...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      named_entities\n",
       "0                                                   \n",
       "1                                                   \n",
       "2    sesame street:Sesame_Street:-1.0571497380025907\n",
       "2                                                   \n",
       "3  cdc:Centers_for_Disease_Control_and_Prevention..."
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_named_entities = tweets[['named_entities']].explode(column='named_entities')\n",
    "del tweets['named_entities']\n",
    "tweets_named_entities.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf28b9d7",
   "metadata": {},
   "source": [
    "Remove rows without 3-tuples (concatenation artifacts) and split the 3-tuples into three columns by splitting the string at ':':"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "acb1bbe9",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sesame street</td>\n",
       "      <td>Sesame_Street</td>\n",
       "      <td>-1.0571497380025907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cdc</td>\n",
       "      <td>Centers_for_Disease_Control_and_Prevention</td>\n",
       "      <td>-2.5639323763517483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>doomsday preppers</td>\n",
       "      <td>Doomsday_Preppers</td>\n",
       "      <td>-1.5662125698192169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ska</td>\n",
       "      <td>Ska</td>\n",
       "      <td>-1.7401045224490466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>hydroxychloroquine</td>\n",
       "      <td>Hydroxychloroquine</td>\n",
       "      <td>-1.083599029221355</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    0                                           1  \\\n",
       "2       sesame street                               Sesame_Street   \n",
       "3                 cdc  Centers_for_Disease_Control_and_Prevention   \n",
       "4   doomsday preppers                           Doomsday_Preppers   \n",
       "4                 ska                                         Ska   \n",
       "7  hydroxychloroquine                          Hydroxychloroquine   \n",
       "\n",
       "                     2  \n",
       "2  -1.0571497380025907  \n",
       "3  -2.5639323763517483  \n",
       "4  -1.5662125698192169  \n",
       "4  -1.7401045224490466  \n",
       "7   -1.083599029221355  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_named_entities = tweets_named_entities[tweets_named_entities['named_entities'] != '']\n",
    "tweets_named_entities = tweets_named_entities['named_entities'].str.split(pat=':', expand=True)\n",
    "tweets_named_entities.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54449eb9",
   "metadata": {},
   "source": [
    "At this point, the index consists of the unique tweet identifiers because we used `explode()` on a `tweets` subtable.\n",
    "\n",
    "<div class='alert alert-block alert-info'>\n",
    "<b>Insight: Data quality</b>\n",
    "\n",
    "Part of what we call \"getting a feeling for your data\" is actually to look at it. For example, here is the right stage in the data processing pipeline to check how well the named-entity-recognition algorithm worked because the `tweets_named_entities` table still contains the 'text' from which a named entity was recognized as well as the name of the 'named_entity'. Pandas is actually not the best tool to read tables as it only displays up to 50 rows at a time. You can cycle through your columns using `loc[]`, but it is more convenient to use a spreadsheet editor for the task. Therefore, we export the distinct rows of the `tweets_named_entities` table to an Excel file. The first column is the tweet index, and you can look up the 'tweet_id' via `tweets.loc[<tweet index without angle brackets>, :]`. You can read the actual tweet of any user (*e.g.*, with the 'tweet id' $1262933787131904001$) by visiting https://twitter.com/anyuser/status/1262933787131904001.\n",
    "\n",
    "Going through the rows, you will notice that the text snippet <span style='font-family:Courier'>democrat</span> is matched to the [Democratic_Party_(United_States)](https://en.wikipedia.org/wiki/Democratic_Party_(United_States)) named entity with a confidence of -1.81. Even though the matching is mostly right in our particular context, the algorithm will also mismatch a lot of discourse about democracy itself. The problem also exists the other way around. The text <span style='font-family:Courier'>dems</span>, which is frequently used to name the Democratic Party of the US, is matched to [Defensively_equipped_merchant_ship](https://en.wikipedia.org/wiki/Defensively_equipped_merchant_ship) with a slightly weaker confidence of -2.03. The idea is to use this score to filter out bad matchings. However, setting the filter to -2.00 would result in missing the correct matching of <span style='font-family:Courier'>us democratic party</span>, which has a score of -2.06. As you go through the spreadsheet, you will notice more mistakes. All these point to the general problem of fully automated data processing.\n",
    "</div>\n",
    "\n",
    "Before the Excel file is saved, the target directory is created if it does not exist:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "79e1663c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "directory = 'results'\n",
    "if not os.path.exists(directory):\n",
    "    os.makedirs(directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "f831841b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you are running this session in Google Colab, install this package\n",
    "#!pip install xlsxwriter==3.0.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "97a121d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_named_entities.drop_duplicates().to_excel(\n",
    "    excel_writer = 'results/tweets_named_entities_dropped_duplicates.xlsx', \n",
    "    engine = 'xlsxwriter'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "539022ea",
   "metadata": {},
   "source": [
    "Continuing with creating `tweets_named_entities`, if we now reset the index without dropping the old one, the tweet index values will be added as the first column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "a23bde26",
   "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_idx</th>\n",
       "      <th>text</th>\n",
       "      <th>named_entity</th>\n",
       "      <th>confidence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>Sesame_Street</td>\n",
       "      <td>-1.0571497380025907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>cdc</td>\n",
       "      <td>Centers_for_Disease_Control_and_Prevention</td>\n",
       "      <td>-2.5639323763517483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>doomsday preppers</td>\n",
       "      <td>Doomsday_Preppers</td>\n",
       "      <td>-1.5662125698192169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>ska</td>\n",
       "      <td>Ska</td>\n",
       "      <td>-1.7401045224490466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>hydroxychloroquine</td>\n",
       "      <td>Hydroxychloroquine</td>\n",
       "      <td>-1.083599029221355</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tweet_idx                text                                named_entity  \\\n",
       "0          2       sesame street                               Sesame_Street   \n",
       "1          3                 cdc  Centers_for_Disease_Control_and_Prevention   \n",
       "2          4   doomsday preppers                           Doomsday_Preppers   \n",
       "3          4                 ska                                         Ska   \n",
       "4          7  hydroxychloroquine                          Hydroxychloroquine   \n",
       "\n",
       "            confidence  \n",
       "0  -1.0571497380025907  \n",
       "1  -2.5639323763517483  \n",
       "2  -1.5662125698192169  \n",
       "3  -1.7401045224490466  \n",
       "4   -1.083599029221355  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_named_entities = tweets_named_entities.reset_index(drop=False)\n",
    "tweets_named_entities.columns = ['tweet_idx', 'text', 'named_entity', 'confidence']\n",
    "tweets_named_entities.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "855c01f3",
   "metadata": {},
   "source": [
    "Drop duplicate relationships because we are not interested in multiple occurrences of a named entity in one tweet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "e3ed913a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_named_entities = tweets_named_entities.drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b691cdb9",
   "metadata": {},
   "source": [
    "The `named_entities` entity table with the desired 'tweets' column is created from the `tweets_named_entities` relationship table, again using a dedicated function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "f325d6f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_entity_table(relationship_table, entity):\n",
    "    '''\n",
    "    Creates an entity table from a relationship table via aggregation.\n",
    "    \n",
    "    Parameters:\n",
    "        relationship_table : Pandas DataFrame\n",
    "            Dataframe that contains tweet entity relationships.\n",
    "        entity : String\n",
    "            Name of the entity column in the relationship table that contains the entities.\n",
    "    \n",
    "    Returns:\n",
    "        An entity table sorted descendingly by an additional 'tweets' column giving the number of tweets that selected an entity.\n",
    "    '''\n",
    "    df = relationship_table.groupby(entity).size().reset_index(name='tweets')\n",
    "    df = df.sort_values(by=['tweets', entity], ascending=[False, True]).reset_index(drop=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "8afefc22",
   "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>named_entity</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Coronavirus_disease_2019</td>\n",
       "      <td>34212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Quarantine</td>\n",
       "      <td>17190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>China</td>\n",
       "      <td>12892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Social_distancing</td>\n",
       "      <td>9636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Twitter</td>\n",
       "      <td>8905</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               named_entity  tweets\n",
       "0  Coronavirus_disease_2019   34212\n",
       "1                Quarantine   17190\n",
       "2                     China   12892\n",
       "3         Social_distancing    9636\n",
       "4                   Twitter    8905"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "named_entities = create_entity_table(relationship_table=tweets_named_entities, entity='named_entity')\n",
    "named_entities.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b533a1f",
   "metadata": {},
   "source": [
    "Given this entity table, add its index to the corresponding `tweets_named_entities` relationship table, reorder the columns to obtain the desired design, and change the data type of the 'confidence' scores from string to a rounded float:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "a5731433",
   "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_idx</th>\n",
       "      <th>named_entity_idx</th>\n",
       "      <th>text</th>\n",
       "      <th>confidence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3890</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>77675</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>207706</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>216831</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tweet_idx  named_entity_idx           text  confidence\n",
       "0          2              6611  sesame street     -1.0571\n",
       "1       3890              6611  sesame street     -1.0571\n",
       "2      77675              6611  sesame street     -1.0571\n",
       "3     207706              6611  sesame street     -1.0571\n",
       "4     216831              6611  sesame street     -1.0571"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_named_entities = add_index(source=named_entities, target=tweets_named_entities, entity='named_entity')\n",
    "tweets_named_entities = tweets_named_entities[['tweet_idx', 'named_entity_idx', 'text', 'confidence']]\n",
    "tweets_named_entities['confidence'] = tweets_named_entities['confidence'].astype(float).round(4)\n",
    "tweets_named_entities.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "435f0df1",
   "metadata": {},
   "source": [
    "#### Creating the other relationship and entity tables\n",
    "\n",
    "The data of the remaining facts (mentions, hashtags, URLs, and TLDs) is easier to normalize because the relationship tables only contain foreign keys. Without having to process metadata from named entity recognition, we can create the relationship tables via a general function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "1f6357d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_relationship_table(entity, to_lower_case, drop_duplicates=True, source=tweets):\n",
    "    '''\n",
    "    Creates a relationship table for a given entity from the `tweets` table.\n",
    "    \n",
    "    Parameters:\n",
    "        source : Pandas DataFrame, default `tweets`\n",
    "            Table that contains the entity column.\n",
    "        entity : String\n",
    "            Name of the entity column in the source table that contains the entities. The column must contain an object data type list of entity names.\n",
    "        to_lower_case : Boolean\n",
    "            Whether entity names should be reduced to lower case.\n",
    "        drop_duplicates : Boolean, default True\n",
    "            Whether duplicate relationships should be removed.\n",
    "    \n",
    "    Returns:\n",
    "        A relationship table linking tweet indices to entity names.\n",
    "    '''\n",
    "    df = source[[entity + 's']].explode(column=entity + 's')\n",
    "    df = df[df[entity + 's'] != '']\n",
    "    df = df.reset_index()\n",
    "    df.columns = ['tweet_idx', entity]\n",
    "    if to_lower_case == True:\n",
    "        df[entity] = df[entity].str.lower()\n",
    "    if drop_duplicates == True:\n",
    "        df = df.drop_duplicates().reset_index(drop=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4e6eb86",
   "metadata": {},
   "source": [
    "The processing pipeline is the same for all four facts:\n",
    "\n",
    "1. Transform the entity column in the `tweets` table to a list\n",
    "2. Create the relationship table from the entity column in the `tweets` table, dropping duplicate rows by default\n",
    "3. Delete the entity column in the `tweets` table\n",
    "4. Create the entity table from the relationship table\n",
    "5. Add the entity index to the relationship table\n",
    "\n",
    "In the case of **mentions**, in step 2, we must transform all capital (upper case) characters to lower case. This is because user names on Twitter are not case-sensitive. In other words, when a user named \"realDonaldTrump\" already exists, no new user will be allowed with the name \"realdonaldtrump\". Since user mentions are extracted as words starting with <span style='font-family:Courier'>@</span>, but tweet creators often use upper and lower cases as they wish, not transforming upper to lower case would result in the same mentioned user getting more than a single unique identifier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "b340aadc",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets['mentions'] = tweets['mentions'].apply(to_list, pat=' ') # Step 1\n",
    "tweets_mentions = create_relationship_table(entity='mention', to_lower_case=True) # Step 2\n",
    "del tweets['mentions'] # Step 3\n",
    "mentions = create_entity_table(relationship_table=tweets_mentions, entity='mention') # Step 4\n",
    "tweets_mentions = add_index(source=mentions, target=tweets_mentions, entity='mention') # Step 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "ae2e9c20",
   "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>mention</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>realdonaldtrump</td>\n",
       "      <td>9396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>pmoindia</td>\n",
       "      <td>1650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>narendramodi</td>\n",
       "      <td>1571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>jaketapper</td>\n",
       "      <td>1493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>youtube</td>\n",
       "      <td>1383</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           mention  tweets\n",
       "0  realdonaldtrump    9396\n",
       "1         pmoindia    1650\n",
       "2     narendramodi    1571\n",
       "3       jaketapper    1493\n",
       "4          youtube    1383"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mentions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33d39d13",
   "metadata": {},
   "source": [
    "Donald Trump is the most mentioned user by far, followed by the prime minister of India. Narendra Modi. both with his official and private accounts.\n",
    "\n",
    "In the case of **hashtags**, do the same transformation from upper to lower case to prevent synonymous hashtags getting from different indices:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "6a591a14",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets['hashtags'] = tweets['hashtags'].apply(to_list, pat=' ') # Step 1\n",
    "tweets_hashtags = create_relationship_table(entity='hashtag', to_lower_case=True) # Step 2\n",
    "del tweets['hashtags'] # Step 3\n",
    "hashtags = create_entity_table(relationship_table=tweets_hashtags, entity='hashtag') # Step 4\n",
    "tweets_hashtags = add_index(source=hashtags, target=tweets_hashtags, entity='hashtag') # Step 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "864f1b50",
   "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>hashtag</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>covid19</td>\n",
       "      <td>21447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>coronavirus</td>\n",
       "      <td>9986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>covid_19</td>\n",
       "      <td>3157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lockdown</td>\n",
       "      <td>2951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>stayhome</td>\n",
       "      <td>2793</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       hashtag  tweets\n",
       "0      covid19   21447\n",
       "1  coronavirus    9986\n",
       "2     covid_19    3157\n",
       "3     lockdown    2951\n",
       "4     stayhome    2793"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hashtags.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4588629d",
   "metadata": {},
   "source": [
    "**URLs** are case-sensitive. Hence, set `to_lower_case=False` in step 2. To create the tables related to **TLDs**, postpone the step 5:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "16c22dd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets['urls'] = tweets['urls'].apply(to_list, pat=':-:') # Step 1\n",
    "tweets_urls = create_relationship_table(entity='url', to_lower_case=False) # Step 2\n",
    "del tweets['urls'] # Step 3\n",
    "urls = create_entity_table(relationship_table=tweets_urls, entity='url') # Step 4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "432675f8",
   "metadata": {},
   "source": [
    "At this point, `tweets_urls` has all the information to create the TLD-related tables. Create the ``tweets_tlds`` as a copy of `tweets_urls` and extract the TLDs (pseudo step 2):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "d15c36f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_tlds = tweets_urls.copy()\n",
    "tweets_tlds['tld'] = tweets_tlds['url'].str[8:].str.split(pat='/').str[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b0b0a8d",
   "metadata": {},
   "source": [
    "Now, finish step 5 for URLs..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "6884056f",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets_urls = add_index(source=urls, target=tweets_urls, entity='url') # Step 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51b4398f",
   "metadata": {},
   "source": [
    "and steps 4 and 5 for TLDs (step 3 is not necessary since no such column ever existed):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "db434588",
   "metadata": {},
   "outputs": [],
   "source": [
    "tlds = create_entity_table(relationship_table=tweets_tlds, entity='tld') # Step 4\n",
    "tweets_tlds = add_index(source=tlds, target=tweets_tlds, entity='tld') # Step 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "779ff51e",
   "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>url</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.twittascope.com/?sign=5</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://api.whatsapp.com/send?phone=9190393567...</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://redcross.give.asia/campaign/essentials...</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.twittascope.com/?sign=7</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://rebrand.ly/work-2020</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 url  tweets\n",
       "0                https://www.twittascope.com/?sign=5     136\n",
       "1  https://api.whatsapp.com/send?phone=9190393567...      82\n",
       "2  https://redcross.give.asia/campaign/essentials...      76\n",
       "3                https://www.twittascope.com/?sign=7      69\n",
       "4                        http://rebrand.ly/work-2020      62"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "urls.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "f99af14f",
   "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>tld</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>twitter.com</td>\n",
       "      <td>8930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>www.youtube.com</td>\n",
       "      <td>6529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>www.instagram.com</td>\n",
       "      <td>3020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>www.theguardian.com</td>\n",
       "      <td>2206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>www.nytimes.com</td>\n",
       "      <td>1767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   tld  tweets\n",
       "0          twitter.com    8930\n",
       "1      www.youtube.com    6529\n",
       "2    www.instagram.com    3020\n",
       "3  www.theguardian.com    2206\n",
       "4      www.nytimes.com    1767"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tlds.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72e1eba4",
   "metadata": {},
   "source": [
    "As expected, the detail of URLs hides which TLDs are most popular.\n",
    "\n",
    "#### Sentiment data\n",
    "\n",
    "Positive and negative sentiment scores are stored as a string in `tweets['sentiments']`. To construct the desired three columns from it, we only have to split and expand the string (line 1), and transform the scores into integers (lines 2–3), create the average score (line 4), ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "8f299a43",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0  1    2\n",
       "0  2 -1  0.5\n",
       "1  2 -1  0.5\n",
       "2  2 -1  0.5\n",
       "3  1 -1  0.0\n",
       "4  1 -2 -0.5"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_sentiments = tweets['sentiments'].str.split(pat=' ', expand=True)\n",
    "tweets_sentiments[0] = tweets_sentiments[0].astype('int')\n",
    "tweets_sentiments[1] = tweets_sentiments[1].astype('int')\n",
    "tweets_sentiments[2] = tweets_sentiments[[0, 1]].mean(axis=1)\n",
    "tweets_sentiments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39308999",
   "metadata": {},
   "source": [
    "and append these columns to the `tweets` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "4b617724",
   "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>user_idx</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sentiment_pos</th>\n",
       "      <th>sentiment_neg</th>\n",
       "      <th>sentiment_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1256010446827433984</td>\n",
       "      <td>80519</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>7449</td>\n",
       "      <td>636</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1256010446559039488</td>\n",
       "      <td>51478</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>85194</td>\n",
       "      <td>1496</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1256010445908881410</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>88212</td>\n",
       "      <td>635</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1257097608738140161</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-04 00:00:00</td>\n",
       "      <td>88395</td>\n",
       "      <td>635</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1259256865126600705</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-09 23:00:07</td>\n",
       "      <td>88644</td>\n",
       "      <td>634</td>\n",
       "      <td>65</td>\n",
       "      <td>201</td>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id  user_idx           timestamp  followers  friends  \\\n",
       "0  1256010446827433984     80519 2020-05-01 00:00:00       7449      636   \n",
       "1  1256010446559039488     51478 2020-05-01 00:00:00      85194     1496   \n",
       "2  1256010445908881410      4418 2020-05-01 00:00:00      88212      635   \n",
       "3  1257097608738140161      4418 2020-05-04 00:00:00      88395      635   \n",
       "4  1259256865126600705      4418 2020-05-09 23:00:07      88644      634   \n",
       "\n",
       "   retweets  favorites  sentiment_pos  sentiment_neg  sentiment_avg  \n",
       "0         3          2              2             -1            0.5  \n",
       "1         0          0              2             -1            0.5  \n",
       "2        13         43              2             -1            0.5  \n",
       "3         2          8              1             -1            0.0  \n",
       "4        65        201              1             -2           -0.5  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets[['sentiment_pos', 'sentiment_neg', 'sentiment_avg']] = tweets_sentiments\n",
    "del tweets['sentiments']\n",
    "tweets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "958f3dc1",
   "metadata": {},
   "source": [
    "As the data is structured now, it is not fully normalized because the `tweets` table is still wide regarding sentiment scores. In a way, it is like the `bad_table` in the toy example above, which did not allow us to compute an average easily. It is worth discussing whether or not it is necessary to fully normalize the data and store the three sentiment scores in a relationship table and the sentiment category in yet another table.For most tasks, working with the three columns in the `tweets` table will certainly be easier. However, as we will see in the following subsection, it is beneficial to normalize our data all the way through. With `tweets_sentiments`, we already have the first step for the relationship table. Use the [`melt()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.melt.html) function, which makes this wide subtable long ([`ignore_index=False`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.melt.html) keeps the tweet index as a column). With the few steps in the following cell, we have already completed steps 2, 3, and 5 from the pipeline:\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "e455e7bb",
   "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_idx</th>\n",
       "      <th>sentiment_idx</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tweet_idx  sentiment_idx  score\n",
       "0          0              0    2.0\n",
       "1          1              0    2.0\n",
       "2          2              0    2.0\n",
       "3          3              0    1.0\n",
       "4          4              0    1.0"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_sentiments = pd.melt(tweets_sentiments, ignore_index=False).reset_index()\n",
    "tweets_sentiments.columns = ['tweet_idx', 'sentiment_idx', 'score']\n",
    "tweets_sentiments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c39958df",
   "metadata": {},
   "source": [
    "The only thing left to do is to create the `sentiments` table, which contains the labels for the 'sentiment_idx' in `tweets_sentiments`. We have to make this manually as we have not introduced these labels yet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "1cf1325f",
   "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>sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>average</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  sentiment\n",
       "0  positive\n",
       "1  negative\n",
       "2   average"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentiments = pd.DataFrame(data=['positive', 'negative', 'average'], columns=['sentiment'])\n",
    "sentiments"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "912b73de",
   "metadata": {},
   "source": [
    "## A2.3. Saving the tables to file(s)\n",
    "\n",
    "#### Multiple TSV files\n",
    "\n",
    "Now that we have a set of linked tables, how do we store them? A simple way is to save each table to a file with tab-separated values (TSV) using [`to_csv(sep='\\t')`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html). To save space, we create compressed files (`compression='gzip'`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "eb0d1550",
   "metadata": {},
   "outputs": [],
   "source": [
    "directory = 'results/TweetsCOV19_tables'\n",
    "if not os.path.exists(directory):\n",
    "    os.makedirs(directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "45b296e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "tweets.to_csv(path_or_buf='results/TweetsCOV19_tables/tweets.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "users.to_csv(path_or_buf='results/TweetsCOV19_tables/users.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "tweets_named_entities.to_csv(path_or_buf='results/TweetsCOV19_tables/tweets_named_entities.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "tweets_mentions.to_csv(path_or_buf='results/TweetsCOV19_tables/tweets_mentions.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "tweets_hashtags.to_csv(path_or_buf='results/TweetsCOV19_tables/tweets_hashtags.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "tweets_urls.to_csv(path_or_buf='results/TweetsCOV19_tables/tweets_urls.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "tweets_tlds.to_csv(path_or_buf='results/TweetsCOV19_tables/tweets_tlds.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "tweets_sentiments.to_csv(path_or_buf='results/TweetsCOV19_tables/tweets_sentiments.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "named_entities.to_csv(path_or_buf='results/TweetsCOV19_tables/named_entities.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "mentions.to_csv(path_or_buf='results/TweetsCOV19_tables/mentions.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "hashtags.to_csv(path_or_buf='results/TweetsCOV19_tables/hashtags.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "urls.to_csv(path_or_buf='results/TweetsCOV19_tables/urls.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "tlds.to_csv(path_or_buf='results/TweetsCOV19_tables/tlds.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')\n",
    "sentiments.to_csv(path_or_buf='results/TweetsCOV19_tables/sentiments.tsv.gz', sep='\\t', index=False, encoding='utf-8', compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d741121",
   "metadata": {},
   "source": [
    "Note that, in the above cell, we set `index=False`. That means the index is not written to file.\n",
    "This is not a problem because it is a row counter starting with 0 in any table. Such an index is automatically created if `index_col=None` when reading these files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "c2d1a584",
   "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>user_idx</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sentiment_pos</th>\n",
       "      <th>sentiment_neg</th>\n",
       "      <th>sentiment_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1256010446827433984</td>\n",
       "      <td>80519</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>7449</td>\n",
       "      <td>636</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1256010446559039488</td>\n",
       "      <td>51478</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>85194</td>\n",
       "      <td>1496</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1256010445908881410</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>88212</td>\n",
       "      <td>635</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1257097608738140161</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-04 00:00:00</td>\n",
       "      <td>88395</td>\n",
       "      <td>635</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1259256865126600705</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-09 23:00:07</td>\n",
       "      <td>88644</td>\n",
       "      <td>634</td>\n",
       "      <td>65</td>\n",
       "      <td>201</td>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>-0.5</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>479220</th>\n",
       "      <td>1267214187530518530</td>\n",
       "      <td>260207</td>\n",
       "      <td>2020-05-31 21:59:40</td>\n",
       "      <td>135</td>\n",
       "      <td>159</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479221</th>\n",
       "      <td>1267214200100859904</td>\n",
       "      <td>106346</td>\n",
       "      <td>2020-05-31 21:59:43</td>\n",
       "      <td>3134</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479222</th>\n",
       "      <td>1267214225270685696</td>\n",
       "      <td>265518</td>\n",
       "      <td>2020-05-31 21:59:49</td>\n",
       "      <td>118</td>\n",
       "      <td>419</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>-4</td>\n",
       "      <td>-1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479223</th>\n",
       "      <td>1267214225283231744</td>\n",
       "      <td>255871</td>\n",
       "      <td>2020-05-31 21:59:49</td>\n",
       "      <td>149</td>\n",
       "      <td>341</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>-3</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479224</th>\n",
       "      <td>1267214242060500992</td>\n",
       "      <td>164068</td>\n",
       "      <td>2020-05-31 21:59:53</td>\n",
       "      <td>892</td>\n",
       "      <td>1080</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>479225 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   tweet_id  user_idx            timestamp  followers  \\\n",
       "0       1256010446827433984     80519  2020-05-01 00:00:00       7449   \n",
       "1       1256010446559039488     51478  2020-05-01 00:00:00      85194   \n",
       "2       1256010445908881410      4418  2020-05-01 00:00:00      88212   \n",
       "3       1257097608738140161      4418  2020-05-04 00:00:00      88395   \n",
       "4       1259256865126600705      4418  2020-05-09 23:00:07      88644   \n",
       "...                     ...       ...                  ...        ...   \n",
       "479220  1267214187530518530    260207  2020-05-31 21:59:40        135   \n",
       "479221  1267214200100859904    106346  2020-05-31 21:59:43       3134   \n",
       "479222  1267214225270685696    265518  2020-05-31 21:59:49        118   \n",
       "479223  1267214225283231744    255871  2020-05-31 21:59:49        149   \n",
       "479224  1267214242060500992    164068  2020-05-31 21:59:53        892   \n",
       "\n",
       "        friends  retweets  favorites  sentiment_pos  sentiment_neg  \\\n",
       "0           636         3          2              2             -1   \n",
       "1          1496         0          0              2             -1   \n",
       "2           635        13         43              2             -1   \n",
       "3           635         2          8              1             -1   \n",
       "4           634        65        201              1             -2   \n",
       "...         ...       ...        ...            ...            ...   \n",
       "479220      159         0          0              2             -1   \n",
       "479221     2020         0          0              1             -1   \n",
       "479222      419         0          0              1             -4   \n",
       "479223      341         0          0              2             -3   \n",
       "479224     1080         0          0              2             -1   \n",
       "\n",
       "        sentiment_avg  \n",
       "0                 0.5  \n",
       "1                 0.5  \n",
       "2                 0.5  \n",
       "3                 0.0  \n",
       "4                -0.5  \n",
       "...               ...  \n",
       "479220            0.5  \n",
       "479221            0.0  \n",
       "479222           -1.5  \n",
       "479223           -0.5  \n",
       "479224            0.5  \n",
       "\n",
       "[479225 rows x 10 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\n",
    "    filepath_or_buffer = 'results/TweetsCOV19_tables/tweets.tsv.gz', \n",
    "    sep = '\\t', \n",
    "    index_col = None, \n",
    "    encoding = 'utf-8'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e1e55cc",
   "metadata": {},
   "source": [
    "#### SQL\n",
    "\n",
    "One benefit of a relational database is that it can be stored in one file. Hence, another possibility is to take the next step on the relational database path and store all tables in an SQL database. [SQLAlchemy](https://www.sqlalchemy.org/) has emerged as the standard Python package for working with databases in SQL-like ways. To store the twelve tables from *figure 3*, all you need to do before exporting the tables is to create a so-called \"engine\" that manages connections through which you create, modify, and query the database. In this case, we create an SQLite database (other SQL dialects are possible):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "7d9e2170",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.4.39'"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlalchemy\n",
    "sqlalchemy.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "9b657382",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sqlalchemy.create_engine(url='sqlite:///results/TweetsCOV19.sql')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dec0572",
   "metadata": {},
   "source": [
    "Use the [`to_sql()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html) method, configured to use the engine just created, to add the tables to the database (the following lines just add the first five rows of each table; to add all rows remove `.head()`; a table is replaced if it already exists):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "96454b26",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets.head().to_sql(name='tweets', con=engine, if_exists='replace', index=False)\n",
    "users.head().to_sql(name='users', con=engine, if_exists='replace', index=False)\n",
    "tweets_named_entities.head().to_sql(name='tweets_named_entities', con=engine, if_exists='replace', index=False)\n",
    "tweets_mentions.head().to_sql(name='tweets_mentions', con=engine, if_exists='replace', index=False)\n",
    "tweets_hashtags.head().to_sql(name='tweets_hashtags', con=engine, if_exists='replace', index=False)\n",
    "tweets_urls.head().to_sql(name='tweets_urls', con=engine, if_exists='replace', index=False)\n",
    "tweets_tlds.head().to_sql(name='tweets_tlds', con=engine, if_exists='replace', index=False)\n",
    "tweets_sentiments.head().to_sql(name='tweets_sentiments', con=engine, if_exists='replace', index=False)\n",
    "named_entities.head().to_sql(name='named_entities', con=engine, if_exists='replace', index=False)\n",
    "mentions.head().to_sql(name='mentions', con=engine, if_exists='replace', index=False)\n",
    "hashtags.head().to_sql(name='hashtags', con=engine, if_exists='replace', index=False)\n",
    "urls.head().to_sql(name='urls', con=engine, if_exists='replace', index=False)\n",
    "tlds.head().to_sql(name='tlds', con=engine, if_exists='replace', index=False)\n",
    "sentiments.head().to_sql(name='sentiments', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdb4f921",
   "metadata": {},
   "source": [
    "To read a dataframe from the database, also use the engine, but now in the [`read_sql()`](https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html) method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "16afcf90",
   "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>user_idx</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sentiment_pos</th>\n",
       "      <th>sentiment_neg</th>\n",
       "      <th>sentiment_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1256010446827433984</td>\n",
       "      <td>80519</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>7449</td>\n",
       "      <td>636</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1256010446559039488</td>\n",
       "      <td>51478</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>85194</td>\n",
       "      <td>1496</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1256010445908881410</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>88212</td>\n",
       "      <td>635</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1257097608738140161</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-04 00:00:00</td>\n",
       "      <td>88395</td>\n",
       "      <td>635</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1259256865126600705</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-09 23:00:07</td>\n",
       "      <td>88644</td>\n",
       "      <td>634</td>\n",
       "      <td>65</td>\n",
       "      <td>201</td>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id  user_idx           timestamp  followers  friends  \\\n",
       "0  1256010446827433984     80519 2020-05-01 00:00:00       7449      636   \n",
       "1  1256010446559039488     51478 2020-05-01 00:00:00      85194     1496   \n",
       "2  1256010445908881410      4418 2020-05-01 00:00:00      88212      635   \n",
       "3  1257097608738140161      4418 2020-05-04 00:00:00      88395      635   \n",
       "4  1259256865126600705      4418 2020-05-09 23:00:07      88644      634   \n",
       "\n",
       "   retweets  favorites  sentiment_pos  sentiment_neg  sentiment_avg  \n",
       "0         3          2              2             -1            0.5  \n",
       "1         0          0              2             -1            0.5  \n",
       "2        13         43              2             -1            0.5  \n",
       "3         2          8              1             -1            0.0  \n",
       "4        65        201              1             -2           -0.5  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(sql='tweets', con=engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e24570d",
   "metadata": {},
   "source": [
    "#### Excel\n",
    "\n",
    "It is also possible to store all tables as sheets in an Excel file. However, the limit is one million rows and columns per sheet, so Excel is not an option for the full dataset. But this is how the tables can be exported in principle:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "816b3d8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(path='results/TweetsCOV19.xlsx', engine='xlsxwriter') as writer:\n",
    "    tweets.head().to_excel(writer, sheet_name='tweets')\n",
    "    users.head().to_excel(writer, sheet_name='users')\n",
    "    tweets_named_entities.head().to_excel(writer, sheet_name='tweets_named_entities')\n",
    "    tweets_mentions.head().to_excel(writer, sheet_name='tweets_mentions')\n",
    "    tweets_hashtags.head().to_excel(writer, sheet_name='tweets_hashtags')\n",
    "    tweets_urls.head().to_excel(writer, sheet_name='tweets_urls')\n",
    "    tweets_tlds.head().to_excel(writer, sheet_name='tweets_tlds')\n",
    "    named_entities.head().to_excel(writer, sheet_name='named_entities')\n",
    "    mentions.head().to_excel(writer, sheet_name='mentions')\n",
    "    hashtags.head().to_excel(writer, sheet_name='hashtags')\n",
    "    urls.head().to_excel(writer, sheet_name='urls')\n",
    "    tlds.head().to_excel(writer, sheet_name='tlds')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6ea2ca7",
   "metadata": {},
   "source": [
    "To read a sheet from that file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "2da2fdb4",
   "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>user_idx</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sentiment_pos</th>\n",
       "      <th>sentiment_neg</th>\n",
       "      <th>sentiment_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1256010446827433984</td>\n",
       "      <td>80519</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>7449</td>\n",
       "      <td>636</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1256010446559038976</td>\n",
       "      <td>51478</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>85194</td>\n",
       "      <td>1496</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1256010445908880896</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-01 00:00:00</td>\n",
       "      <td>88212</td>\n",
       "      <td>635</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1257097608738139904</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-04 00:00:00</td>\n",
       "      <td>88395</td>\n",
       "      <td>635</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1259256865126600960</td>\n",
       "      <td>4418</td>\n",
       "      <td>2020-05-09 23:00:07</td>\n",
       "      <td>88644</td>\n",
       "      <td>634</td>\n",
       "      <td>65</td>\n",
       "      <td>201</td>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id  user_idx           timestamp  followers  friends  \\\n",
       "0  1256010446827433984     80519 2020-05-01 00:00:00       7449      636   \n",
       "1  1256010446559038976     51478 2020-05-01 00:00:00      85194     1496   \n",
       "2  1256010445908880896      4418 2020-05-01 00:00:00      88212      635   \n",
       "3  1257097608738139904      4418 2020-05-04 00:00:00      88395      635   \n",
       "4  1259256865126600960      4418 2020-05-09 23:00:07      88644      634   \n",
       "\n",
       "   retweets  favorites  sentiment_pos  sentiment_neg  sentiment_avg  \n",
       "0         3          2              2             -1            0.5  \n",
       "1         0          0              2             -1            0.5  \n",
       "2        13         43              2             -1            0.5  \n",
       "3         2          8              1             -1            0.0  \n",
       "4        65        201              1             -2           -0.5  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(io='results/TweetsCOV19.xlsx', sheet_name='tweets', index_col=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ce206ff",
   "metadata": {},
   "source": [
    "## A2.4. Querying the database just created\n",
    "\n",
    "Querying is a term from the relational database world and it means to extract information from the database. **Filtering tables** is a simple query.\n",
    "\n",
    "#### Which tweet named-entity relationships can we be confident about?\n",
    "\n",
    "Recall from the discussion above that matches below a threshold (*e.g.*, -2) may be too biased."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "8652c955",
   "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_idx</th>\n",
       "      <th>named_entity_idx</th>\n",
       "      <th>text</th>\n",
       "      <th>confidence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3890</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>77675</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>207706</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>216831</td>\n",
       "      <td>6611</td>\n",
       "      <td>sesame street</td>\n",
       "      <td>-1.0571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tweet_idx  named_entity_idx           text  confidence\n",
       "0          2              6611  sesame street     -1.0571\n",
       "1       3890              6611  sesame street     -1.0571\n",
       "2      77675              6611  sesame street     -1.0571\n",
       "3     207706              6611  sesame street     -1.0571\n",
       "4     216831              6611  sesame street     -1.0571"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_named_entities[tweets_named_entities['confidence'] >= -2.].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cf58f77",
   "metadata": {},
   "source": [
    "#### Which are the most popular tweets by unpopular users?\n",
    "\n",
    "This question is open to interpretation. If we think of popular tweets as those with many retweets and unpopular users as those with few followers, then a possible answer can be:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "5cb0e1f5",
   "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>user_idx</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sentiment_pos</th>\n",
       "      <th>sentiment_neg</th>\n",
       "      <th>sentiment_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>448246</th>\n",
       "      <td>1266127346945359884</td>\n",
       "      <td>334672</td>\n",
       "      <td>2020-05-28 22:00:57</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1448</td>\n",
       "      <td>7903</td>\n",
       "      <td>1</td>\n",
       "      <td>-5</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   tweet_id  user_idx           timestamp  followers  friends  \\\n",
       "448246  1266127346945359884    334672 2020-05-28 22:00:57          6        1   \n",
       "\n",
       "        retweets  favorites  sentiment_pos  sentiment_neg  sentiment_avg  \n",
       "448246      1448       7903              1             -5           -2.0  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets[(tweets['retweets'] >= 1000) & (tweets['followers'] <= 10)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9583b372",
   "metadata": {},
   "source": [
    "Such queries are useful to identify tweets for reading. For unsampled tweets, the first two are:\n",
    "\n",
    "- https://twitter.com/anyuser/status/1262661210647887872\n",
    "- https://twitter.com/anyuser/status/1265468721440514048\n",
    "\n",
    "The third was probably deleted by the user.\n",
    "\n",
    "Since we have eliminated redundancy (*i.e.*, split up data in multiple tables), we also need to link tables to answer certain questions. Linking tables goes by the name of **joining tables**. We will introduce the use of Pandas' [`join()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.join.html) method step by step using this question:\n",
    "\n",
    "#### Which are the most popular tweets by unproductive users?\n",
    "\n",
    "The logic of joining is to link two tables on their indices or a column that exists in both tables. If no column name is specified, Pandas joins on the index by default. You can build a query step by step. Choose a dataframe from which you want to start. In our case, this is the table of popular `tweets`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "dc4b949f",
   "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>user_idx</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sentiment_pos</th>\n",
       "      <th>sentiment_neg</th>\n",
       "      <th>sentiment_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1256734073231474690</td>\n",
       "      <td>113</td>\n",
       "      <td>2020-05-02 23:55:26</td>\n",
       "      <td>673422</td>\n",
       "      <td>2783</td>\n",
       "      <td>5853</td>\n",
       "      <td>9574</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1257852630677643267</td>\n",
       "      <td>113</td>\n",
       "      <td>2020-05-06 02:00:11</td>\n",
       "      <td>673542</td>\n",
       "      <td>2786</td>\n",
       "      <td>1123</td>\n",
       "      <td>3395</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1259825193830268928</td>\n",
       "      <td>113</td>\n",
       "      <td>2020-05-11 12:38:27</td>\n",
       "      <td>675136</td>\n",
       "      <td>2785</td>\n",
       "      <td>1646</td>\n",
       "      <td>3033</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1260036554044702721</td>\n",
       "      <td>113</td>\n",
       "      <td>2020-05-12 02:38:19</td>\n",
       "      <td>675112</td>\n",
       "      <td>2785</td>\n",
       "      <td>1065</td>\n",
       "      <td>2856</td>\n",
       "      <td>1</td>\n",
       "      <td>-3</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1261282495514935300</td>\n",
       "      <td>113</td>\n",
       "      <td>2020-05-15 13:09:15</td>\n",
       "      <td>676182</td>\n",
       "      <td>2784</td>\n",
       "      <td>7788</td>\n",
       "      <td>21508</td>\n",
       "      <td>1</td>\n",
       "      <td>-3</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               tweet_id  user_idx           timestamp  followers  friends  \\\n",
       "24  1256734073231474690       113 2020-05-02 23:55:26     673422     2783   \n",
       "33  1257852630677643267       113 2020-05-06 02:00:11     673542     2786   \n",
       "45  1259825193830268928       113 2020-05-11 12:38:27     675136     2785   \n",
       "47  1260036554044702721       113 2020-05-12 02:38:19     675112     2785   \n",
       "51  1261282495514935300       113 2020-05-15 13:09:15     676182     2784   \n",
       "\n",
       "    retweets  favorites  sentiment_pos  sentiment_neg  sentiment_avg  \n",
       "24      5853       9574              1             -1            0.0  \n",
       "33      1123       3395              1             -1            0.0  \n",
       "45      1646       3033              2             -1            0.5  \n",
       "47      1065       2856              1             -3           -1.0  \n",
       "51      7788      21508              1             -3           -1.0  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popular_tweets = tweets[tweets['retweets'] >= 1000]\n",
    "popular_tweets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b51d08b",
   "metadata": {},
   "source": [
    "The other dataframe that contains information to answer the question is the table of unproductive (*i.e.*, one tweet only) users:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "3558f28f",
   "metadata": {},
   "outputs": [],
   "source": [
    "unproductive_users = users[users['tweets'] == 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51345545",
   "metadata": {},
   "source": [
    "To be able to join these tables on an index, we have stored the 'user_idx' in the `tweets` – and, hence, `popular_tweets` – table. Set this column as the index of the `popular_tweets` table, join the `unproductive_users` table, and sort the result by the number of `retweets`. Adding `[[]]` in line 2 has the effect that no columns of the `unproductive_users` table are actually added to the result, and `how='inner'` in line 3 has the effect that not all popular tweets are shown but only those whose creators are unproductive:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "c9771881",
   "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>timestamp</th>\n",
       "      <th>followers</th>\n",
       "      <th>friends</th>\n",
       "      <th>retweets</th>\n",
       "      <th>favorites</th>\n",
       "      <th>sentiment_pos</th>\n",
       "      <th>sentiment_neg</th>\n",
       "      <th>sentiment_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>136161</th>\n",
       "      <td>1266546753182056453</td>\n",
       "      <td>2020-05-30 01:47:31</td>\n",
       "      <td>1545</td>\n",
       "      <td>874</td>\n",
       "      <td>66604</td>\n",
       "      <td>193599</td>\n",
       "      <td>3</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147295</th>\n",
       "      <td>1266494398080172032</td>\n",
       "      <td>2020-05-29 22:19:29</td>\n",
       "      <td>1236</td>\n",
       "      <td>338</td>\n",
       "      <td>56716</td>\n",
       "      <td>237580</td>\n",
       "      <td>1</td>\n",
       "      <td>-4</td>\n",
       "      <td>-1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85442</th>\n",
       "      <td>1265095165397491712</td>\n",
       "      <td>2020-05-26 01:39:26</td>\n",
       "      <td>6066</td>\n",
       "      <td>426</td>\n",
       "      <td>54193</td>\n",
       "      <td>535341</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224199</th>\n",
       "      <td>1266876587544162304</td>\n",
       "      <td>2020-05-30 23:38:10</td>\n",
       "      <td>291</td>\n",
       "      <td>96</td>\n",
       "      <td>49463</td>\n",
       "      <td>237504</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120093</th>\n",
       "      <td>1266554184607956992</td>\n",
       "      <td>2020-05-30 02:17:03</td>\n",
       "      <td>2215</td>\n",
       "      <td>667</td>\n",
       "      <td>44977</td>\n",
       "      <td>166352</td>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "      <td>-0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   tweet_id           timestamp  followers  friends  retweets  \\\n",
       "136161  1266546753182056453 2020-05-30 01:47:31       1545      874     66604   \n",
       "147295  1266494398080172032 2020-05-29 22:19:29       1236      338     56716   \n",
       "85442   1265095165397491712 2020-05-26 01:39:26       6066      426     54193   \n",
       "224199  1266876587544162304 2020-05-30 23:38:10        291       96     49463   \n",
       "120093  1266554184607956992 2020-05-30 02:17:03       2215      667     44977   \n",
       "\n",
       "        favorites  sentiment_pos  sentiment_neg  sentiment_avg  \n",
       "136161     193599              3             -1            1.0  \n",
       "147295     237580              1             -4           -1.5  \n",
       "85442      535341              1             -1            0.0  \n",
       "224199     237504              1             -1            0.0  \n",
       "120093     166352              1             -2           -0.5  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popular_tweets.set_index(keys='user_idx').join(\n",
    "    other = unproductive_users[[]], \n",
    "    how = 'inner'\n",
    ").sort_values(by='retweets', ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a810c68c",
   "metadata": {},
   "source": [
    "The above query only uses one join. Other queries can also require multiple joins:\n",
    "\n",
    "#### How often is a given hashtag used over time?\n",
    "\n",
    "In this example, the result must be constructed from two tables linked by a relationship table, so we need two joins. There are several ways to build the query. You can start with either table. Here, we start with the relationship table. First, join the `tweets` table with just the 'timestamp' column to the `tweets_hashtags` table  via the tweet index (lines 1–2). Second, join the `hashtags` table, filtered to only contain hashtags from `hashtag_list`, to the resulting table via the hashtag index (lines 3–6). Line 7 shows how the date can be extracted from the timestamp (in other words, how hours, minutes, and seconds can be removed). Line 9 aggregates the data and counts the number of tweets that use a given hashtag on a given day:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "8855e746",
   "metadata": {},
   "outputs": [],
   "source": [
    "hashtag_list = ['coronavirus', 'covid19', 'hydroxychloroquine', 'vaccine']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "7b9a4e30",
   "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>day</th>\n",
       "      <th>hashtag</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>coronavirus</td>\n",
       "      <td>440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>covid19</td>\n",
       "      <td>918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>hydroxychloroquine</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>vaccine</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-05-02</td>\n",
       "      <td>coronavirus</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          day             hashtag  tweets\n",
       "0  2020-05-01         coronavirus     440\n",
       "1  2020-05-01             covid19     918\n",
       "2  2020-05-01  hydroxychloroquine       2\n",
       "3  2020-05-01             vaccine       4\n",
       "4  2020-05-02         coronavirus     365"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "days_hashtags_long = tweets_hashtags.set_index(keys='tweet_idx').join(\n",
    "    other = tweets[['timestamp']]\n",
    ").set_index(keys='hashtag_idx').join(\n",
    "    other = hashtags[hashtags['hashtag'].isin(hashtag_list)][['hashtag']], \n",
    "    how = 'inner'\n",
    ")\n",
    "days_hashtags_long['day'] = days_hashtags_long['timestamp'].dt.date\n",
    "del days_hashtags_long['timestamp']\n",
    "days_hashtags_long = days_hashtags_long.groupby(by=['day', 'hashtag']).size().reset_index(name='tweets')\n",
    "days_hashtags_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "4a3ab827",
   "metadata": {},
   "outputs": [],
   "source": [
    "days_hashtags_long.to_csv(path_or_buf='results/days_hashtags_long.tsv', sep='\\t', index=False, encoding='utf-8')\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de6130e2",
   "metadata": {},
   "source": [
    "<div class='alert alert-block alert-warning'>\n",
    "<b>Additional resources</b>\n",
    "\n",
    "We have set up the TweetsCOV19 dataset in a relational database way and even stored it as an SQL database. It is also possible to use SQL commands to retrieve information from this dataset. Consult the [documentation](https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html) of Pandas' `read_sql()` method and the SQLAlchemy [tutorials](https://docs.sqlalchemy.org/en/14/tutorial/index.html) as entry points. See the following code cell for instant success (it requires the engine from above).\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "15e9138d",
   "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>hashtag</th>\n",
       "      <th>tweets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>covid19</td>\n",
       "      <td>21447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>coronavirus</td>\n",
       "      <td>9986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>covid_19</td>\n",
       "      <td>3157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lockdown</td>\n",
       "      <td>2951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>stayhome</td>\n",
       "      <td>2793</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       hashtag  tweets\n",
       "0      covid19   21447\n",
       "1  coronavirus    9986\n",
       "2     covid_19    3157\n",
       "3     lockdown    2951\n",
       "4     stayhome    2793"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(sql='SELECT hashtag, tweets FROM hashtags', con=engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "522958f8",
   "metadata": {},
   "source": [
    "This concludes Session A2: Data management with Pandas. Some of the tables created here will be used in later sessions. Please continue with [Session A3](3_scientific_computing_and_data_visualization.ipynb) which is about scientific computing and data visualization with NumPy, SciPy, and Seaborn."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34a71b90",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "### Recommended readings\n",
    "\n",
    "<a id='mclevey_doing_2022'></a>\n",
    "McLevey, J. (2022). *Doing Computational Social Science: A Practical Introduction*. SAGE. https://us.sagepub.com/en-us/nam/doing-computational-social-science/book266031. *A rather complete introduction to the field with well-structured and insightful chapters also on using Pandas. The [website](https://github.com/UWNETLAB/dcss_supplementary) offers the code used in the book. For this session, read **ch. 6** on processing structured data.*\n",
    "\n",
    "<a id='weidmann_data_2023'></a>\n",
    "Weidmann, N. B. (2023). *Data Management for Social Scientists: From Files to Databases*. Cambridge University Press. *A fresh account of data management and processing. The book uses R, but general insights also apply to Python. For this session, read **chs. 1, 3, and 8** on data processing, data structures, and relational databases.*\n",
    "\n",
    "### Complementary readings\n",
    "\n",
    "<a id='dimitrov_tweetscov19_2020'></a>\n",
    "Dimitrov, D., Baran, E., Fafalios, P., Yu, R., Zhu, X., Zloch, M., & Dietze, S. (2020). \"TweetsCOV19 – A knowledge base of semantically annotated tweets about the COVID-19 pandemic.\" In: *Proceedings of the 29th ACM International Conference on Information & Knowledge Management* (p. 2991–2998). https://doi.org/10.1145/3340531.3412765. *Technical account how the TweetsCOV19 dataset has been created.*\n",
    "\n",
    "<a id='fafalios_tweetskb_2018'></a>\n",
    "Fafalios, P., Iosifidis, V., Ntoutsi, E., & Dietze, S. (2018). \"TweetsKB: A public and large-scale RDF corpus of annotated tweets.\" In: *The Semantic Web* 10843 (p. 177–190). Springer, Cham. https://doi.org/10.1007/978-3-319-93417-4_12. *Technical account how the TweetsKB dataset has been created.*\n",
    "\n",
    "<a id='wikipedia_twitter_2022'></a>\n",
    "Wikipedia (2022). Twitter. https://en.wikipedia.org/wiki/Twitter. Retrieved 01.12.2022."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abbb79d6",
   "metadata": {},
   "source": [
    "<div class='alert alert-block alert-success'>\n",
    "<b>Document information</b>\n",
    "\n",
    "Contact and main author: Haiko Lietz\n",
    "\n",
    "Contributors: Pouria Mirelmi & N. Gizem Bacaksizlar Turbic\n",
    "\n",
    "Acknowledgements: Olga Zagovora\n",
    "\n",
    "Version date: 15 November 2023\n",
    "\n",
    "License: Creative Commons Attribution 4.0 International (CC BY 4.0)\n",
    "</div>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}