class_09_datascience2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4685dc60",
   "metadata": {},
   "source": [
    "# Class 9: Data Science 2 — Querying SQL Tables for Network Construction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59666d14-5d63-4d92-94d3-6bea51a70597",
   "metadata": {},
   "source": [
    "1. Come in. Sit down. Open Teams.\n",
    "2. Make sure your notebook from last class is saved.\n",
    "3. Open up the Jupyter Lab server.\n",
    "4. Open up the Jupyter Lab terminal.\n",
    "5. Activate Conda: `module load anaconda3/2022.05`\n",
    "6. Activate the shared virtual environment: `source activate /courses/PHYS7332.202510/shared/phys7332-env/`\n",
    "7. Run `python3 git_fixer2.py`\n",
    "8. Github:\n",
    "    - git status (figure out what files have changed)\n",
    "    - git add ... (add the file that you changed, aka the `_MODIFIED` one(s))\n",
    "    - git commit -m \"your changes\"\n",
    "    - git push origin main\n",
    "________"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5a89642",
   "metadata": {},
   "source": [
    "## Goals of today's class\n",
    "1. Get a bunch of reps in using SQL to learn about a dataset.\n",
    "2. Use SQL queries in concert with network science to characterize a network."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7421931a-6c69-4030-88a8-3b27f4f3b954",
   "metadata": {},
   "source": [
    "## Today's Dataset\n",
    "\n",
    "Today we're going to focus on getting a lot of reps in in order to learn things about our dataset. It will involve a lot of hands-on coding and less of me talking at you. \n",
    "\n",
    "The dataset we'll be using today comes from SNAP; it's the [Reddit Hyperlinks dataset](https://snap.stanford.edu/data/soc-RedditHyperlinks.html). The paper that arose from this dataset, by Kumar et al., can be found [here](https://cs.stanford.edu/~srijan/pubs/conflict-paper-www18.pdf). \n",
    "\n",
    "Posts in one subreddit can link out to another subreddit for myriad reasons, including hatred, shared interests, and many things in between. I've somewhat simplified the dataset we'll be working with today, but it has some interesting elements nonetheless. For one, each link has a `TIMESTAMP` attribute that indicates when the hyperlink was created. There's also a `LINK_SENTIMENT` attribute that indicates whether the sentiment of the post or title that contained the link was positive (1) or negative (-1). The `link_source` attribute indicates whether the link came from the title or the body of the post. \n",
    "\n",
    "First let's get connected to our database again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2be577d3-4bb0-4566-9b5c-9ec2ceba5d6c",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "\n",
    "my_creds = []\n",
    "with open('../../../shared/student_mysql_credentials.txt', 'r') as f:\n",
    "    for line in f.readlines():\n",
    "        my_creds.append(line.strip())\n",
    "\n",
    "hostname=\"mysql-0005\"\n",
    "dbname=\"PHYS7332\"\n",
    "uname=my_creds[0]\n",
    "pwd=my_creds[1]\n",
    "\n",
    "engine = create_engine(\"mysql+pymysql://{user}:{pw}@{host}/{db}\"\n",
    "                           .format(host=hostname, db=dbname,\n",
    "                                   user=uname,pw=pwd))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26fd608c-ec04-4bda-8a1a-fcd720f280e9",
   "metadata": {},
   "source": [
    "### Looking at the data\n",
    "Next, we'll take a look at the dataset. Let's look at the first 10 rows just to get a sense of what the data looks like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64c34bd2-ff16-4efe-acd9-8e01d0402be7",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "qu = 'SELECT * FROM reddit_hyperlinks LIMIT 10;'\n",
    "pd.read_sql(qu, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "972ce328-5a9c-4fa9-9d62-752d91c5436d",
   "metadata": {},
   "source": [
    "Which linkage pops up the most often in our dataset? Let's find out. \n",
    "\n",
    "We're going to start by grouping our data by the combination of `SOURCE_SUBREDDIT` and `TARGET_SUBREDDIT`. \n",
    "\n",
    "Then we'll count how many rows each grouping contains and note that, along with the names of the source & target subreddits. \n",
    "\n",
    "Finally, we'll sort by the number of total linkages that occurred."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7aad2316-548e-4a67-8431-c42233adcd3d",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "qu = \"\"\"\n",
    "SELECT COUNT(*) as count_links, SOURCE_SUBREDDIT, TARGET_SUBREDDIT \\\n",
    "FROM reddit_hyperlinks \\\n",
    "GROUP BY SOURCE_SUBREDDIT, TARGET_SUBREDDIT ORDER BY count_links;\n",
    "\"\"\"\n",
    "df_link_counts = pd.read_sql(qu, engine)\n",
    "df_link_counts.sort_values('count_links', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21517821-8e14-45d4-a39e-f51829da3e1a",
   "metadata": {},
   "source": [
    "### Degree Distribution\n",
    "You might also be curious about the in- and out- degree distribution of this network. Can you compute the in- and out- degree distributions using the SQL table? In this case, we want to know how many distinct subreddits each other subreddit linked to, not how many **times** a subreddit was linked to."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c59b252f-4417-4ba2-96fd-0da68a1fa347",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Your Turn!\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "\n",
    "def degree_distribution(k, number_of_bins=15, log_binning=True, density=True):\n",
    "    \"\"\"\n",
    "    Given a degree sequence, return the y values (probability) and the\n",
    "    x values (support) of a degree distribution that you're going to plot.\n",
    "    \n",
    "    Parameters\n",
    "    ----------\n",
    "    k: a list of nodes' degrees\n",
    "\n",
    "    number_of_bins (int):\n",
    "        length of output vectors\n",
    "    \n",
    "    log_binning (bool):\n",
    "        if you are plotting on a log-log axis, then this is useful\n",
    "    \n",
    "    density (bool):\n",
    "        whether to return counts or probability density (default: True)\n",
    "        Note: probability densities integrate to 1 but do not sum to 1. \n",
    "        \n",
    "    Returns\n",
    "    -------\n",
    "    hist, bins (np.ndarray):\n",
    "        probability density if density=True node counts if density=False; binned edges\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    kmax = np.max(k)                    # get the maximum degree\n",
    "    \n",
    "    \n",
    "    # Step 2: Then we'll need to construct bins\n",
    "    if log_binning:\n",
    "        # array of bin edges including rightmost and leftmost\n",
    "        bins = np.logspace(0,np.log10(kmax+1),number_of_bins+1)\n",
    "        bin_edges = []\n",
    "        for ix in range(len(bins) - 1):\n",
    "            bin_edges.append(np.exp((np.log(bins[ix])+np.log(bins[ix + 1]))/2))\n",
    "    else:\n",
    "        bins = np.linspace(0,kmax+1,num=number_of_bins+1)\n",
    "        bin_edges = []\n",
    "        for ix in range(len(bins) - 1):\n",
    "            bin_edges.append((bins[ix] + bins[ix + 1]) / 2)\n",
    "    # Step 3: Then we can compute the histogram using numpy\n",
    "    hist, _ = np.histogram(k,bins,density=density)\n",
    "\n",
    "    return bin_edges, hist\n",
    "    \n",
    "fig, ax = plt.subplots(1,2,figsize=(10,4),dpi=150)\n",
    "\n",
    "in_degree_bin_edges, in_degree_freqs = degree_distribution(in_degree_list)\n",
    "out_degree_bin_edges, out_degree_freqs = degree_distribution(out_degree_list)\n",
    "ax[0].loglog(in_degree_bin_edges, in_degree_freqs, 'o')\n",
    "ax[0].set_title('in-degree distribution')\n",
    "ax[1].loglog(out_degree_bin_edges, out_degree_freqs, 'o')\n",
    "ax[1].set_title('out-degree distribution')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88282ac8-54d5-4ef1-ab98-7a2e675c7412",
   "metadata": {},
   "source": [
    "### Temporal Dynamics of In-Links\n",
    "Now let's look at how in-links to a specific subreddit show up over time. Do they tend to accumulate steadily over time, or do they arrive in bursts? Let's look at the in-links to /r/askreddit. We'll use the handy `pd.to_datetime` function to convert our string timestamps to datetime objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e190a462-6f12-49c1-9f2c-08c0725d1802",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "qu_inlinks = \"\"\"\n",
    "SELECT TIMESTAMP, SOURCE_SUBREDDIT \\\n",
    "FROM reddit_hyperlinks \\\n",
    "WHERE TARGET_SUBREDDIT='askreddit';\n",
    "\"\"\"\n",
    "df_inlinks = pd.read_sql(qu_inlinks, engine)\n",
    "\n",
    "df_inlinks['ts_parsed_rounded'] = df_inlinks['TIMESTAMP'].apply(pd.to_datetime).dt.floor('30d')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "211368dc-1cc4-44a2-97be-7806a382461d",
   "metadata": {},
   "source": [
    "We've just taken the 30-day floor (roughly a month) of the timestamps we computed; this is so we aggregate our in-link counts to every 30 days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d371016-768a-44a0-b2e1-cd680762f00b",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "count_per_30d = df_inlinks.groupby('ts_parsed_rounded').agg({'TIMESTAMP': 'count'})\n",
    "plt.plot(count_per_30d.index, count_per_30d['TIMESTAMP'])\n",
    "plt.xticks(rotation=90)\n",
    "plt.xlabel('time')\n",
    "plt.ylabel('in-link frequency')\n",
    "plt.title('/r/askreddit in-link frequency over time')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac487522-6e1c-45c9-a1f1-1853cd76af83",
   "metadata": {},
   "source": [
    "Next, we'll look at /r/subredditdrama's out-links. Can you make the same plot but for /r/subredditdrama's out-links? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "736beafb-3cb2-48bc-948a-ac1d8d18d3c7",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Your Turn!\n",
    "\n",
    "plt.xticks(rotation=90)\n",
    "plt.xlabel('time')\n",
    "plt.ylabel('out-link frequency')\n",
    "plt.title('/r/subredditdrama out-link frequency over time')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db1ebee3-0803-4f4c-96ca-2bb422a75df4",
   "metadata": {},
   "source": [
    "Recall that links can have sentiment attached to them, either positive (1) or negative (-1). Let's look at the number of positive and negative out-links (so plot each one in a different color in the same plot) for /r/subredditdrama over the entire time period. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b556d591-2c4b-4db6-af5f-4f96247d4ff0",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Your Turn!\n",
    "\n",
    "plt.xticks(rotation=90)\n",
    "plt.xlabel('time')\n",
    "plt.ylabel('out-link frequency')\n",
    "plt.title('/r/subredditdrama out-link frequency over time by sentiment')\n",
    "plt.legend()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0072b5f5-30b8-47b8-a545-a2db68ce3e75",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Now let's turn to temporal changes in the network. Can you plot the network's in- and out-degree distributions for 2014, 2015, and 2016? Hint: the [`EXTRACT`](https://www.w3schools.com/Sql/func_mysql_extract.asp) function in SQL may come in handy here. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40f47d92-1bbb-46b0-b19e-992011a1d1a8",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "fig, ax = plt.subplots(2,3,figsize=(10,4),dpi=150)\n",
    "\n",
    "# Your Turn!\n",
    "for ix, year in enumerate([2014, 2015, 2016]):\n",
    "    in_degree_bin_edges, in_degree_freqs = degree_distribution(in_degree_list)\n",
    "    out_degree_bin_edges, out_degree_freqs = degree_distribution(out_degree_list)\n",
    "    ax[0, ix].loglog(in_degree_bin_edges, in_degree_freqs, 'o')\n",
    "    ax[0, ix].set_title('in-degree {}'.format(str(year)))\n",
    "    ax[1, ix].loglog(out_degree_bin_edges, out_degree_freqs, 'o')\n",
    "    ax[1, ix].set_title('out-degree {}'.format(str(year)))\n",
    "plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cbbd069-4924-438a-ac63-af152602e2df",
   "metadata": {},
   "source": [
    "### Local Clustering\n",
    "Pick a subreddit -- any subreddit (I suggest picking one that has a lot of links to keep things interesting). Can you figure out what its local clustering coefficient is in 2014, 2015, and 2016? Let's assume the graph is unweighted for now, so one hyperlink from subreddit A to subreddit B in 2014 is the same as 10 separate hyperlinks from A to B in the same year. We will also assume the graph is undirected for this exercise. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a18e0807-3836-4583-a60a-bc5d59fc2fd1",
   "metadata": {
    "mystnb": {
     "remove_code_outputs": true
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Your Turn\n",
    "def compute_clustering(subreddit_name, engine):\n",
    "    \"\"\"\n",
    "    Given the name of a subreddit and a SQL connection engine,\n",
    "    return the local clustering coefficient of the subreddit,\n",
    "    considering the network as undirected, OR \n",
    "    raise appropriate errors.\n",
    "\n",
    "    subreddit_name: string; valid name of a subreddit\n",
    "    engine: sqlalchemy engine object\n",
    "\n",
    "    returns: the local clustering coefficient of the subreddit\n",
    "    \"\"\"\n",
    "\n",
    "    pass\n",
    "compute_clustering('amitheasshole', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f79e3539-26b0-459a-a374-5a31ddc32a51",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}