01-repertory_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter, defaultdict\n",
    "import string\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.options.display.max_rows = 999"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Main repertory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df0 = pd.read_excel('data/src/DunhamsData_Repertory_DatasetSample.xlsx', keep_default_na=False)\n",
    "df0.columns = [col.strip() for col in df0.columns]\n",
    "df0.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 8)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df0.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Work</th>\n",
       "      <th>Also Known As</th>\n",
       "      <th>Category</th>\n",
       "      <th>Pieces</th>\n",
       "      <th>Part of Larger Work (n/a if stand-alone)</th>\n",
       "      <th>Sources (collection/box/folder)</th>\n",
       "      <th>Number of events documented as Piece</th>\n",
       "      <th>Number of events documented as Container</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Americana</td>\n",
       "      <td>America</td>\n",
       "      <td>Container</td>\n",
       "      <td>Veracruzana</td>\n",
       "      <td>n/a</td>\n",
       "      <td>SIU Programs Box 85-86; Rhoda Winter Russell P...</td>\n",
       "      <td>0</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Veracruzana</td>\n",
       "      <td></td>\n",
       "      <td>Piece/Standalone</td>\n",
       "      <td></td>\n",
       "      <td>n/a; Americana</td>\n",
       "      <td>SIU Programs Box 85-86; Rhoda Winter Russell P...</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Work Also Known As          Category       Pieces  \\\n",
       "0    Americana       America         Container  Veracruzana   \n",
       "1  Veracruzana                Piece/Standalone                \n",
       "\n",
       "  Part of Larger Work (n/a if stand-alone)  \\\n",
       "0                                      n/a   \n",
       "1                           n/a; Americana   \n",
       "\n",
       "                     Sources (collection/box/folder)  \\\n",
       "0  SIU Programs Box 85-86; Rhoda Winter Russell P...   \n",
       "1  SIU Programs Box 85-86; Rhoda Winter Russell P...   \n",
       "\n",
       "   Number of events documented as Piece  \\\n",
       "0                                     0   \n",
       "1                                    65   \n",
       "\n",
       "   Number of events documented as Container  \n",
       "0                                        52  \n",
       "1                                         0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A Work\n",
      "B Also Known As\n",
      "C Category\n",
      "D Pieces\n",
      "E Part of Larger Work (n/a if stand-alone)\n",
      "F Sources (collection/box/folder)\n",
      "G Number of events documented as Piece\n",
      "H Number of events documented as Container\n"
     ]
    }
   ],
   "source": [
    "for a, b in zip(string.ascii_uppercase, df0.columns):\n",
    "    print(a, b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dances in dances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel('data/src/DunhamsData_Repertory_DatasetSample.xlsx', sheet_name=1)\n",
    "df1.columns = [col.strip() for col in df1.columns]\n",
    "df1.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 4)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Dances in Dances</th>\n",
       "      <th>Appears in</th>\n",
       "      <th>Sources (collection/box/folder)</th>\n",
       "      <th>Number of events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>La Bamba</td>\n",
       "      <td>Veracruzana</td>\n",
       "      <td>SIU Programs Box 85-86</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Dances in Dances    Appears in Sources (collection/box/folder)  \\\n",
       "0        La Bamba   Veracruzana           SIU Programs Box 85-86   \n",
       "\n",
       "   Number of events  \n",
       "0                15  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A Dances in Dances\n",
      "B Appears in\n",
      "C Sources (collection/box/folder)\n",
      "D Number of events\n"
     ]
    }
   ],
   "source": [
    "for a, b in zip(string.ascii_uppercase, df1.columns):\n",
    "    print(a, b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Shows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_excel('data/src/DunhamsData_Repertory_DatasetSample.xlsx', sheet_name=2)\n",
    "df2.columns = [col.strip() for col in df2.columns]\n",
    "df2.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 5)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Name</th>\n",
       "      <th>Also Known As</th>\n",
       "      <th>Pieces</th>\n",
       "      <th>Sources (collection/box/folder)</th>\n",
       "      <th>Number of events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Caribbean Rhapsody</td>\n",
       "      <td>Rapsodia Caribe; A Caribbean Rhapsody; Rhapsod...</td>\n",
       "      <td>Americana; Veracruzana</td>\n",
       "      <td>SIU Programs Box 85-86</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tropical Carnival</td>\n",
       "      <td></td>\n",
       "      <td>Veracruzana; Americana</td>\n",
       "      <td>SIU Programs Box 85-86</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Name                                      Also Known As  \\\n",
       "0  Caribbean Rhapsody  Rapsodia Caribe; A Caribbean Rhapsody; Rhapsod...   \n",
       "1   Tropical Carnival                                                      \n",
       "\n",
       "                   Pieces Sources (collection/box/folder)  Number of events  \n",
       "0  Americana; Veracruzana          SIU Programs Box 85-86                20  \n",
       "1  Veracruzana; Americana          SIU Programs Box 85-86                 4  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A Name\n",
      "B Also Known As\n",
      "C Pieces\n",
      "D Sources (collection/box/folder)\n",
      "E Number of events\n"
     ]
    }
   ],
   "source": [
    "for a, b in zip(string.ascii_uppercase, df2.columns):\n",
    "    print(a, b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-processing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Main repertory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_0_work = 'Work'\n",
    "col_0_aka = 'Also Known As'\n",
    "col_0_pieces = 'Pieces'\n",
    "col_0_larger = 'Part of Larger Work (n/a if stand-alone)'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dances in dances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_1_dance = 'Dances in Dances'\n",
    "col_1_appears = 'Appears in'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Shows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_2_show = 'Name'\n",
    "col_2_aka = 'Also Known As'\n",
    "col_2_pieces = 'Pieces'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### New data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# main_df = df0[[col_0_work, col_0_aka, col_0_pieces, col_0_larger]].copy()\n",
    "# dances_df = df1[[col_1_dance, col_1_appears]].copy()\n",
    "# shows_df = df2[[col_2_show, col_2_aka, col_2_pieces]].copy()\n",
    "\n",
    "main_df = df0.copy()\n",
    "dances_df = df1.copy()\n",
    "shows_df = df2.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def str2set(x):\n",
    "    return set(y.strip() for y in x.split(';') if y.strip())\n",
    "\n",
    "def set2str(x):\n",
    "    return '; '.join(sorted(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_df[col_0_work] = main_df[col_0_work].apply(str.strip)\n",
    "main_df[col_0_aka] = main_df[col_0_aka].apply(str2set)\n",
    "main_df[col_0_pieces] = main_df[col_0_pieces].apply(str2set)\n",
    "main_df[col_0_larger] = main_df[col_0_larger].apply(str2set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "dances_df[col_1_dance] = dances_df[col_1_dance].apply(str.strip)\n",
    "dances_df[col_1_appears] = dances_df[col_1_appears].apply(str2set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "shows_df[col_2_show] = shows_df[col_2_show].apply(str.strip)\n",
    "shows_df[col_2_aka] = shows_df[col_2_aka].apply(str2set)\n",
    "shows_df[col_2_pieces] = shows_df[col_2_pieces].apply(str2set)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Checking data integrity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Unique ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ids = []\n",
    "for i, row in main_df.iterrows():\n",
    "    work = row[col_0_work]\n",
    "    aka = row[col_0_aka]\n",
    "    all_ids.append(work)\n",
    "    for a in aka:\n",
    "        all_ids.append(a)\n",
    "for dance in dances_df[col_1_dance]:\n",
    "    all_ids.append(dance)\n",
    "for i, row in shows_df.iterrows():\n",
    "    show = row[col_2_show]\n",
    "    aka = row[col_2_aka]\n",
    "    all_ids.append(show)\n",
    "    for a in aka:\n",
    "        all_ids.append(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_ids) == len(set(all_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = Counter(all_ids)\n",
    "[(a, b) for (a, b) in c.most_common() if b > 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Preliminary cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dances_df[col_1_dance] = dances_df[col_1_dance].replace('Blues', 'Blues [Dances in Dances]')\n",
    "# dances_df[col_1_dance] = dances_df[col_1_dance].replace('Mambo', 'Mambo [Dances in Dances]')\n",
    "# dances_df[col_1_dance] = dances_df[col_1_dance].replace('Maracatu', 'Maracatu [Dances in Dances]')\n",
    "# shows_df = shows_df[shows_df[col_2_show] != 'Island Song']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing unnecessary duplicated AKAs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# It will affect \"Strutters' Ball\" only\n",
    "for i, row in main_df.iterrows():\n",
    "    work = row[col_0_work]\n",
    "    aka = row[col_0_aka]\n",
    "    if work in aka:\n",
    "        main_df.loc[i, col_0_aka].remove(work)\n",
    "# for i, row in shows_df.iterrows():\n",
    "#     show = row[col_2_show]\n",
    "#     aka = row[col_2_aka]\n",
    "#     if show in aka:\n",
    "#         shows_df.loc[i, col_2_aka].remove(show)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Renaming ambiguous AKAs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# It will affect 'Bel Congo', 'Congo Femme', 'Congo Fran', 'Mambo', and 'Spanish Harlem'\n",
    "for i, row in main_df.iterrows():\n",
    "    work = row[col_0_work]\n",
    "    aka = row[col_0_aka]\n",
    "    for a in aka:\n",
    "        if c[a] > 1:\n",
    "            new_a = '{} [[AKA for {}]]'.format(a, work)\n",
    "            print(new_a)\n",
    "            main_df.loc[i, col_0_aka].remove(a)\n",
    "            main_df.loc[i, col_0_aka].add(new_a)\n",
    "# for i, row in shows_df.iterrows():\n",
    "#     show = row[col_2_show]\n",
    "#     aka = row[col_2_aka]\n",
    "#     for a in aka:\n",
    "#         if c[a] > 1:\n",
    "#             new_a = '{} [[{}]]'.format(a, show)\n",
    "#             shows_df.loc[i, col_2_aka].remove(a)\n",
    "#             shows_df.loc[i, col_2_aka].add(new_a)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Unique ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ids = []\n",
    "for i, row in main_df.iterrows():\n",
    "    work = row[col_0_work]\n",
    "    aka = row[col_0_aka]\n",
    "    all_ids.append(work)\n",
    "    for a in aka:\n",
    "        all_ids.append(a)\n",
    "for dance in dances_df[col_1_dance]:\n",
    "    all_ids.append(dance)\n",
    "for i, row in shows_df.iterrows():\n",
    "    show = row[col_2_show]\n",
    "    aka = row[col_2_aka]\n",
    "    all_ids.append(show)\n",
    "    for a in aka:\n",
    "        all_ids.append(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(all_ids) == len(set(all_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = Counter(all_ids)\n",
    "[(a, b) for (a, b) in c.most_common() if b > 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Smaller and larger work references"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_ids = main_df.Work.unique()\n",
    "\n",
    "for i, row in main_df.iterrows():\n",
    "    pieces = list(row[col_0_pieces])\n",
    "    larger = list(row[col_0_larger])\n",
    "    for p in pieces:\n",
    "        if False:\n",
    "            pass\n",
    "#         if p == 'Strutters Ball':\n",
    "#             new_p = \"Strutters' Ball\"\n",
    "#             main_df.loc[i, col_0_pieces].remove(p)\n",
    "#             main_df.loc[i, col_0_pieces].add(new_p)\n",
    "#         elif p == 'Variations on the Theme \"Boogie Woogie\"':\n",
    "#             new_p = 'Variation on the Theme \"Boogie Woogie\"'\n",
    "#             main_df.loc[i, col_0_pieces].remove(p)\n",
    "#             main_df.loc[i, col_0_pieces].add(new_p)\n",
    "        elif p not in main_ids:\n",
    "            print('Main - Piece:', p)\n",
    "    for lw in larger:\n",
    "        if lw == 'n/a':\n",
    "            pass\n",
    "        elif lw not in main_ids:\n",
    "            print('Main - Larger:', lw)\n",
    "\n",
    "for i, row in dances_df.iterrows():\n",
    "    pieces = list(row[col_1_appears])\n",
    "    for p in pieces:\n",
    "        if False:\n",
    "            pass\n",
    "#         if p == \"Bre'r Rabbit An' De Tah Baby\":\n",
    "#             new_p = \"Bre'r Rabbit an' de Tah Baby\"\n",
    "#             dances_df.loc[i, col_1_appears].remove(p)\n",
    "#             dances_df.loc[i, col_1_appears].add(new_p)\n",
    "#         elif p == 'Mambo - Havana':\n",
    "#             new_p = 'Mambo'\n",
    "#             dances_df.loc[i, col_1_appears].remove(p)\n",
    "#             dances_df.loc[i, col_1_appears].add(new_p)\n",
    "#         elif p == 'Washerwomen':\n",
    "#             new_p = 'Washerwomen Dance'\n",
    "#             dances_df.loc[i, col_1_appears].remove(p)\n",
    "#             dances_df.loc[i, col_1_appears].add(new_p)\n",
    "#         elif p == 'Island Songs':\n",
    "#             new_p = 'Island Song'\n",
    "#             dances_df.loc[i, col_1_appears].remove(p)\n",
    "#             dances_df.loc[i, col_1_appears].add(new_p)\n",
    "        elif p not in main_ids:\n",
    "            print('Dances - Piece:', p)\n",
    "\n",
    "for i, row in shows_df.iterrows():\n",
    "    pieces = list(row[col_2_pieces])\n",
    "    for p in pieces:\n",
    "        if False:\n",
    "            pass\n",
    "#         if p == 'Shango Ritual and Dance':\n",
    "#             new_p = 'Shango'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Strutters Ball':\n",
    "#             new_p = \"Strutters' Ball\"\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Marrakech':\n",
    "#             new_p = 'Marrakech!'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'The Diamond Theif':\n",
    "#             new_p = 'The Diamond Thief'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Shango Dance and Ritual':\n",
    "#             new_p = 'Shango'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Shango Ritual':\n",
    "#             new_p = 'Shango'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Rites De Passage':\n",
    "#             new_p = 'Rites de Passage'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'La Comparsa':\n",
    "#             new_p = 'Comparsa'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == \"L'ag'ya\":\n",
    "#             new_p = \"L'Ag'Ya\"\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Street Scene Port Au Prince':\n",
    "#             new_p = 'Street Scene (Port Au Prince)'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Rumba with a Little Jive Mixed in':\n",
    "#             new_p = 'Rhumba with a Little Jive Mixed In'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Plantation Dance':\n",
    "#             new_p = 'Plantation Dances'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Biguine- Biguine':\n",
    "#             new_p = 'Biguine, Biguine'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Sarabande to Mademe Christophe Queen of Haiti':\n",
    "#             new_p = 'Sarabande to Madame Christophe Queen of Haiti'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Rhumba Santiago Da Cuba':\n",
    "#             new_p = 'Rhumba, Santiago da Cuba'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Bahianna':\n",
    "#             new_p = 'Bahiana'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Plantation Dances from':\n",
    "#             new_p = 'Plantation Dances'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Tropics - Shore Excursion':\n",
    "#             new_p = 'Tropics'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "#         elif p == 'Island Songs':\n",
    "#             new_p = 'Island Song'\n",
    "#             shows_df.loc[i, col_2_pieces].remove(p)\n",
    "#             shows_df.loc[i, col_2_pieces].add(new_p)\n",
    "        elif p not in main_ids:\n",
    "            print('Shows - Piece:', p)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Others"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_df['Category'] = 'Main'\n",
    "shows_df['Category'] = 'Show'\n",
    "dances_df['Category'] = 'Dance'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# main_df.columns = ['Work', 'Aka', 'Pieces', 'Larger_works', 'Category']\n",
    "# dances_df.columns = ['Dance', 'Pieces', 'Category']\n",
    "# shows_df.columns = ['Show', 'Aka', 'Pieces', 'Category']\n",
    "\n",
    "main_df.rename(columns={\n",
    "    'Work':'Work',\n",
    "    'Also Known As':'Aka',\n",
    "    'Pieces':'Pieces',\n",
    "    'Part of Larger Work (n/a if stand-alone)':'Larger_works',\n",
    "    'Category':'Category',\n",
    "}, inplace=True)\n",
    "\n",
    "dances_df.rename(columns={\n",
    "    'Dances in Dances (check HB/KE but use TMU as base)':'Dance',\n",
    "    'Appears in':'Pieces',\n",
    "    'Category':'Category',\n",
    "}, inplace=True)\n",
    "\n",
    "shows_df.rename(columns={\n",
    "    'Name':'Show',\n",
    "    'Also Known As':'Aka',\n",
    "    'Pieces':'Pieces',\n",
    "    'Category':'Category',\n",
    "}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_df.to_csv('data/out/rep_main_structure.csv', index=False)\n",
    "shows_df.to_csv('data/out/rep_shows_structure.csv', index=False)\n",
    "dances_df.to_csv('data/out/rep_dances_structure.csv', index=False)"
   ]
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}