EDA_pyspark_one_perct.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='magenta'>EDA on Reddit 1% sample </font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "one_perct_df = spark.read.parquet(\"one_perct_sample.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The data is a one percent sample from the Reddit parquet file containing +2.8 billion records.\n",
    "> The records are posts/Submissions (in PRAW). This is evident due to the existence of 'num_comments', 'name', 'locked' & other attributes that are common to posts/Submissions "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'parent_id',\n",
       " 'subreddit',\n",
       " 'author',\n",
       " 'created_utc',\n",
       " 'body',\n",
       " 'num_comments',\n",
       " 'score',\n",
       " 'created_utc_year',\n",
       " 'created_utc_yearMonthDay',\n",
       " 'body_len']"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "one_perct_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "309811130"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "one_perct_df.count() #~300+ million"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='magenta'>Deleted authors and bots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|              author|count|\n",
      "+--------------------+-----+\n",
      "|         conspirobot|68608|\n",
      "| Late_Night_Grumbler|38604|\n",
      "|throwthrowawaytothee|37615|\n",
      "|        Franciscouzo|24230|\n",
      "|        morbiusgreen|23352|\n",
      "|              Lots42|22807|\n",
      "|             hit_bot|21160|\n",
      "|                -rix|20748|\n",
      "|          pixis-4950|20559|\n",
      "|         UnluckyLuke|20541|\n",
      "|          amici_ursi|19565|\n",
      "|              matts2|18704|\n",
      "|           TrollaBot|17049|\n",
      "|     NoMoreNicksLeft|16304|\n",
      "|            iam4real|15904|\n",
      "|          raddit-bot|15773|\n",
      "|            gifv-bot|15439|\n",
      "|      atomicimploder|15284|\n",
      "|            G_Morgan|15229|\n",
      "|        noeatnosleep|14783|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "one_perct_df.groupby(\"author\").count().orderBy(\"count\", ascending = False).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The top author is a collection of deleted authors and most of the others appear to be bots."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------+\n",
      "|      body|   count|\n",
      "+----------+--------+\n",
      "| [deleted]|19819730|\n",
      "| [removed]| 1711094|\n",
      "|   Thanks!|    4624|\n",
      "|      Yes.|    4170|\n",
      "|       No.|    3775|\n",
      "|Thank you!|    3130|\n",
      "|       lol|    3035|\n",
      "|         .|    2747|\n",
      "|       Yes|    2408|\n",
      "|        :)|    2324|\n",
      "|      Why?|    1973|\n",
      "|        no|    1951|\n",
      "|       yes|    1925|\n",
      "|     What?|    1920|\n",
      "|     &lt;3|    1860|\n",
      "|        :(|    1825|\n",
      "|        No|    1819|\n",
      "|Thank you.|    1782|\n",
      "|       wat|    1709|\n",
      "|     Nope.|    1429|\n",
      "+----------+--------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "deleted_authors_df = one_perct_df.filter(one_perct_df.author == \"[deleted]\")\n",
    "deleted_authors_df.groupby(\"body\").count().orderBy(\"count\", ascending = False).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The clear majority of the posts/Submissions by deleted authors have also been deleted and won't be useful in our subsequent analysis. Let's filter both the deleted users and the bots out so our data is smaller and more representative of the content posted directly by human end users."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_to_remove = [\n",
    "    \"[deleted]\",\n",
    "    \"AutoModerator\", #bot that enforces Subreddit rules (?)\n",
    "    \"autotldr\",\n",
    "    \"photography_bot\",\n",
    "#     \"conspirobot\", #not a bot\n",
    "    \"ModerationLog\", #suspended, unclear if bot\n",
    "    \"TweetPoster\",\n",
    "    \"tweet_poster\",\n",
    "    \"autowikibot\",\n",
    "    \"imgurtranscriber\",\n",
    "    \"MTGCardFetcher\",\n",
    "    \"PoliticBot\", #suspended, unclear if bot\n",
    "    \"RPBot\",\n",
    "    \"dogetipbot\",\n",
    "    \"qkme_transcriber\", #mostly bot\n",
    "    \"TweetsInCommentsBot\",\n",
    "    \"ImagesOfNetwork\",\n",
    "    \"TotesMessenger\",\n",
    "    \"havoc_bot\",\n",
    "    \"User_Simulator\",\n",
    "    \"PornOverlord\",\n",
    "    \"PriceZombie\",\n",
    "    \"CaptionBot\",\n",
    "    \"WritingPromptsRobot\",\n",
    "#     \"raddit-bot\", #suspended, unclear if bot\n",
    "#     \"gifv-bot\", #suspended, unclear if bot\n",
    "    \"MovieGuide\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36996048"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "one_perct_df.filter(one_perct_df.author.isin(authors_to_remove)).count() #36902074"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> This step will remove +36 million records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "one_perct_df = one_perct_df.filter(~one_perct_df.author.isin(authors_to_remove))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "272815082"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "one_perct_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|              author|count|\n",
      "+--------------------+-----+\n",
      "|         conspirobot|68608|\n",
      "| Late_Night_Grumbler|38604|\n",
      "|throwthrowawaytothee|37615|\n",
      "|        Franciscouzo|24230|\n",
      "|        morbiusgreen|23352|\n",
      "|              Lots42|22807|\n",
      "|             hit_bot|21160|\n",
      "|                -rix|20748|\n",
      "|          pixis-4950|20559|\n",
      "|         UnluckyLuke|20541|\n",
      "|          amici_ursi|19565|\n",
      "|              matts2|18704|\n",
      "|           TrollaBot|17049|\n",
      "|     NoMoreNicksLeft|16304|\n",
      "|            iam4real|15904|\n",
      "|          raddit-bot|15773|\n",
      "|            gifv-bot|15439|\n",
      "|      atomicimploder|15284|\n",
      "|            G_Morgan|15229|\n",
      "|        noeatnosleep|14783|\n",
      "+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "one_perct_df.groupby(\"author\").count().orderBy(\"count\", ascending = False).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> New list of top authors by number of posts/Submissions. The top 20 was checked manually for bots."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Counts of posts/Submissions per year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+--------+\n",
      "|created_utc_year|   count|\n",
      "+----------------+--------+\n",
      "|            2005|     101|\n",
      "|            2006|   36084|\n",
      "|            2007|  197737|\n",
      "|            2008|  585008|\n",
      "|            2009| 1632953|\n",
      "|            2010| 4540038|\n",
      "|            2011|11862253|\n",
      "|            2012|25746724|\n",
      "|            2013|40910031|\n",
      "|            2014|56079460|\n",
      "|            2015|74347450|\n",
      "|            2016|57035162|\n",
      "+----------------+--------+\n",
      "\n",
      "CPU times: user 15.5 ms, sys: 122 µs, total: 15.6 ms\n",
      "Wall time: 4.82 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "one_perct_df.groupby(\"created_utc_year\").count().orderBy(\"created_utc_year\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> This 1% stratified sample is off by ~10%. The actual yearly counts in the sample are greater than the expected yearly counts by an order of magnitude."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='magenta'>Analysze Subreddits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import from_unixtime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "one_perct_df = one_perct_df.withColumn(\n",
    "    \"created_utc_yearMonthDay\", \n",
    "    from_unixtime(\n",
    "        one_perct_df[\"created_utc\"], \n",
    "        \"yyyy-MM-dd\" # full timestamp: yyyy-MM-dd HH:mm:ss.SS\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "one_perct_df = one_perct_df.withColumn(\n",
    "    \"body_len\",\n",
    "    length(one_perct_df[\"body\"])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+------------------+\n",
      "|      subreddit|        avg(count)|\n",
      "+---------------+------------------+\n",
      "|      AskReddit|        8224.86656|\n",
      "|leagueoflegends|2028.0008517887563|\n",
      "|          funny|1969.6778027626085|\n",
      "|           pics|1801.6187141947803|\n",
      "|  AdviceAnimals| 1565.850047755492|\n",
      "|     The_Donald| 1517.828947368421|\n",
      "|            nfl|1289.5458612975392|\n",
      "|   pcmasterrace|1249.5161048689138|\n",
      "|         gaming|1198.7903275176002|\n",
      "|       politics|1142.9749547374774|\n",
      "+---------------+------------------+\n",
      "only showing top 10 rows\n",
      "\n",
      "CPU times: user 34.5 ms, sys: 129 µs, total: 34.6 ms\n",
      "Wall time: 17.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "yearsMonthDay_df = one_perct_df.groupby([\"subreddit\",\"created_utc_yearMonthDay\"]).count()\n",
    "\n",
    "avg_post_df = yearsMonthDay_df.groupby(\"subreddit\").avg()\n",
    "avg_post_df.orderBy(\"avg(count)\", ascending = False).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Average number of posts/Submissions per Subreddit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+----------+\n",
      "|        subreddit|max(count)|\n",
      "+-----------------+----------+\n",
      "|        AskReddit|     23594|\n",
      "|         politics|     16580|\n",
      "|              nfl|     13888|\n",
      "|     pcmasterrace|     12503|\n",
      "|           gaming|     11901|\n",
      "|millionairemakers|     11696|\n",
      "|    SquaredCircle|     11266|\n",
      "|              nba|     11156|\n",
      "|              CFB|     10631|\n",
      "|        pokemongo|     10509|\n",
      "+-----------------+----------+\n",
      "only showing top 10 rows\n",
      "\n",
      "CPU times: user 19.2 ms, sys: 0 ns, total: 19.2 ms\n",
      "Wall time: 13.6 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "max_post_df = yearsMonthDay_df.groupby(\"subreddit\").max()\n",
    "max_post_df.orderBy(\"max(count)\", ascending = False).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Top 10 Subreddits based on maximum number of posts/Submissions per Subreddit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "these_subreddits = [\n",
    "    \"worldnews\", \"news\", \"AskCulinary\",\n",
    "    \"AskHistorians\", \"howto\", \"todayilearned\",\n",
    "    \"conspiracy\", \"MilitaryConspiracy\", \"PedoGate\",\n",
    "    \"FalseFlagWatch\", \"skeptic\", \"politicalfactchecking\",\n",
    "    \"MensRights\", \"MRActivism\", \"glasgow\",\n",
    "    \"melbourne\", \"travel\", \"photography\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "subreddit_created_df = yearsMonthDay_df.filter(yearsMonthDay_df.subreddit.isin(these_subreddits))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+------------------------+-----+\n",
      "|  subreddit|created_utc_yearMonthDay|count|\n",
      "+-----------+------------------------+-----+\n",
      "|      howto|              2014-05-02|   10|\n",
      "|AskCulinary|              2016-06-20|   33|\n",
      "|    skeptic|              2016-07-30|   37|\n",
      "+-----------+------------------------+-----+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "subreddit_created_df.show(3) #df shows the number of posts/Submissions created in a Subreddit on a given day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate submission count IQRs for select Subreddits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "worldnews [196.0, 595.0, 1565.0]\n",
      "news [31.0, 132.0, 1096.0]\n",
      "AskCulinary [20.0, 27.0, 35.0]\n",
      "AskHistorians [48.0, 62.0, 81.0]\n",
      "howto [2.0, 5.0, 9.0]\n",
      "todayilearned [182.0, 1084.0, 1622.0]\n",
      "conspiracy [23.0, 113.0, 234.0]\n",
      "MilitaryConspiracy [1.0, 1.0, 1.0]\n",
      "PedoGate []\n",
      "FalseFlagWatch [1.0, 1.0, 1.0]\n",
      "skeptic [16.0, 28.0, 41.0]\n",
      "politicalfactchecking [1.0, 2.0, 3.0]\n",
      "MensRights [28.0, 108.0, 153.0]\n",
      "MRActivism [1.0, 1.0, 2.0]\n",
      "glasgow [3.0, 7.0, 12.0]\n",
      "melbourne [13.0, 34.0, 86.0]\n",
      "travel [23.0, 49.0, 76.0]\n",
      "photography [32.0, 83.0, 124.0]\n"
     ]
    }
   ],
   "source": [
    "IQR_dict = {}\n",
    "for entry in these_subreddits:\n",
    "    temp_df = subreddit_created_df.filter(subreddit_created_df.subreddit == entry)\n",
    "    IQR_result = temp_df.approxQuantile(col = \"count\", probabilities = [0.25, 0.5, 0.75], relativeError = 0)\n",
    "    print(entry, IQR_result)\n",
    "    IQR_dict[entry] = IQR_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IQR_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate submission score IQRs for select Subreddits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+-----+-----+\n",
      "|      subreddit|score|count|\n",
      "+---------------+-----+-----+\n",
      "|          texas|    6| 1113|\n",
      "|leagueoflegends|   17|11783|\n",
      "|          vinyl|    2|32547|\n",
      "+---------------+-----+-----+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "subreddit_score_df = one_perct_df.groupby([\"subreddit\",\"score\"]).count() #df shows the counts of scores awarded to posts/Submissions in a Subreddit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "worldnews [1.0, 3.0, 12.0]\n",
      "news [1.0, 3.0, 10.0]\n",
      "AskCulinary [1.0, 4.0, 27.0]\n",
      "AskHistorians [1.0, 2.0, 10.0]\n",
      "howto [1.0, 3.0, 20.0]\n",
      "todayilearned [1.0, 3.0, 14.0]\n",
      "conspiracy [1.0, 3.0, 17.0]\n",
      "MilitaryConspiracy [2.0, 3.0, 14.0]\n",
      "PedoGate []\n",
      "FalseFlagWatch [1.0, 5.0, 33.0]\n",
      "skeptic [1.0, 5.0, 30.0]\n",
      "politicalfactchecking [1.0, 3.0, 16.0]\n",
      "MensRights [1.0, 4.0, 24.0]\n",
      "MRActivism [2.0, 3.0, 16.0]\n",
      "glasgow [1.0, 3.0, 32.0]\n",
      "melbourne [1.0, 5.0, 44.0]\n",
      "travel [1.0, 3.0, 16.0]\n",
      "photography [1.0, 4.0, 27.0]\n"
     ]
    }
   ],
   "source": [
    "score_dict = {}\n",
    "for entry in these_subreddits:\n",
    "    temp_df = subreddit_score_df.filter(subreddit_score_df.subreddit == entry)\n",
    "    score_result = temp_df.approxQuantile(col = \"count\", probabilities = [0.25, 0.5, 0.75], relativeError = 0)\n",
    "    print(entry, score_result)\n",
    "    score_dict[entry] = score_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "# score_dict"
   ]
  },
  {
   "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.4.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}