project_starter_script.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "45e24818-874c-4a1c-bbec-31995beead19",
   "metadata": {},
   "source": [
    "# Reddit Project Starter Script"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31472461-d990-4b83-a1ee-79e9f41f532e",
   "metadata": {},
   "source": [
    "### Putting data into your own bucket"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef68cb6d-6297-4fe7-9c2f-29370fa7a5e5",
   "metadata": {},
   "source": [
    "In the following cell you can view the list of subreddits:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "64ffa23c-dcb7-400f-9570-81d2118e7eec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                           PRE ACTrade/\n",
      "                           PRE Coronavirus/\n",
      "                           PRE CryptoCurrency/\n",
      "                           PRE MMA/\n",
      "                           PRE Minecraft/\n",
      "                           PRE NoStupidQuestions/\n",
      "                           PRE PoliticalCompassMemes/\n",
      "                           PRE PublicFreakout/\n",
      "                           PRE Showerthoughts/\n",
      "                           PRE Superstonk/\n",
      "                           PRE apexlegends/\n",
      "                           PRE conspiracy/\n",
      "                           PRE gaming/\n",
      "                           PRE interestingasfuck/\n",
      "                           PRE leagueoflegends/\n",
      "                           PRE movies/\n",
      "                           PRE nba/\n",
      "                           PRE news/\n",
      "                           PRE politics/\n",
      "                           PRE relationship_advice/\n",
      "                           PRE soccer/\n",
      "                           PRE stocks/\n",
      "                           PRE teenagers/\n",
      "                           PRE unpopularopinion/\n",
      "                           PRE wallstreetbets/\n",
      "                           PRE worldnews/\n"
     ]
    }
   ],
   "source": [
    "!aws s3 ls s3://bigdatateaching/reddit/subreddits/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "126e3bc1-53f5-4e01-90ba-f0206e1693e8",
   "metadata": {},
   "source": [
    "In the following cell, you will copy your assigned subreddit from the central teaching bucket to your own persaonl bucket. This cell MUST only be executed once per account!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3606e95b-18ea-4075-abcc-1dce0b07e203",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/_SUCCESS to s3://aa2464-labdata1/reddit/_SUCCESS\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=201908/part-00005-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=201908/part-00005-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=201911/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=201911/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=201910/part-00005-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=201910/part-00005-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=201907/part-00015-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=201907/part-00015-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=201912/part-00014-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=201912/part-00014-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=201909/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=201909/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202001/part-00006-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202001/part-00006-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202002/part-00012-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202002/part-00012-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202003/part-00001-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202003/part-00001-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202005/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202005/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202004/part-00017-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202004/part-00017-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202006/part-00004-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202006/part-00004-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202007/part-00004-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202007/part-00004-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202008/part-00011-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202008/part-00011-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202010/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202010/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202009/part-00000-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202009/part-00000-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202011/part-00004-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202011/part-00004-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202012/part-00016-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202012/part-00016-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202102/part-00017-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202102/part-00017-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202101/part-00000-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202101/part-00000-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202103/part-00009-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202103/part-00009-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202105/part-00016-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202105/part-00016-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202106/part-00002-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202106/part-00002-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n",
      "copy: s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ym_partition=202104/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet to s3://aa2464-labdata1/reddit/ym_partition=202104/part-00010-14a46b6e-136a-46a3-9a88-8efc6285e2aa.c000.snappy.parquet\n"
     ]
    }
   ],
   "source": [
    "!aws s3 cp s3://bigdatateaching/reddit/subreddits/NoStupidQuestions/ s3://aa2464-labdata2/reddit --recursive"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "598eb41d-197c-4f0d-b207-a229e211d97c",
   "metadata": {},
   "source": [
    "### Reading your entire dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b9dc1d0a-0264-4efe-86d4-3d0756c99c9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9db156f0-e1b0-4dad-99b2-a0a247af9e68",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark.sql.functions as f\n",
    "from pyspark.sql.functions import col, lit\n",
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3a82ccb4-e218-4821-a2fd-784909105238",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "22/04/02 03:20:07 WARN Client: Neither spark.yarn.jars nor spark.yarn.archive is set, falling back to uploading libraries under SPARK_HOME.\n"
     ]
    }
   ],
   "source": [
    "spark = SparkSession.builder.appName(\"reddit\").getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6564496-f89d-4cd0-8920-1b9ab3448ea1",
   "metadata": {},
   "source": [
    "Make sure your SparkSession is active:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "705a305d-7dbb-4c08-a7f7-0fee39840510",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://ip-172-31-21-128.ec2.internal:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.0.0-amzn-0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>yarn</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>reddit</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7fd8fc4ef4d0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "edcfea00-1b0c-4c76-94c8-672551d39aba",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_full = spark.read.parquet('s3://aa2464-labdata1/reddit')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b555192-8c1a-4d13-8e63-66c10b429b0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_full = df_full.withColumn(\"filename\",input_file_name())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5cd34f43-88b7-4433-b687-b074a3995c58",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22/04/01 22:49:53 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.\n",
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "8013371"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_full.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4674dd19-4610-43bf-9add-be638eea625f",
   "metadata": {},
   "source": [
    "### Reading one month worth of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ef20a13c-78f1-4e49-b1a2-4e79e885cb19",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                           PRE ym_partition=201907/\n",
      "                           PRE ym_partition=201908/\n",
      "                           PRE ym_partition=201909/\n",
      "                           PRE ym_partition=201910/\n",
      "                           PRE ym_partition=201911/\n",
      "                           PRE ym_partition=201912/\n",
      "                           PRE ym_partition=202001/\n",
      "                           PRE ym_partition=202002/\n",
      "                           PRE ym_partition=202003/\n",
      "                           PRE ym_partition=202004/\n",
      "                           PRE ym_partition=202005/\n",
      "                           PRE ym_partition=202006/\n",
      "                           PRE ym_partition=202007/\n",
      "                           PRE ym_partition=202008/\n",
      "                           PRE ym_partition=202009/\n",
      "                           PRE ym_partition=202010/\n",
      "                           PRE ym_partition=202011/\n",
      "                           PRE ym_partition=202012/\n",
      "                           PRE ym_partition=202101/\n",
      "                           PRE ym_partition=202102/\n",
      "                           PRE ym_partition=202103/\n",
      "                           PRE ym_partition=202104/\n",
      "                           PRE ym_partition=202105/\n",
      "                           PRE ym_partition=202106/\n",
      "2022-04-01 22:46:54          0 _SUCCESS\n"
     ]
    }
   ],
   "source": [
    "!aws s3 ls s3://aa2464-labdata1/reddit/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c1ea9584-1436-4b0d-a9ef-4d33cb0b41bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df = spark.read.parquet('s3://aa2464-labdata1/reddit/ym_partition=202106')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "01a120e2-0219-4433-83a7-4cb29aa301c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from  pyspark.sql.functions import input_file_name\n",
    "df = df.withColumn(\"filename\",input_file_name())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d582b200-8ce3-44a5-ae39-cceb9af83c46",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "392178"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "add5d9f8-b75b-45e4-a290-77afb2762332",
   "metadata": {},
   "source": [
    "### Developing on a small piece of your data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "19a87477-c0d5-4563-a82a-4f0e7b57c0ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_small = df.limit(10000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cf34c1bc-9c61-4423-b1ee-efa2cc19a1d3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "10000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_small.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e9d987ed-eeb6-4f6d-aa84-84d034682d48",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 30:=====================================================>  (20 + 1) / 21]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+----------------+---------------+------------------+-----------------------------+----------------------+---------------------+------------------------+-----------------+-----------------------+-----------------+---------------+--------------------+--------------+--------+--------------------+--------+------------+---------+-------------------------------+----------------+------------+----------------+-----------+-------------+------+------+--------+-------+------------+---------+------+---------+----------+--------------------+-----------+--------------+------------+-----+------------+--------+------------+-----------------------+--------------+----------------+---------------------+--------------+--------------+--------+--------------+--------------------+\n",
      "|all_awardings|associated_award|         author|author_created_utc|author_flair_background_color|author_flair_css_class|author_flair_richtext|author_flair_template_id|author_flair_text|author_flair_text_color|author_flair_type|author_fullname|author_patreon_flair|author_premium|awarders|                body|can_gild|can_mod_post|collapsed|collapsed_because_crowd_control|collapsed_reason|comment_type|controversiality|created_utc|distinguished|edited|gilded|gildings|     id|is_submitter|  link_id|locked|no_follow| parent_id|           permalink|quarantined|removal_reason|retrieved_on|score|send_replies|stickied|subreddit_id|subreddit_name_prefixed|subreddit_type|top_awarded_type|total_awards_received|treatment_tags|author_cakeday|editable|media_metadata|            filename|\n",
      "+-------------+----------------+---------------+------------------+-----------------------------+----------------------+---------------------+------------------------+-----------------+-----------------------+-----------------+---------------+--------------------+--------------+--------+--------------------+--------+------------+---------+-------------------------------+----------------+------------+----------------+-----------+-------------+------+------+--------+-------+------------+---------+------+---------+----------+--------------------+-----------+--------------+------------+-----+------------+--------+------------+-----------------------+--------------+----------------+---------------------+--------------+--------------+--------+--------------+--------------------+\n",
      "|           []|            None| TheOneWhoMixes|     1.541815418E9|                         null|                  null|                   []|                    null|             null|                   null|             text|    t2_2kkmoykj|               false|         false|    <NA>|Uhm, are you not ...|    true|       false|    false|                           null|            null|        null|               0| 1623515113|         None|  null|     0|      {}|h1j1xkq|       false|t3_ny8xrt| false|     true|t1_h1ivvgc|/r/NoStupidQuesti...|      false|          null|        null|    1|        true|   false|    t5_2w844|                   null|        public|            null|                    0|            []|          null|    null|           nan|s3://aa2464-labda...|\n",
      "|           []|            None|  MidLaneCrisis|     1.421119124E9|                         null|                  null|                   []|                    null|             null|                   null|             text|       t2_koj29|               false|         false|    <NA>|not a southern i see|    true|       false|    false|                           null|            null|        null|               0| 1623518425|         None|  null|     0|      {}|h1j8nhf|       false|t3_ny7ir8| false|    false|t1_h1j86jn|/r/NoStupidQuesti...|      false|          null|        null|   10|        true|   false|    t5_2w844|                   null|        public|            null|                    0|            []|          null|    null|           nan|s3://aa2464-labda...|\n",
      "|           []|            None|     DRSteelers|     1.469482202E9|                         null|                  null|                   []|                    null|             null|                   null|             text|       t2_zt12v|               false|         false|    <NA>|is there a way I ...|    true|       false|    false|                           null|            null|        null|               0| 1623708238|         None|  null|     0|      {}|h1s43vn|        true|t3_nzsz3w| false|     true|t1_h1r9ms7|/r/NoStupidQuesti...|      false|          null|        null|    1|        true|   false|    t5_2w844|                   null|        public|            null|                    0|            []|          null|    null|           nan|s3://aa2464-labda...|\n",
      "|           []|            None|orangecrushjedi|     1.521876758E9|                         null|                  null|                   []|                    null|             null|                   null|             text|     t2_isokk7o|               false|         false|    <NA>|Was also present ...|    true|       false|    false|                           null|            null|        null|               0| 1623718068|         None|  null|     0|      {}|h1snwpl|       false|t3_o01g7o| false|     true|t1_h1snjeu|/r/NoStupidQuesti...|      false|          null|        null|    3|        true|   false|    t5_2w844|                   null|        public|            null|                    0|            []|          null|    null|           nan|s3://aa2464-labda...|\n",
      "|           []|            None|  BruceLeeis4Me|     1.588783038E9|                         null|                  null|                   []|                    null|             null|                   null|             text|    t2_6eg1e70p|               false|         false|    null|Because people ar...|    true|       false|    false|                           null|            null|        null|               0| 1622909101|         null|  null|     0|      {}|h0p5zot|       false|t3_nsyjgl| false|     true| t3_nsyjgl|/r/NoStupidQuesti...|      false|          null|        null|    1|        true|   false|    t5_2w844|                   null|        public|            null|                    0|            []|          null|    null|           nan|s3://aa2464-labda...|\n",
      "+-------------+----------------+---------------+------------------+-----------------------------+----------------------+---------------------+------------------------+-----------------+-----------------------+-----------------+---------------+--------------------+--------------+--------+--------------------+--------+------------+---------+-------------------------------+----------------+------------+----------------+-----------+-------------+------+------+--------+-------+------------+---------+------+---------+----------+--------------------+-----------+--------------+------------+-----+------------+--------+------------+-----------------------+--------------+----------------+---------------------+--------------+--------------+--------+--------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_small.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9b51c856-0b8f-4855-a009-83c3757440dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- all_awardings: string (nullable = true)\n",
      " |-- associated_award: string (nullable = true)\n",
      " |-- author: string (nullable = true)\n",
      " |-- author_created_utc: double (nullable = true)\n",
      " |-- author_flair_background_color: string (nullable = true)\n",
      " |-- author_flair_css_class: string (nullable = true)\n",
      " |-- author_flair_richtext: string (nullable = true)\n",
      " |-- author_flair_template_id: string (nullable = true)\n",
      " |-- author_flair_text: string (nullable = true)\n",
      " |-- author_flair_text_color: string (nullable = true)\n",
      " |-- author_flair_type: string (nullable = true)\n",
      " |-- author_fullname: string (nullable = true)\n",
      " |-- author_patreon_flair: boolean (nullable = true)\n",
      " |-- author_premium: boolean (nullable = true)\n",
      " |-- awarders: string (nullable = true)\n",
      " |-- body: string (nullable = true)\n",
      " |-- can_gild: boolean (nullable = true)\n",
      " |-- can_mod_post: boolean (nullable = true)\n",
      " |-- collapsed: boolean (nullable = true)\n",
      " |-- collapsed_because_crowd_control: string (nullable = true)\n",
      " |-- collapsed_reason: string (nullable = true)\n",
      " |-- comment_type: string (nullable = true)\n",
      " |-- controversiality: long (nullable = true)\n",
      " |-- created_utc: long (nullable = true)\n",
      " |-- distinguished: string (nullable = true)\n",
      " |-- edited: double (nullable = true)\n",
      " |-- gilded: long (nullable = true)\n",
      " |-- gildings: string (nullable = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- is_submitter: boolean (nullable = true)\n",
      " |-- link_id: string (nullable = true)\n",
      " |-- locked: boolean (nullable = true)\n",
      " |-- no_follow: boolean (nullable = true)\n",
      " |-- parent_id: string (nullable = true)\n",
      " |-- permalink: string (nullable = true)\n",
      " |-- quarantined: boolean (nullable = true)\n",
      " |-- removal_reason: string (nullable = true)\n",
      " |-- retrieved_on: long (nullable = true)\n",
      " |-- score: long (nullable = true)\n",
      " |-- send_replies: boolean (nullable = true)\n",
      " |-- stickied: boolean (nullable = true)\n",
      " |-- subreddit_id: string (nullable = true)\n",
      " |-- subreddit_name_prefixed: string (nullable = true)\n",
      " |-- subreddit_type: string (nullable = true)\n",
      " |-- top_awarded_type: string (nullable = true)\n",
      " |-- total_awards_received: long (nullable = true)\n",
      " |-- treatment_tags: string (nullable = true)\n",
      " |-- author_cakeday: boolean (nullable = true)\n",
      " |-- editable: boolean (nullable = true)\n",
      " |-- media_metadata: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_small.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "25de1d31-858c-43da-806c-de54b84fd39e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 23:=====================================================>  (20 + 1) / 21]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+\n",
      "|controversiality|\n",
      "+----------------+\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "|               0|\n",
      "+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "x = df_small.select(\"controversiality\")\n",
    "x = x.dropna()\n",
    "x.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3e874b56-e6c5-4c75-9de4-d0d77dcc14b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(controversiality=0, count=9894), Row(controversiality=1, count=106)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x.groupby('controversiality').count().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49fa6f7a-eb8c-404d-9bf3-367679e88964",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find Count of Null, None, NaN of All DataFrame Columns\n",
    "from pyspark.sql.functions import col,isnan, when, count\n",
    "df.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in df.columns]\n",
    "   ).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "bddcb57c-5897-4930-8a01-fc0984dcade5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 33:=============================================>          (17 + 4) / 21]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|           permalink|\n",
      "+--------------------+\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "|/r/NoStupidQuesti...|\n",
      "+--------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "x = df_small.select(\"permalink\")\n",
    "x = x.dropna()\n",
    "x.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c4a6b904-7d03-4bac-8914-435af2084f31",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(top_awarded_type=None, count=392178)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('top_awarded_type').count().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8f71335-0de8-4752-96e2-049a6288baf5",
   "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.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}