{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c1b25072",
   "metadata": {},
   "source": [
    "# Thompson Motif Index\n",
    "\n",
    "Katja Mellmann: Thompson's Motif-Index as CSV File (version 1.2). Open Science Framework (OSF), 2020. DOI: 10.17605/OSF.IO/XEB67 (https://osf.io/xeb67/), also available via https://github.com/KatjaMellmann/TMI_as_CSV/, provides a handy lookup reference for the Thompson Motif index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ba9b07a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>[sorting field]</th>\n",
       "      <th>1st ed.</th>\n",
       "      <th>chapter</th>\n",
       "      <th>division1</th>\n",
       "      <th>division2</th>\n",
       "      <th>division3</th>\n",
       "      <th>section (\"tens\")</th>\n",
       "      <th>MOTIF</th>\n",
       "      <th>bibliographies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>A0000</td>\n",
       "      <td>A0</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>For a general bibliography of creation myths, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>A0001</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1. Identity of creator.</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1.1</td>\n",
       "      <td>A0001.1</td>\n",
       "      <td>A1</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.1. Sun-god as creator.</td>\n",
       "      <td>Egyptian: Müller 69; Persian: Carnoy 260.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A1.2</td>\n",
       "      <td>A0001.2</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.2. Grandfather as creator.</td>\n",
       "      <td>S. Am. Indian (Paressi): Métraux BBAE CXLIII (...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1.3</td>\n",
       "      <td>A0001.3</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.3. Stone-woman as creator.</td>\n",
       "      <td>Paressi: Métraux BBAE CXLIII (3) 359.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code [sorting field] 1st ed.                  chapter         division1  \\\n",
       "0    A0           A0000      A0  A. Mythological motifs.  A0–A99. Creator.   \n",
       "1    A1           A0001          A. Mythological motifs.  A0–A99. Creator.   \n",
       "2  A1.1         A0001.1      A1  A. Mythological motifs.  A0–A99. Creator.   \n",
       "3  A1.2         A0001.2          A. Mythological motifs.  A0–A99. Creator.   \n",
       "4  A1.3         A0001.3          A. Mythological motifs.  A0–A99. Creator.   \n",
       "\n",
       "  division2 division3 section (\"tens\")                          MOTIF  \\\n",
       "0                         A0. Creator.                   A0. Creator.   \n",
       "1                         A0. Creator.       A1. Identity of creator.   \n",
       "2                         A0. Creator.      A1.1. Sun-god as creator.   \n",
       "3                         A0. Creator.  A1.2. Grandfather as creator.   \n",
       "4                         A0. Creator.  A1.3. Stone-woman as creator.   \n",
       "\n",
       "                                      bibliographies  \n",
       "0  For a general bibliography of creation myths, ...  \n",
       "1                                                     \n",
       "2          Egyptian: Müller 69; Persian: Carnoy 260.  \n",
       "3  S. Am. Indian (Paressi): Métraux BBAE CXLIII (...  \n",
       "4              Paressi: Métraux BBAE CXLIII (3) 359.  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "thompson_motif_df = pd.read_csv(\"https://github.com/KatjaMellmann/TMI_as_CSV/blob/main/tmi.csv?raw=true\")\n",
    "thompson_motif_df.fillna(\"\", inplace=True)\n",
    "\n",
    "thompson_motif_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65251763",
   "metadata": {},
   "source": [
    "Tidy up some of the column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "842f4a15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>sortfield</th>\n",
       "      <th>1st ed.</th>\n",
       "      <th>chapter</th>\n",
       "      <th>division1</th>\n",
       "      <th>division2</th>\n",
       "      <th>division3</th>\n",
       "      <th>section10s</th>\n",
       "      <th>MOTIF</th>\n",
       "      <th>bibliographies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>A0000</td>\n",
       "      <td>A0</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>For a general bibliography of creation myths, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>A0001</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1. Identity of creator.</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1.1</td>\n",
       "      <td>A0001.1</td>\n",
       "      <td>A1</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.1. Sun-god as creator.</td>\n",
       "      <td>Egyptian: Müller 69; Persian: Carnoy 260.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A1.2</td>\n",
       "      <td>A0001.2</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.2. Grandfather as creator.</td>\n",
       "      <td>S. Am. Indian (Paressi): Métraux BBAE CXLIII (...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1.3</td>\n",
       "      <td>A0001.3</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.3. Stone-woman as creator.</td>\n",
       "      <td>Paressi: Métraux BBAE CXLIII (3) 359.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46297</th>\n",
       "      <td>Z352</td>\n",
       "      <td>Z0352</td>\n",
       "      <td>Z352</td>\n",
       "      <td>Z. Miscellaneous groups of motifs.</td>\n",
       "      <td>Z300–Z399. Unique exceptions</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Z350. Other unique exceptions.</td>\n",
       "      <td>Z352. All trees except aspen refuse to make Ch...</td>\n",
       "      <td>Dh II 209; North Carolina: Brown Collection I ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46298</th>\n",
       "      <td>Z355</td>\n",
       "      <td>Z0355</td>\n",
       "      <td>Z355</td>\n",
       "      <td>Z. Miscellaneous groups of motifs.</td>\n",
       "      <td>Z300–Z399. Unique exceptions</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Z350. Other unique exceptions.</td>\n",
       "      <td>Z355. All snakes but one placated by music. In...</td>\n",
       "      <td>Hdwb. d. Märchens I 437a.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46299</th>\n",
       "      <td>Z356</td>\n",
       "      <td>Z0356</td>\n",
       "      <td></td>\n",
       "      <td>Z. Miscellaneous groups of motifs.</td>\n",
       "      <td>Z300–Z399. Unique exceptions</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Z350. Other unique exceptions.</td>\n",
       "      <td>Z356. Unique survivor. Only one person left fr...</td>\n",
       "      <td>India: *Thompson-Balys; Hawaii: Beckwith Myth ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46300</th>\n",
       "      <td>Z356.1</td>\n",
       "      <td>Z0356.1</td>\n",
       "      <td></td>\n",
       "      <td>Z. Miscellaneous groups of motifs.</td>\n",
       "      <td>Z300–Z399. Unique exceptions</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Z350. Other unique exceptions.</td>\n",
       "      <td>Z56.1 Unique survivor from detruction of animals</td>\n",
       "      <td>India: *Thompson-Balys.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46301</th>\n",
       "      <td>Z357</td>\n",
       "      <td>Z0357</td>\n",
       "      <td></td>\n",
       "      <td>Z. Miscellaneous groups of motifs.</td>\n",
       "      <td>Z300–Z399. Unique exceptions</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Z350. Other unique exceptions.</td>\n",
       "      <td>Z357. Unique exceptions from curse.</td>\n",
       "      <td>Irish myth: *Cross.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>46302 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         code sortfield 1st ed.                             chapter  \\\n",
       "0          A0     A0000      A0             A. Mythological motifs.   \n",
       "1          A1     A0001                     A. Mythological motifs.   \n",
       "2        A1.1   A0001.1      A1             A. Mythological motifs.   \n",
       "3        A1.2   A0001.2                     A. Mythological motifs.   \n",
       "4        A1.3   A0001.3                     A. Mythological motifs.   \n",
       "...       ...       ...     ...                                 ...   \n",
       "46297    Z352     Z0352    Z352  Z. Miscellaneous groups of motifs.   \n",
       "46298    Z355     Z0355    Z355  Z. Miscellaneous groups of motifs.   \n",
       "46299    Z356     Z0356          Z. Miscellaneous groups of motifs.   \n",
       "46300  Z356.1   Z0356.1          Z. Miscellaneous groups of motifs.   \n",
       "46301    Z357     Z0357          Z. Miscellaneous groups of motifs.   \n",
       "\n",
       "                          division1 division2 division3  \\\n",
       "0                  A0–A99. Creator.                       \n",
       "1                  A0–A99. Creator.                       \n",
       "2                  A0–A99. Creator.                       \n",
       "3                  A0–A99. Creator.                       \n",
       "4                  A0–A99. Creator.                       \n",
       "...                             ...       ...       ...   \n",
       "46297  Z300–Z399. Unique exceptions                       \n",
       "46298  Z300–Z399. Unique exceptions                       \n",
       "46299  Z300–Z399. Unique exceptions                       \n",
       "46300  Z300–Z399. Unique exceptions                       \n",
       "46301  Z300–Z399. Unique exceptions                       \n",
       "\n",
       "                           section10s  \\\n",
       "0                        A0. Creator.   \n",
       "1                        A0. Creator.   \n",
       "2                        A0. Creator.   \n",
       "3                        A0. Creator.   \n",
       "4                        A0. Creator.   \n",
       "...                               ...   \n",
       "46297  Z350. Other unique exceptions.   \n",
       "46298  Z350. Other unique exceptions.   \n",
       "46299  Z350. Other unique exceptions.   \n",
       "46300  Z350. Other unique exceptions.   \n",
       "46301  Z350. Other unique exceptions.   \n",
       "\n",
       "                                                   MOTIF  \\\n",
       "0                                           A0. Creator.   \n",
       "1                               A1. Identity of creator.   \n",
       "2                              A1.1. Sun-god as creator.   \n",
       "3                          A1.2. Grandfather as creator.   \n",
       "4                          A1.3. Stone-woman as creator.   \n",
       "...                                                  ...   \n",
       "46297  Z352. All trees except aspen refuse to make Ch...   \n",
       "46298  Z355. All snakes but one placated by music. In...   \n",
       "46299  Z356. Unique survivor. Only one person left fr...   \n",
       "46300   Z56.1 Unique survivor from detruction of animals   \n",
       "46301                Z357. Unique exceptions from curse.   \n",
       "\n",
       "                                          bibliographies  \n",
       "0      For a general bibliography of creation myths, ...  \n",
       "1                                                         \n",
       "2              Egyptian: Müller 69; Persian: Carnoy 260.  \n",
       "3      S. Am. Indian (Paressi): Métraux BBAE CXLIII (...  \n",
       "4                  Paressi: Métraux BBAE CXLIII (3) 359.  \n",
       "...                                                  ...  \n",
       "46297  Dh II 209; North Carolina: Brown Collection I ...  \n",
       "46298                          Hdwb. d. Märchens I 437a.  \n",
       "46299  India: *Thompson-Balys; Hawaii: Beckwith Myth ...  \n",
       "46300                            India: *Thompson-Balys.  \n",
       "46301                                Irish myth: *Cross.  \n",
       "\n",
       "[46302 rows x 10 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "thompson_motif_df.rename(columns={\"[sorting field]\": \"sortfield\",\n",
    "                                  'section (\"tens\")': \"section10s\"}, inplace=True)\n",
    "thompson_motif_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce5d44ab",
   "metadata": {},
   "source": [
    "We can also process it further to extract out the codes (we really should combine the following so we only iterate through the whole dataset once):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "01558bd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>sortfield</th>\n",
       "      <th>1st ed.</th>\n",
       "      <th>chapter</th>\n",
       "      <th>division1</th>\n",
       "      <th>division2</th>\n",
       "      <th>division3</th>\n",
       "      <th>section10s</th>\n",
       "      <th>MOTIF</th>\n",
       "      <th>bibliographies</th>\n",
       "      <th>motif_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>A0000</td>\n",
       "      <td>A0</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>For a general bibliography of creation myths, ...</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>A0001</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1. Identity of creator.</td>\n",
       "      <td></td>\n",
       "      <td>Identity of creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1.1</td>\n",
       "      <td>A0001.1</td>\n",
       "      <td>A1</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.1. Sun-god as creator.</td>\n",
       "      <td>Egyptian: Müller 69; Persian: Carnoy 260.</td>\n",
       "      <td>Sun-god as creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A1.2</td>\n",
       "      <td>A0001.2</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.2. Grandfather as creator.</td>\n",
       "      <td>S. Am. Indian (Paressi): Métraux BBAE CXLIII (...</td>\n",
       "      <td>Grandfather as creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1.3</td>\n",
       "      <td>A0001.3</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.3. Stone-woman as creator.</td>\n",
       "      <td>Paressi: Métraux BBAE CXLIII (3) 359.</td>\n",
       "      <td>Stone-woman as creator</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code sortfield 1st ed.                  chapter         division1  \\\n",
       "0    A0     A0000      A0  A. Mythological motifs.  A0–A99. Creator.   \n",
       "1    A1     A0001          A. Mythological motifs.  A0–A99. Creator.   \n",
       "2  A1.1   A0001.1      A1  A. Mythological motifs.  A0–A99. Creator.   \n",
       "3  A1.2   A0001.2          A. Mythological motifs.  A0–A99. Creator.   \n",
       "4  A1.3   A0001.3          A. Mythological motifs.  A0–A99. Creator.   \n",
       "\n",
       "  division2 division3    section10s                          MOTIF  \\\n",
       "0                      A0. Creator.                   A0. Creator.   \n",
       "1                      A0. Creator.       A1. Identity of creator.   \n",
       "2                      A0. Creator.      A1.1. Sun-god as creator.   \n",
       "3                      A0. Creator.  A1.2. Grandfather as creator.   \n",
       "4                      A0. Creator.  A1.3. Stone-woman as creator.   \n",
       "\n",
       "                                      bibliographies             motif_label  \n",
       "0  For a general bibliography of creation myths, ...                 Creator  \n",
       "1                                                        Identity of creator  \n",
       "2          Egyptian: Müller 69; Persian: Carnoy 260.      Sun-god as creator  \n",
       "3  S. Am. Indian (Paressi): Métraux BBAE CXLIII (...  Grandfather as creator  \n",
       "4              Paressi: Métraux BBAE CXLIII (3) 359.  Stone-woman as creator  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def motif_splitter(cell):\n",
    "    \"\"\"Split out motif label.\"\"\"\n",
    "    if cell:\n",
    "        parts = cell.split()\n",
    "        return pd.Series({\"motif_label\": \" \".join(parts[1:]).strip(\".\")})\n",
    "    return pd.Series({\"motif_label\":''})\n",
    "\n",
    "thompson_motif_df[[\"motif_label\"]] = thompson_motif_df[\"MOTIF\"].apply(motif_splitter)\n",
    "thompson_motif_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cbd3bee4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>sortfield</th>\n",
       "      <th>1st ed.</th>\n",
       "      <th>chapter</th>\n",
       "      <th>division1</th>\n",
       "      <th>division2</th>\n",
       "      <th>division3</th>\n",
       "      <th>section10s</th>\n",
       "      <th>MOTIF</th>\n",
       "      <th>bibliographies</th>\n",
       "      <th>motif_label</th>\n",
       "      <th>chapter_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>A0000</td>\n",
       "      <td>A0</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>For a general bibliography of creation myths, ...</td>\n",
       "      <td>Creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>A0001</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1. Identity of creator.</td>\n",
       "      <td></td>\n",
       "      <td>Identity of creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1.1</td>\n",
       "      <td>A0001.1</td>\n",
       "      <td>A1</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.1. Sun-god as creator.</td>\n",
       "      <td>Egyptian: Müller 69; Persian: Carnoy 260.</td>\n",
       "      <td>Sun-god as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A1.2</td>\n",
       "      <td>A0001.2</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.2. Grandfather as creator.</td>\n",
       "      <td>S. Am. Indian (Paressi): Métraux BBAE CXLIII (...</td>\n",
       "      <td>Grandfather as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1.3</td>\n",
       "      <td>A0001.3</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.3. Stone-woman as creator.</td>\n",
       "      <td>Paressi: Métraux BBAE CXLIII (3) 359.</td>\n",
       "      <td>Stone-woman as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code sortfield 1st ed.                  chapter         division1  \\\n",
       "0    A0     A0000      A0  A. Mythological motifs.  A0–A99. Creator.   \n",
       "1    A1     A0001          A. Mythological motifs.  A0–A99. Creator.   \n",
       "2  A1.1   A0001.1      A1  A. Mythological motifs.  A0–A99. Creator.   \n",
       "3  A1.2   A0001.2          A. Mythological motifs.  A0–A99. Creator.   \n",
       "4  A1.3   A0001.3          A. Mythological motifs.  A0–A99. Creator.   \n",
       "\n",
       "  division2 division3    section10s                          MOTIF  \\\n",
       "0                      A0. Creator.                   A0. Creator.   \n",
       "1                      A0. Creator.       A1. Identity of creator.   \n",
       "2                      A0. Creator.      A1.1. Sun-god as creator.   \n",
       "3                      A0. Creator.  A1.2. Grandfather as creator.   \n",
       "4                      A0. Creator.  A1.3. Stone-woman as creator.   \n",
       "\n",
       "                                      bibliographies             motif_label  \\\n",
       "0  For a general bibliography of creation myths, ...                 Creator   \n",
       "1                                                        Identity of creator   \n",
       "2          Egyptian: Müller 69; Persian: Carnoy 260.      Sun-god as creator   \n",
       "3  S. Am. Indian (Paressi): Métraux BBAE CXLIII (...  Grandfather as creator   \n",
       "4              Paressi: Métraux BBAE CXLIII (3) 359.  Stone-woman as creator   \n",
       "\n",
       "         chapter_label  \n",
       "0  Mythological motifs  \n",
       "1  Mythological motifs  \n",
       "2  Mythological motifs  \n",
       "3  Mythological motifs  \n",
       "4  Mythological motifs  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def chapter_splitter(cell):\n",
    "    \"\"\"Split out Chapter label.\"\"\"\n",
    "    if cell:\n",
    "        parts = cell.split()\n",
    "        return pd.Series({\"chapter_label\": \" \".join(parts[1:]).strip(\".\")})\n",
    "    return pd.Series({\"chapter_label\":''})\n",
    "\n",
    "thompson_motif_df[[\"chapter_label\"]] = thompson_motif_df[\"chapter\"].apply(chapter_splitter)\n",
    "thompson_motif_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0eebb0d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>sortfield</th>\n",
       "      <th>1st ed.</th>\n",
       "      <th>chapter</th>\n",
       "      <th>division1</th>\n",
       "      <th>division2</th>\n",
       "      <th>division3</th>\n",
       "      <th>section10s</th>\n",
       "      <th>MOTIF</th>\n",
       "      <th>bibliographies</th>\n",
       "      <th>motif_label</th>\n",
       "      <th>chapter_label</th>\n",
       "      <th>section10s_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>A0000</td>\n",
       "      <td>A0</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>For a general bibliography of creation myths, ...</td>\n",
       "      <td>Creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>A0001</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1. Identity of creator.</td>\n",
       "      <td></td>\n",
       "      <td>Identity of creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1.1</td>\n",
       "      <td>A0001.1</td>\n",
       "      <td>A1</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.1. Sun-god as creator.</td>\n",
       "      <td>Egyptian: Müller 69; Persian: Carnoy 260.</td>\n",
       "      <td>Sun-god as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A1.2</td>\n",
       "      <td>A0001.2</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.2. Grandfather as creator.</td>\n",
       "      <td>S. Am. Indian (Paressi): Métraux BBAE CXLIII (...</td>\n",
       "      <td>Grandfather as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1.3</td>\n",
       "      <td>A0001.3</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.3. Stone-woman as creator.</td>\n",
       "      <td>Paressi: Métraux BBAE CXLIII (3) 359.</td>\n",
       "      <td>Stone-woman as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code sortfield 1st ed.                  chapter         division1  \\\n",
       "0    A0     A0000      A0  A. Mythological motifs.  A0–A99. Creator.   \n",
       "1    A1     A0001          A. Mythological motifs.  A0–A99. Creator.   \n",
       "2  A1.1   A0001.1      A1  A. Mythological motifs.  A0–A99. Creator.   \n",
       "3  A1.2   A0001.2          A. Mythological motifs.  A0–A99. Creator.   \n",
       "4  A1.3   A0001.3          A. Mythological motifs.  A0–A99. Creator.   \n",
       "\n",
       "  division2 division3    section10s                          MOTIF  \\\n",
       "0                      A0. Creator.                   A0. Creator.   \n",
       "1                      A0. Creator.       A1. Identity of creator.   \n",
       "2                      A0. Creator.      A1.1. Sun-god as creator.   \n",
       "3                      A0. Creator.  A1.2. Grandfather as creator.   \n",
       "4                      A0. Creator.  A1.3. Stone-woman as creator.   \n",
       "\n",
       "                                      bibliographies             motif_label  \\\n",
       "0  For a general bibliography of creation myths, ...                 Creator   \n",
       "1                                                        Identity of creator   \n",
       "2          Egyptian: Müller 69; Persian: Carnoy 260.      Sun-god as creator   \n",
       "3  S. Am. Indian (Paressi): Métraux BBAE CXLIII (...  Grandfather as creator   \n",
       "4              Paressi: Métraux BBAE CXLIII (3) 359.  Stone-woman as creator   \n",
       "\n",
       "         chapter_label section10s_label  \n",
       "0  Mythological motifs          Creator  \n",
       "1  Mythological motifs          Creator  \n",
       "2  Mythological motifs          Creator  \n",
       "3  Mythological motifs          Creator  \n",
       "4  Mythological motifs          Creator  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def section_tens_splitter(cell):\n",
    "    \"\"\"Split out section(10s) Label and code.\"\"\"\n",
    "    if cell:\n",
    "        parts = cell.split()\n",
    "        return pd.Series({\"section10s_label\": \" \".join(parts[1:]).strip(\".\")})\n",
    "    return pd.Series({\"section10s_label\":''})\n",
    "\n",
    "thompson_motif_df[[\"section10s_label\"]] = thompson_motif_df['section10s'].apply(section_tens_splitter)\n",
    "thompson_motif_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3346f30b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>sortfield</th>\n",
       "      <th>1st ed.</th>\n",
       "      <th>chapter</th>\n",
       "      <th>division1</th>\n",
       "      <th>division2</th>\n",
       "      <th>division3</th>\n",
       "      <th>section10s</th>\n",
       "      <th>MOTIF</th>\n",
       "      <th>bibliographies</th>\n",
       "      <th>motif_label</th>\n",
       "      <th>chapter_label</th>\n",
       "      <th>section10s_label</th>\n",
       "      <th>division1_code</th>\n",
       "      <th>division1_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>A0000</td>\n",
       "      <td>A0</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>For a general bibliography of creation myths, ...</td>\n",
       "      <td>Creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "      <td>A0–A99</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>A0001</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1. Identity of creator.</td>\n",
       "      <td></td>\n",
       "      <td>Identity of creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "      <td>A0–A99</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A1.1</td>\n",
       "      <td>A0001.1</td>\n",
       "      <td>A1</td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.1. Sun-god as creator.</td>\n",
       "      <td>Egyptian: Müller 69; Persian: Carnoy 260.</td>\n",
       "      <td>Sun-god as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "      <td>A0–A99</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A1.2</td>\n",
       "      <td>A0001.2</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.2. Grandfather as creator.</td>\n",
       "      <td>S. Am. Indian (Paressi): Métraux BBAE CXLIII (...</td>\n",
       "      <td>Grandfather as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "      <td>A0–A99</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A1.3</td>\n",
       "      <td>A0001.3</td>\n",
       "      <td></td>\n",
       "      <td>A. Mythological motifs.</td>\n",
       "      <td>A0–A99. Creator.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>A0. Creator.</td>\n",
       "      <td>A1.3. Stone-woman as creator.</td>\n",
       "      <td>Paressi: Métraux BBAE CXLIII (3) 359.</td>\n",
       "      <td>Stone-woman as creator</td>\n",
       "      <td>Mythological motifs</td>\n",
       "      <td>Creator</td>\n",
       "      <td>A0–A99</td>\n",
       "      <td>Creator</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code sortfield 1st ed.                  chapter         division1  \\\n",
       "0    A0     A0000      A0  A. Mythological motifs.  A0–A99. Creator.   \n",
       "1    A1     A0001          A. Mythological motifs.  A0–A99. Creator.   \n",
       "2  A1.1   A0001.1      A1  A. Mythological motifs.  A0–A99. Creator.   \n",
       "3  A1.2   A0001.2          A. Mythological motifs.  A0–A99. Creator.   \n",
       "4  A1.3   A0001.3          A. Mythological motifs.  A0–A99. Creator.   \n",
       "\n",
       "  division2 division3    section10s                          MOTIF  \\\n",
       "0                      A0. Creator.                   A0. Creator.   \n",
       "1                      A0. Creator.       A1. Identity of creator.   \n",
       "2                      A0. Creator.      A1.1. Sun-god as creator.   \n",
       "3                      A0. Creator.  A1.2. Grandfather as creator.   \n",
       "4                      A0. Creator.  A1.3. Stone-woman as creator.   \n",
       "\n",
       "                                      bibliographies             motif_label  \\\n",
       "0  For a general bibliography of creation myths, ...                 Creator   \n",
       "1                                                        Identity of creator   \n",
       "2          Egyptian: Müller 69; Persian: Carnoy 260.      Sun-god as creator   \n",
       "3  S. Am. Indian (Paressi): Métraux BBAE CXLIII (...  Grandfather as creator   \n",
       "4              Paressi: Métraux BBAE CXLIII (3) 359.  Stone-woman as creator   \n",
       "\n",
       "         chapter_label section10s_label division1_code division1_label  \n",
       "0  Mythological motifs          Creator         A0–A99         Creator  \n",
       "1  Mythological motifs          Creator         A0–A99         Creator  \n",
       "2  Mythological motifs          Creator         A0–A99         Creator  \n",
       "3  Mythological motifs          Creator         A0–A99         Creator  \n",
       "4  Mythological motifs          Creator         A0–A99         Creator  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def section_division1_splitter(cell):\n",
    "    \"\"\"Split out division1 Label and code.\"\"\"\n",
    "    if cell:\n",
    "        parts = cell.split()\n",
    "        return pd.Series({\"division1_code\": parts[0].strip(\".\"),\n",
    "                          \"division1_label\": \" \".join(parts[1:]).strip(\".\")})\n",
    "    return pd.Series({\"division1_code\":'', \"division1_label\":''})\n",
    "\n",
    "thompson_motif_df[[\"division1_code\", \"division1_label\"]] = thompson_motif_df['division1'].apply(section_division1_splitter)\n",
    "thompson_motif_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0567ef8d",
   "metadata": {},
   "source": [
    "We can add this table directly to a simple database. First, create the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "545625b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlite_utils import Database\n",
    "\n",
    "db_name = \"motifs_demo.db\"\n",
    "\n",
    "# While developing the script, recreate database each time...\n",
    "db = Database(db_name, recreate=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "045c8365",
   "metadata": {},
   "source": [
    "Now add the dataframe to the database as a databse table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7d77d50f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table motifs_km (code, sortfield, 1st ed., chapter, division1, division2, division3, section10s, MOTIF, bibliographies, motif_label, chapter_label, section10s_label, division1_code, division1_label)>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#db[\"motifs_km\"].drop()\n",
    "#db[\"motifs_km_fts\"].drop()\n",
    "db[\"motifs_km\"].create({\n",
    "    \"code\": str,\n",
    "    \"sortfield\": str,\n",
    "    \"1st ed.\": str,\n",
    "    \"chapter\": str,\n",
    "    \"division1\": str,\n",
    "    \"division2\": str,\n",
    "    \"division3\": str,\n",
    "    \"section10s\": str, \n",
    "    \"MOTIF\": str,\n",
    "    \"bibliographies\": str,\n",
    "    \"motif_label\": str,\n",
    "    \"chapter_label\": str,\n",
    "    \"section10s_label\": str,\n",
    "    \"division1_code\": str, \n",
    "    \"division1_label\": str,\n",
    "})\n",
    "# Create a full text search table to improve search support\n",
    "db[\"motifs_km\"].enable_fts([\"bibliographies\", \"motif_label\", \"sortfield\"], create_triggers=True, tokenize=\"porter\")\n",
    "\n",
    "db[\"motifs_km\"].insert_all(thompson_motif_df.to_dict(orient=\"records\"))\n",
    "#thompson_motif_df.to_sql(\"motifs_km\", index=False, if_exists=\"replace\", con=db.conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1a6e522",
   "metadata": {},
   "source": [
    "And query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4b7b129b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bibliographies</th>\n",
       "      <th>motif_label</th>\n",
       "      <th>sortfield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>For a general bibliography of creation myths, ...</td>\n",
       "      <td>Creator</td>\n",
       "      <td>A0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td>Identity of creator</td>\n",
       "      <td>A0001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Egyptian: Müller 69; Persian: Carnoy 260.</td>\n",
       "      <td>Sun-god as creator</td>\n",
       "      <td>A0001.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      bibliographies          motif_label  \\\n",
       "0  For a general bibliography of creation myths, ...              Creator   \n",
       "1                                                     Identity of creator   \n",
       "2          Egyptian: Müller 69; Persian: Carnoy 260.   Sun-god as creator   \n",
       "\n",
       "  sortfield  \n",
       "0     A0000  \n",
       "1     A0001  \n",
       "2   A0001.1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas import read_sql\n",
    "\n",
    "q = \"SELECT * FROM motifs_km_fts LIMIT 3\"\n",
    "\n",
    "read_sql(q, db.conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "26d3cbc1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bibliographies</th>\n",
       "      <th>motif_label</th>\n",
       "      <th>sortfield</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>India: Thompson-Balys.</td>\n",
       "      <td>Creation of cockroach</td>\n",
       "      <td>A2061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Finnish: Aarne FFC VIII 22 No. 125.</td>\n",
       "      <td>Origin of cockroach in Finland</td>\n",
       "      <td>A2061.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>India: Thompson-Balys.</td>\n",
       "      <td>Why cockroaches live in houses</td>\n",
       "      <td>A2433.5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Antigua: Johnson JAFL XXXIV 66.</td>\n",
       "      <td>Enmity between fowl and cockroach</td>\n",
       "      <td>A2494.13.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>India: Thompson-Balys.</td>\n",
       "      <td>Wedding of mouse and cockroach</td>\n",
       "      <td>B0281.02.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>India: Thompson-Balys.</td>\n",
       "      <td>Wedding of rat and cockroach</td>\n",
       "      <td>B0281.11.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>India: Thompson-Balys.</td>\n",
       "      <td>Wedding of cockroach and rat</td>\n",
       "      <td>B0285.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>India: Thompson-Balys.</td>\n",
       "      <td>Magic whistle gives life to cockroach. (Cf. D1...</td>\n",
       "      <td>D1594.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        bibliographies  \\\n",
       "0               India: Thompson-Balys.   \n",
       "1  Finnish: Aarne FFC VIII 22 No. 125.   \n",
       "2               India: Thompson-Balys.   \n",
       "3      Antigua: Johnson JAFL XXXIV 66.   \n",
       "4               India: Thompson-Balys.   \n",
       "5               India: Thompson-Balys.   \n",
       "6               India: Thompson-Balys.   \n",
       "7               India: Thompson-Balys.   \n",
       "\n",
       "                                         motif_label    sortfield  \n",
       "0                              Creation of cockroach        A2061  \n",
       "1                     Origin of cockroach in Finland      A2061.1  \n",
       "2                     Why cockroaches live in houses    A2433.5.7  \n",
       "3                  Enmity between fowl and cockroach  A2494.13.03  \n",
       "4                     Wedding of mouse and cockroach   B0281.02.2  \n",
       "5                       Wedding of rat and cockroach   B0281.11.1  \n",
       "6                       Wedding of cockroach and rat      B0285.8  \n",
       "7  Magic whistle gives life to cockroach. (Cf. D1...      D1594.6  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# FTS search\n",
    "q = f\"\"\"\n",
    "SELECT motifs_km_fts.* FROM motifs_km_fts\n",
    "WHERE motifs_km_fts MATCH {db.quote('cockroach')};\n",
    "\"\"\"\n",
    "\n",
    "read_sql(q, db.conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "859ba620",
   "metadata": {},
   "source": [
    "## MOMFER - Meerten online motif finder\n",
    "\n",
    "The *MOMFER - Meerten online motif finder*, http://www.momfer.ml, is an online search engine over motifs.\n",
    "\n",
    "Source code and related data files appear to be available at: https://github.com/fbkarsdorp/tmi *TMI: Interface on Thompson's Motif Index\"*.\n",
    "\n",
    "See also:\n",
    "\n",
    "- *Folgert Karsdorp, Marten van der Meulen, Theo Meder, Antal van den Bosch: MOMFER. A Search Engine of Thompson's Motif-Index of Folk Literature. In: Folklore 126 (2015), no. 1, 37–52. https://doi.org/10.1080/0015587X.2015.1006954 ]*\n",
    "- *Thierry Declerck, Antónia Kostová, Lisa Schäfer: Linked Data Access to Folktales classified by Thompson's Motifs and Aarne-Thompson-Uther's Types. In: Rhian Lewis et al. (Eds.): Digital Humanities 2017 (DH2017), Montréal, August 8-11, 2017, Conference Abstracts. Montréal: ADHO, https://dh2017.adho.org/abstracts/465/465.pdf*\n",
    "- https://www.dfki.de/fileadmin/user_upload/import/9028_Dh2017_LOD_TMI-ATU_final.pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83cc014f",
   "metadata": {},
   "source": [
    "The json data in the repository looks very reminiscent of Katja Mellmann's *Thompson's Motif-Index as CSV File* although there does appear to be some additional data, suc as the `locations` field: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ee28d952",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"motif\": \"A0\", \n",
      "        \"description\": \"Creator.\", \n",
      "        \"lemmas\": [\n",
      "            \"creator\"\n",
      "        ], \n",
      "        \"locations\": [\n",
      "            \"Ireland\", \n",
      "            \"Mexico\", \n",
      "            \"Africa\", \n",
      "            \"India\", \n",
      "            \"Loango\", \n",
      "            \"Polynesia\", \n",
      "            \"United States\", \n",
      "            \"Armenia\", \n",
      "            \"Tahiti\"\n",
      "        ], \n",
      "        \"additional_description\": \"\", \n",
      "        \"references\": \"For a general bibliography of creation myths, see Alexander N. Am. 278 n. 15. For bibliographies of North American Indian mythologies arranged by areas, see Thompson Tales 272 n. 1; **Feilberg Skabelses og Syndflodssagn; Jewish: Neuman. Mexican Indian: (Tarascan) Alexander Lat. Am. Armenian: Ananikian 20; African: Werner African 127ff., **Frobenius and Fox, (Loango): Pechul l-Loesche 267; Hindu: Penzer I 10; Buddhist myth: Malalasekera II 338; Icel.: Boberg, MacCulloch Eddic 326; Irish myth: Cross.\"\n",
      "    }, \n",
      "    {\n",
      "        \"motif\": \"A1\", \n",
      "        \"description\": \"Identity of creator.\", \n",
      "        \"lemmas\": [\n",
      "            \"identity\", \n",
      "            \"creator\"\n",
      "        ], \n",
      "        \"locations\": [], \n",
      "        \"additional_description\": \"\", \n",
      "        \"references\": \"\"\n",
      "    }, \n",
      "    {\n",
      "        \"motif\": \"A1.1\", \n",
      "        \"description\": \"Sun-god as creator.\", \n",
      "        \"lemmas\": [\n",
      "            \"sun-god\", \n",
      "            \"creator\"\n",
      "        ], \n",
      "        \"locations\": [\n",
      "            \"Egypt\"\n",
      "        ], \n",
      "        \"additional_description\": \"\", \n",
      "        \"references\": \"Egyptian: M' ller 69; P\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "momfer_json = requests.get(\"https://github.com/fbkarsdorp/tmi/blob/master/data/tmi.json?raw=true\")\n",
    "print(momfer_json.text[:1500])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ab487675",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'motif': 'A0',\n",
       " 'description': 'Creator.',\n",
       " 'lemmas': ['creator'],\n",
       " 'locations': ['Ireland',\n",
       "  'Mexico',\n",
       "  'Africa',\n",
       "  'India',\n",
       "  'Loango',\n",
       "  'Polynesia',\n",
       "  'United States',\n",
       "  'Armenia',\n",
       "  'Tahiti'],\n",
       " 'additional_description': '',\n",
       " 'references': 'For a general bibliography of creation myths, see Alexander N. Am. 278 n. 15. For bibliographies of North American Indian mythologies arranged by areas, see Thompson Tales 272 n. 1; **Feilberg Skabelses og Syndflodssagn; Jewish: Neuman. Mexican Indian: (Tarascan) Alexander Lat. Am. Armenian: Ananikian 20; African: Werner African 127ff., **Frobenius and Fox, (Loango): Pechul l-Loesche 267; Hindu: Penzer I 10; Buddhist myth: Malalasekera II 338; Icel.: Boberg, MacCulloch Eddic 326; Irish myth: Cross.'}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "momfer_json.json()[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fd73ed61",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A0.\tCreator.\n",
      "A1.\tIdentity of creator.\n",
      "A1.1.\tSun-god as creator.\n",
      "A1.2.\tGrandfather as creator.\n",
      "A1.3.\tStone-woman as creator.\n",
      "A1.4.\tBrahma as creator.\n",
      "A2.\tMultiple creators.\n",
      "A2.1.\tThree creators.\n",
      "A2.2.\tFirst human pair as creators.\n",
      "A3.\tCreative mother source of everything.\n",
      "A5.\tReason for creation.\n",
      "A5.1.\tGods make earth to have place to rest their feet.\n",
      "A7.\tCreator's descendants.\n",
      "A7.1.\tCreator has two sons.\n",
      "A10.\tNature of the creator.\n",
      "A11.\tInvisible creator.\n",
      "A11.1.\tInvisibility of creator learned from the impossibility of staring at the sun, his servant.\n",
      "A12.\tHermaphroditic creator.\n",
      "A12.1.\tMale and female creators.\n",
      "A13.\tAnimal as creator.\n",
      "A13.1.\tBeast as creator.\n",
      "A13.1.1.\tCow as creator.\n",
      "A13.2.\tBird as creator.\n",
      "A13.2.1.\tRaven as creator.\n",
      "A13.2.2.\tEagle as creator of man.\n",
      "A13.2.3.\tBlack-winged bird as creator.\n",
      "A13.3.\tInsect as creator.\n",
      "A13.3.1.\tSpider as creator.\n",
      "A13.3.2.\tBeetle as creator.\n",
      "A13.4.\tReptile as creator.\n",
      "A13.4.1.\tSnake as creator.\n",
      "A13.4.2.\tWorm as creator.\n",
      "A15.\tHuman creator.\n",
      "A15.1.\tFemale creator.\n",
      "A15.1.1.\tOld woman as creator.\n",
      "A15.2.\tBrothers as creators.\n",
      "A15.3.\tOld man as creator.\n",
      "A15.3.1.\tOld man with staff as creator.\n",
      "A15.4.\tArtisan as creator.\n",
      "A15.4.1.\tPotter as creator.\n",
      "A17.\tAngel as creator.\n",
      "A18.\tPictorial representations of creator.\n",
      "A18.1.\tCreator with dragon's head.\n",
      "A18.2.\tCreator with two horns on head.\n",
      "A18.3.\tDwarfish creator.\n",
      "A18.4.\tCreator clothed in bear-skin (or in leaves).\n",
      "A18.5.\tCreator with hammer and chisel in hands.\n",
      "A18.6.\tCreator with sun and mo\n"
     ]
    }
   ],
   "source": [
    "tmi_data = requests.get(\"https://github.com/fbkarsdorp/tmi/blob/master/data/tmi-cleaned.txt?raw=true\")\n",
    "print(tmi_data.text[:1500])"
   ]
  }
 ],
 "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.9.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}