Julia DataFrames: Replace entries in a dataframe based on a comparison with another dataframe

Question

I have the following dataframes:

df1 = DataFrame(
    col_A = [1, 2, 3, 4, 5, 6, 7],
    col_B = ["A", "B", "C", "D", "E", "F", "G"],
    col_C = missing,
)

7×3 DataFrame
 Row │ col_A  col_B   col_C   
     │ Int64  String  Missing 
─────┼────────────────────────
   1 │     1  "A"     missing 
   2 │     2  "B"     missing 
   3 │     3  "C"     missing 
   4 │     4  "D"     missing 
   5 │     5  "E"     missing 
   6 │     6  "F"     missing 
   7 │     7  "G"     missing

df2 = DataFrame(
    col_X = [1, 2, 3, 4, 5, 5],
    col_Y = ["A", "nope", "C", "nope", "E", "E"],
    col_Z = ["First", "Second", "Third", "Fourth", "Fifth", "Duplicated"]
)

6×3 DataFrame
 Row │ col_X  col_Y   col_Z      
     │ Int64  String  String     
─────┼───────────────────────────
   1 │     1  "A"     "First"
   2 │     2  "nope"  "Second"
   3 │     3  "C"     "Third"
   4 │     4  "nope"  "Fourth"
   5 │     5  "E"     "Fifth"
   6 │     5  "E"     "Duplicated"

I need to efficiently replace the values of df1.col_C with those of df2.col_Z if there's a match between -let's say- composite keys made from the first 2 columns in both dataframes (e.g. (1, "A") occurs in both, but (2, "B") doesn't), and otherwise keep things unchanged. If there were duplicated composite keys, get the last occurrence in df2.

So df1 would become:

7×3 DataFrame
 Row │ col_A  col_B   col_C      
     │ Int64  String  String?    
─────┼───────────────────────────
   1 │     1  "A"     "First"
   2 │     2  "B"     missing    
   3 │     3  "C"     "Third"
   4 │     4  "D"     missing    
   5 │     5  "E"     "Duplicated"
   6 │     6  "F"     missing    
   7 │     7  "G"     missing    

Answer 1

Is this satisfactory?

julia> df1.col_C .= ifelse.(df1.col_A .== df2.col_X .&& 
                            df1.col_B .== df2.col_Y, 
                            df2.col_Z, missing)
5-element Vector{Union{Missing, String}}:
 "First"
 missing
 missing
 missing
 "Fifth"

Using Bogumil's answer, I think:

mapping = Dict(zip(df2.col_X, df2.col_Y) .=> df2.col_Z)
df1.col_C = [get(mapping, k, missing) 
  for k in zip(df1.col_A, df1.col_B)]

will fix the un-aligned dfs problem you commented about.

Answer 2

using InMemoryDatasets package:

df1 = Dataset(
    col_A = [1, 2, 3, 4, 5, 6, 7],
    col_B = ["A", "B", "C", "D", "E", "F", "G"],
    col_C = missings(String, 7),
)
df2 = Dataset(
    col_A = [1, 2, 3, 4, 5, 5],
    col_B = ["A", "nope", "C", "nope", "E", "E"],
    col_C = ["First", "Second", "Third", "Fourth", "Fifth", "Duplicated"]
)
update!(df1, df2, on = [:col_A, :col_B])

Answer 3

I assume, if you say that row-count does not need to match that you want to lookup in df2 the matching key. This feature is planned to be added in https://github.com/JuliaData/DataFrames.jl/issues/2243 release. Currently we are discussing what the best API would be.

Next, note that it is not clear in your question what to do if there were duplicate keys in the df2 table. I assume there are none. In this case currently you can do:

julia> mapping = Dict(df2.col_X .=> df2.col_Z)
Dict{Int64, Union{Missing, String}} with 5 entries:
  5 => "Fifth"
  4 => "foo"
  2 => "Second"
  3 => missing
  1 => "First"

julia> df1.col_C = [get(mapping, k, v) for (k, v) in zip(df1.col_A, df1.col_C)]
6-element Vector{Union{Missing, String}}:
 "First"
 "Second"
 missing
 "foo"
 "Fifth"
 missing

(note that the result is different than what you expect as in both tables in row 2 there is key value 2 that matches)