data_processing/data_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this sample application, we will explore image similarity search on Azure Cosmos DB for PostgreSQL using the [SemArt Dataset](https://researchdata.aston.ac.uk/id/eprint/380/). This dataset contains approximately 21k paintings gathered from the Web Gallery of Art. Each painting comes with various attributes, like a title, description, and the name of the artist. Let's embark on this creative exploration together!\n",
    "\n",
    "In this notebook, we'll take the following steps:\n",
    "\n",
    "* Load the data into a `pandas.DataFrame`.\n",
    "* Clean up text descriptions by removing special characters and addressing other errors.\n",
    "* Establish the dataset that will serve as the foundation for building the image similarity search application.\n",
    "\n",
    "Before you start:\n",
    "\n",
    "1. Download the dataset into the *semart_dataset* directory.\n",
    "2. Create a [virtual environment](https://docs.python.org/3/library/venv.html) and activate it.\n",
    "3. Install the [required Python packages](../requirements.txt)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from unidecode import unidecode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_folder = \"semart_dataset\"\n",
    "filenames = [\n",
    "    \"semart_train.csv\",\n",
    "    \"semart_test.csv\",\n",
    "    \"semart_val.csv\",\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the data into a `pandas.DataFrame`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `load_data` function creates a DataFrame based on the filepaths provided for the CSV files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_data(filepaths):\n",
    "    return pd.concat((pd.read_csv(file, sep=\"\\t\", encoding=\"cp1252\") for file in filepaths))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "filepaths = [os.path.join(\"..\", dataset_folder, filename) for filename in filenames]\n",
    "data = load_data(filepaths)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's explore the dataset!"
   ]
  },
  {
   "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>IMAGE_FILE</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>AUTHOR</th>\n",
       "      <th>TITLE</th>\n",
       "      <th>TECHNIQUE</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>SCHOOL</th>\n",
       "      <th>TIMEFRAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19873-1darmst.jpg</td>\n",
       "      <td>The Meyer or Darmstadt Madonna is the last, mo...</td>\n",
       "      <td>HOLBEIN, Hans the Younger</td>\n",
       "      <td>Darmstadt Madonna</td>\n",
       "      <td>Oil on limewood, 147 x 102 cm</td>\n",
       "      <td>1526 and after 1528</td>\n",
       "      <td>religious</td>\n",
       "      <td>German</td>\n",
       "      <td>1501-1550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18759-guard301.jpg</td>\n",
       "      <td>Whereas Canaletto incorporates classically ins...</td>\n",
       "      <td>GUARDI, Francesco</td>\n",
       "      <td>Landscape with a Fisherman's Tent</td>\n",
       "      <td>Oil on canvas, 49 x 77 cm</td>\n",
       "      <td>1770-75</td>\n",
       "      <td>landscape</td>\n",
       "      <td>Italian</td>\n",
       "      <td>1751-1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04589-temptati.jpg</td>\n",
       "      <td>In this painting the refinement of the colouri...</td>\n",
       "      <td>BILIVERT, Giovanni</td>\n",
       "      <td>The Temptation of Charles and Ubalde</td>\n",
       "      <td>Oil on copper, 37 x 28 cm</td>\n",
       "      <td>1629-30</td>\n",
       "      <td>religious</td>\n",
       "      <td>Italian</td>\n",
       "      <td>1601-1650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15104-magi.jpg</td>\n",
       "      <td>The left side of the painting was cut, origina...</td>\n",
       "      <td>GEERTGEN tot Sint Jans</td>\n",
       "      <td>Adoration of the Magi</td>\n",
       "      <td>Panel, 111 x 69 cm</td>\n",
       "      <td>1480-85</td>\n",
       "      <td>religious</td>\n",
       "      <td>Netherlandish</td>\n",
       "      <td>1451-1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36582-paolo_f1.jpg</td>\n",
       "      <td>The painting illustrates a famous episode from...</td>\n",
       "      <td>SCHEFFER, Ary</td>\n",
       "      <td>The Ghosts of Paolo and Francesca Appear to Da...</td>\n",
       "      <td>Oil on canvas, 167 x 234 cm</td>\n",
       "      <td>1835</td>\n",
       "      <td>other</td>\n",
       "      <td>Dutch</td>\n",
       "      <td>1801-1850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>00930-30coron.jpg</td>\n",
       "      <td>Fra Angelico dedicated several of his works to...</td>\n",
       "      <td>ANGELICO, Fra</td>\n",
       "      <td>The Coronation of the Virgin</td>\n",
       "      <td>Tempera on wood, 112 x 114 cm</td>\n",
       "      <td>1434-35</td>\n",
       "      <td>religious</td>\n",
       "      <td>Italian</td>\n",
       "      <td>1401-1450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24485-01view.jpg</td>\n",
       "      <td>The Bardi di Vernio Chapel in Santa Croce is o...</td>\n",
       "      <td>MASO DI BANCO</td>\n",
       "      <td>View of the Bardi di Vernio Chapel from the So...</td>\n",
       "      <td>Fresco</td>\n",
       "      <td>c. 1335</td>\n",
       "      <td>religious</td>\n",
       "      <td>Italian</td>\n",
       "      <td>1301-1350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>18424-2105grec.jpg</td>\n",
       "      <td>This portrait is an example of El Greco's late...</td>\n",
       "      <td>GRECO, El</td>\n",
       "      <td>Portrait of Cardinal Tavera</td>\n",
       "      <td>Oil on canvas, 103 x 83 cm</td>\n",
       "      <td>1608-14</td>\n",
       "      <td>portrait</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>1551-1600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>21949-4lastsu3.jpg</td>\n",
       "      <td>Leonardo's painting of the Last Supper was con...</td>\n",
       "      <td>LEONARDO da Vinci</td>\n",
       "      <td>The Last Supper</td>\n",
       "      <td>Mixed technique, 460 x 880 cm</td>\n",
       "      <td>1498</td>\n",
       "      <td>religious</td>\n",
       "      <td>Italian</td>\n",
       "      <td>1451-1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>03093-calvary.jpg</td>\n",
       "      <td>Traditionally the painting was ascribed to Pie...</td>\n",
       "      <td>BALTENS, Peeter</td>\n",
       "      <td>Christ on the Road to Calvary</td>\n",
       "      <td>Oil on oak panel, 116 x 156 cm</td>\n",
       "      <td>1560s</td>\n",
       "      <td>religious</td>\n",
       "      <td>Flemish</td>\n",
       "      <td>1501-1550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           IMAGE_FILE                                        DESCRIPTION  \\\n",
       "0   19873-1darmst.jpg  The Meyer or Darmstadt Madonna is the last, mo...   \n",
       "1  18759-guard301.jpg  Whereas Canaletto incorporates classically ins...   \n",
       "2  04589-temptati.jpg  In this painting the refinement of the colouri...   \n",
       "3      15104-magi.jpg  The left side of the painting was cut, origina...   \n",
       "4  36582-paolo_f1.jpg  The painting illustrates a famous episode from...   \n",
       "5   00930-30coron.jpg  Fra Angelico dedicated several of his works to...   \n",
       "6    24485-01view.jpg  The Bardi di Vernio Chapel in Santa Croce is o...   \n",
       "7  18424-2105grec.jpg  This portrait is an example of El Greco's late...   \n",
       "8  21949-4lastsu3.jpg  Leonardo's painting of the Last Supper was con...   \n",
       "9   03093-calvary.jpg  Traditionally the painting was ascribed to Pie...   \n",
       "\n",
       "                      AUTHOR  \\\n",
       "0  HOLBEIN, Hans the Younger   \n",
       "1          GUARDI, Francesco   \n",
       "2         BILIVERT, Giovanni   \n",
       "3     GEERTGEN tot Sint Jans   \n",
       "4              SCHEFFER, Ary   \n",
       "5              ANGELICO, Fra   \n",
       "6              MASO DI BANCO   \n",
       "7                  GRECO, El   \n",
       "8          LEONARDO da Vinci   \n",
       "9            BALTENS, Peeter   \n",
       "\n",
       "                                               TITLE  \\\n",
       "0                                  Darmstadt Madonna   \n",
       "1                  Landscape with a Fisherman's Tent   \n",
       "2               The Temptation of Charles and Ubalde   \n",
       "3                              Adoration of the Magi   \n",
       "4  The Ghosts of Paolo and Francesca Appear to Da...   \n",
       "5                       The Coronation of the Virgin   \n",
       "6  View of the Bardi di Vernio Chapel from the So...   \n",
       "7                        Portrait of Cardinal Tavera   \n",
       "8                                    The Last Supper   \n",
       "9                      Christ on the Road to Calvary   \n",
       "\n",
       "                        TECHNIQUE                 DATE       TYPE  \\\n",
       "0   Oil on limewood, 147 x 102 cm  1526 and after 1528  religious   \n",
       "1       Oil on canvas, 49 x 77 cm              1770-75  landscape   \n",
       "2       Oil on copper, 37 x 28 cm              1629-30  religious   \n",
       "3              Panel, 111 x 69 cm              1480-85  religious   \n",
       "4     Oil on canvas, 167 x 234 cm                 1835      other   \n",
       "5   Tempera on wood, 112 x 114 cm              1434-35  religious   \n",
       "6                          Fresco              c. 1335  religious   \n",
       "7      Oil on canvas, 103 x 83 cm              1608-14   portrait   \n",
       "8   Mixed technique, 460 x 880 cm                 1498  religious   \n",
       "9  Oil on oak panel, 116 x 156 cm                1560s  religious   \n",
       "\n",
       "          SCHOOL  TIMEFRAME  \n",
       "0         German  1501-1550  \n",
       "1        Italian  1751-1800  \n",
       "2        Italian  1601-1650  \n",
       "3  Netherlandish  1451-1500  \n",
       "4          Dutch  1801-1850  \n",
       "5        Italian  1401-1450  \n",
       "6        Italian  1301-1350  \n",
       "7        Spanish  1551-1600  \n",
       "8        Italian  1451-1500  \n",
       "9        Flemish  1501-1550  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 21382 entries, 0 to 1068\n",
      "Data columns (total 9 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   IMAGE_FILE   21382 non-null  object\n",
      " 1   DESCRIPTION  21382 non-null  object\n",
      " 2   AUTHOR       21382 non-null  object\n",
      " 3   TITLE        21382 non-null  object\n",
      " 4   TECHNIQUE    21382 non-null  object\n",
      " 5   DATE         21382 non-null  object\n",
      " 6   TYPE         21382 non-null  object\n",
      " 7   SCHOOL       21382 non-null  object\n",
      " 8   TIMEFRAME    21382 non-null  object\n",
      "dtypes: object(9)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe shape: (21382, 9)\n"
     ]
    }
   ],
   "source": [
    "print(f\"Dataframe shape: {data.shape}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Initially, make a copy of the original data and eliminate any `NaN` values and duplicate image files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 21382 entries, 0 to 1068\n",
      "Data columns (total 9 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   IMAGE_FILE   21382 non-null  object\n",
      " 1   DESCRIPTION  21382 non-null  object\n",
      " 2   AUTHOR       21382 non-null  object\n",
      " 3   TITLE        21382 non-null  object\n",
      " 4   TECHNIQUE    21382 non-null  object\n",
      " 5   DATE         21382 non-null  object\n",
      " 6   TYPE         21382 non-null  object\n",
      " 7   SCHOOL       21382 non-null  object\n",
      " 8   TIMEFRAME    21382 non-null  object\n",
      "dtypes: object(9)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df = data.dropna()\n",
    "df.drop_duplicates(subset=[\"IMAGE_FILE\"], inplace=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the `DATE` and `SCHOOL` information won't be used in the image similarity search app, you can remove them from the DataFrame."
   ]
  },
  {
   "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>IMAGE_FILE</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>AUTHOR</th>\n",
       "      <th>TITLE</th>\n",
       "      <th>TECHNIQUE</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>TIMEFRAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19873-1darmst.jpg</td>\n",
       "      <td>The Meyer or Darmstadt Madonna is the last, mo...</td>\n",
       "      <td>HOLBEIN, Hans the Younger</td>\n",
       "      <td>Darmstadt Madonna</td>\n",
       "      <td>Oil on limewood, 147 x 102 cm</td>\n",
       "      <td>religious</td>\n",
       "      <td>1501-1550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18759-guard301.jpg</td>\n",
       "      <td>Whereas Canaletto incorporates classically ins...</td>\n",
       "      <td>GUARDI, Francesco</td>\n",
       "      <td>Landscape with a Fisherman's Tent</td>\n",
       "      <td>Oil on canvas, 49 x 77 cm</td>\n",
       "      <td>landscape</td>\n",
       "      <td>1751-1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04589-temptati.jpg</td>\n",
       "      <td>In this painting the refinement of the colouri...</td>\n",
       "      <td>BILIVERT, Giovanni</td>\n",
       "      <td>The Temptation of Charles and Ubalde</td>\n",
       "      <td>Oil on copper, 37 x 28 cm</td>\n",
       "      <td>religious</td>\n",
       "      <td>1601-1650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15104-magi.jpg</td>\n",
       "      <td>The left side of the painting was cut, origina...</td>\n",
       "      <td>GEERTGEN tot Sint Jans</td>\n",
       "      <td>Adoration of the Magi</td>\n",
       "      <td>Panel, 111 x 69 cm</td>\n",
       "      <td>religious</td>\n",
       "      <td>1451-1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36582-paolo_f1.jpg</td>\n",
       "      <td>The painting illustrates a famous episode from...</td>\n",
       "      <td>SCHEFFER, Ary</td>\n",
       "      <td>The Ghosts of Paolo and Francesca Appear to Da...</td>\n",
       "      <td>Oil on canvas, 167 x 234 cm</td>\n",
       "      <td>other</td>\n",
       "      <td>1801-1850</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           IMAGE_FILE                                        DESCRIPTION  \\\n",
       "0   19873-1darmst.jpg  The Meyer or Darmstadt Madonna is the last, mo...   \n",
       "1  18759-guard301.jpg  Whereas Canaletto incorporates classically ins...   \n",
       "2  04589-temptati.jpg  In this painting the refinement of the colouri...   \n",
       "3      15104-magi.jpg  The left side of the painting was cut, origina...   \n",
       "4  36582-paolo_f1.jpg  The painting illustrates a famous episode from...   \n",
       "\n",
       "                      AUTHOR  \\\n",
       "0  HOLBEIN, Hans the Younger   \n",
       "1          GUARDI, Francesco   \n",
       "2         BILIVERT, Giovanni   \n",
       "3     GEERTGEN tot Sint Jans   \n",
       "4              SCHEFFER, Ary   \n",
       "\n",
       "                                               TITLE  \\\n",
       "0                                  Darmstadt Madonna   \n",
       "1                  Landscape with a Fisherman's Tent   \n",
       "2               The Temptation of Charles and Ubalde   \n",
       "3                              Adoration of the Magi   \n",
       "4  The Ghosts of Paolo and Francesca Appear to Da...   \n",
       "\n",
       "                       TECHNIQUE       TYPE  TIMEFRAME  \n",
       "0  Oil on limewood, 147 x 102 cm  religious  1501-1550  \n",
       "1      Oil on canvas, 49 x 77 cm  landscape  1751-1800  \n",
       "2      Oil on copper, 37 x 28 cm  religious  1601-1650  \n",
       "3             Panel, 111 x 69 cm  religious  1451-1500  \n",
       "4    Oil on canvas, 167 x 234 cm      other  1801-1850  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop(columns=[\"DATE\", \"SCHOOL\"])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert column names to lowercase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>image_file</th>\n",
       "      <th>description</th>\n",
       "      <th>author</th>\n",
       "      <th>title</th>\n",
       "      <th>technique</th>\n",
       "      <th>type</th>\n",
       "      <th>timeframe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19873-1darmst.jpg</td>\n",
       "      <td>The Meyer or Darmstadt Madonna is the last, mo...</td>\n",
       "      <td>HOLBEIN, Hans the Younger</td>\n",
       "      <td>Darmstadt Madonna</td>\n",
       "      <td>Oil on limewood, 147 x 102 cm</td>\n",
       "      <td>religious</td>\n",
       "      <td>1501-1550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18759-guard301.jpg</td>\n",
       "      <td>Whereas Canaletto incorporates classically ins...</td>\n",
       "      <td>GUARDI, Francesco</td>\n",
       "      <td>Landscape with a Fisherman's Tent</td>\n",
       "      <td>Oil on canvas, 49 x 77 cm</td>\n",
       "      <td>landscape</td>\n",
       "      <td>1751-1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04589-temptati.jpg</td>\n",
       "      <td>In this painting the refinement of the colouri...</td>\n",
       "      <td>BILIVERT, Giovanni</td>\n",
       "      <td>The Temptation of Charles and Ubalde</td>\n",
       "      <td>Oil on copper, 37 x 28 cm</td>\n",
       "      <td>religious</td>\n",
       "      <td>1601-1650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15104-magi.jpg</td>\n",
       "      <td>The left side of the painting was cut, origina...</td>\n",
       "      <td>GEERTGEN tot Sint Jans</td>\n",
       "      <td>Adoration of the Magi</td>\n",
       "      <td>Panel, 111 x 69 cm</td>\n",
       "      <td>religious</td>\n",
       "      <td>1451-1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36582-paolo_f1.jpg</td>\n",
       "      <td>The painting illustrates a famous episode from...</td>\n",
       "      <td>SCHEFFER, Ary</td>\n",
       "      <td>The Ghosts of Paolo and Francesca Appear to Da...</td>\n",
       "      <td>Oil on canvas, 167 x 234 cm</td>\n",
       "      <td>other</td>\n",
       "      <td>1801-1850</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           image_file                                        description  \\\n",
       "0   19873-1darmst.jpg  The Meyer or Darmstadt Madonna is the last, mo...   \n",
       "1  18759-guard301.jpg  Whereas Canaletto incorporates classically ins...   \n",
       "2  04589-temptati.jpg  In this painting the refinement of the colouri...   \n",
       "3      15104-magi.jpg  The left side of the painting was cut, origina...   \n",
       "4  36582-paolo_f1.jpg  The painting illustrates a famous episode from...   \n",
       "\n",
       "                      author  \\\n",
       "0  HOLBEIN, Hans the Younger   \n",
       "1          GUARDI, Francesco   \n",
       "2         BILIVERT, Giovanni   \n",
       "3     GEERTGEN tot Sint Jans   \n",
       "4              SCHEFFER, Ary   \n",
       "\n",
       "                                               title  \\\n",
       "0                                  Darmstadt Madonna   \n",
       "1                  Landscape with a Fisherman's Tent   \n",
       "2               The Temptation of Charles and Ubalde   \n",
       "3                              Adoration of the Magi   \n",
       "4  The Ghosts of Paolo and Francesca Appear to Da...   \n",
       "\n",
       "                       technique       type  timeframe  \n",
       "0  Oil on limewood, 147 x 102 cm  religious  1501-1550  \n",
       "1      Oil on canvas, 49 x 77 cm  landscape  1751-1800  \n",
       "2      Oil on copper, 37 x 28 cm  religious  1601-1650  \n",
       "3             Panel, 111 x 69 cm  religious  1451-1500  \n",
       "4    Oil on canvas, 167 x 234 cm      other  1801-1850  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns = map(str.lower, df.columns)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transliterate non-ASCII characters\n",
    "\n",
    "The dataset includes various non-ASCII characters like *ú*, *é*, *î*, and *ä*. In this section, the aim is to convert these non-ASCII characters to their ASCII equivalents, minimizing potential issues associated with character encoding.\n",
    "\n",
    "The `find_non_ascii_characters` function returns a list of all non-ASCII characters present in the dataset. You will then use the `unidecode` function to convert these special characters into their ASCII representation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_non_ascii_characters(dataframe):\n",
    "    non_ascii_characters = set()\n",
    "\n",
    "    # Iterate through each cell in the dataframe\n",
    "    for col in dataframe.columns:\n",
    "        for cell in dataframe[col].values:\n",
    "            # Check for non-ASCII characters and add them to the set\n",
    "            non_ascii_characters.update(char for char in cell if ord(char) > 127)\n",
    "\n",
    "    return list(non_ascii_characters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "List of non-ASCII characters: ['’', 'Á', 'Ç', 'â', 'Ú', 'é', 'í', 'Ä', 'Ô', 'õ', 'Ö', 'ë', 'ü', 'É', 'û', 'ž', 'ç', 'ú', 'Ë', 'Ü', 'ó', 'ô', 'è', 'Ó', 'ö', 'î', 'Í', 'Â', 'á', 'ä', '×']\n"
     ]
    }
   ],
   "source": [
    "non_ascii_characters_list = find_non_ascii_characters(df)\n",
    "print(\"List of non-ASCII characters:\", non_ascii_characters_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.map(lambda x: unidecode(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "List of non-ASCII characters: []\n"
     ]
    }
   ],
   "source": [
    "non_ascii_characters_list = find_non_ascii_characters(df)\n",
    "print(\"List of non-ASCII characters:\", non_ascii_characters_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exclude the painting size information from the `technique` column\n",
    "\n",
    "Let's remove the painting size information from the `technique` column since it won't be utilized in our app."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>image_file</th>\n",
       "      <th>description</th>\n",
       "      <th>author</th>\n",
       "      <th>title</th>\n",
       "      <th>technique</th>\n",
       "      <th>type</th>\n",
       "      <th>timeframe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19873-1darmst.jpg</td>\n",
       "      <td>The Meyer or Darmstadt Madonna is the last, mo...</td>\n",
       "      <td>HOLBEIN, Hans the Younger</td>\n",
       "      <td>Darmstadt Madonna</td>\n",
       "      <td>Oil on limewood</td>\n",
       "      <td>religious</td>\n",
       "      <td>1501-1550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18759-guard301.jpg</td>\n",
       "      <td>Whereas Canaletto incorporates classically ins...</td>\n",
       "      <td>GUARDI, Francesco</td>\n",
       "      <td>Landscape with a Fisherman's Tent</td>\n",
       "      <td>Oil on canvas</td>\n",
       "      <td>landscape</td>\n",
       "      <td>1751-1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>04589-temptati.jpg</td>\n",
       "      <td>In this painting the refinement of the colouri...</td>\n",
       "      <td>BILIVERT, Giovanni</td>\n",
       "      <td>The Temptation of Charles and Ubalde</td>\n",
       "      <td>Oil on copper</td>\n",
       "      <td>religious</td>\n",
       "      <td>1601-1650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15104-magi.jpg</td>\n",
       "      <td>The left side of the painting was cut, origina...</td>\n",
       "      <td>GEERTGEN tot Sint Jans</td>\n",
       "      <td>Adoration of the Magi</td>\n",
       "      <td>Panel</td>\n",
       "      <td>religious</td>\n",
       "      <td>1451-1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36582-paolo_f1.jpg</td>\n",
       "      <td>The painting illustrates a famous episode from...</td>\n",
       "      <td>SCHEFFER, Ary</td>\n",
       "      <td>The Ghosts of Paolo and Francesca Appear to Da...</td>\n",
       "      <td>Oil on canvas</td>\n",
       "      <td>other</td>\n",
       "      <td>1801-1850</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           image_file                                        description  \\\n",
       "0   19873-1darmst.jpg  The Meyer or Darmstadt Madonna is the last, mo...   \n",
       "1  18759-guard301.jpg  Whereas Canaletto incorporates classically ins...   \n",
       "2  04589-temptati.jpg  In this painting the refinement of the colouri...   \n",
       "3      15104-magi.jpg  The left side of the painting was cut, origina...   \n",
       "4  36582-paolo_f1.jpg  The painting illustrates a famous episode from...   \n",
       "\n",
       "                      author  \\\n",
       "0  HOLBEIN, Hans the Younger   \n",
       "1          GUARDI, Francesco   \n",
       "2         BILIVERT, Giovanni   \n",
       "3     GEERTGEN tot Sint Jans   \n",
       "4              SCHEFFER, Ary   \n",
       "\n",
       "                                               title        technique  \\\n",
       "0                                  Darmstadt Madonna  Oil on limewood   \n",
       "1                  Landscape with a Fisherman's Tent    Oil on canvas   \n",
       "2               The Temptation of Charles and Ubalde    Oil on copper   \n",
       "3                              Adoration of the Magi            Panel   \n",
       "4  The Ghosts of Paolo and Francesca Appear to Da...    Oil on canvas   \n",
       "\n",
       "        type  timeframe  \n",
       "0  religious  1501-1550  \n",
       "1  landscape  1751-1800  \n",
       "2  religious  1601-1650  \n",
       "3  religious  1451-1500  \n",
       "4      other  1801-1850  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"technique\"] = df[\"technique\"].apply(lambda x: x.split(sep=\", \")[0])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Specify the data type of each column as `string`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 21382 entries, 0 to 1068\n",
      "Data columns (total 7 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   image_file   21382 non-null  string\n",
      " 1   description  21382 non-null  string\n",
      " 2   author       21382 non-null  string\n",
      " 3   title        21382 non-null  string\n",
      " 4   technique    21382 non-null  string\n",
      " 5   type         21382 non-null  string\n",
      " 6   timeframe    21382 non-null  string\n",
      "dtypes: string(7)\n",
      "memory usage: 1.3 MB\n"
     ]
    }
   ],
   "source": [
    "df = df.convert_dtypes()\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exclude artists who have fewer than 15 paintings from the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "author\n",
       "GOGH, Vincent van                 327\n",
       "REMBRANDT Harmenszoon van Rijn    262\n",
       "GIOTTO di Bondone                 249\n",
       "RUBENS, Peter Paul                213\n",
       "TIZIANO Vecellio                  205\n",
       "                                 ... \n",
       "LE GROS, Pierre the Younger         1\n",
       "LAWSON, Ernst                       1\n",
       "LAURETI, Tommaso                    1\n",
       "LAURENS, Jean-Paul                  1\n",
       "ZURBARAN, Juan de                   1\n",
       "Name: count, Length: 3281, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.value_counts(\"author\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 11631 entries, 0 to 1067\n",
      "Data columns (total 7 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   image_file   11631 non-null  string\n",
      " 1   description  11631 non-null  string\n",
      " 2   author       11631 non-null  string\n",
      " 3   title        11631 non-null  string\n",
      " 4   technique    11631 non-null  string\n",
      " 5   type         11631 non-null  string\n",
      " 6   timeframe    11631 non-null  string\n",
      "dtypes: string(7)\n",
      "memory usage: 726.9 KB\n"
     ]
    }
   ],
   "source": [
    "MIN_NUMBER_OF_PAINTINGS_PER_ARTIST = 15\n",
    "df = df[df.groupby(\"author\").author.transform(\"count\") > MIN_NUMBER_OF_PAINTINGS_PER_ARTIST]\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Address any remaining issues in the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Refine the names of the artists in the dataset\n",
    "\n",
    "Several authors' names contain a *?* in place of non-ASCII characters. The `find_questionmarks_in_author_name` function provides a list of these incorrect artist names. Subsequently, you will manually correct these names in the dataset, considering that the *?* represents a different character in each case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_questionmarks_in_author_name(dataframe):\n",
    "    authors = set()\n",
    "    \n",
    "    # Iterate through each cell in the dataframe\n",
    "    for cell in dataframe[\"author\"].values:\n",
    "        # Check for ? and add the name to the set\n",
    "        if any([True for char in cell if char == \"?\"]):\n",
    "            authors.add(cell)\n",
    "    \n",
    "    return list(authors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of authors with incorrect decoded name: 2\n",
      "TURA, Cosm?\n",
      "DELACROIX, Eug?ne\n"
     ]
    }
   ],
   "source": [
    "authors = find_questionmarks_in_author_name(df)\n",
    "print(f\"Number of authors with incorrect decoded name: {len(authors)}\")\n",
    "for author in authors:\n",
    "    print(author)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "REPLACEMENTS = [\n",
    "    (\"TURA, Cosm?\", \"TURA, Cosme\"),\n",
    "    (\"DELACROIX, Eug?ne\", \"DELACROIX, Eugene\"),\n",
    "]\n",
    "for incorrect_name, correct_name in REPLACEMENTS:\n",
    "    df[\"author\"] = df[\"author\"].apply(lambda x: x.replace(incorrect_name, correct_name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exclude Unknown Authors\n",
    "\n",
    "For paintings where the artist is unidentified, the artist's name is presented as: *UNKNOWN MASTER, \\<SCHOOL\\>*. All entries with this information will be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[~df[\"author\"].str.contains(\"UNKNOWN\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save the final dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at some data for the revised dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 11206 entries, 0 to 1067\n",
      "Data columns (total 7 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   image_file   11206 non-null  string\n",
      " 1   description  11206 non-null  string\n",
      " 2   author       11206 non-null  object\n",
      " 3   title        11206 non-null  string\n",
      " 4   technique    11206 non-null  string\n",
      " 5   type         11206 non-null  string\n",
      " 6   timeframe    11206 non-null  string\n",
      "dtypes: object(1), string(6)\n",
      "memory usage: 700.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "image_file     11206\n",
       "description    10503\n",
       "author           250\n",
       "title           8238\n",
       "technique        339\n",
       "type              10\n",
       "timeframe         21\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save the clean dataset as a `csv` file in the *dataset* drectory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_dataset_folder = os.path.join(\"..\", \"dataset\")\n",
    "dataset_csv_filename = \"dataset.csv\"\n",
    "\n",
    "os.makedirs(final_dataset_folder, exist_ok=True)  \n",
    "df.to_csv(\n",
    "    os.path.join(final_dataset_folder, dataset_csv_filename),\n",
    "    index=False,\n",
    "    sep=\"\\t\",\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}