EDA_pyspark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='magenta'>Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "reddit = spark.read.parquet(\"/var/reddit-parquet\") # these records are posts/Submissions (in PRAW) can tell by existence of 'num_comments', 'name', 'locked' & other attributes that are common to posts/Submissions "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyspark.sql.dataframe.DataFrame"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(reddit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(reddit.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- _corrupt_record: string (nullable = true)\n",
      " |-- adserver_click_url: string (nullable = true)\n",
      " |-- adserver_imp_pixel: string (nullable = true)\n",
      " |-- approved_by: string (nullable = true)\n",
      " |-- archived: boolean (nullable = true)\n",
      " |-- author: string (nullable = true)\n",
      " |-- author_flair_css_class: string (nullable = true)\n",
      " |-- author_flair_text: string (nullable = true)\n",
      " |-- banned_by: string (nullable = true)\n",
      " |-- body: string (nullable = true)\n",
      " |-- body_html: string (nullable = true)\n",
      " |-- clicked: boolean (nullable = true)\n",
      " |-- controversiality: long (nullable = true)\n",
      " |-- created: long (nullable = true)\n",
      " |-- created_utc: string (nullable = true)\n",
      " |-- disable_comments: boolean (nullable = true)\n",
      " |-- distinguished: string (nullable = true)\n",
      " |-- domain: string (nullable = true)\n",
      " |-- downs: long (nullable = true)\n",
      " |-- edited: string (nullable = true)\n",
      " |-- from: string (nullable = true)\n",
      " |-- from_id: string (nullable = true)\n",
      " |-- from_kind: string (nullable = true)\n",
      " |-- gilded: long (nullable = true)\n",
      " |-- hidden: boolean (nullable = true)\n",
      " |-- hide_score: boolean (nullable = true)\n",
      " |-- href_url: string (nullable = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- imp_pixel: string (nullable = true)\n",
      " |-- is_self: boolean (nullable = true)\n",
      " |-- likes: boolean (nullable = true)\n",
      " |-- link_flair_css_class: string (nullable = true)\n",
      " |-- link_flair_text: string (nullable = true)\n",
      " |-- link_id: string (nullable = true)\n",
      " |-- locked: boolean (nullable = true)\n",
      " |-- media: string (nullable = true)\n",
      " |-- media_embed: struct (nullable = true)\n",
      " |    |-- content: string (nullable = true)\n",
      " |    |-- height: long (nullable = true)\n",
      " |    |-- scrolling: boolean (nullable = true)\n",
      " |    |-- width: long (nullable = true)\n",
      " |-- mobile_ad_url: string (nullable = true)\n",
      " |-- mod_reports: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- num_comments: long (nullable = true)\n",
      " |-- num_reports: long (nullable = true)\n",
      " |-- parent_id: string (nullable = true)\n",
      " |-- permalink: string (nullable = true)\n",
      " |-- post_hint: string (nullable = true)\n",
      " |-- preview: struct (nullable = true)\n",
      " |    |-- images: array (nullable = true)\n",
      " |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |-- id: string (nullable = true)\n",
      " |    |    |    |-- resolutions: array (nullable = true)\n",
      " |    |    |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |    |    |-- height: long (nullable = true)\n",
      " |    |    |    |    |    |-- url: string (nullable = true)\n",
      " |    |    |    |    |    |-- width: long (nullable = true)\n",
      " |    |    |    |-- source: struct (nullable = true)\n",
      " |    |    |    |    |-- height: long (nullable = true)\n",
      " |    |    |    |    |-- url: string (nullable = true)\n",
      " |    |    |    |    |-- width: long (nullable = true)\n",
      " |    |    |    |-- variants: struct (nullable = true)\n",
      " |    |    |    |    |-- gif: struct (nullable = true)\n",
      " |    |    |    |    |    |-- resolutions: array (nullable = true)\n",
      " |    |    |    |    |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |    |    |    |    |-- height: long (nullable = true)\n",
      " |    |    |    |    |    |    |    |-- url: string (nullable = true)\n",
      " |    |    |    |    |    |    |    |-- width: long (nullable = true)\n",
      " |    |    |    |    |    |-- source: struct (nullable = true)\n",
      " |    |    |    |    |    |    |-- height: long (nullable = true)\n",
      " |    |    |    |    |    |    |-- url: string (nullable = true)\n",
      " |    |    |    |    |    |    |-- width: long (nullable = true)\n",
      " |    |    |    |    |-- mp4: struct (nullable = true)\n",
      " |    |    |    |    |    |-- resolutions: array (nullable = true)\n",
      " |    |    |    |    |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |    |    |    |    |-- height: long (nullable = true)\n",
      " |    |    |    |    |    |    |    |-- url: string (nullable = true)\n",
      " |    |    |    |    |    |    |    |-- width: long (nullable = true)\n",
      " |    |    |    |    |    |-- source: struct (nullable = true)\n",
      " |    |    |    |    |    |    |-- height: long (nullable = true)\n",
      " |    |    |    |    |    |    |-- url: string (nullable = true)\n",
      " |    |    |    |    |    |    |-- width: long (nullable = true)\n",
      " |-- promoted: boolean (nullable = true)\n",
      " |-- quarantine: boolean (nullable = true)\n",
      " |-- removal_reason: string (nullable = true)\n",
      " |-- replies: string (nullable = true)\n",
      " |-- report_reasons: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- retrieved_on: long (nullable = true)\n",
      " |-- saved: boolean (nullable = true)\n",
      " |-- score: long (nullable = true)\n",
      " |-- score_hidden: boolean (nullable = true)\n",
      " |-- secure_media: struct (nullable = true)\n",
      " |    |-- content: string (nullable = true)\n",
      " |    |-- event_id: string (nullable = true)\n",
      " |    |-- height: long (nullable = true)\n",
      " |    |-- oembed: struct (nullable = true)\n",
      " |    |    |-- author_name: string (nullable = true)\n",
      " |    |    |-- author_url: string (nullable = true)\n",
      " |    |    |-- cache_age: long (nullable = true)\n",
      " |    |    |-- description: string (nullable = true)\n",
      " |    |    |-- height: long (nullable = true)\n",
      " |    |    |-- html: string (nullable = true)\n",
      " |    |    |-- mean_alpha: double (nullable = true)\n",
      " |    |    |-- provider_name: string (nullable = true)\n",
      " |    |    |-- provider_url: string (nullable = true)\n",
      " |    |    |-- thumbnail_height: long (nullable = true)\n",
      " |    |    |-- thumbnail_url: string (nullable = true)\n",
      " |    |    |-- thumbnail_width: long (nullable = true)\n",
      " |    |    |-- title: string (nullable = true)\n",
      " |    |    |-- type: string (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |    |    |-- version: string (nullable = true)\n",
      " |    |    |-- width: long (nullable = true)\n",
      " |    |-- type: string (nullable = true)\n",
      " |    |-- width: long (nullable = true)\n",
      " |-- secure_media_embed: struct (nullable = true)\n",
      " |    |-- content: string (nullable = true)\n",
      " |    |-- height: long (nullable = true)\n",
      " |    |-- scrolling: boolean (nullable = true)\n",
      " |    |-- width: long (nullable = true)\n",
      " |-- selftext: string (nullable = true)\n",
      " |-- selftext_html: string (nullable = true)\n",
      " |-- stickied: boolean (nullable = true)\n",
      " |-- subreddit: string (nullable = true)\n",
      " |-- subreddit_id: string (nullable = true)\n",
      " |-- third_party_tracking: string (nullable = true)\n",
      " |-- third_party_tracking_2: string (nullable = true)\n",
      " |-- thumbnail: string (nullable = true)\n",
      " |-- title: string (nullable = true)\n",
      " |-- ups: long (nullable = true)\n",
      " |-- url: string (nullable = true)\n",
      " |-- user_reports: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- over_18: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "reddit.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%time\n",
    "# record_count = reddit.count()\n",
    "record_count = 2859977347 #1% of this is +28 million"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ### The DataFrame we created has a fairly large number of columns (70), is deeply nested in several instances (up to 8 layers deep), and contains a significant number of records (+2.8 billion)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select a subset of columns for EDA\n",
    "\n",
    "these_cols = [\n",
    "    \"id\",\n",
    "    \"parent_id\",\n",
    "    \"subreddit\",\n",
    "    \"author\",\n",
    "    \"created_utc\",\n",
    "    \"body\",\n",
    "    \"num_comments\",\n",
    "    \"score\"\n",
    "] # \"created\" is not as populated as created_utc, better to use created_utc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ### For our initial EDA we primarily care about the columns indicated above so we'll subset the data appropriately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('id', 'string'),\n",
       " ('parent_id', 'string'),\n",
       " ('subreddit', 'string'),\n",
       " ('author', 'string'),\n",
       " ('created_utc', 'string'),\n",
       " ('body', 'string'),\n",
       " ('num_comments', 'bigint'),\n",
       " ('score', 'bigint')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_df = reddit.select(these_cols)\n",
    "cols_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ### It's worthwhile to check the data types of the columns. You may notice that \"created_utc\" is a string. It actually contains the created date in Unix time/Epoch time (the number of seconds since January 1, 1970). We'll want to convert that to a more human readable format."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='magenta'>Created date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import from_unixtime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add new col showing just created year\n",
    "cols_df = cols_df.withColumn(\n",
    "    \"created_utc_year\", \n",
    "    from_unixtime(\n",
    "        cols_df[\"created_utc\"], \n",
    "        \"yyyy\" # full timestamp: yyyy-MM-dd HH:mm:ss.SS\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+---------+\n",
      "|created_utc_year|    count|\n",
      "+----------------+---------+\n",
      "|            null|        1|\n",
      "|            2005|     1086|\n",
      "|            2006|   419341|\n",
      "|            2007|  2745064|\n",
      "|            2008|  9773673|\n",
      "|            2009| 23726352|\n",
      "|            2010| 55571522|\n",
      "|            2011|138398080|\n",
      "|            2012|289689090|\n",
      "|            2013|441953216|\n",
      "|            2014|584765776|\n",
      "|            2015|738997386|\n",
      "|            2016|573936760|\n",
      "+----------------+---------+\n",
      "\n",
      "CPU times: user 18.8 ms, sys: 7.56 ms, total: 26.3 ms\n",
      "Wall time: 1min 1s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "years_df = cols_df.groupby(\"created_utc_year\").count()\n",
    "years_df.orderBy(\"created_utc_year\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ### The data covers 11 years from 2005 to 2016. Reddit was founded in 2005 and we can see a hint of the platform's growth just in terms of the number of posts in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "null_count = cols_df[cols_df[\"body\"].isNull()].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "279383793"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.77"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round((float(null_count)/record_count)*100,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ### While there are a large number of records with null values in \"body\" (+279 million), this is only ~10% of the overall data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_df = cols_df[\n",
    "    (cols_df[\"created_utc_year\"].isNotNull())&\n",
    "    (cols_df[\"body\"].isNotNull())\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%time\n",
    "# clean_df.count() # 2,580,593,554"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ### We are still left with +2.5 billion records!\n",
    "> ### However, using all the records would be excessive and time-intensive.\n",
    "> ### A stratified, one percent sample of the total should be sufficient. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# stratified sample\n",
    "one_perct_df = clean_df.sampleBy(\n",
    "    col = \"created_utc_year\",\n",
    "    fractions = fraction_dict\n",
    ")\n",
    "\n",
    "one_perct_df.write.parquet(\"one_perct_sample.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# END"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}