a new method of the flatten function in DataFrames

[sprmnt21]

what do you think of the utility of having a method of the flatten function in DataFrames that expands a dataframe type df2 in the form dfexp?

df = DataFrame(a=1:3)
df1=DataFrame(b=11:15)

CSV.write("tmp1.csv", df)
CSV.write("tmp2.csv", df)
CSV.write("tmp3.csv", df1)

julia> df2
3×3 DataFrame
 Row │ files     other_cols  subdf                 
     │ String    Int64       DataFrame             
─────┼─────────────────────────────────────        
   1 │ tmp1.csv           1  3×1 DataFrame         
   2 │ tmp2.csv           2  3×1 DataFrame         
   3 │ tmp3.csv           3  5×1 DataFrame         


julia> dfexp=flatten(df2,:subdf)
11×4 DataFrame
 Row │ files     other_cols  a        b            
     │ String    Int64       Int64?   Int64?       
─────┼────────────────────────────────────────     
   1 │ tmp1.csv           1        1  missing      
   2 │ tmp1.csv           1        2  missing      
   3 │ tmp1.csv           1        3  missing      
   4 │ tmp2.csv           2        1  missing      
   5 │ tmp2.csv           2        2  missing      
   6 │ tmp2.csv           2        3  missing      
   7 │ tmp3.csv           3  missing       11      
   8 │ tmp3.csv           3  missing       12      
   9 │ tmp3.csv           3  missing       13      
  10 │ tmp3.csv           3  missing       14      
  11 │ tmp3.csv           3  missing       15    

this is a function that does what is indicated, just to give an idea of what is required not to suggest how it should be done.

mapreduce(r->crossjoin(DataFrame(r[Not(:subdf)]),r.subdf), (x, y) -> vcat(x, y; cols = :union), eachrow(df2))

[bkamins]

As commented on Slack. The operation you request is vcat. The only difference with the standard vcat is that now vcat allows you to create only one indicator column and you want more than one column. We could consider extending the source keyword API to allow what you ask for.

Let us wait for opinions.

[sprmnt21]

thanks for your attention.
Another way of looking at it could be the following.
Think of the starting table df2 as if it were the result of a groupby and the table dfexp was the result of some kind of un-group.

[bkamins]

But under your thinking all data frames in :subdf columns would have to have exactly the same column names and eltypes. This clearly is not the case in your example. That is why I quote vcat as the crucial difference between vcat and un-group is that vcat allows you do decide how you want to handle cases when column names or eltypes do not match.

[pdeffebach]

Additionally, I think that allowing ByRow functions in combine to re-size would solve part of this problem.

It would not work if the data frames have different keys, and you need to convert to Tables.columntable (i.e. a NamedTuple) because data frames don''t support keys. But other than that,

combine(df, :c => ByRow(Tables.columntable) => AsTable)

would maybe work.

I'm not sure this is desirable though. This might not be consistent with how combine actually works / should be thought about.

[bkamins]

This is what I also thought but I have exactly the same reservations.

Now I have realized that actually assuming the columns match in all data frames the following works assuming non-dataframe columns form unique keys (which I guess is expected):

julia> df = DataFrame(x=[1,2,3], y=[DataFrame(p=1:2,q=11:12), DataFrame(p=3:4,q=13:14), DataFrame(p=5:6,q=15:16)])
3×2 DataFrame
 Row │ x      y
     │ Int64  DataFrame
─────┼──────────────────────
   1 │     1  2×2 DataFrame
   2 │     2  2×2 DataFrame
   3 │     3  2×2 DataFrame

julia> combine(x -> only(x).y, groupby(df, Not(:y)))
6×3 DataFrame
 Row │ x      p      q
     │ Int64  Int64  Int64
─────┼─────────────────────
   1 │     1      1     11
   2 │     1      2     12
   3 │     2      3     13
   4 │     2      4     14
   5 │     3      5     15
   6 │     3      6     16

[bkamins]

and if it is not unique:

combine(x -> reduce(vcat, x.y), groupby(df, Not(:y)))

[sprmnt21]

Here she is.
This solution demonstrates the potential of DataFrames to also deal with situations, for which perhaps it was not specifically designed.

[bkamins]

Would Tables.dictrowtable meet your requirements?

[sprmnt21]

Maybe.
But you should help me figure out how to fit it to the case.

[sprmnt21]

this is what I can do ...

transform(df2, :sdf =>Tables.dictrowtable(c->mapreduce(x->copy.(eachrow(x)), vcat, c)) => AsTable)

but i get
LoadError: ArgumentError: no default Tables.rows implementation for type: var"#83#85"

[bkamins]

I meant this:

julia> df = DataFrame(id=1:2, a=[DataFrame(x=1:2), DataFrame(y=3:4)])
2×2 DataFrame
 Row │ id     a
     │ Int64  DataFrame
─────┼──────────────────────
   1 │     1  2×1 DataFrame
   2 │     2  2×1 DataFrame

julia> select(df, :id, :a => Tables.dictrowtable => AsTable)
2×3 DataFrame
 Row │ id     x        y
     │ Int64  Array…?  Array…?
─────┼─────────────────────────
   1 │     1  [1, 2]   missing
   2 │     2  missing  [3, 4]

and then ideally you would do:

flatten(select(df, :id, :a => Tables.dictrowtable => AsTable), [:x, :y])

but now I see that I need to fix flatten to add support for missing to it. I will open a PR for this.

[bkamins]

Ah - this is not as easy as I have thought.

@nalimilan - what do you think. Currently in flatten when we see missing we error as it is not iterable. To make the above examples work we would need to start treating missing as having "any" number of elements - flexibly adjusting to the remaining columns that define length. This is a bit hard to implement (of course doable), but the question is if this would be an intuitive behavior.