Add support for column specs when reading data

[holgerbrandl]

Very often column type guessing does not work as intended by the user. Thus it would be helpful to support an optional column spec. This is very common in other tabular apis, see e.g. col_types argument in https://readr.tidyverse.org/reference/read_delim.html#arguments

Note: krangl supported this for all readers previously in a very similar manner:

DataFrame.readExcel(
                excelFile.absolutePath,
                sheet = "Equipment",
                colTypes = CompactColumnSpec("sis??bbsds")
            )

See https://github.com/holgerbrandl/krangl/blob/master/src/main/kotlin/krangl/TableIO.kt#L226

It would be great if kdf could be extended similarly to support a wider set of input formats directly without manual correction (as suggested in https://kotlin.github.io/dataframe/read.html#reading-excel).

[Jolanrensen]

Could be useful! However, one of the strengths of Dataframe is its strong typing. The recommended way to provide types to the columns read is by casting or converting to a @DataSchema. This is, for instance, also what happens when JSON is read according to an OpenAPI schema.

like:

@DataSchema
interface Type {
    val name: String
    val id: Int
    val group: String
    val something: Any
    etc.
}

DataFrame.readExcel(
    excelFile.absolutePath,
    sheet = "Equipment",
).convertTo<Type>()

We could consider allowing to supply a list of types for use in Jupyter or the String API (or the future compiler plugin) so users won't have to make a @Dataschema themselves, however, I would not make that list a String, rather a list of types or enums.

[holgerbrandl]

I just discovered that other DataFrame.read* such as readCsv support this already via thecolTypes parameter. Not as concise, but still. So I guess the ticket could be reworded to: suppot colTypes in readExcel for consistency with other read* functions.

@DataSchema seems a bit too much to me to finetune the reading process.

[koperagen]

When I added this function, I found a problem that colTypes is not that useful because XLS files have types. For the particular issue described in the documentation, i couldn't see how the desired result can be achieved with this parameter. There were two possible ways to convert already parsed values. I cannot recall the specifics at the moment. I will examine how R handles that conversion to see if we should do it the same way and maybe support this parameter.

But, hm, let me share some thoughts on a desired pipeline for data processing, because it's something i considered as well. Generally it's
1)
IDE: get a schema using plugin, read data as is.
notebooks: read data as is, get schema from it
2) refine schema using common operators such as convert, update, fill etc.
All that, ideally, with good completion and type safety

I think "colType" parameter is more appealing than manual conversion because you cannot easily change schema on the fly. Manual conversion is verbose and involves boilerplate. Isn't it? Even in Jupyter you would have to create a cell for every other expression to update types and column names.

I've been working on a compiler plugin that should make it more pleasant, though. It's still POC and won't be released until K2 release for IntelliJ. It will take more time for notebooks, because scripting in the new compiler is yet to be implemented. Here's a little demonstration of what it does

Screencast.2022-11-09.17.47.03.mp4

It can work only with compile time information. We have a bridge between data and compile time, Jupyter kernel and Gradle / KSP plugin. Then we should be able to evaluate the schema until we encounter a data dependent operation: pivot, gather, variants of split or something else that is not known at compile time.
At this point executing cell in the Jupyter saves the day. There's no general solution for IntelliJ. Just running the program and printing runtime schema could work very well, too

I'd appreciate feedback on this new information c:

[holgerbrandl]

Thanks for your kind reply.

For me, the colTypes approach worked very well with krangl, and we used it many times to fixate the format when reading data sheets. The archived krangl implementation could potentially serve better as an example than R I guess.

The problem with the data-types in excel is, that they are rarely used/applied correctly. So that's why I typically do not trust them, but rather prefer enforcing my intended schema using colTypes.

Personally, I never felt - yet!- the need/desire to use the compiler plugin. Probably, I'm just too old-school, and love to maintain the process which I have used in R and/or krangl. I don't use jupyter much yet (because of lacking completion support and still open bugs that block usage for me whenever I give it a try). I primarily use kdf on jvm (for building backend-services).

Another very practical example why I'd love to see a colTypes in readExcel: Consider a first xls file

foo
1
4
32

and a second file with

foo
2.3
7
45

Knowing the type of foo is Double, I want to enforce/cast/auto-convert this when reading the data. Using convert or having a compiler plugin build a schema seems tedious compared to a simple DataFrame.readExcel(file, colTypes= 'd')

Regarding the screencast, generally, I liked the version before the refactoring the most, as it was clearly stating references "tsDiff<Boolean>()" (and that's the only way I'm working with kdf at the moment). After the refactoring, the origin of tsDiff property felt obscure to me as it was never referenced in the sources (it seemed).