Don't infer schema from the first object in an array

[sa-]

This throws an error KeyError: key :a not found

using DataFrames, JSON3
lines = [
    """{"a": 1}""",
    """{"b": 2}""",
]
DataFrame(JSON3.read.(lines))

This ignores column b

lines = [
    """{"a": 1}""",
    """{"a": 2, "b": 2}""",
]
DataFrame(JSON3.read.(lines))

I would expect this input:

lines = [
    """{"a": 1}""",
    """{"b": 2}""",
]
DataFrame(JSON3.read.(lines))

to have this output:

│ Row │ a      │ b      │
│     │ Union… │ Union… │
├─────┼────────┼────────┤
│ 1   │ 1      │        │
│ 2   │        │ 2      │

[bkamins]

I have transferred the issue as it is Tables.jl not DataFrames.jl related.

[sa-]

I would be happy to contribute as well if I can get some pointers on where to start

[quinnj]

Yes, I've been thinking about this a fair bit recently; in an ideal world, the default Tables.columns(non_homogenous_rows) routine would do this kind of "unioning" by default. Unfortunately, this unioning is necessarily less performant that taking the "column names" from the first row, because on each row, we're basically limited to [Tables.getcolumn(row, nm) for nm in Tables.columnnames(row)] kind of pattern to get all values on each row.

I'm torn: on the one hand, this would be a much better default for non-schema inputs; on the other hand, it's painful to incur the performance hit for all non-schema row tables just because of the tiny minority of these kinds of input tables w/ non-homogenous rows.

I'm going to experiment with a few things, but would be interested to hear others thoughts here.

[bkamins]

I would add a kwarg handling this (just as we do in DataFrames.jl for push!, append! and vcat). Indeed having to check the consistency is expensive (also in DataFrames.jl).

Actually there are three natural scenarios:

1. fast: take first row
2. slow: union
3. slow: take first row BUT do a strict column names consistency check

Then the only question would be what should be the default (the fast or the safe path) - but here probably users should comment on what they prefer.

[sa-]

For relatively small datasets, where coding time is much greater than computing time, an infer_schema kwarg that takes a performance hit would work out well.

For larger datasets there is a another fast option: let the person specify a schema themselves.

[bkamins]

For me a common case where :union is required is schema discovery for very large JSON files.

[tpapp]

I would recommend leaving the API as is and handle this by a wrapper implementing a sink and a source, that passes through eg a rows iterator, that could either check the whole data (small datasets) then union, or do some reprocessing based on the first X columns (whatever was there is in the schema).

This could live in its own package, allowing users to experiment with performance trade-offs.

[quinnj]

Ok, I played around with a couple of ideas in Tables.jl, but it's just tough to get anything close enough to the current performance. @sa- , if you'd like to take a stab at it, I think it'd be great ot add to TableOperations.jl package; I don't know the best name for this, but something like TableOperations.rowunion or TableOperations.unionrows. We have a good pattern in that package for these kinds of constructs; I would follow basically the TableOperations.Map struct and then we just implement the custom logic in the iterate method; we'll need to keep track of all names we've seen before, so something like a names::Vector{Symbol} field, and then we can probably have a UnionedRow <: Tables.AbstractRow wrapper struct, that would wrap each input row, and maybe in iterate, we do a diff between our accumulated names field and the Tables.columnnames(row) of the current row; then for Tables.getcolumn(row, nm) for a given row, we check if the nm is in the current row names and if so, pass through Tables.getcolumn, otherwise, return missing. Probably some more cases to think about, but that sounds like a decent start.

[sa-]

What's the difference between DataFrames and TableOperations?

[bkamins]

TableOperations.jl is generic for any Tables.jl compatible object.

DataFrames.jl is specific to AbstractDataFrame handling that it defines.

[quinnj]

Closing with functionality added in #212