# Coalescing DataFrame columns and record source

**URL:** <https://discourse.julialang.org/t/coalescing-dataframe-columns-and-record-source/97988>\
**Category:** Data\
**Created:** [April 27, 2023, 9:42am UTC](https://discourse.julialang.org/t/coalescing-dataframe-columns-and-record-source/97988 "2023-04-27T09:42:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mreichMPI-BGC](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mreichmpi-bgc/32/43775_2.png) [@mreichMPI-BGC](https://discourse.julialang.org/u/mreichMPI-BGC)\
**Post date:** [April 27, 2023, 9:42am UTC](https://discourse.julialang.org/t/coalescing-dataframe-columns-and-record-source/97988/1 "2023-04-27T09:42:13Z")

</div>

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.

```julia
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

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
```

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.julialang.org/u/jules)\
**Post date:** [April 27, 2023, 10:32am UTC](https://discourse.julialang.org/t/coalescing-dataframe-columns-and-record-source/97988/2 "2023-04-27T10:32:44Z")

</div>

How about this (can probably be beautified a bit)

```julia
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

```

---

<div class="post-metadata">

**Author:** ![mreichMPI-BGC](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mreichmpi-bgc/32/43775_2.png) [@mreichMPI-BGC](https://discourse.julialang.org/u/mreichMPI-BGC)\
**Post date:** [April 27, 2023, 3:52pm UTC](https://discourse.julialang.org/t/coalescing-dataframe-columns-and-record-source/97988/3 "2023-04-27T15:52:35Z")

</div>

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

To add the original variable names to the computed ones:

```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) => 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
```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [May 2, 2023, 7:14am UTC](https://discourse.julialang.org/t/coalescing-dataframe-columns-and-record-source/97988/4 "2023-05-02T07:14:55Z")

</div>

```julia
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