Data collection code.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "dbc71e5a",
   "metadata": {},
   "source": [
    "### Part 2: Data Collection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "097b52e1",
   "metadata": {},
   "source": [
    "To answer the questions of this project, we have to collect variations of information for each department. For example, we could gather information like the number of followers, types, numbers of engagements, and the sentiments of replies and mentions for each account. Using different combinations of information, we can find out whether Twitter is an adequate social media platform. The relevant code for data collection can be found in the data collection Jupyter notebook.\n",
    "\n",
    "We can collect all the information needed from Twitter itself. There are a few limitations to using Twitter API calls. For example, there are limits to the API calls we can make. I will explain more of the constraints when extracting more data. \n",
    "\n",
    "To start, we will call the list of departments at the LSE. We do this in order to search up the related accounts on Twitter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "05c09a48",
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1fdee42f",
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://info.lse.ac.uk/staff/departments-and-institutes\" #List of LSE departments and institutes\n",
    "response = requests.get(url)\n",
    "soup = BeautifulSoup(response.content, \"lxml\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "daebdba5",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_list = soup.find_all('ul')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "266cf8c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "dept = []\n",
    "\n",
    "for tag in raw_list:\n",
    "    list_el = tag.text.strip()\n",
    "    dept.append(list_el)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ed67daed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Department of Accounting\\nDepartment of Anthropology\\nData Science Institute\\nDepartment of Economics\\nDepartment of Economic History\\nEuropean Institute\\nDepartment of Finance\\nFiroz Lalji Institute for Africa\\nDepartment of Gender Studies\\nDepartment of Geography and Environment\\nDepartment of Government\\nDepartment of Health Policy\\nDepartment of International Development\\nDepartment of International History\\nInternational Inequalities Institute\\nDepartment of International Relations\\nLanguage Centre\\nLSE Law School\\nDepartment of Management\\nMarshall Institute\\nDepartment of Mathematics\\nDepartment of Media and Communications\\nDepartment of Methodology\\nDepartment of Philosophy, Logic and Scientific Method\\nDepartment of Psychological and Behavioural Science\\nSchool of Public Policy (formerly Institute of Public Affairs)\\nDepartment of Social Policy\\nDepartment of Sociology\\nDepartment of Statistics']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "depts = dept[2:]\n",
    "depts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ca1658e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Department of Accounting',\n",
       " 'Department of Anthropology',\n",
       " 'Data Science Institute',\n",
       " 'Department of Economics',\n",
       " 'Department of Economic History',\n",
       " 'European Institute',\n",
       " 'Department of Finance',\n",
       " 'Firoz Lalji Institute for Africa',\n",
       " 'Department of Gender Studies',\n",
       " 'Department of Geography and Environment',\n",
       " 'Department of Government',\n",
       " 'Department of Health Policy',\n",
       " 'Department of International Development',\n",
       " 'Department of International History',\n",
       " 'International Inequalities Institute',\n",
       " 'Department of International Relations',\n",
       " 'Language Centre',\n",
       " 'LSE Law School',\n",
       " 'Department of Management',\n",
       " 'Marshall Institute',\n",
       " 'Department of Mathematics',\n",
       " 'Department of Media and Communications',\n",
       " 'Department of Methodology',\n",
       " 'Department of Philosophy, Logic and Scientific Method',\n",
       " 'Department of Psychological and Behavioural Science',\n",
       " 'School of Public Policy (formerly Institute of Public Affairs)',\n",
       " 'Department of Social Policy',\n",
       " 'Department of Sociology',\n",
       " 'Department of Statistics']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dept_list = depts[0].split(\"\\n\")\n",
    "dept_list"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48dcb4f3",
   "metadata": {},
   "source": [
    "In this project, I have only decided to look at LSE departments. Institutes tend to have massive followings and digital presences because they're more of a community than a department. It wouldn't be a fair comparison. Therefore, we won't be looking into Institutes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0a54443d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove all elements in the list with the word Institute as we are looking only at Departments\n",
    "indices = [dept_list.index(\"Data Science Institute\"), dept_list.index(\"European Institute\"), \n",
    "           dept_list.index(\"Firoz Lalji Institute for Africa\"), dept_list.index(\"International Inequalities Institute\"),\n",
    "           dept_list.index(\"Marshall Institute\"), \n",
    "           dept_list.index(\"School of Public Policy (formerly Institute of Public Affairs)\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "05cd9ae6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Department of Accounting',\n",
       " 'Department of Anthropology',\n",
       " 'Department of Economics',\n",
       " 'Department of Economic History',\n",
       " 'Department of Finance',\n",
       " 'Department of Gender Studies',\n",
       " 'Department of Geography and Environment',\n",
       " 'Department of Government',\n",
       " 'Department of Health Policy',\n",
       " 'Department of International Development',\n",
       " 'Department of International History',\n",
       " 'Department of International Relations',\n",
       " 'Language Centre',\n",
       " 'LSE Law School',\n",
       " 'Department of Management',\n",
       " 'Department of Mathematics',\n",
       " 'Department of Media and Communications',\n",
       " 'Department of Methodology',\n",
       " 'Department of Philosophy, Logic and Scientific Method',\n",
       " 'Department of Psychological and Behavioural Science',\n",
       " 'Department of Social Policy',\n",
       " 'Department of Sociology',\n",
       " 'Department of Statistics']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dept_list = [i for j, i in enumerate(dept_list) if j not in indices]\n",
    "dept_list #Final list of departments"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cb466b6",
   "metadata": {},
   "source": [
    "Based on the department list above, I've collected the list of Twitter usernames for each department manually because there isn't a compiled list of the department's Twitter handles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6a1a2dab",
   "metadata": {},
   "outputs": [],
   "source": [
    "usernames = ['LSE_Accounting', 'LSEAnthropology', 'LSEEcon',\n",
    "            'LSEEcHist', 'LSEfinance', 'LSEGenderTweet', 'LSEGeography',\n",
    "            'LSEGovernment', 'LSEHealthPolicy', 'LSE_ID', 'lsehistory',\n",
    "            'LSEIRDept', 'lselangcentre', 'LSELaw', 'LSEManagement', 'LSEMaths',\n",
    "            'MediaLSE', 'MethodologyLSE', 'LSEPhilosophy', 'LSEBehavioural', \n",
    "            'LSESocialPolicy', 'LSEsociology', 'LSEStatistics']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "61f2a6b4",
   "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>Department</th>\n",
       "      <th>Twitter username</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>LSE_Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anthropology</td>\n",
       "      <td>LSEAnthropology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Economics</td>\n",
       "      <td>LSEEcon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Economic History</td>\n",
       "      <td>LSEEcHist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Finance</td>\n",
       "      <td>LSEfinance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gender Studies</td>\n",
       "      <td>LSEGenderTweet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Geography and Environment</td>\n",
       "      <td>LSEGeography</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Government</td>\n",
       "      <td>LSEGovernment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Health Policy</td>\n",
       "      <td>LSEHealthPolicy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>International Development</td>\n",
       "      <td>LSE_ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>International History</td>\n",
       "      <td>lsehistory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>International Relations</td>\n",
       "      <td>LSEIRDept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Language Centre</td>\n",
       "      <td>lselangcentre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>LSE Law School</td>\n",
       "      <td>LSELaw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Management</td>\n",
       "      <td>LSEManagement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Mathematics</td>\n",
       "      <td>LSEMaths</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Media and Communications</td>\n",
       "      <td>MediaLSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Methodology</td>\n",
       "      <td>MethodologyLSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Philosophy, Logic and Scientific Method</td>\n",
       "      <td>LSEPhilosophy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Psychological and Behavioural Science</td>\n",
       "      <td>LSEBehavioural</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Social Policy</td>\n",
       "      <td>LSESocialPolicy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Sociology</td>\n",
       "      <td>LSEsociology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Statistics</td>\n",
       "      <td>LSEStatistics</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Department Twitter username\n",
       "0                                Accounting   LSE_Accounting\n",
       "1                              Anthropology  LSEAnthropology\n",
       "2                                 Economics          LSEEcon\n",
       "3                          Economic History        LSEEcHist\n",
       "4                                   Finance       LSEfinance\n",
       "5                            Gender Studies   LSEGenderTweet\n",
       "6                 Geography and Environment     LSEGeography\n",
       "7                                Government    LSEGovernment\n",
       "8                             Health Policy  LSEHealthPolicy\n",
       "9                 International Development           LSE_ID\n",
       "10                    International History       lsehistory\n",
       "11                  International Relations        LSEIRDept\n",
       "12                          Language Centre    lselangcentre\n",
       "13                           LSE Law School           LSELaw\n",
       "14                               Management    LSEManagement\n",
       "15                              Mathematics         LSEMaths\n",
       "16                 Media and Communications         MediaLSE\n",
       "17                              Methodology   MethodologyLSE\n",
       "18  Philosophy, Logic and Scientific Method    LSEPhilosophy\n",
       "19    Psychological and Behavioural Science   LSEBehavioural\n",
       "20                            Social Policy  LSESocialPolicy\n",
       "21                                Sociology     LSEsociology\n",
       "22                               Statistics    LSEStatistics"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "dept_usernames = pd.DataFrame({\n",
    "    'Department': dept_list,\n",
    "    'Twitter username': usernames\n",
    "})\n",
    "\n",
    "dept_usernames['Department'] = dept_usernames['Department'].str.replace('Department of ', '')\n",
    "dept_usernames"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cc3ef2c",
   "metadata": {},
   "source": [
    "Next, we start identifying what kind of tweets we would like to look at for each department. We want to determine which department has better engagement overall and see what factors contribute to its success. The first thing we will look at is each department's follower counts. The next step is to find out the overall engagement using the metrics of likes, retweets, replies, and quotes. In the next step, we will look at comments (which may be limited). All the data analysis will only go back to the last 100 tweets posted by each account because this is the limit that Twitter allows.\n",
    "\n",
    "Because of the restrictions of the Twitter developer account, the engagement counts are public metrics. If there were no restrictions, I would add more metrics like impression count, total views, and profile views."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fcf2cfbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Twitter authentication for API calls\n",
    "with open('keys.json') as f:\n",
    "    keys = json.load(f)\n",
    "\n",
    "bearer_token = keys['twitter']['bearer_token']\n",
    "headers = {\n",
    "    'Authorization': f\"Bearer {bearer_token}\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "880132e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"data\":[{\"username\":\"LSE_Accounting\",\"public_metrics\":{\"followers_count\":2520,\"following_count\":119,\"tweet_count\":495,\"listed_count\":25},\"id\":\"4900666161\",\"name\":\"LSE Accounting\"},{\"username\":\"LSEAnthropology\",\"public_metrics\":{\"followers_count\":6687,\"following_count\":99,\"tweet_count\":918,\"listed_count\":106},\"id\":\"850888387\",\"name\":\"LSE Anthropology\"},{\"username\":\"LSEEcon\",\"public_metrics\":{\"followers_count\":35783,\"following_count\":636,\"tweet_count\":10194,\"listed_count\":0},\"id\":\"1200727465\",\"name\":\"LSE Department of Economics\"},{\"username\":\"LSEEcHist\",\"public_metrics\":{\"followers_count\":3916,\"following_count\":309,\"tweet_count\":1765,\"listed_count\":101},\"id\":\"224639696\",\"name\":\"LSE Economic History\"},{\"username\":\"LSEfinance\",\"public_metrics\":{\"followers_count\":2482,\"following_count\":179,\"tweet_count\":605,\"listed_count\":51},\"id\":\"972257048\",\"name\":\"LSE Finance\"},{\"username\":\"LSEGenderTweet\",\"public_metrics\":{\"followers_count\":19727,\"following_count\":2626,\"tweet_count\":7243,\"listed_count\":281},\"id\":\"189090262\",\"name\":\"LSE Gender\"},{\"username\":\"LSEGeography\",\"public_metrics\":{\"followers_count\":12606,\"following_count\":607,\"tweet_count\":5339,\"listed_count\":242},\"id\":\"240262055\",\"name\":\"LSE Geography & Environment\"},{\"username\":\"LSEGovernment\",\"public_metrics\":{\"followers_count\":24781,\"following_count\":845,\"tweet_count\":8673,\"listed_count\":0},\"id\":\"303823238\",\"name\":\"LSE Government\"},{\"username\":\"LSEHealthPolicy\",\"public_metrics\":{\"followers_count\":7702,\"following_count\":865,\"tweet_count\":4559,\"listed_count\":143},\"id\":\"472009727\",\"name\":\"LSE Health Policy\"},{\"username\":\"LSE_ID\",\"public_metrics\":{\"followers_count\":12198,\"following_count\":1148,\"tweet_count\":5753,\"listed_count\":201},\"id\":\"317018025\",\"name\":\"LSE International Development\"},{\"username\":\"lsehistory\",\"public_metrics\":{\"followers_count\":7128,\"following_count\":118,\"tweet_count\":7602,\"listed_count\":142},\"id\":\"253471591\",\"name\":\"LSE International History\"},{\"username\":\"LSEIRDept\",\"public_metrics\":{\"followers_count\":15395,\"following_count\":195,\"tweet_count\":3996,\"listed_count\":184},\"id\":\"237225532\",\"name\":\"LSE Intl Relations\"},{\"username\":\"lselangcentre\",\"public_metrics\":{\"followers_count\":1741,\"following_count\":90,\"tweet_count\":645,\"listed_count\":45},\"id\":\"179888345\",\"name\":\"LSE Language Centre\"},{\"username\":\"LSELaw\",\"public_metrics\":{\"followers_count\":15144,\"following_count\":485,\"tweet_count\":9755,\"listed_count\":190},\"id\":\"532172035\",\"name\":\"LSE Law School\"},{\"username\":\"LSEManagement\",\"public_metrics\":{\"followers_count\":6215,\"following_count\":705,\"tweet_count\":6293,\"listed_count\":198},\"id\":\"26465977\",\"name\":\"LSE Management\"},{\"username\":\"LSEMaths\",\"public_metrics\":{\"followers_count\":3968,\"following_count\":332,\"tweet_count\":3782,\"listed_count\":62},\"id\":\"3044880371\",\"name\":\"LSE Mathematics\"},{\"username\":\"MediaLSE\",\"public_metrics\":{\"followers_count\":23165,\"following_count\":6426,\"tweet_count\":13355,\"listed_count\":563},\"id\":\"207534677\",\"name\":\"LSE Media & Communications\"},{\"username\":\"MethodologyLSE\",\"public_metrics\":{\"followers_count\":4617,\"following_count\":512,\"tweet_count\":1757,\"listed_count\":64},\"id\":\"86921024\",\"name\":\"LSE Methodology\"},{\"username\":\"LSEPhilosophy\",\"public_metrics\":{\"followers_count\":16261,\"following_count\":487,\"tweet_count\":3505,\"listed_count\":317},\"id\":\"904251031\",\"name\":\"LSE Philosophy\"},{\"username\":\"LSEBehavioural\",\"public_metrics\":{\"followers_count\":24489,\"following_count\":55,\"tweet_count\":1249,\"listed_count\":541},\"id\":\"1965000560\",\"name\":\"LSE Behavioural\"},{\"username\":\"LSESocialPolicy\",\"public_metrics\":{\"followers_count\":8515,\"following_count\":253,\"tweet_count\":2947,\"listed_count\":105},\"id\":\"2472172578\",\"name\":\"LSE Social Policy\"},{\"username\":\"LSEsociology\",\"public_metrics\":{\"followers_count\":24407,\"following_count\":567,\"tweet_count\":6254,\"listed_count\":348},\"id\":\"1671486960\",\"name\":\"LSE Sociology\"},{\"username\":\"LSEStatistics\",\"public_metrics\":{\"followers_count\":2563,\"following_count\":803,\"tweet_count\":2720,\"listed_count\":57},\"id\":\"420282103\",\"name\":\"LSE Statistics\"}]}'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Getting information about each department's account\n",
    "r = requests.get('https://api.twitter.com/2/users/by?usernames=LSE_Accounting,LSEAnthropology,LSEEcon,LSEEcHist,LSEfinance,LSEGenderTweet,LSEGeography,LSEGovernment,LSEHealthPolicy,LSE_ID,lsehistory,LSEIRDept,lselangcentre,LSELaw,LSEManagement,LSEMaths,MediaLSE,MethodologyLSE,LSEPhilosophy,LSEBehavioural,LSESocialPolicy,LSEsociology,LSEStatistics&user.fields=public_metrics', headers=headers)\n",
    "r.text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "31d34f0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Collecting information about each department's Twitter accounts\n",
    "ids = json.loads(r.text)['data']\n",
    "\n",
    "dept_ids = []; dept_followers = []; tweet_count = []\n",
    "\n",
    "for i in range(0, len(ids)):\n",
    "    idno = ids[i]['id'] #Twitter ID for API calls later on\n",
    "    no_tweets = ids[i]['public_metrics']['tweet_count'] #Tweet count for each account\n",
    "    followers = ids[i]['public_metrics']['followers_count'] #Follow count for each count\n",
    "    dept_ids.append(idno)\n",
    "    dept_followers.append(followers)\n",
    "    tweet_count.append(no_tweets)\n",
    "\n",
    "dept_usernames['Tweet count'] = tweet_count\n",
    "dept_usernames['Twitter ID'] = dept_ids\n",
    "dept_usernames['Follower count'] = dept_followers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5eb1458c",
   "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>Department</th>\n",
       "      <th>Twitter username</th>\n",
       "      <th>Tweet count</th>\n",
       "      <th>Twitter ID</th>\n",
       "      <th>Follower count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>LSE_Accounting</td>\n",
       "      <td>495</td>\n",
       "      <td>4900666161</td>\n",
       "      <td>2520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anthropology</td>\n",
       "      <td>LSEAnthropology</td>\n",
       "      <td>918</td>\n",
       "      <td>850888387</td>\n",
       "      <td>6687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Economics</td>\n",
       "      <td>LSEEcon</td>\n",
       "      <td>10194</td>\n",
       "      <td>1200727465</td>\n",
       "      <td>35783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Economic History</td>\n",
       "      <td>LSEEcHist</td>\n",
       "      <td>1765</td>\n",
       "      <td>224639696</td>\n",
       "      <td>3916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Finance</td>\n",
       "      <td>LSEfinance</td>\n",
       "      <td>605</td>\n",
       "      <td>972257048</td>\n",
       "      <td>2482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gender Studies</td>\n",
       "      <td>LSEGenderTweet</td>\n",
       "      <td>7243</td>\n",
       "      <td>189090262</td>\n",
       "      <td>19727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Geography and Environment</td>\n",
       "      <td>LSEGeography</td>\n",
       "      <td>5339</td>\n",
       "      <td>240262055</td>\n",
       "      <td>12606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Government</td>\n",
       "      <td>LSEGovernment</td>\n",
       "      <td>8673</td>\n",
       "      <td>303823238</td>\n",
       "      <td>24781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Health Policy</td>\n",
       "      <td>LSEHealthPolicy</td>\n",
       "      <td>4559</td>\n",
       "      <td>472009727</td>\n",
       "      <td>7702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>International Development</td>\n",
       "      <td>LSE_ID</td>\n",
       "      <td>5753</td>\n",
       "      <td>317018025</td>\n",
       "      <td>12198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>International History</td>\n",
       "      <td>lsehistory</td>\n",
       "      <td>7602</td>\n",
       "      <td>253471591</td>\n",
       "      <td>7128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>International Relations</td>\n",
       "      <td>LSEIRDept</td>\n",
       "      <td>3996</td>\n",
       "      <td>237225532</td>\n",
       "      <td>15395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Language Centre</td>\n",
       "      <td>lselangcentre</td>\n",
       "      <td>645</td>\n",
       "      <td>179888345</td>\n",
       "      <td>1741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>LSE Law School</td>\n",
       "      <td>LSELaw</td>\n",
       "      <td>9755</td>\n",
       "      <td>532172035</td>\n",
       "      <td>15144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Management</td>\n",
       "      <td>LSEManagement</td>\n",
       "      <td>6293</td>\n",
       "      <td>26465977</td>\n",
       "      <td>6215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Mathematics</td>\n",
       "      <td>LSEMaths</td>\n",
       "      <td>3782</td>\n",
       "      <td>3044880371</td>\n",
       "      <td>3968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Media and Communications</td>\n",
       "      <td>MediaLSE</td>\n",
       "      <td>13355</td>\n",
       "      <td>207534677</td>\n",
       "      <td>23165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Methodology</td>\n",
       "      <td>MethodologyLSE</td>\n",
       "      <td>1757</td>\n",
       "      <td>86921024</td>\n",
       "      <td>4617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Philosophy, Logic and Scientific Method</td>\n",
       "      <td>LSEPhilosophy</td>\n",
       "      <td>3505</td>\n",
       "      <td>904251031</td>\n",
       "      <td>16261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Psychological and Behavioural Science</td>\n",
       "      <td>LSEBehavioural</td>\n",
       "      <td>1249</td>\n",
       "      <td>1965000560</td>\n",
       "      <td>24489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Social Policy</td>\n",
       "      <td>LSESocialPolicy</td>\n",
       "      <td>2947</td>\n",
       "      <td>2472172578</td>\n",
       "      <td>8515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Sociology</td>\n",
       "      <td>LSEsociology</td>\n",
       "      <td>6254</td>\n",
       "      <td>1671486960</td>\n",
       "      <td>24407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Statistics</td>\n",
       "      <td>LSEStatistics</td>\n",
       "      <td>2720</td>\n",
       "      <td>420282103</td>\n",
       "      <td>2563</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Department Twitter username  Tweet count  \\\n",
       "0                                Accounting   LSE_Accounting          495   \n",
       "1                              Anthropology  LSEAnthropology          918   \n",
       "2                                 Economics          LSEEcon        10194   \n",
       "3                          Economic History        LSEEcHist         1765   \n",
       "4                                   Finance       LSEfinance          605   \n",
       "5                            Gender Studies   LSEGenderTweet         7243   \n",
       "6                 Geography and Environment     LSEGeography         5339   \n",
       "7                                Government    LSEGovernment         8673   \n",
       "8                             Health Policy  LSEHealthPolicy         4559   \n",
       "9                 International Development           LSE_ID         5753   \n",
       "10                    International History       lsehistory         7602   \n",
       "11                  International Relations        LSEIRDept         3996   \n",
       "12                          Language Centre    lselangcentre          645   \n",
       "13                           LSE Law School           LSELaw         9755   \n",
       "14                               Management    LSEManagement         6293   \n",
       "15                              Mathematics         LSEMaths         3782   \n",
       "16                 Media and Communications         MediaLSE        13355   \n",
       "17                              Methodology   MethodologyLSE         1757   \n",
       "18  Philosophy, Logic and Scientific Method    LSEPhilosophy         3505   \n",
       "19    Psychological and Behavioural Science   LSEBehavioural         1249   \n",
       "20                            Social Policy  LSESocialPolicy         2947   \n",
       "21                                Sociology     LSEsociology         6254   \n",
       "22                               Statistics    LSEStatistics         2720   \n",
       "\n",
       "    Twitter ID  Follower count  \n",
       "0   4900666161            2520  \n",
       "1    850888387            6687  \n",
       "2   1200727465           35783  \n",
       "3    224639696            3916  \n",
       "4    972257048            2482  \n",
       "5    189090262           19727  \n",
       "6    240262055           12606  \n",
       "7    303823238           24781  \n",
       "8    472009727            7702  \n",
       "9    317018025           12198  \n",
       "10   253471591            7128  \n",
       "11   237225532           15395  \n",
       "12   179888345            1741  \n",
       "13   532172035           15144  \n",
       "14    26465977            6215  \n",
       "15  3044880371            3968  \n",
       "16   207534677           23165  \n",
       "17    86921024            4617  \n",
       "18   904251031           16261  \n",
       "19  1965000560           24489  \n",
       "20  2472172578            8515  \n",
       "21  1671486960           24407  \n",
       "22   420282103            2563  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dept_usernames"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd46d049",
   "metadata": {},
   "source": [
    "Next, I will extract the number of engagements for the last 100 tweets of every LSE department on Twitter. I've chosen to use the most recent 100 tweets because of the limits. However, if the restriction on the call limit did not exist, I would've retrieved tweets for all departments from January. Taking tweets based after a precise date would be a fair comparison."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1cd1903d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Retrieving information about the past 100 tweets for each department\n",
    "\n",
    "#Accounting department\n",
    "r = requests.get('https://api.twitter.com/2/users/4900666161/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "acc = json.loads(r.text)\n",
    "\n",
    "acc_dept_tweets = acc['data']\n",
    "acc_retweet = []; acc_reply = []; acc_like = []; acc_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(acc_dept_tweets)):\n",
    "    tweet_no.append(i) #For the purpose of plotting tweets 1-100 in the visualisation section\n",
    "    dept.append('Accounting') #Department name\n",
    "    acc_retweet.append(acc_dept_tweets[i]['public_metrics']['retweet_count']) #How many times the tweet was retweeted\n",
    "    acc_reply.append(acc_dept_tweets[i]['public_metrics']['reply_count']) #How many replies the tweet got\n",
    "    acc_like.append(acc_dept_tweets[i]['public_metrics']['like_count']) #How many likes the tweet got\n",
    "    acc_quote.append(acc_dept_tweets[i]['public_metrics']['quote_count']) #How many quotes the tweet got\n",
    "    \n",
    "acc_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': acc_retweet,\n",
    "    'Reply count': acc_reply,\n",
    "    'Like count': acc_like,\n",
    "    'Quote count': acc_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ead16a51",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Anthropology department\n",
    "r = requests.get('https://api.twitter.com/2/users/850888387/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "anth = json.loads(r.text)\n",
    "\n",
    "anth_dept_tweets = anth['data']\n",
    "anth_retweet = []; anth_reply = []; anth_like = []; anth_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(anth_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Anthropology')\n",
    "    anth_retweet.append(anth_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    anth_reply.append(anth_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    anth_like.append(anth_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    anth_quote.append(anth_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "anth_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': anth_retweet,\n",
    "    'Reply count': anth_reply,\n",
    "    'Like count': anth_like,\n",
    "    'Quote count': anth_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ff7f8f15",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Economics department\n",
    "r = requests.get('https://api.twitter.com/2/users/1200727465/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "econ = json.loads(r.text)\n",
    "\n",
    "econ_dept_tweets = econ['data']\n",
    "econ_retweet = []; econ_reply = []; econ_like = []; econ_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(econ_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Economics')\n",
    "    econ_retweet.append(econ_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    econ_reply.append(econ_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    econ_like.append(econ_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    econ_quote.append(econ_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "econ_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': econ_retweet,\n",
    "    'Reply count': econ_reply,\n",
    "    'Like count': econ_like,\n",
    "    'Quote count': econ_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e83ec0b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Economics history department\n",
    "r = requests.get('https://api.twitter.com/2/users/224639696/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "econhist = json.loads(r.text)\n",
    "\n",
    "econhist_dept_tweets = econhist['data']\n",
    "econhist_retweet = []; econhist_reply = []; econhist_like = []; econhist_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(econhist_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Economics History')\n",
    "    econhist_retweet.append(econhist_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    econhist_reply.append(econhist_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    econhist_like.append(econhist_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    econhist_quote.append(econhist_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "econhist_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': econhist_retweet,\n",
    "    'Reply count': econhist_reply,\n",
    "    'Like count': econhist_like,\n",
    "    'Quote count': econhist_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7d7239a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Finance department\n",
    "r = requests.get('https://api.twitter.com/2/users/972257048/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "finance = json.loads(r.text)\n",
    "\n",
    "finance_dept_tweets = finance['data']\n",
    "finance_retweet = []; finance_reply = []; finance_like = []; finance_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(finance_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Finance')\n",
    "    finance_retweet.append(finance_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    finance_reply.append(finance_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    finance_like.append(finance_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    finance_quote.append(finance_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "finance_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': finance_retweet,\n",
    "    'Reply count': finance_reply,\n",
    "    'Like count': finance_like,\n",
    "    'Quote count': finance_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "ad56e630",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Gender studies department\n",
    "r = requests.get('https://api.twitter.com/2/users/189090262/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "gender = json.loads(r.text)\n",
    "\n",
    "gender_dept_tweets = gender['data']\n",
    "gender_retweet = []; gender_reply = []; gender_like = []; gender_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(gender_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Gender Studies')\n",
    "    gender_retweet.append(gender_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    gender_reply.append(gender_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    gender_like.append(gender_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    gender_quote.append(gender_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "gender_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': gender_retweet,\n",
    "    'Reply count': gender_reply,\n",
    "    'Like count': gender_like,\n",
    "    'Quote count': gender_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3fec7dac",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Geography department\n",
    "r = requests.get('https://api.twitter.com/2/users/240262055/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "geo = json.loads(r.text)\n",
    "\n",
    "geo_dept_tweets = geo['data']\n",
    "geo_retweet = []; geo_reply = []; geo_like = []; geo_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(geo_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Geography and Environment')\n",
    "    geo_retweet.append(geo_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    geo_reply.append(geo_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    geo_like.append(geo_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    geo_quote.append(geo_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "geo_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': geo_retweet,\n",
    "    'Reply count': geo_reply,\n",
    "    'Like count': geo_like,\n",
    "    'Quote count': geo_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "38e7b551",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Government department\n",
    "r = requests.get('https://api.twitter.com/2/users/303823238/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "gov = json.loads(r.text)\n",
    "\n",
    "gov_dept_tweets = gov['data']\n",
    "gov_retweet = []; gov_reply = []; gov_like = []; gov_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(gov_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Government')\n",
    "    gov_retweet.append(gov_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    gov_reply.append(gov_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    gov_like.append(gov_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    gov_quote.append(gov_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "gov_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': gov_retweet,\n",
    "    'Reply count': gov_reply,\n",
    "    'Like count': gov_like,\n",
    "    'Quote count': gov_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a177dd8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Health Policy Department\n",
    "r = requests.get('https://api.twitter.com/2/users/472009727/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "hp = json.loads(r.text)\n",
    "\n",
    "hp_dept_tweets = hp['data']\n",
    "hp_retweet = []; hp_reply = []; hp_like = []; hp_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(hp_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Health Policy')\n",
    "    hp_retweet.append(hp_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    hp_reply.append(hp_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    hp_like.append(hp_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    hp_quote.append(hp_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "hp_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': hp_retweet,\n",
    "    'Reply count': hp_reply,\n",
    "    'Like count': hp_like,\n",
    "    'Quote count': hp_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f31afd9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#International Development Department\n",
    "r = requests.get('https://api.twitter.com/2/users/317018025/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "id = json.loads(r.text)\n",
    "\n",
    "id_dept_tweets = id['data']\n",
    "id_retweet = []; id_reply = []; id_like = []; id_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(id_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('International Development')\n",
    "    id_retweet.append(id_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    id_reply.append(id_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    id_like.append(id_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    id_quote.append(id_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "id_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': id_retweet,\n",
    "    'Reply count': id_reply,\n",
    "    'Like count': id_like,\n",
    "    'Quote count': id_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e8490006",
   "metadata": {},
   "outputs": [],
   "source": [
    "#International History Department\n",
    "r = requests.get('https://api.twitter.com/2/users/253471591/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "ih = json.loads(r.text)\n",
    "\n",
    "ih_dept_tweets = ih['data']\n",
    "ih_retweet = []; ih_reply = []; ih_like = []; ih_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(ih_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('International History')\n",
    "    ih_retweet.append(ih_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    ih_reply.append(ih_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    ih_like.append(ih_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    ih_quote.append(ih_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "ih_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': ih_retweet,\n",
    "    'Reply count': ih_reply,\n",
    "    'Like count': ih_like,\n",
    "    'Quote count': ih_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "7b3a0903",
   "metadata": {},
   "outputs": [],
   "source": [
    "#International Relations Department\n",
    "r = requests.get('https://api.twitter.com/2/users/237225532/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "ir = json.loads(r.text)\n",
    "\n",
    "ir_dept_tweets = ir['data']\n",
    "ir_retweet = []; ir_reply = []; ir_like = []; ir_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(ir_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('International Relations')\n",
    "    ir_retweet.append(ir_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    ir_reply.append(ir_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    ir_like.append(ir_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    ir_quote.append(ir_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "ir_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': ir_retweet,\n",
    "    'Reply count': ir_reply,\n",
    "    'Like count': ir_like,\n",
    "    'Quote count': ir_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "189b2eaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Language Centre Department\n",
    "r = requests.get('https://api.twitter.com/2/users/179888345/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "lang = json.loads(r.text)\n",
    "\n",
    "lang_dept_tweets = lang['data']\n",
    "lang_retweet = []; lang_reply = []; lang_like = []; lang_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(lang_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Language Centre')\n",
    "    lang_retweet.append(lang_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    lang_reply.append(lang_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    lang_like.append(lang_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    lang_quote.append(lang_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "lang_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': lang_retweet,\n",
    "    'Reply count': lang_reply,\n",
    "    'Like count': lang_like,\n",
    "    'Quote count': lang_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "1b6dd9df",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Law Department\n",
    "r = requests.get('https://api.twitter.com/2/users/532172035/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "law = json.loads(r.text)\n",
    "\n",
    "law_dept_tweets = law['data']\n",
    "law_retweet = []; law_reply = []; law_like = []; law_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(law_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('LSE Law School')\n",
    "    law_retweet.append(law_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    law_reply.append(law_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    law_like.append(law_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    law_quote.append(law_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "law_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': law_retweet,\n",
    "    'Reply count': law_reply,\n",
    "    'Like count': law_like,\n",
    "    'Quote count': law_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b43ecda7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Management Department\n",
    "r = requests.get('https://api.twitter.com/2/users/26465977/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "man = json.loads(r.text)\n",
    "\n",
    "man_dept_tweets = man['data']\n",
    "man_retweet = []; man_reply = []; man_like = []; man_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(man_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Management')\n",
    "    man_retweet.append(man_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    man_reply.append(man_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    man_like.append(man_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    man_quote.append(man_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "man_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': man_retweet,\n",
    "    'Reply count': man_reply,\n",
    "    'Like count': man_like,\n",
    "    'Quote count': man_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "88c46a4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Mathematics Department\n",
    "r = requests.get('https://api.twitter.com/2/users/3044880371/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "math = json.loads(r.text)\n",
    "\n",
    "math_dept_tweets = math['data']\n",
    "math_retweet = []; math_reply = []; math_like = []; math_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(math_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Mathematics')\n",
    "    math_retweet.append(math_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    math_reply.append(math_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    math_like.append(math_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    math_quote.append(math_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "math_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': math_retweet,\n",
    "    'Reply count': math_reply,\n",
    "    'Like count': math_like,\n",
    "    'Quote count': math_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "211d1fc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Media and Communications Department\n",
    "r = requests.get('https://api.twitter.com/2/users/207534677/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "mc = json.loads(r.text)\n",
    "\n",
    "mc_dept_tweets = mc['data']\n",
    "mc_retweet = []; mc_reply = []; mc_like = []; mc_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(mc_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Media and Communications')\n",
    "    mc_retweet.append(mc_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    mc_reply.append(mc_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    mc_like.append(mc_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    mc_quote.append(mc_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "mc_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': mc_retweet,\n",
    "    'Reply count': mc_reply,\n",
    "    'Like count': mc_like,\n",
    "    'Quote count': mc_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "39714a77",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Methodology Department\n",
    "r = requests.get('https://api.twitter.com/2/users/86921024/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "met = json.loads(r.text)\n",
    "\n",
    "met_dept_tweets = met['data']\n",
    "met_retweet = []; met_reply = []; met_like = []; met_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(met_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Methodology')\n",
    "    met_retweet.append(met_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    met_reply.append(met_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    met_like.append(met_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    met_quote.append(met_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "met_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': met_retweet,\n",
    "    'Reply count': met_reply,\n",
    "    'Like count': met_like,\n",
    "    'Quote count': met_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "bab8c25c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Philosophy Department\n",
    "r = requests.get('https://api.twitter.com/2/users/904251031/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "phil = json.loads(r.text)\n",
    "\n",
    "phil_dept_tweets = phil['data']\n",
    "phil_retweet = []; phil_reply = []; phil_like = []; phil_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(phil_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Philosophy, Logic and Scientific Method')\n",
    "    phil_retweet.append(phil_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    phil_reply.append(phil_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    phil_like.append(phil_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    phil_quote.append(phil_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "phil_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': phil_retweet,\n",
    "    'Reply count': phil_reply,\n",
    "    'Like count': phil_like,\n",
    "    'Quote count': phil_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "a023dc51",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Pscyhology Department\n",
    "r = requests.get('https://api.twitter.com/2/users/1965000560/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "psych = json.loads(r.text)\n",
    "\n",
    "psych_dept_tweets = psych['data']\n",
    "psych_retweet = []; psych_reply = []; psych_like = []; psych_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(psych_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Psychological and Behavioural Science')\n",
    "    psych_retweet.append(psych_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    psych_reply.append(psych_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    psych_like.append(psych_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    psych_quote.append(psych_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "psych_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': psych_retweet,\n",
    "    'Reply count': psych_reply,\n",
    "    'Like count': psych_like,\n",
    "    'Quote count': psych_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "011dbcdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Social Policy Department\n",
    "r = requests.get('https://api.twitter.com/2/users/2472172578/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "sp = json.loads(r.text)\n",
    "\n",
    "sp_dept_tweets = sp['data']\n",
    "sp_retweet = []; sp_reply = []; sp_like = []; sp_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(sp_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Social Policy')\n",
    "    sp_retweet.append(sp_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    sp_reply.append(sp_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    sp_like.append(sp_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    sp_quote.append(sp_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "sp_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': sp_retweet,\n",
    "    'Reply count': sp_reply,\n",
    "    'Like count': sp_like,\n",
    "    'Quote count': sp_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "4ff07121",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Sociology Department\n",
    "r = requests.get('https://api.twitter.com/2/users/1671486960/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "socio = json.loads(r.text)\n",
    "\n",
    "socio_dept_tweets = socio['data']\n",
    "socio_retweet = []; socio_reply = []; socio_like = []; socio_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(socio_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Sociology')\n",
    "    socio_retweet.append(socio_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    socio_reply.append(socio_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    socio_like.append(socio_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    socio_quote.append(socio_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "socio_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': socio_retweet,\n",
    "    'Reply count': socio_reply,\n",
    "    'Like count': socio_like,\n",
    "    'Quote count': socio_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "7a87e6c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Statistics Department\n",
    "r = requests.get('https://api.twitter.com/2/users/420282103/tweets?expansions=attachments.poll_ids,attachments.media_keys&tweet.fields=public_metrics&poll.fields=end_datetime&max_results=100', headers=headers)\n",
    "stats = json.loads(r.text)\n",
    "\n",
    "stats_dept_tweets = stats['data']\n",
    "stats_retweet = []; stats_reply = []; stats_like = []; stats_quote = []; dept = []; tweet_no = []\n",
    "\n",
    "for i in range(0, len(stats_dept_tweets)):\n",
    "    tweet_no.append(i)\n",
    "    dept.append('Statistics')\n",
    "    stats_retweet.append(stats_dept_tweets[i]['public_metrics']['retweet_count'])\n",
    "    stats_reply.append(stats_dept_tweets[i]['public_metrics']['reply_count'])\n",
    "    stats_like.append(stats_dept_tweets[i]['public_metrics']['like_count'])\n",
    "    stats_quote.append(stats_dept_tweets[i]['public_metrics']['quote_count'])\n",
    "    \n",
    "stats_twitter = pd.DataFrame({\n",
    "    'Tweet no': tweet_no,\n",
    "    'Department': dept,\n",
    "    'Retweet count': stats_retweet,\n",
    "    'Reply count': stats_reply,\n",
    "    'Like count': stats_like,\n",
    "    'Quote count': stats_quote\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "d64c35ff",
   "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 no</th>\n",
       "      <th>Department</th>\n",
       "      <th>Retweet count</th>\n",
       "      <th>Reply count</th>\n",
       "      <th>Like count</th>\n",
       "      <th>Quote count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>95</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>96</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>97</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>98</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>99</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2300 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Tweet no  Department  Retweet count  Reply count  Like count  Quote count\n",
       "0          0  Accounting              0            0           4            0\n",
       "1          1  Accounting              1            0           0            0\n",
       "2          2  Accounting              1            0           0            0\n",
       "3          3  Accounting              2            0           4            0\n",
       "4          4  Accounting              3            0           0            0\n",
       "..       ...         ...            ...          ...         ...          ...\n",
       "95        95  Statistics              0            0           1            0\n",
       "96        96  Statistics              0            0           0            0\n",
       "97        97  Statistics              0            0           0            0\n",
       "98        98  Statistics              0            0           0            0\n",
       "99        99  Statistics              2            0           2            0\n",
       "\n",
       "[2300 rows x 6 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweets_stats = pd.concat([acc_twitter, anth_twitter, econ_twitter, econhist_twitter,\n",
    "         finance_twitter, gender_twitter, geo_twitter, gov_twitter,\n",
    "         hp_twitter, id_twitter, ih_twitter, ir_twitter, lang_twitter,\n",
    "         law_twitter, man_twitter, math_twitter, mc_twitter, met_twitter,\n",
    "         phil_twitter, psych_twitter, sp_twitter, socio_twitter, stats_twitter])\n",
    "\n",
    "tweets_stats #Dataframe including information about individual tweets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1408cf09",
   "metadata": {},
   "source": [
    "We have collected all the information about engagements for every tweet for each department. We can sum up the result to visualise the information better. Additionally, we found the follower count of each department's social media. Now that we have both the followers and engagement totals, we can calculate the engagement ratio for each account. For engagement ratio, we can take the total engagements(retweets, replies, likes and quotes) and divide them by followers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "8d597a1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "dept_eng = tweets_stats.groupby(['Department'], as_index=False).sum()\n",
    "dept_eng['Follower count'] = dept_usernames['Follower count']\n",
    "dept_eng['Tweet count'] = dept_usernames['Tweet count']\n",
    "dept_eng['Total engagement'] = dept_eng['Retweet count']+dept_eng['Reply count']+dept_eng['Like count']+dept_eng['Quote count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e9894945",
   "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>Department</th>\n",
       "      <th>Retweet count</th>\n",
       "      <th>Reply count</th>\n",
       "      <th>Like count</th>\n",
       "      <th>Quote count</th>\n",
       "      <th>Follower count</th>\n",
       "      <th>Tweet count</th>\n",
       "      <th>Total engagement</th>\n",
       "      <th>Engagement ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>277</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "      <td>3</td>\n",
       "      <td>2520</td>\n",
       "      <td>495</td>\n",
       "      <td>380</td>\n",
       "      <td>0.150794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anthropology</td>\n",
       "      <td>586</td>\n",
       "      <td>3</td>\n",
       "      <td>306</td>\n",
       "      <td>9</td>\n",
       "      <td>6687</td>\n",
       "      <td>918</td>\n",
       "      <td>904</td>\n",
       "      <td>0.135188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Economics</td>\n",
       "      <td>417</td>\n",
       "      <td>7</td>\n",
       "      <td>297</td>\n",
       "      <td>11</td>\n",
       "      <td>35783</td>\n",
       "      <td>10194</td>\n",
       "      <td>732</td>\n",
       "      <td>0.020457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Economics History</td>\n",
       "      <td>187</td>\n",
       "      <td>54</td>\n",
       "      <td>351</td>\n",
       "      <td>17</td>\n",
       "      <td>3916</td>\n",
       "      <td>1765</td>\n",
       "      <td>609</td>\n",
       "      <td>0.155516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Finance</td>\n",
       "      <td>390</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>3</td>\n",
       "      <td>2482</td>\n",
       "      <td>605</td>\n",
       "      <td>434</td>\n",
       "      <td>0.174859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gender Studies</td>\n",
       "      <td>1128</td>\n",
       "      <td>15</td>\n",
       "      <td>398</td>\n",
       "      <td>19</td>\n",
       "      <td>19727</td>\n",
       "      <td>7243</td>\n",
       "      <td>1560</td>\n",
       "      <td>0.079079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Geography and Environment</td>\n",
       "      <td>514</td>\n",
       "      <td>12</td>\n",
       "      <td>374</td>\n",
       "      <td>19</td>\n",
       "      <td>12606</td>\n",
       "      <td>5339</td>\n",
       "      <td>919</td>\n",
       "      <td>0.072902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Government</td>\n",
       "      <td>335</td>\n",
       "      <td>5</td>\n",
       "      <td>116</td>\n",
       "      <td>4</td>\n",
       "      <td>24781</td>\n",
       "      <td>8673</td>\n",
       "      <td>460</td>\n",
       "      <td>0.018563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Health Policy</td>\n",
       "      <td>313</td>\n",
       "      <td>21</td>\n",
       "      <td>396</td>\n",
       "      <td>10</td>\n",
       "      <td>7702</td>\n",
       "      <td>4559</td>\n",
       "      <td>740</td>\n",
       "      <td>0.096079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>International Development</td>\n",
       "      <td>286</td>\n",
       "      <td>4</td>\n",
       "      <td>163</td>\n",
       "      <td>8</td>\n",
       "      <td>12198</td>\n",
       "      <td>5753</td>\n",
       "      <td>461</td>\n",
       "      <td>0.037793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>International History</td>\n",
       "      <td>148</td>\n",
       "      <td>6</td>\n",
       "      <td>120</td>\n",
       "      <td>2</td>\n",
       "      <td>7128</td>\n",
       "      <td>7602</td>\n",
       "      <td>276</td>\n",
       "      <td>0.038721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>International Relations</td>\n",
       "      <td>639</td>\n",
       "      <td>7</td>\n",
       "      <td>244</td>\n",
       "      <td>11</td>\n",
       "      <td>15395</td>\n",
       "      <td>3996</td>\n",
       "      <td>901</td>\n",
       "      <td>0.058525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>LSE Law School</td>\n",
       "      <td>484</td>\n",
       "      <td>8</td>\n",
       "      <td>172</td>\n",
       "      <td>8</td>\n",
       "      <td>1741</td>\n",
       "      <td>645</td>\n",
       "      <td>672</td>\n",
       "      <td>0.385985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Language Centre</td>\n",
       "      <td>275</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>6</td>\n",
       "      <td>15144</td>\n",
       "      <td>9755</td>\n",
       "      <td>355</td>\n",
       "      <td>0.023442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Management</td>\n",
       "      <td>64</td>\n",
       "      <td>4</td>\n",
       "      <td>98</td>\n",
       "      <td>4</td>\n",
       "      <td>6215</td>\n",
       "      <td>6293</td>\n",
       "      <td>170</td>\n",
       "      <td>0.027353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Mathematics</td>\n",
       "      <td>686</td>\n",
       "      <td>16</td>\n",
       "      <td>738</td>\n",
       "      <td>31</td>\n",
       "      <td>3968</td>\n",
       "      <td>3782</td>\n",
       "      <td>1471</td>\n",
       "      <td>0.370716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Media and Communications</td>\n",
       "      <td>557</td>\n",
       "      <td>7</td>\n",
       "      <td>230</td>\n",
       "      <td>14</td>\n",
       "      <td>23165</td>\n",
       "      <td>13355</td>\n",
       "      <td>808</td>\n",
       "      <td>0.034880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Methodology</td>\n",
       "      <td>189</td>\n",
       "      <td>14</td>\n",
       "      <td>366</td>\n",
       "      <td>21</td>\n",
       "      <td>4617</td>\n",
       "      <td>1757</td>\n",
       "      <td>590</td>\n",
       "      <td>0.127789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Philosophy, Logic and Scientific Method</td>\n",
       "      <td>309</td>\n",
       "      <td>6</td>\n",
       "      <td>408</td>\n",
       "      <td>16</td>\n",
       "      <td>16261</td>\n",
       "      <td>3505</td>\n",
       "      <td>739</td>\n",
       "      <td>0.045446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Psychological and Behavioural Science</td>\n",
       "      <td>619</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>24489</td>\n",
       "      <td>1249</td>\n",
       "      <td>625</td>\n",
       "      <td>0.025522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Social Policy</td>\n",
       "      <td>386</td>\n",
       "      <td>1</td>\n",
       "      <td>142</td>\n",
       "      <td>4</td>\n",
       "      <td>8515</td>\n",
       "      <td>2947</td>\n",
       "      <td>533</td>\n",
       "      <td>0.062595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Sociology</td>\n",
       "      <td>697</td>\n",
       "      <td>5</td>\n",
       "      <td>471</td>\n",
       "      <td>20</td>\n",
       "      <td>24407</td>\n",
       "      <td>6254</td>\n",
       "      <td>1193</td>\n",
       "      <td>0.048879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Statistics</td>\n",
       "      <td>147</td>\n",
       "      <td>4</td>\n",
       "      <td>124</td>\n",
       "      <td>6</td>\n",
       "      <td>2563</td>\n",
       "      <td>2720</td>\n",
       "      <td>281</td>\n",
       "      <td>0.109637</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Department  Retweet count  Reply count  \\\n",
       "0                                Accounting            277            1   \n",
       "1                              Anthropology            586            3   \n",
       "2                                 Economics            417            7   \n",
       "3                         Economics History            187           54   \n",
       "4                                   Finance            390            1   \n",
       "5                            Gender Studies           1128           15   \n",
       "6                 Geography and Environment            514           12   \n",
       "7                                Government            335            5   \n",
       "8                             Health Policy            313           21   \n",
       "9                 International Development            286            4   \n",
       "10                    International History            148            6   \n",
       "11                  International Relations            639            7   \n",
       "12                           LSE Law School            484            8   \n",
       "13                          Language Centre            275            0   \n",
       "14                               Management             64            4   \n",
       "15                              Mathematics            686           16   \n",
       "16                 Media and Communications            557            7   \n",
       "17                              Methodology            189           14   \n",
       "18  Philosophy, Logic and Scientific Method            309            6   \n",
       "19    Psychological and Behavioural Science            619            2   \n",
       "20                            Social Policy            386            1   \n",
       "21                                Sociology            697            5   \n",
       "22                               Statistics            147            4   \n",
       "\n",
       "    Like count  Quote count  Follower count  Tweet count  Total engagement  \\\n",
       "0           99            3            2520          495               380   \n",
       "1          306            9            6687          918               904   \n",
       "2          297           11           35783        10194               732   \n",
       "3          351           17            3916         1765               609   \n",
       "4           40            3            2482          605               434   \n",
       "5          398           19           19727         7243              1560   \n",
       "6          374           19           12606         5339               919   \n",
       "7          116            4           24781         8673               460   \n",
       "8          396           10            7702         4559               740   \n",
       "9          163            8           12198         5753               461   \n",
       "10         120            2            7128         7602               276   \n",
       "11         244           11           15395         3996               901   \n",
       "12         172            8            1741          645               672   \n",
       "13          74            6           15144         9755               355   \n",
       "14          98            4            6215         6293               170   \n",
       "15         738           31            3968         3782              1471   \n",
       "16         230           14           23165        13355               808   \n",
       "17         366           21            4617         1757               590   \n",
       "18         408           16           16261         3505               739   \n",
       "19           4            0           24489         1249               625   \n",
       "20         142            4            8515         2947               533   \n",
       "21         471           20           24407         6254              1193   \n",
       "22         124            6            2563         2720               281   \n",
       "\n",
       "    Engagement ratio  \n",
       "0           0.150794  \n",
       "1           0.135188  \n",
       "2           0.020457  \n",
       "3           0.155516  \n",
       "4           0.174859  \n",
       "5           0.079079  \n",
       "6           0.072902  \n",
       "7           0.018563  \n",
       "8           0.096079  \n",
       "9           0.037793  \n",
       "10          0.038721  \n",
       "11          0.058525  \n",
       "12          0.385985  \n",
       "13          0.023442  \n",
       "14          0.027353  \n",
       "15          0.370716  \n",
       "16          0.034880  \n",
       "17          0.127789  \n",
       "18          0.045446  \n",
       "19          0.025522  \n",
       "20          0.062595  \n",
       "21          0.048879  \n",
       "22          0.109637  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dept_eng.drop('Tweet no', axis = 1, inplace = True) \n",
    "dept_eng['Engagement ratio'] = dept_eng['Total engagement']/dept_eng['Follower count'] #Calculating engagement ratio\n",
    "dept_eng #Dataframe with aggregate information"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da43f9cd",
   "metadata": {},
   "source": [
    "The last thing we need to collect is information about the mentions of the department's accounts. After extracting this, I will also perform sentiment classification. Once the sentiment classification is complete, we can count how many mentions have positive, neutral, and negative sentiments. \n",
    "Knowing the sentiment of an account's tweets are valuable. For instance, if a department has a high engagement rate, but most of the engagements are negative, we can't necessarily conclude that using Twitter is effective for a department if all its responses are negative."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2e5bb07f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Retrieving the last 100 mentions for each department\n",
    "\n",
    "#Accounting department\n",
    "r = requests.get('https://api.twitter.com/2/users/4900666161/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "acc_m = json.loads(r.text)['data']\n",
    "\n",
    "acc_m_user = []; acc_m_content = []; dept = []\n",
    "for i in range(0, len(acc_m)):\n",
    "    dept.append('Accounting') #The department name\n",
    "    user = acc_m[i]['author_id'] #The user's Twitter ID\n",
    "    content = acc_m[i]['text'] #Content of the mention\n",
    "    acc_m_user.append(user)\n",
    "    acc_m_content.append(content)\n",
    "    \n",
    "acc_mentions = pd.DataFrame({\n",
    "    'User ID': acc_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': acc_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "9abe1608",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Anthropology department\n",
    "r = requests.get('https://api.twitter.com/2/users/850888387/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "anth_m = json.loads(r.text)['data']\n",
    "\n",
    "anth_m_user = []; anth_m_content = []; dept = []\n",
    "for i in range(0, len(anth_m)):\n",
    "    dept.append('Anthropology')\n",
    "    user = anth_m[i]['author_id']\n",
    "    content = anth_m[i]['text']\n",
    "    anth_m_user.append(user)\n",
    "    anth_m_content.append(content)\n",
    "    \n",
    "anth_mentions = pd.DataFrame({\n",
    "    'User ID': anth_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': anth_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "7ba8d05c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Economics department\n",
    "r = requests.get('https://api.twitter.com/2/users/1200727465/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "econ_m = json.loads(r.text)['data']\n",
    "\n",
    "econ_m_user = []; econ_m_content = []; dept = []\n",
    "for i in range(0, len(econ_m)):\n",
    "    dept.append('Economics')\n",
    "    user = econ_m[i]['author_id']\n",
    "    content = econ_m[i]['text']\n",
    "    econ_m_user.append(user)\n",
    "    econ_m_content.append(content)\n",
    "    \n",
    "econ_mentions = pd.DataFrame({\n",
    "    'User ID': econ_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': econ_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0fd2f95a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Economic History department\n",
    "r = requests.get('https://api.twitter.com/2/users/224639696/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "econhist_m = json.loads(r.text)['data']\n",
    "\n",
    "econhist_m_user = []; econhist_m_content = []; dept = []\n",
    "for i in range(0, len(econhist_m)):\n",
    "    dept.append('Economic History')\n",
    "    user = econhist_m[i]['author_id']\n",
    "    content = econhist_m[i]['text']\n",
    "    econhist_m_user.append(user)\n",
    "    econhist_m_content.append(content)\n",
    "    \n",
    "econhist_mentions = pd.DataFrame({\n",
    "    'User ID': econhist_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': econhist_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "8cff0b5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Finance Department\n",
    "r = requests.get('https://api.twitter.com/2/users/972257048/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "finance_m = json.loads(r.text)['data']\n",
    "\n",
    "finance_m_user = []; finance_m_content = []; dept = []\n",
    "for i in range(0, len(finance_m)):\n",
    "    dept.append('Finance')\n",
    "    user = finance_m[i]['author_id']\n",
    "    content = finance_m[i]['text']\n",
    "    finance_m_user.append(user)\n",
    "    finance_m_content.append(content)\n",
    "    \n",
    "finance_mentions = pd.DataFrame({\n",
    "    'User ID': finance_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': finance_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "a16cdfa3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Gender Studies Department\n",
    "r = requests.get('https://api.twitter.com/2/users/189090262/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "gender_m = json.loads(r.text)['data']\n",
    "\n",
    "gender_m_user = []; gender_m_content = []; dept = []\n",
    "for i in range(0, len(gender_m)):\n",
    "    dept.append('Gender Studies')\n",
    "    user = gender_m[i]['author_id']\n",
    "    content = gender_m[i]['text']\n",
    "    gender_m_user.append(user)\n",
    "    gender_m_content.append(content)\n",
    "    \n",
    "gender_mentions = pd.DataFrame({\n",
    "    'User ID': gender_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': gender_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "af93b318",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Geography department\n",
    "r = requests.get('https://api.twitter.com/2/users/240262055/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "geo_m = json.loads(r.text)['data']\n",
    "\n",
    "geo_m_user = []; geo_m_content = []; dept = []\n",
    "for i in range(0, len(geo_m)):\n",
    "    dept.append('Geography and Evironment')\n",
    "    user = geo_m[i]['author_id']\n",
    "    content = geo_m[i]['text']\n",
    "    geo_m_user.append(user)\n",
    "    geo_m_content.append(content)\n",
    "    \n",
    "geo_mentions = pd.DataFrame({\n",
    "    'User ID': geo_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': geo_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "eb1d37d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Government Department\n",
    "r = requests.get('https://api.twitter.com/2/users/303823238/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "gov_m = json.loads(r.text)['data']\n",
    "\n",
    "gov_m_user = []; gov_m_content = []; dept = []\n",
    "for i in range(0, len(gov_m)):\n",
    "    dept.append('Government')\n",
    "    user = gov_m[i]['author_id']\n",
    "    content = gov_m[i]['text']\n",
    "    gov_m_user.append(user)\n",
    "    gov_m_content.append(content)\n",
    "    \n",
    "gov_mentions = pd.DataFrame({\n",
    "    'User ID': gov_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': gov_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "55f8e411",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Health Policy Department\n",
    "r = requests.get('https://api.twitter.com/2/users/472009727/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "hp_m = json.loads(r.text)['data']\n",
    "\n",
    "hp_m_user = []; hp_m_content = []; dept = []\n",
    "for i in range(0, len(hp_m)):\n",
    "    dept.append('Health Policy')\n",
    "    user = hp_m[i]['author_id']\n",
    "    content = hp_m[i]['text']\n",
    "    hp_m_user.append(user)\n",
    "    hp_m_content.append(content)\n",
    "    \n",
    "hp_mentions = pd.DataFrame({\n",
    "    'User ID': hp_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': hp_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "903d356e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#International Development Department\n",
    "r = requests.get('https://api.twitter.com/2/users/317018025/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "ID_m = json.loads(r.text)['data']\n",
    "\n",
    "ID_m_user = []; ID_m_content = []; dept = []\n",
    "for i in range(0, len(ID_m)):\n",
    "    dept.append('International Development')\n",
    "    user = ID_m[i]['author_id']\n",
    "    content = ID_m[i]['text']\n",
    "    ID_m_user.append(user)\n",
    "    ID_m_content.append(content)\n",
    "    \n",
    "ID_mentions = pd.DataFrame({\n",
    "    'User ID': ID_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': ID_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "08f143ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "#International History department\n",
    "r = requests.get('https://api.twitter.com/2/users/253471591/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "ih_m = json.loads(r.text)['data']\n",
    "\n",
    "ih_m_user = []; ih_m_content = []; dept = []\n",
    "for i in range(0, len(ih_m)):\n",
    "    dept.append('International History')\n",
    "    user = ih_m[i]['author_id']\n",
    "    content = ih_m[i]['text']\n",
    "    ih_m_user.append(user)\n",
    "    ih_m_content.append(content)\n",
    "    \n",
    "ih_mentions = pd.DataFrame({\n",
    "    'User ID': ih_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': ih_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "3d96495c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#International Relations department\n",
    "r = requests.get('https://api.twitter.com/2/users/237225532/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "ir_m = json.loads(r.text)['data']\n",
    "\n",
    "ir_m_user = []; ir_m_content = []; dept = []\n",
    "for i in range(0, len(ir_m)):\n",
    "    dept.append('International Relations')\n",
    "    user = ir_m[i]['author_id']\n",
    "    content = ir_m[i]['text']\n",
    "    ir_m_user.append(user)\n",
    "    ir_m_content.append(content)\n",
    "    \n",
    "ir_mentions = pd.DataFrame({\n",
    "    'User ID': ir_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': ir_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "774f98c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Language Centre Department\n",
    "r = requests.get('https://api.twitter.com/2/users/179888345/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "lang_m = json.loads(r.text)['data']\n",
    "\n",
    "lang_m_user = []; lang_m_content = []; dept = []\n",
    "for i in range(0, len(lang_m)):\n",
    "    dept.append('Language Centre')\n",
    "    user = lang_m[i]['author_id']\n",
    "    content = lang_m[i]['text']\n",
    "    lang_m_user.append(user)\n",
    "    lang_m_content.append(content)\n",
    "    \n",
    "lang_mentions = pd.DataFrame({\n",
    "    'User ID': lang_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': lang_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "3bfe075e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Law Department\n",
    "r = requests.get('https://api.twitter.com/2/users/532172035/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "law_m = json.loads(r.text)['data']\n",
    "\n",
    "law_m_user = []; law_m_content = []; dept = []\n",
    "for i in range(0, len(law_m)):\n",
    "    dept.append('LSE Law School')\n",
    "    user = law_m[i]['author_id']\n",
    "    content = law_m[i]['text']\n",
    "    law_m_user.append(user)\n",
    "    law_m_content.append(content)\n",
    "    \n",
    "law_mentions = pd.DataFrame({\n",
    "    'User ID': law_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': law_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "cfe00133",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Management Department\n",
    "r = requests.get('https://api.twitter.com/2/users/26465977/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "man_m = json.loads(r.text)['data']\n",
    "\n",
    "man_m_user = []; man_m_content = []; dept = []\n",
    "for i in range(0, len(man_m)):\n",
    "    dept.append('Management')\n",
    "    user = man_m[i]['author_id']\n",
    "    content = man_m[i]['text']\n",
    "    man_m_user.append(user)\n",
    "    man_m_content.append(content)\n",
    "    \n",
    "man_mentions = pd.DataFrame({\n",
    "    'User ID': man_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': man_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "fcd882e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Mathematics Department\n",
    "r = requests.get('https://api.twitter.com/2/users/3044880371/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "math_m = json.loads(r.text)['data']\n",
    "\n",
    "math_m_user = []; math_m_content = []; dept = []\n",
    "for i in range(0, len(math_m)):\n",
    "    dept.append('Mathematics')\n",
    "    user = math_m[i]['author_id']\n",
    "    content = math_m[i]['text']\n",
    "    math_m_user.append(user)\n",
    "    math_m_content.append(content)\n",
    "    \n",
    "math_mentions = pd.DataFrame({\n",
    "    'User ID': math_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': math_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "ed2a1ef6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Media and Communications Department\n",
    "r = requests.get('https://api.twitter.com/2/users/207534677/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "mc_m = json.loads(r.text)['data']\n",
    "\n",
    "mc_m_user = []; mc_m_content = []; dept = []\n",
    "for i in range(0, len(mc_m)):\n",
    "    dept.append('Media and Communications')\n",
    "    user = mc_m[i]['author_id']\n",
    "    content = mc_m[i]['text']\n",
    "    mc_m_user.append(user)\n",
    "    mc_m_content.append(content)\n",
    "    \n",
    "mc_mentions = pd.DataFrame({\n",
    "    'User ID': mc_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': mc_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "7ec39db7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Methodology Department\n",
    "r = requests.get('https://api.twitter.com/2/users/86921024/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "met_m = json.loads(r.text)['data']\n",
    "\n",
    "met_m_user = []; met_m_content = []; dept = []\n",
    "for i in range(0, len(met_m)):\n",
    "    dept.append('Methodology')\n",
    "    user = met_m[i]['author_id']\n",
    "    content = met_m[i]['text']\n",
    "    met_m_user.append(user)\n",
    "    met_m_content.append(content)\n",
    "    \n",
    "met_mentions = pd.DataFrame({\n",
    "    'User ID': met_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': met_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "0415474a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Philosophy, Logic and Scientific Method Department\n",
    "r = requests.get('https://api.twitter.com/2/users/904251031/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "phil_m = json.loads(r.text)['data']\n",
    "\n",
    "phil_m_user = []; phil_m_content = []; dept = []\n",
    "for i in range(0, len(phil_m)):\n",
    "    dept.append('Philosophy, Logic and Scientific Method')\n",
    "    user = phil_m[i]['author_id']\n",
    "    content = phil_m[i]['text']\n",
    "    phil_m_user.append(user)\n",
    "    phil_m_content.append(content)\n",
    "    \n",
    "phil_mentions = pd.DataFrame({\n",
    "    'User ID': phil_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': phil_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "2da3e4af",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Psychological and Behavioural Science Department\n",
    "r = requests.get('https://api.twitter.com/2/users/1965000560/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "psych_m = json.loads(r.text)['data']\n",
    "\n",
    "psych_m_user = []; psych_m_content = []; dept = []\n",
    "for i in range(0, len(psych_m)):\n",
    "    dept.append('Psychological and Behavioural Science')\n",
    "    user = psych_m[i]['author_id']\n",
    "    content = psych_m[i]['text']\n",
    "    psych_m_user.append(user)\n",
    "    psych_m_content.append(content)\n",
    "    \n",
    "psych_mentions = pd.DataFrame({\n",
    "    'User ID': psych_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': psych_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "07cda6d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Social Policy Department\n",
    "r = requests.get('https://api.twitter.com/2/users/2472172578/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "sp_m = json.loads(r.text)['data']\n",
    "\n",
    "sp_m_user = []; sp_m_content = []; dept = []\n",
    "for i in range(0, len(sp_m)):\n",
    "    dept.append('Social Policy')\n",
    "    user = sp_m[i]['author_id']\n",
    "    content = sp_m[i]['text']\n",
    "    sp_m_user.append(user)\n",
    "    sp_m_content.append(content)\n",
    "    \n",
    "sp_mentions = pd.DataFrame({\n",
    "    'User ID': sp_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': sp_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "0c450d82",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Sociology Department\n",
    "r = requests.get('https://api.twitter.com/2/users/1671486960/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "socio_m = json.loads(r.text)['data']\n",
    "\n",
    "socio_m_user = []; socio_m_content = []; dept = []\n",
    "for i in range(0, len(socio_m)):\n",
    "    dept.append('Sociology')\n",
    "    user = socio_m[i]['author_id']\n",
    "    content = socio_m[i]['text']\n",
    "    socio_m_user.append(user)\n",
    "    socio_m_content.append(content)\n",
    "    \n",
    "socio_mentions = pd.DataFrame({\n",
    "    'User ID': socio_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': socio_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "58d41563",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Statistics Department\n",
    "r = requests.get('https://api.twitter.com/2/users/420282103/mentions?expansions=author_id&max_results=100', headers=headers)\n",
    "stats_m = json.loads(r.text)['data']\n",
    "\n",
    "stats_m_user = []; stats_m_content = []; dept = []\n",
    "for i in range(0, len(stats_m)):\n",
    "    dept.append('Statistics')\n",
    "    user = stats_m[i]['author_id']\n",
    "    content = stats_m[i]['text']\n",
    "    stats_m_user.append(user)\n",
    "    stats_m_content.append(content)\n",
    "    \n",
    "stats_mentions = pd.DataFrame({\n",
    "    'User ID': stats_m_user,\n",
    "    'Department': dept,\n",
    "    'Tweet content': stats_m_content\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "a325bece",
   "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 ID</th>\n",
       "      <th>Department</th>\n",
       "      <th>Tweet content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4900666161</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>@LSE_Accounting in great company. https://t.co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1898598396</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>@LSE_Accounting in good company here!\\n\\nhttps...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1898598396</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>A great opportunity for a range of #research p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1456252200825597963</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>Develop a financial and managerial accounting ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21861323</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>Join our MRes/PhD in Accounting information se...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2295</th>\n",
       "      <td>68416219</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>@FlorianFoos @LSEGovernment @LSEDataScience @M...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2296</th>\n",
       "      <td>1174697132651102208</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>@melissaleesands @LSEnews @LSEGovernment @Meth...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2297</th>\n",
       "      <td>68416219</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>Know a secondary school / high school student ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2298</th>\n",
       "      <td>740657209</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>Very excited that @LSEGovernment is launching ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2299</th>\n",
       "      <td>768457181846962176</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>🚨 Sign up for the next @LSEStatistics Data Sci...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2300 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  User ID  Department  \\\n",
       "0              4900666161  Accounting   \n",
       "1              1898598396  Accounting   \n",
       "2              1898598396  Accounting   \n",
       "3     1456252200825597963  Accounting   \n",
       "4                21861323  Accounting   \n",
       "...                   ...         ...   \n",
       "2295             68416219  Statistics   \n",
       "2296  1174697132651102208  Statistics   \n",
       "2297             68416219  Statistics   \n",
       "2298            740657209  Statistics   \n",
       "2299   768457181846962176  Statistics   \n",
       "\n",
       "                                          Tweet content  \n",
       "0     @LSE_Accounting in great company. https://t.co...  \n",
       "1     @LSE_Accounting in good company here!\\n\\nhttps...  \n",
       "2     A great opportunity for a range of #research p...  \n",
       "3     Develop a financial and managerial accounting ...  \n",
       "4     Join our MRes/PhD in Accounting information se...  \n",
       "...                                                 ...  \n",
       "2295  @FlorianFoos @LSEGovernment @LSEDataScience @M...  \n",
       "2296  @melissaleesands @LSEnews @LSEGovernment @Meth...  \n",
       "2297  Know a secondary school / high school student ...  \n",
       "2298  Very excited that @LSEGovernment is launching ...  \n",
       "2299  🚨 Sign up for the next @LSEStatistics Data Sci...  \n",
       "\n",
       "[2300 rows x 3 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mentions = pd.concat([acc_mentions, anth_mentions, econ_mentions, econhist_mentions,\n",
    "         finance_mentions, gender_mentions, geo_mentions, gov_mentions,\n",
    "         hp_mentions, ID_mentions, ih_mentions, ir_mentions, lang_mentions,\n",
    "         law_mentions, man_mentions, math_mentions, mc_mentions, met_mentions,\n",
    "         phil_mentions, psych_mentions, sp_mentions, socio_mentions, stats_mentions], ignore_index=True)\n",
    "\n",
    "mentions #Dataframe with information about all the department's mentions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26464321",
   "metadata": {},
   "source": [
    "To determine the sentiment of the mentions, I'll be using TextBlob. There are two handy tools in TextBlob: Subjectivity and polarity. Subjectivity tells us what kind of mention this tweet is. For example, the more subjective a tweet is, the more opinionated or emotion heavy it is. On the other hand, a more objective tweet contains more facts. The polarity score is more important at the moment. I'll be making use of it to classify the sentiments. Polarity operates on a scale of [-1, 1], where -1 represents a negative tweet, and 1 represents a positive tweet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "f3421308",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Sentiment analysis code using TextBlob\n",
    "from textblob import TextBlob\n",
    "\n",
    "#Subjectivity function\n",
    "def Subjectivity(tweet):\n",
    "    return TextBlob(tweet).sentiment.subjectivity\n",
    "    \n",
    "#Polarity function\n",
    "def Polarity(tweet):\n",
    "    return TextBlob(tweet).sentiment.polarity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "961db09b",
   "metadata": {},
   "outputs": [],
   "source": [
    "mentions['Subjectivity'] = mentions['Tweet content'].apply(Subjectivity) #Calculating the subjectivity score\n",
    "mentions['Polarity'] = mentions['Tweet content'].apply(Polarity) #Calculating the polarity score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "763e392a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Classifying the polarity scores \n",
    "def sentiment_analysis(score):\n",
    "    if score < 0:\n",
    "        return 'Negative'\n",
    "    elif score == 0 :\n",
    "        return 'Neutral'\n",
    "    elif score > 0:\n",
    "        return 'Positive'\n",
    "    \n",
    "mentions['Sentiment'] = mentions['Polarity'].apply(sentiment_analysis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "3b3f2781",
   "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 ID</th>\n",
       "      <th>Department</th>\n",
       "      <th>Tweet content</th>\n",
       "      <th>Subjectivity</th>\n",
       "      <th>Polarity</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4900666161</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>@LSE_Accounting in great company. https://t.co...</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>Positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1898598396</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>@LSE_Accounting in good company here!\\n\\nhttps...</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>0.875000</td>\n",
       "      <td>Positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1898598396</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>A great opportunity for a range of #research p...</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>Positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1456252200825597963</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>Develop a financial and managerial accounting ...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Neutral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21861323</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>Join our MRes/PhD in Accounting information se...</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Neutral</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2295</th>\n",
       "      <td>68416219</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>@FlorianFoos @LSEGovernment @LSEDataScience @M...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Neutral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2296</th>\n",
       "      <td>1174697132651102208</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>@melissaleesands @LSEnews @LSEGovernment @Meth...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Neutral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2297</th>\n",
       "      <td>68416219</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>Know a secondary school / high school student ...</td>\n",
       "      <td>0.448636</td>\n",
       "      <td>0.061591</td>\n",
       "      <td>Positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2298</th>\n",
       "      <td>740657209</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>Very excited that @LSEGovernment is launching ...</td>\n",
       "      <td>0.640909</td>\n",
       "      <td>0.319773</td>\n",
       "      <td>Positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2299</th>\n",
       "      <td>768457181846962176</td>\n",
       "      <td>Statistics</td>\n",
       "      <td>🚨 Sign up for the next @LSEStatistics Data Sci...</td>\n",
       "      <td>0.150000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>Positive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2300 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  User ID  Department  \\\n",
       "0              4900666161  Accounting   \n",
       "1              1898598396  Accounting   \n",
       "2              1898598396  Accounting   \n",
       "3     1456252200825597963  Accounting   \n",
       "4                21861323  Accounting   \n",
       "...                   ...         ...   \n",
       "2295             68416219  Statistics   \n",
       "2296  1174697132651102208  Statistics   \n",
       "2297             68416219  Statistics   \n",
       "2298            740657209  Statistics   \n",
       "2299   768457181846962176  Statistics   \n",
       "\n",
       "                                          Tweet content  Subjectivity  \\\n",
       "0     @LSE_Accounting in great company. https://t.co...      0.750000   \n",
       "1     @LSE_Accounting in good company here!\\n\\nhttps...      0.600000   \n",
       "2     A great opportunity for a range of #research p...      0.375000   \n",
       "3     Develop a financial and managerial accounting ...      0.000000   \n",
       "4     Join our MRes/PhD in Accounting information se...      0.400000   \n",
       "...                                                 ...           ...   \n",
       "2295  @FlorianFoos @LSEGovernment @LSEDataScience @M...      0.000000   \n",
       "2296  @melissaleesands @LSEnews @LSEGovernment @Meth...      0.000000   \n",
       "2297  Know a secondary school / high school student ...      0.448636   \n",
       "2298  Very excited that @LSEGovernment is launching ...      0.640909   \n",
       "2299  🚨 Sign up for the next @LSEStatistics Data Sci...      0.150000   \n",
       "\n",
       "      Polarity Sentiment  \n",
       "0     0.800000  Positive  \n",
       "1     0.875000  Positive  \n",
       "2     0.400000  Positive  \n",
       "3     0.000000   Neutral  \n",
       "4     0.000000   Neutral  \n",
       "...        ...       ...  \n",
       "2295  0.000000   Neutral  \n",
       "2296  0.000000   Neutral  \n",
       "2297  0.061591  Positive  \n",
       "2298  0.319773  Positive  \n",
       "2299  0.100000  Positive  \n",
       "\n",
       "[2300 rows x 6 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mentions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "cfbb4a9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating dummy variables for positive, neutral, and negative so we can count these classes\n",
    "def positive(text):\n",
    "    if text == 'Positive':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "def neutral(text):\n",
    "    if text == 'Neutral':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "def negative(text):\n",
    "    if text == 'Negative':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "mentions['Positive'] = mentions['Sentiment'].apply(positive)\n",
    "mentions['Neutral'] = mentions['Sentiment'].apply(neutral)\n",
    "mentions['Negative'] = mentions['Sentiment'].apply(negative)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "e7296c25",
   "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>Department</th>\n",
       "      <th>Subjectivity</th>\n",
       "      <th>Polarity</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Neutral</th>\n",
       "      <th>Negative</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>25.345229</td>\n",
       "      <td>11.531351</td>\n",
       "      <td>35</td>\n",
       "      <td>57</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anthropology</td>\n",
       "      <td>37.999137</td>\n",
       "      <td>19.249280</td>\n",
       "      <td>56</td>\n",
       "      <td>37</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Economic History</td>\n",
       "      <td>27.835433</td>\n",
       "      <td>16.831492</td>\n",
       "      <td>48</td>\n",
       "      <td>44</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Economics</td>\n",
       "      <td>28.546223</td>\n",
       "      <td>11.574058</td>\n",
       "      <td>55</td>\n",
       "      <td>35</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Finance</td>\n",
       "      <td>33.469503</td>\n",
       "      <td>19.475427</td>\n",
       "      <td>51</td>\n",
       "      <td>42</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gender Studies</td>\n",
       "      <td>35.360246</td>\n",
       "      <td>20.252803</td>\n",
       "      <td>51</td>\n",
       "      <td>42</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Geography and Evironment</td>\n",
       "      <td>29.212998</td>\n",
       "      <td>17.007926</td>\n",
       "      <td>54</td>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Government</td>\n",
       "      <td>30.786153</td>\n",
       "      <td>21.124610</td>\n",
       "      <td>59</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Health Policy</td>\n",
       "      <td>22.824255</td>\n",
       "      <td>20.145429</td>\n",
       "      <td>38</td>\n",
       "      <td>61</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>International Development</td>\n",
       "      <td>30.577266</td>\n",
       "      <td>10.727800</td>\n",
       "      <td>41</td>\n",
       "      <td>43</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>International History</td>\n",
       "      <td>34.606632</td>\n",
       "      <td>17.599536</td>\n",
       "      <td>52</td>\n",
       "      <td>39</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>International Relations</td>\n",
       "      <td>34.100674</td>\n",
       "      <td>31.013037</td>\n",
       "      <td>51</td>\n",
       "      <td>44</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>LSE Law School</td>\n",
       "      <td>36.387734</td>\n",
       "      <td>19.606842</td>\n",
       "      <td>58</td>\n",
       "      <td>34</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Language Centre</td>\n",
       "      <td>28.514360</td>\n",
       "      <td>16.146717</td>\n",
       "      <td>55</td>\n",
       "      <td>39</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Management</td>\n",
       "      <td>33.281780</td>\n",
       "      <td>20.526790</td>\n",
       "      <td>53</td>\n",
       "      <td>41</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Mathematics</td>\n",
       "      <td>32.978635</td>\n",
       "      <td>21.406825</td>\n",
       "      <td>53</td>\n",
       "      <td>44</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Media and Communications</td>\n",
       "      <td>30.392833</td>\n",
       "      <td>17.835118</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Methodology</td>\n",
       "      <td>39.076780</td>\n",
       "      <td>27.276825</td>\n",
       "      <td>71</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Philosophy, Logic and Scientific Method</td>\n",
       "      <td>30.634630</td>\n",
       "      <td>26.098333</td>\n",
       "      <td>69</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Psychological and Behavioural Science</td>\n",
       "      <td>30.556759</td>\n",
       "      <td>19.602161</td>\n",
       "      <td>48</td>\n",
       "      <td>47</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Social Policy</td>\n",
       "      <td>35.480266</td>\n",
       "      <td>18.441595</td>\n",
       "      <td>60</td>\n",
       "      <td>32</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Sociology</td>\n",
       "      <td>31.290635</td>\n",
       "      <td>23.023777</td>\n",
       "      <td>51</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Statistics</td>\n",
       "      <td>33.302316</td>\n",
       "      <td>17.010355</td>\n",
       "      <td>50</td>\n",
       "      <td>46</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Department  Subjectivity   Polarity  \\\n",
       "0                                Accounting     25.345229  11.531351   \n",
       "1                              Anthropology     37.999137  19.249280   \n",
       "2                          Economic History     27.835433  16.831492   \n",
       "3                                 Economics     28.546223  11.574058   \n",
       "4                                   Finance     33.469503  19.475427   \n",
       "5                            Gender Studies     35.360246  20.252803   \n",
       "6                  Geography and Evironment     29.212998  17.007926   \n",
       "7                                Government     30.786153  21.124610   \n",
       "8                             Health Policy     22.824255  20.145429   \n",
       "9                 International Development     30.577266  10.727800   \n",
       "10                    International History     34.606632  17.599536   \n",
       "11                  International Relations     34.100674  31.013037   \n",
       "12                           LSE Law School     36.387734  19.606842   \n",
       "13                          Language Centre     28.514360  16.146717   \n",
       "14                               Management     33.281780  20.526790   \n",
       "15                              Mathematics     32.978635  21.406825   \n",
       "16                 Media and Communications     30.392833  17.835118   \n",
       "17                              Methodology     39.076780  27.276825   \n",
       "18  Philosophy, Logic and Scientific Method     30.634630  26.098333   \n",
       "19    Psychological and Behavioural Science     30.556759  19.602161   \n",
       "20                            Social Policy     35.480266  18.441595   \n",
       "21                                Sociology     31.290635  23.023777   \n",
       "22                               Statistics     33.302316  17.010355   \n",
       "\n",
       "    Positive  Neutral  Negative  \n",
       "0         35       57         8  \n",
       "1         56       37         7  \n",
       "2         48       44         8  \n",
       "3         55       35        10  \n",
       "4         51       42         7  \n",
       "5         51       42         7  \n",
       "6         54       41         5  \n",
       "7         59       40         1  \n",
       "8         38       61         1  \n",
       "9         41       43        16  \n",
       "10        52       39         9  \n",
       "11        51       44         5  \n",
       "12        58       34         8  \n",
       "13        55       39         6  \n",
       "14        53       41         6  \n",
       "15        53       44         3  \n",
       "16        56       42         2  \n",
       "17        71       28         1  \n",
       "18        69       29         2  \n",
       "19        48       47         5  \n",
       "20        60       32         8  \n",
       "21        51       48         1  \n",
       "22        50       46         4  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mentions_sum = mentions.groupby(['Department'], as_index=False).sum()\n",
    "mentions_sum #Dataframe with information about the total number of positive, neutral and negative mentions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "4ed3877c",
   "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>Department</th>\n",
       "      <th>Retweet count</th>\n",
       "      <th>Reply count</th>\n",
       "      <th>Like count</th>\n",
       "      <th>Quote count</th>\n",
       "      <th>Follower count</th>\n",
       "      <th>Tweet count</th>\n",
       "      <th>Total engagement</th>\n",
       "      <th>Engagement ratio</th>\n",
       "      <th>Positive mentions</th>\n",
       "      <th>Neutral mentions</th>\n",
       "      <th>Negative mentions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>277</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "      <td>3</td>\n",
       "      <td>2520</td>\n",
       "      <td>495</td>\n",
       "      <td>380</td>\n",
       "      <td>0.150794</td>\n",
       "      <td>35</td>\n",
       "      <td>57</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anthropology</td>\n",
       "      <td>586</td>\n",
       "      <td>3</td>\n",
       "      <td>306</td>\n",
       "      <td>9</td>\n",
       "      <td>6687</td>\n",
       "      <td>918</td>\n",
       "      <td>904</td>\n",
       "      <td>0.135188</td>\n",
       "      <td>56</td>\n",
       "      <td>37</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Economics</td>\n",
       "      <td>417</td>\n",
       "      <td>7</td>\n",
       "      <td>297</td>\n",
       "      <td>11</td>\n",
       "      <td>35783</td>\n",
       "      <td>10194</td>\n",
       "      <td>732</td>\n",
       "      <td>0.020457</td>\n",
       "      <td>48</td>\n",
       "      <td>44</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Economics History</td>\n",
       "      <td>187</td>\n",
       "      <td>54</td>\n",
       "      <td>351</td>\n",
       "      <td>17</td>\n",
       "      <td>3916</td>\n",
       "      <td>1765</td>\n",
       "      <td>609</td>\n",
       "      <td>0.155516</td>\n",
       "      <td>55</td>\n",
       "      <td>35</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Finance</td>\n",
       "      <td>390</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>3</td>\n",
       "      <td>2482</td>\n",
       "      <td>605</td>\n",
       "      <td>434</td>\n",
       "      <td>0.174859</td>\n",
       "      <td>51</td>\n",
       "      <td>42</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Gender Studies</td>\n",
       "      <td>1128</td>\n",
       "      <td>15</td>\n",
       "      <td>398</td>\n",
       "      <td>19</td>\n",
       "      <td>19727</td>\n",
       "      <td>7243</td>\n",
       "      <td>1560</td>\n",
       "      <td>0.079079</td>\n",
       "      <td>51</td>\n",
       "      <td>42</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Geography and Environment</td>\n",
       "      <td>514</td>\n",
       "      <td>12</td>\n",
       "      <td>374</td>\n",
       "      <td>19</td>\n",
       "      <td>12606</td>\n",
       "      <td>5339</td>\n",
       "      <td>919</td>\n",
       "      <td>0.072902</td>\n",
       "      <td>54</td>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Government</td>\n",
       "      <td>335</td>\n",
       "      <td>5</td>\n",
       "      <td>116</td>\n",
       "      <td>4</td>\n",
       "      <td>24781</td>\n",
       "      <td>8673</td>\n",
       "      <td>460</td>\n",
       "      <td>0.018563</td>\n",
       "      <td>59</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Health Policy</td>\n",
       "      <td>313</td>\n",
       "      <td>21</td>\n",
       "      <td>396</td>\n",
       "      <td>10</td>\n",
       "      <td>7702</td>\n",
       "      <td>4559</td>\n",
       "      <td>740</td>\n",
       "      <td>0.096079</td>\n",
       "      <td>38</td>\n",
       "      <td>61</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>International Development</td>\n",
       "      <td>286</td>\n",
       "      <td>4</td>\n",
       "      <td>163</td>\n",
       "      <td>8</td>\n",
       "      <td>12198</td>\n",
       "      <td>5753</td>\n",
       "      <td>461</td>\n",
       "      <td>0.037793</td>\n",
       "      <td>41</td>\n",
       "      <td>43</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>International History</td>\n",
       "      <td>148</td>\n",
       "      <td>6</td>\n",
       "      <td>120</td>\n",
       "      <td>2</td>\n",
       "      <td>7128</td>\n",
       "      <td>7602</td>\n",
       "      <td>276</td>\n",
       "      <td>0.038721</td>\n",
       "      <td>52</td>\n",
       "      <td>39</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>International Relations</td>\n",
       "      <td>639</td>\n",
       "      <td>7</td>\n",
       "      <td>244</td>\n",
       "      <td>11</td>\n",
       "      <td>15395</td>\n",
       "      <td>3996</td>\n",
       "      <td>901</td>\n",
       "      <td>0.058525</td>\n",
       "      <td>51</td>\n",
       "      <td>44</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>LSE Law School</td>\n",
       "      <td>484</td>\n",
       "      <td>8</td>\n",
       "      <td>172</td>\n",
       "      <td>8</td>\n",
       "      <td>1741</td>\n",
       "      <td>645</td>\n",
       "      <td>672</td>\n",
       "      <td>0.385985</td>\n",
       "      <td>58</td>\n",
       "      <td>34</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Language Centre</td>\n",
       "      <td>275</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>6</td>\n",
       "      <td>15144</td>\n",
       "      <td>9755</td>\n",
       "      <td>355</td>\n",
       "      <td>0.023442</td>\n",
       "      <td>55</td>\n",
       "      <td>39</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Management</td>\n",
       "      <td>64</td>\n",
       "      <td>4</td>\n",
       "      <td>98</td>\n",
       "      <td>4</td>\n",
       "      <td>6215</td>\n",
       "      <td>6293</td>\n",
       "      <td>170</td>\n",
       "      <td>0.027353</td>\n",
       "      <td>53</td>\n",
       "      <td>41</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Mathematics</td>\n",
       "      <td>686</td>\n",
       "      <td>16</td>\n",
       "      <td>738</td>\n",
       "      <td>31</td>\n",
       "      <td>3968</td>\n",
       "      <td>3782</td>\n",
       "      <td>1471</td>\n",
       "      <td>0.370716</td>\n",
       "      <td>53</td>\n",
       "      <td>44</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Media and Communications</td>\n",
       "      <td>557</td>\n",
       "      <td>7</td>\n",
       "      <td>230</td>\n",
       "      <td>14</td>\n",
       "      <td>23165</td>\n",
       "      <td>13355</td>\n",
       "      <td>808</td>\n",
       "      <td>0.034880</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Methodology</td>\n",
       "      <td>189</td>\n",
       "      <td>14</td>\n",
       "      <td>366</td>\n",
       "      <td>21</td>\n",
       "      <td>4617</td>\n",
       "      <td>1757</td>\n",
       "      <td>590</td>\n",
       "      <td>0.127789</td>\n",
       "      <td>71</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Philosophy, Logic and Scientific Method</td>\n",
       "      <td>309</td>\n",
       "      <td>6</td>\n",
       "      <td>408</td>\n",
       "      <td>16</td>\n",
       "      <td>16261</td>\n",
       "      <td>3505</td>\n",
       "      <td>739</td>\n",
       "      <td>0.045446</td>\n",
       "      <td>69</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Psychological and Behavioural Science</td>\n",
       "      <td>619</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>24489</td>\n",
       "      <td>1249</td>\n",
       "      <td>625</td>\n",
       "      <td>0.025522</td>\n",
       "      <td>48</td>\n",
       "      <td>47</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Social Policy</td>\n",
       "      <td>386</td>\n",
       "      <td>1</td>\n",
       "      <td>142</td>\n",
       "      <td>4</td>\n",
       "      <td>8515</td>\n",
       "      <td>2947</td>\n",
       "      <td>533</td>\n",
       "      <td>0.062595</td>\n",
       "      <td>60</td>\n",
       "      <td>32</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Sociology</td>\n",
       "      <td>697</td>\n",
       "      <td>5</td>\n",
       "      <td>471</td>\n",
       "      <td>20</td>\n",
       "      <td>24407</td>\n",
       "      <td>6254</td>\n",
       "      <td>1193</td>\n",
       "      <td>0.048879</td>\n",
       "      <td>51</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Statistics</td>\n",
       "      <td>147</td>\n",
       "      <td>4</td>\n",
       "      <td>124</td>\n",
       "      <td>6</td>\n",
       "      <td>2563</td>\n",
       "      <td>2720</td>\n",
       "      <td>281</td>\n",
       "      <td>0.109637</td>\n",
       "      <td>50</td>\n",
       "      <td>46</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Department  Retweet count  Reply count  \\\n",
       "0                                Accounting            277            1   \n",
       "1                              Anthropology            586            3   \n",
       "2                                 Economics            417            7   \n",
       "3                         Economics History            187           54   \n",
       "4                                   Finance            390            1   \n",
       "5                            Gender Studies           1128           15   \n",
       "6                 Geography and Environment            514           12   \n",
       "7                                Government            335            5   \n",
       "8                             Health Policy            313           21   \n",
       "9                 International Development            286            4   \n",
       "10                    International History            148            6   \n",
       "11                  International Relations            639            7   \n",
       "12                           LSE Law School            484            8   \n",
       "13                          Language Centre            275            0   \n",
       "14                               Management             64            4   \n",
       "15                              Mathematics            686           16   \n",
       "16                 Media and Communications            557            7   \n",
       "17                              Methodology            189           14   \n",
       "18  Philosophy, Logic and Scientific Method            309            6   \n",
       "19    Psychological and Behavioural Science            619            2   \n",
       "20                            Social Policy            386            1   \n",
       "21                                Sociology            697            5   \n",
       "22                               Statistics            147            4   \n",
       "\n",
       "    Like count  Quote count  Follower count  Tweet count  Total engagement  \\\n",
       "0           99            3            2520          495               380   \n",
       "1          306            9            6687          918               904   \n",
       "2          297           11           35783        10194               732   \n",
       "3          351           17            3916         1765               609   \n",
       "4           40            3            2482          605               434   \n",
       "5          398           19           19727         7243              1560   \n",
       "6          374           19           12606         5339               919   \n",
       "7          116            4           24781         8673               460   \n",
       "8          396           10            7702         4559               740   \n",
       "9          163            8           12198         5753               461   \n",
       "10         120            2            7128         7602               276   \n",
       "11         244           11           15395         3996               901   \n",
       "12         172            8            1741          645               672   \n",
       "13          74            6           15144         9755               355   \n",
       "14          98            4            6215         6293               170   \n",
       "15         738           31            3968         3782              1471   \n",
       "16         230           14           23165        13355               808   \n",
       "17         366           21            4617         1757               590   \n",
       "18         408           16           16261         3505               739   \n",
       "19           4            0           24489         1249               625   \n",
       "20         142            4            8515         2947               533   \n",
       "21         471           20           24407         6254              1193   \n",
       "22         124            6            2563         2720               281   \n",
       "\n",
       "    Engagement ratio  Positive mentions  Neutral mentions  Negative mentions  \n",
       "0           0.150794                 35                57                  8  \n",
       "1           0.135188                 56                37                  7  \n",
       "2           0.020457                 48                44                  8  \n",
       "3           0.155516                 55                35                 10  \n",
       "4           0.174859                 51                42                  7  \n",
       "5           0.079079                 51                42                  7  \n",
       "6           0.072902                 54                41                  5  \n",
       "7           0.018563                 59                40                  1  \n",
       "8           0.096079                 38                61                  1  \n",
       "9           0.037793                 41                43                 16  \n",
       "10          0.038721                 52                39                  9  \n",
       "11          0.058525                 51                44                  5  \n",
       "12          0.385985                 58                34                  8  \n",
       "13          0.023442                 55                39                  6  \n",
       "14          0.027353                 53                41                  6  \n",
       "15          0.370716                 53                44                  3  \n",
       "16          0.034880                 56                42                  2  \n",
       "17          0.127789                 71                28                  1  \n",
       "18          0.045446                 69                29                  2  \n",
       "19          0.025522                 48                47                  5  \n",
       "20          0.062595                 60                32                  8  \n",
       "21          0.048879                 51                48                  1  \n",
       "22          0.109637                 50                46                  4  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Adding the totals from above to the main dataset dept_eng\n",
    "dept_eng['Positive mentions'] = mentions_sum['Positive']\n",
    "dept_eng['Neutral mentions'] = mentions_sum['Neutral']\n",
    "dept_eng['Negative mentions'] =  mentions_sum['Negative']\n",
    "dept_eng"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "8c8792c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Saving the datasets\n",
    "dept_eng.to_csv('data/dept_eng.csv')\n",
    "mentions.to_csv('data/mentions.csv')\n",
    "tweets_stats.to_csv('data/tweets_stats.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7357df9e",
   "metadata": {},
   "source": [
    "[This code was last run on 25th April 2022]"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}