Prep_Muni_Data.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/BrockDSL/ARCH_Data_Explore/blob/main/Prep_Muni_Data.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WkzC_iG9Eqpt"
      },
      "source": [
        "# Preprocessing of Muni data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AwKsPs_pGpaU",
        "outputId": "dfa34799-29d3-4be0-e3fe-e1844a34092c"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "[nltk_data] Downloading package vader_lexicon to /root/nltk_data...\n"
          ]
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/nltk/twitter/__init__.py:20: UserWarning: The twython library has not been installed. Some functionality from the twitter package will not be available.\n",
            "  warnings.warn(\"The twython library has not been installed. \"\n"
          ]
        }
      ],
      "source": [
        "import pandas as pd\n",
        "import gdown\n",
        "\n",
        "#Sentiment just for fun, going to use VADER\n",
        "import nltk\n",
        "from nltk.sentiment.vader import SentimentIntensityAnalyzer\n",
        "nltk.download('vader_lexicon')from google.colab import files\n",
        "\n",
        "\n",
        "pd.set_option('display.max_columns', None)\n",
        "pd.set_option('display.max_colwidth', False)\n",
        "pd.set_option('display.max_rows', 200)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-c02VmukJZ66",
        "outputId": "62c48e65-a9f1-4f16-b95b-7233183fffbf"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "Downloading...\n",
            "From: https://drive.google.com/u/0/uc?id=1SzIpVc2fYLQTIasZZi3FnR7WhRRhsbJz&export=download\n",
            "To: /content/web-pages_full.csv.gz\n",
            "100%|██████████| 602M/602M [00:07<00:00, 81.4MB/s]\n"
          ]
        }
      ],
      "source": [
        "#Need to update to Dec 31 Crawl infoy\n",
        "gdown.download('https://drive.google.com/u/0/uc?id=1SzIpVc2fYLQTIasZZi3FnR7WhRRhsbJz&export=download','web-pages_full.csv.gz',quiet=False)\n",
        "!gunzip -k web-pages_full.csv.gz"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ur2LFbilNx3M"
      },
      "outputs": [],
      "source": [
        "archive_text = pd.read_csv(\"web-pages_full.csv\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NhW-_jhOOc2q"
      },
      "outputs": [],
      "source": [
        "archive_text['crawl_date']= pd.to_datetime(archive_text['crawl_date'],format='%Y%m%d')\n",
        "archive_text = archive_text[archive_text['language'] =='en']"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "GWLM8ZzTN3WS",
        "outputId": "28451c90-29a2-4645-ae12-41896cb0284e"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-1acd9ed6-7e88-452e-96d2-59af38f565d5\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>crawl_date</th>\n",
              "      <th>url</th>\n",
              "      <th>mime_type_web_server</th>\n",
              "      <th>mime_type_tika</th>\n",
              "      <th>language</th>\n",
              "      <th>content</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>domain</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>brocku.ca</th>\n",
              "      <td>214524</td>\n",
              "      <td>214524</td>\n",
              "      <td>214524</td>\n",
              "      <td>214524</td>\n",
              "      <td>214524</td>\n",
              "      <td>214524</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>youtube.com</th>\n",
              "      <td>44443</td>\n",
              "      <td>44443</td>\n",
              "      <td>44443</td>\n",
              "      <td>44443</td>\n",
              "      <td>44443</td>\n",
              "      <td>44443</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>gncc.ca</th>\n",
              "      <td>44428</td>\n",
              "      <td>44428</td>\n",
              "      <td>44428</td>\n",
              "      <td>44428</td>\n",
              "      <td>44428</td>\n",
              "      <td>44428</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>twitter.com</th>\n",
              "      <td>36782</td>\n",
              "      <td>36782</td>\n",
              "      <td>36782</td>\n",
              "      <td>36782</td>\n",
              "      <td>36782</td>\n",
              "      <td>36782</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>portcolborne.ca</th>\n",
              "      <td>26079</td>\n",
              "      <td>26079</td>\n",
              "      <td>26079</td>\n",
              "      <td>26079</td>\n",
              "      <td>26079</td>\n",
              "      <td>26079</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>gobadgers.ca</th>\n",
              "      <td>21022</td>\n",
              "      <td>21022</td>\n",
              "      <td>21022</td>\n",
              "      <td>21022</td>\n",
              "      <td>21022</td>\n",
              "      <td>21022</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>niagaraanglican.ca</th>\n",
              "      <td>18808</td>\n",
              "      <td>18808</td>\n",
              "      <td>18808</td>\n",
              "      <td>18808</td>\n",
              "      <td>18808</td>\n",
              "      <td>18808</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>niagarahealth.on.ca</th>\n",
              "      <td>15179</td>\n",
              "      <td>15179</td>\n",
              "      <td>15179</td>\n",
              "      <td>15179</td>\n",
              "      <td>15179</td>\n",
              "      <td>15179</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>pelhamlibrary.on.ca</th>\n",
              "      <td>14221</td>\n",
              "      <td>14221</td>\n",
              "      <td>14221</td>\n",
              "      <td>14221</td>\n",
              "      <td>14221</td>\n",
              "      <td>14221</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>stcatharinesstandard.ca</th>\n",
              "      <td>13650</td>\n",
              "      <td>13650</td>\n",
              "      <td>13650</td>\n",
              "      <td>13650</td>\n",
              "      <td>13650</td>\n",
              "      <td>13650</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>notl.com</th>\n",
              "      <td>12340</td>\n",
              "      <td>12340</td>\n",
              "      <td>12340</td>\n",
              "      <td>12340</td>\n",
              "      <td>12340</td>\n",
              "      <td>12340</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>niagararegion.ca</th>\n",
              "      <td>11816</td>\n",
              "      <td>11816</td>\n",
              "      <td>11816</td>\n",
              "      <td>11816</td>\n",
              "      <td>11816</td>\n",
              "      <td>11816</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>google.com</th>\n",
              "      <td>11804</td>\n",
              "      <td>11804</td>\n",
              "      <td>11804</td>\n",
              "      <td>11804</td>\n",
              "      <td>11804</td>\n",
              "      <td>11804</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>thehopecentre.net</th>\n",
              "      <td>8466</td>\n",
              "      <td>8466</td>\n",
              "      <td>8466</td>\n",
              "      <td>8466</td>\n",
              "      <td>8466</td>\n",
              "      <td>8466</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>niagarafallsreview.ca</th>\n",
              "      <td>8463</td>\n",
              "      <td>8463</td>\n",
              "      <td>8463</td>\n",
              "      <td>8463</td>\n",
              "      <td>8463</td>\n",
              "      <td>8463</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>pinterest.com</th>\n",
              "      <td>8240</td>\n",
              "      <td>8240</td>\n",
              "      <td>8240</td>\n",
              "      <td>8240</td>\n",
              "      <td>8240</td>\n",
              "      <td>8240</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>niagaracollege.ca</th>\n",
              "      <td>7041</td>\n",
              "      <td>7041</td>\n",
              "      <td>7041</td>\n",
              "      <td>7041</td>\n",
              "      <td>7041</td>\n",
              "      <td>7041</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>eventbrite.ca</th>\n",
              "      <td>5887</td>\n",
              "      <td>5887</td>\n",
              "      <td>5887</td>\n",
              "      <td>5887</td>\n",
              "      <td>5887</td>\n",
              "      <td>5887</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>tripadvisor.ca</th>\n",
              "      <td>5456</td>\n",
              "      <td>5456</td>\n",
              "      <td>5456</td>\n",
              "      <td>5456</td>\n",
              "      <td>5456</td>\n",
              "      <td>5456</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>cbc.ca</th>\n",
              "      <td>5344</td>\n",
              "      <td>5344</td>\n",
              "      <td>5344</td>\n",
              "      <td>5344</td>\n",
              "      <td>5344</td>\n",
              "      <td>5344</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>wainfleet.ca</th>\n",
              "      <td>4165</td>\n",
              "      <td>4165</td>\n",
              "      <td>4165</td>\n",
              "      <td>4165</td>\n",
              "      <td>4165</td>\n",
              "      <td>4165</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>thorold.ca</th>\n",
              "      <td>3913</td>\n",
              "      <td>3913</td>\n",
              "      <td>3913</td>\n",
              "      <td>3913</td>\n",
              "      <td>3913</td>\n",
              "      <td>3913</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>niagarathisweek.com</th>\n",
              "      <td>3322</td>\n",
              "      <td>3322</td>\n",
              "      <td>3322</td>\n",
              "      <td>3322</td>\n",
              "      <td>3322</td>\n",
              "      <td>3322</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>zoomprospector.com</th>\n",
              "      <td>2718</td>\n",
              "      <td>2718</td>\n",
              "      <td>2718</td>\n",
              "      <td>2718</td>\n",
              "      <td>2718</td>\n",
              "      <td>2718</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mozilla.org</th>\n",
              "      <td>2421</td>\n",
              "      <td>2421</td>\n",
              "      <td>2421</td>\n",
              "      <td>2421</td>\n",
              "      <td>2421</td>\n",
              "      <td>2421</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>wordpress.org</th>\n",
              "      <td>2132</td>\n",
              "      <td>2132</td>\n",
              "      <td>2132</td>\n",
              "      <td>2132</td>\n",
              "      <td>2132</td>\n",
              "      <td>2132</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>niagaraevergreen.ca</th>\n",
              "      <td>2037</td>\n",
              "      <td>2037</td>\n",
              "      <td>2037</td>\n",
              "      <td>2037</td>\n",
              "      <td>2037</td>\n",
              "      <td>2037</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>canada.ca</th>\n",
              "      <td>1800</td>\n",
              "      <td>1800</td>\n",
              "      <td>1800</td>\n",
              "      <td>1800</td>\n",
              "      <td>1800</td>\n",
              "      <td>1800</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>nflibrary.ca</th>\n",
              "      <td>1743</td>\n",
              "      <td>1743</td>\n",
              "      <td>1743</td>\n",
              "      <td>1743</td>\n",
              "      <td>1743</td>\n",
              "      <td>1743</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>wordpress.com</th>\n",
              "      <td>1701</td>\n",
              "      <td>1701</td>\n",
              "      <td>1701</td>\n",
              "      <td>1701</td>\n",
              "      <td>1701</td>\n",
              "      <td>1701</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>ontario.ca</th>\n",
              "      <td>1693</td>\n",
              "      <td>1693</td>\n",
              "      <td>1693</td>\n",
              "      <td>1693</td>\n",
              "      <td>1693</td>\n",
              "      <td>1693</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>iheartradio.ca</th>\n",
              "      <td>1170</td>\n",
              "      <td>1170</td>\n",
              "      <td>1170</td>\n",
              "      <td>1170</td>\n",
              "      <td>1170</td>\n",
              "      <td>1170</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>who.int</th>\n",
              "      <td>1068</td>\n",
              "      <td>1068</td>\n",
              "      <td>1068</td>\n",
              "      <td>1068</td>\n",
              "      <td>1068</td>\n",
              "      <td>1068</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>moatads.com</th>\n",
              "      <td>990</td>\n",
              "      <td>990</td>\n",
              "      <td>990</td>\n",
              "      <td>990</td>\n",
              "      <td>990</td>\n",
              "      <td>990</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>freeskreen.com</th>\n",
              "      <td>942</td>\n",
              "      <td>942</td>\n",
              "      <td>942</td>\n",
              "      <td>942</td>\n",
              "      <td>942</td>\n",
              "      <td>942</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mppapi.io</th>\n",
              "      <td>862</td>\n",
              "      <td>862</td>\n",
              "      <td>862</td>\n",
              "      <td>862</td>\n",
              "      <td>862</td>\n",
              "      <td>862</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>theweathernetwork.com</th>\n",
              "      <td>859</td>\n",
              "      <td>859</td>\n",
              "      <td>859</td>\n",
              "      <td>859</td>\n",
              "      <td>859</td>\n",
              "      <td>859</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>eyereturn.com</th>\n",
              "      <td>812</td>\n",
              "      <td>812</td>\n",
              "      <td>812</td>\n",
              "      <td>812</td>\n",
              "      <td>812</td>\n",
              "      <td>812</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>apple.com</th>\n",
              "      <td>810</td>\n",
              "      <td>810</td>\n",
              "      <td>810</td>\n",
              "      <td>810</td>\n",
              "      <td>810</td>\n",
              "      <td>810</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>theglobeandmail.com</th>\n",
              "      <td>801</td>\n",
              "      <td>801</td>\n",
              "      <td>801</td>\n",
              "      <td>801</td>\n",
              "      <td>801</td>\n",
              "      <td>801</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>projectshare.ca</th>\n",
              "      <td>787</td>\n",
              "      <td>787</td>\n",
              "      <td>787</td>\n",
              "      <td>787</td>\n",
              "      <td>787</td>\n",
              "      <td>787</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>polarcdn-terrax.com</th>\n",
              "      <td>786</td>\n",
              "      <td>786</td>\n",
              "      <td>786</td>\n",
              "      <td>786</td>\n",
              "      <td>786</td>\n",
              "      <td>786</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>anglican.ca</th>\n",
              "      <td>749</td>\n",
              "      <td>749</td>\n",
              "      <td>749</td>\n",
              "      <td>749</td>\n",
              "      <td>749</td>\n",
              "      <td>749</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>windstream.net</th>\n",
              "      <td>748</td>\n",
              "      <td>748</td>\n",
              "      <td>748</td>\n",
              "      <td>748</td>\n",
              "      <td>748</td>\n",
              "      <td>748</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>chch.com</th>\n",
              "      <td>746</td>\n",
              "      <td>746</td>\n",
              "      <td>746</td>\n",
              "      <td>746</td>\n",
              "      <td>746</td>\n",
              "      <td>746</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>wivb.com</th>\n",
              "      <td>728</td>\n",
              "      <td>728</td>\n",
              "      <td>728</td>\n",
              "      <td>728</td>\n",
              "      <td>728</td>\n",
              "      <td>728</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>thespec.com</th>\n",
              "      <td>689</td>\n",
              "      <td>689</td>\n",
              "      <td>689</td>\n",
              "      <td>689</td>\n",
              "      <td>689</td>\n",
              "      <td>689</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>centurylink.net</th>\n",
              "      <td>674</td>\n",
              "      <td>674</td>\n",
              "      <td>674</td>\n",
              "      <td>674</td>\n",
              "      <td>674</td>\n",
              "      <td>674</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>wellandtribune.ca</th>\n",
              "      <td>585</td>\n",
              "      <td>585</td>\n",
              "      <td>585</td>\n",
              "      <td>585</td>\n",
              "      <td>585</td>\n",
              "      <td>585</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>forterie.ca</th>\n",
              "      <td>577</td>\n",
              "      <td>577</td>\n",
              "      <td>577</td>\n",
              "      <td>577</td>\n",
              "      <td>577</td>\n",
              "      <td>577</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1acd9ed6-7e88-452e-96d2-59af38f565d5')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-1acd9ed6-7e88-452e-96d2-59af38f565d5 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-1acd9ed6-7e88-452e-96d2-59af38f565d5');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "                         crawl_date     url  mime_type_web_server  \\\n",
              "domain                                                              \n",
              "brocku.ca                214524      214524  214524                 \n",
              "youtube.com              44443       44443   44443                  \n",
              "gncc.ca                  44428       44428   44428                  \n",
              "twitter.com              36782       36782   36782                  \n",
              "portcolborne.ca          26079       26079   26079                  \n",
              "gobadgers.ca             21022       21022   21022                  \n",
              "niagaraanglican.ca       18808       18808   18808                  \n",
              "niagarahealth.on.ca      15179       15179   15179                  \n",
              "pelhamlibrary.on.ca      14221       14221   14221                  \n",
              "stcatharinesstandard.ca  13650       13650   13650                  \n",
              "notl.com                 12340       12340   12340                  \n",
              "niagararegion.ca         11816       11816   11816                  \n",
              "google.com               11804       11804   11804                  \n",
              "thehopecentre.net        8466        8466    8466                   \n",
              "niagarafallsreview.ca    8463        8463    8463                   \n",
              "pinterest.com            8240        8240    8240                   \n",
              "niagaracollege.ca        7041        7041    7041                   \n",
              "eventbrite.ca            5887        5887    5887                   \n",
              "tripadvisor.ca           5456        5456    5456                   \n",
              "cbc.ca                   5344        5344    5344                   \n",
              "wainfleet.ca             4165        4165    4165                   \n",
              "thorold.ca               3913        3913    3913                   \n",
              "niagarathisweek.com      3322        3322    3322                   \n",
              "zoomprospector.com       2718        2718    2718                   \n",
              "mozilla.org              2421        2421    2421                   \n",
              "wordpress.org            2132        2132    2132                   \n",
              "niagaraevergreen.ca      2037        2037    2037                   \n",
              "canada.ca                1800        1800    1800                   \n",
              "nflibrary.ca             1743        1743    1743                   \n",
              "wordpress.com            1701        1701    1701                   \n",
              "ontario.ca               1693        1693    1693                   \n",
              "iheartradio.ca           1170        1170    1170                   \n",
              "who.int                  1068        1068    1068                   \n",
              "moatads.com              990         990     990                    \n",
              "freeskreen.com           942         942     942                    \n",
              "mppapi.io                862         862     862                    \n",
              "theweathernetwork.com    859         859     859                    \n",
              "eyereturn.com            812         812     812                    \n",
              "apple.com                810         810     810                    \n",
              "theglobeandmail.com      801         801     801                    \n",
              "projectshare.ca          787         787     787                    \n",
              "polarcdn-terrax.com      786         786     786                    \n",
              "anglican.ca              749         749     749                    \n",
              "windstream.net           748         748     748                    \n",
              "chch.com                 746         746     746                    \n",
              "wivb.com                 728         728     728                    \n",
              "thespec.com              689         689     689                    \n",
              "centurylink.net          674         674     674                    \n",
              "wellandtribune.ca        585         585     585                    \n",
              "forterie.ca              577         577     577                    \n",
              "\n",
              "                         mime_type_tika  language  content  \n",
              "domain                                                      \n",
              "brocku.ca                214524          214524    214524   \n",
              "youtube.com              44443           44443     44443    \n",
              "gncc.ca                  44428           44428     44428    \n",
              "twitter.com              36782           36782     36782    \n",
              "portcolborne.ca          26079           26079     26079    \n",
              "gobadgers.ca             21022           21022     21022    \n",
              "niagaraanglican.ca       18808           18808     18808    \n",
              "niagarahealth.on.ca      15179           15179     15179    \n",
              "pelhamlibrary.on.ca      14221           14221     14221    \n",
              "stcatharinesstandard.ca  13650           13650     13650    \n",
              "notl.com                 12340           12340     12340    \n",
              "niagararegion.ca         11816           11816     11816    \n",
              "google.com               11804           11804     11804    \n",
              "thehopecentre.net        8466            8466      8466     \n",
              "niagarafallsreview.ca    8463            8463      8463     \n",
              "pinterest.com            8240            8240      8240     \n",
              "niagaracollege.ca        7041            7041      7041     \n",
              "eventbrite.ca            5887            5887      5887     \n",
              "tripadvisor.ca           5456            5456      5456     \n",
              "cbc.ca                   5344            5344      5344     \n",
              "wainfleet.ca             4165            4165      4165     \n",
              "thorold.ca               3913            3913      3913     \n",
              "niagarathisweek.com      3322            3322      3322     \n",
              "zoomprospector.com       2718            2718      2718     \n",
              "mozilla.org              2421            2421      2421     \n",
              "wordpress.org            2132            2132      2132     \n",
              "niagaraevergreen.ca      2037            2037      2037     \n",
              "canada.ca                1800            1800      1800     \n",
              "nflibrary.ca             1743            1743      1743     \n",
              "wordpress.com            1701            1701      1701     \n",
              "ontario.ca               1693            1693      1693     \n",
              "iheartradio.ca           1170            1170      1170     \n",
              "who.int                  1068            1068      1068     \n",
              "moatads.com              990             990       990      \n",
              "freeskreen.com           942             942       942      \n",
              "mppapi.io                862             862       862      \n",
              "theweathernetwork.com    859             859       859      \n",
              "eyereturn.com            812             812       812      \n",
              "apple.com                810             810       810      \n",
              "theglobeandmail.com      801             801       801      \n",
              "projectshare.ca          787             787       787      \n",
              "polarcdn-terrax.com      786             786       786      \n",
              "anglican.ca              749             749       749      \n",
              "windstream.net           748             748       748      \n",
              "chch.com                 746             746       746      \n",
              "wivb.com                 728             728       728      \n",
              "thespec.com              689             689       689      \n",
              "centurylink.net          674             674       674      \n",
              "wellandtribune.ca        585             585       585      \n",
              "forterie.ca              577             577       577      "
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "archive_text.groupby([\"domain\"]).count().sort_values(by=\"crawl_date\", ascending=False)[0:50]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hWkZlhHhPJ23"
      },
      "outputs": [],
      "source": [
        "#Municiple domains into a specific dataframe\n",
        "\n",
        "municiple_domains = [\n",
        "\n",
        "  \"niagararegion.ca\",\n",
        "  \"notl.com\",\n",
        "  \"stcatharines.ca\",\n",
        "  \"niagarafalls.ca\",\n",
        "  \"lincoln.ca\",\n",
        "  \"forterie.ca\",\n",
        "  \"grimsby.ca\",\n",
        "  \"portcolborne.ca\",\n",
        "  \"thorold.ca\",\n",
        "  \"welland.ca\",\n",
        "  \"westlincoln.ca\",\n",
        "  \"wainfleet.ca\",\n",
        "  \"pelham.ca\"\n",
        "]\n",
        "\n",
        "muni_text = archive_text[archive_text.domain.isin(municiple_domains)]\n",
        "muni_text = muni_text.reset_index()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BYMTDt4cUT-x"
      },
      "outputs": [],
      "source": [
        "del(muni_text['length'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Jz9i-fNITHMo"
      },
      "outputs": [],
      "source": [
        "#length via itertuples, no index\n",
        "\n",
        "lengths = []\n",
        "for row in muni_text.itertuples(index=False):\n",
        "  lengths.append(int(len(row.content)))\n",
        "\n",
        "len_df = pd.DataFrame(lengths,columns=[\"length\"])\n",
        "\n",
        "muni_text = muni_text.join(len_df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YEbB7NzKORrc"
      },
      "outputs": [],
      "source": [
        "#VADER\n",
        "v_pos = []\n",
        "v_neg = []\n",
        "v_neu = []\n",
        "v_comp = []\n",
        "\n",
        "sid = SentimentIntensityAnalyzer()\n",
        "for row in muni_text.itertuples(index=False):\n",
        "    ss = sid.polarity_scores(row.content)\n",
        "    v_pos.append(float(ss[\"pos\"]))\n",
        "    v_neg.append(float(ss[\"neg\"]))\n",
        "    v_neu.append(float(ss[\"neu\"]))\n",
        "    v_comp.append(float(ss[\"compound\"]))\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "09ask2lHPgfW"
      },
      "outputs": [],
      "source": [
        "v_pos_df = pd.DataFrame(v_pos,columns=[\"v_pos\"])\n",
        "v_neg_df = pd.DataFrame(v_neg,columns=[\"v_neg\"])\n",
        "v_neu_df = pd.DataFrame(v_neu,columns=[\"v_neu\"])\n",
        "v_comp_df = pd.DataFrame(v_comp,columns=[\"v_comp\"])\n",
        "\n",
        "muni_text = muni_text.join(v_pos_df)\n",
        "muni_text = muni_text.join(v_neg_df)\n",
        "muni_text = muni_text.join(v_neu_df)\n",
        "muni_text = muni_text.join(v_comp_df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "o0EdFosNEdNQ"
      },
      "outputs": [],
      "source": [
        "#just to make sure\n",
        "muni_text.sample(2)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "id": "CB-1UKFlm077",
        "outputId": "b79a7df4-ec4c-4da9-da00-2fb6c6bc98bb"
      },
      "outputs": [
        {
          "data": {
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/javascript": [
              "download(\"download_d1da5c02-ad98-4913-ba18-a8f870d8e823\", \"municipal_text.csv\", 489139307)"
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "#write of final CSV and download\n",
        "\n",
        "muni_text.to_csv(\"municipal_text.csv\",index=False)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "x66AwNpWiljU",
        "outputId": "6d0a50c0-4376-4e9e-ebf3-2090d3cf440c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "total 5.2G\n",
            "-rw-r--r-- 1 root root 467M Jan  6 17:36 municipal_text.csv\n",
            "drwxr-xr-x 1 root root 4.0K Dec 23 14:32 sample_data\n",
            "-rw-r--r-- 1 root root 4.1G Jan  6 15:55 web-pages_full.csv\n",
            "-rw-r--r-- 1 root root 575M Jan  6 15:55 web-pages_full.csv.gz\n"
          ]
        }
      ],
      "source": [
        "!ls -lh"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "files.download(\"municipal_text.csv\")\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "id": "uqeORq8yjGm3",
        "outputId": "63c4114f-bf20-4e96-c318-f5086c86aaf1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "application/javascript": [
              "download(\"download_3ab55cad-8ab7-44a8-a452-510e8435a178\", \"municipal_text.csv\", 489139307)"
            ],
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ]
          },
          "metadata": {}
        }
      ]
    }
  ],
  "metadata": {
    "colab": {
      "machine_shape": "hm",
      "name": "Prep Muni Data",
      "provenance": [],
      "collapsed_sections": [],
      "mount_file_id": "1CJtlL0EgChUFSURaI5u-06kFcVhTyIXC",
      "authorship_tag": "ABX9TyOYVuvfpc+l54QRg2j7VwdZ",
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}