Exploration_and_Cleaning.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    },
    "gpuClass": "standard"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "#### Setup"
      ],
      "metadata": {
        "id": "d-5jtaHeclUp"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "BBhQIWhfbHLY"
      },
      "outputs": [],
      "source": [
        "import re\n",
        "\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import seaborn as sns"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Interactive cell for pandas\n",
        "from google.colab import data_table\n",
        "data_table.enable_dataframe_formatter()"
      ],
      "metadata": {
        "id": "zq4jh5qobOnE"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import gdown\n",
        "gdown.download_folder(id=\"1pXY7Tp6hArE6j7C0M7E-OXUXAkzevotG\", quiet=True, use_cookies=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PVH2Dq9zbPrf",
        "outputId": "3e4345a2-653f-46a2-f79b-8e6c5742819b"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['/content/hackernews/models/20221028',\n",
              " '/content/hackernews/raw/hackernews-2019-2022-sessions.csv',\n",
              " '/content/hackernews/raw/hackernews-data-from-phone.json',\n",
              " '/content/hackernews/raw/hackernews-since-20221016.json',\n",
              " '/content/hackernews/raw/hackernews-stories-since-2018.csv',\n",
              " '/content/hackernews/raw/hackernews-stories-since-2022-10-14.csv',\n",
              " '/content/hackernews/raw/hackernews-urls-from-browser-deduplicated.csv',\n",
              " '/content/hackernews/tests/test_data_with_topics.csv',\n",
              " '/content/hackernews/tests/test_data.csv',\n",
              " '/content/hackernews/tests/test_set_with_topics.csv',\n",
              " '/content/hackernews/tests/test_set.csv',\n",
              " '/content/hackernews/tests/validation_set_with_topics.csv',\n",
              " '/content/hackernews/tests/validation_set.csv']"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# !mkdir hackernews\n",
        "# !mkdir hackernews/raw\n",
        "# !mv hackernews-* hackernews/raw"
      ],
      "metadata": {
        "id": "5CgeaozybRiu"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# raw data\n",
        "stories_2018 = f'/content/hackernews/raw/hackernews-stories-since-2018.csv'"
      ],
      "metadata": {
        "id": "j1ZbNa9MbSNM"
      },
      "execution_count": 8,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df_2018 = pd.read_csv(stories_2018,\n",
        "                      usecols=['title', 'url', 'id', 'timestamp_formatted'],\n",
        "                      parse_dates=['timestamp_formatted'])\n",
        "df_2018 = df_2018.rename(columns={\"timestamp_formatted\": \"ts\"})\n",
        "df_2018.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cSf8kUaTdZhE",
        "outputId": "063ec174-b54a-4cac-bcb3-9accd2d01743"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 1726813 entries, 0 to 1726812\n",
            "Data columns (total 4 columns):\n",
            " #   Column  Dtype              \n",
            "---  ------  -----              \n",
            " 0   title   object             \n",
            " 1   url     object             \n",
            " 2   id      int64              \n",
            " 3   ts      datetime64[ns, UTC]\n",
            "dtypes: datetime64[ns, UTC](1), int64(1), object(2)\n",
            "memory usage: 52.7+ MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0s00Ipi6Sjwm"
      },
      "source": [
        "#### Exploration & Cleaning"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Notes for future me**\n",
        "\n",
        "(1) Exploration are done using method chaining for fast iteration on the data\n",
        "    (data are transformed and displayed without actually stored, signal options)\n",
        "\n",
        "(2) Several approach I've just discovered might look weird, but somehow works:\n",
        "    - Using `.head(n)` makes sure I'm operating on the same sample of the data\n",
        "    - Chaining `apply` happens because I can't preserve values as columns\n",
        "      (I wonder if this indicate the need to just create a function?\n",
        "       but, what if it's an apply to avoid errors?)\n",
        "    - The logic inside the `lambda` are frequently \"swapped\" based on whether I'm\n",
        "      operating on dirty or clean data. For example, when exploring dirty data,\n",
        "      I use \"True if\" to filter correct values and drop them with `dropna` because I\n",
        "      want to see my transformation on the dirty columns. But when I finished\n",
        "      cleaning, I have to \"swap\" the logic back to \"else True\" so that dirty data\n",
        "      are the ones that is actually dropped (in this case I preserve the indexes)\n",
        "      Variations of these patterns arise in the arithmetic comparison too\n",
        "\n",
        "      example when cleaning one-word title:\n",
        "        exploring dirty data: `True if len(sentence) == 1 else None`  # True and None\n",
        "        keeping clean data  : `None if len(sentence) == 1 else True`  # is swapped\n",
        "\n",
        "(3) Anyway, this data is MUCH. MORE DIRTY than I'd expected. Seriously didn't expect\n",
        "    to spend this much time wrangling :/ really, bless community upvotes I guess\n",
        "    (now that I thought about it, maybe the \"dirty\" patterns arises in the number\n",
        "     of upvotes, kids, or empty urls? now I regret didn't get all the data hmm)"
      ],
      "metadata": {
        "id": "U2l5EVyodQFe"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DSUww7u6-bCR"
      },
      "source": [
        "##### Duplicate Titles :)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dPLmYRQIHg33",
        "outputId": "1b486429-0383-4352-a790-6ac45503f6ce"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(1726813, 4)"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ],
      "source": [
        "df_2018.shape"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zSo-RI_sGPwh",
        "outputId": "e7ae2a10-1389-441f-8d68-f165dceb0c6d"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Y Combinator: Bookmarklet                                                 1558\n",
              "Books for kids (death and bullying)                                        262\n",
              "Test                                                                       119\n",
              "WP Paint – WordPress Image Editor Free Version                             105\n",
              "Abortion Is Murder (Available on Spotify and iTunes  By, Evon Latrail)      98\n",
              "ghost                                                                       97\n",
              "Keybase                                                                     83\n",
              "Referral URL                                                                74\n",
              "GraphQL with NodeJs: From Beginner to Advanced Concepts                     67\n",
              "React Native Debug Tool                                                     60\n",
              "Super Affiliate System                                                      59\n",
              "Stowaway – Multi-hop Proxy Tool for pentesters                              58\n",
              "Check Out 'One Punch Man – Home Workout'                                    55\n",
              "Popcorn Time                                                                54\n",
              "Simple WiFi Profits                                                         51\n",
              "Buy Trustpilot Reviews – Positive Reviews ( 5 Star Reviews)                 50\n",
              "Hello                                                                       48\n",
              "React-native-easy-app – React Native One-stop solution                      46\n",
              "Credit Repair                                                               44\n",
              "Eyes Only                                                                   43\n",
              "Hi                                                                          43\n",
              "How to Become a Better Developer                                            43\n",
              "Buy Google Reviews                                                          42\n",
              "Popcorn Time – Watch Free Movies and TV Shows Instantly Online              42\n",
              "Books for Kids (Death and Bullying)                                         39\n",
              "When a parent loses a child. (Death of a child)                             38\n",
              "Ask HN                                                                      38\n",
              "Reddit Is Down                                                              38\n",
              "Free Software                                                               37\n",
              "Dash Cam King                                                               36\n",
              "Name: title, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ],
      "source": [
        "(\n",
        "    df_2018[\"title\"].value_counts()[:30]\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "id": "y6ozQS5vGZUY"
      },
      "outputs": [],
      "source": [
        "df_2018 = (\n",
        "            df_2018.drop_duplicates(subset=['title'])\n",
        "                   .reset_index(drop=True)\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "EveRHEfXHh1f",
        "outputId": "46e02c77-22ab-4bd0-a909-e3fbc102db92"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(1564079, 4)"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ],
      "source": [
        "df_2018.shape"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qwbzRKM8xYWt"
      },
      "source": [
        "##### One-word Titles"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1iCTrWszgG9W",
        "outputId": "62dafc2c-1128-444a-e94d-1c51eb084457"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Int64Index([5, 12, 70, 131, 148, 236, 285, 339, 411, 445], dtype='int64')"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ],
      "source": [
        "# Identify what \"one-word\" title seems to be\n",
        "# spoiler: it's trash\n",
        "f = (\n",
        "    df_2018[\"title\"]\n",
        "        .head(500)\n",
        "        .str\n",
        "        .split()\n",
        "        .apply(lambda x: len(x) if type(x) == list else True)  # weird float error :/\n",
        "        .apply(lambda x: None if x > 1 else True)\n",
        "        .dropna()\n",
        "        .index\n",
        ")\n",
        "f"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OXoTRgISH-80",
        "outputId": "d737dd03-bad2-4327-ef21-b17b079336ee"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5                                                 Solve\n",
              "12                                        SuperbowlEVE/\n",
              "70                                                  Kek\n",
              "131                                              Crypto\n",
              "148                     -kazakhstan-u20-vs-usa-u20-live\n",
              "236                                                 Aho\n",
              "285    &#20154;&#29983;&#23601;&#26159;&#36825;&#26679;\n",
              "339                                            Captable\n",
              "411                                                Fwef\n",
              "445                                               Nabil\n",
              "Name: title, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ],
      "source": [
        "df_2018.loc[f, 'title']"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ryQRoIRwk2uh",
        "outputId": "2d83a8dd-78c2-4c6d-dd22-59bc9d91d324"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Int64Index([0, 1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 13, 14, 15, 16], dtype='int64')"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ],
      "source": [
        "preserved_indexes = (\n",
        "                        df_2018[\"title\"]\n",
        "                            .str.split()\n",
        "                            .apply(lambda x: len(x) if type(x) == list else True)\n",
        "                            .apply(lambda x: None if x == 1 else x)\n",
        "                            .dropna()\n",
        "                            .index\n",
        ")\n",
        "preserved_indexes[:15]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "W_ZoU9Q9lxbz",
        "outputId": "12a74660-78b6-43f9-cba9-003e35b7f697"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(1548517, 4)"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ],
      "source": [
        "df_2018 = df_2018.loc[preserved_indexes].copy().reset_index(drop=True)\n",
        "df_2018.shape"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sOTVE6yOxigi"
      },
      "source": [
        "##### Job postings in Titles"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fJ6I3F8tmhKz",
        "outputId": "1261157b-3248-44d3-8091-5eafd530f925"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['Anyroad – Https://www.anyroad.com – Full-Time – San Francisco, CA ONSITE',\n",
              "       'Revved – Engineer – Cofounder – Delhi/NCR, India',\n",
              "       'We Are Hiring – Earn Rs.15000/- per Month – Simple Copy Paste Jobs',\n",
              "       'Twitch (Amazon) – Software Engineers – San Francisco or Remote (US/Europe) – FTE',\n",
              "       'Quobyte – Santa Clara, CA and Berlin, Germany – Full-Time – Onsite',\n",
              "       'Maurice – Open Beta – Serverless Load Testing Tool',\n",
              "       'AWS Blog Posts – Different Authors – Same Content?',\n",
              "       'Yocan Evolve Plus – Yocan Evolve Plus Review – Justchillglass $39.99',\n",
              "       'SmartKarma – Singapore| Full-Time Contract – React Native Developer',\n",
              "       'Qliiq.com – Save Your Bookmarks in Qliiq.com – Seek for Feedback',\n",
              "       'Wyre – Senior Full Stack Engineers – Onsite – San Francisco, CA – $100-120k',\n",
              "       'BCG Gamma – Senior Software Engineer – Paris or London – Full-Time – Onsite',\n",
              "       'BeFit Keto Cut– {Update 2020 Reviews} – Ingredients|Prise|Does It Work',\n",
              "       'Heart of Midlothian FC – Celtic FC – Livestream',\n",
              "       'Core Keto Pro Reviews – What Is Core Keto Pro? – Is Core Keto Pro Safe to Take?',\n",
              "       'SEEKING WORK – Remote – Software Architect – Software Development',\n",
              "       'Numph – Numph Clothing – Buy Numph Online on Pintucked.com  – Pintucked'],\n",
              "      dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ],
      "source": [
        "# Identify wrong type column, e.g. job postings that are posted on story\n",
        "# typically has several dashes (minimum 3) on its title\n",
        "# e.g. Company — Sites or Roles — Status — Other Criteria\n",
        "#\n",
        "# examples\n",
        "\"\"\"\n",
        "'Anyroad – Https://www.anyroad.com – Full-Time – San Francisco, CA ONSITE',\n",
        "'Quobyte – Santa Clara, CA and Berlin, Germany – Full-Time – Onsite',\n",
        "'Wyre – Senior Full Stack Engineers – Onsite – San Francisco, CA – $100-120k',\n",
        "'BCG Gamma – Senior Software Engineer – Paris or London – Full-Time – Onsite',\n",
        "'Ask HN: Best way to prepare for an Onsite interview at a Startup?',\n",
        "'Tabella – Ethical Hacker – Full-Time – Onsite – Prague, Czechia (EU)',\n",
        "'Sama AI – Sr Software Developer (Front-End) – Onsite (Mtl) or Remote – Full Time',\n",
        "'Olo – Multiple Jobs Available (see Below) – Full-Time| REMOTE or ONSITE (NYC)'\n",
        "\"\"\"\n",
        "\n",
        "f = (\n",
        "        df_2018['title']\n",
        "            .head(5000)\n",
        "            .str.lower()\n",
        "            .str.count('–')\n",
        "            .apply(lambda x: None if x < 2 else True)\n",
        "            .dropna()\n",
        "            .index\n",
        ")\n",
        "df_2018.head(5000).loc[f, 'title'].values"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8G8oz4X9IedT",
        "outputId": "1520f1f2-9801-47e1-fcee-c46f79702a38"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['Ask HN: Strategies to improve after finishing a product',\n",
              "       \"Ask HN: What to do with 10% of UK LTD i can't work at anymore\",\n",
              "       'Ask HN: What can I include in an effective communication workshop?',\n",
              "       ...,\n",
              "       \"Global Forest Watch – Discover the world's forests through data\",\n",
              "       'Search Google inside sheets and excel',\n",
              "       'Gov trying to ban magnet balls again'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ],
      "source": [
        "# extract clean indexes\n",
        "preserved_indexes = (\n",
        "        df_2018['title']\n",
        "            .str.count('–')\n",
        "            .apply(lambda x: None if x > 1 else True)\n",
        "            .dropna()\n",
        "            .index\n",
        ")\n",
        "df_2018.loc[preserved_indexes, 'title'].values"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "id": "4_HkLCAJyMXh"
      },
      "outputs": [],
      "source": [
        "df_2018 = df_2018.loc[preserved_indexes].copy().reset_index(drop=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZLRQpq-t6out",
        "outputId": "52c0b94d-1b88-4ce5-dc5e-3300ad3de0bd"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(1542775, 4)"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ],
      "source": [
        "df_2018.shape"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Vfweta7ByTNw"
      },
      "source": [
        "##### Links in Title"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jT1ZT7JtolEe",
        "outputId": "2def973d-fa28-41df-d3c8-a6ee2c452c31"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['HTTP: //wintersupplement.com/smart-blood-sugar-book/',\n",
              "       'HTTP: //Www.zzlcdz.com/',\n",
              "       'HTTP: //Wintersupplement.com/Vital-Xl/',\n",
              "       'HTTPS: //Keto4us.org/Trubodx-Keto/',\n",
              "       'HTTPS: //Buddysupplement.com/Biorexin/',\n",
              "       'HTTPS: //www.annews24.com/top-10-most-expensive-watches-in-the-world/',\n",
              "       'Essence CBD Oil HTTPS://buddysupplement.com/essence-cbd-oil-au/',\n",
              "       'Visit Here HTTPS://djsupplement.com/empowered-boost-testosterone/',\n",
              "       'HTTPS: //Djsupplement.com/Keto-Nutrition/',\n",
              "       'Buy HTTPS://www.facebook.com/Trim-Life-Keto-101398769051708'],\n",
              "      dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ],
      "source": [
        "# Identify trash link in the title, from \"http\" keyword\n",
        "f = (\n",
        "    df_2018['title'].head(1000)\n",
        "                    .str.lower()\n",
        "                    .str.contains('https?:\\s*')\n",
        "                    .values\n",
        ")\n",
        "df_2018.head(1000).loc[f].title.values"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "id": "g-V0Xkpf6VQY"
      },
      "outputs": [],
      "source": [
        "# Invert the match to get clean values\n",
        "f = (\n",
        "    df_2018['title'].str.lower()\n",
        "                    .str.contains('https?:\\s*')\n",
        "                    .values\n",
        ")\n",
        "df_2018 = df_2018.loc[~f].copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6m5ELa9L6mdQ",
        "outputId": "bfabd85b-b8e3-42b2-db15-61af0ca113ec"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(1541923, 4)"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ],
      "source": [
        "df_2018.shape"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZTy5BWY17NMY"
      },
      "source": [
        "##### Non-stories ([Something] HN)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0810qek_7_7y",
        "outputId": "112cfce9-abe3-4778-eb84-86c263313253"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Ask                                                                                37\n",
              "We need help with all aspects of marketing/ Contract help needed                    1\n",
              "When PMF meets market: $2.7M ARR ACHIEVED                                           1\n",
              "Tell                                                                                1\n",
              "Corporation Warfare-Protonmail cyberattack sponsored by states and corporations     1\n",
              "The impact of Airbnb on residential real estate price rises                         1\n",
              "Show                                                                                1\n",
              "It's Official. GitHub is slow                                                       1\n",
              "Aspiring YC founder – Can you get into YC without a technical cofounder?            1\n",
              "Looking for Job/Work Contract (Web Developer Javascript/PHP)                        1\n",
              "What podcast hosting provides the best analytics?                                   1\n",
              "What is the state of nanotech?                                                      1\n",
              "Who is using VB.NET? What are they using it for?                                    1\n",
              "Proposition 3                                                                       1\n",
              "Name: title, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ],
      "source": [
        "# explore dirty titles\n",
        "(\n",
        "    df_2018['title'].head(50)\n",
        "                    .str\n",
        "                    .split(\"HN: \")\n",
        "                    .apply(lambda x: x[0].strip())\n",
        "                    .value_counts()\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Pdq4U-bP9FeO",
        "outputId": "32e375f2-14fb-4087-9389-8a9c8d12d234"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0       Strategies to improve after finishing a product\n",
              "1     What to do with 10% of UK LTD i can't work at ...\n",
              "2     What can I include in an effective communicati...\n",
              "3     We need help with all aspects of marketing/ Co...\n",
              "4           Cost Effective Options for Email Marketing?\n",
              "5     Should I sell equity in a past startup on the ...\n",
              "6     How to get the flexibilty of containers withou...\n",
              "7     Why didn't the early web support more client s...\n",
              "8     Something like Khan Academy but full curriculu...\n",
              "9        What's the most fun tradition at your startup?\n",
              "10            When PMF meets market: $2.7M ARR ACHIEVED\n",
              "11    Programming book recommendations for autistic ...\n",
              "12            Enterprise Software Developers in London?\n",
              "13    Have you used Adderall or any other similar drug?\n",
              "14         Why landline make crazy noise while hung up?\n",
              "15    Where to Learn Kubernetes, Ansible and Terraform?\n",
              "16    Banned site-wide from Reddit for helping a fel...\n",
              "17    Is it possible to donate small amounts to any ...\n",
              "18    Corporation Warfare-Protonmail cyberattack spo...\n",
              "19    Which clean tech company is hiring (remote) so...\n",
              "Name: title, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ],
      "source": [
        "# clean the titles\n",
        "(\n",
        "    df_2018['title'].head(20)\n",
        "                    .str\n",
        "                    .split(\"HN: \")\n",
        "                    .apply(lambda x: x[-1])  # take the last one\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "metadata": {
        "id": "4Viv3PgOKvYX"
      },
      "outputs": [],
      "source": [
        "df_2018['title'] = df_2018['title'].str.split(\"HN: \").apply(lambda x: x[-1])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1Pzw0MSr7WIc"
      },
      "source": [
        "##### Last Word [Year] and [PDF]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 30,
      "metadata": {
        "id": "WvSID5H2Mg_Z"
      },
      "outputs": [],
      "source": [
        "dirty_last_word_examples = ['A First Lesson in Econometrics (1970) [pdf]',\n",
        "                    'Monarch: Google’s Planet-Scale In-Memory Time Series Database [pdf]',\n",
        "                    'High System Load with Low CPU Utilization on Linux? (2020)']"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 35,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RYIu1v6F9oPZ",
        "outputId": "ef46bb8c-7cb2-4d28-8ef0-6e366659f656"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array([], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ],
      "source": [
        "# examples of cleaning dirty last word\n",
        "pat = r'\\s\\(\\d+\\)|\\s\\[pdf\\]'\n",
        "\n",
        "(\n",
        "    df_2018\n",
        "        .loc[\n",
        "            df_2018['title'].isin(dirty_last_word_examples),\n",
        "            'title'\n",
        "        ]\n",
        "        .str\n",
        "        .replace(pat, '', regex=True)\n",
        "        .values\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 32,
      "metadata": {
        "id": "lcia4LXTKX-A"
      },
      "outputs": [],
      "source": [
        "df_2018['title'] = df_2018['title'].str.replace(pat, '', regex=True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S8Dcgld7OwSd"
      },
      "source": [
        "##### Non-English language"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4Qn4m42lOyJc"
      },
      "outputs": [],
      "source": [
        "\"\"\"\n",
        "Don't think I'll be using this for now because it might not work properly\n",
        "with short texts according to the docs, which can be seen below, and my\n",
        "tokenizer is probably good enough at this point (could recognize HN stuffs)\n",
        "\"\"\"\n",
        "f = (\n",
        "    df_2018['title'].head(100)\n",
        "        .apply(detect)\n",
        "        .apply(lambda x: None if x == 'en' else 1)\n",
        "        .dropna()\n",
        "        .index\n",
        ")\n",
        "f"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d4-ejnx6R4q8"
      },
      "outputs": [],
      "source": [
        "df_2018.loc[f, 'title']"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6GwM_0VeRq2s"
      },
      "outputs": [],
      "source": [
        "(\n",
        "    df_2018\n",
        "        .loc[f, 'title']\n",
        "        .apply(detect_langs)\n",
        ")"
      ]
    }
  ]
}