0_Lif_Files_MetaData_Reader_vers1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retrieve MetaData from .lif file Header for SP8 Leica microscopy files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook need to read the leica microscopy files stored on the Seagate hard disk drive.  \n",
    "The path to save the parquets and pickles needs to be absolut path. Does not work with relative  path: 'parquets_and_pickles/images_metadata.parquet'."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import the libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "# To import the files\n",
    "import glob\n",
    "from pathlib import Path\n",
    "\n",
    "# To read the metadata of the lif file\n",
    "import read_lif\n",
    "\n",
    "# To format the lif metadata as xml and put them into a dictionnary\n",
    "from display_xml import XML\n",
    "import xmltodict\n",
    "\n",
    "# To read metadata in Dataframe\n",
    "import pandas as pd\n",
    "pd.set_option('max_colwidth', 500)\n",
    "pd.set_option('display.max_columns', None) \n",
    "import numpy as np\n",
    "from itertools import repeat\n",
    "\n",
    "# To plot some of the data\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## .lif Metadata to Dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get the .lif files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = glob.glob('/Volumes/Seagate/eth/0_Leica_SP8/*/*.lif')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# File example for debugging\n",
    "\n",
    "#files[0]\n",
    "#files[39]\n",
    "#files[6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Retrieve the metadata information from the .lif files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example files:\n",
    "\n",
    "#xml_0 = read_lif.get_xml(files[0])\n",
    "#xml_39 = read_lif.get_xml(files[39])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print long xml formatted\n",
    "#XML(xml_39, style='colorful')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Metadata information to dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To explore the doc \n",
    "#doc = xmltodict.parse(xml_39)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To explore the structure of the dictionnary\n",
    "#list(doc.values())[0].keys()         # --> level1\n",
    "#list(doc.values())[0]['Element']     # --> level2\n",
    "#..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xmls = []\n",
    "xmls_attachments = []\n",
    "file_counter = 0\n",
    "\n",
    "for file in files:\n",
    "    \n",
    "    # Retrieve the metadata information as string from .lif files\n",
    "    xml_file = read_lif.get_xml(file)\n",
    "    \n",
    "    # Metadata to ordered dictionnary\n",
    "    doc = xmltodict.parse(xml_file)\n",
    "    \n",
    "    try:\n",
    "        df = pd.json_normalize(list(doc.values())[0]['Element']['Children']['Element'])\n",
    "        df['FileName'] = (f'{Path(file).parent.name}_{Path(file).name}')\n",
    "        \n",
    "        \n",
    "        try:\n",
    "            # Get FileName, Image names and unique Ids\n",
    "            df_name = df[['FileName', '@Name', '@UniqueID']].rename(columns={'@Name' : 'Image.@Name', '@UniqueID': 'Image.@UniqueID'})\n",
    "\n",
    "            # To get the attachement part of each files\n",
    "            attachment = pd.concat(df['Data.Image.Attachment'].explode().apply(pd.json_normalize).values)\n",
    "            attachment.index = list(df['Data.Image.Attachment'].explode().apply(pd.json_normalize).index)\n",
    "\n",
    "            attachment = df_name.join(attachment, how='outer')\n",
    "            del(df_name)\n",
    "            \n",
    "            # Remove the original columns relative to attachment\n",
    "            df = df.drop(['Data.Image.Attachment'], axis=1)\n",
    "            \n",
    "            # To get channel and dimension information on different rows\n",
    "            df = df.explode('Data.Image.ImageDescription.Channels.ChannelDescription').explode('Data.Image.ImageDescription.Dimensions.DimensionDescription')\n",
    "            df = df.reset_index(drop=True)\n",
    "            \n",
    "            # New dataframe containing the information relative to the channnels on different columns\n",
    "            channels = pd.concat(df['Data.Image.ImageDescription.Channels.ChannelDescription'].apply(pd.json_normalize).values, ignore_index=True)\n",
    "            cols_channel = list(map(lambda x: f'Channel.{x}', channels.columns))\n",
    "            channels.columns = cols_channel\n",
    "\n",
    "            # New dataframe containing the information relative to the dimensions on different columns\n",
    "            dimensions = pd.concat(df['Data.Image.ImageDescription.Dimensions.DimensionDescription'].apply(pd.json_normalize).values, ignore_index=True)\n",
    "            cols_dimension = list(map(lambda x: f'Dimension.{x}', dimensions.columns))\n",
    "            dimensions.columns = cols_dimension\n",
    "\n",
    "            # Remove the original columns relative to channel and dimension information\n",
    "            df_images = df.drop(['Data.Image.ImageDescription.Channels.ChannelDescription', 'Data.Image.ImageDescription.Dimensions.DimensionDescription'], axis=1).rename(columns={'@Name' : 'Image.@Name', '@UniqueID': 'Image.@UniqueID'})\n",
    "            cols_images = list(df_images.columns)\n",
    "            cols_images.remove('FileName')\n",
    "            cols_images.remove('Image.@Name')\n",
    "            cols_images.remove('Image.@UniqueID')\n",
    "\n",
    "            df_temp = pd.concat([channels, dimensions], axis=1, sort=False)\n",
    "            cols_temp = cols_channel + cols_dimension\n",
    "            \n",
    "            df_final = df_images.join(df_temp)\n",
    "            \n",
    "            # Re-arrange the columns order\n",
    "            cols_final = ['FileName'] + ['Image.@Name'] + ['Image.@UniqueID'] +cols_temp + cols_images\n",
    "            df_final = df_final.reindex(columns=cols_final)\n",
    "            del(df, df_images, df_temp, channels, dimensions)\n",
    "        \n",
    "        \n",
    "        except KeyError:\n",
    "            \n",
    "            if 'Children.Element' in df.columns:\n",
    "                print(f'The file {Path(file).name} contains collections and images.')\n",
    "                df = df.rename(columns={'@Name' : 'Collection.@Name', '@Visibility' : 'Collection.@Visibility', '@CopyOption' : 'Collection.@CopyOption',\n",
    "                                                                                        '@UniqueID' : 'Collection.@UniqueID', 'Children' : 'Collection.Children', 'Memory.@Size' : 'Collection.Memory.@Size',\n",
    "                                                                                        'Memory.@MemoryBlockID' : 'Collection.Memory.@MemoryBlockID'})\n",
    "                df_collec = df.explode('Children.Element').dropna(subset=['Children.Element'])\n",
    "                df_collec = df_collec.reset_index(drop=False)\n",
    "\n",
    "                images = pd.concat(df_collec['Children.Element'].apply(pd.json_normalize).values, ignore_index=True)\n",
    "                df_collec = df_collec.drop(['Children.Element'], axis=1)\n",
    "                df_collec = df_collec.join(images)\n",
    "                del(images)\n",
    "                df_collec['FileName'] = (f'{Path(file).parent.name}_{Path(file).name}')\n",
    "\n",
    "                # Get FileName, Image names and unique Ids\n",
    "                df_name = df_collec[['FileName', '@Name', '@UniqueID', 'Collection.@Name', 'Collection.@UniqueID']].rename(columns={'@Name' : 'Image.@Name', '@UniqueID': 'Image.@UniqueID'})\n",
    "\n",
    "                # To get the attachement part of each files\n",
    "                attachment = pd.concat(df_collec['Data.Image.Attachment'].explode().apply(pd.json_normalize).values)\n",
    "                attachment.index = list(df_collec['Data.Image.Attachment'].explode().apply(pd.json_normalize).index)\n",
    "\n",
    "                attachment = df_name.join(attachment, how='outer')\n",
    "                del(df_name)\n",
    "\n",
    "                # Remove the original columns relative to attachment\n",
    "                df_collec = df_collec.drop(['Data.Image.Attachment'], axis=1)\n",
    "\n",
    "                # To get channel and dimension information on different rows\n",
    "                df_collec = df_collec.explode('Data.Image.ImageDescription.Channels.ChannelDescription').explode('Data.Image.ImageDescription.Dimensions.DimensionDescription')\n",
    "                df_collec = df_collec.reset_index(drop=True)\n",
    "\n",
    "                # New dataframe containing the information relative to the channnels on different columns\n",
    "                channels = pd.concat(df_collec['Data.Image.ImageDescription.Channels.ChannelDescription'].apply(pd.json_normalize).values, ignore_index=True)\n",
    "                cols_channel = list(map(lambda x: f'Channel.{x}', channels.columns))\n",
    "                channels.columns = cols_channel\n",
    "\n",
    "                # New dataframe containing the information relative to the dimensions on different columns\n",
    "                dimensions = pd.concat(df_collec['Data.Image.ImageDescription.Dimensions.DimensionDescription'].apply(pd.json_normalize).values, ignore_index=True)\n",
    "                cols_dimension = list(map(lambda x: f'Dimension.{x}', dimensions.columns))\n",
    "                dimensions.columns = cols_dimension\n",
    "\n",
    "                # Remove the original columns relative to channel and dimension information\n",
    "                df_images = df_collec.drop(['Data.Image.ImageDescription.Channels.ChannelDescription', 'Data.Image.ImageDescription.Dimensions.DimensionDescription'], axis=1).rename(columns={'@Name' : 'Image.@Name', '@UniqueID': 'Image.@UniqueID'})\n",
    "                cols_images = list(df_images.columns)\n",
    "                cols_images.remove('FileName')\n",
    "                cols_images.remove('Image.@Name')\n",
    "                cols_images.remove('Image.@UniqueID')\n",
    "\n",
    "                df_collec_temp = pd.concat([channels, dimensions], axis=1, sort=False)\n",
    "                cols_collec_temp = cols_channel + cols_dimension\n",
    "\n",
    "                df_collec_final = df_images.join(df_collec_temp)\n",
    "                del(df_collec_temp, channels, dimensions)\n",
    "\n",
    "                # Re-arrange the columns order\n",
    "                cols_collec_final = ['FileName'] + ['Image.@Name'] + ['Image.@UniqueID'] + cols_collec_temp + cols_images\n",
    "                df_collec_final = df_collec_final.reindex(columns=cols_collec_final)\n",
    "\n",
    "\n",
    "                df = df.drop(['Children.Element'], axis=1)\n",
    "                cols_df = list(df.columns)\n",
    "                cols_df.remove('FileName')\n",
    "                cols_df.remove('Collection.@Name')\n",
    "                cols_df.remove('Collection.@UniqueID')\n",
    "\n",
    "\n",
    "                df_final = df.join(df_collec_final.set_index('index'), how='outer', rsuffix='_right')\n",
    "                df_final = df_final.drop(df_final.filter(list(filter(lambda x: [x for y in x if x.endswith('_right')], df_final.columns))), axis=1)\n",
    "                cols_df_final = list(df_final.columns)\n",
    "                cols_df_final.remove('FileName')\n",
    "                cols_df_final.remove('Image.@Name')\n",
    "                cols_df_final.remove('Image.@UniqueID')\n",
    "                cols_df_final.remove('Collection.@Name')\n",
    "                cols_df_final.remove('Collection.@UniqueID')\n",
    "                for c in cols_df:\n",
    "                    cols_df_final.remove(c)\n",
    "\n",
    "                cols_final = ['FileName'] + ['Image.@Name'] + ['Image.@UniqueID'] + ['Collection.@Name'] + ['Collection.@UniqueID'] + cols_df_final + cols_df\n",
    "                df_final = df_final.reindex(columns=cols_final)\n",
    "                del(df, df_collec_final)\n",
    "                \n",
    "            else:\n",
    "                print(f'The file {Path(file).name} contains collections, but collections are empty.')\n",
    "                df_final = df.rename(columns={'@Name' : 'Collection.@Name', '@Visibility' : 'Collection.@Visibility', '@CopyOption' : 'Collection.@CopyOption',\n",
    "                                                                                        '@UniqueID' : 'Collection.@UniqueID', 'Children' : 'Collection.Children', 'Memory.@Size' : 'Collection.Memory.@Size',\n",
    "                                                                                        'Memory.@MemoryBlockID' : 'Collection.Memory.@MemoryBlockID'})\n",
    "                attachment = df_final[['FileName', 'Collection.@Name', 'Collection.@UniqueID']]\n",
    "                del(df)\n",
    "        \n",
    "    except TypeError:\n",
    "        print(f'The file {Path(file).name} does not contain any serie.')\n",
    "        df_final = pd.json_normalize(list(doc.values())[0]['Element'])\n",
    "        df_final.insert(0, column='FileName', value=(f'{Path(file).parent.name}_{Path(file).name}'))\n",
    "        attachment = df_final[['FileName', '@Name', '@UniqueID']].rename(columns={'@Name': 'Serie@Name', '@UniqueID': 'Serie@UniqueID'})\n",
    "    \n",
    "        \n",
    "    finally:\n",
    "        xmls.append(df_final)\n",
    "        xmls_attachments.append(attachment)\n",
    "        del(df_final)\n",
    "        del(attachment)\n",
    "        file_counter = file_counter + 1\n",
    "    \n",
    "        #df_final.to_parquet(f'parquet/{file}.parquet')\n",
    "        \n",
    "print(file_counter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata relative to the images\n",
    "if len(xmls) == len(files):\n",
    "    pd.concat(xmls, ignore_index=True).to_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/images_metadata.parquet')\n",
    "else:\n",
    "    print('Error: Missing image metadata')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dff_images = pd.read_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/images_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata relative to the images'attachments\n",
    "if len(xmls_attachments) == len(files):\n",
    "    pd.concat(xmls_attachments, ignore_index=True).to_pickle('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/attachments_metadata.pickle')\n",
    "else:\n",
    "    print('Error: Missing attachment metadata')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'attachments_metadata.pickle'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-36445d0f975a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdff_attachments\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_pickle\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'attachments_metadata.pickle'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/jupyter-env/lib/python3.8/site-packages/pandas/io/pickle.py\u001b[0m in \u001b[0;36mread_pickle\u001b[0;34m(filepath_or_buffer, compression)\u001b[0m\n\u001b[1;32m    167\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfp_or_buf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mcompression\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"infer\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    168\u001b[0m         \u001b[0mcompression\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 169\u001b[0;31m     \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfh\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_handle\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfp_or_buf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"rb\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompression\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcompression\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mis_text\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    170\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    171\u001b[0m     \u001b[0;31m# 1) try standard library Pickle\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/jupyter-env/lib/python3.8/site-packages/pandas/io/common.py\u001b[0m in \u001b[0;36mget_handle\u001b[0;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors)\u001b[0m\n\u001b[1;32m    497\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    498\u001b[0m             \u001b[0;31m# Binary mode\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 499\u001b[0;31m             \u001b[0mf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpath_or_buf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    500\u001b[0m         \u001b[0mhandles\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    501\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'attachments_metadata.pickle'"
     ]
    }
   ],
   "source": [
    "dff_attachments = pd.read_pickle('attachments_metadata.pickle')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select and keep only the rows containing information : seem to correspond only at 1 row out of 4 named @Name = HardwareSetting\n",
    "lst_index = list(dff_attachments[[col for col in dff_attachments.columns if \"Element.@UniqueID\" in col]].dropna(axis=0, how='all').index)\n",
    "\n",
    "new_attachment = dff_attachments[dff_attachments.index.isin(lst_index)].reset_index(drop=True)\n",
    "new_attachment_cols = new_attachment.columns\n",
    "\n",
    "\n",
    "# New dataframe containing the information relative to the image_attachement on different columns\n",
    "image_attachments = new_attachment.filter(list(filter(lambda x: [x for y in x if x == 'FileName' or x == 'Image.@Name' or x == 'Image.@UniqueID' or x.startswith('@')], new_attachment.columns)))\n",
    "cols_image_attachments = list(map(lambda x: f'Image.Attachment.{x}', image_attachments.columns))\n",
    "image_attachments.columns = cols_image_attachments\n",
    "\n",
    "\n",
    "# New dataframe containing the information relative to ATLConfocalSettingDefinition\n",
    "ATLConfocalSettingDefinition = new_attachment.filter(list(filter(lambda x: [x for y in x if x == 'FileName' or x == 'Image.@Name' or x == 'Image.@UniqueID' or x.startswith('ATLConfocalSettingDefinition')], \n",
    "                                                                 new_attachment.columns)))\n",
    "\n",
    "\n",
    "# New dataframe containing the information relative to LDM_Block_Sequential.LDM_Block_Sequential_Master\n",
    "LDM_Block_Sequential = new_attachment.filter(list(filter(lambda x: [x for y in x if x == 'FileName' or x == 'Image.@Name' or x == 'Image.@UniqueID' or x.startswith('LDM_Block_Sequential')], new_attachment.columns)))\n",
    "\n",
    "\n",
    "new_attachment = new_attachment.drop(columns=list(filter(lambda x: [x for y in x if x.startswith('@') or x.startswith('ATLConfocalSettingDefinition') or x.startswith('LDM_Block_Sequential')], new_attachment.columns)))\n",
    "\n",
    "\n",
    "# Get multiple DataFrames for columns with multiple attributes\n",
    "    \n",
    "lst_ATLConfocalSettingDefinition_element = ['ATLConfocalSettingDefinition.AdditionalZPositionList.AdditionalZPosition',\n",
    "                                            'ATLConfocalSettingDefinition.ShutterList.Shutter',\n",
    "                                            'ATLConfocalSettingDefinition.FilterWheel.Wheel',\n",
    "                                            'ATLConfocalSettingDefinition.Spectro.MultiBand',\n",
    "                                            'ATLConfocalSettingDefinition.AotfList.Aotf',\n",
    "                                            'ATLConfocalSettingDefinition.LUT_List.LUT',\n",
    "                                            'ATLConfocalSettingDefinition.DetectorList.Detector',\n",
    "                                            'ATLConfocalSettingDefinition.LaserArray.Laser']\n",
    "lst_LDM_Block_Sequential_element = [\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.AdditionalZPositionList.AdditionalZPosition',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.ShutterList.Shutter',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.FilterWheel.Wheel',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.Spectro.MultiBand',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.AotfList.Aotf',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.LUT_List.LUT',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.DetectorList.Detector',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_Master.ATLConfocalSettingDefinition.LaserArray.Laser',\n",
    "                'LDM_Block_Sequential.LDM_Block_Sequential_List.ATLConfocalSettingDefinition']\n",
    "\n",
    "\n",
    "lst_sub_xml_ATLConfocalSettingDefinition = []\n",
    "sub_xml_ATLConfocalSettingDefinition_dict = dict() \n",
    "\n",
    "ATL_element_counter = 0\n",
    "\n",
    "for idx_ATL_elem, ATL_elem in enumerate(lst_ATLConfocalSettingDefinition_element):\n",
    "\n",
    "    sub_xml_ATLConfocalSettingDefinition_dict.update({idx_ATL_elem:ATL_elem})\n",
    "\n",
    "    df = pd.concat(ATLConfocalSettingDefinition[ATL_elem].explode().apply(pd.json_normalize).values)\n",
    "    cols_df = list(map(lambda x: f'{ATL_elem}.{x}', df.columns))\n",
    "    df.columns = cols_df\n",
    "    df.index = list(ATLConfocalSettingDefinition[ATL_elem].explode().apply(pd.json_normalize).index)\n",
    "    \n",
    "    df1 = ATLConfocalSettingDefinition.filter(list(filter(lambda x: [x for y in x if x == 'FileName' or x == 'Image.@Name' or x == 'Image.@UniqueID'], ATLConfocalSettingDefinition.columns)))\n",
    "    \n",
    "    sub_xml_ATLConfocalSettingDefinition = df1.join(df, how='outer')\n",
    "    del(df)\n",
    "    del(df1)\n",
    "    \n",
    "    lst_sub_xml_ATLConfocalSettingDefinition.append(sub_xml_ATLConfocalSettingDefinition)\n",
    "    ATLConfocalSettingDefinition = ATLConfocalSettingDefinition.drop([ATL_elem], axis=1)\n",
    "    \n",
    "    ATL_element_counter = ATL_element_counter + 1\n",
    "\n",
    "\n",
    "if (len(lst_ATLConfocalSettingDefinition_element) == ATL_element_counter) and (len(lst_ATLConfocalSettingDefinition_element) == len(lst_sub_xml_ATLConfocalSettingDefinition)):\n",
    "    print('All the ATL elements of the list have been treated')\n",
    "else:\n",
    "    print('An error occurred with the ATL elements!')\n",
    "    \n",
    "    \n",
    "\n",
    "\n",
    "lst_sub_xml_LDM_Block_Sequential = []\n",
    "sub_xml_LDM_Block_Sequential_dict = dict() \n",
    "\n",
    "LDM_element_counter = 0\n",
    "\n",
    "for idx_LDM_elem, LDM_elem in enumerate(lst_LDM_Block_Sequential_element):\n",
    "    \n",
    "    sub_xml_LDM_Block_Sequential_dict.update({idx_LDM_elem:LDM_elem})\n",
    "    \n",
    "    df = pd.concat(LDM_Block_Sequential[LDM_elem].explode().apply(pd.json_normalize).values)\n",
    "    cols_df = list(map(lambda x: f'{LDM_elem}.{x}', df.columns))\n",
    "    df.columns = cols_df\n",
    "    df.index = list(LDM_Block_Sequential[LDM_elem].explode().apply(pd.json_normalize).index)\n",
    "    \n",
    "    df1 = LDM_Block_Sequential.filter(list(filter(lambda x: [x for y in x if x == 'FileName' or x == 'Image.@Name' or x == 'Image.@UniqueID'], LDM_Block_Sequential.columns)))\n",
    "    \n",
    "    sub_xml_LDM_Block_Sequential = df1.join(df, how='outer')\n",
    "    del(df)\n",
    "    del(df1)\n",
    "    \n",
    "    lst_sub_xml_LDM_Block_Sequential.append(sub_xml_LDM_Block_Sequential)\n",
    "    LDM_Block_Sequential = LDM_Block_Sequential.drop([LDM_elem], axis=1)\n",
    "    \n",
    "    LDM_element_counter = LDM_element_counter + 1\n",
    "    \n",
    "\n",
    "if (len(lst_LDM_Block_Sequential_element) == LDM_element_counter) and (len(lst_LDM_Block_Sequential_element) == len(lst_sub_xml_LDM_Block_Sequential)):\n",
    "    print('All the LDM elements of the list have been treated')\n",
    "else:\n",
    "    print('An error occurred with the LDM elements!')\n",
    "    \n",
    "    \n",
    "print(f'ATL_element_counter:{ATL_element_counter}, LDM_element_counter:{LDM_element_counter}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Output Dataframes:\n",
    "\n",
    "#list(image_attachments.columns)\n",
    "#list(new_attachment.columns) # --> Does not contain any information\n",
    "#list(ATLConfocalSettingDefinition.columns)\n",
    "#list(LDM_Block_Sequential.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata relative to the microscope and software\n",
    "image_attachments.to_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/image_attachments_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dff_image_attachments = pd.read_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/image_attachments_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata left after sorting information of interest\n",
    "new_attachment.to_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/new_attachment_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dff_new_attachment = pd.read_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/new_attachment_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata relative to ATLConfocalSettingDefinition\n",
    "# Zoom, Pinhole, ...\n",
    "ATLConfocalSettingDefinition.to_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/ATLConfocalSettingDefinition_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dff_ATLConfocalSettingDefinition = pd.read_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/ATLConfocalSettingDefinition_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata relative to LDM_Block_Sequential\n",
    "# Zoom, Pinhole, ...\n",
    "LDM_Block_Sequential.to_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/LDM_Block_Sequential_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dff_Block_Sequential = pd.read_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/LDM_Block_Sequential_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Output dictionnaries with respective DataFrame lists:\n",
    "\n",
    "# sub_xml_ATLConfocalSettingDefinition_dict\n",
    "# len(lst_sub_xml_ATLConfocalSettingDefinition)\n",
    "\n",
    "# sub_xml_LDM_Block_Sequential_dict\n",
    "# len(lst_sub_xml_LDM_Block_Sequential)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build detector info dataframe for each files / image ID / channel color\n",
    "\n",
    "for key, value in sub_xml_ATLConfocalSettingDefinition_dict.items(): \n",
    "    if 'Detector' in value:\n",
    "        df = lst_sub_xml_ATLConfocalSettingDefinition[key]\n",
    "        df = df.reset_index(drop=False)\n",
    "        \n",
    "        # Select the rows where Gain > 100\n",
    "        _Gain = df.filter(list(filter(lambda x: [x for y in x if x.endswith('Gain')], df.columns))).astype('float') > 100\n",
    "        df.drop(_Gain[~_Gain['ATLConfocalSettingDefinition.DetectorList.Detector.@Gain']].index, axis=0, inplace=True)\n",
    "        \n",
    "        detector_information = df.set_index('index')\n",
    "        del(df)\n",
    "        \n",
    "        \n",
    "        \n",
    "for key, value in sub_xml_ATLConfocalSettingDefinition_dict.items():   \n",
    "    if 'MultiBand' in value:\n",
    "        df = lst_sub_xml_ATLConfocalSettingDefinition[key]\n",
    "        df = df.reset_index(drop=False)\n",
    "        \n",
    "        # Select the rows where @ChannelName correspond at @ChannelName in the 'Detector' dataframe\n",
    "        _ChannelName = list(filter(lambda x: [x for y in x if x.endswith('@ChannelName')], df.columns))\n",
    "\n",
    "        dict_multiband = dict()\n",
    "\n",
    "        for i, (uniq_id, channel) in enumerate(zip(list(detector_information['Image.@UniqueID'].values), detector_information[list(filter(lambda x: [x for y in x if x.endswith('@ChannelName')], detector_information.columns))].values.tolist())):\n",
    "            dict_multiband.update({i:df[(df['Image.@UniqueID'] == uniq_id) & (df[_ChannelName[0]].isin(channel))]})\n",
    "\n",
    "        multiband = pd.concat(dict_multiband)\n",
    "        multiband = multiband.set_index('index')\n",
    "        del(df)\n",
    "        \n",
    "        \n",
    "        \n",
    "for key, value in sub_xml_ATLConfocalSettingDefinition_dict.items():   \n",
    "    if 'LUT' in value:\n",
    "        df = lst_sub_xml_ATLConfocalSettingDefinition[key]\n",
    "        df = df.reset_index(drop=False)\n",
    "        \n",
    "        # Select the rows where @Channel correspond at @Channel in the 'Detector' dataframe\n",
    "        _Channel = list(filter(lambda x: [x for y in x if x.endswith('@Channel')], df.columns))\n",
    "\n",
    "        dict_LUT = dict()\n",
    "\n",
    "        for i, (uniq_id, channel) in enumerate(zip(list(detector_information['Image.@UniqueID'].values), detector_information[list(filter(lambda x: [x for y in x if x.endswith('@Channel')], detector_information.columns))].values.tolist())):\n",
    "            dict_LUT.update({i:df[(df['Image.@UniqueID'] == uniq_id) & (df[_Channel[0]].isin(channel))]})\n",
    "\n",
    "        lut = pd.concat(dict_LUT)\n",
    "        lut = lut.set_index('index')\n",
    "        del(df)\n",
    "\n",
    "\n",
    "\n",
    "# Merge both Dataframe on the Name of the files, the Image Names, the Image Unique IDs and the Channel column:\n",
    "detector_information = detector_information.merge(multiband, how='outer', left_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.DetectorList.Detector.@Channel'],\n",
    "                 right_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.Spectro.MultiBand.@Channel'], sort=False, suffixes=('', '@Repeat'),\n",
    "                            indicator=True)\n",
    "\n",
    "# Check that all the rows from multiband have a counterpart in the detector_information:\n",
    "if detector_information['_merge'].unique().tolist() == ['both', 'left_only']:\n",
    "    # Check which are the images that got 'left_only':\n",
    "    if detector_information[detector_information['_merge'] == 'left_only']['ATLConfocalSettingDefinition.DetectorList.Detector.@Name'].unique().tolist() == ['PMT Trans']:\n",
    "        print('\"left_only\" corresponds to the PMT Trans channel')\n",
    "        # Drop the '_merge' column:\n",
    "        detector_information.drop(['_merge'], axis=1, inplace=True)\n",
    "    else:\n",
    "        print('Error while merging the dataframes.')\n",
    "elif detector_information['_merge'].unique().tolist() == ['both']:\n",
    "    print('No transmission channel')\n",
    "    # Drop the '_merge' column:\n",
    "    detector_information.drop(['_merge'], axis=1, inplace=True)\n",
    "else:\n",
    "    print('Error while merging the dataframes.')\n",
    "\n",
    "\n",
    "# Merge both Dataframe on the Name of the files, the Image Names, the Image Unique IDs and the Channel column:\n",
    "detector_information = detector_information.merge(lut, how='outer', left_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.DetectorList.Detector.@Channel'],\n",
    "                 right_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.LUT_List.LUT.@Channel'], sort=False, suffixes=('', '@Repeat'),\n",
    "                            indicator=True)\n",
    "\n",
    "# Check that all the rows from lut have a counterpart in the detector_information:\n",
    "if detector_information['_merge'].unique().tolist() == ['both']:\n",
    "    print('\"lut\" and \"detector_information\" dataframes fully merged.')\n",
    "    # Drop the '_merge' column:\n",
    "    detector_information.drop(['_merge'], axis=1, inplace=True)\n",
    "else:\n",
    "    print('Error while merging the dataframes.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata relative to detector_information\n",
    "# Zoom, Pinhole, ...\n",
    "detector_information.to_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/detector_information_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dff_detector_information = pd.read_parquet('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/detector_information_metadata.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build laser info dataframe for each files / image ID\n",
    "\n",
    "for key, value in sub_xml_ATLConfocalSettingDefinition_dict.items():\n",
    "    if 'Laser' in value:\n",
    "        df = lst_sub_xml_ATLConfocalSettingDefinition[key]\n",
    "        df = df.reset_index(drop=False)\n",
    "\n",
    "        # Select the rows where PowerState is On\n",
    "        _PowerState = df.filter(list(filter(lambda x: [x for y in x if x.endswith('PowerState')], df.columns))) == 'On'\n",
    "        df.drop(_PowerState[~_PowerState['ATLConfocalSettingDefinition.LaserArray.Laser.@PowerState']].index, inplace=True)\n",
    "\n",
    "        laser_information = df.set_index('index')\n",
    "        del(df)\n",
    "\n",
    "\n",
    "for key, value in sub_xml_ATLConfocalSettingDefinition_dict.items():\n",
    "    if 'Shutter' in value:\n",
    "        df = lst_sub_xml_ATLConfocalSettingDefinition[key]\n",
    "        df = df.reset_index(drop=False)\n",
    "\n",
    "        # Select the rows where @LightSourceName correspond at @LightSourceName in the 'Laser' dataframe\n",
    "        _LightSourceName = list(filter(lambda x: [x for y in x if x.endswith('@LightSourceName')], laser_information.columns))\n",
    "        \n",
    "        dict_shutter = dict()\n",
    "        \n",
    "        for j, (uniq_id, light_source) in enumerate(zip(list(laser_information['Image.@UniqueID'].values), list(laser_information[_LightSourceName].values.tolist()))):\n",
    "\n",
    "            df1 = df[(df['Image.@UniqueID'] == uniq_id) & (df['ATLConfocalSettingDefinition.ShutterList.Shutter.@LightSourceName'].isin(light_source))]\n",
    "\n",
    "            _Active_Shutter = df1.filter(list(filter(lambda x: [x for y in x if x.endswith('@IsActive')], df1.columns))) == '1'\n",
    "            df1.drop(_Active_Shutter[~_Active_Shutter['ATLConfocalSettingDefinition.ShutterList.Shutter.@IsActive']].index, inplace=True)\n",
    "\n",
    "            dict_shutter.update({j:df1})\n",
    "            del(df1)\n",
    "\n",
    "        shutter = pd.concat(dict_shutter)\n",
    "        shutter = shutter.set_index('index')\n",
    "        del(df)\n",
    "    \n",
    "    \n",
    "for key, value in sub_xml_ATLConfocalSettingDefinition_dict.items():\n",
    "        \n",
    "    if 'Aotf' in value:\n",
    "        df = lst_sub_xml_ATLConfocalSettingDefinition[key]\n",
    "        df = df.reset_index(drop=False)\n",
    "\n",
    "        # Select the rows where @LightSourceName correspond at @LightSourceName in the 'Laser' dataframe\n",
    "        dict_Aotf = dict()\n",
    "\n",
    "        for k, (uniq_id, light_source) in enumerate(zip(list(laser_information['Image.@UniqueID'].values), laser_information[_LightSourceName].values.tolist())):\n",
    "\n",
    "            df1 = df[(df['Image.@UniqueID'] == uniq_id) & (df['ATLConfocalSettingDefinition.AotfList.Aotf.@LightSourceName'].isin(light_source))]\n",
    "\n",
    "            dict_Aotf.update({k:df1})\n",
    "            del(df1)\n",
    "\n",
    "        aotf = pd.concat(dict_Aotf)\n",
    "        aotf = aotf.set_index('index')\n",
    "        del(df)\n",
    "\n",
    "    \n",
    "    \n",
    "# Merge both Dataframe on the Name of the files, the Image Names, the Image Unique IDs and the LightSourceType column:\n",
    "laser_information = laser_information.merge(shutter, how='inner', left_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.LaserArray.Laser.@LightSourceType'],\n",
    "                 right_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.ShutterList.Shutter.@LightSourceType'], sort=False, suffixes=('', '@Repeat'),\n",
    "                            indicator=True)\n",
    "\n",
    "# Check that all the rows from shutter have a counterpart in the laser_information:\n",
    "if laser_information['_merge'].unique().tolist() == ['both']:\n",
    "    print('\"shutter\" and \"laser_information\" dataframes fully merged.')\n",
    "    # Drop the '_merge' column:\n",
    "    laser_information.drop(['_merge'], axis=1, inplace=True)\n",
    "else:\n",
    "    print('Error while merging the dataframes.')\n",
    "    \n",
    "\n",
    "\n",
    "# Merge both Dataframe on the Name of the files, the Image Names, the Image Unique IDs and the LightSourceType column:\n",
    "laser_information = laser_information.merge(aotf, how='left', left_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.LaserArray.Laser.@LightSourceType'],\n",
    "                 right_on=['FileName', 'Image.@Name', 'Image.@UniqueID', 'ATLConfocalSettingDefinition.AotfList.Aotf.@LightSourceType'], sort=False, suffixes=('', '@Repeat'),\n",
    "                            indicator=True)\n",
    "\n",
    "# Check that all the rows from aotf have a counterpart in the laser_information:\n",
    "if laser_information['_merge'].unique().tolist() == ['both']:\n",
    "    print('\"aotf\" and \"laser_information\" dataframes fully merged.')\n",
    "    # Drop the '_merge' column:\n",
    "    laser_information.drop(['_merge'], axis=1, inplace=True)\n",
    "else:\n",
    "    print('Error while merging the dataframes.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store the metadata relative to laser_information\n",
    "# Zoom, Pinhole, ...\n",
    "laser_information.to_pickle('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/laser_information_metadata.pickle')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dff_laser_information = pd.read_pickle('/Users/virginie/bioformats/notebooks/metadata_leica_files/parquets_and_pickles/laser_information_metadata.pickle')"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}