--- a/process_xlsx.py	Fri Apr 30 12:09:25 2021 +0000
+++ b/process_xlsx.py	Thu Jul 15 20:12:34 2021 +0000
@@ -4,23 +4,35 @@
 
 import xlrd
 import yaml
+from mappings import optional_samples_cols_mapping
 
 FILE_FORMAT = 'fastq'
 
 
-def extract_data(xl_sheet, expected_columns):
+def extract_data(xl_sheet, expected_columns, optional_cols=None):
     """
     1. Check that the columns I expect are present in the sheet
     (any order and mixed with others, it's just a verification that
     the user filled the correct template)
     2. Fill a dictionary with the rows data indexed by first column in list"""
     sheet_columns = {}
+    if optional_cols is None:
+        optional_cols = []
+    optional_cols_loaded = []
     for sh_col in range(xl_sheet.ncols):
-        if xl_sheet.cell(0, sh_col).value in expected_columns:
+        if (xl_sheet.cell(0, sh_col).value in expected_columns) \
+           or (xl_sheet.cell(0, sh_col).value in optional_cols):
             if xl_sheet.cell(0, sh_col).value in sheet_columns.keys():
-                sys.exit("Duplicated columns")
+                sys.exit("Duplicated columns found")
             else:
                 sheet_columns[xl_sheet.cell(0, sh_col).value] = sh_col
+                if xl_sheet.cell(0, sh_col).value in optional_cols:
+                    # store the list of optional cols available
+                    optional_cols_loaded.append(xl_sheet.cell(0, sh_col).value)
+    provided_cols = expected_columns + optional_cols_loaded
+
+    # check that the required columns are all present
+    # TODO: revise this for optional columns
     for col in range(len(expected_columns)):
         assert expected_columns[col] in sheet_columns.keys(), \
             "Expected column %s not found" % expected_columns[col]
@@ -32,9 +44,9 @@
     # skip first 2 rows: column names + comments rows
     for row_id in range(2, xl_sheet.nrows):
         row_dict = {}
-        for col in range(1, len(expected_columns)):
-            sheet_col_index = sheet_columns[expected_columns[col]]
-            row_dict[expected_columns[col]] = xl_sheet.cell(row_id, sheet_col_index).value
+        for col in range(1, len(provided_cols)):
+            sheet_col_index = sheet_columns[provided_cols[col]]
+            row_dict[provided_cols[col]] = xl_sheet.cell(row_id, sheet_col_index).value
         # should check for duplicate alias/ids?
         if xl_sheet.cell(row_id, index_col).value in data_dict.keys():
             tmp = data_dict[xl_sheet.cell(row_id, index_col).value]
@@ -42,7 +54,7 @@
             data_dict[xl_sheet.cell(row_id, index_col).value].append(row_dict)
         else:
             data_dict[xl_sheet.cell(row_id, index_col).value] = row_dict
-    return data_dict
+    return data_dict, optional_cols_loaded
 
 
 def paste_xls2yaml(xlsx_path):
@@ -86,22 +98,25 @@
     raise ValueError('No entries found in studies sheet')
 studies_dict = {}
 studies_col = ['alias', 'title', 'study_type', 'study_abstract']
-studies_dict = extract_data(xl_sheet, studies_col)
+studies_dict, _ = extract_data(xl_sheet, studies_col)
 
 # PARSE SAMPLES
 #################
 xl_sheet = xl_workbook.sheet_by_name('ENA_sample')
 if xl_sheet.nrows < 3:
     raise ValueError('No entries found in samples')
+
+samples_cols_excel = ['alias', 'title', 'scientific_name', 'sample_description']
+# optional_samples_cols_mapping = {}
 if args.viral_submission:
-    samples_cols = ['alias', 'title', 'scientific_name', 'sample_description',
-                    'geographic location (country and/or sea)', 'host common name',
-                    'host health state', 'host sex', 'host scientific name', 'collector name',
-                    'collection date', 'collecting institution', 'isolate']
-else:
-    samples_cols = ['alias', 'title', 'scientific_name', 'sample_description']
-samples_dict = extract_data(xl_sheet, samples_cols)
+    # load columns names from the table
+    samples_cols_excel = samples_cols_excel + ['geographic location (country and/or sea)',
+                                               'host common name', 'host health state',
+                                               'host sex', 'host scientific name', 'collector name',
+                                               'collecting institution', 'isolate']
 
+samples_dict, samples_optional_cols_loaded = extract_data(xl_sheet, samples_cols_excel,
+                                                          optional_samples_cols_mapping.keys())
 # PARSE EXPERIMENTS
 #################
 xl_sheet = xl_workbook.sheet_by_name('ENA_experiment')
@@ -112,7 +127,7 @@
                'library_layout', 'insert_size', 'library_construction_protocol',
                'platform', 'instrument_model']
 
-experiments_dict = extract_data(xl_sheet, exp_columns)
+experiments_dict, _ = extract_data(xl_sheet, exp_columns)
 
 # PARSE RUNS SHEET
 #################
@@ -120,23 +135,27 @@
 if xl_sheet.nrows < 3:
     raise ValueError('No entries found in runs sheet')
 run_cols = ['alias', 'experiment_alias', 'file_name', 'file_format']
