BeyondCtrlF_SystematicQueriesWithPandas_Liliana.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/lilimelgar/systematic_queries_with_pandas_and_regex/blob/main/BeyondCtrlF_SystematicQueriesWithPandas_Liliana.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "toc": true,
        "id": "q0V-dzP6y0n6"
      },
      "source": [
        "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
        "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Querying-CERA-letters-with-regular-expressions-in-Python\" data-toc-modified-id=\"Querying-CERA-letters-with-regular-expressions-in-Python-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Querying CERA letters with regular expressions in Python</a></span></li><li><span><a href=\"#Loading-file-with-letters'-metadata-+-content\" data-toc-modified-id=\"Loading-file-with-letters'-metadata-+-content-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Loading file with letters' metadata + content</a></span></li><li><span><a href=\"#Querying-the-entire-set-with-Regex\" data-toc-modified-id=\"Querying-the-entire-set-with-Regex-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Querying the entire set with Regex</a></span></li></ul></div>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kC2wm46By0n-"
      },
      "source": [
        "# Querying CERA letters with regular expressions in Python\n",
        "\n",
        "Notebook prepared by Liliana Melgar for SKILLNET project\n",
        "\n",
        "Last update: March 31, 2021 (includes a for loop to create a dataframe that includes all the queries (patterns) aggregated into one dataframe\n",
        "Created: March 16, 2021\n",
        "\n",
        "Sources:\n",
        "- https://jakevdp.github.io/PythonDataScienceHandbook/03.10-working-with-strings.html\n",
        "\n",
        "This notebook shows a basic way to use Python regular expressions for querying data, in this case different volumes of the CERA letters. The data from the editions was converted to a table which includes the basic page metadata and the content of the letters downloaded from the CERA website.\n",
        "\n",
        "First batch:\n",
        "- http://mateo.uni-mannheim.de/cera/autoren/schurzfleisch_cera.html\n",
        "\n",
        "Second batch:\n",
        "- http://mateo.uni-mannheim.de/cera/autoren/hessus_cera.html\n",
        "- http://mateo.uni-mannheim.de/cera/autoren/reinesius_cera.html\n",
        "- http://mateo.uni-mannheim.de/cera/autoren/richter_cera.html"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nmuBEUDgy0n_"
      },
      "outputs": [],
      "source": [
        "import matplotlib\n",
        "import pandas as pd\n",
        "import re\n",
        "from IPython.display import clear_output\n",
        "import numpy as np\n",
        "\n",
        "# to export to Excel\n",
        "import openpyxl\n",
        "\n",
        "# from IPython.display import display, HTML\n",
        "# # display(HTML(\"<style>.container { width:95% !important; }</style>\"))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bAvW-lK_y0oA"
      },
      "source": [
        "# Loading file with letters' metadata + content"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# This is the code to indicate to this notebook that the file will be loaded from Google Drive\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DSfvppDYy-Nm",
        "outputId": "a31902c3-d537-4488-dc5b-f6b189d52092"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Kllic89sy0oB"
      },
      "outputs": [],
      "source": [
        "# Load the file as a pandas (pd) data frame, in this case a .csv file which contains tabular data\n",
        "\n",
        "# second batch = Hessus, Helius; Reinesius, Thomas; Richter, Georg\n",
        "# file = pd.read_csv(\"CERA-Batch2-hessus-reinesius-richter-ALL_v20210330_DownloadedFromOR-Clean.csv\", sep = \"ℑ\", engine='python')\n",
        "# remember to upload file to \"sample_data\"\n",
        "\n",
        "path = \"/content/sample_data/CERA-Batch2-hessus-reinesius-richter-ALL_v20210330_DownloadedFromOR-Clean.csv\"\n",
        "file = pd.read_csv(path, sep = \"ℑ\", engine='python')\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6RwE73vyy0oC",
        "outputId": "61fae2b6-78c9-4b6c-d2b6-0a949949f48a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 3156 entries, 0 to 3155\n",
            "Data columns (total 5 columns):\n",
            " #   Column   Non-Null Count  Dtype \n",
            "---  ------   --------------  ----- \n",
            " 0   pageId   3156 non-null   object\n",
            " 1   edition  3156 non-null   object\n",
            " 2   page     3156 non-null   object\n",
            " 3   link     3156 non-null   object\n",
            " 4   text     3156 non-null   object\n",
            "dtypes: object(5)\n",
            "memory usage: 123.4+ KB\n"
          ]
        }
      ],
      "source": [
        "# Get familiar with the columns and data types of the data\n",
        "\n",
        "file.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-rKcZet3y0oD",
        "outputId": "f132e17b-de3c-4824-d744-5e86cad6b73e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 3156 entries, 0 to 3155\n",
            "Data columns (total 5 columns):\n",
            " #   Column   Non-Null Count  Dtype \n",
            "---  ------   --------------  ----- \n",
            " 0   pageId   3156 non-null   object\n",
            " 1   edition  3156 non-null   object\n",
            " 2   page     3156 non-null   object\n",
            " 3   link     3156 non-null   object\n",
            " 4   text     3156 non-null   string\n",
            "dtypes: object(4), string(1)\n",
            "memory usage: 123.4+ KB\n"
          ]
        }
      ],
      "source": [
        "# Change the type of data of the \"Content\" column to string\n",
        "\n",
        "file['text'] = file['text'].astype('string')\n",
        "\n",
        "file.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gIyMwZpey0oE"
      },
      "outputs": [],
      "source": [
        "# store the copy to work on from now\n",
        "\n",
        "letters = file.copy()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UJaF_IZ_y0oE",
        "outputId": "93c856b1-ea5b-4372-f85f-1e8656657331",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 3156 entries, 0 to 3155\n",
            "Data columns (total 5 columns):\n",
            " #   Column   Non-Null Count  Dtype \n",
            "---  ------   --------------  ----- \n",
            " 0   pageId   3156 non-null   object\n",
            " 1   edition  3156 non-null   object\n",
            " 2   page     3156 non-null   object\n",
            " 3   link     3156 non-null   object\n",
            " 4   text     3156 non-null   string\n",
            "dtypes: object(4), string(1)\n",
            "memory usage: 123.4+ KB\n"
          ]
        }
      ],
      "source": [
        "letters.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "L9kAVPkcy0oF",
        "outputId": "ba909a9f-74e3-4683-d828-09d192993dd1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 337
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-c7b8c13b-7f48-4684-b957-dd28898880c7\">\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>pageId</th>\n",
              "      <th>edition</th>\n",
              "      <th>page</th>\n",
              "      <th>link</th>\n",
              "      <th>text</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>hessus_01_s001</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s001</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>NARRATIO DE H. EOBANO HESSO, COMPREHENdens men...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>hessus_01_s002</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s002</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>SIGNVM EOBANICVM CVM ELOGIO IPSIVS.\n",
              "/[gap: ill...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>hessus_01_s003</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s003</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>DE H. EOBANO HESSO NARRATIO IOACHIMI CAMErarij...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>hessus_01_s004</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s004</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>in acerrima aegritudine, neque in tanta hominu...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>hessus_01_s005</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s005</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>secundum Poetam, floribus austri sane procello...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c7b8c13b-7f48-4684-b957-dd28898880c7')\"\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-c7b8c13b-7f48-4684-b957-dd28898880c7 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-c7b8c13b-7f48-4684-b957-dd28898880c7');\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": [
              "           pageId  ...                                               text\n",
              "0  hessus_01_s001  ...  NARRATIO DE H. EOBANO HESSO, COMPREHENdens men...\n",
              "1  hessus_01_s002  ...  SIGNVM EOBANICVM CVM ELOGIO IPSIVS.\n",
              "/[gap: ill...\n",
              "2  hessus_01_s003  ...  DE H. EOBANO HESSO NARRATIO IOACHIMI CAMErarij...\n",
              "3  hessus_01_s004  ...  in acerrima aegritudine, neque in tanta hominu...\n",
              "4  hessus_01_s005  ...  secundum Poetam, floribus austri sane procello...\n",
              "\n",
              "[5 rows x 5 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "letters.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CvuHaj33y0oF"
      },
      "source": [
        "# Querying the entire set with Regex"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Zp3IOzKMy0oG"
      },
      "outputs": [],
      "source": [
        "# findall(): Return a list containing every occurrence, but in this case using the \"text\" column of the entire \n",
        "# dataframe \n",
        "# (notice the difference in the syntax between the way to use Regex in a dataframe (this cell) and in text only \n",
        "# (no dataframe: cell above))\n",
        "\n",
        "# pattern to query for all variations of \"republica literaria\" listed here: https://surfdrive.surf.nl/files/index.php/s/PZ0H5KAILzbrvpW\n",
        "pattern1a = re.compile('re.*p.* lit+erar.*|lit+erar.* re.*p.*', re.IGNORECASE) #with \"p\", case insensitive)\n",
        "pattern1b = re.compile('re.* lit+erar.*|lit+erar.* re.*', re.IGNORECASE) #without \"p\", case insensitive\n",
        "\n",
        "# pattern to query for all variations of republica academica (with or without words in between, e.g., republicae gerendis academica)\n",
        "pattern2 = re.compile('re\\w*?p.*\\s\\w*?\\sacad.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for all variations of commercio literario\n",
        "pattern3 = re.compile('commerc.*lit+erar.*|lit+erar.*commerc.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of academia\n",
        "pattern4 = re.compile('\\s?academi.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of communium amicorum\n",
        "pattern5 = re.compile('commun.*\\samic.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of communium studiorum\n",
        "pattern6 = re.compile('commun.*\\sstu.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of forum lit('t)erarium\n",
        "pattern7 = re.compile('for.*\\slit+erar.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of lit('t)erarum officium\n",
        "pattern8 = re.compile('lit+erar.*\\soffic.*|offic.*\\slit+erar.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of literarum commodo\n",
        "pattern9 = re.compile('lit+erar.*\\scommo.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of \\literarum humaniorum\\\n",
        "pattern10 = re.compile('lit+erar.*\\shuman.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of \\nationis lit('t)erariorum\\\n",
        "pattern11 = re.compile('natio.*\\slit+erar.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of \\Orbi lit('t)erato\\\n",
        "pattern12 = re.compile('lit+erato\\sorbi|orbi\\slit+erato', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of \\universum studiosorum\\\n",
        "pattern13 = re.compile('univers.*\\sstudi.*', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of \\republica\\ alone\n",
        "pattern14 = re.compile(r'\\bre\\w?\\s?public.*\\b', re.IGNORECASE)\n",
        "\n",
        "# pattern to query for variations of \\literaria\\ alone\n",
        "pattern15 = re.compile(r'\\blit+era.*\\b', re.IGNORECASE)\n",
        "\n",
        "\n",
        "#other interesting queries:\n",
        "# stud.*nostra\n",
        "# academia nostra\\n\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aB7biRDiy0oG"
      },
      "outputs": [],
      "source": [
        "# create a dictionary with patterns\n",
        "\n",
        "patternName = ['pat01a', 'pat01b', 'pat02', 'pat03', 'pat04', 'pat05', 'pat06', 'pat07', 'pat08', 'pat09', 'pat10', 'pat11', 'pat12', 'pat13', 'pat14', 'pat15']\n",
        "\n",
        "pattern = [pattern1a, pattern1b, pattern2, pattern3, pattern4, pattern5, pattern6, pattern7, pattern8, pattern9, pattern10, pattern11, pattern12, pattern13, pattern14, pattern15]\n",
        "\n",
        "patterns_dictionary = dict(zip(patternName, pattern))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pInbtP8-y0oH",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "634803b4-4139-41b1-c4ed-870970132cf4"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'pat01a': re.compile(r're.*p.* lit+erar.*|lit+erar.* re.*p.*',\n",
              " re.IGNORECASE|re.UNICODE),\n",
              " 'pat01b': re.compile(r're.* lit+erar.*|lit+erar.* re.*',\n",
              " re.IGNORECASE|re.UNICODE),\n",
              " 'pat02': re.compile(r're\\w*?p.*\\s\\w*?\\sacad.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat03': re.compile(r'commerc.*lit+erar.*|lit+erar.*commerc.*',\n",
              " re.IGNORECASE|re.UNICODE),\n",
              " 'pat04': re.compile(r'\\s?academi.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat05': re.compile(r'commun.*\\samic.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat06': re.compile(r'commun.*\\sstu.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat07': re.compile(r'for.*\\slit+erar.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat08': re.compile(r'lit+erar.*\\soffic.*|offic.*\\slit+erar.*',\n",
              " re.IGNORECASE|re.UNICODE),\n",
              " 'pat09': re.compile(r'lit+erar.*\\scommo.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat10': re.compile(r'lit+erar.*\\shuman.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat11': re.compile(r'natio.*\\slit+erar.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat12': re.compile(r'lit+erato\\sorbi|orbi\\slit+erato',\n",
              " re.IGNORECASE|re.UNICODE),\n",
              " 'pat13': re.compile(r'univers.*\\sstudi.*', re.IGNORECASE|re.UNICODE),\n",
              " 'pat14': re.compile(r'\\bre\\w?\\s?public.*\\b', re.IGNORECASE|re.UNICODE),\n",
              " 'pat15': re.compile(r'\\blit+era.*\\b', re.IGNORECASE|re.UNICODE)}"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ],
      "source": [
        "patterns_dictionary"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "t-b9tBLPy0oH"
      },
      "outputs": [],
      "source": [
        "# # get the list of keys and values from the dictionary (to use it later in creating the column with the pattern name)\n",
        "\n",
        "key_list = list(patterns_dictionary.keys())\n",
        "value_list = list(patterns_dictionary.values())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HJpz6ojVy0oH"
      },
      "outputs": [],
      "source": [
        "# iterating over the dictionary of patterns \n",
        "# and using each value for the query\n",
        "\n",
        "for pattern in patterns_dictionary.values():\n",
        "    query = letters.text.str.findall(pattern)\n",
        "    # create a copy of original letters dataframe\n",
        "    queryOutput = letters.copy()\n",
        "    # add a column with matching terms which contains the output of the query\n",
        "    queryOutput['matchingTerms'] = query\n",
        "    # apply function to convert the list of matching terms to strings separated by '|'\n",
        "    queryOutput['matchingTerms'] = queryOutput.apply(lambda x: ('|'.join([str(s) for s in x['matchingTerms']])), axis = 1)\n",
        "    # replace empty space left in the cell with nan\n",
        "    queryOutput['matchingTerms'] = queryOutput['matchingTerms'].str.strip().replace('',np.nan)\n",
        "    # obtain the name of the pattern (key) from the dictionary using the value (pattern)\n",
        "    queryOutput['patternName'] = (key_list[value_list.index(pattern)])\n",
        "    # make a copy of the dataframe\n",
        "    queryResults = queryOutput.copy()\n",
        "    # create a dataframe with the query results only (drop empty rows)\n",
        "    queryResultsPerPage = queryResults.dropna(how='any', subset=['matchingTerms'])\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6poY1tGHy0oI",
        "outputId": "d9ecbac5-6df9-476c-81c2-4eb1110fc04a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "Int64Index: 1053 entries, 0 to 3153\n",
            "Data columns (total 7 columns):\n",
            " #   Column         Non-Null Count  Dtype \n",
            "---  ------         --------------  ----- \n",
            " 0   pageId         1053 non-null   object\n",
            " 1   edition        1053 non-null   object\n",
            " 2   page           1053 non-null   object\n",
            " 3   link           1053 non-null   object\n",
            " 4   text           1053 non-null   string\n",
            " 5   matchingTerms  1053 non-null   object\n",
            " 6   patternName    1053 non-null   object\n",
            "dtypes: object(6), string(1)\n",
            "memory usage: 65.8+ KB\n"
          ]
        }
      ],
      "source": [
        "queryResultsPerPage.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0ZVnzusky0oI",
        "outputId": "4b8172f1-ff84-445d-caa3-432af0e7d33f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 371
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-22c81e28-fd72-4770-8a80-7e9186471a0c\">\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>pageId</th>\n",
              "      <th>edition</th>\n",
              "      <th>page</th>\n",
              "      <th>link</th>\n",
              "      <th>text</th>\n",
              "      <th>matchingTerms</th>\n",
              "      <th>patternName</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>hessus_01_s001</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s001</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>NARRATIO DE H. EOBANO HESSO, COMPREHENdens men...</td>\n",
              "      <td>literatae: Cum quibusdam Camerarij et</td>\n",
              "      <td>pat15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>hessus_01_s005</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s005</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>secundum Poetam, floribus austri sane procello...</td>\n",
              "      <td>literas, sine quibus persuasum</td>\n",
              "      <td>pat15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>hessus_01_s008</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s008</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>incorrecta et mendosa diuulgata sunt, et te no...</td>\n",
              "      <td>literas solerct</td>\n",
              "      <td>pat15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>hessus_01_s011</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s011</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>abhorret a uero, in pago natum esse, cui est n...</td>\n",
              "      <td>litterarum. Verisimile est, quod|litterarum st...</td>\n",
              "      <td>pat15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>hessus_01_s012</td>\n",
              "      <td>Hessus_01_Camerarius-Narratio-EobanoHesso</td>\n",
              "      <td>image: s012</td>\n",
              "      <td>http://mateo.uni-mannheim.de/cera/camerarius1/...</td>\n",
              "      <td>studeret, ut pueri inprimis Grammaticas praece...</td>\n",
              "      <td>literarum tum celcbris</td>\n",
              "      <td>pat15</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-22c81e28-fd72-4770-8a80-7e9186471a0c')\"\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-22c81e28-fd72-4770-8a80-7e9186471a0c 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-22c81e28-fd72-4770-8a80-7e9186471a0c');\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": [
              "            pageId  ... patternName\n",
              "0   hessus_01_s001  ...       pat15\n",
              "4   hessus_01_s005  ...       pat15\n",
              "7   hessus_01_s008  ...       pat15\n",
              "10  hessus_01_s011  ...       pat15\n",
              "11  hessus_01_s012  ...       pat15\n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ],
      "source": [
        "queryResultsPerPage.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "B85tJgb-y0oI"
      },
      "outputs": [],
      "source": [
        "# store output file\n",
        "# queryResultsPerPage.to_excel('QueryOutput_batch2_Hessus-Reinesius-Richter.xlsx', sheet_name='queryResultsBatch2')\n",
        "queryResultsPerPage.to_excel(\"/content/sample_data/queryResultsBatch2.xlsx\", sheet_name=\"queryResultsBatch2\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rK6XwFniy0oI"
      },
      "outputs": [],
      "source": []
    }
  ],
  "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.10"
    },
    "toc": {
      "base_numbering": 1,
      "nav_menu": {},
      "number_sections": true,
      "sideBar": true,
      "skip_h1_title": false,
      "title_cell": "Table of Contents",
      "title_sidebar": "Contents",
      "toc_cell": true,
      "toc_position": {
        "height": "calc(100% - 180px)",
        "left": "10px",
        "top": "150px",
        "width": "165px"
      },
      "toc_section_display": true,
      "toc_window_display": true
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}