Coalescing DataFrame columns and record source

Specific Domains Data
1

Dear Julia Data community,
I would like to coalesce entries across columns of a DataFrame and record where the value is coming from. I came up with the following function, which seems to work but feels a bit cumbersome and indirect.

using DataFrames, DataFrameMacros
function coalesce_dfcols!(df::DataFrame, vars::Vector{<:AbstractString}; outname=join(vars, "_") * "_coal")
  @transform! df  outname = coalesce({{vars}}...)
  #transform!(df, AsTable([:x,:y,:z]) => ByRow(x->coalesce(x...)) => x->join(x,"_")*"_coal" ) #Dataframe style
  @transform! df "source_of_" * outname = ismissing({outname}) ? missing : vars[findfirst(isequal.({{vars}}, {outname}))]
  #transform(ttdf, (AsTable([:x,:y,:z]), :x_y_z_coal) => ((x,y) -> ismissing(y) ? missing : 1) => (x,y)->"source_of_"*y) ##does not work
end

Is there a simpler way (e.g. can one access the names of the in the transform function of the minilanguage?) [And I did not manage the second transform in original Dataframe minilanguage]

Below a MWE:



julia> ttdf = DataFrame(id=1:5, x=[1,2,missing, missing, missing], y=[missing, 4,missing,6, missing], z=[5,8,3,2, missing])
5×4 DataFrame
 Row │ id     x        y        z       
     │ Int64  Int64?   Int64?   Int64?  
─────┼──────────────────────────────────
   1 │     1        1  missing        5
   2 │     2        2        4        8
   3 │     3  missing  missing        3
   4 │     4  missing        6        2
   5 │     5  missing  missing  missing 

julia> coalesce_dfcols!(ttdf, ["x", "y", "z"])
5×6 DataFrame
 Row │ id     x        y        z        x_y_z_coal  source_of_x_y_z_coal 
     │ Int64  Int64?   Int64?   Int64?   Int64?      String?
─────┼────────────────────────────────────────────────────────────────────
   1 │     1        1  missing        5           1  x
   2 │     2        2        4        8           2  x
   3 │     3  missing  missing        3           3  z
   4 │     4  missing        6        2           6  y
   5 │     5  missing  missing  missing     missing  missing
2

How about this (can probably be beautified a bit)

julia> transform(ttdf, AsTable([:x, :y, :z]) => ByRow(nt -> begin
           i = findfirst(x -> x !== missing, values(nt))
           i === nothing ? (missing, missing) : (keys(nt)[i], values(nt[i]))
       end) => [:from, :coalesced])

5×6 DataFrame
 Row │ id     x        y        z        from     coalesced
     │ Int64  Int64?   Int64?   Int64?   Symbol?  Int64?
─────┼──────────────────────────────────────────────────────
   1 │     1        1  missing        5  x                1
   2 │     2        2        4        8  x                2
   3 │     3  missing  missing        3  z                3
   4 │     4  missing        6        2  y                6
   5 │     5  missing  missing  missing  missing    missing
3

Thanks! Yes, was not aware that named tuples are passed so that keys works.

To add the original variable names to the computed ones:

transform(ttdf, AsTable([:x, :y, :z]) => ByRow(nt -> begin
                  i = findfirst(x -> x !== missing, values(nt))
                  i === nothing ? (missing, missing) : (keys(nt)[i], values(nt[i]))
              end) => nt -> join(nt, '_').* ["_source", "_coal"])

5×6 DataFrame
 Row │ id     x        y        z        x_y_z_source  x_y_z_coal 
     │ Int64  Int64?   Int64?   Int64?   Symbol?       Int64?     
─────┼────────────────────────────────────────────────────────────
   1 │     1        1  missing        5  x                      1
   2 │     2        2        4        8  x                      2
   3 │     3  missing  missing        3  z                      3
   4 │     4  missing        6        2  y                      6
   5 │     5  missing  missing  missing  missing          missing
4 
julia> cols=[:x,:y,:z]
3-element Vector{Symbol}:
 :x
 :y
 :z

julia> ttdf.from=findfirst.(!ismissing, eachrow(ttdf[:,cols]))
5-element Vector{Union{Nothing, Symbol}}:
 :x
 :x
 :z
 :y
 nothing

julia> ttdf.coal=[!isnothing(c) ? ttdf[r,c] : missing for (r,c) in  enumerate(ttdf.from) ]
5-element Vector{Union{Missing, Int64}}:
 1
 2
 3
 6
  missing

julia> ttdf
5×6 DataFrame
 Row │ id     x        y        z        from    coal    
     │ Int64  Int64?   Int64?   Int64?   Union…  Int64?
─────┼───────────────────────────────────────────────────
   1 │     1        1  missing        5  x             1
   2 │     2        2        4        8  x             2
   3 │     3  missing  missing        3  z             3
   4 │     4  missing        6        2  y             6
   5 │     5  missing  missing  missing          missing

EDIT: I hadn’t read the previous posts carefully, to see that this one of mine adds little