Regs_code.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2ce478c0-e1f9-42b3-bdb1-5476ad2dca01",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/scratch/nf33/rd7475/ohw24_proj/ohw24_proj_micronekton_img_pipeline_au\n"
     ]
    }
   ],
   "source": [
    "cd '/scratch/nf33/rd7475/ohw24_proj/ohw24_proj_micronekton_img_pipeline_au'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ce81757-7036-4cad-b72b-a118f2d5175b",
   "metadata": {},
   "source": [
    "### Source info DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "23a250a9-47af-490c-8c37-4fa5581009a3",
   "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>Station</th>\n",
       "      <th>Photo</th>\n",
       "      <th>Source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL00001</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL00002</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL00003</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL00004</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL00005</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5447</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL09995</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5448</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL09996</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5449</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL09997</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5450</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL09998</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5451</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL09999</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5452 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Station     Photo                                             Source\n",
       "0         01  OBL00001  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "1         01  OBL00002  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "2         01  OBL00003  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "3         01  OBL00004  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "4         01  OBL00005  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "...      ...       ...                                                ...\n",
       "5447      02  OBL09995  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "5448      02  OBL09996  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "5449      02  OBL09997  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "5450      02  OBL09998  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "5451      02  OBL09999  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...\n",
       "\n",
       "[5452 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pathlib import Path\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re\n",
    "import time\n",
    "\n",
    "def extract_data(pattern, file_paths):\n",
    "    \"\"\"Extract unique numeric data from file paths based on regex pattern.\"\"\"\n",
    "    data = []\n",
    "    seen = set()\n",
    "    for path in file_paths:\n",
    "        path = str(path)\n",
    "        match = re.search(pattern, path)\n",
    "        if match:\n",
    "            num = int(match.group(1))  # Change to float(match.group(1)) if needed\n",
    "            if num not in seen:\n",
    "                seen.add(num)\n",
    "                data.append(num)\n",
    "    return sorted(data)\n",
    "\n",
    "####################################################################################\n",
    "\n",
    "# # Define source path\n",
    "# source_path = Path('/Volumes/csiro_data/IN2020_V08/PLAOS/Data/')\n",
    "\n",
    "# # Get relevant file paths\n",
    "# relevent_paths = list(source_path.rglob('Station_*/**/OBL*.JPG'))\n",
    "\n",
    "####################################################################################\n",
    "\n",
    "import pickle\n",
    "with open('relevent_paths.pkl', 'rb') as file:\n",
    "    relevent_paths = pickle.load(file)\n",
    "\n",
    "####################################################################################\n",
    "\n",
    "relevent_paths_str = []\n",
    "for p in relevent_paths:\n",
    "    relevent_paths_str.append(str(p))\n",
    "\n",
    "# Extract station numbers\n",
    "station_numbers = extract_data(pattern=r'Station_(\\d+)', file_paths=relevent_paths)\n",
    "\n",
    "# CAP NUMBER OF STATIONS FOR THE MOMENT!\n",
    "station_numbers = [f'{num:02}' for num in np.arange(1, 3)]\n",
    "\n",
    "# Prepare lists to store data\n",
    "img_input_path = []\n",
    "S = []\n",
    "I = []\n",
    "\n",
    "# Process each station\n",
    "for station in station_numbers:\n",
    "\n",
    "    station_paths = []\n",
    "    for path in relevent_paths_str:\n",
    "        # Format the station number as a two-digit string\n",
    "        station_str = f'{station:02}'\n",
    "        # Construct the station path with the formatted station number\n",
    "        station_path = f'Station_{station_str}'\n",
    "        # Check if the formatted station path is in the path\n",
    "        if station_path in path:\n",
    "            station_paths.append(path)\n",
    "\n",
    "    # Extract image numbers for the current station\n",
    "    img_numbers = extract_data(pattern=r'OBL(\\d+)', file_paths=station_paths)\n",
    "\n",
    "    # Process each image number\n",
    "    for img_number in img_numbers:\n",
    "\n",
    "        img_number = f'{img_number:05}'\n",
    "\n",
    "        img_path = [s for s in station_paths if 'OBL'+img_number in s]\n",
    "\n",
    "        img_input_path.append(str(img_path[0]))\n",
    "        S.append(station)\n",
    "        I.append('OBL'+img_number)\n",
    "\n",
    "M = img_input_path\n",
    "\n",
    "# Create a DataFrame\n",
    "df = pd.DataFrame({\n",
    "    'Station': S,\n",
    "    'Photo': I,\n",
    "    'Source': M\n",
    "})\n",
    "\n",
    "source_info = df\n",
    "source_info"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c769b08-f53a-4d24-916b-1f029b5b208d",
   "metadata": {},
   "source": [
    "### Exif info DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d1a2a68e-b90f-4297-8d06-e6537d680619",
   "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>Station</th>\n",
       "      <th>Photo</th>\n",
       "      <th>Date</th>\n",
       "      <th>Time</th>\n",
       "      <th>AMPM</th>\n",
       "      <th>Photo_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09618</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>06:54:03</td>\n",
       "      <td>AM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09619</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>06:54:05</td>\n",
       "      <td>AM</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09620</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>06:54:07</td>\n",
       "      <td>AM</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09621</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>06:54:09</td>\n",
       "      <td>AM</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09622</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>06:54:11</td>\n",
       "      <td>AM</td>\n",
       "      <td>5</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>5818</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01531</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>13:09:10</td>\n",
       "      <td>PM</td>\n",
       "      <td>1917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5819</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01532</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>13:09:12</td>\n",
       "      <td>PM</td>\n",
       "      <td>1918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5820</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01533</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>13:09:14</td>\n",
       "      <td>PM</td>\n",
       "      <td>1919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5821</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01534</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>13:09:16</td>\n",
       "      <td>PM</td>\n",
       "      <td>1920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5822</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01535</td>\n",
       "      <td>5/12/2020</td>\n",
       "      <td>13:09:18</td>\n",
       "      <td>PM</td>\n",
       "      <td>1921</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5823 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Station     Photo       Date      Time AMPM Photo_number\n",
       "0         01  OBL09618  5/12/2020  06:54:03   AM            1\n",
       "1         01  OBL09619  5/12/2020  06:54:05   AM            2\n",
       "2         01  OBL09620  5/12/2020  06:54:07   AM            3\n",
       "3         01  OBL09621  5/12/2020  06:54:09   AM            4\n",
       "4         01  OBL09622  5/12/2020  06:54:11   AM            5\n",
       "...      ...       ...        ...       ...  ...          ...\n",
       "5818      02  OBL01531  5/12/2020  13:09:10   PM         1917\n",
       "5819      02  OBL01532  5/12/2020  13:09:12   PM         1918\n",
       "5820      02  OBL01533  5/12/2020  13:09:14   PM         1919\n",
       "5821      02  OBL01534  5/12/2020  13:09:16   PM         1920\n",
       "5822      02  OBL01535  5/12/2020  13:09:18   PM         1921\n",
       "\n",
       "[5823 rows x 6 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for station in station_numbers:\n",
    "\n",
    "    exif_info_station = pd.read_csv('IN2020_V08_PLAOS_station_'+str(int(station))+'_oblique_exif.txt', header=None, names=['Photo', 'Date', 'Time', 'AMPM', 'Photo_number'])\n",
    "    exif_info_station = exif_info_station.iloc[1:].reset_index(drop=True)\n",
    "    exif_info_station.insert(0, 'Station', station)\n",
    "\n",
    "    if station == station_numbers[0]:\n",
    "        exif_info = exif_info_station\n",
    "\n",
    "    exif_info = pd.concat([exif_info, exif_info_station], ignore_index=True)\n",
    "\n",
    "exif_info\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1529370d-3888-42bb-abb7-75b12301c02d",
   "metadata": {},
   "source": [
    "### Combine relevant info for each image"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bb484ef9-224b-430a-9cce-0e7a70c54bc1",
   "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>Station</th>\n",
       "      <th>Photo</th>\n",
       "      <th>Date</th>\n",
       "      <th>Time</th>\n",
       "      <th>AMPM</th>\n",
       "      <th>Photo_number</th>\n",
       "      <th>Depth</th>\n",
       "      <th>Source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09618</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>06:54:03</td>\n",
       "      <td>AM</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09619</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>06:54:05</td>\n",
       "      <td>AM</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09620</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>06:54:07</td>\n",
       "      <td>AM</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09621</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>06:54:09</td>\n",
       "      <td>AM</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09622</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>06:54:11</td>\n",
       "      <td>AM</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5818</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01531</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>13:09:10</td>\n",
       "      <td>PM</td>\n",
       "      <td>1917</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5819</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01532</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>13:09:12</td>\n",
       "      <td>PM</td>\n",
       "      <td>1918</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5820</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01533</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>13:09:14</td>\n",
       "      <td>PM</td>\n",
       "      <td>1919</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5821</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01534</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>13:09:16</td>\n",
       "      <td>PM</td>\n",
       "      <td>1920</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5822</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01535</td>\n",
       "      <td>05/12/2020</td>\n",
       "      <td>13:09:18</td>\n",
       "      <td>PM</td>\n",
       "      <td>1921</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5823 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Station     Photo        Date      Time AMPM Photo_number  Depth  \\\n",
       "0         01  OBL09618  05/12/2020  06:54:03   AM            1    NaN   \n",
       "1         01  OBL09619  05/12/2020  06:54:05   AM            2    NaN   \n",
       "2         01  OBL09620  05/12/2020  06:54:07   AM            3    NaN   \n",
       "3         01  OBL09621  05/12/2020  06:54:09   AM            4    NaN   \n",
       "4         01  OBL09622  05/12/2020  06:54:11   AM            5    NaN   \n",
       "...      ...       ...         ...       ...  ...          ...    ...   \n",
       "5818      02  OBL01531  05/12/2020  13:09:10   PM         1917    NaN   \n",
       "5819      02  OBL01532  05/12/2020  13:09:12   PM         1918    NaN   \n",
       "5820      02  OBL01533  05/12/2020  13:09:14   PM         1919    NaN   \n",
       "5821      02  OBL01534  05/12/2020  13:09:16   PM         1920    NaN   \n",
       "5822      02  OBL01535  05/12/2020  13:09:18   PM         1921    NaN   \n",
       "\n",
       "                                                 Source  \n",
       "0     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "1     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "2     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "3     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "4     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "...                                                 ...  \n",
       "5818  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "5819  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "5820  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "5821  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "5822  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...  \n",
       "\n",
       "[5823 rows x 8 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import_df = pd.DataFrame()\n",
    "\n",
    "for index, img_info in exif_info.iterrows():\n",
    "    station = img_info.Station\n",
    "    photo = img_info.Photo\n",
    "    \n",
    "    # Filter the source_info DataFrame to match the current row\n",
    "    img_source_info = source_info[(source_info.Station == station) & (source_info.Photo == photo)]\n",
    "    \n",
    "    if not img_source_info.empty:\n",
    "        # Convert both rows to dictionaries\n",
    "        img_info_dict = img_info.to_dict()\n",
    "        img_source_info_dict = img_source_info.iloc[0].to_dict()\n",
    "        \n",
    "        # Combine dictionaries, img_source_info_dict will overwrite img_info_dict if there are conflicts\n",
    "        combined_dict = {**img_info_dict, **img_source_info_dict}\n",
    "        \n",
    "        # Convert the combined dictionary to a DataFrame\n",
    "        new_row_df = pd.DataFrame([combined_dict])\n",
    "        \n",
    "        # Append the new row DataFrame to import_df\n",
    "        import_df = pd.concat([import_df, new_row_df], ignore_index=True)\n",
    "\n",
    "import_df.insert(len(import_df.columns) - 1, 'Depth', np.nan)\n",
    "\n",
    "import_df['Date'] = pd.to_datetime(import_df['Date']).dt.strftime('%m/%d/%Y')\n",
    "\n",
    "import_df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c4b6755-9082-4171-992c-c76a3772193d",
   "metadata": {},
   "source": [
    "### Find Depth for each Image"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "be74a31a-92cb-4a9f-9029-07d9b78f0f16",
   "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>Date</th>\n",
       "      <th>Time</th>\n",
       "      <th>Depth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29/11/2020</td>\n",
       "      <td>23:20:29</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29/11/2020</td>\n",
       "      <td>23:20:30</td>\n",
       "      <td>3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29/11/2020</td>\n",
       "      <td>23:20:31</td>\n",
       "      <td>3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29/11/2020</td>\n",
       "      <td>23:20:32</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29/11/2020</td>\n",
       "      <td>23:20:33</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61634</th>\n",
       "      <td>09/12/2020</td>\n",
       "      <td>10:37:43</td>\n",
       "      <td>4.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61635</th>\n",
       "      <td>09/12/2020</td>\n",
       "      <td>10:37:44</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61636</th>\n",
       "      <td>09/12/2020</td>\n",
       "      <td>10:37:44</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61637</th>\n",
       "      <td>09/12/2020</td>\n",
       "      <td>10:37:46</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61638</th>\n",
       "      <td>09/12/2020</td>\n",
       "      <td>10:37:47</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61639 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Date      Time  Depth\n",
       "0      29/11/2020  23:20:29    4.7\n",
       "1      29/11/2020  23:20:30    3.6\n",
       "2      29/11/2020  23:20:31    3.6\n",
       "3      29/11/2020  23:20:32    3.2\n",
       "4      29/11/2020  23:20:33    3.2\n",
       "...           ...       ...    ...\n",
       "61634  09/12/2020  10:37:43    4.3\n",
       "61635  09/12/2020  10:37:44    3.2\n",
       "61636  09/12/2020  10:37:44    3.2\n",
       "61637  09/12/2020  10:37:46    1.4\n",
       "61638  09/12/2020  10:37:47    2.9\n",
       "\n",
       "[61639 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "def round_to_nearest_second(time_str):\n",
    "    dt = datetime.strptime(time_str, '%H:%M:%S:%f')\n",
    "    rounded_seconds = round(dt.second + dt.microsecond / 1_000_000)\n",
    "    new_dt = dt.replace(second=0, microsecond=0) + timedelta(seconds=rounded_seconds)\n",
    "    return new_dt.strftime('%H:%M:%S')\n",
    "\n",
    "depth_info = pd.read_csv('IN2020_V08_date_time_depth_20201129-20201209-op-test-03.txt', \n",
    "                         header=None, \n",
    "                         names=['XXX', 'Date', 'Time', 'Depth']).iloc[:, 1:]\n",
    "\n",
    "depth_info = depth_info.iloc[1:].reset_index(drop=True)\n",
    "depth_info['Date'] = pd.to_datetime(depth_info['Date'], format='%Y%m%d').dt.strftime('%d/%m/%Y')\n",
    "\n",
    "\n",
    "depth_info['Time'] = depth_info['Time'].apply(lambda x: f\"{int(x * 100):08d}\")\n",
    "t_str_new = [round_to_nearest_second(f\"{t[:2]}:{t[2:4]}:{t[4:6]}:{t[6:]}\") for t in depth_info['Time']]\n",
    "depth_info['Time'] = t_str_new\n",
    "\n",
    "depth_info\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7003700a-a650-4fc7-9c8d-82dafdbabfdf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import numpy as np\n",
    "from scipy.spatial import cKDTree\n",
    "\n",
    "depth_info['Datetime'] = depth_info.apply(lambda row: datetime.strptime(f\"{row['Date']} {row['Time']}\", \"%d/%m/%Y %H:%M:%S\"), axis=1)\n",
    "depth_info['Timestamp'] = depth_info['Datetime'].astype(np.int64) // 10**9\n",
    "\n",
    "tree = cKDTree(depth_info[['Timestamp']].values)\n",
    "\n",
    "import_df['Datetime'] = import_df.apply(lambda row: datetime.strptime(f\"{row['Date']} {row['Time']}\", \"%d/%m/%Y %H:%M:%S\"), axis=1)\n",
    "import_df['Timestamp'] = import_df['Datetime'].astype(np.int64) // 10**9\n",
    "\n",
    "distances, indices = tree.query(import_df[['Timestamp']].values, k=1)\n",
    "\n",
    "import_df['Depth'] = depth_info.iloc[indices.flatten()]['Depth'].values\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66dc6b86-49da-421f-a075-17c0f1492016",
   "metadata": {},
   "source": [
    "### Finalise DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4d00ca4d-fae5-4463-a7a5-ec7ee6d71bc7",
   "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>Station</th>\n",
       "      <th>Photo</th>\n",
       "      <th>Datetime</th>\n",
       "      <th>Depth</th>\n",
       "      <th>Source</th>\n",
       "      <th>OutSource</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09618</td>\n",
       "      <td>2020-12-05 06:54:03</td>\n",
       "      <td>6.9</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/01/OBL09618/2020-12-05T06:54:03/6....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09619</td>\n",
       "      <td>2020-12-05 06:54:05</td>\n",
       "      <td>6.2</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/01/OBL09619/2020-12-05T06:54:05/6....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09620</td>\n",
       "      <td>2020-12-05 06:54:07</td>\n",
       "      <td>6.5</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/01/OBL09620/2020-12-05T06:54:07/6....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09621</td>\n",
       "      <td>2020-12-05 06:54:09</td>\n",
       "      <td>5.4</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/01/OBL09621/2020-12-05T06:54:09/5....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01</td>\n",
       "      <td>OBL09622</td>\n",
       "      <td>2020-12-05 06:54:11</td>\n",
       "      <td>5.8</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/01/OBL09622/2020-12-05T06:54:11/5....</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>5818</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01531</td>\n",
       "      <td>2020-12-05 13:09:10</td>\n",
       "      <td>5.8</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/02/OBL01531/2020-12-05T13:09:10/5....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5819</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01532</td>\n",
       "      <td>2020-12-05 13:09:12</td>\n",
       "      <td>6.2</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/02/OBL01532/2020-12-05T13:09:12/6....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5820</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01533</td>\n",
       "      <td>2020-12-05 13:09:14</td>\n",
       "      <td>5.4</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/02/OBL01533/2020-12-05T13:09:14/5....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5821</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01534</td>\n",
       "      <td>2020-12-05 13:09:16</td>\n",
       "      <td>6.9</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/02/OBL01534/2020-12-05T13:09:16/6....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5822</th>\n",
       "      <td>02</td>\n",
       "      <td>OBL01535</td>\n",
       "      <td>2020-12-05 13:09:18</td>\n",
       "      <td>6.2</td>\n",
       "      <td>/Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...</td>\n",
       "      <td>/IN2020_V09/02/OBL01535/2020-12-05T13:09:18/6....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5823 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Station     Photo            Datetime  Depth  \\\n",
       "0         01  OBL09618 2020-12-05 06:54:03    6.9   \n",
       "1         01  OBL09619 2020-12-05 06:54:05    6.2   \n",
       "2         01  OBL09620 2020-12-05 06:54:07    6.5   \n",
       "3         01  OBL09621 2020-12-05 06:54:09    5.4   \n",
       "4         01  OBL09622 2020-12-05 06:54:11    5.8   \n",
       "...      ...       ...                 ...    ...   \n",
       "5818      02  OBL01531 2020-12-05 13:09:10    5.8   \n",
       "5819      02  OBL01532 2020-12-05 13:09:12    6.2   \n",
       "5820      02  OBL01533 2020-12-05 13:09:14    5.4   \n",
       "5821      02  OBL01534 2020-12-05 13:09:16    6.9   \n",
       "5822      02  OBL01535 2020-12-05 13:09:18    6.2   \n",
       "\n",
       "                                                 Source  \\\n",
       "0     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "1     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "2     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "3     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "4     /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "...                                                 ...   \n",
       "5818  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "5819  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "5820  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "5821  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "5822  /Volumes/csiro_data/IN2020_V08/PLAOS/Data/Stat...   \n",
       "\n",
       "                                              OutSource  \n",
       "0     /IN2020_V09/01/OBL09618/2020-12-05T06:54:03/6....  \n",
       "1     /IN2020_V09/01/OBL09619/2020-12-05T06:54:05/6....  \n",
       "2     /IN2020_V09/01/OBL09620/2020-12-05T06:54:07/6....  \n",
       "3     /IN2020_V09/01/OBL09621/2020-12-05T06:54:09/5....  \n",
       "4     /IN2020_V09/01/OBL09622/2020-12-05T06:54:11/5....  \n",
       "...                                                 ...  \n",
       "5818  /IN2020_V09/02/OBL01531/2020-12-05T13:09:10/5....  \n",
       "5819  /IN2020_V09/02/OBL01532/2020-12-05T13:09:12/6....  \n",
       "5820  /IN2020_V09/02/OBL01533/2020-12-05T13:09:14/5....  \n",
       "5821  /IN2020_V09/02/OBL01534/2020-12-05T13:09:16/6....  \n",
       "5822  /IN2020_V09/02/OBL01535/2020-12-05T13:09:18/6....  \n",
       "\n",
       "[5823 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_import_df = import_df.drop(columns=['Date', 'Time', 'Timestamp', 'Photo_number', 'AMPM'])\n",
    "cols = final_import_df.columns.tolist()\n",
    "last_col = cols.pop()\n",
    "cols.insert(2, last_col)\n",
    "final_import_df = final_import_df[cols]\n",
    "\n",
    "final_import_df['OutSource'] = final_import_df.astype(str).apply('/'.join, axis=1) \\\n",
    "                              .str.replace('//', '/') \\\n",
    "                              .str.replace(' ', 'T') \\\n",
    "                              .apply(lambda x: f'/IN2020_V09/{x}')\n",
    "\n",
    "final_import_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc7b2443-6183-4b07-9104-99ac496fdecb",
   "metadata": {},
   "source": [
    "## Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9abf245-35b2-4482-b43a-6e31e2de1696",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re\n",
    "import time\n",
    "from datetime import datetime, timedelta\n",
    "from scipy.spatial import cKDTree\n",
    "\n",
    "####################################################################################\n",
    "\n",
    "# # Define source path\n",
    "# source_path = Path('/Volumes/csiro_data/IN2020_V08/PLAOS/Data/')\n",
    "\n",
    "# # Get relevant file paths\n",
    "# relevent_paths = list(source_path.rglob('Station_*/**/OBL*.JPG'))\n",
    "\n",
    "####################################################################################\n",
    "\n",
    "import pickle\n",
    "with open('relevent_paths.pkl', 'rb') as file:\n",
    "    relevent_paths = pickle.load(file)\n",
    "\n",
    "####################################################################################\n",
    "\n",
    "\n",
    "def pipeline(relevent_paths):\n",
    "    \n",
    "    def extract_data(pattern, file_paths):\n",
    "        \"\"\"Extract unique numeric data from file paths based on regex pattern.\"\"\"\n",
    "        data = []\n",
    "        seen = set()\n",
    "        for path in file_paths:\n",
    "            path = str(path)\n",
    "            match = re.search(pattern, path)\n",
    "            if match:\n",
    "                num = int(match.group(1))  \n",
    "                if num not in seen:\n",
    "                    seen.add(num)\n",
    "                    data.append(num)\n",
    "        return sorted(data)\n",
    "    \n",
    "\n",
    "    relevent_paths_str = []\n",
    "    for p in relevent_paths:\n",
    "        relevent_paths_str.append(str(p))\n",
    "    \n",
    "    # Extract station numbers\n",
    "    station_numbers = extract_data(pattern=r'Station_(\\d+)', file_paths=relevent_paths)\n",
    "    \n",
    "    # CAP NUMBER OF STATIONS FOR THE MOMENT!\n",
    "    station_numbers = [f'{num:02}' for num in np.arange(1, 3)]\n",
    "    \n",
    "    # Prepare lists to store data\n",
    "    img_input_path = []\n",
    "    S = []\n",
    "    I = []\n",
    "    \n",
    "    # Process each station\n",
    "    for station in station_numbers:\n",
    "    \n",
    "        station_paths = []\n",
    "        for path in relevent_paths_str:\n",
    "            # Format the station number as a two-digit string\n",
    "            station_str = f'{station:02}'\n",
    "            # Construct the station path with the formatted station number\n",
    "            station_path = f'Station_{station_str}'\n",
    "            # Check if the formatted station path is in the path\n",
    "            if station_path in path:\n",
    "                station_paths.append(path)\n",
    "    \n",
    "        # Extract image numbers for the current station\n",
    "        img_numbers = extract_data(pattern=r'OBL(\\d+)', file_paths=station_paths)\n",
    "    \n",
    "        # Process each image number\n",
    "        for img_number in img_numbers:\n",
    "    \n",
    "            img_number = f'{img_number:05}'\n",
    "    \n",
    "            img_path = [s for s in station_paths if 'OBL'+img_number in s]\n",
    "    \n",
    "            img_input_path.append(str(img_path[0]))\n",
    "            S.append(station)\n",
    "            I.append('OBL'+img_number)\n",
    "    \n",
    "    # Create a DataFrame\n",
    "    source_info = pd.DataFrame({\n",
    "        'Station': S,\n",
    "        'Photo': I,\n",
    "        'Source': img_input_path\n",
    "    })\n",
    "\n",
    "    ################\n",
    "    \n",
    "    for station in station_numbers:\n",
    "    \n",
    "        exif_info_station = pd.read_csv('IN2020_V08_PLAOS_station_'+str(int(station))+'_oblique_exif.txt', header=None, names=['Photo', 'Date', 'Time', 'AMPM', 'Photo_number'])\n",
    "        exif_info_station = exif_info_station.iloc[1:].reset_index(drop=True)\n",
    "        exif_info_station.insert(0, 'Station', station)\n",
    "    \n",
    "        if station == station_numbers[0]:\n",
    "            exif_info = exif_info_station\n",
    "    \n",
    "        exif_info = pd.concat([exif_info, exif_info_station], ignore_index=True)\n",
    "    \n",
    "    ###############\n",
    "    \n",
    "    import_df = pd.DataFrame()\n",
    "    \n",
    "    for index, img_info in exif_info.iterrows():\n",
    "        station = img_info.Station\n",
    "        photo = img_info.Photo\n",
    "        \n",
    "        # Filter the source_info DataFrame to match the current row\n",
    "        img_source_info = source_info[(source_info.Station == station) & (source_info.Photo == photo)]\n",
    "        \n",
    "        if not img_source_info.empty:\n",
    "            # Convert both rows to dictionaries\n",
    "            img_info_dict = img_info.to_dict()\n",
    "            img_source_info_dict = img_source_info.iloc[0].to_dict()\n",
    "            \n",
    "            # Combine dictionaries, img_source_info_dict will overwrite img_info_dict if there are conflicts\n",
    "            combined_dict = {**img_info_dict, **img_source_info_dict}\n",
    "            \n",
    "            # Convert the combined dictionary to a DataFrame\n",
    "            new_row_df = pd.DataFrame([combined_dict])\n",
    "            \n",
    "            # Append the new row DataFrame to import_df\n",
    "            import_df = pd.concat([import_df, new_row_df], ignore_index=True)\n",
    "    \n",
    "    import_df.insert(len(import_df.columns) - 1, 'Depth', np.nan)\n",
    "    \n",
    "    import_df['Date'] = pd.to_datetime(import_df['Date']).dt.strftime('%m/%d/%Y')\n",
    "    \n",
    "    \n",
    "    ###############\n",
    "    \n",
    "    \n",
    "    def round_to_nearest_second(time_str):\n",
    "        dt = datetime.strptime(time_str, '%H:%M:%S:%f')\n",
    "        rounded_seconds = round(dt.second + dt.microsecond / 1_000_000)\n",
    "        new_dt = dt.replace(second=0, microsecond=0) + timedelta(seconds=rounded_seconds)\n",
    "        return new_dt.strftime('%H:%M:%S')\n",
    "    \n",
    "    depth_info = pd.read_csv('IN2020_V08_date_time_depth_20201129-20201209-op-test-03.txt', \n",
    "                             header=None, \n",
    "                             names=['XXX', 'Date', 'Time', 'Depth']).iloc[:, 1:]\n",
    "    \n",
    "    depth_info = depth_info.iloc[1:].reset_index(drop=True)\n",
    "    depth_info['Date'] = pd.to_datetime(depth_info['Date'], format='%Y%m%d').dt.strftime('%d/%m/%Y')\n",
    "    \n",
    "    \n",
    "    depth_info['Time'] = depth_info['Time'].apply(lambda x: f\"{int(x * 100):08d}\")\n",
    "    t_str_new = [round_to_nearest_second(f\"{t[:2]}:{t[2:4]}:{t[4:6]}:{t[6:]}\") for t in depth_info['Time']]\n",
    "    depth_info['Time'] = t_str_new\n",
    "    \n",
    "    ######################\n",
    "    \n",
    "    depth_info['Datetime'] = depth_info.apply(lambda row: datetime.strptime(f\"{row['Date']} {row['Time']}\", \"%d/%m/%Y %H:%M:%S\"), axis=1)\n",
    "    depth_info['Timestamp'] = depth_info['Datetime'].astype(np.int64) // 10**9\n",
    "    \n",
    "    tree = cKDTree(depth_info[['Timestamp']].values)\n",
    "    \n",
    "    import_df['Datetime'] = import_df.apply(lambda row: datetime.strptime(f\"{row['Date']} {row['Time']}\", \"%d/%m/%Y %H:%M:%S\"), axis=1)\n",
    "    import_df['Timestamp'] = import_df['Datetime'].astype(np.int64) // 10**9\n",
    "    \n",
    "    distances, indices = tree.query(import_df[['Timestamp']].values, k=1)\n",
    "    \n",
    "    import_df['Depth'] = depth_info.iloc[indices.flatten()]['Depth'].values\n",
    "    \n",
    "    \n",
    "    ######################\n",
    "    \n",
    "    final_import_df = import_df.drop(columns=['Date', 'Time', 'Timestamp', 'Photo_number'])\n",
    "    cols = final_import_df.columns.tolist()\n",
    "    last_col = cols.pop()\n",
    "    cols.insert(2, last_col)\n",
    "    final_import_df = final_import_df[cols]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cde9d4ca-7785-4928-b594-65d4f8c09813",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:analysis3-24.07]",
   "language": "python",
   "name": "conda-env-analysis3-24.07-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}