At work, I find myself frequently crunching CSVs with headers prone to change names and location. I have a solution, but with no opportunity for code review I was hoping SE could offer a look

header_indexer was built to allow me to us the same parsing code among spreadsheets where the data is known, but it's location prone to change.

The indexer parses the header row and creates an index_calculated dictionary based on the below user generated dict, effectively translating the headers to find into their column indexes:

headers_dict = {
        # "reference":  "header to find"
        "hostname":     "DNSHostname",
        "track":        "TrackingID",
        "OS":           "OperatingSystem"

(Adjusting "header to find" values ensures adaptability when spreadsheet format changes)

Assuming we're using a header row like so...

spreadsheet[0] = ["Date", "Status", "TrackingID", "Title", "DNSHostname", "OperatingSystem"]

Feeding the header row and headers_dict to the below code, as arguments: sheet_headers=spreadsheet[0], head_names=headers_dict...

from typing import List, Dict

def index_headers(sheet_headers: List[str], head_names: Dict[str, str],
                  ignore_duplicates=False, ignore_nonindexed=False) -> Dict[str, int]:
    """Builds and returns an Index calc dict based on the provided header row\n
    Provide headers row and head names dictionary"""

    # A dictionary of all header names from given CSV
    # For each column header, ndx_all[header] = header's index
    ndx_all = {}
    index = 0
    for header in sheet_headers:
        ndx_all[header] = index
        index += 1

    # Create new NEW_NDX entries based on HEAD_NAMES entries, using indexes stored in ndx_all
    ndx_calc = {}
    for key, val in head_names.items():
            ndx_calc[key] = ndx_all[val]
        # Missing or unfound header. Address in later checks
        except (IndexError, KeyError):
            ndx_calc[key] = None

    # Check for non indexed headers, if allowed
    error_string = "\n"
    nonindexed = []
    if not ignore_nonindexed:
        for key, val in ndx_calc.items():
            if val is None:
                nonindexed.append([key, val])

        # Prepare string out to display missing headers
        if nonindexed:
            error_string += "\nNon indexed headers!\n" + '\n'.join(str(x) for x in nonindexed)

    # Check for duplicate indexes, if allowed
    duplicates = []
    dup_check = {}
    if not ignore_duplicates:
        for key, val in ndx_calc.items():
            dup_check.setdefault(val, set()).add(key)
        for val in dup_check.values():
            if len(val) > 1:
                for k in val:
                    duplicates.append([k, ndx_calc[k]])

        # Prepare string out to display problem headers
        if duplicates:
            error_string += "\nDuplcate header indexes!\n" + '\n'.join(str(x) for x in duplicates)

    # Raise ValueError,
    if duplicates or nonindexed:
        raise ValueError(error_string)

    return ndx_calc

...creates and returns the following dict, corresponding to headers_dict above:

ndx_calc = {
    "hostname": 4,
    "track":    2,
    "OS":       5

I have some ideas of improvement, namely to prompt a query against all non-indexed headers when given values aren't found, but wanted to get some sort of feedback before I proceeded

Thank you

  • \$\begingroup\$ Some quick thoughts: ndx_all = {} index = 0 for header in sheet_headers: ndx_all[header] = index index += 1` can be replaced with ndx_all = {} for index, header in enumerate(sheet_headers): ndx_all[header] = index I'd recommend, if you're planning on extending this code out, splitting up the index_header function - have one function for new_ndx entries, checking for non-indexed headers, etc. I also am not sure if the dupe-check makes sense as is - at least, there's an easier way to de-dupe than that. \$\endgroup\$ – n1c9 Jan 31 at 3:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.