conversations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conversations\n",
    "\n",
    "This notebook explores some tweet datasets collected by the Documenting the Now project to look at several Ferguson activists who have died, and the conversations they had in Twitter. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "db = sqlite3.connect('results.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See what users the activists replied to the most:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('KingDSeals', 'KingDSeals', 12)\n",
      "('KingDSeals', 'DefJamRecords', 6)\n",
      "('KingDSeals', 'MrChuckD', 6)\n",
      "('KingDSeals', 'deray', 6)\n",
      "('KingDSeals', 'kendricklamar', 5)\n",
      "('KingDSeals', 'AZEALIABANKS', 4)\n",
      "('KingDSeals', 'TefPoe', 4)\n",
      "('KingDSeals', 'myfabolouslife', 4)\n",
      "('KingDSeals', 'youngbuck', 4)\n",
      "('KingDSeals', 'BarackObama', 3)\n",
      "('KingDSeals', 'MasterPMiller', 3)\n",
      "('KingDSeals', 'ShetheStreet', 3)\n",
      "('KingDSeals', 'TalibKweli', 3)\n",
      "('KingDSeals', 'TheRevAl', 3)\n",
      "('KingDSeals', 'lostvoices14', 3)\n",
      "('KingDSeals', 'AntonioFrench', 2)\n",
      "('KingDSeals', 'Complex', 2)\n",
      "('KingDSeals', 'DeplorableSunny', 2)\n",
      "('KingDSeals', 'FTP_2015', 2)\n",
      "('KingDSeals', 'FortuneMagazine', 2)\n",
      "('KingDSeals', 'FunnyMaine', 2)\n",
      "('KingDSeals', 'JColeNC', 2)\n",
      "('KingDSeals', 'JuanMThompson', 2)\n",
      "('KingDSeals', 'KingJames', 2)\n",
      "('KingDSeals', 'Nettaaaaaaaa', 2)\n",
      "('KingDSeals', 'SybrinaFulton', 2)\n",
      "('KingDSeals', 'TheDreadPoet', 2)\n",
      "('KingDSeals', 'TupacShakurST', 2)\n",
      "('KingDSeals', 'allhiphopcom', 2)\n",
      "('KingDSeals', 'bassem_masri', 2)\n",
      "('KingDSeals', 'common', 2)\n",
      "('KingDSeals', 'davidbanner', 2)\n",
      "('KingDSeals', 'es_snipes', 2)\n",
      "('KingDSeals', 'icecube', 2)\n",
      "('KingDSeals', 'lattegriot', 2)\n",
      "('KingDSeals', 'mtvhiphop', 2)\n",
      "('KingDSeals', 'omyword', 2)\n",
      "('KingDSeals', 'standingup2', 2)\n",
      "('bassem_masri', 'bassem_masri', 241)\n",
      "('bassem_masri', 'TefPoe', 17)\n",
      "('bassem_masri', 'OpFerguson', 12)\n",
      "('bassem_masri', 'GovJayNixon', 10)\n",
      "('bassem_masri', 'stlcountypd', 9)\n",
      "('bassem_masri', 'T_DUBB_O', 7)\n",
      "('bassem_masri', 'Hauckers', 6)\n",
      "('bassem_masri', 'mirahwood', 6)\n",
      "('bassem_masri', 'RaniaKhalek', 5)\n",
      "('bassem_masri', 'ajamazing', 5)\n",
      "('bassem_masri', 'anthonyshahid1', 5)\n",
      "('bassem_masri', '3ofhartsGarden', 4)\n",
      "('bassem_masri', 'CNN', 4)\n",
      "('bassem_masri', 'CassandraRules', 4)\n",
      "('bassem_masri', 'ChrisHayesTV', 4)\n",
      "('bassem_masri', 'Delo_Taylor', 4)\n",
      "('bassem_masri', 'ImRikaJai', 4)\n",
      "('bassem_masri', 'JadoreKENNEDY', 4)\n",
      "('bassem_masri', 'Jeannie_Parr', 4)\n",
      "('bassem_masri', 'Lnonblonde', 4)\n",
      "('bassem_masri', 'NorthSTLCounty', 4)\n",
      "('bassem_masri', 'Nothiefsallowed', 4)\n",
      "('bassem_masri', 'Rebelutionary_Z', 4)\n",
      "('bassem_masri', 'glirette', 4)\n",
      "('bassem_masri', 'rextrabextra', 4)\n",
      "('bassem_masri', 'skankbones', 4)\n",
      "('bassem_masri', 'ucfulidiot', 4)\n",
      "('bassem_masri', 'wshemp', 4)\n",
      "('bassem_masri', 'AltRightInfo', 3)\n",
      "('bassem_masri', 'AnonCopWatch', 3)\n",
      "('bassem_masri', 'AnonIntelGroup', 3)\n",
      "('bassem_masri', 'AnonLove253FTP', 3)\n",
      "('bassem_masri', 'AnonyMobLive', 3)\n",
      "('bassem_masri', 'ChristinaKSDK', 3)\n",
      "('bassem_masri', 'CommanderXanon', 3)\n",
      "('bassem_masri', 'CruzIncRadio', 3)\n",
      "('bassem_masri', 'FRANI20', 3)\n",
      "('bassem_masri', 'Ferguson__Guy', 3)\n",
      "('bassem_masri', 'FundFerguson', 3)\n",
      "('bassem_masri', 'Jeffmw', 3)\n",
      "('bassem_masri', 'ReadCONTRA', 3)\n",
      "('bassem_masri', 'RetiredHandle', 3)\n",
      "('bassem_masri', 'Shaggy_TheAngel', 3)\n",
      "('bassem_masri', 'SophiaLamar1', 3)\n",
      "('bassem_masri', 'TheSailor2014', 3)\n",
      "('bassem_masri', 'VanguardTNT', 3)\n",
      "('bassem_masri', 'dreamhampton', 3)\n",
      "('bassem_masri', 'handsupunited_', 3)\n",
      "('bassem_masri', 'mariclaire81', 3)\n",
      "('bassem_masri', 'moonbeam0811', 3)\n",
      "('bassem_masri', 'sarasidnerCNN', 3)\n",
      "('bassem_masri', 'scottishtoodler', 3)\n",
      "('bassem_masri', 'sdwilkins3019', 3)\n",
      "('bassem_masri', 'typicalcircle', 3)\n",
      "('bassem_masri', 'withoutwingz', 3)\n",
      "('bassem_masri', 'AntonioFrench', 2)\n",
      "('bassem_masri', 'BlackGClouds', 2)\n",
      "('bassem_masri', 'Cheese_Gravy', 2)\n",
      "('bassem_masri', 'ChingonaRasta', 2)\n",
      "('bassem_masri', 'ChristineDByers', 2)\n",
      "('bassem_masri', 'Colorado2108', 2)\n",
      "('bassem_masri', 'DLoesch', 2)\n",
      "('bassem_masri', 'DamnationNow', 2)\n",
      "('bassem_masri', 'DarrisTestimony', 2)\n",
      "('bassem_masri', 'DefConAlert', 2)\n",
      "('bassem_masri', 'DiaaMahmoud', 2)\n",
      "('bassem_masri', 'Downinthe210', 2)\n",
      "('bassem_masri', 'FoxNews', 2)\n",
      "('bassem_masri', 'HowNowBrownDowd', 2)\n",
      "('bassem_masri', 'JohnnyMack_', 2)\n",
      "('bassem_masri', 'KKKOfficial311', 2)\n",
      "('bassem_masri', 'Kermit_Progress', 2)\n",
      "('bassem_masri', 'LadyJudahJr', 2)\n",
      "('bassem_masri', 'Lambityann', 2)\n",
      "('bassem_masri', 'LetsLow', 2)\n",
      "('bassem_masri', 'Mogaza', 2)\n",
      "('bassem_masri', 'Ms_Workes', 2)\n",
      "('bassem_masri', 'MustafaASN', 2)\n",
      "('bassem_masri', 'NikoWavy', 2)\n",
      "('bassem_masri', 'NubianQueenIAm', 2)\n",
      "('bassem_masri', 'PDPJ', 2)\n",
      "('bassem_masri', 'PIX2CLICK', 2)\n",
      "('bassem_masri', 'PamSchep', 2)\n",
      "('bassem_masri', 'PolitiBunny', 2)\n",
      "('bassem_masri', 'RebelDiaz', 2)\n",
      "('bassem_masri', 'ShaunKing', 2)\n",
      "('bassem_masri', 'SteveStenger', 2)\n",
      "('bassem_masri', 'TalibKweli', 2)\n",
      "('bassem_masri', 'That_Guy_19033', 2)\n",
      "('bassem_masri', 'ThisBIGSHAQ', 2)\n",
      "('bassem_masri', 'TonyTheHIITMan', 2)\n",
      "('bassem_masri', 'WhyJustBeth', 2)\n",
      "('bassem_masri', 'YankeeSkum', 2)\n",
      "('bassem_masri', 'clantro', 2)\n",
      "('bassem_masri', 'classysportsfan', 2)\n",
      "('bassem_masri', 'colleenkelly', 2)\n",
      "('bassem_masri', 'dafnegs', 2)\n",
      "('bassem_masri', 'deray', 2)\n",
      "('bassem_masri', 'dingalingy55', 2)\n",
      "('bassem_masri', 'divineem', 2)\n",
      "('bassem_masri', 'jasonredonnelly', 2)\n",
      "('bassem_masri', 'justagirlatl', 2)\n",
      "('bassem_masri', 'kbpellett314', 2)\n",
      "('bassem_masri', 'megameg96', 2)\n",
      "('bassem_masri', 'mtaylor1211', 2)\n",
      "('bassem_masri', 'my2cnz', 2)\n",
      "('bassem_masri', 'nativekittens', 2)\n",
      "('bassem_masri', 'rochellek14', 2)\n",
      "('bassem_masri', 'search4swag', 2)\n",
      "('bassem_masri', 'shariline', 2)\n",
      "('bassem_masri', 'stabier', 2)\n",
      "('bassem_masri', 'starletwilliams', 2)\n",
      "('bassem_masri', 'tribex_stl', 2)\n",
      "('bassem_masri', 'user95815', 2)\n",
      "('eyeFLOODpanties', 'Tip', 2)\n",
      "('eyeFLOODpanties', 'darth__mouth', 2)\n",
      "('eyeFLOODpanties', 'joshuamahony', 2)\n"
     ]
    }
   ],
   "source": [
    "q = \"\"\"\n",
    "    SELECT \n",
    "      user_screen_name,\n",
    "      in_reply_to_screen_name,\n",
    "      COUNT(*) AS total\n",
    "    FROM results\n",
    "    WHERE\n",
    "      match_type = \"replied\"\n",
    "    GROUP BY\n",
    "      user_screen_name,\n",
    "      in_reply_to_screen_name\n",
    "    HAVING\n",
    "      total > 1\n",
    "    ORDER BY\n",
    "      user_screen_name,\n",
    "      total DESC\n",
    "    \"\"\"\n",
    "    \n",
    "for result in db.execute(q):\n",
    "    print(result)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What does this look like as a nework? First create the graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import networkx\n",
    "\n",
    "replies_g = networkx.DiGraph()\n",
    "for from_user, to_user, count in db.execute(q):\n",
    "    if from_user == to_user:\n",
    "        continue\n",
    "    replies_g.add_node(from_user)\n",
    "    replies_g.add_node(to_user)\n",
    "    replies_g.add_edge(from_user, to_user, weight=count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now create a function to visualize the graph with pyvis. I'm copying this little handy function from another project:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"600\"\n",
       "            src=\"replies.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x10c5245d0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pyvis\n",
    "\n",
    "def label(s):\n",
    "    if len(s) > 40:\n",
    "        return s[0:10]\n",
    "    return s\n",
    "\n",
    "def vis(g, html_path, width=\"100%\", height=600):\n",
    "    degree = g.degree(g)\n",
    "        \n",
    "    v = pyvis.network.Network(notebook=True, height=height, width=width, directed=True)\n",
    "    for src, dst, data in g.edges(data=True):\n",
    "        v.add_node(src, label(src), title=src, value=degree[src], color='#ccc')\n",
    "        v.add_node(dst, label(dst), title=dst, value=degree[dst], color='lightgreen')\n",
    "        v.add_edge(src, dst, value=data['weight'])\n",
    "        \n",
    "    \n",
    "    v.set_options('{\"edges\":{\"color\":{\"inherit\":true},\"smooth\":false},\"physics\":{\"forceAtlas2Based\":{\"springLength\":50},\"minVelocity\":0.75,\"solver\":\"forceAtlas2Based\"}}')\n",
    "    return v.show(html_path)\n",
    "\n",
    "vis(replies_g, 'replies.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's use it to generate a network visualization of the activists and their replies."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do a similar query to see who the users are retweeting:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('KingDSeals', 'bassem_masri', 30)\n",
      "('KingDSeals', 'VanguardTNT', 10)\n",
      "('KingDSeals', 'davidbanner', 7)\n",
      "('KingDSeals', 'RE_invent_ED', 5)\n",
      "('KingDSeals', 'TheOutlawz', 5)\n",
      "('KingDSeals', 'ImRikaJai', 4)\n",
      "('KingDSeals', 'T_DUBB_O', 4)\n",
      "('KingDSeals', 'YOUNG_NOBLE1', 4)\n",
      "('KingDSeals', 'brothercartan', 4)\n",
      "('KingDSeals', 'handsupunited_', 4)\n",
      "('KingDSeals', 'tariqnasheed', 4)\n",
      "('KingDSeals', 'Gimme_A_Break1', 3)\n",
      "('KingDSeals', 'TefPoe', 3)\n",
      "('KingDSeals', 'jjmcphatter', 3)\n",
      "('KingDSeals', 'lostvoices14', 3)\n",
      "('KingDSeals', 'mikebrowncover', 3)\n",
      "('KingDSeals', 'neweryork', 3)\n",
      "('KingDSeals', 'Cherrell_Brown', 2)\n",
      "('KingDSeals', 'ChuckModi1', 2)\n",
      "('KingDSeals', 'Khan_SHEGOG', 2)\n",
      "('KingDSeals', 'Nettaaaaaaaa', 2)\n",
      "('KingDSeals', 'OwlsAsylum', 2)\n",
      "('KingDSeals', 'dlatchison011', 2)\n",
      "('KingDSeals', 'guygruber', 2)\n",
      "('KingDSeals', 'peaceful_birdie', 2)\n",
      "('KingDSeals', 'youthradio', 2)\n",
      "('King_D_Seals', 'THEREALBANNER', 7)\n",
      "('bassem_masri', 'Delo_Taylor', 203)\n",
      "('bassem_masri', 'sarahkendzior', 45)\n",
      "('bassem_masri', 'deray', 41)\n",
      "('bassem_masri', 'handsupunited_', 40)\n",
      "('bassem_masri', 'CruzIncRadio', 36)\n",
      "('bassem_masri', 'TefPoe', 26)\n",
      "('bassem_masri', 'Cygnussid42', 24)\n",
      "('bassem_masri', 'Lnonblonde', 24)\n",
      "('bassem_masri', 'dejahsmommy', 24)\n",
      "('bassem_masri', 'AntonioFrench', 22)\n",
      "('bassem_masri', 'Hauckers', 22)\n",
      "('bassem_masri', 'RE_invent_ED', 22)\n",
      "('bassem_masri', 'CassandraRules', 21)\n",
      "('bassem_masri', 'Jeannie_Hartley', 21)\n",
      "('bassem_masri', 'CruzInc4u', 20)\n",
      "('bassem_masri', 'NYC2Palestine', 18)\n",
      "('bassem_masri', 'SeanSharesStuff', 18)\n",
      "('bassem_masri', 'typicalcircle', 18)\n",
      "('bassem_masri', 'AnonCopWatch', 17)\n",
      "('bassem_masri', 'norarahimian', 17)\n",
      "('bassem_masri', 'ImRikaJai', 16)\n",
      "('bassem_masri', 'Just4Emanuel9', 15)\n",
      "('bassem_masri', 'PenofUmar', 15)\n",
      "('bassem_masri', 'ChristinaKSDK', 14)\n",
      "('bassem_masri', 'AnonyMobLive', 13)\n",
      "('bassem_masri', 'HatchHarkness', 13)\n",
      "('bassem_masri', 'MungenCakes', 13)\n",
      "('bassem_masri', 'my2cnz', 13)\n",
      "('bassem_masri', 'KWRose', 12)\n",
      "('bassem_masri', 'ReadCONTRA', 12)\n",
      "('bassem_masri', 'i_AM_theChange', 12)\n",
      "('bassem_masri', 'mikebrowncover', 12)\n",
      "('bassem_masri', 'Hotflashes10', 11)\n",
      "('bassem_masri', 'RaniaKhalek', 11)\n",
      "('bassem_masri', 'TheVikChick', 11)\n",
      "('bassem_masri', 'YourAnonNews', 11)\n",
      "('bassem_masri', 'AnonFerg', 10)\n",
      "('bassem_masri', 'AnonLove253FTP', 10)\n",
      "('bassem_masri', 'Nettaaaaaaaa', 10)\n",
      "('bassem_masri', 'Rebelutionary_Z', 10)\n",
      "('bassem_masri', 'ChingonaRasta', 9)\n",
      "('bassem_masri', 'VanguardTNT', 9)\n",
      "('bassem_masri', 'CinnaAyn', 8)\n",
      "('bassem_masri', 'CruzIncGlobal', 8)\n",
      "('bassem_masri', 'IsakandRbka', 8)\n",
      "('bassem_masri', 'KatCapps', 8)\n",
      "('bassem_masri', 'justinstoned', 8)\n",
      "('bassem_masri', 'stop1033', 8)\n",
      "('bassem_masri', 'ChuckModi1', 7)\n",
      "('bassem_masri', 'Cleosbaby64', 7)\n",
      "('bassem_masri', 'MissJupiter1957', 7)\n",
      "('bassem_masri', 'Ms_Workes', 7)\n",
      "('bassem_masri', 'OwlsAsylum', 7)\n",
      "('bassem_masri', 'StlGal_36', 7)\n",
      "('bassem_masri', 'WyzeChef', 7)\n",
      "('bassem_masri', 'kash04316_d', 7)\n",
      "('bassem_masri', 'mollyrosestl', 7)\n",
      "('bassem_masri', 'pbj_stl13', 7)\n",
      "('bassem_masri', 'BlackGClouds', 6)\n",
      "('bassem_masri', 'IdalinBobe', 6)\n",
      "('bassem_masri', 'SMoKeeGyRL', 6)\n",
      "('bassem_masri', 'nubian_1', 6)\n",
      "('bassem_masri', 'sdkstl', 6)\n",
      "('bassem_masri', 'wshemp', 6)\n",
      "('bassem_masri', 'ILoveMyWife0007', 5)\n",
      "('bassem_masri', 'LeslieMac', 5)\n",
      "('bassem_masri', 'Mayberry200', 5)\n",
      "('bassem_masri', 'MintPressNews', 5)\n",
      "('bassem_masri', 'OpFerguson', 5)\n",
      "('bassem_masri', 'bdoulaoblongata', 5)\n",
      "('bassem_masri', 'dolphin_LS', 5)\n",
      "('bassem_masri', 'drumbeats4peace', 5)\n",
      "('bassem_masri', 'jrosenbaum', 5)\n",
      "('bassem_masri', 'lsarsour', 5)\n",
      "('bassem_masri', 'mirahwood', 5)\n",
      "('bassem_masri', 'raydowns', 5)\n",
      "('bassem_masri', 'tchopstl_', 5)\n",
      "('bassem_masri', 'thewanji', 5)\n",
      "('bassem_masri', 'Anna_Baltzer', 4)\n",
      "('bassem_masri', 'AnonymousPress', 4)\n",
      "('bassem_masri', 'CNN', 4)\n",
      "('bassem_masri', 'Ellevation_', 4)\n",
      "('bassem_masri', 'Kelemchrist', 4)\n",
      "('bassem_masri', 'LAPeoplesMedia', 4)\n",
      "('bassem_masri', 'LaminSwann', 4)\n",
      "('bassem_masri', 'Nechanna', 4)\n",
      "('bassem_masri', 'Shanna_Parker', 4)\n",
      "('bassem_masri', 'T_DUBB_O', 4)\n",
      "('bassem_masri', 'TalibKweli', 4)\n",
      "('bassem_masri', 'angeldevil1111', 4)\n",
      "('bassem_masri', 'bexology_', 4)\n",
      "('bassem_masri', 'deathrep', 4)\n",
      "('bassem_masri', 'maryteatowel', 4)\n",
      "('bassem_masri', 'mel_takes_pics', 4)\n",
      "('bassem_masri', 'rousseau_ist', 4)\n",
      "('bassem_masri', 'search4swag', 4)\n",
      "('bassem_masri', 't0p_100', 4)\n",
      "('bassem_masri', 'verbal_riot', 4)\n",
      "('bassem_masri', 'AB_Syed', 3)\n",
      "('bassem_masri', 'AVASradiance', 3)\n",
      "('bassem_masri', 'AnitaStewart', 3)\n",
      "('bassem_masri', 'BellaEiko', 3)\n",
      "('bassem_masri', 'Bohemianmoon', 3)\n",
      "('bassem_masri', 'Copwatch', 3)\n",
      "('bassem_masri', 'CourtneyPFB', 3)\n",
      "('bassem_masri', 'DConford', 3)\n",
      "('bassem_masri', 'FaithInFerguson', 3)\n",
      "('bassem_masri', 'GeekNStereo', 3)\n",
      "('bassem_masri', 'GlobalRevLive', 3)\n",
      "('bassem_masri', 'JCoopPhoto', 3)\n",
      "('bassem_masri', 'JohnnyMack_', 3)\n",
      "('bassem_masri', 'JoyAnnReid', 3)\n",
      "('bassem_masri', 'MelodyTheMellow', 3)\n",
      "('bassem_masri', 'NewsRevo', 3)\n",
      "('bassem_masri', 'NubianQueenIAm', 3)\n",
      "('bassem_masri', 'PMbeers', 3)\n",
      "('bassem_masri', 'Pie324', 3)\n",
      "('bassem_masri', 'PresidentFraud', 3)\n",
      "('bassem_masri', 'SherriQueenMoma', 3)\n",
      "('bassem_masri', 'TheDreadPoet', 3)\n",
      "('bassem_masri', 'TheNYCBMX', 3)\n",
      "('bassem_masri', 'TheZOMB', 3)\n",
      "('bassem_masri', 'anthonyshahid1', 3)\n",
      "('bassem_masri', 'argusnewsnow', 3)\n",
      "('bassem_masri', 'brandonlongo', 3)\n",
      "('bassem_masri', 'cgrapski', 3)\n",
      "('bassem_masri', 'devotedones', 3)\n",
      "('bassem_masri', 'iJesseWilliams', 3)\n",
      "('bassem_masri', 'j7vii', 3)\n",
      "('bassem_masri', 'kavn', 3)\n",
      "('bassem_masri', 'kaybunce', 3)\n",
      "('bassem_masri', 'longgoneblond', 3)\n",
      "('bassem_masri', 'moonbeam0811', 3)\n",
      "('bassem_masri', 'mterr337', 3)\n",
      "('bassem_masri', 'overtherye', 3)\n",
      "('bassem_masri', 'phampel', 3)\n",
      "('bassem_masri', 'prussogirl', 3)\n",
      "('bassem_masri', 'queenlintlicker', 3)\n",
      "('bassem_masri', 'sarasidnerCNN', 3)\n",
      "('bassem_masri', 'stacielovestea', 3)\n",
      "('bassem_masri', 'stlpsc', 3)\n",
      "('bassem_masri', '12160mhz', 2)\n",
      "('bassem_masri', '88blackhatss', 2)\n",
      "('bassem_masri', 'ABernM', 2)\n",
      "('bassem_masri', 'Amazing32582', 2)\n",
      "('bassem_masri', 'Anon_OpSK', 2)\n",
      "('bassem_masri', 'Anondad', 2)\n",
      "('bassem_masri', 'ArrestWilson', 2)\n",
      "('bassem_masri', 'Auntie_Allison', 2)\n",
      "('bassem_masri', 'BigBull45', 2)\n",
      "('bassem_masri', 'Blackstarjus', 2)\n",
      "('bassem_masri', 'Chaosforthefly', 2)\n",
      "('bassem_masri', 'ChristNme', 2)\n",
      "('bassem_masri', 'CigarDaisy311', 2)\n",
      "('bassem_masri', 'CommanderXanon', 2)\n",
      "('bassem_masri', 'CynnyB', 2)\n",
      "('bassem_masri', 'De400blues', 2)\n",
      "('bassem_masri', 'DeadPrecariat', 2)\n",
      "('bassem_masri', 'DemondFernandez', 2)\n",
      "('bassem_masri', 'DesignsInCode', 2)\n",
      "('bassem_masri', 'Erberhawk', 2)\n",
      "('bassem_masri', 'FergusonMafia', 2)\n",
      "('bassem_masri', 'Fiveonadub', 2)\n",
      "('bassem_masri', 'GC4Hillary', 2)\n",
      "('bassem_masri', 'GrlMeechie', 2)\n",
      "('bassem_masri', 'HowNowBrownDowd', 2)\n",
      "('bassem_masri', 'IAM_aVOICE', 2)\n",
      "('bassem_masri', 'JASVPUMP', 2)\n",
      "('bassem_masri', 'JessLivMo', 2)\n",
      "('bassem_masri', 'JesseBenn', 2)\n",
      "('bassem_masri', 'Jmoon901', 2)\n",
      "('bassem_masri', 'KINGofMONSTARS', 2)\n",
      "('bassem_masri', 'KickGamePropers', 2)\n",
      "('bassem_masri', 'LiberalHDWoman', 2)\n",
      "('bassem_masri', 'MercutioXanon', 2)\n",
      "('bassem_masri', 'MidWestMisfit13', 2)\n",
      "('bassem_masri', 'NAGOUL1', 2)\n",
      "('bassem_masri', 'OBS_STL', 2)\n",
      "('bassem_masri', 'OLAASM', 2)\n",
      "('bassem_masri', 'OccHiloMedia', 2)\n",
      "('bassem_masri', 'OccuWorld', 2)\n",
      "('bassem_masri', 'OchiProp', 2)\n",
      "('bassem_masri', 'POTUSIsLOVED44', 2)\n",
      "('bassem_masri', 'PaulKWeinhold', 2)\n",
      "('bassem_masri', 'Penny_G', 2)\n",
      "('bassem_masri', 'Platypus64', 2)\n",
      "('bassem_masri', 'PolicePoliceACP', 2)\n",
      "('bassem_masri', 'RationalBassist', 2)\n",
      "('bassem_masri', 'RisePDX', 2)\n",
      "('bassem_masri', 'SeattleKennetha', 2)\n",
      "('bassem_masri', 'SophiaLamar1', 2)\n",
      "('bassem_masri', 'T_H_E_F_A_C_E', 2)\n",
      "('bassem_masri', 'Thisl', 2)\n",
      "('bassem_masri', 'TinaGaston87', 2)\n",
      "('bassem_masri', 'Truth573', 2)\n",
      "('bassem_masri', 'US_Campaign', 2)\n",
      "('bassem_masri', 'WesleyLowery', 2)\n",
      "('bassem_masri', 'Yamiche', 2)\n",
      "('bassem_masri', '_Elle_Spencer_', 2)\n",
      "('bassem_masri', 'aaronRmcmullin', 2)\n",
      "('bassem_masri', 'adbridgeforth', 2)\n",
      "('bassem_masri', 'ageless_vintage', 2)\n",
      "('bassem_masri', 'amnestyusa', 2)\n",
      "('bassem_masri', 'backonmylizzie', 2)\n",
      "('bassem_masri', 'bleedingheartmx', 2)\n",
      "('bassem_masri', 'brownblaze', 2)\n",
      "('bassem_masri', 'ccchicky', 2)\n",
      "('bassem_masri', 'chriskingstl', 2)\n",
      "('bassem_masri', 'common', 2)\n",
      "('bassem_masri', 'damkina', 2)\n",
      "('bassem_masri', 'doccivilwarsr', 2)\n",
      "('bassem_masri', 'dreamhampton', 2)\n",
      "('bassem_masri', 'evenbev', 2)\n",
      "('bassem_masri', 'eyeseeslive', 2)\n",
      "('bassem_masri', 'fergusonRus', 2)\n",
      "('bassem_masri', 'jaa4th', 2)\n",
      "('bassem_masri', 'jimprincems', 2)\n",
      "('bassem_masri', 'jonswaine', 2)\n",
      "('bassem_masri', 'justinbaragona', 2)\n",
      "('bassem_masri', 'kgosztola', 2)\n",
      "('bassem_masri', 'kittygal5399', 2)\n",
      "('bassem_masri', 'lamproce', 2)\n",
      "('bassem_masri', 'limeonaire', 2)\n",
      "('bassem_masri', 'maccalled', 2)\n",
      "('bassem_masri', 'minefield101', 2)\n",
      "('bassem_masri', 'msugrl33', 2)\n",
      "('bassem_masri', 'myownspin', 2)\n",
      "('bassem_masri', 'occupytheory', 2)\n",
      "('bassem_masri', 'pastormykmac', 2)\n",
      "('bassem_masri', 'realmelodic', 2)\n",
      "('bassem_masri', 'ronfullwood', 2)\n",
      "('bassem_masri', 'sacourtreporter', 2)\n",
      "('bassem_masri', 'sarsoora834', 2)\n",
      "('bassem_masri', 'sepulvev', 2)\n",
      "('bassem_masri', 'shazza_razza', 2)\n",
      "('bassem_masri', 'sophiaponders', 2)\n",
      "('bassem_masri', 'soylentgs', 2)\n",
      "('bassem_masri', 'stephencdeere', 2)\n",
      "('bassem_masri', 'stevenjhsieh', 2)\n",
      "('bassem_masri', 'stlouisx50', 2)\n",
      "('bassem_masri', 'stubbscrew', 2)\n",
      "('eyeFLOODpanties', 'kodacohen', 12)\n",
      "('eyeFLOODpanties', 'PDPJ', 4)\n",
      "('eyeFLOODpanties', 'Tedderman1', 4)\n",
      "('eyeFLOODpanties', 'DylanPlayfair', 3)\n",
      "('eyeFLOODpanties', '_LadyT23', 3)\n",
      "('eyeFLOODpanties', 'AntonioFrench', 2)\n",
      "('eyeFLOODpanties', 'Bigcee56', 2)\n",
      "('eyeFLOODpanties', 'Bipartisanism', 2)\n",
      "('eyeFLOODpanties', 'Breaking911', 2)\n",
      "('eyeFLOODpanties', 'CocoDivinexxx', 2)\n",
      "('eyeFLOODpanties', 'ElanMartinDCLaw', 2)\n",
      "('eyeFLOODpanties', 'FierceBeautyCo', 2)\n",
      "('eyeFLOODpanties', 'HazeyKilla', 2)\n",
      "('eyeFLOODpanties', 'KadieUSA', 2)\n",
      "('eyeFLOODpanties', 'MrsTKidd', 2)\n",
      "('eyeFLOODpanties', 'MzJonesEsq', 2)\n",
      "('eyeFLOODpanties', 'Nettaaaaaaaa', 2)\n",
      "('eyeFLOODpanties', 'Rebelutionary_Z', 2)\n",
      "('eyeFLOODpanties', 'ReignOfApril', 2)\n",
      "('eyeFLOODpanties', 'SavingEJ', 2)\n",
      "('eyeFLOODpanties', 'StrangePintura', 2)\n",
      "('eyeFLOODpanties', 'TheCubicleChick', 2)\n",
      "('eyeFLOODpanties', 'TheObamaDiary', 2)\n",
      "('eyeFLOODpanties', 'WallyBeamin', 2)\n",
      "('eyeFLOODpanties', 'brianstelter', 2)\n",
      "('eyeFLOODpanties', 'coolmamamia', 2)\n",
      "('eyeFLOODpanties', 'deray', 2)\n",
      "('eyeFLOODpanties', 'drewXdeficit', 2)\n",
      "('eyeFLOODpanties', 'drgoddess', 2)\n",
      "('eyeFLOODpanties', 'jadewalkerr21', 2)\n",
      "('eyeFLOODpanties', 'jaywise3', 2)\n",
      "('eyeFLOODpanties', 'kentuckyviews', 2)\n",
      "('eyeFLOODpanties', 'kreactivity', 2)\n",
      "('eyeFLOODpanties', 'lana_heart300', 2)\n",
      "('eyeFLOODpanties', 'lataysimp', 2)\n",
      "('eyeFLOODpanties', 'newsjunke', 2)\n",
      "('eyeFLOODpanties', 'reneefranks', 2)\n",
      "('eyeFLOODpanties', 'sauvignonelle', 2)\n"
     ]
    }
   ],
   "source": [
    "q = \"\"\"\n",
    "    SELECT \n",
    "      user_screen_name,\n",
    "      retweet_or_quote_screen_name,\n",
    "      COUNT(*) AS total\n",
    "    FROM results\n",
    "    WHERE\n",
    "      match_type = \"retweeted\"\n",
    "    GROUP BY\n",
    "      user_screen_name,\n",
    "      retweet_or_quote_screen_name\n",
    "    HAVING\n",
    "      total > 1\n",
    "    ORDER BY\n",
    "      user_screen_name,\n",
    "      total DESC\n",
    "    \"\"\"\n",
    "    \n",
    "for result in db.execute(q):\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The retweet network looks interesting because bassem_masri and KingDSeals are retweeting each other. Let's create a network visualization of the retweets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'vis' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-9792618724cf>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      7\u001b[0m     \u001b[0mretweets_g\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0madd_edge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfrom_user\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mto_user\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mweight\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcount\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 9\u001b[0;31m \u001b[0mvis\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mretweets_g\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'retweets.html'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'vis' is not defined"
     ]
    }
   ],
   "source": [
    "retweets_g = networkx.DiGraph()\n",
    "for from_user, to_user, count in db.execute(q):\n",
    "    if from_user == to_user:\n",
    "        continue\n",
    "    retweets_g.add_node(from_user)\n",
    "    retweets_g.add_node(to_user)\n",
    "    retweets_g.add_edge(from_user, to_user, weight=count)\n",
    "\n",
    "vis(retweets_g, 'retweets.html')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}