data/GenerateDataFiles.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='top'></a>\n",
    "# Extract Data for Final Plots of the Paper\n",
    "This noteboook will create the CSV files needed to generate the plots presented in the paper.\n",
    "\n",
    "Data files available:\n",
    "\n",
    "1. [Followers](#followers)\n",
    "1. [Follow-Back](#follow_back)\n",
    "1. [Ego Networks](#ego)\n",
    "1. [Exposure to echo chamber](#echo_chamber)\n",
    "1. [Exposure to low credibity content](#misinformation)\n",
    "1. [Political Valence and Algorithmic Bias](#political_valence)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import sys\n",
    "sys.path.insert(1, '../exps/')\n",
    "import posgres_helper as db_helper\n",
    "sys.path.insert(1, '../metric/')\n",
    "import time_series_scores as ts_helper\n",
    "import plot_helper as plt_helper\n",
    "from collections import OrderedDict\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime as dt\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib import dates\n",
    "import glob\n",
    "import networkx as nx\n",
    "import json\n",
    "import hashlib\n",
    "%matplotlib inline\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_helper.connect_db()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Personalized data - NEED INPUT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "GLOB_TO_EGO_NET = \"../data/to_delete/ego_network_graph/*.gexf\"\n",
    "\n",
    "BOTS_RENAME = OrderedDict()\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_1>\"] = \"bot1\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_2>\"] = \"bot2\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_3>\"] = \"bot3\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_4>\"] = \"bot4\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_5>\"] = \"bot5\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_6>\"] = \"bot6\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_7>\"] = \"bot7\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_8>\"] = \"bot8\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_9>\"] = \"bot9\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_10>\"] = \"bot10\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_11>\"] = \"bot11\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_12>\"] = \"bot12\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_13>\"] = \"bot13\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_14>\"] = \"bot14\"\n",
    "BOTS_RENAME[\"<DRIFTER_SCREENAME_15>\"] = \"bot15\"\n",
    "\n",
    "INIT_SEED_MAP = {\n",
    "  'thenation': ['<DRIFTER_SCREENAME_1>', '<DRIFTER_SCREENAME_2>', '<DRIFTER_SCREENAME_3>'],\n",
    "  'washingtonpost': ['<DRIFTER_SCREENAME_4>', '<DRIFTER_SCREENAME_5>', '<DRIFTER_SCREENAME_6>'],\n",
    "  'USATODAY': ['<DRIFTER_SCREENAME_7>', '<DRIFTER_SCREENAME_8>', '<DRIFTER_SCREENAME_9>'],\n",
    "  'WSJ': ['<DRIFTER_SCREENAME_10>', '<DRIFTER_SCREENAME_11>', '<DRIFTER_SCREENAME_12>'],\n",
    "  'BreitbartNews': ['<DRIFTER_SCREENAME_13>', '<DRIFTER_SCREENAME_14>', '<DRIFTER_SCREENAME_15>']\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) <a id='followers'></a>\n",
    "## Followers           \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "followers_query = \"\"\"\n",
    "select b.screen_name, c.date, \n",
    "COUNT(c.t_usr_id_conn) from (\n",
    "   select distinct date_trunc('day', time) as date,\n",
    "                   t_usr_id_ego,\n",
    "                   conn_type, no_connctions,\n",
    "                   t_usr_id_conn from connections\n",
    ") as c\n",
    "inner join bot b on b.twitter_user_id= c.t_usr_id_ego\n",
    "where c.conn_type is true and c.no_connctions is false \n",
    "group by c.t_usr_id_ego, b.screen_name, date\n",
    "order by c.t_usr_id_ego, date;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = db_helper.getDataframeFromQuery(conn, followers_query)\n",
    "df = df.pivot(index=\"date\",columns=\"screen_name\",values=\"count\")\n",
    "# anonymize bots\n",
    "df.columns = [BOTS_RENAME.get(c,c) for c in df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 168 entries, 2019-07-13 to 2020-01-14\n",
      "Data columns (total 15 columns):\n",
      "bot11    163 non-null float64\n",
      "bot14    163 non-null float64\n",
      "bot12    163 non-null float64\n",
      "bot13    163 non-null float64\n",
      "bot10    163 non-null float64\n",
      "bot7     164 non-null float64\n",
      "bot2     165 non-null float64\n",
      "bot5     163 non-null float64\n",
      "bot6     149 non-null float64\n",
      "bot15    162 non-null float64\n",
      "bot1     161 non-null float64\n",
      "bot4     163 non-null float64\n",
      "bot3     163 non-null float64\n",
      "bot8     163 non-null float64\n",
      "bot9     164 non-null float64\n",
      "dtypes: float64(15)\n",
      "memory usage: 21.0 KB\n"
     ]
    },
    {
     "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>bot11</th>\n",
       "      <th>bot14</th>\n",
       "      <th>bot12</th>\n",
       "      <th>bot13</th>\n",
       "      <th>bot10</th>\n",
       "      <th>bot7</th>\n",
       "      <th>bot2</th>\n",
       "      <th>bot5</th>\n",
       "      <th>bot6</th>\n",
       "      <th>bot15</th>\n",
       "      <th>bot1</th>\n",
       "      <th>bot4</th>\n",
       "      <th>bot3</th>\n",
       "      <th>bot8</th>\n",
       "      <th>bot9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-07-13</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            bot11  bot14  bot12  bot13  bot10  bot7  bot2  bot5  bot6  bot15  \\\n",
       "date                                                                           \n",
       "2019-07-13    1.0    NaN    1.0    3.0    NaN   1.0   NaN   NaN   NaN    1.0   \n",
       "2019-07-14    NaN    1.0    NaN    NaN    1.0   NaN   1.0   NaN   NaN    NaN   \n",
       "2019-07-15    NaN    NaN    NaN    NaN    NaN   NaN   2.0   NaN   NaN    NaN   \n",
       "\n",
       "            bot1  bot4  bot3  bot8  bot9  \n",
       "date                                      \n",
       "2019-07-13   NaN   2.0   1.0   1.0   NaN  \n",
       "2019-07-14   NaN   NaN   NaN   NaN   1.0  \n",
       "2019-07-15   NaN   NaN   NaN   NaN   NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.info()\n",
    "# df[df.index<dt(2019,12,2)].to_csv(\"followers_data.csv\")\n",
    "df.to_csv(\"followers_data.csv\")\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) <a id='follow_back'></a>\n",
    "## Follow-back "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find connection with conn_type and has tweet_update_time\n",
    "connections_sql = \"\"\"\n",
    "    select \n",
    "        b.screen_name as t_usr_id_ego, t_usr_id_conn, conn_type, time::TIMESTAMP::DATE \n",
    "    from connections as c\n",
    "    inner join bot b on b.twitter_user_id= c.t_usr_id_ego\n",
    "    where \n",
    "        conn_tweet_update_time is not null \n",
    "        and time < DATE('{}')\n",
    "    order by time;\n",
    "\"\"\".format(\"2019/12/2\")\n",
    "connections_df = db_helper.getDataframeFromQuery(conn, connections_sql)\n",
    "connections_df.time = pd.to_datetime(connections_df.time)\n",
    "connections_df[\"seed\"] = connections_df.t_usr_id_ego.apply(\n",
    "    BOTS_RENAME.get\n",
    ").apply(\n",
    "    plt_helper.BOT_SEED_MAP.get\n",
    ").apply(\n",
    "    plt_helper.INIT_SEED_RENAME.get\n",
    ")\n",
    "connections_df.t_usr_id_ego = connections_df.t_usr_id_ego.apply(lambda x: BOTS_RENAME.get(x,x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drifter_connections = connections_df.groupby([\n",
    "    \"t_usr_id_ego\",\n",
    "    \"conn_type\",\n",
    "    \"time\"\n",
    "]).t_usr_id_conn.unique().reset_index()\n",
    "drifter_connections = drifter_connections.sort_values([\"conn_type\",\"seed\",\"t_usr_id_ego\"])\n",
    "drifter_connections[\"drifter\"] = [\"{}_{}\".format(a,b) for a,b in zip(drifter_connections.seed, 10*[1,2,3])]\n",
    "\n",
    "followback_dict = {}\n",
    "for k,v in drifter_connections.set_index([\"conn_type\",\"drifter\"]).iterrows():\n",
    "    followback_dict[k] = {}\n",
    "    for k2,v2 in drifter_connections.set_index([\"conn_type\",\"drifter\"]).iterrows():\n",
    "        followback_dict[k][k2] = len(np.intersect1d(v.t_usr_id_conn, v2.t_usr_id_conn))/len(v.t_usr_id_conn)\n",
    "follow_back_df = pd.DataFrame(followback_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = {}\n",
    "for label,rel in zip(\n",
    "    [\"follow_back\",\"friend_follow\"],\n",
    "    [(\"follower\",\"friend\"),\n",
    "    (\"friend\",\"follower\")]\n",
    "):\n",
    "    res[label] = {}\n",
    "    for political_align in set([c[:-1] for c in follow_back_df.follower.columns]):\n",
    "        tmp = follow_back_df[rel[0]].loc[rel[1]].loc[\n",
    "            [c for c in follow_back_df.follower.columns if political_align in c],\n",
    "            [c for c in follow_back_df.follower.columns if political_align in c]\n",
    "        ]\n",
    "        res[label][political_align[:-1]] = np.diag(tmp)\n",
    "        \n",
    "pd.DataFrame(res).loc[\n",
    "    [\"Left\",\"Center-left\",\"Center\",\"Center-right\",\"Right\"],:\n",
    "].transpose().stack().apply(pd.Series).to_csv(\"follow_back.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) \n",
    "<a id='ego'></a>\n",
    "## Creating Anonimmized Ego Networks\n",
    "To create the anonimmized ego networks with the respective link and hashtag scores, we need the real (no hashed) ego network as input. The method below will:\n",
    "\n",
    "1. access the data base to compute the link and hashtag scores.\n",
    "1. anonimize the nodes.\n",
    "1. save the anonimized ego networks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def add_edges_to_bot(g, bot_id=\"bot\"):\n",
    "    for i in list(g.nodes()):\n",
    "        if bot_id != i:\n",
    "            g.add_edge(bot_id,i)\n",
    "    return g\n",
    "\n",
    "def hash_user_id(twitter_user_id):\n",
    "    return int(hashlib.sha1(twitter_user_id.encode('utf-8')).hexdigest(), 16) % (10 ** 8)\n",
    "\n",
    "def clean_node_attributes(graph):\n",
    "    clean_graph = nx.from_edgelist(graph.edges)\n",
    "    for id, attr in pd.DataFrame(\n",
    "                        dict(graph.nodes(data=True)).values(),\n",
    "                        index=dict(graph.nodes(data=True)).keys()\n",
    "                    ).dropna(\n",
    "                        axis=\"columns\", \n",
    "                        how=\"all\"\n",
    "                    ).iterrows():\n",
    "        clean_graph.add_node(id, **attr.dropna().to_dict())\n",
    "    return clean_graph\n",
    "\n",
    "def anonymize_graph(graph):\n",
    "    nx.relabel_nodes(graph, lambda x: hash_user_id(x), copy=False)\n",
    "    nx.set_node_attributes(\n",
    "        graph,\n",
    "        values={k:(v[\"label\"] if \"bot\" in v[\"label\"] else k) for k,v in graph.nodes(data=True)},\n",
    "        name=\"label\"\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bots = db_helper.getDataframeFromQuery(db_helper.connect_db(), \"select screen_name, twitter_user_id, seed_screen_name from bot;\")\n",
    "bots.seed_screen_name = bots.seed_screen_name.apply(plt_helper.INIT_SEED_RENAME.get)\n",
    "bots[\"mask_name\"] = bots.screen_name.apply(BOTS_RENAME.get)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1476"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_graph = nx.Graph()\n",
    "for filename in glob.glob(GLOB_TO_EGO_NET):\n",
    "    bot_name = filename.split(\"/\")[-1].replace(\"_noBotNoHash.gexf\",\"\")\n",
    "    seed = plt_helper.INIT_SEED_RENAME.get(plt_helper.BOT_SEED_MAP.get(BOTS_RENAME.get(bot_name)))\n",
    "    graph = nx.read_gexf(filename)\n",
    "    \n",
    "    ## adding bot to the network\n",
    "    bot_profile = bots[bots.screen_name==bot_name].iloc[0]\n",
    "    nx.set_node_attributes(graph,values=True,name=bot_profile.mask_name)\n",
    "    graph.add_node(\n",
    "        bot_profile.twitter_user_id, \n",
    "        seed=bot_profile.seed_screen_name, \n",
    "        label=bot_profile.mask_name,\n",
    "#         **{bot_profile.mask_name:True}\n",
    "    )\n",
    "    add_edges_to_bot(graph, bot_id=bot_profile.twitter_user_id)\n",
    "    \n",
    "    total_graph = nx.compose(total_graph, graph)\n",
    "total_graph.number_of_nodes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 1383 entries, 1000599852936396800 to 99806132\n",
      "Data columns (total 3 columns):\n",
      "url_score         919 non-null float64\n",
      "hashtag_score     1069 non-null float64\n",
      "low_cred_score    1175 non-null float64\n",
      "dtypes: float64(3)\n",
      "memory usage: 43.2+ KB\n"
     ]
    }
   ],
   "source": [
    "homo_df = db_helper.getDataframeFromQuery(\n",
    "    db_helper.connect_db(),\n",
    "    \"\"\"\n",
    "select \n",
    "    user_id,\n",
    "    avg(url_score) as url_score,\n",
    "    avg(hashtag_score) as hashtag_score,\n",
    "    sum(low_cred_score) as low_cred_score\n",
    "from \n",
    "    tweet \n",
    "where \n",
    "    user_id in {}\n",
    "group by\n",
    "    user_id\n",
    ";\"\"\".format(tuple(total_graph.nodes))\n",
    ")\n",
    "\n",
    "homo_df = homo_df.set_index(\"user_id\").apply(pd.to_numeric)\n",
    "# adjust hashtag score to center at zero\n",
    "homo_df.hashtag_score = homo_df.hashtag_score.apply(lambda x: x - plt_helper.USATODAY_HASHTAG_SCORE if x else x)\n",
    "homo_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k,v in homo_df.to_dict(orient=\"index\").items():\n",
    "    total_graph.add_node(k, **v)\n",
    "\n",
    "homo_df = pd.DataFrame.from_dict(dict(total_graph.nodes(data=True)), orient='index')\n",
    "\n",
    "anonymize_graph(total_graph)\n",
    "nx.write_graphml(clean_node_attributes(total_graph),\"./ego_networks/homogeneity_network.graphml\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "for filename in glob.glob(GLOB_TO_EGO_NET):\n",
    "    bot_name = filename.split(\"/\")[-1].replace(\"_noBotNoHash.gexf\",\"\")\n",
    "    seed = plt_helper.INIT_SEED_RENAME.get(plt_helper.BOT_SEED_MAP.get(BOTS_RENAME.get(bot_name)))\n",
    "    graph = nx.read_gexf(filename)\n",
    "    \n",
    "    ## adding bot to the network\n",
    "    bot_profile = bots[bots.screen_name==bot_name].iloc[0]\n",
    "    nx.set_node_attributes(graph,values=True,name=bot_profile.mask_name)\n",
    "    graph.add_node(\n",
    "        bot_profile.twitter_user_id, \n",
    "        seed=bot_profile.seed_screen_name, \n",
    "        label=bot_profile.mask_name,\n",
    "#         **{bot_profile.mask_name:True}\n",
    "    )\n",
    "    add_edges_to_bot(graph, bot_id=bot_profile.twitter_user_id)\n",
    "    \n",
    "    # adding scores to nodes\n",
    "    for node in graph.nodes:\n",
    "        if node in homo_df.index:\n",
    "            graph.add_node(node, **homo_df.loc[node].to_dict())\n",
    "        \n",
    "    # rename nodes ids and labels to preserve anonymity \n",
    "    anonymize_graph(graph)\n",
    "\n",
    "    nx.write_gexf(clean_node_attributes(graph),\"./ego_networks/ego_network_with_scores-{}.gexf\".format(bot_profile.mask_name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) \n",
    "<a id='echo_chamber'></a>\n",
    "## Echo Chambers\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = []\n",
    "for filename in glob.glob(\"./ego_networks/ego_network_with_scores-*.gexf\"):\n",
    "    bot_name = filename.split(\"/\")[-1].replace(\".gexf\",\"\").replace(\"ego_network_with_scores-\",\"\")\n",
    "    seed = plt_helper.INIT_SEED_RENAME.get(plt_helper.BOT_SEED_MAP.get(bot_name))\n",
    "    graph = nx.read_gexf(filename)\n",
    "\n",
    "    columns = [\"trans\",\"density\",\"avg_clus\",\"edges\",\"nodes\"]\n",
    "#     graph = add_edges_to_bot(graph)\n",
    "    bot_res = pd.Series(\n",
    "        (nx.transitivity(graph),nx.density(graph), nx.average_clustering(graph),len(graph.edges),len(graph.nodes)),\n",
    "        index = columns\n",
    "    )\n",
    "    bot_res[\"bot\"] = bot_name\n",
    "    bot_res[\"seed\"] = seed\n",
    "    \n",
    "    # creating new graph shuffling the edges\n",
    "    n,d = zip(*list(graph.degree))\n",
    "    \n",
    "    trial=[]\n",
    "    for i in range(30):\n",
    "#         graph2 = nx.gnm_random_graph(len(graph.nodes), len(graph.edges))\n",
    "        graph2 = nx.configuration_model(d, create_using=nx.Graph)\n",
    "        graph2.remove_edges_from(nx.selfloop_edges(graph2))\n",
    "        while len(graph.edges) - len(graph2.edges):\n",
    "            source = pd.Series(graph2.nodes).sample(1).values[0]\n",
    "            neighbors = list(nx.neighbors(graph2,source)) + [source]\n",
    "            target = pd.Series(\n",
    "                np.setdiff1d(graph2.nodes, neighbors)\n",
    "            ).sample(1).values[0]\n",
    "            graph2.add_edge(source, target)\n",
    "        trial.append(\n",
    "            (nx.transitivity(graph2),nx.density(graph2), nx.average_clustering(graph2),len(graph2.edges),len(graph2.nodes)  )\n",
    "        )\n",
    "    bot_rand_res = pd.Series(\n",
    "        pd.DataFrame(trial).apply(\"mean\").values,\n",
    "        index = [f\"{c}_rand\" for c in columns]\n",
    "    )\n",
    "    \n",
    "    results.append(pd.concat([bot_res, bot_rand_res]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_res = pd.DataFrame(results)\n",
    "final_res[\"trans_rand_norm\"] = final_res.trans/final_res.trans_rand\n",
    "final_res[\"trans_density_norm\"] = final_res.trans/final_res.density\n",
    "final_res = final_res[final_res.columns.sort_values()]\n",
    "\n",
    "final_res_grp = final_res.groupby(\"seed\").agg(\n",
    "    [\"mean\",\"sem\",lambda x: sorted(x.tolist())]\n",
    ").rename(columns={\"<lambda_0>\":\"raw_values\"}).transpose()\n",
    "final_res_grp = final_res_grp[[c for c in plt_helper.ACCOUNT_COLORS.keys() if c in final_res_grp.columns]]\n",
    "final_res_grp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "final_res_grp.to_csv(\"echo_chamber_data.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) <a id='misinformation'></a>\n",
    "## Low Credibility           \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 317425 entries, 0 to 317424\n",
      "Data columns (total 5 columns):\n",
      "screen_name       317425 non-null object\n",
      "seed              317425 non-null object\n",
      "tweet_id          317425 non-null object\n",
      "low_cred_score    62935 non-null float64\n",
      "checked_at        317425 non-null datetime64[ns]\n",
      "dtypes: datetime64[ns](1), float64(1), object(3)\n",
      "memory usage: 12.1+ MB\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/u/pacheco/.local/lib/python3.6/site-packages/pandas/core/generic.py:3946: PerformanceWarning: dropping on a non-lexsorted multi-index without a level parameter may impact performance.\n",
      "  new_axis = axis.drop(labels, errors=errors)\n"
     ]
    },
    {
     "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"9\" halign=\"left\">low_cred_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"3\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"3\" halign=\"left\">count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>sem</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>sem</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>sem</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>seed</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Center</th>\n",
       "      <td>128.0</td>\n",
       "      <td>42.666667</td>\n",
       "      <td>18.800118</td>\n",
       "      <td>0.026115</td>\n",
       "      <td>0.008705</td>\n",
       "      <td>0.003626</td>\n",
       "      <td>13716</td>\n",
       "      <td>4572.000000</td>\n",
       "      <td>1036.289696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-left</th>\n",
       "      <td>162.0</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>43.139309</td>\n",
       "      <td>0.029846</td>\n",
       "      <td>0.009949</td>\n",
       "      <td>0.007425</td>\n",
       "      <td>13625</td>\n",
       "      <td>4541.666667</td>\n",
       "      <td>813.457094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Center-right</th>\n",
       "      <td>987.0</td>\n",
       "      <td>329.000000</td>\n",
       "      <td>78.117433</td>\n",
       "      <td>0.292726</td>\n",
       "      <td>0.097575</td>\n",
       "      <td>0.006896</td>\n",
       "      <td>10266</td>\n",
       "      <td>3422.000000</td>\n",
       "      <td>924.730772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Left</th>\n",
       "      <td>114.0</td>\n",
       "      <td>38.000000</td>\n",
       "      <td>24.542480</td>\n",
       "      <td>0.029712</td>\n",
       "      <td>0.009904</td>\n",
       "      <td>0.008004</td>\n",
       "      <td>17461</td>\n",
       "      <td>5820.333333</td>\n",
       "      <td>1245.954297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Right</th>\n",
       "      <td>1129.0</td>\n",
       "      <td>376.333333</td>\n",
       "      <td>66.237787</td>\n",
       "      <td>0.434573</td>\n",
       "      <td>0.144858</td>\n",
       "      <td>0.004100</td>\n",
       "      <td>7867</td>\n",
       "      <td>2622.333333</td>\n",
       "      <td>511.244995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             low_cred_score                                             \\\n",
       "                        sum                             mean             \n",
       "                        sum        mean        sem       sum      mean   \n",
       "seed                                                                     \n",
       "Center                128.0   42.666667  18.800118  0.026115  0.008705   \n",
       "Center-left           162.0   54.000000  43.139309  0.029846  0.009949   \n",
       "Center-right          987.0  329.000000  78.117433  0.292726  0.097575   \n",
       "Left                  114.0   38.000000  24.542480  0.029712  0.009904   \n",
       "Right                1129.0  376.333333  66.237787  0.434573  0.144858   \n",
       "\n",
       "                                                         \n",
       "                        count                            \n",
       "                   sem    sum         mean          sem  \n",
       "seed                                                     \n",
       "Center        0.003626  13716  4572.000000  1036.289696  \n",
       "Center-left   0.007425  13625  4541.666667   813.457094  \n",
       "Center-right  0.006896  10266  3422.000000   924.730772  \n",
       "Left          0.008004  17461  5820.333333  1245.954297  \n",
       "Right         0.004100   7867  2622.333333   511.244995  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = \"2019-04-01\"\n",
    "end_date = \"2019-12-02\"\n",
    "test=\"\"\"\n",
    "SELECT\n",
    "    DISTINCT b.screen_name, \n",
    "    b.seed_screen_name as seed,\n",
    "    tw.tweet_id,\n",
    "    tw.low_cred_score,\n",
    "    DATE(checked_at) checked_at\n",
    "FROM\n",
    "    home_timeline ht, home_timeline_tweets ht_tw, tweet tw, bot b\n",
    "WHERE\n",
    "    checked_at >= DATE '{}'\n",
    "    AND checked_at < DATE '{}'\n",
    "    AND ht.id = ht_tw.htl_id\n",
    "    AND ht_tw.tw_id = tw.tweet_id\n",
    "    AND ht.bot_id = b.bot_id\n",
    ";\n",
    "\"\"\"\n",
    "low_cred_tw = db_helper.getDataframeFromQuery(conn, test.format(start_date, end_date))\n",
    "low_cred_tw.checked_at = pd.to_datetime(low_cred_tw.checked_at)\n",
    "low_cred_tw.low_cred_score = low_cred_tw.low_cred_score.astype(\"double\")\n",
    "low_cred_tw.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "low_cred_summary = low_cred_tw.groupby([\"screen_name\",\"seed\"]).low_cred_score.agg([\"sum\",\"mean\",\"count\"]).reset_index()\n",
    "low_cred_summary[\"seed\"] = low_cred_summary.seed.apply(\n",
    "    plt_helper.INIT_SEED_RENAME.get\n",
    ")\n",
    "low_cred_summary = low_cred_summary.groupby(\"seed\").agg([\"sum\",\"mean\",\"sem\",lambda x: sorted(x.tolist())]\n",
    ").rename(columns={\"<lambda_0>\":\"raw_values\"})\n",
    "low_cred_summary.to_csv(\"low_credibility_summary_data.csv\")\n",
    "low_cred_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[back to top](#top) <a id='political_valence'></a>\n",
    "## Political Valence and Algorithmic Bias\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "url bot1 url_thenation_sliced_home_tl\n",
      "url bot2 url_thenation_sliced_home_tl\n",
      "url bot3 url_thenation_sliced_home_tl\n",
      "url bot1 url_thenation_sliced_usr_tl\n",
      "url bot2 url_thenation_sliced_usr_tl\n",
      "url bot3 url_thenation_sliced_usr_tl\n",
      "url bot1 url_thenation_sliced_friend_usr_tl\n",
      "url bot2 url_thenation_sliced_friend_usr_tl\n",
      "url bot3 url_thenation_sliced_friend_usr_tl\n",
      "hashtag bot1 hashtag_thenation_sliced_home_tl\n",
      "hashtag bot2 hashtag_thenation_sliced_home_tl\n",
      "hashtag bot3 hashtag_thenation_sliced_home_tl\n",
      "hashtag bot1 hashtag_thenation_sliced_usr_tl\n",
      "hashtag bot2 hashtag_thenation_sliced_usr_tl\n",
      "hashtag bot3 hashtag_thenation_sliced_usr_tl\n",
      "hashtag bot1 hashtag_thenation_sliced_friend_usr_tl\n",
      "hashtag bot2 hashtag_thenation_sliced_friend_usr_tl\n",
      "hashtag bot3 hashtag_thenation_sliced_friend_usr_tl\n",
      "url bot4 url_washingtonpost_sliced_home_tl\n",
      "url bot5 url_washingtonpost_sliced_home_tl\n",
      "url bot6 url_washingtonpost_sliced_home_tl\n",
      "url bot4 url_washingtonpost_sliced_usr_tl\n",
      "url bot5 url_washingtonpost_sliced_usr_tl\n",
      "url bot6 url_washingtonpost_sliced_usr_tl\n",
      "url bot4 url_washingtonpost_sliced_friend_usr_tl\n",
      "url bot5 url_washingtonpost_sliced_friend_usr_tl\n",
      "url bot6 url_washingtonpost_sliced_friend_usr_tl\n",
      "hashtag bot4 hashtag_washingtonpost_sliced_home_tl\n",
      "hashtag bot5 hashtag_washingtonpost_sliced_home_tl\n",
      "hashtag bot6 hashtag_washingtonpost_sliced_home_tl\n",
      "hashtag bot4 hashtag_washingtonpost_sliced_usr_tl\n",
      "hashtag bot5 hashtag_washingtonpost_sliced_usr_tl\n",
      "hashtag bot6 hashtag_washingtonpost_sliced_usr_tl\n",
      "hashtag bot4 hashtag_washingtonpost_sliced_friend_usr_tl\n",
      "hashtag bot5 hashtag_washingtonpost_sliced_friend_usr_tl\n",
      "hashtag bot6 hashtag_washingtonpost_sliced_friend_usr_tl\n",
      "url bot7 url_USATODAY_sliced_home_tl\n",
      "url bot8 url_USATODAY_sliced_home_tl\n",
      "url bot9 url_USATODAY_sliced_home_tl\n",
      "url bot7 url_USATODAY_sliced_usr_tl\n",
      "url bot8 url_USATODAY_sliced_usr_tl\n",
      "url bot9 url_USATODAY_sliced_usr_tl\n",
      "url bot7 url_USATODAY_sliced_friend_usr_tl\n",
      "url bot8 url_USATODAY_sliced_friend_usr_tl\n",
      "url bot9 url_USATODAY_sliced_friend_usr_tl\n",
      "hashtag bot7 hashtag_USATODAY_sliced_home_tl\n",
      "hashtag bot8 hashtag_USATODAY_sliced_home_tl\n",
      "hashtag bot9 hashtag_USATODAY_sliced_home_tl\n",
      "hashtag bot7 hashtag_USATODAY_sliced_usr_tl\n",
      "hashtag bot8 hashtag_USATODAY_sliced_usr_tl\n",
      "hashtag bot9 hashtag_USATODAY_sliced_usr_tl\n",
      "hashtag bot7 hashtag_USATODAY_sliced_friend_usr_tl\n",
      "hashtag bot8 hashtag_USATODAY_sliced_friend_usr_tl\n",
      "hashtag bot9 hashtag_USATODAY_sliced_friend_usr_tl\n",
      "url bot10 url_WSJ_sliced_home_tl\n",
      "url bot11 url_WSJ_sliced_home_tl\n",
      "url bot12 url_WSJ_sliced_home_tl\n",
      "url bot10 url_WSJ_sliced_usr_tl\n",
      "url bot11 url_WSJ_sliced_usr_tl\n",
      "url bot12 url_WSJ_sliced_usr_tl\n",
      "url bot10 url_WSJ_sliced_friend_usr_tl\n",
      "url bot11 url_WSJ_sliced_friend_usr_tl\n",
      "url bot12 url_WSJ_sliced_friend_usr_tl\n",
      "hashtag bot10 hashtag_WSJ_sliced_home_tl\n",
      "hashtag bot11 hashtag_WSJ_sliced_home_tl\n",
      "hashtag bot12 hashtag_WSJ_sliced_home_tl\n",
      "hashtag bot10 hashtag_WSJ_sliced_usr_tl\n",
      "hashtag bot11 hashtag_WSJ_sliced_usr_tl\n",
      "hashtag bot12 hashtag_WSJ_sliced_usr_tl\n",
      "hashtag bot10 hashtag_WSJ_sliced_friend_usr_tl\n",
      "hashtag bot11 hashtag_WSJ_sliced_friend_usr_tl\n",
      "hashtag bot12 hashtag_WSJ_sliced_friend_usr_tl\n",
      "url bot14 url_BreitbartNews_sliced_home_tl\n",
      "url bot15 url_BreitbartNews_sliced_home_tl\n",
      "url bot13 url_BreitbartNews_sliced_home_tl\n",
      "url bot14 url_BreitbartNews_sliced_usr_tl\n",
      "url bot15 url_BreitbartNews_sliced_usr_tl\n",
      "url bot13 url_BreitbartNews_sliced_usr_tl\n",
      "url bot14 url_BreitbartNews_sliced_friend_usr_tl\n",
      "url bot15 url_BreitbartNews_sliced_friend_usr_tl\n",
      "url bot13 url_BreitbartNews_sliced_friend_usr_tl\n",
      "hashtag bot14 hashtag_BreitbartNews_sliced_home_tl\n",
      "hashtag bot15 hashtag_BreitbartNews_sliced_home_tl\n",
      "hashtag bot13 hashtag_BreitbartNews_sliced_home_tl\n",
      "hashtag bot14 hashtag_BreitbartNews_sliced_usr_tl\n",
      "hashtag bot15 hashtag_BreitbartNews_sliced_usr_tl\n",
      "hashtag bot13 hashtag_BreitbartNews_sliced_usr_tl\n",
      "hashtag bot14 hashtag_BreitbartNews_sliced_friend_usr_tl\n",
      "hashtag bot15 hashtag_BreitbartNews_sliced_friend_usr_tl\n",
      "hashtag bot13 hashtag_BreitbartNews_sliced_friend_usr_tl\n"
     ]
    }
   ],
   "source": [
    "ts_helper.generate_all_time_series(\n",
    "    db_conn=db_helper.connect_db(), \n",
    "    INIT_SEED_MAP=INIT_SEED_MAP, \n",
    "    bots_mask=BOTS_RENAME\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}