def build_full_tally_sheet(double_translated, fpath: Path, taxonomy: Taxonomy, parameters: Parameters, circle_prefix: str): candidate_names = [x for x, y in double_translated] local_names = process_exceptions(candidate_names, fpath, circle_prefix) # if issf etc in list, then base species must be also issfs = taxonomy.filter_issf(local_names) for cn in issfs: base_species = taxonomy.report_as(cn) if base_species: local_names.append(base_species) entries = [] for local_name in local_names: # common_name, taxon_order, species_group, NACC_SORT_ORDER record = taxonomy.find_local_name_row(local_name) if record is not None: # e.g. ('White-throated Sparrow', 31943, 'New World Sparrows', 1848.0) entry = (record.comName, record.TAXON_ORDER, record.SPECIES_GROUP, record.NACC_SORT_ORDER, record.ABA_SORT_ORDER, '', 0 ) # append 'Rare', 'Total' entries.append(entry) df = pd.DataFrame(entries, columns=[ 'CommonName', 'TaxonOrder', 'Group', 'NACC_SORT_ORDER', 'ABA_SORT_ORDER', 'Rare', 'Total' ]) # Re-order cols = [ 'Group', 'CommonName', 'Rare', 'Total', 'TaxonOrder', 'NACC_SORT_ORDER', 'ABA_SORT_ORDER' ] local_checklist = df[cols] local_checklist.sort_values(by='TaxonOrder', inplace=True) # local_checklist.shape # double_translated may have duplicates local_checklist = local_checklist[ ~local_checklist.duplicated(subset=['CommonName'], keep='first')] local_checklist = process_annotations_or_rarities(local_checklist, fpath, circle_prefix) # Re-order columns preferred_order = [ 'Group', 'CommonName', 'Rare', 'D', 'Total', 'Ad', 'Im', 'TaxonOrder', 'NACC_SORT_ORDER', 'ABA_SORT_ORDER', 'Difficulty', 'Adult', 'Immature', 'W-morph', 'B-Morph', 'CountSpecial' ] newcols = [ col for col in preferred_order if col in local_checklist.columns ] local_checklist = local_checklist[newcols] # Write out full tally sheet # circle_code = circle_prefix[0:4] # double_path = outputs_path / f'{circle_code}-DoubleX.xlsx' # write_local_checklist_with_group(local_checklist, double_path, parameters.parameters) return local_checklist