# Intersection of two dataframes based on columns
**URL:** https://discourse.julialang.org/t/intersection-of-two-dataframes-based-on-columns/31421
**Category:** General Usage
**Created:** [November 23, 2019, 2:23pm UTC](https://discourse.julialang.org/t/intersection-of-two-dataframes-based-on-columns/31421 "2019-11-23T14:23:27Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)
#### Post date: [November 23, 2019, 2:23pm UTC](https://discourse.julialang.org/t/intersection-of-two-dataframes-based-on-columns/31421/1 "2019-11-23T14:23:27Z")
Hi all-
I have two dataframes in which one is a subset of the other based on the values in some columns. For example,
```julia
using DataFrames
df1 = DataFrame(a=[1,1,2,2,3,3], b=[1,2,1,2,1,2], c=rand(6))
df2 = DataFrame(a=[1,1,2,2], b=[1,2,1,2], c=rand(4))
```
df2 is a subset of df1 based on shared values in columns a and b:
```julia
julia> df1
6×3 DataFrame
│ Row │ a │ b │ c │
│ │ Int64 │ Int64 │ Float64 │
├─────┼───────┼───────┼──────────┤
│ 1 │ 1 │ 1 │ 0.448961 │
│ 2 │ 1 │ 2 │ 0.858127 │
│ 3 │ 2 │ 1 │ 0.97272 │
│ 4 │ 2 │ 2 │ 0.655589 │
│ 5 │ 3 │ 1 │ 0.723655 │
│ 6 │ 3 │ 2 │ 0.426203 │
julia> df2
4×3 DataFrame
│ Row │ a │ b │ c │
│ │ Int64 │ Int64 │ Float64 │
├─────┼───────┼───────┼────────────┤
│ 1 │ 1 │ 1 │ 0.498987 │
│ 2 │ 1 │ 2 │ 0.813332 │
│ 3 │ 2 │ 1 │ 0.566679 │
│ 4 │ 2 │ 2 │ 0.00879591 │
```
What I would like to do is extract the subset of df1 that intersects with d2 on columns a and b, yielding the following new dataframe:
```julia
julia> df3
4×3 DataFrame
│ Row │ a │ b │ c │
│ │ Int64 │ Int64 │ Float64 │
├─────┼───────┼───────┼──────────┤
│ 1 │ 1 │ 1 │ 0.448961 │
│ 2 │ 1 │ 2 │ 0.858127 │
│ 3 │ 2 │ 1 │ 0.97272 │
│ 4 │ 2 │ 2 │ 0.655589 │
```
Is there a general way to achieve this result when given two dataframes and a vector of columns, e.g. `extract(df1, df2, [:a, :b])`
---
### Author:  [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)
#### Post date: [November 23, 2019, 3:26pm UTC](https://discourse.julialang.org/t/intersection-of-two-dataframes-based-on-columns/31421/2 "2019-11-23T15:26:11Z")
Short answer as I am on mobile, but what you want is “join”:
`fullDf = join(df1, df2, on = :commonCol)` : Join dataframes horizontally. The on parameter can also be an array of common columns. There are many possible types of join , the most common ones are:
- `:inner` (default, only rows with keys on both sides are returned),
- `:left` (additionally, rows on the left df with keys not present on the df on the right are also returned),
- `:right` (opposite of :left ),
- `:outer` (rows with elements missing in any of the two df are also returned);
From my [https://julia-book.com](https://julia-book.com)
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [November 23, 2019, 3:27pm UTC](https://discourse.julialang.org/t/intersection-of-two-dataframes-based-on-columns/31421/3 "2019-11-23T15:27:13Z")
I think you’re just looking for an inner join:
```julia
julia> join(df1, df2[!, [:a, :b]], on = [:a, :b], kind = :inner)
4×3 DataFrame
│ Row │ a │ b │ c │
│ │ Int64 │ Int64 │ Float64 │
├─────┼───────┼───────┼──────────┤
│ 1 │ 1 │ 1 │ 0.069781 │
│ 2 │ 1 │ 2 │ 0.317998 │
│ 3 │ 2 │ 1 │ 0.384543 │
│ 4 │ 2 │ 2 │ 0.445616 │
```
Edit: beaten by a second 🙂
---
### Author:  [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)
#### Post date: [November 23, 2019, 5:39pm UTC](https://discourse.julialang.org/t/intersection-of-two-dataframes-based-on-columns/31421/4 "2019-11-23T17:39:23Z")
Thank you guys for the solution and thank you syvitcus for the in depth explanation.
I see now where I went wrong. Among my various failed attempts, I tried
`df4 = join(df1, df2, on = [:a, :b], kind = :inner, makeunique=true)`
which produced an unnecessary column because I didn’t think of using `df2[!, [:a, :b]]`
---
### Author:  [@alex-s-gardner](https://discourse.julialang.org/u/alex-s-gardner)
#### Post date: [July 14, 2024, 10:01pm UTC](https://discourse.julialang.org/t/intersection-of-two-dataframes-based-on-columns/31421/5 "2024-07-14T22:01:17Z")
`kind` is no longer a supported key word argument. Updated solution is:
```julia
julia> innerjoin(df1, df2[!, [:a, :b]], on = [:a, :b])
4×3 DataFrame
Row │ a b c
│ Int64 Int64 Float64
─────┼─────────────────────────
1 │ 1 1 0.142809
2 │ 1 2 0.0343224
3 │ 2 1 0.692197
4 │ 2 2 0.454196
```
see: [Joins · DataFrames.jl](https://dataframes.juliadata.org/stable/man/joins/)