Metadata.ipynb

{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# Metadata\n",
    "\n",
    "Loads and cleans the Metadata from Compact Memory"
   ],
   "id": "e09ef4bddb525c26"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:37.695462Z",
     "start_time": "2024-04-29T19:08:37.689292Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# for the sake of readability remove the warnings\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings('ignore')"
   ],
   "id": "a751934d6e3ae2c",
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### Bibliotheken importieren",
   "id": "636e5f00326d5871"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:38.152556Z",
     "start_time": "2024-04-29T19:08:37.697114Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ],
   "id": "747ec481761fe459",
   "outputs": [],
   "execution_count": 2
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## Metadaten laden\n",
    "Die Daten werden aus der Datei `CM_Seiten_Metadaten.csv` geladen.\n",
    "Alle Metadaten finden sich in dem Ordner `metadata`.\n",
    "Die Spalten sollten mit den richtigen Datentypen geladen werden."
   ],
   "id": "ade940c082f4ed53"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:42.464736Z",
     "start_time": "2024-04-29T19:08:38.154683Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Specify the desired data types for each column\n",
    "dtype_options = {\n",
    "    'VLID_Seite': 'Int64',\n",
    "    'OT_PATH': 'str',\n",
    "    'VLID_Zs': 'Int64',\n",
    "    'VLID_Parent': 'Int64',\n",
    "    'Parent Knotentyp': 'str',\n",
    "    'Parent-Type': 'str',\n",
    "    'Zs_Caption': 'str',\n",
    "    'Volume_Caption': 'str',\n",
    "    'Heft_Caption': 'str',\n",
    "    'Aufsatz_Caption': 'str',\n",
    "    'Datum': 'str',  # You'll need to parse this as date later\n",
    "    'Seite (OT_SORT)': 'Int64',\n",
    "    'Seite_Caption': 'str',\n",
    "    'Seitenzahl_Caption': 'str'\n",
    "}\n",
    "# Load the CSV file with dtype parameter\n",
    "df_metadata = pd.read_csv(\"metadata/CM_Seiten_Metadaten.csv\", sep=\"\\t\", dtype=dtype_options, parse_dates=['Datum'])"
   ],
   "id": "346dfc4ec2564ac5",
   "outputs": [],
   "execution_count": 3
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## Statistiken über die Metadaten  \n",
    "Hier erst einmal die Statistiken zu den Metadaten."
   ],
   "id": "3cded4dab6affdf"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:43.613777Z",
     "start_time": "2024-04-29T19:08:42.468392Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.info()",
   "id": "3e5b36a7b0fe764b",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 918165 entries, 0 to 918164\n",
      "Data columns (total 14 columns):\n",
      " #   Column              Non-Null Count   Dtype \n",
      "---  ------              --------------   ----- \n",
      " 0   VLID_Seite          918165 non-null  Int64 \n",
      " 1   OT_PATH             918165 non-null  object\n",
      " 2   VLID_Zs             918165 non-null  Int64 \n",
      " 3   VLID_Parent         918165 non-null  Int64 \n",
      " 4   Parent Knotentyp    918165 non-null  object\n",
      " 5   Parent-Type         918165 non-null  object\n",
      " 6   Zs_Caption          918165 non-null  object\n",
      " 7   Volume_Caption      918164 non-null  object\n",
      " 8   Heft_Caption        823773 non-null  object\n",
      " 9   Aufsatz_Caption     163772 non-null  object\n",
      " 10  Datum               918165 non-null  object\n",
      " 11  Seite (OT_SORT)     918165 non-null  Int64 \n",
      " 12  Seite_Caption       584763 non-null  object\n",
      " 13  Seitenzahl_Caption  814735 non-null  object\n",
      "dtypes: Int64(4), object(10)\n",
      "memory usage: 101.6+ MB\n"
     ]
    }
   ],
   "execution_count": 4
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.408748Z",
     "start_time": "2024-04-29T19:08:43.618040Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.describe()",
   "id": "de1fc676bc1b88a",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         VLID_Seite       VLID_Zs   VLID_Parent  Seite (OT_SORT)\n",
       "count  9.181650e+05  9.181650e+05  9.181650e+05    918165.000000\n",
       "mean   3.742243e+06  3.748121e+06  4.144832e+06        27.944801\n",
       "std    1.904469e+06  1.972278e+06  2.329042e+06        94.073622\n",
       "min    5.259800e+04  5.259600e+04  1.776570e+05         1.000000\n",
       "25%    2.617876e+06  2.583875e+06  2.626174e+06         4.000000\n",
       "50%    3.050373e+06  3.020846e+06  3.054014e+06         8.000000\n",
       "75%    3.980816e+06  4.782723e+06  5.449731e+06        16.000000\n",
       "max    1.097610e+07  1.101468e+07  1.101479e+07      3888.000000"
      ],
      "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>VLID_Seite</th>\n",
       "      <th>VLID_Zs</th>\n",
       "      <th>VLID_Parent</th>\n",
       "      <th>Seite (OT_SORT)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>9.181650e+05</td>\n",
       "      <td>9.181650e+05</td>\n",
       "      <td>9.181650e+05</td>\n",
       "      <td>918165.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.742243e+06</td>\n",
       "      <td>3.748121e+06</td>\n",
       "      <td>4.144832e+06</td>\n",
       "      <td>27.944801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.904469e+06</td>\n",
       "      <td>1.972278e+06</td>\n",
       "      <td>2.329042e+06</td>\n",
       "      <td>94.073622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>5.259800e+04</td>\n",
       "      <td>5.259600e+04</td>\n",
       "      <td>1.776570e+05</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.617876e+06</td>\n",
       "      <td>2.583875e+06</td>\n",
       "      <td>2.626174e+06</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.050373e+06</td>\n",
       "      <td>3.020846e+06</td>\n",
       "      <td>3.054014e+06</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>3.980816e+06</td>\n",
       "      <td>4.782723e+06</td>\n",
       "      <td>5.449731e+06</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.097610e+07</td>\n",
       "      <td>1.101468e+07</td>\n",
       "      <td>1.101479e+07</td>\n",
       "      <td>3888.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 5
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.416944Z",
     "start_time": "2024-04-29T19:08:45.409719Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.head()",
   "id": "d12a86a4de8b8e0f",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   VLID_Seite                  OT_PATH  VLID_Zs  VLID_Parent Parent Knotentyp  \\\n",
       "0       52598  |52596||9443938||52598|    52596      9443938        Metadaten   \n",
       "1       52599  |52596||9443938||52599|    52596      9443938        Metadaten   \n",
       "2       52600  |52596||9443938||52600|    52596      9443938        Metadaten   \n",
       "3       52601  |52596||9443938||52601|    52596      9443938        Metadaten   \n",
       "4       52602  |52596||9443938||52602|    52596      9443938        Metadaten   \n",
       "\n",
       "      Parent-Type                                         Zs_Caption  \\\n",
       "0  journal volume  ... Bericht ueber den Verein für die Provinz W...   \n",
       "1  journal volume  ... Bericht ueber den Verein für die Provinz W...   \n",
       "2  journal volume  ... Bericht ueber den Verein für die Provinz W...   \n",
       "3  journal volume  ... Bericht ueber den Verein für die Provinz W...   \n",
       "4  journal volume  ... Bericht ueber den Verein für die Provinz W...   \n",
       "\n",
       "  Volume_Caption Heft_Caption Aufsatz_Caption       Datum  Seite (OT_SORT)  \\\n",
       "0       8 (1835)          NaN             NaN  1835-01-01                1   \n",
       "1       8 (1835)          NaN             NaN  1835-01-01                2   \n",
       "2       8 (1835)          NaN             NaN  1835-01-01                3   \n",
       "3       8 (1835)          NaN             NaN  1835-01-01                4   \n",
       "4       8 (1835)          NaN             NaN  1835-01-01                5   \n",
       "\n",
       "  Seite_Caption Seitenzahl_Caption  \n",
       "0       [Seite]                NaN  \n",
       "1       [Seite]                NaN  \n",
       "2       [Seite]                NaN  \n",
       "3       Seite 4                  4  \n",
       "4       Seite 5                  5  "
      ],
      "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>VLID_Seite</th>\n",
       "      <th>OT_PATH</th>\n",
       "      <th>VLID_Zs</th>\n",
       "      <th>VLID_Parent</th>\n",
       "      <th>Parent Knotentyp</th>\n",
       "      <th>Parent-Type</th>\n",
       "      <th>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Aufsatz_Caption</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Seite (OT_SORT)</th>\n",
       "      <th>Seite_Caption</th>\n",
       "      <th>Seitenzahl_Caption</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>52598</td>\n",
       "      <td>|52596||9443938||52598|</td>\n",
       "      <td>52596</td>\n",
       "      <td>9443938</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>[Seite]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>52599</td>\n",
       "      <td>|52596||9443938||52599|</td>\n",
       "      <td>52596</td>\n",
       "      <td>9443938</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>[Seite]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>52600</td>\n",
       "      <td>|52596||9443938||52600|</td>\n",
       "      <td>52596</td>\n",
       "      <td>9443938</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>3</td>\n",
       "      <td>[Seite]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>52601</td>\n",
       "      <td>|52596||9443938||52601|</td>\n",
       "      <td>52596</td>\n",
       "      <td>9443938</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>4</td>\n",
       "      <td>Seite 4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52602</td>\n",
       "      <td>|52596||9443938||52602|</td>\n",
       "      <td>52596</td>\n",
       "      <td>9443938</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>5</td>\n",
       "      <td>Seite 5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.424853Z",
     "start_time": "2024-04-29T19:08:45.417951Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.tail()",
   "id": "5ef85c33663dc71f",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        VLID_Seite                        OT_PATH   VLID_Zs  VLID_Parent  \\\n",
       "918160     1958923  |11014679||11014793||1958923|  11014679     11014793   \n",
       "918161     1958924  |11014679||11014793||1958924|  11014679     11014793   \n",
       "918162     1958925  |11014679||11014793||1958925|  11014679     11014793   \n",
       "918163     1958926  |11014679||11014793||1958926|  11014679     11014793   \n",
       "918164     1958927  |11014679||11014793||1958927|  11014679     11014793   \n",
       "\n",
       "       Parent Knotentyp     Parent-Type                            Zs_Caption  \\\n",
       "918160        Metadaten  journal volume  Séance du comité de direction du ...   \n",
       "918161        Metadaten  journal volume  Séance du comité de direction du ...   \n",
       "918162        Metadaten  journal volume  Séance du comité de direction du ...   \n",
       "918163        Metadaten  journal volume  Séance du comité de direction du ...   \n",
       "918164        Metadaten  journal volume  Séance du comité de direction du ...   \n",
       "\n",
       "       Volume_Caption Heft_Caption Aufsatz_Caption       Datum  \\\n",
       "918160           1914          NaN             NaN  1914-01-01   \n",
       "918161           1914          NaN             NaN  1914-01-01   \n",
       "918162           1914          NaN             NaN  1914-01-01   \n",
       "918163           1914          NaN             NaN  1914-01-01   \n",
       "918164           1914          NaN             NaN  1914-01-01   \n",
       "\n",
       "        Seite (OT_SORT) Seite_Caption Seitenzahl_Caption  \n",
       "918160               47      Seite 45                 45  \n",
       "918161               48       [Seite]                NaN  \n",
       "918162               49       [Seite]                NaN  \n",
       "918163               50       [Seite]                NaN  \n",
       "918164               51       [Seite]                NaN  "
      ],
      "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>VLID_Seite</th>\n",
       "      <th>OT_PATH</th>\n",
       "      <th>VLID_Zs</th>\n",
       "      <th>VLID_Parent</th>\n",
       "      <th>Parent Knotentyp</th>\n",
       "      <th>Parent-Type</th>\n",
       "      <th>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Aufsatz_Caption</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Seite (OT_SORT)</th>\n",
       "      <th>Seite_Caption</th>\n",
       "      <th>Seitenzahl_Caption</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>918160</th>\n",
       "      <td>1958923</td>\n",
       "      <td>|11014679||11014793||1958923|</td>\n",
       "      <td>11014679</td>\n",
       "      <td>11014793</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1914-01-01</td>\n",
       "      <td>47</td>\n",
       "      <td>Seite 45</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918161</th>\n",
       "      <td>1958924</td>\n",
       "      <td>|11014679||11014793||1958924|</td>\n",
       "      <td>11014679</td>\n",
       "      <td>11014793</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1914-01-01</td>\n",
       "      <td>48</td>\n",
       "      <td>[Seite]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918162</th>\n",
       "      <td>1958925</td>\n",
       "      <td>|11014679||11014793||1958925|</td>\n",
       "      <td>11014679</td>\n",
       "      <td>11014793</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1914-01-01</td>\n",
       "      <td>49</td>\n",
       "      <td>[Seite]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918163</th>\n",
       "      <td>1958926</td>\n",
       "      <td>|11014679||11014793||1958926|</td>\n",
       "      <td>11014679</td>\n",
       "      <td>11014793</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1914-01-01</td>\n",
       "      <td>50</td>\n",
       "      <td>[Seite]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918164</th>\n",
       "      <td>1958927</td>\n",
       "      <td>|11014679||11014793||1958927|</td>\n",
       "      <td>11014679</td>\n",
       "      <td>11014793</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1914-01-01</td>\n",
       "      <td>51</td>\n",
       "      <td>[Seite]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 7
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.463364Z",
     "start_time": "2024-04-29T19:08:45.426631Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.sample(5)",
   "id": "491db1ffb9e345cc",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        VLID_Seite                                        OT_PATH   VLID_Zs  \\\n",
       "912844    10749445                 |10719318||10749704||10749445|  10719318   \n",
       "916731    10768923                 |10719318||10772218||10768923|  10719318   \n",
       "88583      2408815  |2379786||2379939||2379948||2408665||2408815|   2379786   \n",
       "626376     3342840           |3315709||3316186||3316199||3342840|   3315709   \n",
       "804061     5367722           |5367336||6488242||5991011||5367722|   5367336   \n",
       "\n",
       "        VLID_Parent Parent Knotentyp     Parent-Type  \\\n",
       "912844     10749704        Metadaten  journal volume   \n",
       "916731     10772218        Metadaten  journal volume   \n",
       "88583       2408665        Metadaten         article   \n",
       "626376      3316199        Metadaten   journal issue   \n",
       "804061      5991011        Metadaten   journal issue   \n",
       "\n",
       "                         Zs_Caption Volume_Caption          Heft_Caption  \\\n",
       "912844                      Aḥiasaf     3 (1895/6)                   NaN   \n",
       "916731                      Aḥiasaf    11 (1903/4)                   NaN   \n",
       "88583                     Palästina       8 (1835)  Heft 8 (August 1936)   \n",
       "626376                     Die Welt       8 (1835)        12 (23.3.1906)   \n",
       "804061  Das jüdische Literaturblatt       8 (1835)   No. 12. (23.3.1881)   \n",
       "\n",
       "                                      Aufsatz_Caption       Datum  \\\n",
       "912844                                            NaN  1895-01-01   \n",
       "916731                                            NaN  1903-01-01   \n",
       "88583   Probleme der Jugendverwahrlosung in Palästina  1936-08-01   \n",
       "626376                                            NaN  1906-03-23   \n",
       "804061                                            NaN  1881-03-23   \n",
       "\n",
       "        Seite (OT_SORT) Seite_Caption Seitenzahl_Caption  \n",
       "912844              253     Seite 431                431  \n",
       "916731              471     Seite 458                458  \n",
       "88583                 2     Seite 398                398  \n",
       "626376                8           NaN             (null)  \n",
       "804061                4      Seite 48                 48  "
      ],
      "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>VLID_Seite</th>\n",
       "      <th>OT_PATH</th>\n",
       "      <th>VLID_Zs</th>\n",
       "      <th>VLID_Parent</th>\n",
       "      <th>Parent Knotentyp</th>\n",
       "      <th>Parent-Type</th>\n",
       "      <th>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Aufsatz_Caption</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Seite (OT_SORT)</th>\n",
       "      <th>Seite_Caption</th>\n",
       "      <th>Seitenzahl_Caption</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>912844</th>\n",
       "      <td>10749445</td>\n",
       "      <td>|10719318||10749704||10749445|</td>\n",
       "      <td>10719318</td>\n",
       "      <td>10749704</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>3 (1895/6)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1895-01-01</td>\n",
       "      <td>253</td>\n",
       "      <td>Seite 431</td>\n",
       "      <td>431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>916731</th>\n",
       "      <td>10768923</td>\n",
       "      <td>|10719318||10772218||10768923|</td>\n",
       "      <td>10719318</td>\n",
       "      <td>10772218</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal volume</td>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>11 (1903/4)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1903-01-01</td>\n",
       "      <td>471</td>\n",
       "      <td>Seite 458</td>\n",
       "      <td>458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88583</th>\n",
       "      <td>2408815</td>\n",
       "      <td>|2379786||2379939||2379948||2408665||2408815|</td>\n",
       "      <td>2379786</td>\n",
       "      <td>2408665</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>article</td>\n",
       "      <td>Palästina</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 8 (August 1936)</td>\n",
       "      <td>Probleme der Jugendverwahrlosung in Palästina</td>\n",
       "      <td>1936-08-01</td>\n",
       "      <td>2</td>\n",
       "      <td>Seite 398</td>\n",
       "      <td>398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626376</th>\n",
       "      <td>3342840</td>\n",
       "      <td>|3315709||3316186||3316199||3342840|</td>\n",
       "      <td>3315709</td>\n",
       "      <td>3316199</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal issue</td>\n",
       "      <td>Die Welt</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>12 (23.3.1906)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1906-03-23</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(null)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>804061</th>\n",
       "      <td>5367722</td>\n",
       "      <td>|5367336||6488242||5991011||5367722|</td>\n",
       "      <td>5367336</td>\n",
       "      <td>5991011</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal issue</td>\n",
       "      <td>Das jüdische Literaturblatt</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>No. 12. (23.3.1881)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1881-03-23</td>\n",
       "      <td>4</td>\n",
       "      <td>Seite 48</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.509518Z",
     "start_time": "2024-04-29T19:08:45.464500Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.corr()",
   "id": "2688a2ce64e074c",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                 VLID_Seite   VLID_Zs  VLID_Parent  Seite (OT_SORT)\n",
       "VLID_Seite         1.000000  0.891214     0.840641         0.121274\n",
       "VLID_Zs            0.891214  1.000000     0.813594         0.109924\n",
       "VLID_Parent        0.840641  0.813594     1.000000         0.099550\n",
       "Seite (OT_SORT)    0.121274  0.109924     0.099550         1.000000"
      ],
      "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>VLID_Seite</th>\n",
       "      <th>VLID_Zs</th>\n",
       "      <th>VLID_Parent</th>\n",
       "      <th>Seite (OT_SORT)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>VLID_Seite</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.891214</td>\n",
       "      <td>0.840641</td>\n",
       "      <td>0.121274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VLID_Zs</th>\n",
       "      <td>0.891214</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.813594</td>\n",
       "      <td>0.109924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VLID_Parent</th>\n",
       "      <td>0.840641</td>\n",
       "      <td>0.813594</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.099550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Seite (OT_SORT)</th>\n",
       "      <td>0.121274</td>\n",
       "      <td>0.109924</td>\n",
       "      <td>0.099550</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 9
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.513160Z",
     "start_time": "2024-04-29T19:08:45.510347Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.shape",
   "id": "1d7597110210e299",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(918165, 14)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## Beispiel für die Metadaten\n",
    "Damit können wir die Spalten und die Werte in den Metadaten sehen um die Daten besser zu verstehen."
   ],
   "id": "a0927fdc8b5fa6b2"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.538361Z",
     "start_time": "2024-04-29T19:08:45.514278Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.sample(5)",
   "id": "7d8b9ca6506cb536",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        VLID_Seite                               OT_PATH  VLID_Zs  \\\n",
       "293683     2679589  |2651273||2653156||2653183||2679589|  2651273   \n",
       "560381     3239966  |3224737||3225606||3225611||3239966|  3224737   \n",
       "210009     2562114  |2554541||2554813||2554816||2562114|  2554541   \n",
       "492496     3068133  |3062972||3063062||3063098||3068133|  3062972   \n",
       "136759     2479951  |2446951||2448122||2448159||2479951|  2446951   \n",
       "\n",
       "        VLID_Parent Parent Knotentyp    Parent-Type  \\\n",
       "293683      2653183        Metadaten  journal issue   \n",
       "560381      3225611        Metadaten  journal issue   \n",
       "210009      2554816        Metadaten  journal issue   \n",
       "492496      3063098        Metadaten  journal issue   \n",
       "136759      2448159        Metadaten  journal issue   \n",
       "\n",
       "                               Zs_Caption Volume_Caption  \\\n",
       "293683                 Jüdische Rundschau       8 (1835)   \n",
       "560381  Allgemeine Zeitung des Judenthums       8 (1835)   \n",
       "210009                        Dibre Emeth       8 (1835)   \n",
       "492496                       Die Wahrheit       8 (1835)   \n",
       "136759                       Der Israelit       8 (1835)   \n",
       "\n",
       "                  Heft_Caption Aufsatz_Caption       Datum  Seite (OT_SORT)  \\\n",
       "293683      Heft 27 (4.4.1930)             NaN  1930-04-04                1   \n",
       "560381      Heft 4 (22.1.1849)             NaN  1849-01-22                7   \n",
       "210009              3-4 (1874)             NaN  1874-01-01               15   \n",
       "492496     Heft 36 (13.9.1901)             NaN  1901-09-13                2   \n",
       "136759  Heft 39-40 (20.5.1891)             NaN  1891-05-20               10   \n",
       "\n",
       "       Seite_Caption Seitenzahl_Caption  \n",
       "293683           NaN             (null)  \n",
       "560381      Seite 51                 51  \n",
       "210009           NaN             (null)  \n",
       "492496           NaN             (null)  \n",
       "136759           NaN             (null)  "
      ],
      "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>VLID_Seite</th>\n",
       "      <th>OT_PATH</th>\n",
       "      <th>VLID_Zs</th>\n",
       "      <th>VLID_Parent</th>\n",
       "      <th>Parent Knotentyp</th>\n",
       "      <th>Parent-Type</th>\n",
       "      <th>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Aufsatz_Caption</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Seite (OT_SORT)</th>\n",
       "      <th>Seite_Caption</th>\n",
       "      <th>Seitenzahl_Caption</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>293683</th>\n",
       "      <td>2679589</td>\n",
       "      <td>|2651273||2653156||2653183||2679589|</td>\n",
       "      <td>2651273</td>\n",
       "      <td>2653183</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal issue</td>\n",
       "      <td>Jüdische Rundschau</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 27 (4.4.1930)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1930-04-04</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(null)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560381</th>\n",
       "      <td>3239966</td>\n",
       "      <td>|3224737||3225606||3225611||3239966|</td>\n",
       "      <td>3224737</td>\n",
       "      <td>3225611</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal issue</td>\n",
       "      <td>Allgemeine Zeitung des Judenthums</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 4 (22.1.1849)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1849-01-22</td>\n",
       "      <td>7</td>\n",
       "      <td>Seite 51</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210009</th>\n",
       "      <td>2562114</td>\n",
       "      <td>|2554541||2554813||2554816||2562114|</td>\n",
       "      <td>2554541</td>\n",
       "      <td>2554816</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal issue</td>\n",
       "      <td>Dibre Emeth</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>3-4 (1874)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1874-01-01</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(null)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492496</th>\n",
       "      <td>3068133</td>\n",
       "      <td>|3062972||3063062||3063098||3068133|</td>\n",
       "      <td>3062972</td>\n",
       "      <td>3063098</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal issue</td>\n",
       "      <td>Die Wahrheit</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 36 (13.9.1901)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1901-09-13</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(null)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136759</th>\n",
       "      <td>2479951</td>\n",
       "      <td>|2446951||2448122||2448159||2479951|</td>\n",
       "      <td>2446951</td>\n",
       "      <td>2448159</td>\n",
       "      <td>Metadaten</td>\n",
       "      <td>journal issue</td>\n",
       "      <td>Der Israelit</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 39-40 (20.5.1891)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1891-05-20</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(null)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 11
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Die Spalten der Metadaten anzeigen",
   "id": "7ad9fef84964c863"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.542191Z",
     "start_time": "2024-04-29T19:08:45.539391Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.columns",
   "id": "46c2c78a8aaa1409",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['VLID_Seite', 'OT_PATH', 'VLID_Zs', 'VLID_Parent', 'Parent Knotentyp',\n",
       "       'Parent-Type', 'Zs_Caption', 'Volume_Caption', 'Heft_Caption',\n",
       "       'Aufsatz_Caption', 'Datum', 'Seite (OT_SORT)', 'Seite_Caption',\n",
       "       'Seitenzahl_Caption'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 12
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## Bereinigung der Metadaten\n",
    "\n",
    "Unnötige Spalten entfernen. Wir benötigen die Informationen zu den Dateien und den Seiten nicht. Diese dienen nur zur Verknüpfung der Metadaten mit den Texten der einzelnen Artikel und Seiten, ist aber für die Analyse nicht relevant.\n",
    "\n",
    "Das sind: VLID_Seite, OT_PATH, VLID_Zs, VLID_Parent, Parent Knotentyp, Parent-Type, Heft_Caption, Aufsatz_Caption, Seite (OT_SORT), Seite_Caption, Seitenzahl_Caption\n",
    "\n",
    "Anmerkung: VLID steht für Verlagslösungs-ID, OT für Online-Titelsuche, Zs für Zeitschrift, Heft für Ausgabe, Aufsatz für Artikel, Seite für Seite\n"
   ],
   "id": "6cefd2184dda00cd"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.573505Z",
     "start_time": "2024-04-29T19:08:45.542938Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.drop(columns=['VLID_Seite', 'OT_PATH', 'VLID_Zs', 'VLID_Parent', 'Parent Knotentyp', 'Parent-Type', 'Seite (OT_SORT)', 'Aufsatz_Caption', 'Seite_Caption', 'Seitenzahl_Caption'], axis=1, inplace=True)",
   "id": "bc939331dc11058",
   "outputs": [],
   "execution_count": 13
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### Testen ob die Spalten entfernt wurden",
   "id": "a1124893c8176d19"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:45.725004Z",
     "start_time": "2024-04-29T19:08:45.720441Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.head()",
   "id": "8ede848f99b543e5",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                          Zs_Caption Volume_Caption  \\\n",
       "0  ... Bericht ueber den Verein für die Provinz W...       8 (1835)   \n",
       "1  ... Bericht ueber den Verein für die Provinz W...       8 (1835)   \n",
       "2  ... Bericht ueber den Verein für die Provinz W...       8 (1835)   \n",
       "3  ... Bericht ueber den Verein für die Provinz W...       8 (1835)   \n",
       "4  ... Bericht ueber den Verein für die Provinz W...       8 (1835)   \n",
       "\n",
       "  Heft_Caption       Datum  \n",
       "0          NaN  1835-01-01  \n",
       "1          NaN  1835-01-01  \n",
       "2          NaN  1835-01-01  \n",
       "3          NaN  1835-01-01  \n",
       "4          NaN  1835-01-01  "
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Datum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 14
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:46.054430Z",
     "start_time": "2024-04-29T19:08:46.018558Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.sample(5)",
   "id": "cdc3601d2dabe1cc",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                               Zs_Caption Volume_Caption  \\\n",
       "850613  Boletín informativo de la Sociedad Cultural Is...       8 (1835)   \n",
       "680594                           Jüdischer Bote vom Rhein       8 (1835)   \n",
       "431469                               Wiener Morgenzeitung       8 (1835)   \n",
       "114684                                       Der Israelit       8 (1835)   \n",
       "792620                                         Der Schild       8 (1835)   \n",
       "\n",
       "                Heft_Caption       Datum  \n",
       "850613       6 (Junio, 1967)  1967-06-01  \n",
       "680594       152 (28.4.1922)  1922-04-28  \n",
       "431469  Heft 357 (19.1.1920)  1920-01-19  \n",
       "114684    Heft 3 (16.1.1861)  1861-01-16  \n",
       "792620          9 (8.5.1930)  1930-05-08  "
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Datum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>850613</th>\n",
       "      <td>Boletín informativo de la Sociedad Cultural Is...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>6 (Junio, 1967)</td>\n",
       "      <td>1967-06-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680594</th>\n",
       "      <td>Jüdischer Bote vom Rhein</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>152 (28.4.1922)</td>\n",
       "      <td>1922-04-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431469</th>\n",
       "      <td>Wiener Morgenzeitung</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 357 (19.1.1920)</td>\n",
       "      <td>1920-01-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114684</th>\n",
       "      <td>Der Israelit</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 3 (16.1.1861)</td>\n",
       "      <td>1861-01-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>792620</th>\n",
       "      <td>Der Schild</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>9 (8.5.1930)</td>\n",
       "      <td>1930-05-08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 15
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:46.446270Z",
     "start_time": "2024-04-29T19:08:46.443529Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.columns",
   "id": "db3a9279fefc03cd",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Zs_Caption', 'Volume_Caption', 'Heft_Caption', 'Datum'], dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 16
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "### Liste aller Zeitschriften\n",
    "\n",
    "Anzahl der Zeitschriften ausgeben lassen und die Liste aller Zeitschriften."
   ],
   "id": "23c3c8e77b2fc7cb"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:55.198456Z",
     "start_time": "2024-04-29T19:08:55.119648Z"
    }
   },
   "cell_type": "code",
   "source": "len(df_metadata['Zs_Caption'].unique())",
   "id": "5aea8b0c1247d330",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "308"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 17
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:08:55.551085Z",
     "start_time": "2024-04-29T19:08:55.481733Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata['Zs_Caption'].unique()",
   "id": "291a8de5dc3fa5ed",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['... Bericht ueber den Verein für die Provinz Westfalen zur Bildung von Elementar-Lehrern und Befoerderung von Handwerken und Kuensten unter den Juden',\n",
       "       'Führer durch die jüdische Gemeindeverwaltung und Wohlfahrtspflege in Deutschland',\n",
       "       'Sechzehnter Bericht über die Religionsschule der jüdischen Gemeinde, womit zu der Sonntag den 26. März in dem Hörsaale der jüdischen Knaben-Schule ... öffentlichen Prüfung ... einladet',\n",
       "       '... Bericht ueber den Verein für Westfalen und Rheinprovinz zur Bildung von Elementar-Lehrern und Befoerderung von Handwerken und Kuensten unter den Juden',\n",
       "       \"... Verwaltungsbericht des Haupt-Grenz-Comité's zu Königsberg i. Pr. für Beseitigung der Nothstände unter den Israeliten West-Rußlands\",\n",
       "       'Führer durch die jüdische Wohlfahrtspflege in Deutschland',\n",
       "       'Bericht der Isr. Religionsschule zu Kiel',\n",
       "       'Actes et conférences de la Société des Études Juives',\n",
       "       'Jüdischer Almanach', 'Österreichisch-ungarische Cantoren-Zeitung',\n",
       "       'Rechenschaftsbericht des Vereins \"Gumpertz\\'sches Siechenhaus\" und der \"Minka von Goldschmidt-Rothschild-Stiftung\"',\n",
       "       '[Verein zur Pflege und Unterstützung israelitischer Kranker Bikkur Cholim, gegründet v. Dr. M. Hirsch. Bericht 4, 1895/97-14,1909]',\n",
       "       '... Jahresbericht der Jüdischen Frauenvereinigung e.V. zu Frankfurt a.M.',\n",
       "       'Bericht des Pflegamtes des Almosenkastens der israelitischen Gemeinde über dessen Wirksamkeit im Jahre ...',\n",
       "       'Jahresbericht der Frauen-Vereinigung der Frankfurt-Loge in Frankfurt a. M.',\n",
       "       'Hebraeische Bibliographie', 'Freie jüdische Lehrerstimme',\n",
       "       'Mitteilungen des Jüdischen Turn- und Sportvereins Bar-Kochba e.V. Frankfurt a.M.',\n",
       "       'Zeitschrift für hebraeische Bibliographie',\n",
       "       'Zeitschrift für die Geschichte der Juden in Deutschland',\n",
       "       'Altneuland', 'KC-Blätter',\n",
       "       'Bericht der Hochschule für die Wissenschaft des Judenthums in Berlin',\n",
       "       'Bar Kochba', 'Central-Verein-Zeitung', 'Jeschurun', 'Sulamith',\n",
       "       'Im deutschen Reich', 'Der Orient',\n",
       "       'Mitteilungen aus dem Verband der Vereine für Jüdische Geschichte und Literatur in Deutschland',\n",
       "       'Palästina',\n",
       "       'Zeitschrift für die Geschichte der Juden in der Tschechoslowakei',\n",
       "       'Illustrirte Monatshefte für die gesammten Interessen des Judenthums',\n",
       "       'Jahrbücher für jüdische Geschichte und Litteratur',\n",
       "       'Central-Anzeiger für jüdische Litteratur',\n",
       "       'Jüdische Zeitschrift für Wissenschaft und Leben',\n",
       "       'Jüdische Weltrundschau', 'Blau-Weiss-Blätter',\n",
       "       'Neue Nationalzeitung', 'Nationalzeitung', 'Neue Zeitung',\n",
       "       'Die Stimme', 'Jüdisches Volksblatt', 'Israelitische Rundschau',\n",
       "       'Jüdische Zeitung', 'Der Israelit', 'Der Jude', 'Ben Chananja',\n",
       "       'Nachrichtendienst', 'Palästina-Nachrichten',\n",
       "       'Mitteilungen der Arbeitsgemeinschaft Jüdisch-Liberaler Jugendvereine Deutschlands',\n",
       "       'Nahalat tsevi', 'Dibre Emeth', 'Deborah',\n",
       "       'Der Israelit des neunzehnten Jahrhunderts',\n",
       "       'Magazin für die Wissenschaft des Judenthums',\n",
       "       'Bericht über das Schuljahr ... der Stiftischen Realschule mit Lyzeum der Israelitischen Religionsgesellschaft in Frankfurt am Main',\n",
       "       'Freie zionistische Blätter', 'Ost und West',\n",
       "       'Kalender und Jahrbuch für Israeliten', 'Jüdisch-liberale Zeitung',\n",
       "       'Jüdische Volksstimme', 'Der treue Zionswächter',\n",
       "       'Jüdische Presse', 'Mitteilungen zur jüdischen Volkskunde',\n",
       "       'Der Jüdische Arbeiter',\n",
       "       'Mitteilungen der Gesellschaft für Jüdische Volkskunde',\n",
       "       'Jahrbuch der Gesellschaft für Geschichte der Juden in der Čechoslovakischen Republik',\n",
       "       'Volk und Land',\n",
       "       'Zeitschrift für Demographie und Statistik der Juden',\n",
       "       'Jüdische Rundschau', 'Neue jüdische Presse',\n",
       "       'Korrespondenzblatt des Vereins zur Gründung und Erhaltung einer Akademie für die Wissenschaft des Judentums',\n",
       "       'Judaica', 'Jüdische Arbeits- und Wanderfürsorge', 'Der Anfang',\n",
       "       'Der neue Anfang', 'Die neue Welt',\n",
       "       \"Bericht des Jüdisch-Theologischen Seminars Fraenckel'scher Stiftung\",\n",
       "       'Bayerische israelitische Gemeindezeitung',\n",
       "       'Zeitschrift für die religiösen Interessen des Judenthums',\n",
       "       'Mitteilungen des Gesamtarchivs der Deutschen Juden',\n",
       "       'Unsere Tribüne', 'KC-Mitteilungen', 'Die Kreatur',\n",
       "       'Jüdische Schulzeitung', 'Der jüdische Wille', 'Freie Tribüne',\n",
       "       'Esra',\n",
       "       'Monatsschrift für Geschichte und Wissenschaft des Judentums',\n",
       "       'Der Morgen', 'Neue jüdische Monatshefte', 'Menorah',\n",
       "       'Wissenschaftliche Zeitschrift für jüdische Theologie',\n",
       "       'Zeitschrift für die Wissenschaft des Judenthums',\n",
       "       'Populär-wissenschaftliche Monatsblätter zur Belehrung über das Judenthum für Gebildete aller Confessionen',\n",
       "       'Wiener Morgenzeitung', 'Berliner Vereinsbote',\n",
       "       \"Dr. Bloch's oesterreichische Wochenschrift\", 'Die Wahrheit',\n",
       "       'Programm', 'Selbst-Emancipation',\n",
       "       'Gemeindeblatt der Israelitischen Gemeinde Frankfurt am Main',\n",
       "       'Jahrbuch für jüdische Geschichte und Literatur', 'Die Freistatt',\n",
       "       'Jahresbericht der Landes-Rabbinerschule in Budapest',\n",
       "       'Liberales Judentum', 'Der israelitische Volkslehrer', 'Zion',\n",
       "       'Israelitische Annalen', 'Jüdisches Jahrbuch für die Schweiz',\n",
       "       'Jahrbuch für die Geschichte der Juden und des Judenthums',\n",
       "       'Das Zelt',\n",
       "       'Jüdisches Jahrbuch für Sachsen und Adressbuch der Gemeindebehörden, Organisationen und Vereine',\n",
       "       'Allgemeine Zeitung des Judenthums', 'Die Welt',\n",
       "       'Stenographisches Protokoll der Verhandlungen des … Zionisten-Kongresses … in …',\n",
       "       'Jahrbuch der Jüdisch-Literarischen Gesellschaft',\n",
       "       'Jüdische Korrespondenz', 'Der junge Jude',\n",
       "       'Bericht über die Jacobson-Schule, Realschule mit (Reform-)Realprogymnasium (nach dem Frankfurter Lehrplan) zu Seesen am Harz',\n",
       "       'Der jüdische Student', 'Jung Juda', 'Jüdischer Nationalkalender',\n",
       "       'Di Yidishe eḳonomiḳ', 'Jerubbaal', 'Jüdischer Bote vom Rhein',\n",
       "       'Breslauer jüdisches Gemeindeblatt',\n",
       "       'Verwaltungsblatt des Preußischen Landesverbandes Jüdischer Gemeinden',\n",
       "       'Jüdisches Wochenblatt', 'Soncino-Blätter',\n",
       "       'Jahrbuch für Israeliten', 'Jüdische allgemeine Zeitung',\n",
       "       'Bericht über den Zustand der Israelitischen Waisen-Knaben-Anstalt zu Posen',\n",
       "       'Lûaḥ', \"Bikkūrē hā-'ittīm\", 'Kokhve Yitsḥaḳ',\n",
       "       'Luaḥ erets Yiśraʾel', 'Zeitschrift für jüdische Wohlfahrtspflege',\n",
       "       'Allgemeine Israelitische Wochenschrift', 'Literarishe bleter',\n",
       "       'Blätter des Jüdischen Frauenbundes für Frauenarbeit und Frauenbewegung',\n",
       "       'Die jüdische Presse', 'Mim-mizraḥ û-mim-maʿarāv',\n",
       "       'K.C. Jahrbuch ...', 'Bericht der Großloge für Deutschland',\n",
       "       'Haḳedem', 'Das Morgenland', 'Der Sabbath', 'Die Menschenrechte',\n",
       "       'Gemeindeblatt der Israelitischen Religionsgemeinde zu Leipzig',\n",
       "       'Handwerk und Gewerbe', 'Allgemeine illustrirte Judenzeitung',\n",
       "       'Der Aufstieg', \"Der bibel'sche Orient\",\n",
       "       'Bibliographischer Vierteljahresbericht für die jüdische Literatur',\n",
       "       'Monatsschrift der Berliner Logen UOBB', 'Freies Blatt',\n",
       "       'Das Füllhorn', 'Die Gemeinschaft', 'Die Gegenwart',\n",
       "       'Gemeindeblatt der Deutsch-Israelitischen Gemeinde zu Hamburg',\n",
       "       'Gemeindeblatt der Israelitischen Religionsgemeinde Dresden',\n",
       "       'Herzl-Bund-Blätter', 'Joseph', 'Der israelitische Lehrer',\n",
       "       'Die jüdische Frau', 'Jüdische Monatshefte', 'Jüdischer Volksbote',\n",
       "       'Jüdisches Gemeindeblatt für das Gebiet der Rheinpfalz',\n",
       "       'Gemeinde-Zeitung für die israelitischen Gemeinden Württembergs',\n",
       "       'Korrespondenzblatt des Verbandes der Deutschen Juden',\n",
       "       'Mitteilungen des Daniel-Bundes', 'Der Schild',\n",
       "       'Jüdisches Gemeindeblatt',\n",
       "       'Bleṭer far idisher demografye, sṭaṭisṭik un eḳonomiḳ',\n",
       "       'Korrespondenzblatt des Centralbüros für Jüdische Auswanderungsangelegenheiten des Hilfsvereins der Deutschen Juden',\n",
       "       'Jüdische Turnzeitung', 'Pult und Bühne', 'Rimmōn',\n",
       "       'Mitteilungen des Verbandes der Jüdischen Jugendvereine Deutschlands',\n",
       "       'Jahresbericht über die Real- u. Volksschule der Israelitischen Gemeinde zu Frankfurt am Main',\n",
       "       'Das jüdische Literaturblatt',\n",
       "       'Israelitische Wochenschrift für die religiösen und socialen Interessen des Judenthums',\n",
       "       'Mitteilungsblatt des Verbandes nationaldeutscher Juden e.V.',\n",
       "       'Israelitische Schulzeitung', 'Cultur-almanach', 'Blätter',\n",
       "       'Rechenschafts-Bericht',\n",
       "       'Gemeindeblatt der Israelitischen Gemeinde Bremen',\n",
       "       'Jüdisches Gemeindeblatt für die israelitischen Gemeinden in Württemberg',\n",
       "       'Jüdisches Gemeindeblatt für das Gebiet der Hansestadt Hamburg',\n",
       "       'Jüdisches Gemeindeblatt Dresden', 'Der jüdische Handwerker',\n",
       "       'Israelitische Wochenschrift', 'Jüdisches Familienblatt',\n",
       "       'Jüdische Monatshefte für Turnen und Sport',\n",
       "       'Jüdische Turn- und Sportzeitung',\n",
       "       'Jüdisches Gemeindeblatt und Nachrichtenblatt der Gemeindeverwaltung der Israelitischen Religionsgemeinde zu Leipzig',\n",
       "       'Jüdisches Litteratur-Blatt',\n",
       "       'Korrespondenzblatt über Auswanderungs- und Siedlungswesen',\n",
       "       'Jüdische Auswanderung', 'Der jüdische Arbeitsmarkt',\n",
       "       'Der Jugendbund', 'Der nationaldeutsche Jude',\n",
       "       'Jüdische Volkszeitung', 'Der Orden Bne Briss', 'Die Bne Briss',\n",
       "       'Jahresbericht', 'Jüdische Jugend',\n",
       "       'Israelitisches Predigt- und Schul-Magazin', 'Razsvět',\n",
       "       'Undzer Worṭ', 'Adressbuch für den jüdischen Buchhandel',\n",
       "       'Yediʿot shel Irgun ʿOle Breslau be-Yiśraʾel',\n",
       "       'Yediʿot shel Irgun Yotsʾe Shlezyah ʿIlit be-Yiśraʾel',\n",
       "       'Yidishe bilder', 'Aus unserer Spielzeit',\n",
       "       'Juedischer Almanach für Gross-Rumänien',\n",
       "       'Židovská ročenka pre Slovensko', 'Pardes',\n",
       "       'Wiener jüdisches Familienblatt',\n",
       "       'Literarisches und homiletisches Beiblatt', 'Luaḥ Ḥermon',\n",
       "       \"Boletín informativo de la Sociedad Cultural Israelita B'ne Jisroel\",\n",
       "       'Sabbath-Blatt', 'Yedies̀ fun Yidishn Ṿisnshaftlekhn Insṭiṭuṭ',\n",
       "       'Bericht der Gesellschaft zur Beförderung des Christenthums unter den Juden in Frankfurt am Main',\n",
       "       'Archiv für jüdische Familienforschung, Kunstgeschichte und Museumswesen',\n",
       "       'Yor-bukh fun Yidishn yishev in Argenṭine',\n",
       "       'Nachricht von der ... Generalversammlung und Jahresfeyer der Frankfurter Gesellschaft zur Beförderung des Christenthums unter den Juden',\n",
       "       '... Bericht der Jüdischen Haushaltungsschule zu Frankfurt a.M. für die Zeit vom ... bis ...',\n",
       "       'Antisemitisches Jahrbuch für ...',\n",
       "       'Bericht der Exekutive der Zionistischen Organisation an den ... Zionistenkongress',\n",
       "       'Dirnfurṭer prifilegirṭe tsayṭung', 'Das Band', 'Das Abendland',\n",
       "       'Blätter für jüdische Geschichte und Litteratur',\n",
       "       'Das jüdische Blatt', 'Wissen und Waffen', 'Das jüdische Echo',\n",
       "       '... Jahresbericht der Israelitischen Waisenanstalt zu Frankfut a.M.',\n",
       "       'Urwähler-Zeitung',\n",
       "       'Mitteilungen des Reichsausschusses der Jüdischen Jugendverbände',\n",
       "       'Von deutsch-jüdischer Jugend', \"The B'nai B'rith magazine\",\n",
       "       'Zionist library for boys and girls', 'Alt-Prager Almanach',\n",
       "       'Circulare der K.K. Landesregierung im Erzherzogthume Oesterreich unter der Enns',\n",
       "       'Kinderblatt', 'Jüdisches Gemeindeblatt für die britische Zone',\n",
       "       'Mitteilungen des Israelitischen Lehrervereins für Bayern',\n",
       "       'Seite der Jugend', 'Jüdische Zeitung für Ostdeutschland',\n",
       "       'Der jüdische Herold', 'Jungjüdischer Wanderer',\n",
       "       'Mitteilungs-Blatt des Jüdischen Volksrats Posen', 'Der Zug',\n",
       "       'Die Jüdische Gemeinde',\n",
       "       'Mitteilungsblatt des Landesverbandes israelitischer Religionsgemeinden Hessens',\n",
       "       \"B'nai B'rith\",\n",
       "       'Mitteilungen des Verbandes Bayerischer Israelitischer Gemeinden',\n",
       "       'Ha- Meʾasef', 'Milgroym',\n",
       "       'Mittheilungen der Oesterreichisch-Israelitischen Union',\n",
       "       'Vereins-Mitteilungen des jüdischen Religions-Vereins Friedenau, Steglitz u. Umgegend, e.V., des jüdischen Frauen-Vereins der südwestl. Vororte, des jüdischen Jugend-Vereins der südwestl. Vororte, des religiösen Vereins zu Steglitz',\n",
       "       'Jüdische Nachrichten', 'Israelitischer Reichs-Bote',\n",
       "       'Israelitisches Predigt-Magazin', 'Jüdische Wochenschau',\n",
       "       'Das Echo', 'Central-Vereins-Dienst', 'Das jüdische Centralblatt',\n",
       "       'Der israelitische Bote', 'Israelitischer Jugendfreund',\n",
       "       'Blätter der Jüdischen Buchvereinigung',\n",
       "       'Gross-Berliner C.V.-Post',\n",
       "       'Einladungsschrift zu der auf den ... festgesetzten öffentlichen Prüfung der Real- und Volksschule der Israelitischen Gemeinde',\n",
       "       'Einladungsschrift zu der am ... stattfindenden öffentlichen Prüfung der Real- und Volksschule der Israelitischen Gemeinde zu Frankfurt am Main',\n",
       "       'Einladungsschrift zu der öffentlichen Prüfung der Bürger- und Realschule der Israelitischen Gemeinde',\n",
       "       'Einladungsschrift zu der auf den ... statthabenden öffentlichen Prüfung in der Real- und Volksschule der Israelitischen Gemeinde',\n",
       "       'Einladung zu der am ... statthabenden öffentlichen Prüfung in der Real- und Volksschule der Israelitischen Gemeinde',\n",
       "       'Einladungsschrift zu der am ... statt habenden öffentlichen Prüfung in der Bürger- und Realschule der Israelitischen Gemeinde',\n",
       "       'Einladung zu der auf den ... festgesetzten öffentlichen Prüfung in der Bürger- und Realschule der Israelitischen Gemeinde',\n",
       "       'Einladung zu der am ... statthabenden öffentlichen Prüfung in der Bürger- und Realschule der Israelitischen Gemeinde',\n",
       "       'Einladungsschrift zu der auf den ... statthabenden öffentlichen Prüfung in der Israelitischen Realschule',\n",
       "       'Einladungsschrift zu der auf den ... festgesetzten öffentlichen Prüfung der Bürger- und Realschule der Israelitischen Gemeinde',\n",
       "       'Einladungsschrift zu der am ... stattfindenden öffentlichen Prüfung der Bürger- und Real-Schule der Israel. Gemeinde',\n",
       "       'Einladungsschrift zu der am stattfindenden öffentlichen Prüfung der Volks- und Realschule der Israelitischen Gemeinde',\n",
       "       'Monatschrift der Oesterreichisch-Israelitischen Union',\n",
       "       'Statuten für die \"Achawa\", Verein zur Unterstützung Hilfsbedürftiger Israelitischer Lehrer, Lehrer-Witwen und -Waisen in Deutschland',\n",
       "       'Financial report of the Palestine Zionist Executive',\n",
       "       'Report to the ... Zionist Congress and to the Council of the Jewish Agency in ...',\n",
       "       'Bericht des Departements für Landwirtschaftliche Kolonisation über die Jahre ... an den ... Zionisten-Kongress und die ... Tagung des Council der Jewish Agency',\n",
       "       'Political report of the Executive of the Jewish Agency submitted to the ... Zionist Congress and the ... session of the Council of the Jewish Agency',\n",
       "       'Mitteilungen des Jüdischen Lehrervereins für Bayern',\n",
       "       'Bericht des Vorstandes der Israelitischen Wohlfahrtskasse zu Frankfurt a.M. (früher \"Israelitische Sterbekasse\")',\n",
       "       'Jüdischer Volksfreund', 'Zuwachsverzeichnis für die Jahre ...',\n",
       "       'Beilage in Wiedergutmachungsfragen',\n",
       "       'Bericht des Vorstandes der Israelitischen Sterbekasse zu Frankfurt am Main',\n",
       "       'Gemeindeblatt für die Jüdischen Gemeinden Preussens',\n",
       "       'Jüdisches Gemeindeblatt für die Synagogen-Gemeinden in Preussen und Norddeutschland',\n",
       "       'Illustrirter jüdischer Familienkalender',\n",
       "       'Jahresbericht der Jugendgruppe (begründet von der Frauenvereinigung der Frankfurt-Loge) in Frankfurt a. M. für den Zeitraum vom ...',\n",
       "       'Jüdisches Gemeindeblatt für den Verband der Kultusgemeinden in Bayern und die Kultusgemeinden München, Augsburg, Bamberg, Würzburg',\n",
       "       'Aḥiasaf', 'Séance du comité de direction du ...'], dtype=object)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 18
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## Duplikate entfernen\n",
    "Durch die Spalte ZS-Caption sieht man, dass einige Zeitschriften mehrfach vorkommen. Diese Duplikate müssen entfernt werden.\n",
    "\n",
    "Es kommen dann immer noch die Zeitschriften mehrfach vor aber mit unterschiedlichen Heft- und Volumenangaben. Das ist in Ordnung, da es sich um unterschiedliche Ausgaben handelt."
   ],
   "id": "845fd1cf4460897e"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:09:01.236357Z",
     "start_time": "2024-04-29T19:09:00.787741Z"
    }
   },
   "cell_type": "code",
   "source": [
    "cols_to_check = ['Zs_Caption', 'Volume_Caption', 'Heft_Caption', 'Datum']\n",
    "\n",
    "# Remove duplicates from specific columns\n",
    "df_metadata = df_metadata.drop_duplicates(subset=cols_to_check)"
   ],
   "id": "40604efee7f863af",
   "outputs": [],
   "execution_count": 19
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:09:01.770580Z",
     "start_time": "2024-04-29T19:09:01.757876Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.shape[0]",
   "id": "f5ef231087ced7a2",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55300"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 20
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:09:02.101803Z",
     "start_time": "2024-04-29T19:09:02.095155Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata.sample(5)",
   "id": "d4427832ed1f05d0",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                      Zs_Caption Volume_Caption           Heft_Caption  \\\n",
       "764400      Handwerk und Gewerbe       8 (1835)  1. u. 2. Oktober-Heft   \n",
       "257374           Jüdische Presse       8 (1835)         17 (23.4.1926)   \n",
       "776487  Der israelitische Lehrer       8 (1835)         45 (5.10.1863)   \n",
       "740423       Die jüdische Presse       8 (1835)         11 (18.3.1886)   \n",
       "100752      Neue Nationalzeitung       8 (1835)    Heft 32 (14.8.1908)   \n",
       "\n",
       "             Datum  \n",
       "764400  1915-10-01  \n",
       "257374  1926-04-23  \n",
       "776487  1863-10-05  \n",
       "740423  1886-01-01  \n",
       "100752  1908-08-14  "
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Datum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>764400</th>\n",
       "      <td>Handwerk und Gewerbe</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>1. u. 2. Oktober-Heft</td>\n",
       "      <td>1915-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257374</th>\n",
       "      <td>Jüdische Presse</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>17 (23.4.1926)</td>\n",
       "      <td>1926-04-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>776487</th>\n",
       "      <td>Der israelitische Lehrer</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>45 (5.10.1863)</td>\n",
       "      <td>1863-10-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>740423</th>\n",
       "      <td>Die jüdische Presse</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>11 (18.3.1886)</td>\n",
       "      <td>1886-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100752</th>\n",
       "      <td>Neue Nationalzeitung</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 32 (14.8.1908)</td>\n",
       "      <td>1908-08-14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 21
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### Show the dataframe",
   "id": "8b0c8bdc8b4075d1"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:09:06.860788Z",
     "start_time": "2024-04-29T19:09:06.849626Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata",
   "id": "95eb56fdbfc77788",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                               Zs_Caption  \\\n",
       "0       ... Bericht ueber den Verein für die Provinz W...   \n",
       "93      Führer durch die jüdische Gemeindeverwaltung u...   \n",
       "687     Sechzehnter Bericht über die Religionsschule d...   \n",
       "724     ... Bericht ueber den Verein für Westfalen und...   \n",
       "881     ... Verwaltungsbericht des Haupt-Grenz-Comité'...   \n",
       "...                                                   ...   \n",
       "917902               Séance du comité de direction du ...   \n",
       "917948               Séance du comité de direction du ...   \n",
       "918012               Séance du comité de direction du ...   \n",
       "918068               Séance du comité de direction du ...   \n",
       "918114               Séance du comité de direction du ...   \n",
       "\n",
       "              Volume_Caption Heft_Caption       Datum  \n",
       "0                   8 (1835)          NaN  1835-01-01  \n",
       "93                   1932-33          NaN  1932-01-01  \n",
       "687                        0          NaN  1871-01-01  \n",
       "724                 9 (1836)          NaN  1836-01-01  \n",
       "881     Erstes Semester 1870          NaN  1870-01-01  \n",
       "...                      ...          ...         ...  \n",
       "917902                  1910          NaN  1910-01-01  \n",
       "917948                  1911          NaN  1911-01-01  \n",
       "918012                  1912          NaN  1912-01-01  \n",
       "918068                  1913          NaN  1913-01-01  \n",
       "918114                  1914          NaN  1914-01-01  \n",
       "\n",
       "[55300 rows x 4 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Datum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Führer durch die jüdische Gemeindeverwaltung u...</td>\n",
       "      <td>1932-33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1932-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>Sechzehnter Bericht über die Religionsschule d...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1871-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>9 (1836)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1836-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881</th>\n",
       "      <td>... Verwaltungsbericht des Haupt-Grenz-Comité'...</td>\n",
       "      <td>Erstes Semester 1870</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1870-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917902</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1910</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1910-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917948</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1911</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1911-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918012</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1912</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1912-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918068</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1913</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1913-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918114</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1914-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>55300 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 22
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "So viele Zeitschriften sind dennoch mehrfach vorhanden.",
   "id": "a3bcb09bbea4b3a0"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:09:41.440099Z",
     "start_time": "2024-04-29T19:09:41.426309Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# count duplicate rows in 'Zs_Caption'\n",
    "df_metadata['Zs_Caption'].duplicated().sum()"
   ],
   "id": "ed05697dd9a42f54",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "54992"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 23
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "## Clean up the titles\n",
    "\n",
    "Wir ersetzen die Zeichen `...` durch `*` und entfernen führende und abschließende Leerzeichen.\n",
    "Die ... waren in den Titeln wohl Platzhalter für Datumsangaben, die nicht mehr vorhanden sind.\n",
    "Durch das Ersetzen mit einer Wildcard können wir die Titel besser finden."
   ],
   "id": "881cce9ecfda9bf9"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:11:08.599105Z",
     "start_time": "2024-04-29T19:11:08.535084Z"
    }
   },
   "cell_type": "code",
   "source": [
    "#df_metadata['Zs_Caption'] = df_metadata['Zs_Caption'].str.replace(\"\\.\\.\\.\", \"*\", regex=True)\n",
    "df_metadata['Zs_Caption'] = df_metadata['Zs_Caption'].str.strip()"
   ],
   "id": "c9e0d2a8c423bc0c",
   "outputs": [],
   "execution_count": 24
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# remove all nan values\n",
    "df_metadata = df_metadata.fillna(\"\")\n",
    "df_metadata = df_metadata.replace('nan', '')\n",
    "df_metadata"
   ],
   "id": "a20f67163cfab4c2",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Bereinigung der Spalten",
   "id": "8cc5aa87289a4292"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Jahr aus Datum extrahieren",
   "id": "99f3e0395577a710"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:18:54.540091Z",
     "start_time": "2024-04-29T19:18:54.457541Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# take the Datum as datetime and extract the year\n",
    "df_metadata['Datum'].replace({'0001-01-07': '1896-01-07'}, inplace=True) # ist ein Fehler in den Daten\n",
    "df_metadata['Datum'] = pd.to_datetime(df_metadata['Datum'], errors='coerce')\n",
    "df_metadata['Year'] = pd.to_numeric(df_metadata['Datum'].dt.year)\n",
    "df_metadata.sample(5)"
   ],
   "id": "387b1a9075797b17",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                    Zs_Caption Volume_Caption         Heft_Caption      Datum  \\\n",
       "83759                Palästina       8 (1835)             Heft 6-7 1927-01-01   \n",
       "292149      Jüdische Rundschau       8 (1835)  Heft 14 (17.2.1928) 1928-02-17   \n",
       "254381  Der treue Zionswächter       8 (1835)       24 (15.6.1847) 1847-06-15   \n",
       "508578            Die Wahrheit       8 (1835)  Heft 34 (19.8.1932) 1932-08-19   \n",
       "216822  Central-Verein-Zeitung       8 (1835)   Heft 6 (Juni 1926) 1926-06-01   \n",
       "\n",
       "          Year  \n",
       "83759   1927.0  \n",
       "292149  1928.0  \n",
       "254381  1847.0  \n",
       "508578  1932.0  \n",
       "216822  1926.0  "
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>83759</th>\n",
       "      <td>Palästina</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 6-7</td>\n",
       "      <td>1927-01-01</td>\n",
       "      <td>1927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292149</th>\n",
       "      <td>Jüdische Rundschau</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 14 (17.2.1928)</td>\n",
       "      <td>1928-02-17</td>\n",
       "      <td>1928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254381</th>\n",
       "      <td>Der treue Zionswächter</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>24 (15.6.1847)</td>\n",
       "      <td>1847-06-15</td>\n",
       "      <td>1847.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508578</th>\n",
       "      <td>Die Wahrheit</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 34 (19.8.1932)</td>\n",
       "      <td>1932-08-19</td>\n",
       "      <td>1932.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216822</th>\n",
       "      <td>Central-Verein-Zeitung</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>Heft 6 (Juni 1926)</td>\n",
       "      <td>1926-06-01</td>\n",
       "      <td>1926.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 29
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:18:58.298084Z",
     "start_time": "2024-04-29T19:18:58.290451Z"
    }
   },
   "cell_type": "code",
   "source": "df_metadata",
   "id": "4adfd842a6724f07",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                               Zs_Caption  \\\n",
       "0       ... Bericht ueber den Verein für die Provinz W...   \n",
       "93      Führer durch die jüdische Gemeindeverwaltung u...   \n",
       "687     Sechzehnter Bericht über die Religionsschule d...   \n",
       "724     ... Bericht ueber den Verein für Westfalen und...   \n",
       "881     ... Verwaltungsbericht des Haupt-Grenz-Comité'...   \n",
       "...                                                   ...   \n",
       "917902               Séance du comité de direction du ...   \n",
       "917948               Séance du comité de direction du ...   \n",
       "918012               Séance du comité de direction du ...   \n",
       "918068               Séance du comité de direction du ...   \n",
       "918114               Séance du comité de direction du ...   \n",
       "\n",
       "              Volume_Caption Heft_Caption      Datum    Year  \n",
       "0                   8 (1835)          NaN 1835-01-01  1835.0  \n",
       "93                   1932-33          NaN 1932-01-01  1932.0  \n",
       "687                        0          NaN 1871-01-01  1871.0  \n",
       "724                 9 (1836)          NaN 1836-01-01  1836.0  \n",
       "881     Erstes Semester 1870          NaN 1870-01-01  1870.0  \n",
       "...                      ...          ...        ...     ...  \n",
       "917902                  1910          NaN 1910-01-01  1910.0  \n",
       "917948                  1911          NaN 1911-01-01  1911.0  \n",
       "918012                  1912          NaN 1912-01-01  1912.0  \n",
       "918068                  1913          NaN 1913-01-01  1913.0  \n",
       "918114                  1914          NaN 1914-01-01  1914.0  \n",
       "\n",
       "[55300 rows x 5 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Volume_Caption</th>\n",
       "      <th>Heft_Caption</th>\n",
       "      <th>Datum</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>8 (1835)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>1835.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Führer durch die jüdische Gemeindeverwaltung u...</td>\n",
       "      <td>1932-33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1932-01-01</td>\n",
       "      <td>1932.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>Sechzehnter Bericht über die Religionsschule d...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1871-01-01</td>\n",
       "      <td>1871.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>9 (1836)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1836-01-01</td>\n",
       "      <td>1836.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881</th>\n",
       "      <td>... Verwaltungsbericht des Haupt-Grenz-Comité'...</td>\n",
       "      <td>Erstes Semester 1870</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1870-01-01</td>\n",
       "      <td>1870.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917902</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1910</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1910-01-01</td>\n",
       "      <td>1910.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917948</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1911</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1911-01-01</td>\n",
       "      <td>1911.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918012</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1912</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1912-01-01</td>\n",
       "      <td>1912.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918068</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1913</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1913-01-01</td>\n",
       "      <td>1913.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918114</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1914-01-01</td>\n",
       "      <td>1914.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>55300 rows × 5 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 30
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## XXX Ausgabe der Zeitschriften",
   "id": "fa882640795401c5"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "df_metadata['Volume_Caption'].unique().tolist()",
   "id": "cda89e721922ee1b",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "1ae0491ac8692965"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Convert 'Year' column to string for comparison\n",
    "df_metadata['Year'] = df_metadata['Year'].astype(str)\n",
    "\n",
    "# Initialize an empty list to store tuples of values and corresponding years\n",
    "values_and_years_to_print = []\n",
    "\n",
    "# Iterate over each row in the DataFrame\n",
    "for index, row in df_metadata.iterrows():\n",
    "    # Check if the year from 'Year' column is included in the 'Volume_Caption' column\n",
    "    if row['Year'] not in row['Volume_Caption']:\n",
    "        # If the year is not included, append a tuple of (value, year) to the list\n",
    "        values_and_years_to_print.append((row['Volume_Caption'], row['Year']))\n",
    "\n",
    "# Print the values and corresponding years\n",
    "for value, year in values_and_years_to_print:\n",
    "    print(f\"Volume Caption: {value}, Year: {year}\")\n",
    "\n"
   ],
   "id": "ae2eca4b030de625",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## XXX Ausgabe der Hefte",
   "id": "6739717526d137ea"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# list all unique values in 'Heft_Caption'\n",
    "df_metadata['Heft_Caption'].unique().tolist()\n"
   ],
   "id": "3b750ba0a83f5f21",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Extract the year from 'Heft_Caption' column\n",
    "year_regex = r'(?:Heft \\d{4}\\D*)(\\d{4})|\\b(\\d{4})\\b'\n",
    "extracted_years = df_metadata['Heft_Caption'].str.extract(year_regex)\n",
    "\n",
    "# Apply lambda function to handle the extracted year values\n",
    "df_metadata['Heft_Year'] = extracted_years.apply(lambda x: x[0] if not pd.isnull(x[0]) else x[1], axis=1)\n",
    "\n",
    "df_metadata.sample(5)"
   ],
   "id": "84cc2bce3f6cc5a3",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## XXX Check if the years in 'Heft_Year' and 'Year' are different",
   "id": "854b008513791a6c"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# Check if 'Heft_Year' and 'Year' contain different values\n",
    "#  Replace non-finite values in 'Year' with an empty string\n",
    "df_metadata['Year'].fillna(\"\", inplace=True)\n",
    "\n",
    "# Convert 'Year' to string\n",
    "df_metadata['Year'] = df_metadata['Year'].astype(str)\n",
    "df_metadata['Heft_Year'] = df_metadata['Heft_Year'].astype(str)\n",
    "\n",
    "# Check if 'Heft_Year' and 'Year' contain different values\n",
    "different_values_mask = df_metadata['Heft_Year'] != df_metadata['Year']\n",
    "\n",
    "# Print rows where the values are different\n",
    "df_metadata[different_values_mask]\n"
   ],
   "id": "9e2ba388f5ca8d81",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "df_metadata['Year'].dtype",
   "id": "6e823d3b537518eb",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Aggregation der Metadaten",
   "id": "1b8d5365d730c06e"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "Die Title werden zusammengeführt und die frühesten und spätesten Jahre und Daten ermittelt.\n",
    "Lücken werden dabei nicht berücksichtigt.\n",
    "Die Daten aus der ZDB sind auch nicht so detailiert, dass sie die einzelnen Ausgaben und Jahrgänge enthalten."
   ],
   "id": "cfb376fd73248721"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:19:44.276344Z",
     "start_time": "2024-04-29T19:19:43.422501Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Step 1: Create a new DataFrame with unique values from 'Zs_Caption'\n",
    "unique_titles_df = pd.DataFrame(df_metadata['Zs_Caption'].unique(), columns=['Zs_Caption'])\n",
    "\n",
    "# Step 2-4: Iterate over each unique title, find earliest and latest year, and store in a new DataFrame\n",
    "result_rows = []\n",
    "for title in unique_titles_df['Zs_Caption']:\n",
    "    # Filter df_metadata for the current title\n",
    "    title_metadata = df_metadata[df_metadata['Zs_Caption'] == title]\n",
    "    # Find the earliest and latest year\n",
    "    year_from = title_metadata['Year'].min()\n",
    "    year_to = title_metadata['Year'].max()\n",
    "    # Append the result as a new row\n",
    "    result_rows.append({'Zs_Caption': title, 'Year_From': year_from, 'Year_To': year_to})\n",
    "\n",
    "# Create a new DataFrame with the results\n",
    "result_df = pd.DataFrame(result_rows)\n",
    "\n",
    "# Convert string representations of \"nan\" to actual NaN values\n",
    "result_df['Year_To'] = result_df['Year_To'].replace('nan', np.nan)\n",
    "\n",
    "# Replace NaN values and missing values in 'Year_To' with the corresponding values from 'Year_From'\n",
    "result_df['Year_To'].fillna(result_df['Year_From'], inplace=True)\n",
    "\n",
    "result_df['Year_From'] = pd.to_numeric(result_df['Year_From'], errors='coerce').fillna(0).astype(int)\n",
    "result_df['Year_To'] = pd.to_numeric(result_df['Year_To'], errors='coerce').fillna(0).astype(int)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "result_df"
   ],
   "id": "4f677b43266ab2a1",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  Year_From  Year_To\n",
       "0    ... Bericht ueber den Verein für die Provinz W...       1835     1835\n",
       "1    Führer durch die jüdische Gemeindeverwaltung u...       1932     1932\n",
       "2    Sechzehnter Bericht über die Religionsschule d...       1871     1871\n",
       "3    ... Bericht ueber den Verein für Westfalen und...       1836     1836\n",
       "4    ... Verwaltungsbericht des Haupt-Grenz-Comité'...       1870     1870\n",
       "..                                                 ...        ...      ...\n",
       "303            Illustrirter jüdischer Familienkalender       1881     1907\n",
       "304  Jahresbericht der Jugendgruppe (begründet von ...       1913     1913\n",
       "305  Jüdisches Gemeindeblatt für den Verband der Ku...       1937     1938\n",
       "306                                            Aḥiasaf       1893     1922\n",
       "307               Séance du comité de direction du ...       1907     1914\n",
       "\n",
       "[308 rows x 3 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>1835</td>\n",
       "      <td>1835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Führer durch die jüdische Gemeindeverwaltung u...</td>\n",
       "      <td>1932</td>\n",
       "      <td>1932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sechzehnter Bericht über die Religionsschule d...</td>\n",
       "      <td>1871</td>\n",
       "      <td>1871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>1836</td>\n",
       "      <td>1836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Verwaltungsbericht des Haupt-Grenz-Comité'...</td>\n",
       "      <td>1870</td>\n",
       "      <td>1870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>Illustrirter jüdischer Familienkalender</td>\n",
       "      <td>1881</td>\n",
       "      <td>1907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>Jahresbericht der Jugendgruppe (begründet von ...</td>\n",
       "      <td>1913</td>\n",
       "      <td>1913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>Jüdisches Gemeindeblatt für den Verband der Ku...</td>\n",
       "      <td>1937</td>\n",
       "      <td>1938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>1893</td>\n",
       "      <td>1922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>1907</td>\n",
       "      <td>1914</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>308 rows × 3 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 31
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:20:09.247157Z",
     "start_time": "2024-04-29T19:20:08.057343Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Iterate over each unique title in the 'Zs_Caption' column\n",
    "for title in unique_titles_df['Zs_Caption']:\n",
    "    # Filter df_metadata for the current title\n",
    "    title_metadata = df_metadata[df_metadata['Zs_Caption'] == title]\n",
    "    # Find the earliest and latest date\n",
    "    date_from = title_metadata['Datum'].min()\n",
    "    date_to = title_metadata['Datum'].max()\n",
    "    # Append the result as a new row to the existing results_df\n",
    "    result_df = result_df.append({'Zs_Caption': title, 'Date_From': date_from, 'Date_To': date_to}, ignore_index=True)\n",
    "\n",
    "# Step 5: Replace NaN values in 'Date_To' with values from 'Date_From'\n",
    "result_df['Date_To'].fillna(result_df['Date_From'], inplace=True)\n",
    "\n",
    "# Step 6: Convert 'Date_From' and 'Date_To' to datetime\n",
    "result_df['Date_From'] = pd.to_datetime(result_df['Date_From'])\n",
    "result_df['Date_To'] = pd.to_datetime(result_df['Date_To'])\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "result_df\n"
   ],
   "id": "8fa975b4a83b98c9",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  Year_From  Year_To  \\\n",
       "0    ... Bericht ueber den Verein für die Provinz W...     1835.0   1835.0   \n",
       "1    Führer durch die jüdische Gemeindeverwaltung u...     1932.0   1932.0   \n",
       "2    Sechzehnter Bericht über die Religionsschule d...     1871.0   1871.0   \n",
       "3    ... Bericht ueber den Verein für Westfalen und...     1836.0   1836.0   \n",
       "4    ... Verwaltungsbericht des Haupt-Grenz-Comité'...     1870.0   1870.0   \n",
       "..                                                 ...        ...      ...   \n",
       "611            Illustrirter jüdischer Familienkalender        NaN      NaN   \n",
       "612  Jahresbericht der Jugendgruppe (begründet von ...        NaN      NaN   \n",
       "613  Jüdisches Gemeindeblatt für den Verband der Ku...        NaN      NaN   \n",
       "614                                            Aḥiasaf        NaN      NaN   \n",
       "615               Séance du comité de direction du ...        NaN      NaN   \n",
       "\n",
       "     Date_From    Date_To  \n",
       "0          NaT        NaT  \n",
       "1          NaT        NaT  \n",
       "2          NaT        NaT  \n",
       "3          NaT        NaT  \n",
       "4          NaT        NaT  \n",
       "..         ...        ...  \n",
       "611 1881-01-01 1907-01-01  \n",
       "612 1913-01-01 1913-01-01  \n",
       "613 1937-08-01 1938-11-01  \n",
       "614 1893-01-01 1922-01-01  \n",
       "615 1907-01-01 1914-01-01  \n",
       "\n",
       "[616 rows x 5 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Date_From</th>\n",
       "      <th>Date_To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Führer durch die jüdische Gemeindeverwaltung u...</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sechzehnter Bericht über die Religionsschule d...</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Verwaltungsbericht des Haupt-Grenz-Comité'...</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>611</th>\n",
       "      <td>Illustrirter jüdischer Familienkalender</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1881-01-01</td>\n",
       "      <td>1907-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Jahresbericht der Jugendgruppe (begründet von ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1913-01-01</td>\n",
       "      <td>1913-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Jüdisches Gemeindeblatt für den Verband der Ku...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1937-08-01</td>\n",
       "      <td>1938-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1907-01-01</td>\n",
       "      <td>1914-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>616 rows × 5 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 32
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:20:15.860431Z",
     "start_time": "2024-04-29T19:20:15.802540Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Merge unique_titles_df with df_metadata to get dates for each Zs_Caption\n",
    "merged_df = pd.merge(unique_titles_df, df_metadata[['Zs_Caption', 'Datum']], on='Zs_Caption', how='left')\n",
    "\n",
    "# Group by Zs_Caption and aggregate to find the earliest and latest dates\n",
    "aggregated_df = merged_df.groupby('Zs_Caption').agg(Date_From=('Datum', 'min'), Date_To=('Datum', 'max')).reset_index()\n",
    "\n",
    "# Replace NaN values in 'Date_To' with values from 'Date_From'\n",
    "aggregated_df['Date_To'].fillna(aggregated_df['Date_From'], inplace=True)\n",
    "\n",
    "# Convert 'Date_From' and 'Date_To' to datetime\n",
    "aggregated_df['Date_From'] = pd.to_datetime(aggregated_df['Date_From'])\n",
    "aggregated_df['Date_To'] = pd.to_datetime(aggregated_df['Date_To'])\n",
    "\n",
    "# Update result_df with the aggregated dates\n",
    "result_df.update(aggregated_df)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "result_df"
   ],
   "id": "7fcee76af8c53fbb",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Zs_Caption  Year_From  Year_To  \\\n",
       "0    ... Bericht der Jüdischen Haushaltungsschule z...     1835.0   1835.0   \n",
       "1    ... Bericht ueber den Verein für Westfalen und...     1932.0   1932.0   \n",
       "2    ... Bericht ueber den Verein für die Provinz W...     1871.0   1871.0   \n",
       "3    ... Jahresbericht der Israelitischen Waisenans...     1836.0   1836.0   \n",
       "4    ... Jahresbericht der Jüdischen Frauenvereinig...     1870.0   1870.0   \n",
       "..                                                 ...        ...      ...   \n",
       "611            Illustrirter jüdischer Familienkalender        NaN      NaN   \n",
       "612  Jahresbericht der Jugendgruppe (begründet von ...        NaN      NaN   \n",
       "613  Jüdisches Gemeindeblatt für den Verband der Ku...        NaN      NaN   \n",
       "614                                            Aḥiasaf        NaN      NaN   \n",
       "615               Séance du comité de direction du ...        NaN      NaN   \n",
       "\n",
       "     Date_From    Date_To  \n",
       "0   1821-01-01 1910-01-01  \n",
       "1   1836-01-01 1836-01-01  \n",
       "2   1835-01-01 1835-01-01  \n",
       "3   1915-01-01 1917-01-01  \n",
       "4   1855-01-01 1922-01-01  \n",
       "..         ...        ...  \n",
       "611 1881-01-01 1907-01-01  \n",
       "612 1913-01-01 1913-01-01  \n",
       "613 1937-08-01 1938-11-01  \n",
       "614 1893-01-01 1922-01-01  \n",
       "615 1907-01-01 1914-01-01  \n",
       "\n",
       "[616 rows x 5 columns]"
      ],
      "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>Zs_Caption</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Date_From</th>\n",
       "      <th>Date_To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>... Bericht der Jüdischen Haushaltungsschule z...</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1835.0</td>\n",
       "      <td>1821-01-01</td>\n",
       "      <td>1910-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>... Bericht ueber den Verein für Westfalen und...</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1932.0</td>\n",
       "      <td>1836-01-01</td>\n",
       "      <td>1836-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>... Bericht ueber den Verein für die Provinz W...</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1871.0</td>\n",
       "      <td>1835-01-01</td>\n",
       "      <td>1835-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>... Jahresbericht der Israelitischen Waisenans...</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>1836.0</td>\n",
       "      <td>1915-01-01</td>\n",
       "      <td>1917-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>... Jahresbericht der Jüdischen Frauenvereinig...</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1870.0</td>\n",
       "      <td>1855-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>611</th>\n",
       "      <td>Illustrirter jüdischer Familienkalender</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1881-01-01</td>\n",
       "      <td>1907-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Jahresbericht der Jugendgruppe (begründet von ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1913-01-01</td>\n",
       "      <td>1913-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Jüdisches Gemeindeblatt für den Verband der Ku...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1937-08-01</td>\n",
       "      <td>1938-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Aḥiasaf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>1922-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615</th>\n",
       "      <td>Séance du comité de direction du ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1907-01-01</td>\n",
       "      <td>1914-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>616 rows × 5 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 33
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "",
   "id": "d17ab91e7f555e70",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### Löschen von NaN-Werten",
   "id": "f3ede31b1ed6a5d5"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "df_metadata = df_metadata.fillna(\"\")\n",
    "df_metadata = df_metadata.replace('nan', '')\n",
    "\n",
    "result_df = result_df.fillna(\"\")\n",
    "result_df = result_df.replace('nan', '')\n",
    "\n"
   ],
   "id": "462e1e71bd63cc37",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Speichern der aggregierten Metadaten",
   "id": "9795f7f9e2cb5864"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-04-29T19:20:27.413629Z",
     "start_time": "2024-04-29T19:20:27.383739Z"
    }
   },
   "cell_type": "code",
   "source": "result_df.to_csv(\"metadata/CM_Seiten_Metadaten_aggregated.csv\", sep=\"\\t\", index=False)",
   "id": "10581c494cafa24b",
   "outputs": [],
   "execution_count": 34
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Save the cleaned metadata",
   "id": "cc33cb8c5280689f"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "df_metadata.to_csv(\"metadata/CM_Seiten_Metadaten_cleaned.csv\", sep=\"\\t\", index=False)",
   "id": "6a4e38288bbe4668",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### Speichern der Zeitschriftentitel in einer Textdatei",
   "id": "be1e55c7e4d3f6b7"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "with open(\"metadata/journal_titles.txt\", \"w\") as f:\n",
    "    for title in df_metadata['Zs_Caption'].unique():\n",
    "        # remove leading and trailing whitespaces\n",
    "        title = title.strip()\n",
    "        f.write(f\"{title}\\n\")\n"
   ],
   "id": "8e15a8a82fb45a4f",
   "outputs": [],
   "execution_count": null
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}