Serebrennikov_Create_unite_df.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import shutil\n",
    "import string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(r'C:/EU-Study/Pandan/JT_project/Лудза.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Создаем df для записи в него папки происхождения изображения, его оригинального имени и имени, усеченного до идентификационного номера\n",
    "\n",
    "df_old_path = pd.DataFrame(columns=['folder',\n",
    "                                    'im_name',\n",
    "                                    'id']) \n",
    "\n",
    "# Копируем все изображения из одной папки в другую, сохраняя их метаданные в таблицу\n",
    "\n",
    "old_path = 'C:/EU-Study/Pandan/JT_project/old/'\n",
    "new_path = 'C:/EU-Study/Pandan/JT_project/new/'\n",
    "\n",
    "folders = os.listdir(path=old_path)\n",
    "\n",
    "for f in folders:\n",
    "    im_list = os.listdir(path=old_path+f)\n",
    "\n",
    "    for im in im_list:\n",
    "        \n",
    "        # Многие надгробия снимаются по нескольку раз и в названиях им делают постфиксы \"_1\"|\"_2\" или \"a\"|\"b\", но\n",
    "        # в таблицах с переводами они значатся без них. соответственно для мёрджа нужно их убрать:\n",
    "        im_id = im.split('.')[0].split('_')[0]\n",
    "        im_id = im_id.rstrip(string.ascii_letters + string.whitespace)        \n",
    "\n",
    "        src_dir = old_path+str(f)+'/'+str(im)\n",
    "        dst_dir = new_path+str(im)\n",
    "        shutil.copy(src_dir,dst_dir)\n",
    "\n",
    "        df_old_path = df_old_path.append({'folder': f,\n",
    "                                          'im_name': im.split('.')[0],\n",
    "                                          'id': im_id}, ignore_index=True)  \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Создаем общую таблицу для таблиц переводов текстов с надгробий\n",
    "\n",
    "df_text = pd.DataFrame(columns=['id', 'jewish_text', 'rus_transl'])\n",
    "\n",
    "fileDir = r\"C:/EU-Study/Pandan/JT_project/\"\n",
    "file_list = [_ for _ in os.listdir(fileDir) if _.endswith(\".xlsx\")]\n",
    "file_list.extend([_ for _ in os.listdir(fileDir) if _.endswith(\".xls\")])\n",
    "file_list.remove('Оргеев_координаты.xlsx')\n",
    "\n",
    "\n",
    "for f in file_list:\n",
    "    \n",
    "    df_temp = pd.read_excel(r'C:/EU-Study/Pandan/JT_project/'+f)\n",
    "    df_temp.columns = ['id', 'jewish_text', 'rus_transl']\n",
    "    \n",
    "    df_text = df_text.append(df_temp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>jewish_text</th>\n",
       "      <th>rus_transl</th>\n",
       "      <th>folder</th>\n",
       "      <th>im_name</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORG001</td>\n",
       "      <td>פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...</td>\n",
       "      <td>Здесь похоронен  мужчина, р.  Яаков, сын р.  М...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG001</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORG001</td>\n",
       "      <td>פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...</td>\n",
       "      <td>Здесь похоронен  мужчина, р.  Яаков, сын р.  М...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG001_1</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORG002</td>\n",
       "      <td>פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...</td>\n",
       "      <td>Здесь похоронен  почтенный человек  р. Ицхак  ...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG002</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORG002</td>\n",
       "      <td>פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...</td>\n",
       "      <td>Здесь похоронен  почтенный человек  р. Ицхак  ...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG002_1</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORG002</td>\n",
       "      <td>פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...</td>\n",
       "      <td>Здесь похоронен  почтенный человек  р. Ицхак  ...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG002_3</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6104</th>\n",
       "      <td>SDB088</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Стародуб</td>\n",
       "      <td>SDB088_2</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6105</th>\n",
       "      <td>SDB089</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Стародуб</td>\n",
       "      <td>SDB089</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6106</th>\n",
       "      <td>SDB089</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Стародуб</td>\n",
       "      <td>SDB089a</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6107</th>\n",
       "      <td>SDB098</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Стародуб</td>\n",
       "      <td>SDB098</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6108</th>\n",
       "      <td>SDB099</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Стародуб</td>\n",
       "      <td>SDB099</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6109 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                        jewish_text  \\\n",
       "0     ORG001  פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...   \n",
       "1     ORG001  פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...   \n",
       "2     ORG002  פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...   \n",
       "3     ORG002  פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...   \n",
       "4     ORG002  פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...   \n",
       "...      ...                                                ...   \n",
       "6104  SDB088                                                NaN   \n",
       "6105  SDB089                                                NaN   \n",
       "6106  SDB089                                                NaN   \n",
       "6107  SDB098                                                NaN   \n",
       "6108  SDB099                                                NaN   \n",
       "\n",
       "                                             rus_transl    folder   im_name  \\\n",
       "0     Здесь похоронен  мужчина, р.  Яаков, сын р.  М...    Оргеев    ORG001   \n",
       "1     Здесь похоронен  мужчина, р.  Яаков, сын р.  М...    Оргеев  ORG001_1   \n",
       "2     Здесь похоронен  почтенный человек  р. Ицхак  ...    Оргеев    ORG002   \n",
       "3     Здесь похоронен  почтенный человек  р. Ицхак  ...    Оргеев  ORG002_1   \n",
       "4     Здесь похоронен  почтенный человек  р. Ицхак  ...    Оргеев  ORG002_3   \n",
       "...                                                 ...       ...       ...   \n",
       "6104                                                NaN  Стародуб  SDB088_2   \n",
       "6105                                                NaN  Стародуб    SDB089   \n",
       "6106                                                NaN  Стародуб   SDB089a   \n",
       "6107                                                NaN  Стародуб    SDB098   \n",
       "6108                                                NaN  Стародуб    SDB099   \n",
       "\n",
       "          _merge  \n",
       "0           both  \n",
       "1           both  \n",
       "2           both  \n",
       "3           both  \n",
       "4           both  \n",
       "...          ...  \n",
       "6104  right_only  \n",
       "6105  right_only  \n",
       "6106  right_only  \n",
       "6107  right_only  \n",
       "6108  right_only  \n",
       "\n",
       "[6109 rows x 6 columns]"
      ]
     },
     "execution_count": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Мерджим две части\n",
    "\n",
    "df = pd.merge(df_text, df_old_path, how='outer', indicator=True)\n",
    "df.replace({'-': None},inplace =True)\n",
    "df['rus_transl'] = df['rus_transl'].replace('\\\\n', ' ', regex=True)\n",
    "df['jewish_text'] = df['jewish_text'].replace('\\\\n', ' ', regex=True)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          5319\n",
       "left_only      457\n",
       "right_only     333\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 262,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Посмотрим на результат:\n",
    "\n",
    "df._merge.value_counts()\n",
    "\n",
    "# Слишком много left_only. Ручная проверка показывает, что это в основном те изображения, \n",
    "# у которых в самом названии есть буквы (а мы их убирали выше). В таком случае сделаем отдельный мёрдж по left_only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>jewish_text</th>\n",
       "      <th>rus_transl</th>\n",
       "      <th>folder</th>\n",
       "      <th>im_name</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORG074A</td>\n",
       "      <td>פנ\\n ... שלמה\\n יעקב [שנ]\\n חו ניסן שנת\\n תקפט...</td>\n",
       "      <td>Здесь похоронен  … Шломо  Яаков, [который скон...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORG074B</td>\n",
       "      <td>פנ\\n איש חשב הר\\n שמאל בה יצחק\\n שנ ךב בטבת\\n ...</td>\n",
       "      <td>Здесь похоронен  почтенный человек, р.  Шмуэль...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORG150</td>\n",
       "      <td>…\\n ...לפ\\n תנצבה</td>\n",
       "      <td>…  По малому [летоисчислению].  Да будет душа ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORG170</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORG252A</td>\n",
       "      <td>פנ\\n הילד ישראל\\n בהר דניאל שנ‘\\n יום טז‘ בחו’...</td>\n",
       "      <td>Здесь покоится  ребенок Исраэль,  сын р. Даниэ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>LDZ5158b</td>\n",
       "      <td>פנ\\nהרב\\nמופ חוב יא מו\\nמשה נתן בר דן\\nנ כִד ט...</td>\n",
       "      <td>Здесь похоронен раввин, наш выдающийся, усердн...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ5158b</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>LDZ2054a</td>\n",
       "      <td>פנ\\nהאשה\\nגיטא בת\\nאברהם\\nחייט\\nנפ ב כסלו\\nתרסו</td>\n",
       "      <td>Здесь похоронена женщина Гита, дочь Авраама, Х...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2054a</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>LDZ2054b</td>\n",
       "      <td>פנ\\nחיה שרה\\nבר יעקב\\nקאראקאווסקע\\nנפט ח סיון\\...</td>\n",
       "      <td>Здесь похоронена Хая Сара, дочь р. Яакова, Кар...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2054b</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>LDZ2070a</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2070a</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>LDZ2136a</td>\n",
       "      <td>פנ\\nהבתולה\\nלאה בתר\\nדוב שלמה\\nקיסין מה\\nנפ ה ...</td>\n",
       "      <td>Здесь похоронена девушка Лея, дочь р. Дова Шло...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2136a</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>457 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                        jewish_text  \\\n",
       "0     ORG074A  פנ\\n ... שלמה\\n יעקב [שנ]\\n חו ניסן שנת\\n תקפט...   \n",
       "1     ORG074B  פנ\\n איש חשב הר\\n שמאל בה יצחק\\n שנ ךב בטבת\\n ...   \n",
       "2      ORG150                                  …\\n ...לפ\\n תנצבה   \n",
       "3      ORG170                                                NaN   \n",
       "4     ORG252A  פנ\\n הילד ישראל\\n בהר דניאל שנ‘\\n יום טז‘ בחו’...   \n",
       "..        ...                                                ...   \n",
       "452  LDZ5158b  פנ\\nהרב\\nמופ חוב יא מו\\nמשה נתן בר דן\\nנ כִד ט...   \n",
       "453  LDZ2054a    פנ\\nהאשה\\nגיטא בת\\nאברהם\\nחייט\\nנפ ב כסלו\\nתרסו   \n",
       "454  LDZ2054b  פנ\\nחיה שרה\\nבר יעקב\\nקאראקאווסקע\\nנפט ח סיון\\...   \n",
       "455  LDZ2070a                                               None   \n",
       "456  LDZ2136a  פנ\\nהבתולה\\nלאה בתר\\nדוב שלמה\\nקיסין מה\\nנפ ה ...   \n",
       "\n",
       "                                            rus_transl folder   im_name  \\\n",
       "0    Здесь похоронен  … Шломо  Яаков, [который скон...    NaN       NaN   \n",
       "1    Здесь похоронен  почтенный человек, р.  Шмуэль...    NaN       NaN   \n",
       "2    …  По малому [летоисчислению].  Да будет душа ...    NaN       NaN   \n",
       "3                                                  NaN    NaN       NaN   \n",
       "4    Здесь покоится  ребенок Исраэль,  сын р. Даниэ...    NaN       NaN   \n",
       "..                                                 ...    ...       ...   \n",
       "452  Здесь похоронен раввин, наш выдающийся, усердн...  Лудза  LDZ5158b   \n",
       "453  Здесь похоронена женщина Гита, дочь Авраама, Х...  Лудза  LDZ2054a   \n",
       "454  Здесь похоронена Хая Сара, дочь р. Яакова, Кар...  Лудза  LDZ2054b   \n",
       "455                                               None  Лудза  LDZ2070a   \n",
       "456  Здесь похоронена девушка Лея, дочь р. Дова Шло...  Лудза  LDZ2136a   \n",
       "\n",
       "        _merge  \n",
       "0    left_only  \n",
       "1    left_only  \n",
       "2    left_only  \n",
       "3    left_only  \n",
       "4    left_only  \n",
       "..         ...  \n",
       "452       both  \n",
       "453       both  \n",
       "454       both  \n",
       "455       both  \n",
       "456       both  \n",
       "\n",
       "[457 rows x 6 columns]"
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_with_letter_in_id = df[df._merge == \"left_only\"].drop(['folder', 'im_name', '_merge'], 1)\n",
    "\n",
    "df_with_letter_in_id = pd.merge(df_with_letter_in_id, df_old_path.drop('id', 1), how='left', left_on='id', right_on='im_name', indicator=True)\n",
    "df_with_letter_in_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "left_only     345\n",
       "both          112\n",
       "right_only      0\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Проверим:\n",
    "df_with_letter_in_id._merge.value_counts()\n",
    "\n",
    "# Ручная проверка показывает, что оставшиеся 112 - это те, название которых в таблице не соответствует названию \n",
    "# файла, либо просто такого файла нет, тут уже ничего не поделать."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>jewish_text</th>\n",
       "      <th>rus_transl</th>\n",
       "      <th>folder</th>\n",
       "      <th>im_name</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORG001</td>\n",
       "      <td>פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...</td>\n",
       "      <td>Здесь похоронен  мужчина, р.  Яаков, сын р.  М...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG001</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORG001</td>\n",
       "      <td>פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...</td>\n",
       "      <td>Здесь похоронен  мужчина, р.  Яаков, сын р.  М...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG001_1</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORG002</td>\n",
       "      <td>פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...</td>\n",
       "      <td>Здесь похоронен  почтенный человек  р. Ицхак  ...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG002</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORG002</td>\n",
       "      <td>פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...</td>\n",
       "      <td>Здесь похоронен  почтенный человек  р. Ицхак  ...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG002_1</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORG002</td>\n",
       "      <td>פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...</td>\n",
       "      <td>Здесь похоронен  почтенный человек  р. Ицхак  ...</td>\n",
       "      <td>Оргеев</td>\n",
       "      <td>ORG002_3</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>LDZ5158b</td>\n",
       "      <td>פנ\\nהרב\\nמופ חוב יא מו\\nמשה נתן בר דן\\nנ כִד ט...</td>\n",
       "      <td>Здесь похоронен раввин, наш выдающийся, усердн...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ5158b</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>LDZ2054a</td>\n",
       "      <td>פנ\\nהאשה\\nגיטא בת\\nאברהם\\nחייט\\nנפ ב כסלו\\nתרסו</td>\n",
       "      <td>Здесь похоронена женщина Гита, дочь Авраама, Х...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2054a</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>LDZ2054b</td>\n",
       "      <td>פנ\\nחיה שרה\\nבר יעקב\\nקאראקאווסקע\\nנפט ח סיון\\...</td>\n",
       "      <td>Здесь похоронена Хая Сара, дочь р. Яакова, Кар...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2054b</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>LDZ2070a</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2070a</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>LDZ2136a</td>\n",
       "      <td>פנ\\nהבתולה\\nלאה בתר\\nדוב שלמה\\nקיסין מה\\nנפ ה ...</td>\n",
       "      <td>Здесь похоронена девушка Лея, дочь р. Дова Шло...</td>\n",
       "      <td>Лудза</td>\n",
       "      <td>LDZ2136a</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6109 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                        jewish_text  \\\n",
       "0      ORG001  פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...   \n",
       "1      ORG001  פנ\\n איש הר‘\\n יעקב בהר‘\\n משה מזרחי\\n שנפטר י...   \n",
       "2      ORG002  פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...   \n",
       "3      ORG002  פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...   \n",
       "4      ORG002  פנ\\n איש חשוב\\n הרר יצחק\\n אייזק בהרר\\n יצחק ה...   \n",
       "..        ...                                                ...   \n",
       "452  LDZ5158b  פנ\\nהרב\\nמופ חוב יא מו\\nמשה נתן בר דן\\nנ כִד ט...   \n",
       "453  LDZ2054a    פנ\\nהאשה\\nגיטא בת\\nאברהם\\nחייט\\nנפ ב כסלו\\nתרסו   \n",
       "454  LDZ2054b  פנ\\nחיה שרה\\nבר יעקב\\nקאראקאווסקע\\nנפט ח סיון\\...   \n",
       "455  LDZ2070a                                               None   \n",
       "456  LDZ2136a  פנ\\nהבתולה\\nלאה בתר\\nדוב שלמה\\nקיסין מה\\nנפ ה ...   \n",
       "\n",
       "                                            rus_transl  folder   im_name  \\\n",
       "0    Здесь похоронен  мужчина, р.  Яаков, сын р.  М...  Оргеев    ORG001   \n",
       "1    Здесь похоронен  мужчина, р.  Яаков, сын р.  М...  Оргеев  ORG001_1   \n",
       "2    Здесь похоронен  почтенный человек  р. Ицхак  ...  Оргеев    ORG002   \n",
       "3    Здесь похоронен  почтенный человек  р. Ицхак  ...  Оргеев  ORG002_1   \n",
       "4    Здесь похоронен  почтенный человек  р. Ицхак  ...  Оргеев  ORG002_3   \n",
       "..                                                 ...     ...       ...   \n",
       "452  Здесь похоронен раввин, наш выдающийся, усердн...   Лудза  LDZ5158b   \n",
       "453  Здесь похоронена женщина Гита, дочь Авраама, Х...   Лудза  LDZ2054a   \n",
       "454  Здесь похоронена Хая Сара, дочь р. Яакова, Кар...   Лудза  LDZ2054b   \n",
       "455                                               None   Лудза  LDZ2070a   \n",
       "456  Здесь похоронена девушка Лея, дочь р. Дова Шло...   Лудза  LDZ2136a   \n",
       "\n",
       "    _merge  \n",
       "0     both  \n",
       "1     both  \n",
       "2     both  \n",
       "3     both  \n",
       "4     both  \n",
       "..     ...  \n",
       "452   both  \n",
       "453   both  \n",
       "454   both  \n",
       "455   both  \n",
       "456   both  \n",
       "\n",
       "[6109 rows x 6 columns]"
      ]
     },
     "execution_count": 269,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Соединим два блока:\n",
    "df_final = pd.concat([df[df._merge != \"left_only\"], df_with_letter_in_id])\n",
    "df_final\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "both          5431\n",
       "left_only      345\n",
       "right_only     333\n",
       "Name: _merge, dtype: int64"
      ]
     },
     "execution_count": 270,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final._merge.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Я не стал мёрджить с координатами, т.к. там есть проблемы в несовпадающих id, которые нужно исправлять + их не так много (чуть больше 800). Если необходимо, могу сделать "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('C:/EU-Study/Pandan/JT_project/sefer_unite_df.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}