Add `matchmissing = :notequal` option

[nilshg]

Consider two DataFrames:

julia> df = DataFrame(a = [1, 2, missing, missing])
4×1 DataFrame
 Row │ a       
     │ Int64?  
─────┼─────────
   1 │       1
   2 │       2
   3 │ missing 
   4 │ missing 

julia> df2 = DataFrame(a = [1, 3, missing, missing], b = rand(4))
4×2 DataFrame
 Row │ a        b        
     │ Int64?   Float64  
─────┼───────────────────
   1 │       1  0.459054
   2 │       3  0.649346
   3 │ missing  0.875563
   4 │ missing  0.709856

currently to join them I would need to set matchmissing = :equal, which produces duplicates:

julia> leftjoin(df, df2, on = :a, matchmissing = :equal)
6×2 DataFrame
 Row │ a        b
     │ Int64?   Float64?       
─────┼─────────────────────────
   1 │       1        0.459054
   2 │ missing        0.875563
   3 │ missing        0.875563
   4 │ missing        0.709856
   5 │ missing        0.709856
   6 │       2  missing     

I would like an option matchmissing = :ignore (or whatever other name) that preserves the left table exactly, and only adds information on the right side where non-missing values match. Currently I think this would be achieved via

julia> leftjoin(df, dropmissing(df2), on = :a, matchmissing = :equal)
4×2 DataFrame
 Row │ a        b
     │ Int64?   Float64?       
─────┼─────────────────────────
   1 │       1        0.459054
   2 │       2  missing        
   3 │ missing  missing        
   4 │ missing  missing       

