STRO20_4_Images_Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7845c965",
   "metadata": {},
   "source": [
    "Dataframes based on CSV files images.csv, secties_totaal.csv and register_totaal.csv (all downloaded from www.soundtoll.nl) are created. <br>\n",
    "Columns are selected and column names are translated. <br>\n",
    "The notebook explores the data and reviews the proposed method for linking register, sections and image data, and identifies errors and inconsistencies.<br>\n",
    "The method adds a 'reg_id' and 'section_id' derived from register_totaal.csv and secties_totaal.csv to the images data. <br>\n",
    "The method uses the information about microfilm numbers and scan numbers for registers and sections to add the corresponding reg_id and section_id to the images table. <br>\n",
    "The result of the notebook are csv files that will be used to solve inconsistencies and errors in the data before implementing the method. <br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0133cf09",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6c14b90-3de2-4e5e-a768-5a567b6ef163",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the file registers.csv, sections.csv and images.csv from the legacy data downloaded previously (See Jupyter Notebook STRO20_0_sparseDensity_STRO10.ipynb).\n",
    "# If you have not downloaded the file before, you can find it here: https://doi.org/10.6084/m9.figshare.27221169.v2\n",
    "registers = r\"INSERT_FILEPATH\"\n",
    "sections = r\"INSERT_FILEPATH\"\n",
    "images = r\"INSERT_FILEPATH\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b520a68-1112-4b25-a5b4-dabf3a2021d7",
   "metadata": {},
   "source": [
    "# 1. Preparation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79a9d46f-d29d-4ba1-ac87-c19d45b8f630",
   "metadata": {},
   "outputs": [],
   "source": [
    "# retrieve files for preparing entity creation from the STRO 2.0 GitHub repository\n",
    "owner = 'dhofu'\n",
    "repo = 'stro20'\n",
    "column_selection = 'STRO_20_column_selection'\n",
    "rename_columns = 'STRO_20_column_names'\n",
    "url_selection = f\"https://api.github.com/repos/{owner}/{repo}/contents/{column_selection}\"\n",
    "url_rename = f\"https://api.github.com/repos/{owner}/{repo}/contents/{rename_columns}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c836c176-0ba9-45f1-ab76-fdaa5eb182ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "response_column_selection = requests.get(url_selection)\n",
    "response_rename_columns = requests.get(url_rename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "477786a7-e9bc-4960-8198-f20e6d7d1a78",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fetch the files for selecting columns from the 'registers', 'sections' and 'images' dataframes\n",
    "selection_mappings = {\n",
    "    'usecols_registers': None,\n",
    "    'usecols_sections': None,\n",
    "    'usecols_images': None\n",
    "}\n",
    "for file in response_column_selection.json():\n",
    "    if file['name'].startswith('registers_'):\n",
    "        key1 = file['name'].split('_', 1)[1].split('.', 1)[0]\n",
    "        selection_mappings[key1] = requests.get(file['download_url']).json()\n",
    "    if file['name'].startswith('sections_'):\n",
    "        key2 = file['name'].split('_', 1)[1].split('.', 1)[0]\n",
    "        selection_mappings[key2] = requests.get(file['download_url']).json()\n",
    "    if file['name'].startswith('images_'):\n",
    "        key3 = file['name'].split('_', 1)[1].split('.', 1)[0]\n",
    "        selection_mappings[key3] = requests.get(file['download_url']).json()\n",
    "\n",
    "usecols_registers = selection_mappings['usecols_registers']\n",
    "usecols_sections = selection_mappings['usecols_sections']\n",
    "usecols_images = selection_mappings['usecols_images']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45f0fe81-8e2d-4d10-85a9-dabfc14ce0b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fetch the files for renaming columns from the 'registers', 'sections' and 'images' dataframes \n",
    "rename_mappings = {\n",
    "    'rename_registers': None,\n",
    "    'rename_sections': None,\n",
    "    'rename_images': None\n",
    "}\n",
    "\n",
    "for file in response_rename_columns.json():\n",
    "    if file['name'].startswith('registers_'):\n",
    "        key1 = file['name'].split('_', 1)[1].split('.', 1)[0]\n",
    "        rename_mappings[key1] = requests.get(file['download_url']).json()\n",
    "    if file['name'].startswith('sections_'):\n",
    "        key2 = file['name'].split('_', 1)[1].split('.', 1)[0]\n",
    "        rename_mappings[key2] = requests.get(file['download_url']).json()\n",
    "    if file['name'].startswith('images_'):\n",
    "        key3 = file['name'].split('_', 1)[1].split('.', 1)[0]\n",
    "        rename_mappings[key3] = requests.get(file['download_url']).json()\n",
    "\n",
    "rename_registers = rename_mappings['rename_registers']\n",
    "rename_sections = rename_mappings['rename_sections']\n",
    "rename_images = rename_mappings['rename_images']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a803de4-cbf6-4d22-bda9-e4b7407c66f8",
   "metadata": {},
   "source": [
    "## create registers dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27cb04a0-281f-4220-a5a2-f14971b57f7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the registers dataframe from registers_totaal.csv\n",
    "df_registers = pd.read_csv(registers, sep=\",\", quotechar='\"', \\\n",
    "                         usecols=usecols_registers, header=0, encoding=\"utf-8\").rename(columns=rename_registers).reset_index(names=\"reg_id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d37c2df2-158c-4185-971e-aef9d373465a",
   "metadata": {},
   "source": [
    "## create sections dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da0e8425-7093-4dde-98be-1ad7371e9514",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the registers dataframe from secties_totaal.csv\n",
    "df_sections = pd.read_csv(sections, sep=\",\", quotechar='\"', \\\n",
    "                         usecols=usecols_sections, encoding=\"utf-8\").rename(columns=rename_sections)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21c9bd97-db72-41f7-826e-32e9a35a09c4",
   "metadata": {},
   "source": [
    "Matching sections and registers merging dataframes and then applying conditions as below, results in 3 duplicates for one section and non-matches for 215 sections. The reason is that in these cases the scan numbers of the sections are inconsistent with the scan numbers of the registers. Often, the end of a section is set at a higher scan number than the end of the register. Moreover, data about the latest registers is more detailed than data about the sections during that period. Resolving the former error is done in the next processing step (see notebook STRO20_4_Images_M.ipynb). Resolving the latter error will be extremely time-consuming."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27269d8a-5d38-44b1-890b-71c99f2ca0b6",
   "metadata": {},
   "source": [
    "## images.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1520cbb8-13ce-4505-bb2f-f37910944754",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the images dataframe from images.csv\n",
    "df_images = pd.read_csv(images, sep=\",\", quotechar='\"', \\\n",
    "                         usecols=usecols_images, header=0, encoding=\"utf-8\").rename(columns=rename_images)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b93e1957-e061-4a0c-b279-554d27e22c23",
   "metadata": {},
   "outputs": [],
   "source": [
    "# len(df_images)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd25cad4-2806-449b-b770-0e3912e26bf7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The columns 'filename' is split into its constituent parts.\n",
    "# Information about micrifilm number (the origin of the scans is a set of microfilms) and scan are used to match register. section and image data below. \n",
    "df_images[['Sonttolregisters', 'mf_nr', 'scan', 'jpg']] = df_images.filename.str.split(r'-|_|\\.', expand=True)\n",
    "df_images.drop(columns=['Sonttolregisters', 'jpg'], inplace=True)\n",
    "df_images['scannr'] = df_images['scan'].astype(int)\n",
    "df_images['microfilm_number'] = df_images['mf_nr'].astype(int)\n",
    "df_images.drop(columns=['mf_nr', 'scan'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e54372e8-b1c2-4f31-b190-1ed6cd68dfc8",
   "metadata": {},
   "source": [
    "## merging images and registration dataframes to identify non-matches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "092db31b-858a-4e54-b0ef-76820c3ae90a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imgreg = pd.merge(df_images, df_registers, how=\"left\", on=\"microfilm_number\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12670b93-aedc-4957-856f-4982408db023",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imgreg2 = df_imgreg[['ce_id', 'scannr', 'reg_id']].loc[(df_imgreg['scannr'] >= df_imgreg['first_scan']) & (df_imgreg['scannr'] <= df_imgreg['last_scan'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e4fd047-fdea-42a5-9a51-e76af0c00f3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# len(df_imgreg2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d9e8702-fda5-4006-a6b2-02faf0fd7787",
   "metadata": {},
   "outputs": [],
   "source": [
    "# find missing entries\n",
    "# match on ce_id and scannr to account for entries across several scans\n",
    "missing_CE = df_images.merge(df_imgreg2, on=['ce_id', 'scannr'], how='left', indicator='Match')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12367adb-c11d-41b1-b39e-2e04af94efa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# scannr is outside of the scope of the first and last scans in the registers dataframe\n",
    "# this occurs in 774 cases spread across 54 microfilm numbers (see below)\n",
    "df_missing_CE = missing_CE.loc[missing_CE['Match'] == 'left_only']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "140b9855-2e42-4b90-b36a-4e634de38dea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can use this information to raise the value of the last_scan in the registers dataframe to the highest value in the images dataframe \n",
    "missing_regs = df_missing_CE[['scannr', 'reg_id']].groupby(df_missing_CE['microfilm_number']).max('scannr').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df36ffd5-e7bc-48d2-ab41-698a63605306",
   "metadata": {},
   "outputs": [],
   "source": [
    "# missing_regs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f5dcf5a-69af-4069-8ae4-d80070c7baa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the dataframe for further manual processing; the processed dataframe is available as CSV file on GitHub (STRO_20_corrections)\n",
    "# missing_regs.to_csv(r\"C:\\STRO_HUYGENS\\STRO_20_corrections\\registers_changeLastScan.csv\", encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5d89cdd-cd9a-404f-a70f-8a11436c48cc",
   "metadata": {},
   "source": [
    "## merging images and sections dataframes to find non-matches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a4811e1-d157-478d-a856-4f99e04014f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imgsec = pd.merge(df_images, df_sections, how=\"left\", on=\"microfilm_number\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd51d36e-baef-4957-ac50-733833d3a29c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imgsec2 = df_imgsec[['ce_id', 'scannr', 'section_id']].loc[(df_imgsec['scannr'] >= df_imgsec['section_first_scan']) & (df_imgsec['scannr'] <= df_imgsec['section_last_scan'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d15cac27-9b75-4958-93c1-3d0943f40059",
   "metadata": {},
   "outputs": [],
   "source": [
    "# len(df_imgsec2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01eafa35-f35a-431e-9329-777ae04cd190",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_imgsec2.duplicated(subset=['ce_id', 'scannr'], keep=False).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bea45a7-679e-4e51-a624-458ea7422d63",
   "metadata": {},
   "outputs": [],
   "source": [
    "# find missing entries\n",
    "# match on ce_id and scannr to account for entries across several scans\n",
    "missing_CE = df_images.merge(df_imgsec2, on=['ce_id', 'scannr'], how='left', indicator='Match')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1190aa77-00f0-496e-85b9-93c072263a4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# scannr is outside of the scope of the first and last scans in the registers dataframe\n",
    "# this occurs in 774 cases spread across 54 microfilm numbers (see below)\n",
    "df_missing_CE = missing_CE.loc[missing_CE['Match'] == 'left_only']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ca5264b-8120-4249-8d24-a9f99d66871e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_missing_CE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e2adfcc-6faa-4595-93af-1f7b7330eabd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can use this information to raise the value of the last_scan in the sections dataframe to the highest value in the images dataframe \n",
    "missing_sections = df_missing_CE[['microfilm_number', 'scannr']].sort_values(by=['microfilm_number', 'scannr'], ascending=True).value_counts().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b0900e5-bab1-416a-9a2a-e238720d96b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the dataframe for further manual processing; the processed dataframe is available as CSV file on GitHub (STRO_20_corrections)\n",
    "# missing_sections.to_csv(r\"C:\\STRO_HUYGENS\\STRO_20_corrections\\sections_changeSectionScanNumber.csv\", encoding='utf-8')"
   ]
  }
 ],
 "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.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}