Disambiguate duplicated column names when reading data

[holgerbrandl]

E.g. when using DataFrame.readExcel(), it fails to read sheets with duplicated column names. It's bad practice to have this type of duplication in data, but that's how data ends up on one's desk quite frequently.

kdf should follow the approach implemented in other tabular-data-APIs, to disambiguate (or repair) duplications, e.g by correcting duplicatedcolumn name

• foo (first appearance)
• foo_1 (second appearance)
• foo_2 (third appearance)

Such functionality is also referred to as name-repair strategy, e.g. see https://readr.tidyverse.org/reference/read_delim.html (name_repair)

The function should be applied/provided to/by all DataFrame.read* methods for API consistency.

Optionally, the user could be given more control over the repair strategy.

[koperagen]

My bad, i think all other formats do support disambiguation using the same strategy, but i forgot about it when implementing read Excel. Oh well.
With I/O i still wonder if we can just already copy most of the parameters from R or Python. They should cover most of the people's needs. But for me it's an open question, because i honestly never saw so many parameters in any Kotlin/Java library. It totally makes sense for IO, but still..

[holgerbrandl]

Thanks @koperagen , looking forward to the fix.

Regarding parameters, that's a very interesting - almost religious - question to me. Generally, I feel that kotlin devs are very very shy to add many (>3) parameters. In contrast in r/python it's natural and widely adopted to have a gazillion of parameters with defaults to address the many shades of data. Partially its because in scripting language there's not so great auto-discoverability via completion, and even professionals barely remember more a few dozen method names. So library makers naturally fall back to encode functional variants as parameters. However, also when using kdf, auto-completion does not do magic and often fails (to me at least) to provide the next/missing api element.

[koperagen]

I think one of the reasons is that in dynamic languages, one parameter can actually accept argument of several types. It looks very cool: one function for all possible read_excel. In Kotlin, we'll have to either introduce overloads and their number will grow exponentially, or sacrifice type safety (use Any) or ease of use (use sealed class like a tagged union so you can readCSV(Data(URL("..."))), readCSV(Data(File(...))), readCSV(Data.Literal("...")). Well, mixing approaches seems like a way to go: we can introduce overloads for some parameters and use sealed classes for others.

[holgerbrandl]

Indeed, I'm always shy to use Any although I have absolutely no problem to work with "untyped" APIs in R. The idea of using sealed classes here never occurred to me but seems indeed a nice workaround.

[holgerbrandl]

Irrespective of the interesting API design considerations, are there any news on the reported issue itself: I've just stumbled over an other excel data set with a duplicated column name which the library currently can't read (with no workaround afaik).

[zaleslaw]

Could you please generate fake excel file which fully reflects your problem and upload here, will add it as a broken test

[holgerbrandl]

Sure, see attached example dataset. A corresponding test snippet could be just:

fun main() {
    val df = DataFrame.readExcel("iris_mod.xlsx")
    df.print()
}

Currently, this fails with

Exception in thread "main" org.jetbrains.kotlinx.dataframe.exceptions.DuplicateColumnNamesException: Duplicate column names: [Species]
All column names: [Sepal.Length, Sepal.Width, Petal.Length, Petal.Width, Species, Other.Width, Species]
	at org.jetbrains.kotlinx.dataframe.impl.DataFrameImpl.<init>(DataFrameImpl.kt:51)
	at org.jetbrains.kotlinx.dataframe.api.ConstructorsKt.dataFrameOf(constructors.kt:185)
	at org.jetbrains.kotlinx.dataframe.io.XlsxKt.readExcel(xlsx.kt:195)
	at org.jetbrains.kotlinx.dataframe.io.XlsxKt.readExcel(xlsx.kt:139)

iris_mod.xlsx

In this example, my expectation would be that some renaming policy kicks in and renames the second "Species" column to "Species_2" (or similar).