tests/valtests.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!-- Auto table of contents -->\n",
    "<h1 class='tocIgnore'>Validation tests</h1>\n",
    "\n",
    "**pyaudisam**: Automation of Distance Sampling analyses with [Distance software](http://distancesampling.org/)\n",
    "\n",
    "Copyright (C) 2021 Jean-Philippe Meuret\n",
    "\n",
    "This program is free software: you can redistribute it and/or modify it under the terms\n",
    "of the GNU General Public License as published by the Free Software Foundation,\n",
    "either version 3 of the License, or (at your option) any later version.\n",
    "\n",
    "This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;\n",
    "without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.\n",
    "See the GNU General Public License for more details.\n",
    "\n",
    "You should have received a copy of the GNU General Public License along with this program.\n",
    "If not, see https://www.gnu.org/licenses/.\n",
    "\n",
    "<div style=\"overflow-y: auto\">\n",
    "  <h2 class='tocIgnore'>Table of contents</h2>\n",
    "  <div id=\"toc\"></div>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%javascript\n",
    "$.getScript('ipython_notebook_toc.js')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import shutil\n",
    "import pathlib as pl\n",
    "import importlib as implib\n",
    "\n",
    "import re\n",
    "\n",
    "from collections import OrderedDict as odict, namedtuple as ntuple\n",
    "\n",
    "import math\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from IPython.display import HTML, Markdown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sys.path.insert(0, '..')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyaudisam as ads\n",
    "\n",
    "ads.runtime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Commons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create temporary directory if not yet done.\n",
    "tmpDir = pl.Path('tmp')\n",
    "tmpDir.mkdir(exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Logging configuration.\n",
    "ads.log.configure(handlers=[sys.stdout, tmpDir / 'valtst.log'], reset=True,\n",
    "                  loggers=[dict(name='matplotlib', level=ads.WARNING),\n",
    "                           dict(name='ads', level=ads.INFO),\n",
    "                           #dict(name='ads.dat', level=ads.INFO2),\n",
    "                           #dict(name='ads.eng', level=ads.INFO2),\n",
    "                           dict(name='ads.anr', level=ads.INFO2),\n",
    "                           dict(name='ads.onr', level=ads.INFO2),\n",
    "                           dict(name='ads.rep', level=ads.INFO1),\n",
    "                           dict(name='valtst', level=ads.DEBUG)])\n",
    "\n",
    "logger = ads.logger('valtst')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Activate Warnings as Exceptions\n",
    "if False:\n",
    "    \n",
    "    import warnings\n",
    "\n",
    "    warnings.filterwarnings(action='error')\n",
    "\n",
    "    # pd.read_excel\n",
    "    warnings.filterwarnings(action='default', module='etree')\n",
    "    warnings.filterwarnings(action='default', module='xlrd')\n",
    "    warnings.filterwarnings(action='default', module='defusedxml')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def backup(fpn, to='.', tsFmt='.%y%m%d'):\n",
    "    \"\"\"Backup given file to target folder with custom-formatted timestamp in name\"\"\"\n",
    "    fpn = pl.Path(fpn)\n",
    "    tn = fpn.stem + pd.Timestamp.now().strftime(tsFmt) + fpn.suffix\n",
    "    tp = pl.Path(to) if to != '.' else fpn.parent\n",
    "    print('Backingup to', (tp / tn).as_posix())\n",
    "    tpn = tp / tn\n",
    "    shutil.copy(fpn, tpn)\n",
    "    return tpn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Short string for sample \"identification\"\n",
    "def sampleAbbrev(sSample):\n",
    "    \n",
    "    abrvSpe = ''.join(word[:4].title() for word in sSample['Espèce'].split(' ')[:2])\n",
    "    \n",
    "    sampAbbrev = '{}-{}-{}-{}'.format(abrvSpe, sSample.Passage.replace('+', ''),\n",
    "                                      sSample.Adulte.replace('+', ''), sSample['Durée'])\n",
    "    \n",
    "    return sampAbbrev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Short string for analysis \"identification\"\n",
    "def analysisAbbrev(sAnlys):\n",
    "    \n",
    "    # Sample abbreviation\n",
    "    abbrevs = [sampleAbbrev(sAnlys)]\n",
    "\n",
    "    # Model + Parameters abbreviation\n",
    "    abbrevs += [sAnlys['FonctionClé'][:3].lower(), sAnlys['SérieAjust'][:3].lower()]\n",
    "    dTroncAbrv = { 'l': 'TrGche' if 'TrGche' in sAnlys.index else 'TroncGche',\n",
    "                   'r': 'TrDrte' if 'TrDrte' in sAnlys.index else 'TroncDrte',\n",
    "                   'm': 'NbTrches' if 'NbTrches' in sAnlys.index else 'NbTrModel'\n",
    "                                   if 'NbTrModel' in sAnlys.index else  'NbTrchMod',\n",
    "                   'd': 'NbTrDiscr' }\n",
    "    for abrv, name in dTroncAbrv.items():\n",
    "        if name in sAnlys.index and not pd.isnull(sAnlys[name]):\n",
    "            abbrevs.append('{}{}'.format(abrv, sAnlys[name][0].lower() if isinstance(sAnlys[name], str)\n",
    "                                               else int(sAnlys[name])))\n",
    "   \n",
    "    return '-'.join(abbrevs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jump to :\n",
    "* [II. Run pre-analyses / 0. Data Description](#II.-Run-pre-analyses)\n",
    "* [III. Run analyses with same real life field data / 0. Data Description](#III.-Run-analyses-with-same-real-life-field-data)\n",
    "* [IV. Run truncation opt-analyses with same real life field data](#IV.-Run-truncation-opt-analyses-with-same-real-life-field-data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# I. Generate input files for manual analyses in Distance interactive software\n",
    "\n",
    "* through an Excel input field data file,\n",
    "* and a reference output file set, proved as OK by using it in Distance software ;\n",
    "* automated comparison to reference is achieved at the end.\n",
    "\n",
    "WARNING: \n",
    "* Obsolete, as the same can be done through an MCDSPreAnalyser (see [II.4A.c.](#(option)-c.-Generate-input-files-for-manual-analyses-with-Distance-GUI) below).\n",
    "* And ... such unit tests already done in unintests.ipynb and unint_engine_test.py !"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDistCases = pd.DataFrame([dict(inFileName='ACDC2019-Papyrus-ALAARV-saisie-ttes-cols.xlsx',\n",
    "                                 decimalFields=['EFFORT', 'DISTANCE', 'NOMBRE'],\n",
    "                                 refOutFileName='ACDC2019-Papyrus-ALAARV-saisie-5-cols.txt', withExtraFields=False),\n",
    "                            dict(inFileName='ACDC2019-Papyrus-ALAARV-saisie-ttes-cols.xlsx',\n",
    "                                 decimalFields=['EFFORT', 'DISTANCE', 'NOMBRE'],\n",
    "                                 refOutFileName='ACDC2019-Papyrus-ALAARV-saisie-ttes-cols.txt', withExtraFields=True)])\n",
    "dfDistCases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "eng = ads.MCDSEngine(workDir=tmpDir / 'mcds-out')\n",
    "\n",
    "pl.Path(eng.workDir, 'distance-in').mkdir(exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fails = 0\n",
    "for ind, sCase in dfDistCases.iterrows():\n",
    "    \n",
    "    print('#', ind, ':', sCase.inFileName)\n",
    "\n",
    "    # Create data set\n",
    "    sds = ads.SampleDataSet(source=pl.Path('refin', sCase.inFileName),\n",
    "                           decimalFields=sCase.decimalFields)\n",
    "    \n",
    "    # Build distance import data file\n",
    "    ofn = pl.Path(eng.workDir, 'distance-in', sCase.refOutFileName)\n",
    "    ofn = eng.buildDistanceDataFile(sds, tgtFilePathName=ofn, withExtraFields=sCase.withExtraFields)\n",
    "    \n",
    "    # Compare generated file to reference\n",
    "    rfn = pl.Path('refout', sCase.refOutFileName)\n",
    "    with open(ofn, 'r') as fOut, open(rfn, 'r') as fRef:\n",
    "        if fOut.read() == fRef.read():\n",
    "            print('Success : Conform to reference.')\n",
    "        else:\n",
    "            print('Error: Generated file differs from reference', rfn)\n",
    "            fails += 1\n",
    "            \n",
    "    print()\n",
    "    \n",
    "print('All test cases succeeded !' if fails == 0 else 'Error: {} test case(s) failed.'.format(fails))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "eng.shutdown()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# II. Run and report pre-analyses\n",
    "\n",
    "Thanks to MCDSPreAnalyser and MCDSPreReport.\n",
    "\n",
    "Short code, fast (parallel) run.\n",
    "\n",
    "Note: 2 modes here, with explicit or implicit sample specification (manual switch).\n",
    "\n",
    "Note: The exact same results (implicit mode) and reports can be produced through the command line :\n",
    "```\n",
    "$ cd ..\n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-preanlr -n --preanalyses --prereports excel,html -u\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Data description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "transectPlaceCols = ['Point']\n",
    "passIdCol = 'Passage'\n",
    "effortCol = 'Effort'\n",
    "\n",
    "sampleDecCols = [effortCol, 'Distance']\n",
    "\n",
    "sampleNumCol = 'NumEchant'\n",
    "sampleSelCols = ['Espèce', passIdCol, 'Adulte', 'Durée']\n",
    "\n",
    "sampleAbbrevCol = 'AbrevEchant'\n",
    "\n",
    "speciesAbbrevCol = 'AbrevEsp'\n",
    "\n",
    "dSurveyArea = dict(Zone='ACDC', Surface='2400')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jump to [III. Run analyses with same real life field data / 0. Data Description](#III.-Run-analyses-with-same-real-life-field-data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Individuals data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfObsIndiv = ads.DataSet('refin/ACDC2019-Naturalist-extrait-ObsIndiv.ods', sheet='DonnéesIndiv').dfData\n",
    "dfObsIndiv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "{ col: dfObsIndiv[col].unique() for col in ['Observateur', 'Point', 'Passage', 'Adulte', 'Durée', 'Espèce'] }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Actual transects\n",
    "\n",
    "(can't deduce them from data, some points are missing because of data selection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfTransects = ads.DataSet('refin/ACDC2019-Naturalist-extrait-ObsIndiv.ods', sheet='Inventaires').dfData\n",
    "dfTransects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Samples to pre-analyse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Implicit variants\n",
    "varEspeces = ['Sylvia atricapilla', 'Turdus merula', 'Luscinia megarhynchos'] # 1 variante espèce ... par espèce <8-]\n",
    "\n",
    "varPassages = ['b', 'a+b'] # Passage b ou a+b => 2 variantes\n",
    "varAdultes = ['m'] # Les mâles, et ensuite les mâles et autres adultes (=> 2 variantes)\n",
    "varDurees = ['5mn', '10mn'] # 5 1ères mn, ou toutes les 10 => 2 variantes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Explicitation of variants or not\n",
    "# a. Implicites specs\n",
    "dImplSampleSpecs = { 'Espèce': varEspeces, 'Passage': varPassages, 'Adulte': varAdultes, 'Durée':   varDurees }\n",
    "\n",
    "specsAreExplicit = False  # Manually switch for testing explicit mode !\n",
    "if specsAreExplicit:\n",
    "    \n",
    "    # b. Explicit combinations\n",
    "    dfExplSampleSpecs = ads.Analyser.explicitVariantSpecs(dict(_impl=dImplSampleSpecs))\n",
    "    #dfExplSampleSpecs = ads.Analyser.explicitPartialVariantSpecs(dImplSampleSpecs) # Just the same, but less generic.\n",
    "\n",
    "    # c. Add sample order columns (usefull for reports, as pre-analyses are run parallely !).\n",
    "    #dfExplSampleSpecs.reset_index(drop=False, inplace=True)\n",
    "    #dfExplSampleSpecs.rename(columns=dict(index=sampleNumCol), inplace=True)\n",
    "\n",
    "    # d. Add sample abbreviation column (mainly for analysis traces)\n",
    "    #dfExplSampleSpecs[sampleAbbrevCol] = dfExplSampleSpecs.apply(sampleAbbrev, axis='columns')\n",
    "\n",
    "    # e. Add neutral and pass-through column (from sample specs to results)\n",
    "    dfExplSampleSpecs[speciesAbbrevCol] = dfExplSampleSpecs['Espèce'].apply(lambda s: ''.join(m[:4] for m in s.split()))\n",
    "    \n",
    "    print(dfExplSampleSpecs)\n",
    "    \n",
    "else:\n",
    "    \n",
    "    # b. Keep unexplicited : run will do automatically\n",
    "    implSampleSpecs = dict(_impl=dImplSampleSpecs)\n",
    "    \n",
    "    print(implSampleSpecs)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "computed = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4A. Or : Really run pre-analyses\n",
    "\n",
    "Note: The exact same results (implicit mode) can be produced through the command line :\n",
    "```\n",
    "$ cd ..\n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-preanlr -n --preanalyses -u\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. MCDSPreAnalyser object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "preAnlysr = \\\n",
    "    ads.MCDSPreAnalyser(dfObsIndiv, dfTransects=dfTransects, dSurveyArea=dSurveyArea,\n",
    "                        transectPlaceCols=transectPlaceCols, passIdCol=passIdCol, effortCol=effortCol,\n",
    "                        sampleSelCols=sampleSelCols, sampleDecCols=sampleDecCols, sampleSpecCustCols=[speciesAbbrevCol],\n",
    "                        abbrevCol=sampleAbbrevCol, abbrevBuilder=sampleAbbrev, sampleIndCol=sampleNumCol,\n",
    "                        distanceUnit='Meter', areaUnit='Hectare',\n",
    "                        surveyType='Point', distanceType='Radial', clustering=False,\n",
    "                        resultsHeadCols=dict(before=[sampleNumCol], sample=sampleSelCols,\n",
    "                                             after=([speciesAbbrevCol] if specsAreExplicit else []) + [sampleAbbrevCol]),\n",
    "                        workDir=workDir, logProgressEvery=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert preAnlysr.specs == {\n",
    "    'Zone': 'ACDC',\n",
    "    'Surface': '2400',\n",
    "    'distanceUnit': 'Meter',\n",
    "    'areaUnit': 'Hectare',\n",
    "    'runMethod': 'subprocess.run',\n",
    "    'runTimeOut': 300,\n",
    "    'surveyType': 'Point',\n",
    "    'distanceType': 'Radial',\n",
    "    'clustering': False,\n",
    "    'defEstimKeyFn': 'UNIFORM',\n",
    "    'defEstimAdjustFn': 'POLY',\n",
    "    'defEstimCriterion': 'AIC',\n",
    "    'defCVInterval': 95,\n",
    "    'defMinDist': None,\n",
    "    'defMaxDist': None,\n",
    "    'defFitDistCuts': None,\n",
    "    'defDiscrDistCuts': None\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Check pre-analyses specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfExplSampleSpecs, userParamSpecCols, intParamSpecCols, unmUserParamSpecCols, verdict, reasons = \\\n",
    "    preAnlysr.explicitParamSpecs(dfExplParamSpecs=dfExplSampleSpecs if specsAreExplicit else None,\n",
    "                                 implParamSpecs=implSampleSpecs if not specsAreExplicit else None,\n",
    "                                 dropDupes=True, check=True)\n",
    "\n",
    "print(verdict, reasons, len(dfExplSampleSpecs), userParamSpecCols, intParamSpecCols, unmUserParamSpecCols)\n",
    "\n",
    "assert len(dfExplSampleSpecs) == 12\n",
    "assert userParamSpecCols == [] # No analysis params here (auto. generated by PreAnalyser)\n",
    "assert intParamSpecCols == [] # Idem\n",
    "assert unmUserParamSpecCols == []\n",
    "assert verdict\n",
    "assert not reasons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (option) c. Generate input files for manual analyses with Distance GUI\n",
    "\n",
    "(not needed for pre-analyses: here only for example)\n",
    "\n",
    "Notes:\n",
    "* Only minimalistic checks done here, as already more deeply tested in unintests.ipynb and unint_engine_test.py\n",
    "* The exact same results can be produced through the command line:\n",
    "```\n",
    "$ cd ..\n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-preanlr -n --distexport -u\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "preAnlysr.exportDSInputData(dfExplSampleSpecs=dfExplSampleSpecs if specsAreExplicit else None,\n",
    "                            implSampleSpecs=implSampleSpecs if not specsAreExplicit else None,\n",
    "                            format='Distance')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check generated file list\n",
    "expdGenFileNames = []\n",
    "for esp in varEspeces:\n",
    "    for pas in varPassages:\n",
    "        for ad in varAdultes:\n",
    "            for dur in varDurees:\n",
    "                sampAbbrv = sampleAbbrev(pd.Series({'Espèce': esp, passIdCol: pas, 'Adulte': ad, 'Durée': dur}))\n",
    "                fileName = f'{sampAbbrv}-dist.txt'\n",
    "                expdGenFileNames.append(fileName)\n",
    "                print('*', fileName)\n",
    "assert all(fpn.name in expdGenFileNames for fpn in preAnlysr.workDir.glob('*-dist.txt'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check 1 generated file\n",
    "sampAbbrv = sampleAbbrev(pd.Series({'Espèce': 'Luscinia megarynchos', 'Passage': 'a+b', 'Adulte': 'm', 'Durée': '10mn'}))\n",
    "fpnSamp = preAnlysr.workDir / f'{sampAbbrv}-dist.txt'\n",
    "dfSampDist = pd.read_csv(fpnSamp, sep='\\t', decimal=',')\n",
    "dfSampDist\n",
    "\n",
    "assert len(dfSampDist) == 182\n",
    "assert dfSampDist.columns.tolist() == ['Region*Label', 'Region*Area', 'Point transect*Label', 'Point transect*Survey effort', 'Observation*Radial distance']\n",
    "assert dfSampDist['Point transect*Label'].nunique() == 96\n",
    "assert all(dfSampDist['Region*Label'].unique() == [dSurveyArea['Zone']])\n",
    "assert all(dfSampDist['Region*Area'].unique() == [int(dSurveyArea['Surface'])])\n",
    "assert dfSampDist['Point transect*Survey effort'].sum() == 362\n",
    "assert dfSampDist['Observation*Radial distance'].isnull().sum() == 26"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### d. Run pre-analyses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Model fall-down strategy\n",
    "modelStrategy = [dict(keyFn=kf, adjSr=js, estCrit='AIC', cvInt=95) \\\n",
    "                 for js in['COSINE', 'POLY', 'HERMITE']\n",
    "                 for kf in['HNORMAL', 'HAZARD', 'UNIFORM', 'NEXPON']]\n",
    "\n",
    "# Note: For real bird study analyses, you'll probably avoid NEXPON key function (model with no shoulder : g'(0) << 1).\n",
    "#       And also HERMITE adjustment series (overkill fitting)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "preResults = preAnlysr.run(dfExplSampleSpecs if specsAreExplicit else None,\n",
    "                           implSampleSpecs=implSampleSpecs if not specsAreExplicit else None, \n",
    "                           dModelStrategy=modelStrategy, threads=12)\n",
    "\n",
    "preAnlysr.shutdown()\n",
    "\n",
    "computed = True\n",
    "\n",
    "preResults.specs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Performances figures on a Ruindows 10 laptop with PCI-e SSD, \"optimal performances\" power scheme, Python 3.8 :\n",
    "* 4-HT-core i5-8350U:\n",
    "  * 2021 (precise date ?): 50s to ~1mn10s elapsed for 12 samples, 6-12 threads\n",
    "* 6-core i7-10750H (HT off):\n",
    "  * 2022-01-17, 2023-11-02: 39-40s elapsed for 12 samples, 6-12 threads (N=5)\n",
    "\n",
    "Performances figures on a Ruindows 11 laptop with PCI-e SSD, \"high performances\" power scheme, Python 3.8 :\n",
    "* 6-core i7-10750H (HT on):\n",
    "  * 2024-03-02: 40s elapsed for 12 samples, 6 threads (N=1)\n",
    "  * 2024-03-02: 39s elapsed for 12 samples, 12 threads (N=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert not specsAreExplicit or speciesAbbrevCol in preResults.dfTransData('fr').columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "preResults.dfData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfFrRes = preResults.dfTransData('fr')\n",
    "dfFrRes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for anlysFolderPath in dfFrRes.DossierExec:\n",
    "    print(anlysFolderPath)\n",
    "    assert {fpn.name for fpn in pl.Path(anlysFolderPath).iterdir()} == {'cmd.txt', 'data.txt', 'log.txt', 'output.txt', 'plots.txt', 'stats.txt'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### e. Save results for later reload or examination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "preResFileName = workDir / 'valtests-preanalyses-results.xlsx'\n",
    "\n",
    "preResults.toExcel(preResFileName)\n",
    "\n",
    "_ = backup(preResFileName)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#preResults.toExcel(workDir / 'valtests-preanalyses-results-fr.xlsx', lang='fr')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4B. Or : Load pre-analyses from a previous run\n",
    "\n",
    "(already run and saved above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not computed:\n",
    "    \n",
    "    # An analyser object knowns how to build an empty results object ...\n",
    "    preAnlysr = \\\n",
    "        ads.MCDSPreAnalyser(dfObsIndiv, dfTransects=dfTransects, dSurveyArea=dSurveyArea,\n",
    "                            transectPlaceCols=transectPlaceCols, passIdCol=passIdCol, effortCol=effortCol,\n",
    "                            sampleSelCols=sampleSelCols, sampleDecCols=sampleDecCols, sampleSpecCustCols=[speciesAbbrevCol],\n",
    "                            abbrevCol=sampleAbbrevCol, abbrevBuilder=sampleAbbrev, sampleIndCol=sampleNumCol,\n",
    "                            distanceUnit='Meter', areaUnit='Hectare',\n",
    "                            surveyType='Point', distanceType='Radial', clustering=False,\n",
    "                            resultsHeadCols=dict(before=[sampleNumCol], sample=sampleSelCols,\n",
    "                                                 after=([speciesAbbrevCol] if specsAreExplicit else []) + [sampleAbbrevCol]))\n",
    "    \n",
    "    preResults = preAnlysr.setupResults()\n",
    "    \n",
    "    # Load results from file\n",
    "    preResFileName = workDir / 'valtests-preanalyses-results.xlsx'\n",
    "    print('Loading results from {} ...'.format(preResFileName))\n",
    "\n",
    "    preResults.fromExcel(preResFileName)\n",
    "    \n",
    "else:\n",
    "    \n",
    "    print('Just computed, not reloading ...')\n",
    "    \n",
    "print('... {} analyses to compare'.format(len(preResults)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Compare command-line to notebook pre-analysis results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Generate notebook results (through 4. right above)\n",
    "# => preResults\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. Generate command-line results (through an external console with relevant python env activated)\n",
    "# $ cd .. && python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-preanlr -n --preanalyses -u\n",
    "# => file(preResFileName)\n",
    "print(preResFileName)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. Load command line results\n",
    "clPreResults = preResults.copy(withData=False)\n",
    "clPreResults.fromExcel(preResFileName)\n",
    "\n",
    "# 4. Check that 2 was really run ...\n",
    "assert (clPreResults._dfData[ads.MCDSAnalysisResultsSet.CLRunStartTime].max() \\\n",
    "        - preResults._dfData[ads.MCDSAnalysisResultsSet.CLRunStartTime].max()).total_seconds() > 1, \\\n",
    "       'Please run above given command line first: you are actually comparing preResults to itself !'\n",
    "\n",
    "# 5. Compare\n",
    "assert preResults.dfTransData('en').drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumEchant') \\\n",
    "        .compare(clPreResults.dfTransData('en').drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumEchant')) \\\n",
    "        .empty\n",
    "\n",
    "logger.info('Success !')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Compare results to reference\n",
    "\n",
    "(reference generated with same kind of \"long\" code like in III above, but on another data set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load reference\n",
    "# 1. Clone results _without_ data.\n",
    "rsRef = preResults.copy(withData=False)\n",
    "\n",
    "# 2. Load it with reference data (prevent re-postComputation as this ref. file is old, with now missing computed cols)\n",
    "rsRef.fromOpenDoc('refout/ACDC2019-Naturalist-extrait-PreResultats.ods', postComputed=True)  \n",
    "\n",
    "rsRef.dfData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare (ignore sample and analysis indexes, no use here).\n",
    "indexPreCols = [col for col in preResults.miCustomCols.to_list() if '(sample)' in col[0]] \\\n",
    "                + [('parameters', 'estimator key function', 'Value'),\n",
    "                   ('parameters', 'estimator adjustment series', 'Value')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subsetPreCols = [col for col in preResults.dfData.columns.to_list() \\\n",
    "                 if col in rsRef.columns\n",
    "                    and col not in indexPreCols + [col for col in preResults.miCustomCols.to_list()\n",
    "                                                   if '(sample)' not in col[0]]\n",
    "                                   + [('parameters', 'estimator selection criterion', 'Value'),\n",
    "                                      ('parameters', 'CV interval', 'Value'),\n",
    "                                      ('run output', 'start time', 'Value'),\n",
    "                                      ('run output', 'elapsed time', 'Value'),\n",
    "                                      ('run output', 'run folder', 'Value'),\n",
    "                                      ('detection probability', 'key function type', 'Value'),\n",
    "                                      ('detection probability', 'adjustment series type', 'Value'),\n",
    "                                      ('detection probability', 'Delta AIC', 'Value'),\n",
    "                                      ('density/abundance', 'density of animals', 'Delta Cv')]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiff = rsRef.compare(preResults, indexCols=indexPreCols, subsetCols=subsetPreCols, dropCloser=13, dropNans=True)\n",
    "\n",
    "assert dfDiff.empty, 'Oh oh ... some differences !'\n",
    "\n",
    "print('Yessssss !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To be perfectly honest ... there may be some 10**-14/-16 glitches (due to worksheet I/O ?) ... or not.\n",
    "dfComp = rsRef.compare(preResults, indexCols=indexPreCols, subsetCols=subsetPreCols, dropNans=True)\n",
    "dfComp = dfComp[(dfComp != np.inf).all(axis='columns')]\n",
    "dfComp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Generate HTML and Excel pre-analyses reports\n",
    "\n",
    "Note: This can be also achieved through command-line:\n",
    "```\n",
    "$ cd .. \n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-preanlr -n --prereports excel,html -u\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "R = preResults"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Super-synthesis sub-report : Selected analysis results columns for the 3 textual columns of the table\n",
    "samplePreRepCols = [\n",
    "    ('header (head)', 'NumEchant', 'Value'),\n",
    "    ('header (sample)', 'Espèce', 'Value'),\n",
    "    ('header (sample)', 'Passage', 'Value'),\n",
    "    ('header (sample)', 'Adulte', 'Value'),\n",
    "    ('header (sample)', 'Durée', 'Value'),\n",
    "    R.CLNTotObs, R.CLMinObsDist, R.CLMaxObsDist\n",
    "]\n",
    "\n",
    "paramPreRepCols = [\n",
    "    R.CLParEstKeyFn, R.CLParEstAdjSer\n",
    "    #R.CLParEstSelCrit, R.CLParEstCVInt\n",
    "]\n",
    "    \n",
    "resultPreRepCols = [\n",
    "    R.CLRunStatus,\n",
    "    R.CLNObs, R.CLEffort,\n",
    "    R.CLAic, R.CLChi2, R.CLKS, R.CLDCv,\n",
    "    \n",
    "    R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3,\n",
    "    \n",
    "    R.CLPDetec,\n",
    "    R.CLEswEdr,\n",
    "    R.CLDensity, R.CLDensityMin, R.CLDensityMax,\n",
    "    R.CLNumber, R.CLNumberMin, R.CLNumberMax\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Synthesis sub-report : Selected analysis results columns for the \n",
    "synthPreRepCols = [\n",
    "    ('header (head)', 'NumEchant', 'Value'),\n",
    "    ('header (sample)', 'Espèce', 'Value'),\n",
    "    ('header (sample)', 'Passage', 'Value'),\n",
    "    ('header (sample)', 'Adulte', 'Value'),\n",
    "    ('header (sample)', 'Durée', 'Value'),\n",
    "    R.CLParEstKeyFn,\n",
    "    R.CLParEstAdjSer,\n",
    "    #R.CLParEstSelCrit,\n",
    "    #R.CLParEstCVInt,\n",
    "    #R.CLParTruncLeft,\n",
    "    #R.CLParTruncRight,\n",
    "    #R.CLParModFitDistCuts,\n",
    " \n",
    "    R.CLNTotObs, R.CLNObs, R.CLNTotPars, R.CLEffort, R.CLDeltaAic,\n",
    "    R.CLChi2, R.CLKS, R.CLCvMUw, R.CLCvMCw, R.CLDCv,\n",
    "\n",
    "    R.CLSightRate,\n",
    "    R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3,\n",
    "    R.CLCmbQuaChi2, R.CLCmbQuaKS, R.CLCmbQuaDCv,\n",
    "\n",
    "    R.CLPDetec, R.CLPDetecMin, R.CLPDetecMax,\n",
    "    R.CLDensity, R.CLDensityMin, R.CLDensityMax,\n",
    "    R.CLNumber, R.CLNumberMin, R.CLNumberMax\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sorting columns for all the sub-reports\n",
    "sortPreRepCols = [('header (head)', 'NumEchant', 'Value')]\n",
    "sortPreRepAscend = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "preReport = ads.MCDSResultsPreReport(resultsSet=preResults,\n",
    "                                     title='PyAuDiSam Validation: Pre-analyses', subTitle='Pre-analysis results report',\n",
    "                                     anlysSubTitle='Pre-analysis results details',\n",
    "                                     description='Easy and parallel run through MCDSPreAnalyser',\n",
    "                                     keywords='pyaudisam, validation, pre-analysis',\n",
    "                                     lang='en', superSynthPlotsHeight=288,\n",
    "                                     #plotImgSize=(640, 400), plotLineWidth=1, plotDotWidth=4,\n",
    "                                     #plotFontSizes=dict(title=11, axes=10, ticks=9, legend=10),\n",
    "                                     sampleCols=samplePreRepCols, paramCols=paramPreRepCols,\n",
    "                                     resultCols=resultPreRepCols, synthCols=synthPreRepCols,\n",
    "                                     sortCols=sortPreRepCols, sortAscend=sortPreRepAscend,\n",
    "                                     tgtFolder=workDir, tgtPrefix='valtests-preanalyses-report')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xlsxPreRep = preReport.toExcel()\n",
    "\n",
    "xlsxBkpPreRep = backup(xlsxPreRep)\n",
    "\n",
    "HTML(f'Excel pre-report: <a href=\"{xlsxPreRep}\" target=\"blank\">{xlsxPreRep}</a>')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.startfile(xlsxPreRep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "htmlPreRep = preReport.toHtml()\n",
    "\n",
    "backup(htmlPreRep)\n",
    "\n",
    "print('Pre-report: ' + pl.Path(htmlPreRep).resolve().as_uri())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Compare command-line and notebook Excel pre-analyses reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Generate notebook report (through 7. right above)\n",
    "# => xlsxBkpPreRep\n",
    "print(xlsxBkpPreRep)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. Generate command-line report (through an external console with relevant python env activated)\n",
    "# $ cd .. && python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-preanlr -n --prereports excel -u\n",
    "# => xlsxPreRep\n",
    "print(xlsxPreRep)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. Load the 2 reports\n",
    "ddfNbPreReport = pd.read_excel(xlsxBkpPreRep, sheet_name=None, index_col=0)  # Notebook (backup) one\n",
    "ddfClPreReport = pd.read_excel(xlsxPreRep, sheet_name=None, index_col=0)  # Command-line one\n",
    "\n",
    "# 4. Check that 2 was really run ...\n",
    "assert (ddfClPreReport['Details']['StartTime'].max() - ddfNbPreReport['Details']['StartTime'].max()).total_seconds() > 1, \\\n",
    "       'Please run above given command line first: you are actually comparing notebook report to itself !'\n",
    "\n",
    "# 5. Compare Synthesis and Details sheets\n",
    "assert ddfNbPreReport['Synthesis'].drop(columns=['RunFolder']).set_index('NumEchant') \\\n",
    "        .compare(ddfClPreReport['Synthesis'].drop(columns=['RunFolder']).set_index('NumEchant')) \\\n",
    "        .empty\n",
    "assert ddfNbPreReport['Details'].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumEchant') \\\n",
    "        .compare(ddfClPreReport['Details'].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumEchant')) \\\n",
    "        .empty\n",
    "\n",
    "logger.info('Success !')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# III. Run analyses with same real life field data\n",
    "\n",
    "Thanks to MCDSAnalyser class.\n",
    "\n",
    "Short code, fast (parallel) run.\n",
    "\n",
    "Note: The exact same results and reports can be also produced through command line:\n",
    "```\n",
    "$ cd .. \n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-anlr -n --analyses --reports excel,html -u\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Data description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run first [II. Run pre-analyses / 0. Data Description](#II.-Run-pre-analyses)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "transectPlaceCols = ['Point']\n",
    "passIdCol = 'Passage'\n",
    "effortCol = 'Effort'\n",
    "\n",
    "sampleDecCols = [effortCol, 'Distance']\n",
    "\n",
    "sampleNumCol = 'NumEchant'\n",
    "sampleSelCols = ['Espèce', passIdCol, 'Adulte', 'Durée']\n",
    "\n",
    "varIndCol = 'NumAnlys'\n",
    "anlysAbbrevCol = 'AbrevAnlys'\n",
    "\n",
    "dSurveyArea = dict(Zone='ACDC', Surface='2400')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jump to [IV. Run truncation opt-analyses with same real life field data](#IV.-Run-truncation-opt-analyses-with-same-real-life-field-data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Individuals data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfObsIndiv = ads.DataSet('refin/ACDC2019-Naturalist-extrait-ObsIndiv.ods', sheet='DonnéesIndiv').dfData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfObsIndiv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "{ col: dfObsIndiv[col].unique() for col in ['Observateur', 'Point', 'Passage', 'Adulte', 'Durée', 'Espèce'] }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Actual transects\n",
    "\n",
    "(can't deduce them from data, some points are missing because of data selection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfTransects = ads.DataSet('refin/ACDC2019-Naturalist-extrait-ObsIndiv.ods', sheet='Inventaires').dfData\n",
    "len(dfTransects)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfTransects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Analyses specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAnlysSpecs = ads.Analyser.explicitVariantSpecs('refin/ACDC2019-Naturalist-extrait-SpecsAnalyses.xlsx', \n",
    "                                                 keep=['Echant1_impl', 'Echant2_impl', 'Modl_impl',\n",
    "                                                       'Params1_expl', 'Params2_expl'],\n",
    "                                                 varIndCol=varIndCol,\n",
    "                                                 #convertCols={ 'Durée': int }, # float 'cause of Excel\n",
    "                                                 computedCols={ anlysAbbrevCol: analysisAbbrev })\n",
    "\n",
    "len(dfAnlysSpecs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For faster debugging : reduce work.\n",
    "#dfAnlysSpecs = dfAnlysSpecs[(dfAnlysSpecs.Passage == 'a+b') & (dfAnlysSpecs.Adulte == 'm') \\\n",
    "#                            & (dfAnlysSpecs['Durée'] == '10mn') \\\n",
    "#                            & ((dfAnlysSpecs.TrGche.isnull()) | (dfAnlysSpecs.TrGche < 20)) \\\n",
    "#                            & ((dfAnlysSpecs.TrDrte.isnull()) | (dfAnlysSpecs.TrDrte <= 500))]\n",
    "#len(dfAnlysSpecs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAnlysSpecs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Recall analysis set without truncation params\n",
    "dfAnlysSpecs[['Espèce', 'Passage', 'Adulte', 'Durée', 'FonctionClé', 'SérieAjust']].drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "computed = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workDir = tmpDir / 'mcds-anlr'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4A. Or : Really run analyses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. MCDS Analyser object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "anlysr = ads.MCDSAnalyser(dfObsIndiv, dfTransects=dfTransects, dSurveyArea=dSurveyArea,\n",
    "                          transectPlaceCols=transectPlaceCols, passIdCol=passIdCol, effortCol=effortCol,\n",
    "                          sampleSelCols=sampleSelCols, sampleDecCols=sampleDecCols,\n",
    "                          abbrevCol=anlysAbbrevCol, anlysIndCol=varIndCol, sampleIndCol=sampleNumCol,\n",
    "                          distanceUnit='Meter', areaUnit='Hectare',\n",
    "                          surveyType='Point', distanceType='Radial', clustering=False,\n",
    "                          resultsHeadCols=dict(before=[varIndCol, sampleNumCol], sample=sampleSelCols,\n",
    "                                               after=[anlysAbbrevCol]),\n",
    "                          workDir=workDir, logProgressEvery=5,\n",
    "                          defEstimCriterion='AIC', defCVInterval=95)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Check analysis explicit specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAnlysSpecs, userParamSpecCols, intParamSpecCols, unmUserParamSpecCols, verdict, reasons = \\\n",
    "    anlysr.explicitParamSpecs(dfExplParamSpecs=dfAnlysSpecs, dropDupes=True, check=True)\n",
    "\n",
    "assert len(dfAnlysSpecs) == 48\n",
    "assert userParamSpecCols == ['FonctionClé', 'SérieAjust', 'TrGche', 'TrDrte', 'NbTrchMod']\n",
    "assert intParamSpecCols == ['EstimKeyFn', 'EstimAdjustFn', 'MinDist', 'MaxDist', 'FitDistCuts']\n",
    "assert unmUserParamSpecCols == []\n",
    "assert verdict\n",
    "assert not reasons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAnlysSpecs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. Run analyses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "results = anlysr.run(dfAnlysSpecs, threads=6)\n",
    "\n",
    "anlysr.shutdown()\n",
    "\n",
    "computed = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Performance figures on a Ruindows 10 laptop with PCI-e SSD, \"optimal performances\" power scheme:\n",
    "* 6-HT-core i7-8850H (python 3.7?):\n",
    "  * 2019 or 2020 before 06: min=5, max=11s elapsed for 64 analyses, 6 threads ?\n",
    "* 4-HT-core i5-8350U (python 3.8):\n",
    "  * 2021-01: min=5.3, max=5.7s elapsed for 48 analyses, 6 threads ?\n",
    "  * 2021-10-02: min=4.2s, max=5.7s (n=3) elapsed for 48 analyses, 6 threads ?\n",
    "* 6-core i7-10750H, HT disabled (python 3.8):\n",
    "  * 2022-01-01: mean=3.4s (n=4) elapsed for 48 analyses, 6 threads\n",
    "* 6-HT-core i7-10750H, HT on (python 3.8):\n",
    "  * 2023-11-02: mean=3.1s (n=2) elapsed for 48 analyses, 6 threads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "results.dfTransData('fr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "results.dfData"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### d. Save results for later reload or examination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "resFileName = workDir / 'valtests-analyses-results.xlsx'\n",
    "\n",
    "results.toExcel(resFileName)\n",
    "\n",
    "_ = backup(resFileName)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#results.toExcel(workDir / 'valtests-analyses-results-fr.xlsx', lang='fr')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4B. Or : Load analyses from a previous run\n",
    "\n",
    "(already run and saved above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not computed:\n",
    "    \n",
    "    # An analyser object knowns how to build an empty results object ...\n",
    "    anlysr = ads.MCDSAnalyser(dfObsIndiv, dfTransects=dfTransects, dSurveyArea=dSurveyArea,\n",
    "                              resultsHeadCols=dict(before=[varIndCol, sampleNumCol], sample=sampleSelCols,\n",
    "                                                   after=[anlysAbbrevCol]),\n",
    "                              transectPlaceCols=transectPlaceCols, passIdCol=passIdCol, effortCol=effortCol,\n",
    "                              sampleSelCols=sampleSelCols, sampleDecCols=sampleDecCols,\n",
    "                              abbrevCol=anlysAbbrevCol, anlysIndCol=varIndCol, sampleIndCol=sampleNumCol,\n",
    "                              distanceUnit='Meter', areaUnit='Hectare',\n",
    "                              surveyType='Point', distanceType='Radial', clustering=False)\n",
    "    \n",
    "    results = anlysr.setupResults()\n",
    "    \n",
    "    # Load results from file.\n",
    "    resFileName = workDir / 'valtests-analyses-results.xlsx'\n",
    "    print('Loading results from {} ...'.format(resFileName))\n",
    "\n",
    "    results.fromExcel(resFileName)\n",
    "    \n",
    "else:\n",
    "    \n",
    "    print('Just computed, not reloading ...')\n",
    "    \n",
    "print('... {} analyses to compare'.format(len(results)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Compare command-line to notebook analysis results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 1. Generate notebook results (through 4. right above)\n",
    "# => results\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 2. Generate command-line results (through an external console with relevant python env activated)\n",
    "# $ cd .. && python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-anlr -n --analyses -u\n",
    "# => file(resFileName)\n",
    "print(resFileName)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 3. Load command line results\n",
    "clResults = results.copy(withData=False)\n",
    "clResults.fromExcel(resFileName)\n",
    "\n",
    "# 4. Check that 2 was really run ...\n",
    "assert (clResults._dfData[ads.MCDSAnalysisResultsSet.CLRunStartTime].max() \\\n",
    "        - results._dfData[ads.MCDSAnalysisResultsSet.CLRunStartTime].max()).total_seconds() >= 1, \\\n",
    "       'Please run above given command line first: you are actually comparing results to itself !'\n",
    "\n",
    "# 5. Compare\n",
    "assert results.dfTransData('en').drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys') \\\n",
    "        .compare(clResults.dfTransData('en').drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys')) \\\n",
    "        .empty\n",
    "\n",
    "logger.info('Success !')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Compare results to reference\n",
    "\n",
    "(reference generated with same kind of \"long\" code like in III above, but on another data set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load reference\n",
    "# 1. Clone results _without_ data.\n",
    "rsRef = results.copy(withData=False)\n",
    "\n",
    "# 2. Load it with reference data (prevent re-postComputation as this ref. file is old, with now missing computed cols)\n",
    "rsRef.fromFile('refout/ACDC2019-Naturalist-extrait-Resultats.ods', postComputed=True)\n",
    "\n",
    "rsRef.dfData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare (ignore sample and analysis indexes, no use here).\n",
    "indexCols = [col for col in results.miCustomCols.to_list() if '(sample)' in col[0]] \\\n",
    "            + [('parameters', 'estimator key function', 'Value'),\n",
    "               ('parameters', 'estimator adjustment series', 'Value'),\n",
    "               ('parameters', 'left truncation distance', 'Value'),\n",
    "               ('parameters', 'right truncation distance', 'Value'),\n",
    "               ('parameters', 'model fitting distance cut points', 'Value')]\n",
    "\n",
    "# Ignore also string params (comparison not implemented) and computed values.\n",
    "subsetCols = [col for col in results.dfData.columns.to_list() \\\n",
    "              if col in rsRef.columns\n",
    "                 and col not in (indexCols + [col for col in results.miCustomCols.to_list()\n",
    "                                              if '(sample)' not in col[0]]\n",
    "                                 + [('parameters', 'estimator selection criterion', 'Value'),\n",
    "                                    ('parameters', 'CV interval', 'Value'),\n",
    "                                    ('run output', 'start time', 'Value'),\n",
    "                                    ('run output', 'elapsed time', 'Value'),\n",
    "                                    ('run output', 'run folder', 'Value'),\n",
    "                                    ('detection probability', 'key function type', 'Value'),\n",
    "                                    ('detection probability', 'adjustment series type', 'Value'),\n",
    "                                    ('detection probability', 'Delta AIC', 'Value'),\n",
    "                                    ('density/abundance', 'density of animals', 'Delta Cv')])]\n",
    "\n",
    "dfDiff = rsRef.compare(results, indexCols=indexCols, subsetCols=subsetCols, dropCloser=12, dropNans=True)\n",
    "\n",
    "assert dfDiff.empty, 'No, no, no : not the same ...'\n",
    "\n",
    "print('Yessssss !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To be perfectly honnest ... may be some 10**-12/15 glitches (due to worksheet I/O ?) or not.\n",
    "rsRef.compare(results, indexCols=indexCols, subsetCols=subsetCols, dropCloser=14, dropNans=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Generate HTML and Excel analyses reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "R = results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Super-synthesis sub-report : Selected analysis results columns for the 3 textual columns of the table\n",
    "sampleRepCols = [\n",
    "    ('header (head)', 'NumEchant', 'Value'),\n",
    "    ('header (sample)', 'Espèce', 'Value'),\n",
    "    ('header (sample)', 'Passage', 'Value'),\n",
    "    ('header (sample)', 'Adulte', 'Value'),\n",
    "    ('header (sample)', 'Durée', 'Value'),\n",
    "    R.CLNTotObs, R.CLMinObsDist, R.CLMaxObsDist\n",
    "]\n",
    "\n",
    "paramRepCols = [\n",
    "    R.CLParEstKeyFn, R.CLParEstAdjSer,\n",
    "    #R.CLParEstSelCrit, R.CLParEstCVInt,\n",
    "    R.CLParTruncLeft, R.CLParTruncRight, R.CLParModFitDistCuts\n",
    "]\n",
    "    \n",
    "resultRepCols = [\n",
    "    ('header (head)', 'NumAnlys', 'Value'),\n",
    "    R.CLRunStatus,\n",
    "    R.CLNObs, R.CLEffort,\n",
    "    R.CLAic, R.CLChi2, R.CLKS, R.CLDCv,\n",
    "    \n",
    "    R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3,\n",
    "    \n",
    "    R.CLPDetec,\n",
    "    R.CLEswEdr,\n",
    "    R.CLDensity, R.CLDensityMin, R.CLDensityMax,\n",
    "    R.CLNumber, R.CLNumberMin, R.CLNumberMax\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Synthesis sub-report: Selected analysis results columns for the table\n",
    "synthRepCols = [\n",
    "    ('header (head)', 'NumEchant', 'Value'),\n",
    "    ('header (sample)', 'Espèce', 'Value'),\n",
    "    ('header (sample)', 'Passage', 'Value'),\n",
    "    ('header (sample)', 'Adulte', 'Value'),\n",
    "    ('header (sample)', 'Durée', 'Value'),\n",
    "    ('header (head)', 'NumAnlys', 'Value'),\n",
    "    \n",
    "    R.CLParEstKeyFn, R.CLParEstAdjSer,\n",
    "    #R.CLParEstSelCrit, R.CLParEstCVInt,\n",
    "    R.CLParTruncLeft, R.CLParTruncRight, R.CLParModFitDistCuts,\n",
    " \n",
    "    R.CLNTotObs, R.CLNObs, R.CLNTotPars, R.CLEffort, R.CLDeltaAic, R.CLChi2, R.CLKS, R.CLCvMUw, R.CLCvMCw, R.CLDCv, \n",
    "    R.CLPDetec, R.CLPDetecMin, R.CLPDetecMax, R.CLDensity, R.CLDensityMin, R.CLDensityMax,\n",
    "\n",
    "    R.CLSightRate,\n",
    "    R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3,\n",
    "    R.CLCmbQuaChi2, R.CLCmbQuaKS, R.CLCmbQuaDCv,\n",
    "\n",
    "    R.CLGrpOrdSmTrAic,\n",
    "    R.CLGrpOrdClTrChi2KSDCv, #R.CLGrpOrdClTrChi2,\n",
    "    R.CLGrpOrdClTrDCv,\n",
    "    R.CLGrpOrdClTrQuaBal1, R.CLGrpOrdClTrQuaBal2, R.CLGrpOrdClTrQuaBal3, R.CLGrpOrdClTrQuaChi2,\n",
    "    R.CLGrpOrdClTrQuaKS, R.CLGrpOrdClTrQuaDCv,\n",
    "    R.CLGblOrdChi2KSDCv, R.CLGblOrdQuaBal1, R.CLGblOrdQuaBal2, R.CLGblOrdQuaBal3,\n",
    "    R.CLGblOrdQuaChi2, R.CLGblOrdQuaKS, R.CLGblOrdQuaDCv,\n",
    "    R.CLGblOrdDAicChi2KSDCv,\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sorting columns for all the sub-reports\n",
    "sortRepCols = \\\n",
    "[('header (head)', 'NumEchant', 'Value')] \\\n",
    "+ [R.CLParTruncLeft, R.CLParTruncRight,\n",
    "   R.CLDeltaAic,\n",
    "   R.CLCmbQuaBal3]\n",
    "\n",
    "sortRepAscend = [True] * (len(sortRepCols) - 1) + [False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "report = ads.MCDSResultsFullReport(resultsSet=results, \n",
    "                                   sampleCols=sampleRepCols, paramCols=paramRepCols,\n",
    "                                   resultCols=resultRepCols, synthCols=synthRepCols,\n",
    "                                   sortCols=sortRepCols, sortAscend=sortRepAscend,\n",
    "                                   title='PyAuDiSam Validation: Analyses', subTitle='Global analyses report',\n",
    "                                   anlysSubTitle='Detailed report',\n",
    "                                   description='Easy and parallel run through MCDSAnalyser',\n",
    "                                   keywords='pyaudisam, validation, analysis', pySources=['valtests.ipynb'],\n",
    "                                   lang='en', superSynthPlotsHeight=288,\n",
    "                                   #plotImgSize=(640, 400), plotLineWidth=1, plotDotWidth=4,\n",
    "                                   #plotFontSizes=dict(title=11, axes=10, ticks=9, legend=10),\n",
    "                                   tgtFolder=workDir, tgtPrefix='valtests-analyses-report')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "xlsxAnlrRep = report.toExcel()\n",
    "\n",
    "xlsxAnlrBkpRep = backup(xlsxAnlrRep)\n",
    "\n",
    "HTML(f'Excel report: <a href=\"{xlsxAnlrRep}\" target=\"blank\">{xlsxAnlrRep}</a>')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.startfile(xlsxAnlrRep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "htmlAnlrRep = report.toHtml()  # Auto-number of parallel generators \n",
    "\n",
    "backup(htmlAnlrRep)\n",
    "\n",
    "print('Report: ' + pl.Path(htmlAnlrRep).resolve().as_uri())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Compare command-line and notebook Excel analyses reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Generate notebook report (through 7. right above)\n",
    "# => xlsxAnlrBkpRep\n",
    "print(xlsxAnlrBkpRep)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. Generate command-line report (through an external console with relevant python env activated)\n",
    "# $ cd .. && python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-anlr -n --reports excel:full -u\n",
    "# => xlsxAnlrRep\n",
    "print(xlsxAnlrRep)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 3. Load the 2 reports\n",
    "ddfNbReport = pd.read_excel(xlsxAnlrBkpRep, sheet_name=None, index_col=0)  # Notebook (backup) one\n",
    "ddfClReport = pd.read_excel(xlsxAnlrRep, sheet_name=None, index_col=0)  # Command-line one\n",
    "\n",
    "# 4. Check that 2 was really run ...\n",
    "assert (ddfClReport['Details']['StartTime'].max() - ddfNbReport['Details']['StartTime'].max()).total_seconds() > 1, \\\n",
    "       'Please run above given command line first: you are actually comparing notebook report to itself !'\n",
    "\n",
    "# 5. Compare Synthesis and Details sheets\n",
    "assert ddfNbReport['Synthesis'].drop(columns=['RunFolder']).set_index('NumAnlys').sort_index() \\\n",
    "        .compare(ddfClReport['Synthesis'].drop(columns=['RunFolder']).set_index('NumAnlys').sort_index()) \\\n",
    "        .empty\n",
    "assert ddfNbReport['Details'].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys').sort_index() \\\n",
    "        .compare(ddfClReport['Details'].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys').sort_index()) \\\n",
    "        .empty\n",
    "\n",
    "logger.info('Success !')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IV. Run truncation opt-analyses with same real life field data\n",
    "\n",
    "i.e. analyses with:\n",
    "* ready-to-go (const values) analysis parameters,\n",
    "* sometimes, some distance truncation parameters auto-computed:\n",
    "    * through some kind of optimisation process around MCDS.exe,\n",
    "    * from easily specified optimisation parameters.\n",
    "\n",
    "Thanks to MCDSTruncationOptanalyser class.\n",
    "\n",
    "Note: The exact same results and reports can be also produced through command line:\n",
    "```\n",
    "$ cd .. \n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-optanlr -n --optanalyses --optreports excel,html:mqua-r92 -u\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Data description and optanalysis parameters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run first\n",
    "* [II. Run pre-analyses / 0. Data Description](#II.-Run-pre-analyses)\n",
    "* [III. Run analyses with same real life field data / 0. Data Description](#III.-Run-analyses-with-same-real-life-field-data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Source / Results data\n",
    "transectPlaceCols = ['Point']\n",
    "passIdCol = 'Passage'\n",
    "effortCol = 'Effort'\n",
    "\n",
    "sampleDistCol = 'Distance'\n",
    "sampleDecCols = [effortCol, sampleDistCol]\n",
    "\n",
    "sampleNumCol = 'NumEchant'\n",
    "sampleSelCols = ['Espèce', passIdCol, 'Adulte', 'Durée']\n",
    "\n",
    "sampleAbbrevCol = 'AbrevEchant'\n",
    "\n",
    "# optIndCol = 'IndOptim'\n",
    "# optAbbrevCol = 'AbrevOptim'\n",
    "\n",
    "dSurveyArea = dict(Zone='ACDC', Surface='2400')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# General DS analysis parameters\n",
    "varIndCol = 'NumAnlys'\n",
    "anlysAbbrevCol = 'AbrevAnlys'\n",
    "anlysParamCols = ['FonctionClé', 'SérieAjust', 'TrGche', 'TrDrte', 'NbTrchMod']\n",
    "\n",
    "distanceUnit = 'Meter'\n",
    "areaUnit = 'Hectare'\n",
    "surveyType = 'Point'\n",
    "distanceType = 'Radial'\n",
    "clustering = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Default optimisation parameters.\n",
    "defEstimKeyFn = 'HNORMAL'\n",
    "defEstimAdjustFn = 'COSINE'\n",
    "defEstimCriterion = 'AIC'\n",
    "defCVInterval = 95\n",
    "defMinDist = None\n",
    "defMaxDist = None, \n",
    "defFitDistCuts = None\n",
    "defDiscrDistCuts = None\n",
    "\n",
    "defExpr2Optimise = 'chi2'\n",
    "defMinimiseExpr = False\n",
    "defOutliersMethod = 'tucquant'\n",
    "defOutliersQuantCutPct = 7\n",
    "defFitDistCutsFctr = ads.Interval(min=0.6, max=1.4)\n",
    "defDiscrDistCutsFctr = ads.Interval(min=0.5, max=1.2)\n",
    "\n",
    "defSubmitTimes = 1\n",
    "defSubmitOnlyBest = None\n",
    "\n",
    "defCoreEngine = 'zoopt'\n",
    "defCoreMaxIters = 100\n",
    "defCoreTermExprValue = None\n",
    "defCoreAlgorithm = 'racos'\n",
    "defCoreMaxRetries = 0\n",
    "\n",
    "dDefSubmitOtherParams = dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Results post-computation parameters\n",
    "ldTruncIntrvSpecs = [dict(col='left', minDist=5.0, maxLen=5.0),\n",
    "                     dict(col='right', minDist=25.0, maxLen=25.0)]\n",
    "truncIntrvEpsilon = 1e-6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Les analyses à faire (avec specs d'optimisation dedans si nécessaire)\n",
    "optanlysSpecFile = 'refin/ACDC2019-Naturalist-extrait-SpecsOptanalyses.xlsx'\n",
    "#optanlysSpecFile = '../donnees/acdc/ACDC2019-Naturalist-extrait-SpecsOptanalyses-reduit.ods'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# def optimAbbrev(sAnlys):\n",
    "#     \n",
    "#     # Sample abbreviation\n",
    "#     spcAbbrev = ''.join(word[:4].title() for word in sAnlys['Espèce'].split(' ')[:2])\n",
    "#     sampAbbrev = [str(x) for x in [spcAbbrev, sAnlys.Passage.replace('+', ''),\n",
    "#                                    sAnlys.Adulte.replace('+', ''), sAnlys['Durée']]]\n",
    "# \n",
    "#     # Model + Parameters abbreviation\n",
    "#     modParAbbrev = [sAnlys['FonctionClé'][:3].lower(), sAnlys['SérieAjust'][:3].lower()]\n",
    "#     \n",
    "#     return '-'.join(sampAbbrev + modParAbbrev)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Individuals data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Les données individualisées et transects\n",
    "indivObsFile = 'refin/ACDC2019-Naturalist-extrait-ObsIndiv.ods'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfObsIndiv = ads.DataSet(indivObsFile, sheet='DonnéesIndiv').dfData\n",
    "len(dfObsIndiv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfObsIndiv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "{ col: dfObsIndiv[col].unique() for col in ['Observateur', 'Point', 'Passage', 'Adulte', 'Durée', 'Espèce'] }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Actual transects\n",
    "\n",
    "(can't deduce them from data, some points are missing because of data selection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfTransects = ads.DataSet(indivObsFile, sheet='Inventaires').dfData\n",
    "len(dfTransects)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfTransects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workDir = tmpDir / 'mcds-optanlr'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jump to [3C. Or : Load opt-analyses results from a previous run](#3C.-Or-%3A-Load-opt-analyses-results-from-a-previous-run)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3A. Or : Really run opt-analyses\n",
    "\n",
    "Note: The exact same results can be also produced through command line:\n",
    "```\n",
    "$ cd .. \n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-optanlr -n --optanalyses -u\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. MCDS Opt-Analyser object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optanlr = \\\n",
    "    ads.MCDSTruncationOptanalyser(dfObsIndiv, dfTransects=dfTransects, dSurveyArea=dSurveyArea, \n",
    "                                  transectPlaceCols=transectPlaceCols, passIdCol=passIdCol, effortCol=effortCol,\n",
    "                                  sampleSelCols=sampleSelCols, sampleDecCols=sampleDecCols, sampleDistCol=sampleDistCol,\n",
    "                                  abbrevCol=anlysAbbrevCol, abbrevBuilder=analysisAbbrev,\n",
    "                                  anlysIndCol=varIndCol, sampleIndCol=sampleNumCol,\n",
    "                                  distanceUnit=distanceUnit, areaUnit=areaUnit,\n",
    "                                  surveyType=surveyType, distanceType=distanceType, clustering=clustering,\n",
    "                                  resultsHeadCols=dict(before=[varIndCol, sampleNumCol], sample=sampleSelCols,\n",
    "                                                       after=anlysParamCols + [anlysAbbrevCol]),\n",
    "                                  ldTruncIntrvSpecs=ldTruncIntrvSpecs, truncIntrvEpsilon=truncIntrvEpsilon,\n",
    "                                  workDir=workDir, runMethod='subprocess.run', runTimeOut=120,\n",
    "                                  #runMethod='os.system', runTimeOut=None,  # Uncomment to test os.system run method.\n",
    "                                  logAnlysProgressEvery=5, logOptimProgressEvery=3, backupOptimEvery=5,\n",
    "                                  defEstimKeyFn=defEstimKeyFn, defEstimAdjustFn=defEstimAdjustFn,\n",
    "                                  defEstimCriterion=defEstimCriterion, defCVInterval=defCVInterval,\n",
    "                                  defExpr2Optimise=defExpr2Optimise, defMinimiseExpr=defMinimiseExpr,\n",
    "                                  defOutliersMethod=defOutliersMethod, defOutliersQuantCutPct=defOutliersQuantCutPct,\n",
    "                                  defFitDistCutsFctr=defFitDistCutsFctr, defDiscrDistCutsFctr=defDiscrDistCutsFctr,\n",
    "                                  defSubmitTimes=defSubmitTimes, defSubmitOnlyBest=defSubmitOnlyBest,\n",
    "                                  dDefSubmitOtherParams=dDefSubmitOtherParams,\n",
    "                                  dDefOptimCoreParams=dict(core=defCoreEngine, maxIters=defCoreMaxIters,\n",
    "                                                           termExprValue=defCoreTermExprValue,\n",
    "                                                           algorithm=defCoreAlgorithm, maxRetries=defCoreMaxRetries))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(optanlr.specs) == 27"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optanlr.specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(optanlr.specs['defFitDistCutsFctr'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Check opt-analyses specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAnlysSpecs, userParamSpecCols, intParamSpecCols, unmUserParamSpecCols, verdict, reasons = \\\n",
    "    optanlr.explicitParamSpecs(implParamSpecs=optanlysSpecFile, dropDupes=True, check=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(dfAnlysSpecs) == 60\n",
    "assert userParamSpecCols == ['FonctionClé', 'SérieAjust', 'TrGche', 'TrDrte', 'NbTrchMod', 'MultiOpt']\n",
    "assert intParamSpecCols == ['EstimKeyFn', 'EstimAdjustFn', 'MinDist', 'MaxDist', 'FitDistCuts', 'SubmitParams']\n",
    "assert unmUserParamSpecCols == []\n",
    "assert verdict\n",
    "assert not reasons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAnlysSpecs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(dfAnlysSpecs))\n",
    "if not verdict:\n",
    "    print(reasons)\n",
    "    print(userParamSpecCols, intParamSpecCols, unmUserParamSpecCols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. Run opt-analyses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('* OptAnalyser specs:', ', '.join(f'{k}={v}' for k, v in optanlr.specs.items()))\n",
    "print('* OptAnalyses specs:', len(dfAnlysSpecs), 'optimisations from', optanlysSpecFile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "optResults = optanlr.run(implParamSpecs=optanlysSpecFile, threads=24)\n",
    "# optResults = optanlr.run(dfExplParamSpecs=dfAnlysSpecs.loc[51:52], threads=1)  # A small sample, for a quicker check\n",
    "\n",
    "optanlr.shutdown()\n",
    "\n",
    "computed = True\n",
    "\n",
    "optResults.specs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Performances figures on a 4-HT-core i5-8365U Ruindows 10 laptop with PCI-e SSD, \"optimal performance power scheme\", 12 threads, Python 3.8 :\n",
    "* 2021-01-05\n",
    "  * OptAnalyserspecs: Zone=ACDC, Surface=2400, distanceUnit=Meter, areaUnit=Hectare, surveyType=Point, distanceType=Radial, clustering=False, defEstimKeyFn=HNORMAL, defEstimAdjustFn=COSINE, defEstimCriterion=AIC, defCVInterval=95, defMinDist=None, defMaxDist=None, defFitDistCuts=None, defDiscrDistCuts=None, defExpr2Optimise=chi2, defMinimiseExpr=False, dDefOptimCoreParams={'core': 'zoopt', 'maxIters': 100, 'termExprValue': None, 'algorithm': 'racos', 'maxRetries': 0}, defSubmitTimes=1, defSubmitOnlyBest=None, dDefSubmitOtherParams={}, defOutliersMethod=tucquant, defOutliersQuantCutPct=7, defFitDistCutsFctr=[0.6, 1.4], defDiscrDistCutsFctr=[0.5, 1.2]\n",
    "  * OptAnalyses specs: 60 optimisations, from refin/ACDC2019-Naturalist-extrait-SpecsOptanalyses.xlsx => 70 resultats,\n",
    "  * runMethod: subprocess.run => 4mn40, 4mn52, 4mn38, 4mn23, 4mn40, 5mn00, 4mn41, 4mn35, 4mn47 (mean 4mn42)\n",
    "  * runMethod: os.system      => 4mn35, 4mn24, 4mn20, 4mn30 (mean 4mn27)\n",
    "\n",
    "* 2021-08-22, 2021-10-02\n",
    "  * same OptAnalyserspecs, OptAnalyses specs\n",
    "  * runMethod: subprocess.run => 4mn35 (n >= 2)\n",
    "  \n",
    "* 2021-10-06\n",
    "  * same OptAnalyserspecs, OptAnalyses specs\n",
    "  * runMethod: subprocess.run => 4mn08 (n = 1)\n",
    "* 2021-11-19 After adding quality indicators computation in analysis results post-processing\n",
    "  * same OptAnalyserspecs, OptAnalyses specs\n",
    "  * runMethod: subprocess.run => 6mn21 (n = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Performances figures on a 6-core (HT disabled) i7-10850H Ruindows 10 laptop with PCI-e SSD, \"optimal performance power scheme\", Python 3.8 :\n",
    "* 2021-11-28 After optimizing quality indicators computation in analysis results post-processing\n",
    "  * same OptAnalyserspecs, OptAnalyses specs as on 2021-01-05\n",
    "  * 12 threads, runMethod: subprocess.run => 4mn12 (n = 1)\n",
    "  * 18 threads, runMethod: subprocess.run => 3mn20 (n = 1)\n",
    "  * 24 threads, runMethod: subprocess.run => 3mn30 (n = 1)\n",
    "* 2022-01-01,02 (no change)\n",
    "  * 24 threads, runMethod: subprocess.run => 3mn16 to 3mn28 (n = 2)\n",
    "* 2022-01-17 (no change)\n",
    "  * 24 threads, runMethod: subprocess.run => 3mn03 (n = 1)\n",
    "* 2023-11-02 (no change)\n",
    "  * 24 threads, runMethod: subprocess.run => 2mn58 (n = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert ads.MCDSTruncationOptanalyser.OptimTruncFlagCol in optResults.dfTransData('fr').columns\n",
    "# Note: This also runs post-computations ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResults.dfTransData('fr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResults.dfData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#optResults.dfTransData('fr').to_excel('tmp/res-tst.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#optResults._dfData.to_excel('tmp/rawres-tst.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### d. Save results for later reload or examination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResFileName = workDir / 'valtests-optanalyses-results.xlsx'\n",
    "\n",
    "optResults.toExcel(optResFileName)\n",
    "\n",
    "_ = backup(optResFileName)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResults.toOpenDoc(workDir / 'valtests-optanalyses-results-fr.ods', lang='fr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#results.fromExcel(workDir / 'valtests-optanalyses-results.xlsx', specs=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3B. Or : Restart opt-analyses from recovery files\n",
    "\n",
    "(already run above)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. MCDS Opt-Analyser object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Warning: Must be a real clone of the 3A optanalyser (about data, not technical run stuff),\n",
    "# otherwise recovery might not work.\n",
    "optanlr = \\\n",
    "    ads.MCDSTruncationOptanalyser(dfObsIndiv, dfTransects=dfTransects, dSurveyArea=dSurveyArea, \n",
    "                                  transectPlaceCols=transectPlaceCols, passIdCol=passIdCol, effortCol=effortCol,\n",
    "                                  sampleSelCols=sampleSelCols, sampleDecCols=sampleDecCols, sampleDistCol=sampleDistCol,\n",
    "                                  abbrevCol=anlysAbbrevCol, abbrevBuilder=analysisAbbrev,\n",
    "                                  anlysIndCol=varIndCol, sampleIndCol=sampleNumCol,\n",
    "                                  distanceUnit=distanceUnit, areaUnit=areaUnit,\n",
    "                                  surveyType=surveyType, distanceType=distanceType, clustering=clustering,\n",
    "                                  resultsHeadCols=dict(before=[varIndCol, sampleNumCol], sample=sampleSelCols,\n",
    "                                                       after=anlysParamCols + [anlysAbbrevCol]),\n",
    "                                  ldTruncIntrvSpecs=ldTruncIntrvSpecs, truncIntrvEpsilon=truncIntrvEpsilon,\n",
    "                                  workDir=workDir, logAnlysProgressEvery=5, logOptimProgressEvery=3,\n",
    "                                  defEstimKeyFn=defEstimKeyFn, defEstimAdjustFn=defEstimAdjustFn,\n",
    "                                  defEstimCriterion=defEstimCriterion, defCVInterval=defCVInterval,\n",
    "                                  defExpr2Optimise=defExpr2Optimise, defMinimiseExpr=defMinimiseExpr,\n",
    "                                  defOutliersMethod=defOutliersMethod, defOutliersQuantCutPct=defOutliersQuantCutPct,\n",
    "                                  defFitDistCutsFctr=defFitDistCutsFctr, defDiscrDistCutsFctr=defDiscrDistCutsFctr,\n",
    "                                  defSubmitTimes=defSubmitTimes, defSubmitOnlyBest=defSubmitOnlyBest,\n",
    "                                  dDefSubmitOtherParams=dDefSubmitOtherParams,\n",
    "                                  dDefOptimCoreParams=dict(core=defCoreEngine, maxIters=defCoreMaxIters,\n",
    "                                                           termExprValue=defCoreTermExprValue,\n",
    "                                                           algorithm=defCoreAlgorithm, maxRetries=defCoreMaxRetries))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(optanlr.specs) == 27"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Check opt-analyses specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAnlysSpecs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. Run opt-analyses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "optResults2 = optanlr.run(implParamSpecs=optanlysSpecFile, recoverOptims=True, threads=12)\n",
    "\n",
    "# A small sample, for a quicker check\n",
    "#results2 = optanlr.run(dfExplParamSpecs=dfAnlysSpecs.loc[51:52], recoverOptims=True, threads=1)\n",
    "\n",
    "optanlr.shutdown()\n",
    "\n",
    "computed = True\n",
    "\n",
    "optResults2.specs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResults2.dfTransData('fr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResults2.dfData"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### d. Save results for later reload or examination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResults2.specs.toExcel(workDir / 'vvaltests-optanalyses-results2.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3C. Or : Load opt-analyses results from a previous run\n",
    "\n",
    "(already run and saved above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if 'computed' not in dir():\n",
    "    computed = False\n",
    "\n",
    "if not computed:\n",
    "    \n",
    "    # An opt-analyser object knowns how to build an empty results object ...\n",
    "    optanlr = \\\n",
    "        ads.MCDSTruncationOptanalyser(dfObsIndiv, dfTransects=dfTransects, dSurveyArea=dSurveyArea, \n",
    "                                      transectPlaceCols=transectPlaceCols, passIdCol=passIdCol, effortCol=effortCol,\n",
    "                                      sampleSelCols=sampleSelCols, sampleDecCols=sampleDecCols,\n",
    "                                      sampleDistCol=sampleDistCol,\n",
    "                                      abbrevCol=anlysAbbrevCol, abbrevBuilder=analysisAbbrev,\n",
    "                                      anlysIndCol=varIndCol, sampleIndCol=sampleNumCol,\n",
    "                                      distanceUnit=distanceUnit, areaUnit=areaUnit,\n",
    "                                      surveyType=surveyType, distanceType=distanceType, clustering=clustering,\n",
    "                                      resultsHeadCols=dict(before=[varIndCol, sampleNumCol], sample=sampleSelCols,\n",
    "                                                           after=anlysParamCols + [anlysAbbrevCol]),\n",
    "                                      ldTruncIntrvSpecs=ldTruncIntrvSpecs, truncIntrvEpsilon=truncIntrvEpsilon)\n",
    "\n",
    "    optResults = optanlr.setupResults()\n",
    "    \n",
    "    # Load results from file.\n",
    "    optResFileName = workDir / 'valtests-optanalyses-results.xlsx'\n",
    "    print('Loading results from {} ...'.format(optResFileName))\n",
    "\n",
    "    optResults.fromExcel(optResFileName)\n",
    "    \n",
    "else:\n",
    "    \n",
    "    print('Just computed, not reloading ...')\n",
    "    \n",
    "print('... {} analyses to compare'.format(len(optResults)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jump to [5. Generate HTML and Excel opt-analyses reports](#5.-Generate-HTML-and-Excel-opt-analyses-reports)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3D. Generate reference for non-regression tests\n",
    "\n",
    "To be used in unintests.ipynb / 14. MCDSTruncOptAnalysisResultsSet / y. Non regression\n",
    "\n",
    "Warning: Needs probably to be fully reworked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 1. Clone results _without_ data.\n",
    "#results3 = optResults.copy(withData=True)\n",
    "#\n",
    "## 2. Remove analyses with non-unique 'NumAnlys' (because of multiple optimisation tries)\n",
    "##    (to make comparison easier, sorry)\n",
    "#numAnlysCols = ('header (head)', 'NumAnlys', 'Value')\n",
    "#numEchantCol = ('header (head)', 'NumEchant', 'Value')\n",
    "#\n",
    "#sb = results3.dfData[[numAnlysCols, numEchantCol]].groupby([numAnlysCols]).transform(len)[numEchantCol] > 1\n",
    "#results3.dropRows(sb)\n",
    "#\n",
    "#results3.toExcel(workDir / 'valtests-optanalyses-results.ref.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optResFileName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Compare command-line to notebook opt-analysis results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# a. Generate notebook results (through 34. right above)\n",
    "# => optResults\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# b. Generate command-line results (through an external console with relevant python env activated)\n",
    "# $ cd .. && python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-optanlr -n --optanalyses -u\n",
    "# => file(optResFileName)\n",
    "print(optResFileName)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# WARNING: Need to run 5. Compare opt-analyses results to reference / a and b below first !\n",
    "# WARNING : Does not work :( (comparison failure on 2 analyses)\n",
    "\n",
    "# c. Load command line results\n",
    "clOptResults = optResults.copy(withData=False)\n",
    "clOptResults.fromExcel(optResFileName)\n",
    "\n",
    "# d. Check that 2 was really run ...\n",
    "assert (clOptResults._dfData[ads.MCDSAnalysisResultsSet.CLRunStartTime].max() \\\n",
    "        - optResults._dfData[ads.MCDSAnalysisResultsSet.CLRunStartTime].max()).total_seconds() > 1, \\\n",
    "       'Please run above given command line first: you are actually comparing optResults to itself !'\n",
    "\n",
    "# e. Compare (using chapter 5. below, stating that \"reference\" is command-line results, and \"actual\" is optResults)\n",
    "#    i. Select \"reference\" unoptimised analysis results\n",
    "rsUnoptRef = clOptResults.copy()\n",
    "optTruncFlagCol = ads.MCDSTruncationOptanalyser.OptimTruncFlagCol\n",
    "rsUnoptRef.dropRows(rsUnoptRef.dfData[('header (tail)', optTruncFlagCol, 'Value')] == 1)\n",
    "unoptAnlysAbbrevs = rsUnoptRef.dfData[('header (tail)', anlysAbbrevCol, 'Value')].tolist()\n",
    "\n",
    "excludeUnoptCols = [col for col in rsUnoptRes.columns.to_list() if col[2] != 'Order']  # Some pre/post Excel IO issues ...\n",
    "\n",
    "len(unoptAnlysAbbrevs)\n",
    "\n",
    "#    ii. Select \"reference\" with-optimisation analysis results (i.e. with truncation params computed through optimisation)\n",
    "rsOptRef = clOptResults.copy()\n",
    "rsOptRef.dropRows(rsOptRef.dfData[('header (tail)', optTruncFlagCol, 'Value')] != 1)\n",
    "\n",
    "#    iii. Compare using chapter 5. below, taking care of skipping 5.a. and 5.d. (just replaced by i. and ii. above :-)\n",
    "print('\\nNow, run chapter 5. below, skipping 5.a. and 5.d., and see what\\'s happening ...')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Compare opt-analyses results to reference\n",
    "\n",
    "(reference analysis results generated with same kind of \"long\" code like in [valarchives.ipynb / I. Run analyses with real life field data (1/2 : long code, long run)](./valarchives.ipynb#I.-Run-analyses-with-real-life-field-data-(1%2F2-%3A-long-code%2C-long-run)), but on another data set)\n",
    "\n",
    "Note: As for now, filter and sort post-computed columns are not checked here ; only DS analyses are."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### a. Load reference unoptimised analyses results from file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load unoptimised reference\n",
    "# 1. Clone optResults _without_ data.\n",
    "rsUnoptRef = optResults.copy(withData=False)\n",
    "\n",
    "# 2. Load it with reference data (prevent re-postComputation as this ref. file is old, with now missing computed cols)\n",
    "rsUnoptRef.fromOpenDoc('refout/ACDC2019-Naturalist-extrait-Resultats.ods', postComputed=True)\n",
    "\n",
    "unoptAnlysAbbrevs = list(rsUnoptRef.dfData[('header (tail)', anlysAbbrevCol, 'Value')])\n",
    "\n",
    "excludeUnoptCols = []\n",
    "\n",
    "len(unoptAnlysAbbrevs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### b. Separate actual optanalysis results in 2 sets : optimised, and unoptimised"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unoptimised results.\n",
    "rsUnoptRes = optResults.copy()\n",
    "#rsUnoptRes = optResults2.copy() # For recovered run\n",
    "\n",
    "rsUnoptRes.dropRows(~rsUnoptRes.dfData[('header (tail)', anlysAbbrevCol, 'Value')].isin(unoptAnlysAbbrevs))\n",
    "\n",
    "#rsUnoptRes.dfTransData('fr').to_excel('tmp/res.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Optimised results.\n",
    "rsOptRes = optResults.copy()\n",
    "\n",
    "rsOptRes.dropRows(rsOptRes.dfData[('header (tail)', anlysAbbrevCol, 'Value')].isin(unoptAnlysAbbrevs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict(unoptRef=len(rsUnoptRef), unoptRes=len(rsUnoptRes), optRes=len(rsOptRes), allRes=len(optResults))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indexCols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "subsetCols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### c. Compare \"unoptimised\" analyses results to reference ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare (ignore sample and analysis indexes, no use here).\n",
    "indexCols = [col for col in rsUnoptRes.miCustomCols.to_list() if '(sample)' in col[0]] \\\n",
    "            + [('parameters', 'estimator key function', 'Value'),\n",
    "               ('parameters', 'estimator adjustment series', 'Value'),\n",
    "               ('parameters', 'left truncation distance', 'Value'),\n",
    "               ('parameters', 'right truncation distance', 'Value'),\n",
    "               ('parameters', 'model fitting distance cut points', 'Value'),\n",
    "               ('header (tail)', 'AbrevAnlys', 'Value')]\n",
    "subsetCols = [col for col in rsUnoptRes.columns.to_list() \\\n",
    "              if col in rsUnoptRef.columns #and col not in excludeFromComp\n",
    "                 and col not in (indexCols + excludeUnoptCols\n",
    "                                 + [col for col in rsUnoptRes.miCustomCols.to_list() if '(sample)' not in col[0]]\n",
    "                                 + [('parameters', 'estimator selection criterion', 'Value'),\n",
    "                                    ('parameters', 'CV interval', 'Value'),\n",
    "                                    ('run output', 'start time', 'Value'),\n",
    "                                    ('run output', 'elapsed time', 'Value'),\n",
    "                                    ('run output', 'run folder', 'Value'),\n",
    "                                    ('detection probability', 'Delta AIC', 'Value'),\n",
    "                                    ('detection probability', 'key function type', 'Value'),\n",
    "                                    ('detection probability', 'adjustment series type', 'Value')])]\n",
    "\n",
    "dfDiff = rsUnoptRef.compare(rsUnoptRes, indexCols=indexCols, subsetCols=subsetCols, dropCloser=14, dropNans=True)\n",
    "\n",
    "assert dfDiff.empty, 'No, no, no : not the same ...'\n",
    "\n",
    "print('Yessssss !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiff.to_excel('tmp/_.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# To be perfectly honest, may be some 10^-15 differences (when some results loaded from Excel, some other not) ... or not.\n",
    "rsUnoptRef.compare(rsUnoptRes, indexCols=indexCols, subsetCols=subsetCols, dropCloser=15, dropNans=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### d. Load reference \"with optimisation\" analyses results from file\n",
    "\n",
    "**Warning** : reference =\n",
    "* analyses results computed through [valarchives.ipynb / VII. Truncation optimisation (short code and fast run) / 6A. Or : Really run analyses](./valarchives.ipynb#VII.-Truncation-optimisation-(short-code-and-fast-run))\n",
    "* with [IV. 0. Optanalyser parameters]((#0.-Optanalyser-parameters) exactly the same\n",
    "* using variant 'main' for [3. Samples and analyses to optimise]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load optimised reference (analysis results with truncation params computed through optimisation)\n",
    "# 1. Clone results _without_ data.\n",
    "rsOptRef = optResults.copy(withData=False)\n",
    "\n",
    "# 2. Load it with reference data (need to enforce presence of OptimTrunc column, as the source file may have been\n",
    "#    built with an MCDSAnalisysResultsSet, not an MCDSTruncOptanalisysResultsSet, the actual class of results ;\n",
    "#    otherwise, postCompute will fail ... => dDefMissingCols arg)\n",
    "rsOptRef.fromExcel(f'tmp/mcds-anaftopt/valarc-mcds-analyser-afteropt-main-results.xlsx', \n",
    "                   dDefMissingCols={('header (tail)', 'OptimTrunc', 'Value'): np.nan})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### e. Compare \"with optimisation\" analysis results to \"reference\" ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort rows for each analysis optim param specs ... by left truncation distance first\n",
    "miSortCols = [('header (tail)', 'AbrevAnlys', 'Value'),\n",
    "              ('parameters', 'left truncation distance', 'Value'),\n",
    "              ('parameters', 'right truncation distance', 'Value'),\n",
    "              ('parameters', 'model fitting distance cut points', 'Value')]\n",
    "\n",
    "rsOptRes.sortRows(by=miSortCols)\n",
    "rsOptRef.sortRows(by=miSortCols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Simple columns index (fr) + setup sorted analyses index\n",
    "miAnlysNumCol = 'NumAnlys'\n",
    "dfOptRes = rsOptRes.dfTransData('fr')\n",
    "dfOptRes[miAnlysNumCol] = [i for i in range(len(dfOptRes))]\n",
    "dfOptRef = rsOptRef.dfTransData('fr')\n",
    "dfOptRef[miAnlysNumCol] = [i for i in range(len(dfOptRef))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check that order is \"compatible\" between reference and actual results\n",
    "miAnlysAbrevCol = 'AbrevAnlys'\n",
    "\n",
    "assert dfOptRes[miAnlysAbrevCol].to_list() == dfOptRef[miAnlysAbrevCol].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save to disk for visual checks / comparison\n",
    "#dfOptRes.to_excel('tmp/opt-res-fr.xlsx')\n",
    "#dfOptRef.to_excel('tmp/opt-ref-fr.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare a small and simple subset of analyses results columns ...\n",
    "indexCols = [miAnlysNumCol, miAnlysAbrevCol]\n",
    "subsetCols = ['AIC', 'PDetec', 'EDR/ESW', 'Densité']\n",
    "\n",
    "dfDiff = ads.DataSet.compareDataFrames(dfOptRes, dfOptRef, indexCols=indexCols, subsetCols=subsetCols, dropNans=True)\n",
    "\n",
    "dfDiff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some diff. stats\n",
    "dfDiffStats = pd.DataFrame(data=[dfDiff.min(), dfDiff.max(), dfDiff.replace(np.inf, 16).mean()],\n",
    "                           index=['min', 'max', 'mean'])\n",
    "dfDiffStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Not too bad if less that 10% mean difference (100 / 10**1 = 10%) !\n",
    "assert dfDiffStats.loc['mean'].min() >= 1.0\n",
    "\n",
    "# And actually at most P % difference : let's compute P ...\n",
    "p = 100 / 10**dfDiffStats.loc['mean'].min()\n",
    "\n",
    "assert p < 10, f'Oh oh ... {p=} >= 10 %'\n",
    "\n",
    "print(f'Good: {p=:.2f} < 10 %')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save to disk after \"merging\" ref and actual results, again for visual checks\n",
    "dfOptRef.insert(0, 'x', 'ref')\n",
    "dfOptRes.insert(0, 'x', 'res')\n",
    "\n",
    "dfOptComp = dfOptRef.append(dfOptRes, sort=False)\n",
    "\n",
    "dfOptComp.sort_values(by=['NumAnlys', 'x'], inplace=True)\n",
    "\n",
    "dfOptComp.to_excel('tmp/opt-comp.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### f. Some history of computations difference stats with various 'maxIters' values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep stats for history ... copy/paste results below ...\n",
    "print('**maxIters={} (N=?): max delta = {:.2f} %**'.format(defCoreMaxIters, 100 / 10**dfDiffStats.loc['mean'].min()))\n",
    "print()\n",
    "print(dfDiffStats.to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%html\n",
    "<style>\n",
    "table {float:left}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**maxIter=100 (core i7 10850H, N=1, last on 2021-11-28) : max delta = 7.15 %**\n",
    "\n",
    "|      |     AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|--------:|---------:|----------:|----------:|\n",
    "| min  | 1.2     |  0.3     |   0.5     |   0.3     |\n",
    "| max  | 4.9     |  3.4     |   3.7     |   3.4     |\n",
    "| mean | 1.70455 |  1.14545 |   1.51364 |   1.22727 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**maxIter=100 (core i5 8365U, N=8, last on 2021-11-20) : max delta = 3.6 %, 3.7 %, 6.44 %, 5.01 %, 5.12 %, 3.55 %, 5.28 %**\n",
    "\n",
    "|      |     AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|--------:|---------:|----------:|----------:|\n",
    "| min  | 1.1     |  0.2     |   0.4     |   0.2     |\n",
    "| max  | 6.3     |  4.6     |   4.9     |   4.6     |\n",
    "| mean | 2.06818 |  1.29091 |   1.85455 |   1.54545 |\n",
    "    \n",
    "|      |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1       |  0.2     |       0.6 |   0.4     |\n",
    "| max  | inf       |  4.9     |     inf   | inf       |\n",
    "| mean |   2.82273 |  1.44545 |       2.7 |   2.40455 |\n",
    "\n",
    "|      |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.3     |   0.5     |   0.3     |\n",
    "| max  | inf       |  4.5     |   5.4     |   4.5     |\n",
    "| mean |   2.63182 |  1.49091 |   2.03182 |   1.43182 |\n",
    "\n",
    "|      |     AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|--------:|---------:|----------:|----------:|\n",
    "| min  | 1       |  0.2     |   0.5     |   0.3     |\n",
    "| max  | 5.7     |  4.3     |   4.6     |   4.3     |\n",
    "| mean | 1.92273 |  1.19091 |   1.80455 |   1.50455 |\n",
    "\n",
    "|      |     AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|--------:|---------:|----------:|----------:|\n",
    "| min  | 1       |      0.2 |   0.8     |   0.6     |\n",
    "| max  | 4.6     |      3.4 |   3.7     |   3.4     |\n",
    "| mean | 1.80909 |      1.3 |   1.73636 |   1.53182 |\n",
    "\n",
    "|      |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.2     |     0.2  |   0.9     |   0.6     |\n",
    "| max  | inf       |     4.1  | inf       | inf       |\n",
    "| mean |   2.64091 |     1.45 |   2.55455 |   2.28182 |\n",
    "\n",
    "|      |     AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|--------:|---------:|----------:|----------:|\n",
    "| min  | 1.1     |  0.4     |       0.8 |   0.6     |\n",
    "| max  | 4.1     |  2.7     |       3   |   2.4     |\n",
    "| mean | 1.76364 |  1.27727 |       1.7 |   1.38182 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**maxIter=120 (N=3) : max delta = 6.1 %, 1.6 %, 1.7 %**\n",
    "\n",
    "|Exec1 |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.2     |   0.9     |   0.6     |\n",
    "| max  | inf       |  5.1     | inf       |   6.5     |\n",
    "| mean |   2.37273 |  1.21364 |   2.15909 |   1.47273 |\n",
    "\n",
    "|Exec2 |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.2     |   0.8     |   0.6     |\n",
    "| max  | inf       |  5       | inf       | inf       |\n",
    "| mean |   3.15455 |  1.79545 |   2.82273 |   2.47273 |\n",
    "\n",
    "|Exec3 |     AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|--------:|---------:|----------:|----------:|\n",
    "| min  | 1.1     |  0.3     |   0.6     |   0.4     |\n",
    "| max  | 6.6     |  4.9     |   5.2     |   4.9     |\n",
    "| mean | 2.57727 |  1.76818 |   2.21364 |   1.92273 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**maxIter=250 (N=3) : max delta = 0.83 %, 3.4 %, 0.53 %**\n",
    "\n",
    "|Exec1 |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.4     |   0.8     |   0.6     |\n",
    "| max  | inf       |  5.9     | inf       | inf       |\n",
    "| mean |   4.39545 |  2.08182 |   2.95455 |   2.68636 |\n",
    "\n",
    "|Exec2 |     AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|--------:|---------:|----------:|----------:|\n",
    "| min  | 1       |  0.4     |   0.5     |      0.3  |\n",
    "| max  | 6.7     |  5.4     |   5.7     |      5.5  |\n",
    "| mean | 2.18636 |  1.46818 |   1.82273 |      1.55 |\n",
    "\n",
    "|Exec3 |       AIC |    PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|----------:|----------:|----------:|\n",
    "| min  |   1       |   0.3     |   0.9     |   0.6     |\n",
    "| max  | inf       | inf       | inf       | inf       |\n",
    "| mean |   3.76818 |   2.27727 |   3.50909 |   3.24091 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**maxIters=400 (N=4): max delta = 2.6 %, 2.9%, 1.9%, 1.8%**\n",
    "\n",
    "|Exec1 |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.1     |   0.5     |   0.3     |\n",
    "| max  | inf       |  6.7     | inf       |   6.4     |\n",
    "| mean |   3.03182 |  1.57727 |   2.65455 |   1.89091 |\n",
    "\n",
    "|Exec2 |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.3     |   0.9     |   0.6     |\n",
    "| max  | inf       |  4.3     |   5       |   4.7     |\n",
    "| mean |   2.79091 |  1.54091 |   2.08182 |   1.80909 |\n",
    "\n",
    "|Exec3 |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.2     |   0.5     |   0.3     |\n",
    "| max  | inf       |  6.7     | inf       |  15.9     |\n",
    "| mean |   3.40455 |  1.71818 |   2.46364 |   2.24545 |\n",
    "\n",
    "|Exec4 |       AIC |   PDetec |   EDR/ESW |   Densité |\n",
    "|:-----|----------:|---------:|----------:|----------:|\n",
    "| min  |   1.1     |  0.2     |      0.8  |   0.6     |\n",
    "| max  |   6.7     |  4.9     |      5.2  |   4.9     |\n",
    "| mean |   2.66818 |  1.74091 |      2.45 |   2.18636 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Generate HTML and Excel opt-analyses reports\n",
    "\n",
    "Note: The exact same reports can be also produced through command line:\n",
    "```\n",
    "$ cd .. \n",
    "$ python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-optanlr -n --optreports excel,html:mqua-r92 -u\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "R = optResults"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Super-synthesis sub-report : Selected analysis results columns for the 3 textual columns of the table\n",
    "sampleCols = [\n",
    "    ('header (head)', 'NumEchant', 'Value'),\n",
    "    ('header (sample)', 'Espèce', 'Value'),\n",
    "    ('header (sample)', 'Passage', 'Value'),\n",
    "    ('header (sample)', 'Adulte', 'Value'),\n",
    "    ('header (sample)', 'Durée', 'Value'),\n",
    "    \n",
    "    R.CLNTotObs, R.CLMinObsDist, R.CLMaxObsDist]\n",
    "\n",
    "paramCols = [\n",
    "    ('header (head)', 'NumAnlys', 'Value'),\n",
    "    R.CLParEstKeyFn, R.CLParEstAdjSer,\n",
    "    R.CLParTruncLeft, R.CLParTruncRight, R.CLParModFitDistCuts]\n",
    "    \n",
    "resultCols = [\n",
    "    R.CLRunStatus,\n",
    "    R.CLNObs, R.CLEffort, R.CLSightRate, R.CLNAdjPars,\n",
    "    R.CLAic, R.CLChi2, R.CLKS, R.CLDCv,\n",
    "    R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3,\n",
    "    \n",
    "    R.CLEswEdr, R.CLPDetec, \n",
    "    R.CLDensity, R.CLDensityMin, R.CLDensityMax,\n",
    "    R.CLNumber, R.CLNumberMin, R.CLNumberMax]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Synthesis sub-report : Selected analysis results columns for the table\n",
    "synthCols = [\n",
    "    ('header (head)', 'NumEchant', 'Value'),\n",
    "    ('header (sample)', 'Espèce', 'Value'),\n",
    "    ('header (sample)', 'Passage', 'Value'),\n",
    "    ('header (sample)', 'Adulte', 'Value'),\n",
    "    ('header (sample)', 'Durée', 'Value'),\n",
    "\n",
    "    ('header (head)', 'NumAnlys', 'Value'),\n",
    "    R.CLParEstKeyFn, R.CLParEstAdjSer,\n",
    "    #R.CLParEstSelCrit, R.CLParEstCVInt,\n",
    "    R.CLParTruncLeft, R.CLParTruncRight, R.CLParModFitDistCuts,\n",
    " \n",
    "    R.CLNTotObs, R.CLNObs, R.CLNTotPars, R.CLEffort,\n",
    "    R.CLDeltaAic, R.CLChi2, R.CLKS, R.CLCvMUw, R.CLCvMCw, R.CLDCv, \n",
    "    R.CLSightRate,\n",
    "    R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3,\n",
    "    R.CLCmbQuaChi2, R.CLCmbQuaKS, R.CLCmbQuaDCv,\n",
    "\n",
    "    R.CLPDetec, R.CLPDetecMin, R.CLPDetecMax,\n",
    "    R.CLDensity, R.CLDensityMin, R.CLDensityMax,\n",
    "    R.CLNumber, R.CLNumberMin, R.CLNumberMax,\n",
    "\n",
    "    R.CLGrpOrdSmTrAic,\n",
    "    R.CLGrpOrdClTrChi2KSDCv, #R.CLGrpOrdClTrChi2,\n",
    "    R.CLGrpOrdClTrDCv,\n",
    "    R.CLGrpOrdClTrQuaBal1, R.CLGrpOrdClTrQuaBal2, R.CLGrpOrdClTrQuaBal3, R.CLGrpOrdClTrQuaChi2,\n",
    "    R.CLGrpOrdClTrQuaKS, R.CLGrpOrdClTrQuaDCv,\n",
    "    R.CLGblOrdChi2KSDCv, R.CLGblOrdQuaBal1, R.CLGblOrdQuaBal2, R.CLGblOrdQuaBal3,\n",
    "    R.CLGblOrdQuaChi2, R.CLGblOrdQuaKS, R.CLGblOrdQuaDCv,\n",
    "    R.CLGblOrdDAicChi2KSDCv]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter and sort sub-reports : schemes to apply\n",
    "whichFinalQua = R.CLCmbQuaBal3\n",
    "ascFinalQua = False\n",
    "\n",
    "whichBestQua = [R.CLGrpOrdClTrChi2KSDCv, R.CLGrpOrdClTrDCv, whichFinalQua,\n",
    "               R.CLGrpOrdClTrQuaChi2, R.CLGrpOrdClTrQuaKS, R.CLGrpOrdClTrQuaDCv]\n",
    "\n",
    "dupSubset = [R.CLNObs, R.CLEffort, R.CLDeltaAic, R.CLChi2, R.CLKS, R.CLCvMUw, R.CLCvMCw, R.CLDCv, \n",
    "             R.CLPDetec, R.CLPDetecMin, R.CLPDetecMax, R.CLDensity, R.CLDensityMin, R.CLDensityMax]\n",
    "dDupRounds = {R.CLDeltaAic: 1, R.CLChi2: 2, R.CLKS: 2, R.CLCvMUw: 2, R.CLCvMCw: 2, R.CLDCv: 2, \n",
    "              R.CLPDetec: 3, R.CLPDetecMin: 3, R.CLPDetecMax: 3, R.CLDensity: 2, R.CLDensityMin: 2, R.CLDensityMax: 2}\n",
    "\n",
    "filSorSchemes = [dict(method=ads.MCDSTruncOptanalysisResultsSet.filterSortOnExecCode,\n",
    "                      deduplicate=dict(dupSubset=dupSubset, dDupRounds=dDupRounds),\n",
    "                      filterSort=dict(whichFinalQua=whichFinalQua, ascFinalQua=ascFinalQua),\n",
    "                      preselCols=[R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3],\n",
    "                      preselAscs=False, preselThrhs=0.2, preselNum=4),\n",
    "                 dict(method=ads.MCDSTruncOptanalysisResultsSet.filterSortOnExCAicMulQua,\n",
    "                      deduplicate=dict(dupSubset=dupSubset, dDupRounds=dDupRounds),\n",
    "                      filterSort=dict(sightRate=90, nBestAIC=4, nBestQua=2, whichBestQua=whichBestQua,\n",
    "                                      nFinalRes=15, whichFinalQua=whichFinalQua, ascFinalQua=ascFinalQua),\n",
    "                      preselCols=[R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3],\n",
    "                      preselAscs=False, preselThrhs=0.2, preselNum=3),\n",
    "                 dict(method=ads.MCDSTruncOptanalysisResultsSet.filterSortOnExCAicMulQua,\n",
    "                      deduplicate=dict(dupSubset=dupSubset, dDupRounds=dDupRounds),\n",
    "                      filterSort=dict(sightRate=92, nBestAIC=3, nBestQua=2, whichBestQua=whichBestQua,\n",
    "                                      nFinalRes=12, whichFinalQua=whichFinalQua, ascFinalQua=ascFinalQua),\n",
    "                      preselCols=[R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3],\n",
    "                      preselAscs=False, preselThrhs=0.2, preselNum=3),\n",
    "                 dict(method=ads.MCDSTruncOptanalysisResultsSet.filterSortOnExCAicMulQua,\n",
    "                      deduplicate=dict(dupSubset=dupSubset, dDupRounds=dDupRounds),\n",
    "                      filterSort=dict(sightRate=94, nBestAIC=2, nBestQua=1, whichBestQua=whichBestQua,\n",
    "                                      nFinalRes=10, whichFinalQua=whichFinalQua, ascFinalQua=ascFinalQua),\n",
    "                      preselCols=[R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3],\n",
    "                      preselAscs=False, preselThrhs=0.2, preselNum=3),\n",
    "                 dict(method=ads.MCDSTruncOptanalysisResultsSet.filterSortOnExCAicMulQua,\n",
    "                      deduplicate=dict(dupSubset=dupSubset, dDupRounds=dDupRounds),\n",
    "                      filterSort=dict(sightRate=96, nBestAIC=2, nBestQua=1, whichBestQua=whichBestQua,\n",
    "                                      nFinalRes=8, whichFinalQua=whichFinalQua, ascFinalQua=ascFinalQua),\n",
    "                      preselCols=[R.CLCmbQuaBal1, R.CLCmbQuaBal2, R.CLCmbQuaBal3],\n",
    "                      preselAscs=False, preselThrhs=0.2, preselNum=3)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Super-synthesis, synthesis and detail tables, HTML or Excel : sort parameters.\n",
    "sortCols = [('header (head)', 'NumEchant', 'Value'), whichFinalQua]\n",
    "sortAscend = [True, False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "afsReport = ads.MCDSResultsFilterSortReport(resultsSet=optResults,\n",
    "                                            title=\"PyAuDiSam Validation : Analyses with optimised truncations\",\n",
    "                                            subTitle=\"Auto-selection of best results\",\n",
    "                                            description='Automated filtering et sorting : method \"{fsId}\" ; after '\n",
    "                                                        'easy and parallel run through MCDSTruncationOptAnalyser',\n",
    "                                            anlysSubTitle='Analyses details',\n",
    "                                            lang='en', keywords='pyaudisam, validation, optimisation',\n",
    "                                            superSynthPlotsHeight=280, plotImgSize=(512, 280),\n",
    "                                            sampleCols=sampleCols, paramCols=paramCols,\n",
    "                                            resultCols=resultCols, synthCols=synthCols,\n",
    "                                            sortCols=sortCols, sortAscend=sortAscend,\n",
    "                                            filSorSchemes=filSorSchemes, \n",
    "                                            tgtFolder=workDir,\n",
    "                                            tgtPrefix='valtests-optanalyses-report')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# XLSX report\n",
    "xlsxAFSRep = afsReport.toExcel()\n",
    "\n",
    "xlsxBkpAFSRep = backup(xlsxAFSRep)\n",
    "\n",
    "HTML(f'Auto-filtered Excel report: <a href=\"{xlsxAFSRep}\" target=\"blank\">{xlsxAFSRep}</a>')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.startfile(xlsxAFSRep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select scheme to apply for HTML report.\n",
    "scheme = next(sch for sch in filSorSchemes \n",
    "              if sch['method'] is ads.MCDSTruncOptanalysisResultsSet.filterSortOnExCAicMulQua\n",
    "                 and sch['filterSort']['sightRate'] == 92)\n",
    "print(optResults.filSorIdMgr.dFilSorSchemes.keys(), '\\n=>', optResults.filSorSchemeId(scheme))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# HTML report\n",
    "htmlAFSRep = afsReport.toHtml(filSorScheme=scheme, rebuild=False)\n",
    "\n",
    "backup(htmlAFSRep)\n",
    "\n",
    "afsId = optResults.filSorSchemeId(scheme)\n",
    "print(f'Auto-filtered HTML report({afsId} scheme): ' + pl.Path(htmlAFSRep).resolve().as_uri())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Compare command-line and notebook Excel opt-analyses reports\n",
    "\n",
    "(cd .. && python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-optanlr -n --optreports excel -u)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Generate notebook report (through 6. right above)\n",
    "# => xlsxBkpAFSRep\n",
    "print(xlsxBkpAFSRep)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. Generate command-line report (through an external console with relevant python env activated)\n",
    "# $ cd .. && python -m pyaudisam -p tests/valtests-ds-params.py -w tests/tmp/mcds-optanlr -n --optreports excel -u\n",
    "# => xlsxAFSRep\n",
    "print(xlsxAFSRep)\n",
    "raise Exception('Yes, do it really !')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. Load the 2 reports\n",
    "ddfNbOptReport = pd.read_excel(xlsxBkpAFSRep, sheet_name=None, index_col=0)  # Notebook (backup) one\n",
    "ddfClOptReport = pd.read_excel(xlsxAFSRep, sheet_name=None, index_col=0)  # Command-line one\n",
    "\n",
    "# 4. Check that 2 was really run ...\n",
    "assert (ddfClOptReport['Details']['StartTime'].max() - ddfNbOptReport['Details']['StartTime'].max()).total_seconds() > 1, \\\n",
    "       'Please run above given command line first: you are actually comparing notebook report to itself !'\n",
    "\n",
    "# 5. Compare Synthesis and Details sheets\n",
    "# assert ddfNbOptReport['Synthesis'].drop(columns=['RunFolder']).set_index('NumAnlys') \\\n",
    "#         .compare(ddfClOptReport['Synthesis'].drop(columns=['RunFolder']).set_index('NumAnlys')) \\\n",
    "#         .empty\n",
    "# assert ddfNbOptReport['Details'].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys') \\\n",
    "#         .compare(ddfClOptReport['Details'].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys')) \\\n",
    "#         .empty\n",
    "\n",
    "# 6. Compare auto-filtered sheets\n",
    "#afsMeths = [sn for sn in ddfNbOptReport if sn.startswith('')]\n",
    "#assert set(afsMeths) == set([sn for sn in ddfClOptReport if sn.startswith('')])\n",
    "#for afsMeth in afsMeths:\n",
    "#    assert ddfNbOptReport[afsMeths].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys') \\\n",
    "#            .compare(ddfClOptReport[afsMeths].drop(columns=['StartTime', 'ElapsedTime', 'RunFolder']).set_index('NumAnlys')) \\\n",
    "#            .empty\n",
    "#    \n",
    "#logger.info('Success !')\n",
    "\n",
    "logger.error('This can\\'t work, optimisations rarely give twice the same results ...')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Simple checks on opt-analyses reports\n",
    "\n",
    "(accurate non regression tests are not simple to achieve ... here we keep it simple, but not very accurate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#xlsxAFSRep = 'tmp/mcds-optanlr/valtests-optanalyses-report.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load last generated Excel report\n",
    "ddfAfsXlsxReport = pd.read_excel(xlsxAFSRep, sheet_name=None)\n",
    "\n",
    "snPrfx = 'AFSM-'\n",
    "{sn[len(snPrfx):]: len(ddfAfsXlsxReport[sn]) for sn in ddfAfsXlsxReport if sn.startswith(snPrfx)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddfAfsXlsxReport.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check expected approximate number of results for each filter and sort method.\n",
    "tolerance = 3  # +/-\n",
    "KExpectedNbOfResults = {'ExCode': 70, 'ExAicMQua-r900m6q3d15': 51, 'ExAicMQua-r920m6q3d12': 48,\n",
    "                        'ExAicMQua-r940m6q3d10': 43, 'ExAicMQua-r960m6q3d8': 34}  # OK on 2021-11-05 & 06\n",
    "\n",
    "assert all(sn in ddfAfsXlsxReport for sn in KExpectedNbOfResults if sn.startswith(snPrfx)), 'Missing subreports'\n",
    "assert all(KExpectedNbOfResults[sn[len(snPrfx):]] - tolerance <= len(ddfAfsXlsxReport[sn])\n",
    "                                                              <= KExpectedNbOfResults[sn[len(snPrfx):]] + tolerance\n",
    "           for sn in ddfAfsXlsxReport if sn.startswith(snPrfx)), 'Too much difference for some sub-reports'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check some results\n",
    "dfAfsXlsxRes = ddfAfsXlsxReport['Details']\n",
    "\n",
    "dfAfsXlsxRes['UniqueId'] = range(len(dfAfsXlsxRes))  # Unique row Id, usefull for some comparisons\n",
    "\n",
    "len(dfAfsXlsxRes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAfsXlsxRes[['NumEchant', 'NumAnlys', 'Group Left Trunc', 'Group Right Trunc']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load reference Excel report\n",
    "xlsxAFSRefRep = 'tmp/valtests-optanalysis-report.220102.xlsx'\n",
    "ddfAfsXlsxRefReport = pd.read_excel(xlsxAFSRefRep, sheet_name=None)\n",
    "\n",
    "snPrfx = 'AFSM-'\n",
    "{sn[len(snPrfx):]: len(ddfAfsXlsxRefReport[sn]) for sn in ddfAfsXlsxRefReport if sn.startswith(snPrfx)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check expected approximate number of results for each filter and sort method.\n",
    "tolerance = 3  # +/-\n",
    "KExpectedNbOfResults = {'ExCode': 70, 'ExAicMQua-r900m6q3d15': 51, 'ExAicMQua-r920m6q3d12': 48,\n",
    "                        'ExAicMQua-r940m6q3d10': 43, 'ExAicMQua-r960m6q3d8': 34}  # OK on 2021-11-05 & 06\n",
    "\n",
    "assert all(KExpectedNbOfResults[sn[len(snPrfx):]] - tolerance <= len(ddfAfsXlsxRefReport[sn])\n",
    "                                                              <= KExpectedNbOfResults[sn[len(snPrfx):]] + tolerance\n",
    "           for sn in ddfAfsXlsxRefReport if sn.startswith(snPrfx))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check some results\n",
    "dfAfsXlsxRefRes = ddfAfsXlsxRefReport['Details']\n",
    "\n",
    "dfAfsXlsxRefRes['UniqueId'] = range(len(dfAfsXlsxRefRes))  # Unique row Id, usefull for some comparisons\n",
    "\n",
    "len(dfAfsXlsxRefRes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Truncation groups\n",
    "dfComp = dfAfsXlsxRefRes[['NumEchant', 'NumAnlys', 'Group Left Trunc', 'Group Right Trunc']] \\\n",
    "             .compare(dfAfsXlsxRes[['NumEchant', 'NumAnlys', 'Group Left Trunc', 'Group Right Trunc']])\n",
    "\n",
    "assert dfComp.empty  # Don't even think of it ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Quality indicators\n",
    "quaCols = [col for col in dfAfsXlsxRefRes.columns if col.startswith('Qual ')]\n",
    "\n",
    "dfRelDiff = \\\n",
    "    ads.DataSet.compareDataFrames(dfAfsXlsxRefRes, dfAfsXlsxRes, indexCols=['UniqueId'], subsetCols=quaCols, dropCloser=14)\n",
    "\n",
    "assert dfRelDiff.empty  # Don't even think of it ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sandbox"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Opt-analysis results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct = pd.read_excel('C:/git/pyaudisam/tests/tmp/valtests-optanalyses-results-api.xlsx', header=[0, 1, 2], skiprows=[3], index_col=0)\n",
    "dfAct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef = pd.read_excel('C:/git/pyaudisam/tests/refout/ACDC2019-Naturalist-extrait-OptResultats.ods', header=[0, 1, 2], skiprows=[3], index_col=0)\n",
    "dfRef"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optTruncFlagCol = ads.MCDSTruncationOptanalyser.OptimTruncFlagCol\n",
    "dfRef = dfRef[dfRef[('header (tail)', optTruncFlagCol, 'Value')] == 0]\n",
    "dfAct = dfAct[dfAct[('header (tail)', optTruncFlagCol, 'Value')] == 0]\n",
    "len(dfRef), len(dfAct)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dfRef.reset_index(inplace=True, drop=True)\n",
    "#dfAct.reset_index(inplace=True, drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct.to_excel('tmp/act.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct[('header (tail)', 'AbrevAnlys', 'Value')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef.to_excel('tmp/ref.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indexCols = [('header (head)', 'NumAnlys', 'Value'),\n",
    "             ('header (head)', 'NumEchant', 'Value'),\n",
    "             ('header (sample)', 'Espèce', 'Value'),\n",
    "             ('header (sample)', 'Passage', 'Value'),\n",
    "             ('header (sample)', 'Adulte', 'Value'),\n",
    "             ('header (sample)', 'Durée', 'Value'),\n",
    "             ('header (tail)', 'FonctionClé', 'Value'),\n",
    "             ('header (tail)', 'SérieAjust', 'Value'),\n",
    "             ('header (tail)', 'TrGche', 'Value'),\n",
    "             ('header (tail)', 'TrDrte', 'Value'),\n",
    "             ('header (tail)', 'NbTrchMod', 'Value'),\n",
    "             #('header (tail)', 'AbrevAnlys', 'Value'),\n",
    "             ('parameters', 'estimator key function', 'Value'),\n",
    "             ('parameters', 'estimator adjustment series', 'Value'),\n",
    "             ('parameters', 'left truncation distance', 'Value'),\n",
    "             ('parameters', 'right truncation distance', 'Value'),\n",
    "             ('parameters', 'model fitting distance cut points', 'Value')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "igndUnoptCols = [col for col in dfAct.columns.to_list() if col[2] == 'Order']  # TODO: Check why really.\n",
    "subsetCols = [col for col in dfAct.columns.to_list()\n",
    "              if col in dfRef.columns\n",
    "              and col not in (indexCols + igndUnoptCols\n",
    "                              + [('parameters', 'estimator selection criterion', 'Value'),\n",
    "                                 ('run output', 'start time', 'Value'),\n",
    "                                 ('run output', 'elapsed time', 'Value'),\n",
    "                                 ('run output', 'run folder', 'Value'),\n",
    "                                 ('detection probability', 'key function type', 'Value'),\n",
    "                                 ('detection probability', 'adjustment series type', 'Value'),\n",
    "                                 ('detection probability', 'Delta AIC', 'Value')])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiff = ads.DataSet.compareDataFrames(dfAct, dfRef, subsetCols=subsetCols, indexCols=indexCols, noneIsNan=True, dropCloser=14, dropNans=True)\n",
    "dfDiff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiff.to_excel('tmp/diff.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct.columns.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct = pd.read_excel('C:/git/pyaudisam/tests/tmp/valtests-optanalyses-results-api.xlsx', sheet_name='sp-analyser', index_col=0)\n",
    "dfAct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef = pd.read_excel('C:/git/pyaudisam/tests/refout/ACDC2019-Naturalist-extrait-OptResultats.ods', sheet_name='sp-analyser', index_col=0)\n",
    "dfRef"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series(dict(a='1', b=2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(s.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActAnlysSpecs = pd.read_excel('C:/git/pyaudisam/tests/tmp/val-oanlr/valtests-optanalyses-results.xlsx', index_col=0, sheet_name='sp-analyses')\n",
    "dfActAnlysSpecs = dfActAnlysSpecs[dfActAnlysSpecs.OptimTrunc == 1]\n",
    "dfActAnlysSpecs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "set(dfActAnlysSpecs.MultiOpt.unique()) == {np.nan, 'times(2)', 'times(3, b=2)', 'times(4)'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "anlysNOptParamCols = ['Espèce', 'Passage', 'Adulte', 'Durée', 'FonctionClé', 'SérieAjust']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActMTimes = dfActAnlysSpecs[['NumAnlys', 'MultiOpt'] + anlysNOptParamCols].groupby(['NumAnlys'] + anlysNOptParamCols).count().replace(0, 1)\n",
    "#dfActMTimes.reset_index(inplace=True)\n",
    "#dfActMTimes.set_index('NumAnlys', inplace=True)\n",
    "dfActMTimes.rename(columns=dict(MultiOpt='actualNTimes'), inplace=True)\n",
    "dfActMTimes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActAnlysSpecs = dfActAnlysSpecs.set_index(['NumAnlys'] + anlysNOptParamCols).join(dfActMTimes)\n",
    "dfActAnlysSpecs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def nTimes(mOpt):\n",
    "    return 1 if pd.isnull(mOpt) else 2 if 'b=2' in mOpt or '(2)' in mOpt else 4 if '(4)' in mOpt else 0\n",
    "dfActAnlysSpecs['exptdNTimes'] = dfActAnlysSpecs.MultiOpt.apply(nTimes)\n",
    "dfActAnlysSpecs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActAnlysSpecs.actualNTimes.eq(dfActAnlysSpecs.exptdNTimes).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActAnlysSpecs.drop(columns=['actualNTimes', 'exptdNTimes', 'NTimes'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiffStatHistory = pd.DataFrame(data=[dict(source='a', table='b', n=1, m=2, p='r')], index=[pd.Timestamp.now()])\n",
    "dfDiffStatHistory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiffStatHistory = pd.DataFrame()\n",
    "dfDiffStatHistory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiffStats = pd.DataFrame(data=[dict(p='t', n=4, m=7)], index=['mean'])\n",
    "dfDiffStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dNewDiffStats.update(dfDiffStats.loc['mean'])\n",
    "pd.concat([dfDiffStatHistory, pd.DataFrame([dNewDiffStats], index=[pd.Timestamp.now()])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dNewDiffStats = dict(source='x', table='y')\n",
    "dNewDiffStats.update(pd.Series(dict(n=9, p='y')))\n",
    "pd.concat([dfDiffStatHistory, pd.DataFrame([dNewDiffStats], index=[pd.Timestamp.now()])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dNewDiffStats = dict(source='x', table='y')\n",
    "dNewDiffStats.update(pd.Series(dict(n=9, p='y', m=22, z=34)))\n",
    "pd.concat([dfDiffStatHistory, pd.DataFrame([dNewDiffStats], index=[pd.Timestamp.now()])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiffStats = pd.DataFrame(data=[dict(AIC=7, PDetect=1)], index=['mean'])\n",
    "dfDiffStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dNewDiffStats = dict(source='x', table='y')\n",
    "dNewDiffStats.update(dfDiffStats.loc['mean'].to_dict())\n",
    "\n",
    "fpnDiffStatHistory = 'tmp/dont-remove-valtests-optanalyses-opt-diff-stats-history.xlsx'\n",
    "dfDiffStatHistory = pd.read_excel(fpnDiffStatHistory, index_col=0)\n",
    "dfDiffStatHistory = pd.concat([dfDiffStatHistory, \n",
    "                               pd.DataFrame(data=[dNewDiffStats],\n",
    "                                            index=pd.Index([pd.Timestamp.now()], name='timestamp'))])\n",
    "dfDiffStatHistory.to_excel('tmp/dont-remove-valtests-optanalyses-opt-diff-stats-history.test.xlsx', index=True)\n",
    "dfDiffStatHistory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Index([pd.Timestamp.now()], name='timestamp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Opt-analysis tabular workbook reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddfRefRep = pd.read_excel('refout/ACDC2019-Naturalist-extrait-OptRapport.ods', sheet_name=None, index_col=0)\n",
    "ddfRefRep.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ddfActRep = pd.read_excel('tmp/val-oanlr/valtests-optanalyses-report.xlsx', sheet_name=None, index_col=0)\n",
    "ddfActRep.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indexCols = ['Scheme', 'Step', 'Property']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef = ddfRefRep['AFS-Steps'].reset_index(drop=True)\n",
    "dfRef = dfRef[dfRef.Property != 'datetime'].copy()\n",
    "dfRef"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRefPars = dfRef[dfRef.Property != 'results'].set_index(indexCols)\n",
    "dfRefPars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct = ddfActRep['AFS-Steps'].reset_index(drop=True)\n",
    "dfAct = dfAct[dfAct.Property != 'datetime'].copy()\n",
    "dfAct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActPars = dfAct[dfAct.Property != 'results'].set_index(indexCols)\n",
    "dfActPars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfComp = dfRefPars.compare(dfActPars)\n",
    "dfComp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRefRes = dfRef[dfRef.Property == 'results'].set_index(indexCols)\n",
    "dfRefRes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActRes = dfAct[dfAct.Property == 'results'].set_index(indexCols)\n",
    "dfActRes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfCompRes = dfRefRes.join(dfActRes, lsuffix=' Ref', rsuffix=' Act')\n",
    "dfCompRes['Delta'] = dfCompRes['Value Ref'].subtract(dfCompRes['Value Act']).abs()\n",
    "\n",
    "KSDeltaResThresh = pd.Series({\n",
    "    'before': 10,\n",
    "    'run status': 5,\n",
    "    'duplicates on params': 5,\n",
    "    'best AIC': 5,\n",
    "    'best results for >= 1 indicator': 5,\n",
    "    'non-outlier sightings': 10,\n",
    "    'final best results': 5,\n",
    "    'after': 5,\n",
    "}, name='Threshold')\n",
    "dfCompRes = dfCompRes.reset_index().join(KSDeltaResThresh, on='Step').set_index(indexCols)\n",
    "dfCompRes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef = ddfRefRep['Analyses']\n",
    "dfRef"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct = ddfActRep['Analyses']\n",
    "dfAct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef = dfRef[dfRef.OptimTrunc == 1]\n",
    "dfRef"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct = dfAct[dfAct.OptimTrunc == 1]\n",
    "dfAct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "optCols = ['TrGche', 'TrDrte', 'NbTrchMod']\n",
    "unoptCols = [col for col in dfRef if col not in optCols]\n",
    "dfRef = dfRef[unoptCols].drop_duplicates()\n",
    "print(len(dfRef))\n",
    "dfRef.set_index('NumAnlys')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAct = dfAct[unoptCols].drop_duplicates()\n",
    "print(len(dfAct))\n",
    "dfAct.set_index('NumAnlys')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfComp = dfRef.compare(dfAct)\n",
    "dfComp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sRefAnrSpecs = ddfRefRep['Analyser']['Value']\n",
    "sRefAnrSpecs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sActAnrSpecs = ddfActRep['Analyser']['Value']\n",
    "sActAnrSpecs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for anrSpecName in ['defFitDistCutsFctr', 'defDiscrDistCutsFctr']:\n",
    "    if not isinstance(sActAnrSpecs[anrSpecName], str):\n",
    "        sActAnrSpecs[anrSpecName] = str(sActAnrSpecs[anrSpecName])\n",
    "\n",
    "dfComp = sRefAnrSpecs.compare(sActAnrSpecs)\n",
    "dfComp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRef = ddfRefRep['AFSM-ExAicMQua-r900m6q3d15'].reset_index()\n",
    "dfRef"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dfAct = ddfActRep['AFSM-ExAicMQua-r900m6q3d15'].reset_index()\n",
    "dfAct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleCols = ['NumEchant', 'Espèce', 'Passage', 'Adulte', 'Durée']\n",
    "dfRefSampCnts = dfRef[sampleCols + ['Density']].groupby(sampleCols).count()\n",
    "dfRefSampCnts.rename(columns=dict(Density='number'), inplace=True)\n",
    "dfRefSampCnts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActSampCnts = dfAct[sampleCols + ['Density']].groupby(sampleCols).count()\n",
    "dfActSampCnts.rename(columns=dict(Density='number'), inplace=True)\n",
    "dfActSampCnts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRefSampCnts.subtract(dfActSampCnts).number.abs().lt(3).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#anlysNumCol = 'NumAnlys'\n",
    "sampleNumCol = 'NumEchant'\n",
    "sampleCols = [sampleNumCol, 'Espèce', 'Passage', 'Adulte', 'Durée']\n",
    "# modelCols = ['Mod Key Fn', 'Mod Adj Ser']\n",
    "indexCols = sampleCols # [anlysNumCol] + sampleCols  # + modelCols\n",
    "compCols = ['Qual Bal 3', 'Qual Bal 2', 'Qual Bal 1', 'PDetec', 'Density']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRefStats = dfRef[indexCols + compCols].groupby(indexCols).agg(['mean', 'std'])\n",
    "dfRefStats.columns = [lvl0 + ' ' + lvl1 for lvl0, lvl1 in dfRefStats.columns]\n",
    "dfRefStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActStats = dfAct[indexCols + compCols].groupby(indexCols).agg(['mean', 'std'])\n",
    "dfActStats.columns = [lvl0 + ' ' + lvl1 for lvl0, lvl1 in dfActStats.columns]\n",
    "dfActStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "compStatCols = dfActStats.columns.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfDiffStats = ads.DataSet.compareDataFrames(dfRefStats.reset_index(), dfActStats.reset_index(), indexCols=indexCols, subsetCols=compStatCols)\n",
    "dfDiffStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sampleNumCol = 'NumEchant'\n",
    "sampleCols = [sampleNumCol, 'Espèce', 'Passage', 'Adulte', 'Durée']\n",
    "dfRefSampCnts = dfRef[sampleCols + ['Density']].groupby(sampleCols).count().rename(columns=dict(Density='count'))\n",
    "dfRefSampCnts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActSampCnts = dfAct[sampleCols + ['Density']].groupby(sampleCols).count()\n",
    "dfActSampCnts.rename(columns=dict(Density='count'), inplace=True)\n",
    "\n",
    "dfActSampCnts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Opt-analysis HTML reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf = pd.read_html('refout/ACDC2019-Naturalist-extrait-OptRapport.ExAicMQua-r900m6q3d15.html')\n",
    "len(ldf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldfTables = ldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nResults = (len(ldfTables) - 9) // 3\n",
    "nResults"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[0].values[:, 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([ldf[i] for i in [2, 3, 4]]).set_index(0).loc[:, 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfRes = pd.DataFrame([pd.concat([ldf[subTblInd] for subTblInd in range(resInd, resInd + 3)]).set_index(0).loc[:, 1]\n",
    "                      for resInd in range(2, 2 + 3 * nResults, 3)])\n",
    "dfRes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "', '.join(ldf[2 + 87*3 + 0].columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 0].drop(columns=['Unnamed: 0']).set_index('NumEchant')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "', '.join(ldf[2 + 87*3 + 1].columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 1].drop(columns=['Unnamed: 0']).set_index('NumEchant')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 1][ ['Qual Bal 3', 'Qual Bal 2', 'Qual Bal 1', 'PDetec', 'Density']].dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "indexCols = ['Scheme', 'Step', 'Property']\n",
    "df = ldf[2 + 87*3 + 2]\n",
    "df.columns = ['Scheme', 'Step', 'Property', 'Value']\n",
    "dfActRes = df[df.Property == 'results'].set_index(indexCols)\n",
    "dfActRes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfActRes.Value[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 5].set_index('Unnamed: 0').Value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ldf[2 + 87*3 + 7]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(dtype=float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "p.suffix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "p.stem"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "p.parent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8",
   "language": "python",
   "name": "py38"
  },
  "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.17"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}