count_quotes_words.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ctoCxAQpbnW4"
      },
      "source": [
        "# Count quotes and words for the first table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WHGiePplbnW9"
      },
      "source": [
        "## Count total number of quotes\n",
        "\n",
        "These come from the \"CBC_qt_output/Quotes_new\" dir. This is the 'last' place where we can count quotes, as in the sentiment output, the quotes for each article are merged into one document."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "DFOglSc0bnW9"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import json\n",
        "from pandas import json_normalize\n",
        "import ast\n",
        "from ast import literal_eval\n",
        "import os\n",
        "import glob\n",
        "import re\n",
        "import matplotlib.pyplot as plt"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "N6rP-dxtcmtS",
        "outputId": "9e8d4ed4-e77b-4733-9be6-ea4caf59662c"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Counting number of quotes\n",
        "We count number of quotes from files in 'CBC_qt_output/Quotes_new' dir, as this is the last place where we can count quotes individually (in future files, they are merged by article). **Important:** Because Quotes_new only contains files that were re-processed by using an online csv -> xlsx converter, and Feb and Jun were already previously processed in this manner, this folder does not contain new files for Feb and Jun. Instead, these files, named '2023_02_feb_CBC_news_qt.xlsx' and '2023_06_jun_CBC_news_qt.xlsx' live in the folder 'CBC_qt_output/old'."
      ],
      "metadata": {
        "id": "xd3A-seNjE0X"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "directory = '/content/drive/My Drive/evaluation_quotes/CBC_input'\n",
        "excel_files = glob.glob(os.path.join(directory, '2023*.xlsx'))\n",
        "quotes = []\n",
        "\n",
        "for file in excel_files:\n",
        "    try:\n",
        "        # read the full sheet and the quotes column\n",
        "        df = pd.read_excel(file, sheet_name='full')\n",
        "\n",
        "        # count the number of rows\n",
        "        num_rows_quotes = len(df)\n",
        "\n",
        "        # Store the result in the list\n",
        "        quotes.append({\n",
        "            'file': os.path.basename(file),\n",
        "            'rows_in_full': num_rows_quotes,\n",
        "        })\n",
        "\n",
        "    except Exception as e:\n",
        "        # Handle any errors (e.g., sheet not found)\n",
        "        print(f\"Could not process {file}: {e}\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kWK9OSMFj-cz",
        "outputId": "f869b535-47c1-40b0-efaf-a459dac0f132"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n",
            "/usr/local/lib/python3.10/dist-packages/openpyxl/styles/stylesheet.py:232: UserWarning: Workbook contains no stylesheet, using openpyxl's defaults\n",
            "  warn(\"Workbook contains no stylesheet, using openpyxl's defaults\")\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "quotes_df = pd.DataFrame(quotes)\n",
        "\n",
        "quotes_df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 426
        },
        "id": "lJjt8GdvvDIG",
        "outputId": "c8dd1446-67f6-47c3-c182-a69b597c369e"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                file  rows_in_full\n",
              "0   2023_01_jan_CBC_news_qt_new.xlsx         55161\n",
              "1       2023_02_feb_CBC_news_qt.xlsx         53574\n",
              "2       2023_06_jun_CBC_news_qt.xlsx         55454\n",
              "3   2023_04_apr_CBC_news_qt_new.xlsx         51383\n",
              "4   2023_05_may_CBC_news_qt_new.xlsx         58306\n",
              "5   2023_07_jul_CBC_news_qt_new.xlsx         51754\n",
              "6   2023_08_aug_CBC_news_qt_new.xlsx         51011\n",
              "7   2023_09_sep_CBC_news_qt_new.xlsx         52230\n",
              "8   2023_10_oct_CBC_news_qt_new.xlsx         55202\n",
              "9   2023_11_nov_CBC_news_qt_new.xlsx         55528\n",
              "10  2023_12_dec_CBC_news_qt_new.xlsx         47561\n",
              "11  2023_03_mar_CBC_news_qt_new.xlsx         60157"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-70e238a0-655f-416b-904b-183d8bc76b6a\" 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>file</th>\n",
              "      <th>rows_in_full</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2023_01_jan_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55161</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2023_02_feb_CBC_news_qt.xlsx</td>\n",
              "      <td>53574</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2023_06_jun_CBC_news_qt.xlsx</td>\n",
              "      <td>55454</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2023_04_apr_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51383</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2023_05_may_CBC_news_qt_new.xlsx</td>\n",
              "      <td>58306</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2023_07_jul_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51754</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2023_08_aug_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51011</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2023_09_sep_CBC_news_qt_new.xlsx</td>\n",
              "      <td>52230</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2023_10_oct_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55202</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2023_11_nov_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55528</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>2023_12_dec_CBC_news_qt_new.xlsx</td>\n",
              "      <td>47561</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>2023_03_mar_CBC_news_qt_new.xlsx</td>\n",
              "      <td>60157</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-70e238a0-655f-416b-904b-183d8bc76b6a')\"\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 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\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",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\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-70e238a0-655f-416b-904b-183d8bc76b6a 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-70e238a0-655f-416b-904b-183d8bc76b6a');\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",
              "\n",
              "\n",
              "<div id=\"df-e218d5fb-21dd-47a4-abc4-6b0922cb105f\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-e218d5fb-21dd-47a4-abc4-6b0922cb105f')\"\n",
              "            title=\"Suggest charts\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-e218d5fb-21dd-47a4-abc4-6b0922cb105f button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_5a05eac8-967e-451f-87b5-b2742d95a665\">\n",
              "    <style>\n",
              "      .colab-df-generate {\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-generate: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-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate: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",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('quotes_df')\"\n",
              "            title=\"Generate code using this dataframe.\"\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=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_5a05eac8-967e-451f-87b5-b2742d95a665 button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('quotes_df');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "quotes_df",
              "summary": "{\n  \"name\": \"quotes_df\",\n  \"rows\": 12,\n  \"fields\": [\n    {\n      \"column\": \"file\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 12,\n        \"samples\": [\n          \"2023_12_dec_CBC_news_qt_new.xlsx\",\n          \"2023_11_nov_CBC_news_qt_new.xlsx\",\n          \"2023_01_jan_CBC_news_qt_new.xlsx\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"rows_in_full\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 3427,\n        \"min\": 47561,\n        \"max\": 60157,\n        \"num_unique_values\": 12,\n        \"samples\": [\n          47561,\n          55528,\n          55161\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Calculate the total row by summing all numeric columns\n",
        "total_row = quotes_df[['rows_in_full']].sum()\n",
        "\n",
        "# Add 'Total' as a label for the 'Category' column in the total row\n",
        "total_row['file'] = 'Total'\n",
        "\n",
        "# Append the total row to the DataFrame\n",
        "quotes_df = pd.concat([quotes_df, pd.DataFrame([total_row])], ignore_index=True)\n",
        "\n",
        "# Display the updated DataFrame\n",
        "quotes_df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 457
        },
        "id": "Ld5cXxEgvLAg",
        "outputId": "13b8cc35-e4dc-41b7-a7a3-096baff9e2b9"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                file  rows_in_full\n",
              "0   2023_01_jan_CBC_news_qt_new.xlsx         55161\n",
              "1       2023_02_feb_CBC_news_qt.xlsx         53574\n",
              "2       2023_06_jun_CBC_news_qt.xlsx         55454\n",
              "3   2023_04_apr_CBC_news_qt_new.xlsx         51383\n",
              "4   2023_05_may_CBC_news_qt_new.xlsx         58306\n",
              "5   2023_07_jul_CBC_news_qt_new.xlsx         51754\n",
              "6   2023_08_aug_CBC_news_qt_new.xlsx         51011\n",
              "7   2023_09_sep_CBC_news_qt_new.xlsx         52230\n",
              "8   2023_10_oct_CBC_news_qt_new.xlsx         55202\n",
              "9   2023_11_nov_CBC_news_qt_new.xlsx         55528\n",
              "10  2023_12_dec_CBC_news_qt_new.xlsx         47561\n",
              "11  2023_03_mar_CBC_news_qt_new.xlsx         60157\n",
              "12                             Total        647321"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1923ed35-13ff-4bb7-b92e-0cdebb53aa4f\" 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>file</th>\n",
              "      <th>rows_in_full</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2023_01_jan_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55161</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2023_02_feb_CBC_news_qt.xlsx</td>\n",
              "      <td>53574</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2023_06_jun_CBC_news_qt.xlsx</td>\n",
              "      <td>55454</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2023_04_apr_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51383</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2023_05_may_CBC_news_qt_new.xlsx</td>\n",
              "      <td>58306</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2023_07_jul_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51754</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2023_08_aug_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51011</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2023_09_sep_CBC_news_qt_new.xlsx</td>\n",
              "      <td>52230</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2023_10_oct_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55202</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2023_11_nov_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55528</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>2023_12_dec_CBC_news_qt_new.xlsx</td>\n",
              "      <td>47561</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>2023_03_mar_CBC_news_qt_new.xlsx</td>\n",
              "      <td>60157</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Total</td>\n",
              "      <td>647321</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1923ed35-13ff-4bb7-b92e-0cdebb53aa4f')\"\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 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\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",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\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-1923ed35-13ff-4bb7-b92e-0cdebb53aa4f 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-1923ed35-13ff-4bb7-b92e-0cdebb53aa4f');\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",
              "\n",
              "\n",
              "<div id=\"df-94bc2301-bfaf-4d95-81a3-c868170a3c29\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-94bc2301-bfaf-4d95-81a3-c868170a3c29')\"\n",
              "            title=\"Suggest charts\"\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",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-94bc2301-bfaf-4d95-81a3-c868170a3c29 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_d3ae30a8-a6bf-4d3b-aaee-5978aa227bc0\">\n",
              "    <style>\n",
              "      .colab-df-generate {\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-generate: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-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate: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",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('quotes_df')\"\n",
              "            title=\"Generate code using this dataframe.\"\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=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_d3ae30a8-a6bf-4d3b-aaee-5978aa227bc0 button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('quotes_df');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "quotes_df",
              "summary": "{\n  \"name\": \"quotes_df\",\n  \"rows\": 13,\n  \"fields\": [\n    {\n      \"column\": \"file\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 13,\n        \"samples\": [\n          \"2023_03_mar_CBC_news_qt_new.xlsx\",\n          \"2023_11_nov_CBC_news_qt_new.xlsx\",\n          \"2023_01_jan_CBC_news_qt_new.xlsx\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"rows_in_full\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 164606,\n        \"min\": 47561,\n        \"max\": 647321,\n        \"num_unique_values\": 13,\n        \"samples\": [\n          60157,\n          55528,\n          55161\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Counting number of words in quotes and non-quotes\n",
        "Input dir is sentiment_output. We count the words in the following tabs/columns of each file; i.e. for each month:\n",
        "* quotes tab - quote column\n",
        "* non_quotes tab - non_quoted_text column\n",
        "\n",
        "In doing this by accessing files in my Google Drive, I split the text files in the output corpus into 'quotes' and 'non_quotes' folders to make accessing them easier.\n",
        "\n",
        "Paths are 'CBC_output_corpus/quotes' and 'CBC_output_corpus/non_quotes' respectively"
      ],
      "metadata": {
        "id": "MXnq-zFzkAvS"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "quotes_dir = '/content/drive/My Drive/evaluation_quotes/CBC_output_corpus/quotes'\n",
        "non_quotes_dir = '/content/drive/My Drive/evaluation_quotes/CBC_output_corpus/non_quotes'"
      ],
      "metadata": {
        "id": "5ynIoSnGkH1C"
      },
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "quote_txts = glob.glob(os.path.join(quotes_dir, '*.txt'))\n",
        "\n",
        "# Initialize a variable to store the total word count\n",
        "quotes_wc = 0\n",
        "\n",
        "for fp in quote_txts:\n",
        "  with open(fp, 'r', encoding='utf-8') as qt_txt:\n",
        "    lines = qt_txt.readlines()\n",
        "\n",
        "    # convert the lines to a df\n",
        "    df = pd.DataFrame(lines, columns=['text'])\n",
        "\n",
        "    # count the number of words in each line and sum them\n",
        "    df['word_count'] = df['text'].apply(lambda x: len(x.split()))\n",
        "    quotes_wc += df['word_count'].sum()\n",
        "\n",
        "# print total word count\n",
        "print(f\"Total number of words in all 'quotes' related text files: {quotes_wc}\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fN8u7SDj2Fsv",
        "outputId": "cfaa8495-3f93-4f4e-b8d5-2feae7df4a9e"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Total number of words in all 'quotes' related text files: 10873667\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "non_quote_txts = glob.glob(os.path.join(non_quotes_dir, '*.txt'))\n",
        "\n",
        "# Initialize a variable to store the total word count\n",
        "non_quotes_wc = 0\n",
        "\n",
        "for fp in non_quote_txts:\n",
        "  with open(fp, 'r', encoding='utf-8') as qt_txt:\n",
        "    lines = qt_txt.readlines()\n",
        "\n",
        "    # convert the lines to a df\n",
        "    df = pd.DataFrame(lines, columns=['text'])\n",
        "\n",
        "    # count the number of words in each line and sum them\n",
        "    df['word_count'] = df['text'].apply(lambda x: len(x.split()))\n",
        "    non_quotes_wc += df['word_count'].sum()\n",
        "\n",
        "# print total word count\n",
        "print(f\"Total number of words in all 'non_quotes' related text files: {non_quotes_wc}\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "i4-tfgoi51tR",
        "outputId": "dd20af21-cc32-4e7c-febd-c143275397d1"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Total number of words in all 'non_quotes' related text files: 17314398\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Total number of words in quotes and non-quotes: \", str(quotes_wc + non_quotes_wc))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "m013I-wy6ixg",
        "outputId": "bb0e7882-fa46-4614-acea-d1f0996ec720"
      },
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Total number of words in quotes and non-quotes:  28188065\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Counting original corpus for words\n",
        "Because total word count also includes speakers and verbs"
      ],
      "metadata": {
        "id": "FFNwxoAv-yrl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "input = '/content/drive/My Drive/evaluation_quotes/CBC_input_correct_encoding'\n",
        "xlsx_files = glob.glob(os.path.join(input, '*.xlsx'))\n",
        "\n",
        "total_word_count = 0\n",
        "\n",
        "for fp in xlsx_files:\n",
        "  # read excel file\n",
        "  df = pd.read_excel(fp, usecols=['body'])\n",
        "\n",
        "  # count words and add to total\n",
        "  df['body'] = df['body'].apply(lambda x: len(str(x).split()) if pd.notnull(x) else 0)\n",
        "  total_word_count += df['body'].sum()\n",
        "\n",
        "# print total word count\n",
        "print(f\"Total number of words in body across all xlsx files: {total_word_count}\")\n",
        "\n",
        "# compare with number of words in quotes and non quotes\n",
        "print(\"Total number of words in quotes and non-quotes: \", str(quotes_wc + non_quotes_wc))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hU2rlEFh-2m-",
        "outputId": "3038f80d-5c06-4b12-f694-365a03f18c08"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Total number of words in body across all xlsx files: 32287506\n",
            "Total number of words in quotes and non-quotes:  28188065\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Initial attempt to count quotes and words\n"
      ],
      "metadata": {
        "id": "r0glfOZXcYFF"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Count number of quotes"
      ],
      "metadata": {
        "id": "-cbbg7RMvSem"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0tnUdCM_bnW_"
      },
      "outputs": [],
      "source": [
        "directory = 'C:/Maite/MOD/projects/Monika_Bednarek/Evaluation_quotes/Data/CBC_qt_output/Quotes_new'"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IrhgMClbbnXA"
      },
      "outputs": [],
      "source": [
        "excel_files = glob.glob(os.path.join(directory, '2023*.xlsx'))\n",
        "\n",
        "results = []\n",
        "\n",
        "for file in excel_files:\n",
        "    try:\n",
        "        # read the full sheet and the quotes column\n",
        "        df = pd.read_excel(file, sheet_name='full')\n",
        "\n",
        "        # count the number of rows\n",
        "        num_rows_quotes = len(df)\n",
        "\n",
        "        # Store the result in the list\n",
        "        results.append({\n",
        "            'file': os.path.basename(file),\n",
        "            'rows_in_full': num_rows_quotes,\n",
        "        })\n",
        "\n",
        "    except Exception as e:\n",
        "        # Handle any errors (e.g., sheet not found)\n",
        "        print(f\"Could not process {file}: {e}\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xkuNlv4ebnXA",
        "outputId": "407610fb-6de3-4528-f310-406b6d07b06d"
      },
      "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>file</th>\n",
              "      <th>rows_in_full</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2023_01_jan_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55161</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2023_03_mar_CBC_news_qt_new.xlsx</td>\n",
              "      <td>60157</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2023_04_apr_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51383</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2023_05_may_CBC_news_qt_new.xlsx</td>\n",
              "      <td>58306</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2023_07_jul_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51754</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2023_08_aug_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51011</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2023_09_sep_CBC_news_qt_new.xlsx</td>\n",
              "      <td>52230</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2023_10_oct_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55202</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2023_11_nov_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55528</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2023_12_dec_CBC_news_qt_new.xlsx</td>\n",
              "      <td>47561</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                               file  rows_in_full\n",
              "0  2023_01_jan_CBC_news_qt_new.xlsx         55161\n",
              "1  2023_03_mar_CBC_news_qt_new.xlsx         60157\n",
              "2  2023_04_apr_CBC_news_qt_new.xlsx         51383\n",
              "3  2023_05_may_CBC_news_qt_new.xlsx         58306\n",
              "4  2023_07_jul_CBC_news_qt_new.xlsx         51754\n",
              "5  2023_08_aug_CBC_news_qt_new.xlsx         51011\n",
              "6  2023_09_sep_CBC_news_qt_new.xlsx         52230\n",
              "7  2023_10_oct_CBC_news_qt_new.xlsx         55202\n",
              "8  2023_11_nov_CBC_news_qt_new.xlsx         55528\n",
              "9  2023_12_dec_CBC_news_qt_new.xlsx         47561"
            ]
          },
          "execution_count": 7,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "results_df = pd.DataFrame(results)\n",
        "\n",
        "results_df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xXGNMD3ybnXC",
        "outputId": "13958aba-292b-431e-8bab-185d22fd3459"
      },
      "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>file</th>\n",
              "      <th>rows_in_full</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2023_01_jan_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55161</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2023_03_mar_CBC_news_qt_new.xlsx</td>\n",
              "      <td>60157</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2023_04_apr_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51383</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2023_05_may_CBC_news_qt_new.xlsx</td>\n",
              "      <td>58306</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2023_07_jul_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51754</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2023_08_aug_CBC_news_qt_new.xlsx</td>\n",
              "      <td>51011</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2023_09_sep_CBC_news_qt_new.xlsx</td>\n",
              "      <td>52230</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2023_10_oct_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55202</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2023_11_nov_CBC_news_qt_new.xlsx</td>\n",
              "      <td>55528</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2023_12_dec_CBC_news_qt_new.xlsx</td>\n",
              "      <td>47561</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Total</td>\n",
              "      <td>538293</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                file  rows_in_full\n",
              "0   2023_01_jan_CBC_news_qt_new.xlsx         55161\n",
              "1   2023_03_mar_CBC_news_qt_new.xlsx         60157\n",
              "2   2023_04_apr_CBC_news_qt_new.xlsx         51383\n",
              "3   2023_05_may_CBC_news_qt_new.xlsx         58306\n",
              "4   2023_07_jul_CBC_news_qt_new.xlsx         51754\n",
              "5   2023_08_aug_CBC_news_qt_new.xlsx         51011\n",
              "6   2023_09_sep_CBC_news_qt_new.xlsx         52230\n",
              "7   2023_10_oct_CBC_news_qt_new.xlsx         55202\n",
              "8   2023_11_nov_CBC_news_qt_new.xlsx         55528\n",
              "9   2023_12_dec_CBC_news_qt_new.xlsx         47561\n",
              "10                             Total        538293"
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Calculate the total row by summing all numeric columns\n",
        "total_row = results_df[['rows_in_full']].sum()\n",
        "\n",
        "# Add 'Total' as a label for the 'Category' column in the total row\n",
        "total_row['file'] = 'Total'\n",
        "\n",
        "# Append the total row to the DataFrame\n",
        "results_df = results_df.append(total_row, ignore_index=True)\n",
        "\n",
        "# Display the updated DataFrame\n",
        "results_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Qak1LMfdbnXC"
      },
      "source": [
        "## Count words in quotes and non quotes\n",
        "\n",
        "Input dir is sentiment_output. We count the words in the respective tab/columns of each excel, i.e, for each month:\n",
        "\n",
        "* quotes tab\n",
        "* quote column\n",
        "* non-quotes tab\n",
        "* non-quoted text"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DFoAjR-SbnXD"
      },
      "outputs": [],
      "source": [
        "os.chdir(r'C:\\Maite\\MOD\\projects\\Monika_Bednarek\\Evaluation_quotes\\Data\\CBC_output_corpus')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rYv-cGAZbnXD"
      },
      "source": [
        "This one counts with the \\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rOSqIFZPbnXE",
        "outputId": "a041781d-f6a6-4140-abc5-f805940a844f"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Total number of words: 895688\n"
          ]
        }
      ],
      "source": [
        "with open('2023_02_feb_CBC_news_sentiment_quote_text.txt', 'r', encoding='utf-8') as file:\n",
        "    lines = file.readlines()\n",
        "\n",
        "df = pd.DataFrame(lines, columns=['text'])\n",
        "\n",
        "# count the number of words in each line\n",
        "df['word_count'] = df['text'].apply(lambda x: len(x.split()))\n",
        "\n",
        "total_words = df['word_count'].sum()\n",
        "\n",
        "print(f\"Total number of words: {total_words}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aCK5mOWzbnXF"
      },
      "source": [
        "This one without the \\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4K9SniyrbnXF",
        "outputId": "0970055e-0e5c-4e36-8237-7fb49d6af1db"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Total number of words: 895688\n"
          ]
        }
      ],
      "source": [
        "with open('2023_02_feb_CBC_news_sentiment_quote_text.txt', 'r', encoding='utf-8') as file:\n",
        "    lines = file.readlines()\n",
        "    # Remove the newline character and strip any extra whitespace from each line\n",
        "    lines = [line.strip().replace('\\n', '') for line in lines]\n",
        "\n",
        "df = pd.DataFrame(lines, columns=['text'])\n",
        "\n",
        "# count the number of words in each line\n",
        "df['word_count'] = df['text'].apply(lambda x: len(x.split()))\n",
        "\n",
        "total_words = df['word_count'].sum()\n",
        "\n",
        "print(f\"Total number of words: {total_words}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1MKP5tjObnXG"
      },
      "source": [
        "From this, I conclude that somehow pandas doesn't see the \\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "j_kzJhy1bnXG",
        "outputId": "7c7a4fcc-6c19-4e50-ba37-c29fe9e2771e"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "                                                   text  word_count\n",
            "0     Wednesday's order from the 6th U.S. Circuit Co...         182\n",
            "1     that thousands of people were travelling acros...         146\n",
            "2     13 state landmarks — including One World Trade...          94\n",
            "3     it's going to take a lot more than talk to put...         617\n",
            "4     We're in a situation where we have members say...          26\n",
            "...                                                 ...         ...\n",
            "4211  that while more government money is needed, th...         807\n",
            "4212  \"A dollar invested today, and making sure you ...         314\n",
            "4213  he had accepted the resignation of Bryce Phill...          78\n",
            "4214  she felt things were going well right up until...         543\n",
            "4215  the heaviest snow will be centred in the north...          43\n",
            "\n",
            "[4216 rows x 2 columns]\n"
          ]
        }
      ],
      "source": [
        "print(df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3WDkmlSqbnXG",
        "outputId": "2a9eec9f-9d85-4c7d-f967-9257d3841dc7"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "'13 state landmarks — including One World Trade Center, Empire State Plaza and Niagara Falls — would be illuminated Monday evening in red, black and green\"Amidst the recent rise in bigotry, division and hate across this country, it is more important than ever that we remember Dr. Martin Luther King Jr.\\'s teachings on justice, peace, equality and loveMarching bands, dance teams, local politicians and activists made their way along a route that \"traces the steps\" of King during visits to Miami,\"the sacred right to vote\"\"one of the country\\'s oldest and largest\"\"I am the dream,\"'"
            ]
          },
          "execution_count": 18,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df.iloc[2,0]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QYDyF7eIbnXH"
      },
      "source": [
        "So now I'll proceed to do this for the quotes vs. non-quotes in the dir"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VFpz4UkrbnXH"
      },
      "outputs": [],
      "source": [
        "directory = 'C:/Maite/MOD/projects/Monika_Bednarek/Evaluation_quotes/Data/CBC_output_corpus'"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0MzIgytdbnXH",
        "outputId": "f9dd7af5-476d-4c26-b957-00f23e288445"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "'C:\\\\Maite\\\\MOD\\\\projects\\\\Monika_Bednarek\\\\Evaluation_quotes\\\\Data\\\\CBC_output_corpus'"
            ]
          },
          "execution_count": 30,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "pwd"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mD1-yTXfbnXH",
        "outputId": "5bab78e7-23ff-47f1-90cd-7df535365858"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Total number of words in all 'quotes' related text files: 28188065\n"
          ]
        }
      ],
      "source": [
        "# Step 2: Get all text files in the directory that contain \"quote\" between underscore in their name\n",
        "quotes_files = [f for f in os.listdir(directory) if '_quote_' in f.lower() and f.endswith('.txt')]\n",
        "\n",
        "# Initialize a variable to store the total word count\n",
        "total_word_count = 0\n",
        "\n",
        "# Step 3: Process each file\n",
        "for file_name in quotes_files:\n",
        "    file_path = os.path.join(directory, file_name)\n",
        "\n",
        "    # Read the text file into a list of lines\n",
        "    with open(file_path, 'r', encoding='utf-8') as file:\n",
        "        lines = file.readlines()\n",
        "\n",
        "    # Convert the lines into a DataFrame\n",
        "    df = pd.DataFrame(lines, columns=['text'])\n",
        "\n",
        "    # Count the number of words in each line and sum them\n",
        "    df['word_count'] = df['text'].apply(lambda x: len(x.split()))\n",
        "    total_word_count += df['word_count'].sum()\n",
        "\n",
        "# Step 4: Print the total word count\n",
        "print(f\"Total number of words in all 'quotes' related text files: {total_word_count}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ffha4wnybnXI"
      },
      "source": [
        "This is actually the total number of everyting, as I cannot tell _quote_ from _non_quote"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "v3aaa4CBbnXI",
        "outputId": "59b193fa-a627-426e-9433-290132ae0a85"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Total number of words in all 'non quotes' related text files: 17314398\n"
          ]
        }
      ],
      "source": [
        "# Step 2: Get all text files in the directory that contain \"quote\" between underscore in their name\n",
        "quotes_files = [f for f in os.listdir(directory) if '_non_quote_' in f.lower() and f.endswith('.txt')]\n",
        "\n",
        "# Initialize a variable to store the total word count\n",
        "total_word_count = 0\n",
        "\n",
        "# Step 3: Process each file\n",
        "for file_name in quotes_files:\n",
        "    file_path = os.path.join(directory, file_name)\n",
        "\n",
        "    # Read the text file into a list of lines\n",
        "    with open(file_path, 'r', encoding='utf-8') as file:\n",
        "        lines = file.readlines()\n",
        "\n",
        "    # Convert the lines into a DataFrame\n",
        "    df = pd.DataFrame(lines, columns=['text'])\n",
        "\n",
        "    # Count the number of words in each line and sum them\n",
        "    df['word_count'] = df['text'].apply(lambda x: len(x.split()))\n",
        "    total_word_count += df['word_count'].sum()\n",
        "\n",
        "# Step 4: Print the total word count\n",
        "print(f\"Total number of words in all 'non quotes' related text files: {total_word_count}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qT-xuW5PbnXI"
      },
      "source": [
        "## Now, I'm counting the original corpus words, because the quotes vs. non-quotes excludes verbs and speakers"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VGjZVqbhbnXJ"
      },
      "outputs": [],
      "source": [
        "directory = 'C:/Maite/MOD/projects/Monika_Bednarek/Evaluation_quotes/Data/CBC_input_correct_encoding'"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "LdgOFpY4bnXJ",
        "outputId": "819fbeee-b653-4557-f03d-618f53e01393"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Total number of words in column 'body' across all xlsx files: 26772464\n"
          ]
        }
      ],
      "source": [
        "xlsx_files = [f for f in os.listdir(directory) if f.endswith('.xlsx')]\n",
        "\n",
        "column_name = 'body'\n",
        "\n",
        "total_word_count = 0\n",
        "\n",
        "for file_name in xlsx_files:\n",
        "    file_path = os.path.join(directory, file_name)\n",
        "\n",
        "    # Read the Excel file into a pandas ExcelFile object to handle multiple sheets\n",
        "    xlsx_file = pd.ExcelFile(file_path)\n",
        "\n",
        "    # Step 4: Process each sheet in the Excel file\n",
        "    for sheet_name in xlsx_file.sheet_names:\n",
        "        df = xlsx_file.parse(sheet_name)\n",
        "\n",
        "        # Step 5: Check if the specified column exists in the sheet\n",
        "        if column_name in df.columns:\n",
        "            # Step 6: Count the number of words in the specified column (excluding NaN values)\n",
        "            df[column_name] = df[column_name].apply(lambda x: len(str(x).split()) if pd.notnull(x) else 0)\n",
        "\n",
        "            # Add up the word counts in this column\n",
        "            total_word_count += df[column_name].sum()\n",
        "\n",
        "# Step 7: Print the total word count\n",
        "print(f\"Total number of words in column '{column_name}' across all xlsx files: {total_word_count}\")\n"
      ]
    }
  ],
  "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.7.3"
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}