STRO20_4_Images_Method.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 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 is a CSV file 'img.csv' that contains data about customs entries, and their images, registers and sections. <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": "dbda3977-02c8-4a79-a993-513490bba24a",
   "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": "b383cba1-017e-4d81-95eb-72f1d08de19f",
   "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",
    "corrections = 'STRO_20_corrections'\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}\"\n",
    "url_corrections = f\"https://api.github.com/repos/{owner}/{repo}/contents/{corrections}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c58fe911-967c-4f16-b73a-73779a4130c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "response_column_selection = requests.get(url_selection)\n",
    "response_rename_columns = requests.get(url_rename)\n",
    "response_corrections = requests.get(url_corrections)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b673a594-fe56-4e66-bf06-2ef2f979c901",
   "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",
    "    'usecols_remarks': 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']\n",
    "usecols_remarks = selection_mappings['usecols_remarks']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cabae37c-1280-4ce5-abe8-1b97be3c3d47",
   "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",
    "    'rename_remarks': 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']\n",
    "rename_remarks = rename_mappings['rename_remarks']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5eb00f38-2501-4552-bcd2-82b59d3f8f7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fetch the URLs for the corrections to be made to registers and sections dataframe\n",
    "for file in response_corrections.json():\n",
    "    if file['name'] == 'registers_changeLastScan.csv':\n",
    "        corrections_reg = file['download_url']\n",
    "    if file['name'] == 'sections_changeSectionFirstScanNumber.csv':\n",
    "        corr_sec_first = file['download_url']\n",
    "    if file['name'] == 'sections_changeSectionLastScanNumber.csv':\n",
    "        corr_sec_last = file['download_url']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a803de4-cbf6-4d22-bda9-e4b7407c66f8",
   "metadata": {},
   "source": [
    "## 2. Process registers_totaal.csv"
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "id": "434b37e9-1568-464f-80a2-3700fdf2927e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert update of inconsistent values here => load CSV with corrections and add corrections to the dataframe\n",
    "# corrections_reg is read directly from the GitHub URL fetched above\n",
    "df_corr_reg = pd.read_csv(corrections_reg, sep=';', encoding='utf-8').drop(columns=['Unnamed: 0'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ee570cf-2218-422f-be06-90b7a326bc2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to have a look at the dataframe\n",
    "# df_corr_reg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3444dbac-1030-49f9-8dcc-fa0b1f868b23",
   "metadata": {},
   "outputs": [],
   "source": [
    "# this iteration updates the values of last_scan in the df_registers based on the corrections stored in df_corr_reg\n",
    "for i in range(len(df_corr_reg)):\n",
    "    last_scan = df_corr_reg.iloc[i, 1]\n",
    "    reg_id = df_corr_reg.iloc[i, 2]\n",
    "    df_registers.loc[df_registers['reg_id'] == reg_id, 'last_scan'] = last_scan\n",
    "    i += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bf22132-d2d9-41d7-af09-f5802e2cff6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the updated dataframe to a CSV file\n",
    "df_registers.to_csv(r\"C:\\STRO20\\registers.csv\", sep=';', quotechar= '\"', index_label='index', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d37c2df2-158c-4185-971e-aef9d373465a",
   "metadata": {},
   "source": [
    "## 3. Process secties_totaal.csv"
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "id": "e4447769-1b43-4b30-a376-7147b636cd98",
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert update of inconsistent values here => load CSV with corrections and add corrections to the dataframe\n",
    "# then save the dataframe\n",
    "df_corr_sec_first = pd.read_csv(corr_sec_first, sep=';', encoding='utf-8').drop(columns=['Unnamed: 0'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1fd5d29-e38f-4777-ac54-da661fbf028f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to have a look at the dataframe\n",
    "# df_corr_sec_first.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1687f9ac-0061-44b4-92ab-00ddaa7db06d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# this iteration updates the values of section_first_scan in the df_sections based on the corrections stored in df_corr_sec_first\n",
    "for i in range(len(df_corr_sec_first)):\n",
    "    first_scan = df_corr_sec_first.iloc[i, 1]\n",
    "    section_id = df_corr_sec_first.iloc[i, 2]\n",
    "    df_sections.loc[df_sections['section_id'] == section_id, 'section_first_scan'] = first_scan\n",
    "    i += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e4e6121-4a17-45a0-9824-324a5ff9a5ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# insert update of inconsistent values here => load CSV with corrections and add corrections to the dataframe\n",
    "# then save the dataframe\n",
    "df_corr_sec_last = pd.read_csv(corr_sec_last, sep=';', encoding='utf-8').drop(columns=['Unnamed: 0'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0993697-ad09-4eb3-b483-0445ce3d38bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to view the dataframe\n",
    "# df_corr_sec_last.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d8ba44c-cb6a-43e1-a541-756cb222304a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# this iteration updates the values of section_first_scan in the df_sections based on the corrections stored in df_corr_sec_first\n",
    "for i in range(len(df_corr_sec_last)):\n",
    "    last_scan = df_corr_sec_last.iloc[i, 1]\n",
    "    section_id = df_corr_sec_last.iloc[i, 2]\n",
    "    df_sections.loc[df_sections['section_id'] == section_id, 'section_last_scan'] = last_scan\n",
    "    i += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58dad553-1c79-44d7-b500-515bb303e1b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to verify results\n",
    "# df_sections.loc[df_sections['section_id'] == 3307]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f54032b0-8096-41f6-a8c1-b6e89719c54a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the dataframe to a CSV file\n",
    "df_sections.to_csv(r\"C:\\STRO20\\sections.csv\", sep=';', quotechar= '\"', index_label='index', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27269d8a-5d38-44b1-890b-71c99f2ca0b6",
   "metadata": {},
   "source": [
    "## 4. Process 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": "7b73f884-180e-4ea9-b059-a1a8b5812493",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_remarks_images = pd.read_csv(images, sep=\",\", quotechar='\"', \\\n",
    "                         usecols=usecols_remarks, encoding=\"utf-8\", low_memory=False).rename(columns=rename_remarks).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59026eec-e55a-4abe-855c-4bf2f277f985",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_remarks_images.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afcfc634-e53b-4fa9-9e7a-6b9a81770f23",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_remarks_images"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd25cad4-2806-449b-b770-0e3912e26bf7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# process the information in the filename to separate out microfilm and scan number\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": "code",
   "execution_count": null,
   "id": "8902f9d8-d1ce-4a4e-aacb-75cb6fb80a10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to verify the results\n",
    "# df_images.loc[df_images['ce_id'] == 1169737]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e54372e8-b1c2-4f31-b190-1ed6cd68dfc8",
   "metadata": {},
   "source": [
    "## 5. Process images and registrations to add reg_id to images"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "092db31b-858a-4e54-b0ef-76820c3ae90a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge the images and registers dataframe on microfilm number\n",
    "# the merging produces a lot of false matches that will be filtered out in the next step\n",
    "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": [
    "# clean the merged dataframe based on a few conditions\n",
    "# the conditions are that the scan number of the customs entry must be within the range of scans for each register\n",
    "df_imgreg2 = df_imgreg[['ce_id', 'scannr', 'reg_id']].\\\n",
    "loc[(df_imgreg['scannr'] >= df_imgreg['first_scan']) & (df_imgreg['scannr'] <= df_imgreg['last_scan'])].\\\n",
    "reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e4fd047-fdea-42a5-9a51-e76af0c00f3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to verify the results\n",
    "# df_imgreg2.loc[df_imgreg2['ce_id'] == 1169737]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "581066d8-caef-43fb-98f9-797ebdc0a09c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from the cleaned dataframe, we only need the reg_id\n",
    "# matching with the images dataframe will be done based on the index\n",
    "df_imgreg3 = df_imgreg2[['reg_id']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5d89cdd-cd9a-404f-a70f-8a11436c48cc",
   "metadata": {},
   "source": [
    "## 6. Process images and sections to add section_id to images"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f1a4510-1fa0-4a3a-8323-dc2ec31b969b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge the images and registers dataframe on microfilm number\n",
    "# the merging produces a lot of false matches that will be filtered out in the next step\n",
    "df_imgsec = pd.merge(df_images, df_sections, how=\"left\", on=\"microfilm_number\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e07b85ae-2e80-410b-86b8-c61c7143150b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean the merged dataframe based on a few conditions\n",
    "# the conditions are that the scan number of the customs entry must be within the range of scans for each section\n",
    "df_imgsec2 = df_imgsec.\\\n",
    "loc[(df_imgsec['scannr'] <= df_imgsec['section_last_scan']) & (df_imgsec['scannr'] >= df_imgsec['section_first_scan'])].\\\n",
    "drop_duplicates(subset=['ce_id', 'scannr'], keep='first', inplace=False).\\\n",
    "reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06c83ea9-0400-4748-9f94-27a452f33fb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to verify the results\n",
    "# df_imgsec2.loc[df_imgsec2['ce_id'] == 1169737]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5525c0fb-ffd8-47cc-8fcc-7cca4c59a05b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from the cleaned dataframe, we only need the reg_id\n",
    "# matching with the images dataframe will be done based on the index\n",
    "df_imgsec3 = df_imgsec2[['section_id']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c98c90f-19e3-4099-8fef-490ddbab6d99",
   "metadata": {},
   "source": [
    "## 7. concatenate images, registers and sections into one dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0718803-7d2b-40ae-a5e6-d7f672075afb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_img = pd.concat([df_images, df_imgreg3, df_imgsec3], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21b1d083-af8e-4f49-b6e8-07f220adc1d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# uncomment to review the dataframe\n",
    "# df_img"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02835f86-c419-4bb1-a9a5-7c3035d9302f",
   "metadata": {},
   "source": [
    "## 8. Save dataframes to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6eb7475f-333d-41a1-bb6d-2b31a99d3862",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the dataframe to a CSV file\n",
    "df_img.to_csv(r\"C:\\STRO20\\img.csv\", sep=';', quotechar= '\"', index_label='index', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e10bb1e2-f09f-4bc8-a5bc-f5eb1361096a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_remarks_images.to_csv(r\"C:\\STRO20\\remarks_images.csv\", sep=';', quotechar= '\"', index_label='index', 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
}