-runs_dict = extract_data(xl_sheet, run_cols)
+runs_dict, _ = extract_data(xl_sheet, run_cols)
 
 # WRITE HEADERS TO TABLES
 studies_table = open(pathlib.Path(args.out_path) / 'studies.tsv', 'w')
 studies_table.write('\t'.join(['alias', 'status', 'accession', 'title', 'study_type',
                                'study_abstract', 'pubmed_id', 'submission_date']) + '\n')
 samples_table = open(pathlib.Path(args.out_path) / 'samples.tsv', 'w')
+
+samples_cols = ['alias', 'title', 'scientific_name', 'sample_description']
+# extend the samples_cols list to add the ones that are filled by the CLI
+samples_cols = samples_cols + ['status', 'accession', 'taxon_id', 'submission_date']
 if args.viral_submission:
-    samples_table.write('\t'.join(['alias', 'status', 'accession', 'title', 'scientific_name',
-                                   'taxon_id', 'sample_description', 'collection_date',
-                                   'geographic_location', 'host_common_name', 'host_subject_id',
-                                   'host_health_state', 'host_sex', 'host_scientific_name',
-                                   'collector_name', 'collecting_institution', 'isolate',
-                                   'submission_date']) + '\n')
-else:
-    samples_table.write('\t'.join(['alias', 'status', 'accession', 'title', 'scientific_name',
-                                   'taxon_id', 'sample_description', 'submission_date']) + '\n')
+    # extend the samples columns with the viral specific data
+    samples_cols = samples_cols + ['geographic_location', 'host_common_name',
+                                   'host_subject_id', 'host_health_state', 'host_sex',
+                                   'host_scientific_name', 'collector_name',
+                                   'collecting_institution', 'isolate']
+    if len(samples_optional_cols_loaded) > 0:
+        for optional_cols_excel in samples_optional_cols_loaded:
+            samples_cols.append(optional_samples_cols_mapping[optional_cols_excel])
+samples_table.write('\t'.join(samples_cols) + '\n')
 
 experiments_table = open(pathlib.Path(args.out_path) / 'experiments.tsv', 'w')
 experiments_table.write('\t'.join(['alias', 'status', 'accession', 'title', 'study_alias',
@@ -164,22 +183,44 @@
                                    'ENA_submission_data']) + '\n')  # assuming no pubmed_id
 for sample_alias, sample in samples_dict.items():
     # sample_alias = sample_alias + '_' + timestamp
+    samples_row_values = [sample_alias, sample['title'], sample['scientific_name'],
+                          sample['sample_description'], action, 'ena_accession',
+                          'tax_id_updated_by_ENA', 'ENA_submission_date']
     if args.viral_submission:
+        # add the values that are unique for the viral samples
         if sample['collector name'] == '':
             sample['collector name'] = 'unknown'
-        samples_table.write('\t'.join([sample_alias, action, 'ena_accession', sample['title'],
-                                       sample['scientific_name'], 'tax_id_updated_by_ENA',
-                                       sample['sample_description'], sample['collection date'],
-                                       sample['geographic location (country and/or sea)'],
-                                       sample['host common name'], 'host subject id',
-                                       sample['host health state'], sample['host sex'],
-                                       sample['host scientific name'], sample['collector name'],
-                                       sample['collecting institution'], sample['isolate'],
-                                       'ENA_submission_date']) + '\n')
-    else:
-        samples_table.write('\t'.join([sample_alias, action, 'ena_accession', sample['title'],
-                                       sample['scientific_name'], 'tax_id_updated_by_ENA',
-                                       sample['sample_description']]) + '\n')
+        samples_row_values = samples_row_values + \
+            [sample['geographic location (country and/or sea)'], sample['host common name'],
+             'host subject id', sample['host health state'], sample['host sex'],
+             sample['host scientific name'], sample['collector name'],
+             sample['collecting institution'], sample['isolate']]
+        # add the (possible) optional columns values
+        if len(samples_optional_cols_loaded) > 0:
+            for optional_col in samples_optional_cols_loaded:
+                # parse values stored as in excel date format (=float)
+                if optional_col in ('collection date', 'receipt date'):
+                    # check if excel stored it as date
+                    if isinstance(sample[optional_col], float):
+                        year, month, day, hour, minute, second = xlrd.xldate_as_tuple(
+                            sample[optional_col], xl_workbook.datemode)
+                        month = "{:02d}".format(month)
+                        day = "{:02d}".format(day)
+                        hour = "{:02d}".format(hour)
+                        minute = "{:02d}".format(minute)
+                        second = "{:02d}".format(second)
+                        # format it as 2008-01-23T19:23:10
+                        sample[optional_col] = str(year) + '-' + str(month) + '-' + str(day) + \
+                            'T' + str(hour) + ':' + str(minute) + ':' + str(second)
+                # excel stores everything as float so I need to check if
+                # the value was actually an int and keep it as int
+                if isinstance(sample[optional_col], float):
+                    if int(sample[optional_col]) == sample[optional_col]:
+                        # it is not really a float but an int
+                        sample[optional_col] = int(sample[optional_col])
+                samples_row_values.append(str(sample[optional_col]))
+    samples_table.write('\t'.join(samples_row_values) + '\n')
+
     for exp_alias, exp in experiments_dict.items():
         # should I check here if any experiment has a study or sample alias that is incorrect?
         # (not listed in the samples or study dict)