which is a bit counterintuitive from an API perspective (I need to set matchmissing to :equal even though I don't want to match missings!), and might also suboptimal from an efficiency perspective.

[bkamins]

I think we should add it.

[pstorozenko]

I believe
leftjoin(df, df2, on = :a, matchmissing = :ignore)
should work like
leftjoin(df, dropmissing(df2, :a), on = :a, matchmissing = :equal)
instead of proposed
leftjoin(df, dropmissing(df2), on = :a, matchmissing = :equal)
right?
It seems not working properly for df2 with missings in b column.

How about other types of join?
Rightjoin is symmetric to leftjoin.
With inner and outer I think we should drop missings on key in both dataframes.
With anti and semi, we should only drop missings in df2.

What do you think?

[bkamins]

dropmissing(df2, :a) is a correct way to write it. I think that @nilshg just made a typo.

Regarding other joins - what you propose makes sense. However, we need to make sure that these rules are easy to communicate as the following is also feasible:

leftjoin and rightjoin - make sense as they promise not to touch left or right table respectively.
innerjoin - makes sense - as then we say (consistently with left and right join above) that missing is never considered a match to missing but does not error.
outerjoin is tricky - the other approach to your would be to give the following logic: outerjoin promises to keep all rows from both tables; this means that we cannot do dropmissing; then row with missing in key would be "no-match" but still included (but this is not a super nice solution); the question is if this would be useful; we can always say that :ignore is not supported by outerjoin and decide later what we prefer.
semijoin - makes sense; missing is dropped as it does not match df2
antijoin - makes sense; missing is retained as it does not match `df2.

So in summary - the problematic case is outerjoin, but as commented above we can skip support of :ignore in this case to avoid user confusion.

And the rules would be:

• :equal => assume that missing == missing is true;
• :ignore => assume that missing == missing is false;

[pstorozenko]

This is my very first contribution to any open source project (as well as developing pkg for julia) so I don't know how to share my findings but I'll give it a try.
I've implemented :ignore by passing kind variable to DataFrameJoiner and adding the following to the DataFrameJoiner constructor:

elseif matchmissing === :ignore
    if kind in [:left, :semi, :anti]
        dfr = dropmissing(dfr, right_on)
        dfr_on = dfr[!, right_on]
    elseif kind === :right
        dfl = dropmissing(dfl, left_on)
        dfl_on = dfl[!, left_on]
    elseif kind === :inner
        dfl = dropmissing(dfl, left_on)
        dfl_on = dfl[!, left_on]
        dfr = dropmissing(dfr, right_on)
        dfr_on = dfr[!, right_on]
    elseif kind === :outer 
        throw("matchmissing == :ignore with kind == :outer not allowed")
    else
        throw("matchmissing == :ignore not implemented for kind == $kind")
    end

I don't like solution with adding computation part to constructor but at least matchmissing logic is being kept in one place.

I was afraid that creating dataframes copies (by dropmissing) may blowup memory usage but I was unable to show it in benchmarks.
I've tried setting view=true but it was slower and used more memory probably due to this.
Benchmark is attached in pdf.
Ignorematch.pdf
@bkamins As you are the major contributor to joins, please tell my what do you think about this approach and I'll add tests, docs if you think it's acceptable.

[bkamins]

Thank you for working on this.

Throwing error should be:

throw(ArgumentError("matchmissing == :ignore with kind == :outer not allowed"))

but this is a minor thing.

I did not implement this PR immediately exactly because making it fast and not memory greedy requires benchmarking. My initial idea was:

1. use completecases instead of dropmissing and store the selector.
2. then materialize (as in your code) only dfr_on and dfl_on (as needed) with dropped cases
3. finally when composing the final table apply completecases to dfr and dfl along with the join condition

But this is complex.

Actually your proposal seems better, but I would do:

dfr = dropmissing(dfr, right_on, view=true)
dfr_on = select(dfr, right_on)

in this way dfr is a view (so it does not allocate much), but dfr_on is materialized (and most likely it is a narrow table). This should be both memory efficient (as materializing dfr can be prohibitive if this table is very wide) but at the same time fast to join (as only dfr_on is used in joining logic).

Could you please benchmark it and let me know.

One small comment. The code dfr[!, right_on] is correct but is unsafe. I preferred select over getindex as it clearly signals that we want to get a data frame (as dfr[!, right_on] could produce a vector; in this case it is ok as right_on is guaranteed to be a vector but in the future it might change so it is safer to use select which always produces a data frame).

[pstorozenko]

For some reason it is always more efficient to do

dfr = dropmissing(dfr, right_on)
dfr_on = select(dfr, right_on, copycols=false)

then

dfr = dropmissing(dfr, right_on, view=true)
dfr_on = select(dfr, right_on)

Benchmark here
Ignorematch_select.pdf
I should add that there was always quite big variance in running times so sometimes matchmissing = :error appears slower then :ignore.
Do you have any specific benchmark scenarios in mind to check?

[bkamins]

This is super strange. I am getting different results of the core part that makes the difference between both options:

julia> df = DataFrame(rand(10^6, 5), :auto);

julia> idx = rand(1:10^6, 10^6);

julia> @btime dropmissing($df)[$idx, [1,2,3,4]];
  51.561 ms (99 allocations: 76.44 MiB)

julia> @btime dropmissing($df, view=true)[$idx, [1,2,3,4]];
  48.648 ms (75 allocations: 45.92 MiB)

and using a view is a bit faster (but this is not the part we are fighting for) and allocates much less (this is what we care about)

The difference is much bigger for wide tables:

julia> df = DataFrame(rand(10^6, 100), :auto);

julia> idx = rand(1:10^6, 10^6);

julia> @btime dropmissing($df)[$idx, 1:99];
  1.210 s (1522 allocations: 1.49 GiB)

julia> @btime dropmissing($df, view=true)[$idx, 1:99];
  963.472 ms (1019 allocations: 771.13 MiB)

[bkamins]

maybe open a PR with your changes and then I will profile both options? (or maybe you know how to do profiling to identify the source of the performance loss then please check and let me know)

Thank you very much for working on this.

[pstorozenko]

I wasn't interpolating dataframes in @btime, I reran experiments and results are the same, less allocs but more memory.

As this change is not very important/urgent right now, I'll try profiling myself to learn how to do it in julia and will let you know results later.

[bkamins]

Sure. The release timing is:

1. DataFrames.jl 1.0: April 2021
2. DataFrames.jl 1.1: late summer 2021

but I did not assume to have this feature in 1.0 release so there is no rush.