data/tables_to_data_v0_4_minimal.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "61810d34",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import os\n",
    "from tqdm import tqdm\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from datetime import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a0617b0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "tblpath = \"./tables/\"\n",
    "k = 1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "85d638b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def iso_date_to_int(date_col):\n",
    "    d2int = lambda s: int(s.replace(\"1000000\", \"\").replace(\".0\", \"\")[:4])\n",
    "    return date_col.fillna(0).apply(lambda x: d2int(str(x)))\n",
    "\n",
    "def replaceNI(row):\n",
    "    return f\"{row.BeginISODate} - {row.EndISODate}\" if row.Dated == \"[NI]\" else row.Dated\n",
    "\n",
    "def to_date_str(year_int):\n",
    "    return dt.strptime(str(year_int).zfill(4),\n",
    "                      \"%Y\").date()\n",
    "\n",
    "def dept_id_to_int(id_col):\n",
    "    for x in id_col:\n",
    "        try:\n",
    "            yield int(x)\n",
    "        except ValueError:\n",
    "            yield -10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f5cb047",
   "metadata": {},
   "outputs": [],
   "source": [
    "pbar = tqdm(total=4)\n",
    "\n",
    "# LOAD TABLES\n",
    "id_fields = [\"DepartmentID\", \"ClassificationID\", \"ObjectName\", # this is the type of object\n",
    "             \"Medium\", \"Provenance\", \"Exhibitions\"]\n",
    "date_fields = [\"BeginISODate\", \"EndISODate\", \"Dated\"]\n",
    "txt_fields = [\"Title\", \"Description\", \"Notes\", \"CuratorialRemarks\", \"RelatedWorks\", \"HistAttributions\"]\n",
    "relevant_fields = id_fields + date_fields + txt_fields\n",
    "objs = pd.read_csv(tblpath + f\"Objects_{k}.csv.gz\").set_index(\"ObjectID\")[relevant_fields]\n",
    "\n",
    "cls_fields = [\"ClassificationID\",\"Classification\", \"AATCN\", \"SubClassification\"] #, \"SubClassification2\"] -> always the same as AATCN when present\n",
    "clsss = pd.read_csv(tblpath + \"Classifications.csv.gz\")[cls_fields].set_index(\"ClassificationID\")\n",
    "\n",
    "dept_fields = [\"DepartmentID\", \"Department\"]\n",
    "depts = pd.read_csv(tblpath + \"Departments.csv.gz\").drop(\"GSRowVersion\", axis=\"columns\").drop_duplicates(keep=\"first\").set_index(\"DepartmentID\")\n",
    "\n",
    "pbar.update(1)\n",
    "\n",
    "### DATES\n",
    "new_begin_dates = iso_date_to_int(objs.BeginISODate)\n",
    "new_end_dates = iso_date_to_int(objs.EndISODate)\n",
    "\n",
    "objs[\"BeginISODate\"] = new_begin_dates\n",
    "objs[\"EndISODate\"] = new_end_dates\n",
    "new_dated = objs.apply(replaceNI, axis=1)\n",
    "objs[\"Dated\"] = new_dated\n",
    "\n",
    "objs = objs[objs.BeginISODate < objs.EndISODate]\n",
    "objs = objs[(objs.BeginISODate + objs.EndISODate) > 0]\n",
    "objs = objs[objs.BeginISODate < 2022]\n",
    "objs = objs[objs.EndISODate < 2022]\n",
    "# highly aggressive\n",
    "objs = objs[objs.BeginISODate > 0] ### !\n",
    "objs = objs[objs.EndISODate > 0] ### !\n",
    "\n",
    "objs[\"BeginISODate\"] = objs.BeginISODate.apply(to_date_str)\n",
    "objs[\"EndISODate\"] = objs.EndISODate.apply(to_date_str)\n",
    "\n",
    "pbar.update(1)\n",
    "\n",
    "### CLASSIFICATIONS\n",
    "objs = objs.join(clsss.drop_duplicates(keep=\"last\"), how=\"left\", on=\"ClassificationID\")\n",
    "\n",
    "pbar.update(1)\n",
    "\n",
    "### DEPARTMENTS\n",
    "objs[\"DepartmentID\"] = list(dept_id_to_int(objs.DepartmentID))\n",
    "objs = objs[objs.DepartmentID > -1]\n",
    "objs[\"Department\"] = list(depts.loc[objs.DepartmentID].Department)\n",
    "\n",
    "pbar.update(1)\n",
    "\n",
    "\n",
    "### FILTER OBJECTS\n",
    "objs = objs.drop_duplicates()\n",
    "\n",
    "\n",
    "### RENAME COLUMNS\n",
    "objs = objs.reset_index().rename(columns=dict(\n",
    "        Title=\"name\",\n",
    "        Description=\"description\",\n",
    "        ObjectID=\"ID\",\n",
    "        BeginISODate=\"start_date\",\n",
    "        EndISODate=\"end_date\",\n",
    "        Dated=\"date_string\"\n",
    "    )\n",
    ").set_index(\"ID\")\n",
    "objs.index = objs.index.astype(\"int\")\n",
    "\n",
    "objs[\"source_url\"] = \"https://hdl.handle.net/20.500.11840/\" + objs.index.astype(str)\n",
    "\n",
    "objs.to_csv(\"NMvW.v0_4.csv.gz\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d5e0982",
   "metadata": {},
   "source": [
    "---\n",
    "# Test Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b7235eed",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"NMvW.v0_4.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f24b3888",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                     int64\n",
       "DepartmentID           int64\n",
       "ClassificationID     float64\n",
       "ObjectName            object\n",
       "Medium                object\n",
       "Provenance            object\n",
       "Exhibitions           object\n",
       "start_date            object\n",
       "end_date              object\n",
       "date_string           object\n",
       "name                  object\n",
       "description           object\n",
       "Notes                 object\n",
       "CuratorialRemarks     object\n",
       "RelatedWorks          object\n",
       "HistAttributions      object\n",
       "Classification        object\n",
       "AATCN                 object\n",
       "SubClassification     object\n",
       "Department            object\n",
       "source_url            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c1f21ea7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/valentin/.local/lib/python3.10/site-packages/IPython/core/interactiveshell.py:3457: DtypeWarning: Columns (0,3,4,8,9,10,11,15,17,18,19,20,21,22,24,25,26,27,29,31,32,34,38,39,42,43,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,66,67,68,69,71,79,80) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "id_fields = [\"DepartmentID\", \"ClassificationID\", \"ObjectName\", # this is the type of object\n",
    "             \"Medium\", \"Provenance\", \"Exhibitions\"]\n",
    "date_fields = [\"BeginISODate\", \"EndISODate\", \"Dated\"]\n",
    "txt_fields = [\"Title\", \"Description\", \"Notes\", \"CuratorialRemarks\", \"RelatedWorks\", \"HistAttributions\"]\n",
    "relevant_fields = id_fields + date_fields + txt_fields\n",
    "objs = pd.read_csv(tblpath + f\"Objects_{k}.csv.gz\").set_index(\"ObjectID\")[relevant_fields]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f823d4e5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([      1,       2,       3,       4,       5,       6,       7,       8,\n",
       "             9,      10,\n",
       "       ...\n",
       "       1140711, 1140712, 1140713, 1140714, 1140715, 1140716, 1140717, 1140718,\n",
       "       1140719, 1140720],\n",
       "      dtype='object', name='ObjectID', length=872745)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "objs.index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0053ac8a",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5d742149",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"NMvW.v0_4.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "af4dc58f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID', 'DepartmentID', 'ClassificationID', 'ObjectName', 'Medium',\n",
       "       'Provenance', 'Exhibitions', 'start_date', 'end_date', 'date_string',\n",
       "       'name', 'description', 'Notes', 'CuratorialRemarks', 'RelatedWorks',\n",
       "       'HistAttributions', 'Classification', 'AATCN', 'SubClassification',\n",
       "       'Department', 'source_url'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "78105cfc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "53        Is gedateerd voor/before 1963 (omdat het genum...\n",
       "54        Is gedateerd voor/before 1963 (omdat het genum...\n",
       "74        Geruild tegen 29 voorwerpen van het Museum für...\n",
       "109       Bij de voorbereidingen van de tentoonstelling ...\n",
       "111                                      Herkomst onbekend.\n",
       "                                ...                        \n",
       "146566    Bruikleen 23-02-1914 Payeti *[nT], Sumba.\\r\\nD...\n",
       "146567    Bruikleen 23-02-1914 Payeti *[nT], Sumba.\\r\\nD...\n",
       "146573    Door dr. Büttikofer intertijd meegebracht van ...\n",
       "146574    Door dr. Büttikofer intertijd meegebracht van ...\n",
       "146690    verworven van of uit nalatenschap van J.J. van...\n",
       "Name: Provenance, Length: 20346, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Provenance[~df.Provenance.isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8a87b62b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24644720"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage().sum()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}