tutorial_notebook/4_Navigating_db_tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "323b8e4d-21f9-4535-9411-8f2c48840332",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ethoscopy.metadata_db import db_organiser, metadata_handler, db_crawler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e6de6e69-befe-49ec-a2ec-92c722100735",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# This is the step in which we crawl through our folder containing all the db files from the lab\n",
    "# If refresh is set to True, it will scan all the files and generate a new csv list of all the files available in the system\n",
    "# If refresh is set to False, it will simply load a pre-compiled csv list (like in the case of this tutorial)\n",
    "# We provide a copy of `ethoscope_db.csv` which list all the 16.8k db files of the Gilestro lab as of Dec 2022\n",
    "\n",
    "datapath = \"/mnt/ethoscope_results\"\n",
    "db = db_organiser(datapath, refresh=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8527c044-df3d-4fa7-91e8-09600885969f",
   "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>machine_name</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-04-04</td>\n",
       "      <td>ETHOSCOPE_001</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-04-04</td>\n",
       "      <td>ETHOSCOPE_003</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-04-04</td>\n",
       "      <td>ETHOSCOPE_004</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-04-04</td>\n",
       "      <td>ETHOSCOPE_005</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-04-04</td>\n",
       "      <td>ETHOSCOPE_020</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td>2022-02-24</td>\n",
       "      <td>ETHOSCOPE_125</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>2022-02-24</td>\n",
       "      <td>ETHOSCOPE_143</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>2022-02-24</td>\n",
       "      <td>ETHOSCOPE_151</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>2022-02-24</td>\n",
       "      <td>ETHOSCOPE_218</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <td>2022-02-24</td>\n",
       "      <td>ETHOSCOPE_273</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>381 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date   machine_name  count\n",
       "0    2016-04-04  ETHOSCOPE_001     20\n",
       "1    2016-04-04  ETHOSCOPE_003     20\n",
       "2    2016-04-04  ETHOSCOPE_004     20\n",
       "3    2016-04-04  ETHOSCOPE_005     20\n",
       "4    2016-04-04  ETHOSCOPE_020     20\n",
       "..          ...            ...    ...\n",
       "376  2022-02-24  ETHOSCOPE_125     24\n",
       "377  2022-02-24  ETHOSCOPE_143     24\n",
       "378  2022-02-24  ETHOSCOPE_151     24\n",
       "379  2022-02-24  ETHOSCOPE_218     24\n",
       "380  2022-02-24  ETHOSCOPE_273     24\n",
       "\n",
       "[381 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now we load the metadata file and do the first processing \n",
    "# to try and understand how many db files we should be looking for\n",
    "# This is a real dataset from Jones_et_al 2023\n",
    "\n",
    "metadata_filename = 'jones_et_al_metadata.csv'\n",
    "meta = metadata_handler(metadata_filename, project='coccinella_2022', authors=['Jones'] )\n",
    "meta.db_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4b7fcfa6-ad9a-4f5f-b824-644808369973",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'filename': 'all_ethoscope_metadata.csv',\n",
       " 'entries': 8720,\n",
       " 'columns': ['date', 'machine_name', 'region_id', 'db_filename', 'filesize'],\n",
       " 'db_files': 381,\n",
       " 'info_mtime': '2022-12-01 16:58',\n",
       " 'entries_not_found': 72,\n",
       " 'db_files_na': 3,\n",
       " 'db_files_size': '168.73 GB',\n",
       " 'tags': [],\n",
       " 'project': 'coccinella_2022',\n",
       " 'description': '',\n",
       " 'authors': ['Jones'],\n",
       " 'paper_doi': ''}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is where we go through the metadata and find the relevant db files\n",
    "meta.associate_to_db(db)\n",
    "meta.summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4e00f959-d300-410c-8683-65abc9e471e7",
   "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>machine_name</th>\n",
       "      <th>db_filename</th>\n",
       "      <th>filesize</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>2020-08-17</td>\n",
       "      <td>ETHOSCOPE_220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>2020-09-20</td>\n",
       "      <td>ETHOSCOPE_141</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>2020-09-20</td>\n",
       "      <td>ETHOSCOPE_218</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           date   machine_name db_filename  filesize  count\n",
       "166  2020-08-17  ETHOSCOPE_220         NaN       NaN     24\n",
       "183  2020-09-20  ETHOSCOPE_141         NaN       NaN     24\n",
       "184  2020-09-20  ETHOSCOPE_218         NaN       NaN     24"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# It seems we have 8720 in our metadata list, corresponding to 381 files\n",
    "# However, we could not find 3 of these 381, corresponding to 72 animals\n",
    "# To see which experiments are missing (probably because they were mislabeled) use the following:\n",
    "meta.list_dbs(notfound = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4764f872-f040-4afd-8742-a1c695d595d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Which can be saved in the following way\n",
    "meta.list_dbs(notfound = True).to_csv('jones_et_al_dbs_not_found.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b5cdf915-29ec-4715-b842-b4241d503c4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Conversely, if we want to write all the other succesfully found db to a csv we can use the following:\n",
    "meta.list_dbs().to_csv('jones_et_al_all_info.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9f203214-31d5-4073-bb91-c24cb21ee749",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Those options however are rather verbose, providing also info about the db filesize and so on\n",
    "# IF we want only the filenames and location of each db we can use\n",
    "meta.list_dbs().db_filename.to_csv('jones_et_al_only_dbs.txt', header=None, index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03a67627-ef34-4fa3-a868-ff8c2930c6cd",
   "metadata": {},
   "source": [
    "### Upload all the db files to Zenodo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fbbce4f-af54-4b62-962a-f076cdaf8f91",
   "metadata": {},
   "source": [
    "Now we have a handy list of all the db files associated to our experiments, ready to be shared with the world.\n",
    "Notice that these 381 files take 168.73 GB which is above the 50GB limit that we would have on a Zenodo repository.\n",
    "We will have to zip them and use three repositories. **The following is not Python but bash!**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4de6e5af",
   "metadata": {},
   "source": [
    "First we need to copy all these files to a temporary destination\n",
    "The following will create a copy of all the files we want to upload in the repo\n",
    "\n",
    "```\n",
    "mkdir /mnt/data/temporary/jones_et_al\n",
    "rsync -aruRP --files-from=jones_et_al_only_dbs.txt /mnt/data/results /mnt/data/temporary/jones_et_al/\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f26c704",
   "metadata": {},
   "source": [
    "Now we zip all these files in multiple archives, each 2GB in size\n",
    "```\n",
    "zip -r -s 2000m jones_et_al_db_files.zip jones_et_al\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6956d64f",
   "metadata": {},
   "source": [
    "Now create some descriptive files of what is in the archive\n",
    "```\n",
    "cd /mnt/data/temporary/jones_et_al\n",
    "```\n",
    "create a txt file providing the md5sum of each zip archive\n",
    "```\n",
    "find . -type f -name \"*.z*\" -exec md5sum {} + > ../all_zips_with_checksum.txt\n",
    "```\n",
    "\n",
    "same thing but for the original uncompressed db files\n",
    "```\n",
    "find . -type f -name \"*.db\" -exec md5sum {} + > ../all_dbs_with_checksum.txt\n",
    "```\n",
    "\n",
    "visualize the structure of the folders / files\n",
    "```\n",
    "tree > jones_et_al/db_files_content.txt\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d7ab718",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}