Static analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Static analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Intro - importing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install pandas\n",
    "import pandas as pd\n",
    "\n",
    "#!pip install pickle\n",
    "import pickle\n",
    "\n",
    "#!pip install numpy\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data/comments_cleaned\", 'rb') as file:\n",
    "    comments = pickle.load(file)\n",
    "    \n",
    "with open(\"data/submissions_cleaned\", 'rb') as file:\n",
    "    submissions = pickle.load(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>submissions_id</th>\n",
       "      <th>url</th>\n",
       "      <th>permalink</th>\n",
       "      <th>author</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>num_comments</th>\n",
       "      <th>score</th>\n",
       "      <th>over_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>648oo</td>\n",
       "      <td>http://www.ignorancedenied.com/viewthread.php?...</td>\n",
       "      <td>/r/reddit.com/comments/648oo/brain_disease_is_...</td>\n",
       "      <td>DITUS</td>\n",
       "      <td>1199145615</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648op</td>\n",
       "      <td>http://www.flascience.org/wp/?p=363</td>\n",
       "      <td>/r/science/comments/648op/three_more_florida_c...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145634</td>\n",
       "      <td>science</td>\n",
       "      <td>t5_mouw</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>648or</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_SHO...</td>\n",
       "      <td>/r/reddit.com/comments/648or/nude_couple_grapp...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145709</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>648os</td>\n",
       "      <td>http://www.sltrib.com/opinion/ci_7846101?sourc...</td>\n",
       "      <td>/r/politics/comments/648os/apparently_bushs_pr...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>648ot</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_RAR...</td>\n",
       "      <td>/r/reddit.com/comments/648ot/diners_find_rare_...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044805</th>\n",
       "      <td>7mq3n</td>\n",
       "      <td>http://ventaboutsports.blogspot.com/2008/12/so...</td>\n",
       "      <td>/r/funny/comments/7mq3n/some_extremely_corny_j...</td>\n",
       "      <td>themightymidget</td>\n",
       "      <td>1230767909</td>\n",
       "      <td>funny</td>\n",
       "      <td>t5_2qh33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044806</th>\n",
       "      <td>7mq3o</td>\n",
       "      <td>http://www.pbs.org/mormons/etc/genealogy.html</td>\n",
       "      <td>/r/news/comments/7mq3o/pbs_looks_at_the_massiv...</td>\n",
       "      <td>Tom22</td>\n",
       "      <td>1230767926</td>\n",
       "      <td>news</td>\n",
       "      <td>t5_2qh3l</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044807</th>\n",
       "      <td>7mq3q</td>\n",
       "      <td>http://www.narutogames.biz</td>\n",
       "      <td>/r/reddit.com/comments/7mq3q/naruto_games/</td>\n",
       "      <td>bixiebix</td>\n",
       "      <td>1230767937</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044808</th>\n",
       "      <td>7mq3r</td>\n",
       "      <td>http://www.youtube.com/watch?v=gdQH1CI4LHY&amp;amp...</td>\n",
       "      <td>/r/politics/comments/7mq3r/ron_paul_on_recent_...</td>\n",
       "      <td>middkidd</td>\n",
       "      <td>1230767963</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044809</th>\n",
       "      <td>7mq3v</td>\n",
       "      <td>http://player.video.news.com.au/dailytelegraph...</td>\n",
       "      <td>/r/reddit.com/comments/7mq3v/video_nye_firewor...</td>\n",
       "      <td>klippa</td>\n",
       "      <td>1230767988</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2044810 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        submissions_id                                                url  \\\n",
       "0                648oo  http://www.ignorancedenied.com/viewthread.php?...   \n",
       "1                648op                http://www.flascience.org/wp/?p=363   \n",
       "2                648or  http://hosted.ap.org/dynamic/stories/O/ODD_SHO...   \n",
       "3                648os  http://www.sltrib.com/opinion/ci_7846101?sourc...   \n",
       "4                648ot  http://hosted.ap.org/dynamic/stories/O/ODD_RAR...   \n",
       "...                ...                                                ...   \n",
       "2044805          7mq3n  http://ventaboutsports.blogspot.com/2008/12/so...   \n",
       "2044806          7mq3o      http://www.pbs.org/mormons/etc/genealogy.html   \n",
       "2044807          7mq3q                         http://www.narutogames.biz   \n",
       "2044808          7mq3r  http://www.youtube.com/watch?v=gdQH1CI4LHY&amp...   \n",
       "2044809          7mq3v  http://player.video.news.com.au/dailytelegraph...   \n",
       "\n",
       "                                                 permalink           author  \\\n",
       "0        /r/reddit.com/comments/648oo/brain_disease_is_...            DITUS   \n",
       "1        /r/science/comments/648op/three_more_florida_c...           rmuser   \n",
       "2        /r/reddit.com/comments/648or/nude_couple_grapp...            zorno   \n",
       "3        /r/politics/comments/648os/apparently_bushs_pr...           rmuser   \n",
       "4        /r/reddit.com/comments/648ot/diners_find_rare_...            zorno   \n",
       "...                                                    ...              ...   \n",
       "2044805  /r/funny/comments/7mq3n/some_extremely_corny_j...  themightymidget   \n",
       "2044806  /r/news/comments/7mq3o/pbs_looks_at_the_massiv...            Tom22   \n",
       "2044807         /r/reddit.com/comments/7mq3q/naruto_games/         bixiebix   \n",
       "2044808  /r/politics/comments/7mq3r/ron_paul_on_recent_...         middkidd   \n",
       "2044809  /r/reddit.com/comments/7mq3v/video_nye_firewor...           klippa   \n",
       "\n",
       "         created_utc   subreddit subreddit_id  num_comments  score  over_18  \n",
       "0         1199145615  reddit.com         t5_6             1      0    False  \n",
       "1         1199145634     science      t5_mouw             5     20    False  \n",
       "2         1199145709  reddit.com         t5_6             1      3    False  \n",
       "3         1199145735    politics     t5_2cneq             2      0    False  \n",
       "4         1199145735  reddit.com         t5_6             0      0    False  \n",
       "...              ...         ...          ...           ...    ...      ...  \n",
       "2044805   1230767909       funny     t5_2qh33             0      1    False  \n",
       "2044806   1230767926        news     t5_2qh3l             0      0    False  \n",
       "2044807   1230767937  reddit.com         t5_6             7      1    False  \n",
       "2044808   1230767963    politics     t5_2cneq             3      1    False  \n",
       "2044809   1230767988  reddit.com         t5_6             1      1    False  \n",
       "\n",
       "[2044810 rows x 10 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "submissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>comments_id</th>\n",
       "      <th>author</th>\n",
       "      <th>link_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>c02s9t4</td>\n",
       "      <td>LiberalDemocrat</td>\n",
       "      <td>648os</td>\n",
       "      <td>648os</td>\n",
       "      <td>1199145817</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>c02s9u6</td>\n",
       "      <td>DiscoWolf</td>\n",
       "      <td>648os</td>\n",
       "      <td>648os</td>\n",
       "      <td>1199146090</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>c02s9uh</td>\n",
       "      <td>Flemlord</td>\n",
       "      <td>648p5</td>\n",
       "      <td>648p5</td>\n",
       "      <td>1199146155</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>c02s9un</td>\n",
       "      <td>LiberalDemocrat</td>\n",
       "      <td>648oy</td>\n",
       "      <td>648oy</td>\n",
       "      <td>1199146229</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>c02s9us</td>\n",
       "      <td>LiberalDemocrat</td>\n",
       "      <td>648p5</td>\n",
       "      <td>648p5</td>\n",
       "      <td>1199146266</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873683</th>\n",
       "      <td>c06vwuc</td>\n",
       "      <td>fergie</td>\n",
       "      <td>7k6cl</td>\n",
       "      <td>7k6cl</td>\n",
       "      <td>1229579665</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873685</th>\n",
       "      <td>c06vwue</td>\n",
       "      <td>wolfzero</td>\n",
       "      <td>7k4if</td>\n",
       "      <td>c06vs7l</td>\n",
       "      <td>1229579675</td>\n",
       "      <td>technology</td>\n",
       "      <td>t5_2qh16</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873686</th>\n",
       "      <td>c06vwug</td>\n",
       "      <td>Morgin_Black</td>\n",
       "      <td>7k3w5</td>\n",
       "      <td>7k3w5</td>\n",
       "      <td>1229579679</td>\n",
       "      <td>comics</td>\n",
       "      <td>t5_2qh0s</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873687</th>\n",
       "      <td>c06vwui</td>\n",
       "      <td>onezerozeroone</td>\n",
       "      <td>7k2bc</td>\n",
       "      <td>c06vrvz</td>\n",
       "      <td>1229579685</td>\n",
       "      <td>atheism</td>\n",
       "      <td>t5_2qh2p</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873688</th>\n",
       "      <td>c06vwuj</td>\n",
       "      <td>vypermann</td>\n",
       "      <td>7k65l</td>\n",
       "      <td>c06vvzu</td>\n",
       "      <td>1229579686</td>\n",
       "      <td>WTF</td>\n",
       "      <td>t5_2qh61</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3210167 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        comments_id           author link_id parent_id  created_utc  \\\n",
       "25          c02s9t4  LiberalDemocrat   648os     648os   1199145817   \n",
       "55          c02s9u6        DiscoWolf   648os     648os   1199146090   \n",
       "63          c02s9uh         Flemlord   648p5     648p5   1199146155   \n",
       "69          c02s9un  LiberalDemocrat   648oy     648oy   1199146229   \n",
       "72          c02s9us  LiberalDemocrat   648p5     648p5   1199146266   \n",
       "...             ...              ...     ...       ...          ...   \n",
       "4873683     c06vwuc           fergie   7k6cl     7k6cl   1229579665   \n",
       "4873685     c06vwue         wolfzero   7k4if   c06vs7l   1229579675   \n",
       "4873686     c06vwug     Morgin_Black   7k3w5     7k3w5   1229579679   \n",
       "4873687     c06vwui   onezerozeroone   7k2bc   c06vrvz   1229579685   \n",
       "4873688     c06vwuj        vypermann   7k65l   c06vvzu   1229579686   \n",
       "\n",
       "          subreddit subreddit_id  score  \n",
       "25         politics     t5_2cneq      1  \n",
       "55         politics     t5_2cneq      1  \n",
       "63         politics     t5_2cneq      7  \n",
       "69       reddit.com         t5_6      0  \n",
       "72         politics     t5_2cneq    -10  \n",
       "...             ...          ...    ...  \n",
       "4873683    politics     t5_2cneq      2  \n",
       "4873685  technology     t5_2qh16      4  \n",
       "4873686      comics     t5_2qh0s      0  \n",
       "4873687     atheism     t5_2qh2p      1  \n",
       "4873688         WTF     t5_2qh61      0  \n",
       "\n",
       "[3210167 rows x 8 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Actual start of the analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q1: How many unique subreddits occur? Which has the most comments, and which has the most active users?\n",
    "\n",
    "I will divide this into 3 questions:\n",
    "\n",
    "    1. Need to count submissions and comments with unique subreddits \n",
    "    2. Need to group and count subreddits by comments\n",
    "    3. Need to group and count subreddits by users\n",
    "\n",
    "> Could have used *subreddit_ids* but the name is also unqiue!        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "subreddits_authors = pd.concat([submissions[['subreddit', 'author']], comments[['subreddit', 'author']]], ignore_index=True)\n",
    "subreddits_authors = subreddits_authors.groupby('subreddit').agg({'author': \"nunique\"})\n",
    "subreddits_authors = subreddits_authors.sort_values(by=\"author\", ascending=False)\n",
    "# subreddits_authors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two birds one stone: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Subreddits:\n",
      "\n",
      " 4191\n",
      "Stored 'NUMBER_OF_SUBREDDITS' (int)\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of Subreddits:\\n\\n\", subreddits_authors.shape[0])\n",
    "NUMBER_OF_SUBREDDITS = subreddits_authors.shape[0]\n",
    "%store NUMBER_OF_SUBREDDITS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top 10 with most users:\n",
      "\n",
      "                author\n",
      "subreddit            \n",
      "reddit.com     158641\n",
      "politics        34902\n",
      "pics            26759\n",
      "technology      26070\n",
      "funny           25615\n",
      "entertainment   24717\n",
      "programming     23629\n",
      "business        23347\n",
      "science         23332\n",
      "worldnews       22472\n"
     ]
    }
   ],
   "source": [
    "print(\"Top 10 with most users:\\n\\n\", subreddits_authors.iloc[:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "comments_size = comments[['subreddit']].groupby('subreddit').size().reset_index(name='counts')\n",
    "comments_size = comments_size.sort_values(by=\"counts\", ascending=False)\n",
    "# comments_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Subreddits with the most comments:\n",
      "\n",
      "           subreddit  counts\n",
      "1580     reddit.com  738291\n",
      "1535       politics  526425\n",
      "1553    programming  255419\n",
      "1523           pics  187328\n",
      "1632        science  161279\n",
      "1870      worldnews  150029\n",
      "734             WTF  117793\n",
      "1148          funny  114429\n",
      "1742     technology   99609\n",
      "1084  entertainment   96118\n"
     ]
    }
   ],
   "source": [
    "print(\"Subreddits with the most comments:\\n\\n\", comments_size.iloc[:10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q2: Avrage number of users per subreddit?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Avrage user per subreddit: 141.32283\n"
     ]
    }
   ],
   "source": [
    "print(f\"Avrage user per subreddit: {round(subreddits_authors['author'].mean(), 5)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q3: Users with the most submissions, users with the most comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_of_submissions = submissions[['author']].groupby('author').size().reset_index(name='counts')\n",
    "authors_of_submissions = authors_of_submissions.sort_values(by=\"counts\", ascending=False)\n",
    "# authors_of_submissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Users with the most submissions:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>84823</th>\n",
       "      <td>gst</td>\n",
       "      <td>18870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141813</th>\n",
       "      <td>qgyh2</td>\n",
       "      <td>12238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147359</th>\n",
       "      <td>rmuser</td>\n",
       "      <td>9822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173691</th>\n",
       "      <td>twolf1</td>\n",
       "      <td>8597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13172</th>\n",
       "      <td>IAmperfectlyCalm</td>\n",
       "      <td>8308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141766</th>\n",
       "      <td>qazamisan</td>\n",
       "      <td>6927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54960</th>\n",
       "      <td>charlatan</td>\n",
       "      <td>5998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90683</th>\n",
       "      <td>igeldard</td>\n",
       "      <td>5373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130852</th>\n",
       "      <td>noname99</td>\n",
       "      <td>5334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64933</th>\n",
       "      <td>democracy101</td>\n",
       "      <td>5332</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  author  counts\n",
       "84823                gst   18870\n",
       "141813             qgyh2   12238\n",
       "147359            rmuser    9822\n",
       "173691            twolf1    8597\n",
       "13172   IAmperfectlyCalm    8308\n",
       "141766         qazamisan    6927\n",
       "54960          charlatan    5998\n",
       "90683           igeldard    5373\n",
       "130852          noname99    5334\n",
       "64933       democracy101    5332"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Users with the most submissions:\")\n",
    "authors_of_submissions.iloc[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_of_comments = comments[['author']].groupby('author').size().reset_index(name='counts')\n",
    "authors_of_comments = authors_of_comments.sort_values(by=\"counts\", ascending=False)\n",
    "# authors_of_comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Users with the most comments:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11308</th>\n",
       "      <td>NoMoreNicksLeft</td>\n",
       "      <td>8465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51139</th>\n",
       "      <td>malcontent</td>\n",
       "      <td>7423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52040</th>\n",
       "      <td>matts2</td>\n",
       "      <td>6975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>7oby</td>\n",
       "      <td>5640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52943</th>\n",
       "      <td>mexicodoug</td>\n",
       "      <td>5499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55356</th>\n",
       "      <td>mutatron</td>\n",
       "      <td>4980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62351</th>\n",
       "      <td>randomb0y</td>\n",
       "      <td>4896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18866</th>\n",
       "      <td>aletoledo</td>\n",
       "      <td>4880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58533</th>\n",
       "      <td>otakucode</td>\n",
       "      <td>4744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19141</th>\n",
       "      <td>alllie</td>\n",
       "      <td>4734</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                author  counts\n",
       "11308  NoMoreNicksLeft    8465\n",
       "51139       malcontent    7423\n",
       "52040           matts2    6975\n",
       "567               7oby    5640\n",
       "52943       mexicodoug    5499\n",
       "55356         mutatron    4980\n",
       "62351        randomb0y    4896\n",
       "18866        aletoledo    4880\n",
       "58533        otakucode    4744\n",
       "19141           alllie    4734"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Users with the most comments:\")\n",
    "authors_of_comments.iloc[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q4: Which users are the most active on the biggest number of subreddits? How many subreddits are they active on?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_on_subreddits = pd.concat([submissions[['subreddit', 'author']], comments[['subreddit', 'author']]], ignore_index=True)\n",
    "authors_on_subreddits = authors_on_subreddits.groupby('author').agg({'subreddit': \"nunique\"})\n",
    "authors_on_subreddits = authors_on_subreddits.sort_values(by=\"subreddit\", ascending=False)\n",
    "# authors_on_subreddits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Most active users and the number of subreddits they were active:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subreddit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>author</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Escafane</th>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MrKlaatu</th>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>omfgninja</th>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>scientologist2</th>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>codepoet</th>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>krugerlive</th>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>turkourjurbs</th>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tuoder</th>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bobcat</th>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b34nz</th>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                subreddit\n",
       "author                   \n",
       "Escafane              152\n",
       "MrKlaatu              147\n",
       "omfgninja             111\n",
       "scientologist2        106\n",
       "codepoet              103\n",
       "krugerlive            102\n",
       "turkourjurbs          101\n",
       "tuoder                101\n",
       "bobcat                 98\n",
       "b34nz                  97"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Most active users and the number of subreddits they were active:\")\n",
    "authors_on_subreddits.iloc[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q5: Define the correlation between the number of submissions and the number of comments by users. Compute Pearson's coefficient and visualize the results\n",
    "\n",
    "> https://en.wikipedia.org/wiki/Pearson_correlation_coefficient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install scipy\n",
    "from scipy.stats import pearsonr\n",
    "\n",
    "#!pip install matplotlib\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "author_submissions = submissions[[\"author\"]].groupby(\"author\").size().reset_index(name=\"submissions_count\")\n",
    "author_comments = comments[[\"author\"]].groupby(\"author\").size().reset_index(name=\"comments_count\")\n",
    "\n",
    "all_posting = pd.merge(author_submissions, author_comments, how=\"outer\", left_on=\"author\", right_on=\"author\").reset_index().fillna(0)\n",
    "# all_posting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pearson's coefficient is: 0.155797\n"
     ]
    }
   ],
   "source": [
    "pearson, ttp = pearsonr(all_posting[\"submissions_count\"], all_posting[\"comments_count\"])\n",
    "print(f\"Pearson's coefficient is: {round(pearson, 6)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0, 0.5, 'Submissions Count')"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYsAAAEGCAYAAACUzrmNAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAAlcElEQVR4nO3dfZRddX3v8fcnkwEmFJwgkcJATKAQbpBCZIooVnloDdVWAlTBasXWW5ZeL0Vq0yaty4euukxvWqm0lUqrAkp5qMQQRBu5gNBFCXRCEkOAkfAgZJJCsAzkmhGGyff+sfeZ7Dk5TzNz9nmY+bzWOmvO+Z199v7OmWR/9/49KiIwMzOrZEazAzAzs9bnZGFmZlU5WZiZWVVOFmZmVpWThZmZVTWz2QHk5dBDD4158+Y1Owwzs7ayfv36FyJiTnH5lE0W8+bNo6+vr9lhmJm1FUk/KVXuaigzM6vKycLMzKpysjAzs6qcLMzMrConCzMzq2rK9oaajNUbBli5tp/tg0Mc0d3F0sULWLKop9lhmZk1jZNFkdUbBli+ajNDwyMADAwOsXzVZgAnDDObtlwNVWTl2v7RRFEwNDzCyrX9TYrIzKz5nCyKbB8cGle5mdl04GRR5IjurnGVm5lNB04WRZYuXkBXZ8eYsq7ODpYuXtCkiMzMms8N3EUKjdjuDWVmtpeTRQlLFvU4OZiZZbgayszMqnKyMDOzqpwszMysqlyThaTLJW2R9LCkGyQdIOkQSXdIejz9OTuz/XJJWyX1S1qcKT9F0ub0vSslKc+4zcxsrNyShaQe4A+B3oh4E9ABXAQsA+6MiGOBO9PXSFqYvn8CcA7wFUmFPqxXAZcAx6aPc/KK28zM9pV3NdRMoEvSTGAWsB04F7g2ff9aYEn6/Fzgxoh4JSKeArYCp0o6HDg4Iu6PiACuy3zGzMwaILdkEREDwF8DzwA7gJci4gfAYRGxI91mB/CG9CM9wLOZXWxLy3rS58Xl+5B0iaQ+SX07d+6s569jZjat5VkNNZvkbmE+cARwoKQPVfpIibKoUL5vYcTVEdEbEb1z5swZb8hmZlZGntVQvwY8FRE7I2IYWAW8DXgurVoi/fl8uv024KjM548kqbbalj4vLjczswbJM1k8A5wmaVbae+ls4FFgDXBxus3FwK3p8zXARZL2lzSfpCH7wbSqapek09L9fDjzGTMza4DcpvuIiAckfRt4CHgN2ABcDfwCcLOkj5IklPel22+RdDPwSLr9JyKisLDEx4FrgC7g++nDzMwaREkHo6mnt7c3+vr6mh2GmVlbkbQ+InqLyz2C28zMqnKyMDOzqpwszMysKicLMzOrysnCzMyq8kp5RVZvGPCSqmZmRZwsMlZvGGD5qs0MDSfDOwYGh1i+ajOAE4aZTWuuhspYubZ/NFEUDA2PsHJtf5MiMjNrDU4WGdsHh8ZVbmY2XThZZBzR3TWucjOz6cLJImPp4gV0dXaMKevq7GDp4gVNisjMrDW4gTuj0Ijt3lBmZmM5WRRZsqjHycHMrIiroczMrConCzMzq8rVUEU8gtvMbF9OFhkewW1mVpqroTI8gtvMrDQniwyP4DYzK83JIsMjuM3MSnOyyPAIbjOz0tzAneER3GZmpTlZFPEIbjOzfbkayszMqnKyMDOzqpwszMysKicLMzOrysnCzMyqcrIwM7OqnCzMzKwqJwszM6vKycLMzKpysjAzs6qcLMzMrConCzMzq8rJwszMqqqaLCR9s5YyMzObumq5szgh+0JSB3BKPuGYmVkrKpssJC2XtAv4ZUkvp49dwPPArbXsXFK3pG9LekzSo5LeKukQSXdIejz9ObvomFsl9UtanCk/RdLm9L0rJWkSv7OZmY1T2WQREV+MiIOAlRFxcPo4KCJeHxHLa9z/l4F/i4jjgZOAR4FlwJ0RcSxwZ/oaSQuBi0juZM4BvpLexQBcBVwCHJs+zhnvL2pmZhNXdaW8iFguqQd4Y3b7iLi30uckHQy8A/hIuv2rwKuSzgXOSDe7Fvgh8KfAucCNEfEK8JSkrcCpkp4GDo6I+9P9XgcsAb5f4+9oZmaTVDVZSFpBcsX/CDCSFgdQMVkARwM7gW9IOglYD1wGHBYROwAiYoekN6Tb9wDrMp/flpYNp8+Ly0vFegnJHQhz586t9quZmVmNalmD+zxgQXrFP959vxm4NCIekPRl0iqnMkq1Q0SF8n0LI64Grgbo7e0tuY2ZmY1fLb2hngQ6J7DvbcC2iHggff1tkuTxnKTDAdKfz2e2Pyrz+SOB7Wn5kSXKzcysQWpJFruBjZK+mvZEulLSldU+FBH/BTwraUFadDZJVdYa4OK07GL29qxaA1wkaX9J80kash9Mq6x2STot7QX1YWrsjWVmZvVRSzXUmvQxEZcC10vaj+QO5fdIEtTNkj4KPAO8DyAitki6mSShvAZ8IiIKbSQfB64Bukgatt24bWbWQIqYmlX7vb290dfX1+wwRq3eMMDKtf1sHxziiO4uli5ewJJFJdvpzcyaRtL6iOgtLq+lN9RTlGhQjoij6xTblLd6wwDLV21maDi5URoYHGL5qs0AThhm1hZqqYbKZpgDSKqNDsknnKlp5dr+0URRMDQ8wsq1/U4WZtYWqjZwR8RPM4+BiPhb4Kz8Q5s6tg8OjavczKzV1FIN9ebMyxkkdxoH5RZRE+XVrnBEdxcDJRLDEd1dk963mVkj1FIN9TeZ568BTwPvzyWaJsqzXWHp4gVj9g3Q1dnB0sULKnzKzKx11DI31JmNCKTZ8mxXKHzevaHMrF3VUg31OuCzJJMCAtwD/EVEvJRnYI2Wd7vCkkU9Tg5m1rZqGcH9dWAXSdXT+4GXgW/kGVQzlGs/cLuCmVltyeKYiPhsRDyZPj5PMqPslLJ08QK6OjvGlLldwcwsUUuyGJL09sILSacDU67P55JFPXzx/BPp6e5CQE93F188/0RXHZmZUVtvqI8B16VtFwAvki5oNNW4XcHMrLRaekNtAk5KV74jIl7OPSozM2spZauhJP1ROjMskCSJiHhZ0qWSPtmQ6MzMrCVUarP4feCbJcqvTt8zM7NpolKyiIh4tUThK5Re6tTMzKaoim0Wkg6LiOeKy/INqXV4DQozs0SlO4uVwO2S3inpoPRxBnAb8NeNCK6ZCnNFDQwOEeydK2r1hoFmh2Zm1nBl7ywi4jpJO4G/AN5EsgDSFuCzETEllzXN3knMkBgpWkVwaHiET928CfCiRWY2vVSshkqTwpRMDMWKZ50tThQFIxFe5c7Mpp1aRnBPC6VmnS2nMButmdl0UcsI7mlhvLPLttMqd26oN7PJcrJIva6rk8Gh4Zq3b5fZaPNc1MnMpo+q1VCSLpN0sBJfk/SQpHc1IrhGUoWRI50dY99sp9loKy3qZGZWq1raLH4/nQ/qXcAc4PeAFblG1QQv7q5wVxEwe1ZnW85Gm/eiTmY2PdRSDVW4rH438I2I2CRVug5vTzMEe0p3gGJ4TzBrv5ls+Ez73VAd0d3FQInE0C7VaGbWGmq5s1gv6QckyWKtpIOAPfmG1XjlEkVBu16Je1EnM6uHWu4sPgqcDDwZEbslvZ6kKmpaadcr8UJ1mXtDmdlk1LKexR5JzwELJU3L3lPtfiXuRZ3MbLKqnvwl/RVwIfAIUOhWE8C9OcbVcJXaLNqpQdvMLA+13CksARakU5NPWb/zlrl8a90z+5R/6LS5ThRmNu3VkiyeBDqBKZ0s/nLJiQDc8MCzjETQIfGBtxw1Wm5mNp0pykyYN7qBdAtwEnAnmYQREX+Yb2iT09vbG319fc0Ow8ysrUhaHxG9xeW13FmsSR9mZjZN1dIb6lpJ+wHHpUX9EVH7JEpmZtb2aukNdQZwLfA0yWjuoyRdHBFTqjdUlmdpNTMbq5ZqqL8B3hUR/QCSjgNuAE7JM7Bm+PTqzVz/wDNkm3E8S6uZWW3TfXQWEgVARPyYpHfUlPLp1Zv51rqxiaLAs7Sa2XRXS7LoS6cmPyN9/BOwvtYDSOqQtEHSd9PXh0i6Q9Lj6c/ZmW2XS9oqqV/S4kz5KZI2p+9dmcdEhjc88GzF99t1bigzs3qoJVl8HNgC/CFwGclI7o+N4xiXAY9mXi8D7oyIY0m64y4DkLQQuAg4ATgH+Iqkwgx4VwGXAMemj3PGcfyalFtzu6Bd54YyM6uHqskiIl6JiC9FxPkRcV5EXFHraG5JRwLvAf45U3wuSYM56c8lmfIb0+M9BWwFTpV0OHBwRNwfyaCQ6zKfaZgzj5/T6EOambWMsg3ckm6OiPdL2kwyF9QYEfHLNez/b4E/AQ7KlB0WETvSfeyQ9Ia0vAdYl9luW1o2nD4vLm+oux/b2ehDmpm1jEq9oS5Lf/7mRHYs6TeB5yNifdr9tupHSpRFhfJSx7yEpLqKuXPn1hZoqqfMIkEFbrMws+msbDVU4eofeAF4NiJ+AuxPMvXH9hr2fTrwXklPAzcCZ0n6FvBcWrVE+vP5dPttwFGZzx+ZHmdb+ry4vFTMV0dEb0T0zpkzvmqjUosEZc2QmL/sdk5fcRerNwyMa99mZu2ulgbue4EDJPWQNEj/HnBNtQ9FxPKIODIi5pE0XN8VER8imTrk4nSzi4Fb0+drgIsk7S9pPklD9oNp0tol6bS0F9SHM5+pmyWLevji+Sdy4H6lE8ZIBEEy7uLymzby6dWb6x2CmVnLqiVZKCJ2A+cDfxcR5wELJ3HMFcCvS3oc+PX0NRGxBbiZpLfVvwGfiIjC+hkfJ2kk3wo8AXx/EsevqNryqpDUgV2/7hnfYZjZtFHLCG5JeivwQZIlVmv93KiI+CHww/T5T4Gzy2z3BeALJcr7gDeN55gT8bk1WxgaHqm+IUnCWLm236O6zWxaqOXO4pPAcuA7EbFF0tHA3blG1QSrNwwwODS++RHd6G1m00Uts87eA9wDIGkG8EKrr2UxEROZzsMD9cxsuqh6ZyHpXyQdLOlAkvaEfklL8w+tsSrdJZx+zCH79N/t6uxg6eIF+QZlZtYiaqmGWhgRL5OMmv4eMBf43TyDaobuWaXnRjxwvw6u/4O3csWFJ9PT3YVIxmR88fwT3V5hZtNGLQ3VnZI6SZLF30fEsKQa+gy1l1fKNGzvfnWE1RsGWLKox8nBzKatWu4svkqy8NGBwL2S3gi8nGdQzbB7eE/J8gCWr9rsbrJmNq3VMpHglRHRExHvjsRPgDMbEFvL8HoWZjbdVZpI8EMR8S1Jf1Rmky/lFFNTdHd1Vuw6626yZjadVbqzODD9eVCZx5RywhGVfyV3kzWz6azsnUVEfDX9+fnGhdM86558seL7P3vltdGGbjOz6aZqb6h0Ur9LgXnZ7SPivfmF1XjVVsobHBpm+apk8kAnDDObbmrpOrsa+BpwG1C6y9A0UWjodrIws+mmlmTx84i4MvdI2oQbus1sOqolWXxZ0meBHwCja29HxEO5RdXCSjV0r94wwMq1/WwfHOKI7i6WLl7guw8zm1JqSRYnkkzvcRZ7q6EifT1liDJrtWaUmg9q9YYBlq/aPDq1+cDgkNs2zGzKqSVZnAccHRGv5h1MM1VLFB0SF5zSw8q1/Vx+08bRO4iVa/v3WQPDbRtmNtXUMt3HJqA75ziarqfKOIoPvOUoblk/wMDg0OjyqstXbWagTBuG2zbMbCqpJVkcBjwmaa2kNYVH3oE12pnHz6n4/nc37Sh5B9Gh4snLEx7EZ2ZTSS3VUJ/NPYoWcPdjOyu+X24qkJEIujo7xiQSr3VhZlNNLRMJ3gP0A68DDgb607IpZaLVRrNndfLF80+c0FoXqzcMcPqKu5i/7HZOX3GXZ7Y1s5ZVywju/wl8BriLpNPQ30n6i4j4et7BNVL3rE5e3D2+NbgBBncPjzZ4X3HhyTU3arsXlZm1k1qqoZYCiyLipwCSXg/8BzClksXPyyx+VE2hF9V4T/buRWVm7aSWBu5twK7M613As/mE0zxDZRY/Gt8+al/3oly1l3tRmVkrqrSeRWEdiwHgAUm3klxInws82IDY2lKtJ/sjurtKdrt1Lyoza0WV7iwK61Y8QTKZYKHG5VZgR75hta5CR9nJdpldungBXZ0dY8rci8rMWlWl9SymxToW4xUkPZ6WLl4wpoEaxneyL7RLeE4pM2sHtfSGupsSs2FExJSaG2o8tg8O1eVkv2RRj5ODmbWFWnpD/XHm+QHABcBr+YTTHgpVTY0+2Xt2WzNrlqrJIiLWFxXdJ2nKDcqrlaAp7Qoel2FmzVS166ykQzKPQyUtBn6xAbG1pGqz0+al0rgMM7O81VINtZ7kHCmS6qengI/mGVSra8YVvcdlmFkz1VINNb8RgbSTZoy09rgMM2umstVQkn5F0i9mXn9Y0q2SrpR0SGPCa12lrujznBjQ4zKmLk8oae2gUpvFV4FXASS9A1gBXAe8BFydf2it7XVdnWNeFxqgixdHqtd//CWLeiY8u621rrz/3ZjViyJKN9lK2hQRJ6XP/wHYGRGfS19vjIiTGxXkRPT29kZfX1/N289bdvuEjtOTWV61VDVRT3cX9y2btkNSrIrTV9zlfzfWUiStj4je4vJKbRYdkmZGxGvA2cAlNX5uWilcCRb3VCrIqwHaYy6mBndcsHZR6aR/A3CPpBeAIeDfAST9EklVlKUKy6uOlLhLy6MB2mMupg53XLB2UbbNIiK+AHwKuAZ4e+ytr5oBXJp/aO2lsLxqVl4N0B5zMXW444K1i4qD8iJiXUR8JyJ+lin7cUQ8VG3Hko6SdLekRyVtkXRZWn6IpDskPZ7+nJ35zHJJWyX1p4P/CuWnSNqcvnelVGbK1ybq6e7iglN6Rmej7ZC44JR8pgNx1cXU4Y4L1i7ybHt4DfhURDwk6SBgvaQ7gI8Ad0bECknLgGXAn0paCFwEnAAcAfxfScdFxAhwFUmbyTrge8A5wPdzjH1cujo7OPP4OdyyfmC0KmokglvWD9D7xkMm9R+/VNuEqy6mFk8oae2glpXyJiQidhTuQCJiF/Ao0EOyeNK16WbXAkvS5+cCN0bEKxHxFLAVOFXS4cDBEXF/WhV2XeYzTdPd1TnmSvDux3bWvWqoXLfKM4+f46oLM2uo3JJFlqR5wCLgAeCwiNgBSUIB3pBu1sPY5Vq3pWU96fPi8lLHuURSn6S+nTt31vV3KHbg/jN5asV7uG/ZWSxZ1JNL1VC5tom7H9vpqgsza6jcu8BK+gXgFuCTEfFyheaGUm9EhfJ9CyOuJh0w2Nvbm+ucf8VJII+qoUoJyFUXZtZIud5ZSOokSRTXR8SqtPi5tGqJ9Ofzafk24KjMx48EtqflR5Yob6riJJBHr5ZyicZtE2bWaLkli7TH0teARyPiS5m31gAXp88vJlnTu1B+kaT9Jc0HjgUeTKuqdkk6Ld3nhzOfaYrODo0mgcK8PpfftJH9Z85g9qzOulUNuVulmbWKPKuhTgd+F9gsaWNa9mckc0zdLOmjwDPA+wAiYoukm4FHSHpSfSLtCQXwcZLxHl0kvaCa2hNqeCRYubafvp/8N7esHxhtVxgcGqars4MrLjy5LlVEXqfbms0zBVhB2bmh2l2j5oYqxfP62FRQPFMAJHe27kwxtZWbG6ohvaGmm4HBIc8aam3PMwVYlpNFTjzNtLU7zxRgWU4WOfEVmLU798azLCeLHPkKzNqZe+NZltelyFG5KzD3MLF24N54luVkkZPOGWL3q68xf9ntY/6T5bUWhROQ5cEzBViBk0UOZnXOYPfwHl7cPQyMTQiVephM9D+lF0Mys7y5zaLOPnTaXIaG9+xTXkgIjZxw0A3sZlYvThZ1JMHdj+0sPcshjFYRlZLXhINmZvXgZFFHEZVP0IW2hOIeJiKpOjp9xV0TGpvhLo5mljcnizqaPauz7AlaMNroXFiLolBeuBMptDWMN2G4i6OZ5c3Joo5e3D3M4O5X6Zyx7xIcbztm7/KqSxb1cN+ys+jp7tqnymoibQ1ex9nM8ubeUHX2s1dH6CiRLP7jif/m06s385dLThwtq2dbQ95dHN0112x6c7LIwciefZu4A7h+3TP0vnHvHUYeq+vlcVJ311wzczVUAwWMqWKqd1tD4aQ+MDhEMPE2kGLummtmThYNlq1iKrQ1zJ7VOVq2/8yJ/0nyOqm7a66ZOVk0WKkqpp9nBvENDg1P+G6g3Ml7Mt1ywV1zzczJIjcfOm0uxc3cpaqY6nk3UOnkPZkqqUZ2zS2saT5/2e2TSnBmVl9OFjno6e7iL5ecyAdPm0uHkpTRIXHBKfv2WKpWxTOek2epk3rWRJNQo7rm5tXmYmaT595QddbZIX72ymvMW3b7mAF3IxHcsn5gTG8oqNwjary9kLJTSpfaJ0y8naERs4/mMcmimdWH7yzqbGQkGBxKZputZcBdpSqeiVRRZQf8ldLK7QxuSDdrXU4WdbbvfLNjDQwOjalaWrm2nwtO6SlZxTOZk2ee7Qx5tSu4Id2sdbkaqgmWfnsTwyPJfcfA4BC3rB8o2QYwmUF7ea1yNt6qsfEMEly6eMGYfYPnuDJrFYooN6F2e+vt7Y2+vr6at5+37PYco6lNT9HJtPjEDMnJs5nzPp2+4q6SCaynu4v7lp01pmwi8XtaEbPmkrQ+InqLy31n0UKKr9JbcQ3k8VSNTaTB2st4mrUmJ4sWMzQ8wqdu3sTlN20cTQ7FV+zNNJ6qMTdYm00dbuBuQSMRLTvOYDwN5+XaVmZILfU7mVl1ThYtrnCnUarnUTNGO49ngF65QYIjES2XBM2sMjdwp1qhgbsWhQZiYJ/GY4Durk4+994TWqbef/WGAT518yZGSvw7K9UobmbNVa6B23cWbabQQFyq8RgmNxFhHpYs6mFPmQsSt12YtQ8niybq7Nh3Rb1abB8cqniinegcUB5sZ2blOFk0SU93Fyt/+6TRiQZLKffeEd1ddGfWwChlvFfteU7id+bxc2qagdfMJi7vNkwniwbr6pzB7FmdbB8cYuXafk47enbJ7WYIPvCWo8r2PKrW1DTeq/a8Fk5avWGAW9YPjJknS1ByBl4zm5hGzNjsZNFgQ8N7eHH38Ogf9KFnXqKzxF9hT8C31j3D/jOT5FLc8+ildLLCUiZy1Z7XmIhSSSiAux/bOan9NpvX3bBW0oiljz0or8lKNVJnDQ4N09XZwRUXnjzmSrx7Vicv7t43YcwQE5oOZDLzUFUyFQfmjXd+LLO8NeL/me8s2kBhrEX26rVcNdTBByRtGeO96s1rltqp2LjdiKs4s/FoxP8zJ4s2UTyQrVw1VKHr7HjrLvNaDW/p4gX79Prq7FBbN25Pxbsla2+NWPrY1VBtZGh4hMtv3giUrzbqkKpO3rd6wwCfv23LaDVWdiBfLtUoxXdBbTYOtHgm3HJVgO18t2TtrRGTjrbNCG5J5wBfBjqAf46IFZW2n6ojuAtmdc7g58N7qi62VKy7q5Ndr7zGyJ6xf/cZwJeK2kXqYTxTmk9WHtObl5pmvXOGQIyuSQLNnzrerF7aegS3pA7gH4DfABYCH5C0sLlRNdfuCSQKSKqpihMFJCv8fW7NlknHVaxRVTZ5dR0s1T4xvCc4cL+Zda+yM2tl7VINdSqwNSKeBJB0I3Au8EhTo5piBit0x52ovHpZFZvI2hm1KJfUXhoaZuNn3zXh/Zq1m7a4swB6gGczr7elZWNIukRSn6S+nTvbux//VNGIhjfI7w5mKvbmMpuIdkkWpea92KcuJSKujojeiOidM2dOA8KaWmZXmUJkIvLqZVUsr5N6o5KdWatrl2qobcBRmddHAtubFEtb6OwQRFK/XmwG7NPe0dkhPvtbJ+QSSyOWSl26eEHJ9b4ne1JvxaVtzZqhLXpDSZoJ/Bg4GxgA/hP4nYgo2yI73t5Q0PweUfvPnEFXZweDQ8N0SIxE0JM5ORX39jnz+Dnc/dhOtg8O0T2rk4ikLr1wQoPkJDcwOLTP/grvTaUTYB69ocymm3K9odoiWQBIejfwtyRdZ78eEV+otP1EkoWZ2XRXLlm0SzUUEfE94HvNjsPMbDpqlwZuMzNrIicLMzOrysnCzMyqcrIwM7Oq2qY31HhJ2gn8ZIIfPxR4oY7h1FurxwetH6Pjm7xWj9HxTcwbI2KfUc1TNllMhqS+Ul3HWkWrxwetH6Pjm7xWj9Hx1ZeroczMrConCzMzq8rJorSrmx1AFa0eH7R+jI5v8lo9RsdXR26zMDOzqnxnYWZmVTlZmJlZVU4WGZLOkdQvaaukZQ087lGS7pb0qKQtki5Lyz8naUDSxvTx7sxnlqdx9ktanCk/RdLm9L0rJZVaOGqicT6d7nujpL607BBJd0h6PP05uxkxSlqQ+Z42SnpZ0ieb+R1K+rqk5yU9nCmr2/claX9JN6XlD0iaV6cYV0p6TNKPJH1HUndaPk/SUOa7/Me8YywTX93+pjnFd1MmtqclbWzW91dXEeFH0m7TATwBHA3sB2wCFjbo2IcDb06fH0SydsdC4HPAH5fYfmEa3/7A/DTujvS9B4G3kqwu+H3gN+oY59PAoUVl/wdYlj5fBvxVM2PM/C3/C3hjM79D4B3Am4GH8/i+gP8F/GP6/CLgpjrF+C5gZvr8rzIxzstuV7SfXGIsE1/d/qZ5xFf0/t8An2nW91fPh+8s9joV2BoRT0bEq8CNwLmNOHBE7IiIh9Lnu4BHKbHGeMa5wI0R8UpEPAVsBU6VdDhwcETcH8m/ruuAJflGz7nAtenzazPHa2aMZwNPRESlEfy5xxcR9wL/XeK49fq+svv6NnD2eO+CSsUYET+IiNfSl+tIVqYsK88Yy3yH5TT8O6wUX7qf9wM3VNpH3n/jenGy2KsHeDbzehuVT9i5SG8zFwEPpEX/O60O+HqmyqJcrD3p8+LyegngB5LWS7okLTssInZAkvSANzQ5RkiuwLL/QVvpO6zn9zX6mfTk/hLw+jrGCvD7JFe6BfMlbZB0j6RfzcTR6Bjr9TfN8zv8VeC5iHg8U9Yq39+4OVnsVSpbN7RfsaRfAG4BPhkRLwNXAccAJwM7SG5poXysef8Op0fEm4HfAD4h6R0Vtm1KjJL2A94L/Gta1GrfYTkTiSfv7/LPgdeA69OiHcDciFgE/BHwL5IObkKM9fyb5vkdfoCxFy2t8v1NiJPFXtuAozKvjwS2N+rgkjpJEsX1EbEKICKei4iRiNgD/BNJVVmlWLcxtsqgrr9DRGxPfz4PfCeN57n0NrpwO/18M2MkSWQPRcRzaawt9R1S3+9r9DNK1ql/HbVX2VQk6WLgN4EPplUjpNU7P02frydpEziu0THW+W+ay3eY7ut84KZM3C3x/U2Uk8Ve/wkcK2l+enV6EbCmEQdO6yC/BjwaEV/KlB+e2ew8oNDjYg1wUdpTYj5wLPBgWq2xS9Jp6T4/DNxapxgPlHRQ4TlJI+jDaSwXp5tdnDlew2NMjbmaa6XvMHPcen1f2X39NnBX4cQ+GZLOAf4UeG9E7M6Uz5HUkT4/Oo3xyUbHWOe/aS7fIfBrwGMRMVq91Crf34Q1q2W9FR/Au0l6Ij0B/HkDj/t2klvLHwEb08e7gW8Cm9PyNcDhmc/8eRpnP5neOkAvyX+eJ4C/Jx2lX4cYjybpabIJ2FL4fkjqT+8EHk9/HtLEGGcBPwVelylr2ndIkrR2AMMkV4gfref3BRxAUt22laQ3zdF1inErST154d9ioTfOBenffhPwEPBbecdYJr66/U3ziC8tvwb4WNG2Df/+6vnwdB9mZlaVq6HMzKwqJwszM6vKycLMzKpysjAzs6qcLMzMrConC5syJP2ipBslPSHpEUnfk3Rcs+MqR9IZkt42zs8cl/5eW5XMUnyzpMPqHNcSSQvruU9rf04WNiWkg5m+A/wwIo6JiIXAnwF1PZHW2RlAzclC0gHA7cBVEfFLEfE/SKa+mFPnuJaQzOBqNsrJwqaKM4HhiBhdIyAiNkbEvyuxUtLDStYMuBBGr+zvSa/OfyxphaQPSnow3e6YdLtrJF2lZM2RJyW9M53A7lFJ1xSOJ+ldku6X9JCkf03n+iqsA/L5tHyzpOOVTBj5MeByJWsb/Kqk96UxbpJ0b4nf8XeA+yPitszveHdEPCzpAEnfSPe/QdKZ6bE/IunvMzF+V9IZ6fP/J+kL6fHWSTosvdN5L7AyjeuYuvx1rO05WdhU8SZgfZn3zieZdO4kkmkYVmamjDgJuAw4Efhd4LiIOBX4Z+DSzD5mA2cBlwO3AVcAJwAnSjpZ0qHAp4Ffi2SyxT6SyeIKXkjLryJZi+Fp4B+BKyLi5Ij4d+AzwOKIOInkhD2e3/ETABFxIsmUJ9emdyKVHAisS493L/AHEfEfJKOil6ZxPVFlHzZNOFnYdPB24IZIJp97DrgH+JX0vf+MZD2RV0imWvhBWr6ZZLGagtsime5gM8m005sjmchuS7rdaSRVN/cpWRntYpLFlwpWpT/XF+036z7gGkl/QLKA03h/x28CRMRjwE9IJqmr5FXguzXEZcbMZgdgVidbSCZaK6XSYjGvZJ7vybzew9j/H6+U2Ca73QhwR0R8oMpxRijz/y4iPibpLcB7gI2STo50ltLUFuCdZfZf7nd8jbEXhdm7jeHYO99P2bjMwHcWNnXcBeyfXpUDIOlXJL2TpIrlQkkdkuaQLIX5YJ2Pvw44XdIvpceeVUNPrF0ky+gW4j0mIh6IiM8ALzB2um2AfwHeJuk9mc+cI+lEkt/xg2nZccBcksn0ngZOljRD0lHsnc675rjMwMnCpoj0Cvk84NfTrrNbSNZq3k7SS+pHJLN93gX8SUT8V52PvxP4CHCDpB+RJI/jq3zsNuC8QgM3SVvKZkkPk5z8NxUdY4hkjYlLJT0u6ZH0mM8DXwE6JG0mWUPhI2nV2n3AUyTVZ39NMttpNTcCS9OGcjdwG4BnnTUzs+p8Z2FmZlU5WZiZWVVOFmZmVpWThZmZVeVkYWZmVTlZmJlZVU4WZmZW1f8HdMA2QUtcMM4AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.scatter(all_posting[\"submissions_count\"], all_posting[\"comments_count\"])\n",
    "plt.xlabel(\"Comments Count\")\n",
    "plt.ylabel(\"Submissions Count\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Comment extreme: \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>comments_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11308</th>\n",
       "      <td>NoMoreNicksLeft</td>\n",
       "      <td>8465</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                author  comments_count\n",
       "11308  NoMoreNicksLeft            8465"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Comment extreme: \")\n",
    "author_comments.sort_values(by=\"comments_count\", ascending=False).iloc[:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions extreme: \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>submissions_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>84823</th>\n",
       "      <td>gst</td>\n",
       "      <td>18870</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      author  submissions_count\n",
       "84823    gst              18870"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Submissions extreme: \")\n",
    "author_submissions.sort_values(by=\"submissions_count\", ascending=False).iloc[:1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q6: Submissions with the most comments and their subreddits. Show their info (subreddit and context),  skip over18"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>link_id</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155775</th>\n",
       "      <td>6nz1k</td>\n",
       "      <td>1313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27284</th>\n",
       "      <td>675oj</td>\n",
       "      <td>1225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351484</th>\n",
       "      <td>7beo2</td>\n",
       "      <td>1074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27511</th>\n",
       "      <td>676ja</td>\n",
       "      <td>961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247824</th>\n",
       "      <td>6z9op</td>\n",
       "      <td>913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201186</th>\n",
       "      <td>6tmfu</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201187</th>\n",
       "      <td>6tmfz</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201190</th>\n",
       "      <td>6tmgi</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201194</th>\n",
       "      <td>6tmh1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439513</th>\n",
       "      <td>7mq30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>439514 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       link_id  counts\n",
       "155775   6nz1k    1313\n",
       "27284    675oj    1225\n",
       "351484   7beo2    1074\n",
       "27511    676ja     961\n",
       "247824   6z9op     913\n",
       "...        ...     ...\n",
       "201186   6tmfu       1\n",
       "201187   6tmfz       1\n",
       "201190   6tmgi       1\n",
       "201194   6tmh1       1\n",
       "439513   7mq30       1\n",
       "\n",
       "[439514 rows x 2 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments_submissions = comments[[\"link_id\"]].groupby(\"link_id\").size().reset_index(name=\"counts\")\n",
    "comments_submissions = comments_submissions.sort_values(by=\"counts\", ascending=False)\n",
    "comments_submissions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> These are unflitered comments\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>url</th>\n",
       "      <th>permalink</th>\n",
       "      <th>score</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>154071</th>\n",
       "      <td>zekel</td>\n",
       "      <td>science</td>\n",
       "      <td>http://hundredpushups.com</td>\n",
       "      <td>/r/science/comments/6nz1k/got_six_weeks_try_th...</td>\n",
       "      <td>1621</td>\n",
       "      <td>1313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27108</th>\n",
       "      <td>matiasklein</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>https://www.reddit.com/r/reddit.com/comments/6...</td>\n",
       "      <td>/r/reddit.com/comments/675oj/post_the_funniest...</td>\n",
       "      <td>1098</td>\n",
       "      <td>1225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347032</th>\n",
       "      <td>willjohnston</td>\n",
       "      <td>politics</td>\n",
       "      <td>https://www.reddit.com/r/politics/comments/7be...</td>\n",
       "      <td>/r/politics/comments/7beo2/obama_wins_the_pres...</td>\n",
       "      <td>8538</td>\n",
       "      <td>1074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27334</th>\n",
       "      <td>rpi22</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>http://www.washingtonpost.com/wp-dyn/content/c...</td>\n",
       "      <td>/r/reddit.com/comments/676ja/new_study_confirm...</td>\n",
       "      <td>669</td>\n",
       "      <td>961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244944</th>\n",
       "      <td>georgeb</td>\n",
       "      <td>programming</td>\n",
       "      <td>http://www.google.com/chrome</td>\n",
       "      <td>/r/programming/comments/6z9op/chrome_is_here/</td>\n",
       "      <td>1904</td>\n",
       "      <td>913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>428848</th>\n",
       "      <td>Schlichten</td>\n",
       "      <td>worldnews</td>\n",
       "      <td>http://www.dailykos.com/story/2008/12/28/11443...</td>\n",
       "      <td>/r/worldnews/comments/7m6m4/today_i_end_my_sup...</td>\n",
       "      <td>1589</td>\n",
       "      <td>904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200875</th>\n",
       "      <td>TheRealStick</td>\n",
       "      <td>politics</td>\n",
       "      <td>https://www.reddit.com/r/politics/comments/6tv...</td>\n",
       "      <td>/r/politics/comments/6tvaz/im_a_bleedingheart_...</td>\n",
       "      <td>788</td>\n",
       "      <td>840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162829</th>\n",
       "      <td>jtmarlin</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>http://www.nytimes.com/aponline/business/AP-Sc...</td>\n",
       "      <td>/r/reddit.com/comments/6p30u/supreme_court_rul...</td>\n",
       "      <td>1071</td>\n",
       "      <td>787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285037</th>\n",
       "      <td>IM_A_REPTILIAN</td>\n",
       "      <td>politics</td>\n",
       "      <td>http://www.msnbc.msn.com/id/26884523/?</td>\n",
       "      <td>/r/politics/comments/7488a/bailout_does_not_pa...</td>\n",
       "      <td>3361</td>\n",
       "      <td>765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133321</th>\n",
       "      <td>valeriepieris</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>http://www.txstudy.org/please.html</td>\n",
       "      <td>/r/reddit.com/comments/6lbr7/is_reddit_really_...</td>\n",
       "      <td>492</td>\n",
       "      <td>762</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                author    subreddit  \\\n",
       "154071           zekel      science   \n",
       "27108      matiasklein   reddit.com   \n",
       "347032    willjohnston     politics   \n",
       "27334            rpi22   reddit.com   \n",
       "244944         georgeb  programming   \n",
       "428848      Schlichten    worldnews   \n",
       "200875    TheRealStick     politics   \n",
       "162829        jtmarlin   reddit.com   \n",
       "285037  IM_A_REPTILIAN     politics   \n",
       "133321   valeriepieris   reddit.com   \n",
       "\n",
       "                                                      url  \\\n",
       "154071                          http://hundredpushups.com   \n",
       "27108   https://www.reddit.com/r/reddit.com/comments/6...   \n",
       "347032  https://www.reddit.com/r/politics/comments/7be...   \n",
       "27334   http://www.washingtonpost.com/wp-dyn/content/c...   \n",
       "244944                       http://www.google.com/chrome   \n",
       "428848  http://www.dailykos.com/story/2008/12/28/11443...   \n",
       "200875  https://www.reddit.com/r/politics/comments/6tv...   \n",
       "162829  http://www.nytimes.com/aponline/business/AP-Sc...   \n",
       "285037             http://www.msnbc.msn.com/id/26884523/?   \n",
       "133321                 http://www.txstudy.org/please.html   \n",
       "\n",
       "                                                permalink  score  counts  \n",
       "154071  /r/science/comments/6nz1k/got_six_weeks_try_th...   1621    1313  \n",
       "27108   /r/reddit.com/comments/675oj/post_the_funniest...   1098    1225  \n",
       "347032  /r/politics/comments/7beo2/obama_wins_the_pres...   8538    1074  \n",
       "27334   /r/reddit.com/comments/676ja/new_study_confirm...    669     961  \n",
       "244944      /r/programming/comments/6z9op/chrome_is_here/   1904     913  \n",
       "428848  /r/worldnews/comments/7m6m4/today_i_end_my_sup...   1589     904  \n",
       "200875  /r/politics/comments/6tvaz/im_a_bleedingheart_...    788     840  \n",
       "162829  /r/reddit.com/comments/6p30u/supreme_court_rul...   1071     787  \n",
       "285037  /r/politics/comments/7488a/bailout_does_not_pa...   3361     765  \n",
       "133321  /r/reddit.com/comments/6lbr7/is_reddit_really_...    492     762  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sfw_comments = pd.merge(submissions[submissions[\"over_18\"] == False], comments_submissions, how=\"inner\", left_on=\"submissions_id\", right_on=\"link_id\")\n",
    "sfw_comments = sfw_comments.sort_values(by=\"counts\", ascending=False)\n",
    "sfw_comments[[\"author\",\"subreddit\",\"url\",\"permalink\",\"score\",\"counts\"]].iloc[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Filtered out: 6568 comments from nsfw submissions\n",
      "Check if any nsfw are left:  0\n"
     ]
    }
   ],
   "source": [
    "print(f\"Filtered out: {comments_submissions.shape[0] - sfw_comments.shape[0]} comments from nsfw submissions\")\n",
    "print(\"Check if any nsfw are left: \", sfw_comments[sfw_comments[\"over_18\"]==True].shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "sfw_comments[[\"author\",\"subreddit\",\"url\",\"permalink\",\"score\",\"counts\"]].iloc[:10].to_csv(\"data/pandas/static-analysis/submissions_most_comments.csv\")"
   ]
  }
 ],
 "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.8.5"
  },
  "vscode": {
   "interpreter": {
    "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}