Duplicated columns

[J-Moravec]

According to documentation, fastexcel maps directly to the arrow format which doesn't allow duplicated column names.

For this reason, fastexcel turns duplicated column into unique by appending _1, _2 etc. to them.

This is highly inconvenient for any data analysis where _X might be real key, and thus it might be impossible to know if _1 was added because of duplicated columns, or _1 is real data.

On top of this, I haven't found any documentation specifying how fastexcel is even handling duplicated columns.

1. How fastexcel deduplicates columns needs to be documented.
2. Either allow passing custom make_unique function or expose separator such that column name have form: f"{column}{sep}{i}"

The only way I have been able to do it is by basically reading the sheet twice, first the header row, transforming it using custom make_unique function, and then replacing the names from second read.

excel = fastexcel.read_excel(path)
header = excel.load_sheet(sheet, header_row=None, n_rows=1).to_polars().row(0)
columns = make_unique(header)

data = excel.load_sheet(sheet).to_polars()
data.columns = columns

My custom make_unique function looks like this:

def make_unique(x: Iterable, separator="_DUPLICATED_", none="_UNNAMED_"):
    """
    Make iterable unique by adding separator {separator}{n} to all duplicated names,
    where `n` is number of seen duplications starting (present excluded).

    In additional to this cleanup, `None` variables are replaced with `f"{none}{i}"`
    where `i` is column index, and values are coerced to string with whitespace excluded.

    Example:
        x = ["a ", 1, "a", "a", None]
        y = make_unique(x, "")
        # ["a", "1", "a1", "a2", "_UNNAMED_4"]
    """
    seen: dict[str, int] = {}
    y = []
    for i, item in enumerate(x):
        if item is None or not str(item).strip():
            item = f"{none}{i}"
        else:
            item = str(item).strip()

        if item in seen:
            seen[item] += 1
            y.append(f"{item}{separator}{seen[item]}")
        else:
            seen[item] = 0
            y.append(item)

    return y

_DUPLICATED_ and _UNNAMED_ might look ugly, but they have much smaller chance to exist in real data. And these are configurable, so if user needs something else, they can configure it themselves.

Consider comparison with R's tidyverse workhorse tibble:

#' @param .name_repair Treatment of problematic column names:
#'   * `"minimal"`: No name repair or checks, beyond basic existence,
#'   * `"unique"`: Make sure names are unique and not empty,
#'   * `"check_unique"`: (default value), no name repair, but check they are
#'     `unique`,
#'   * `"universal"`: Make the names `unique` and syntactic
#'   * `"unique_quiet"`: Same as `"unique"`, but "quiet"
#'   * `"universal_quiet"`: Same as `"universal"`, but "quiet"
#'   * a function: apply custom name repair (e.g., `.name_repair = make.names`
#'     for names in the style of base R).
#'   * A purrr-style anonymous function, see [rlang::as_function()]
#'
#'   This argument is passed on as `repair` to [vctrs::vec_as_names()].
#'   See there for more details on these terms and the strategies used
#'   to enforce them.

or a similar option, though less convenient, in R base::data.frame

* check.names:  logical. If TRUE then the names of the variables in the data frame are checked to ensure that they are syntactically valid variable names and are not duplicated. If necessary they are adjusted (by [make.names](https://www.rdocumentation.org/link/make.names?package=base&version=3.6.2)) so that they are.

in which case user can toggle check.names=FALSE and then fix them themselves (which is possible with data.frames since they are just arrays of columns and unique names are not required)

[lukapeschke]

Thank you for the issue @J-Moravec , the usecase sounds legit. What do you think about the following solution:

We add two alias and original_name to ColumnInfo. original_name becomes the name of the column as defined in the sheet, or the generated __UNNAMED__x name for unnamed columns. alias becomes the deduplicated name, or None if no deduplication happened.

This would allow you to use sheet.available_columns() to determine wether a column was deduplicated or not, so you don't have to load the sheet twice. It would also avoid introducing new renaming logic

[J-Moravec]

Thanks for response.

That would certainly solve the issue of not having to read the sheet twice, but I don't understand why not just provide API for handling the issue in easier way.

Given that fastexcel is the default engine for reading XLSX with Polars, I would like to highlight that likely many consumers of fastexcel code would be running the code from within polars using polars.read_excel.

In that case, proposed solution doesn't really solve the issue for anyone calling polars.read_excel as there isn't any parameter I can pass to fastexcel to transform the duplicated (and empty) columns in such way it won't crash with user's coding scheme.

I am not familiar enough with fastexcel, underlining carmine parsing Rust lib (if Polars documentation is correct), or Rust in general, so I don't know how hard or problematic would be to just expose a parameter to pass a str | func to modify this behaviour. Or if that is too difficult, just specify the duplicated and unnamed separators which default to duplicated_sep = "", unnamed_sep = "__UNNAMED__".

Then I don't need to manually inspect columns, but just pass these arguments to polars.read_excel and be done with it.
Then, and only then, manually inspecting columns could be great solution for more complex scenarios, but 90% of cases would be already solved by being able to specify the separators (and documenting the behaviour btw., haven't seen it documented).