# Comparison 2 data sets

**URL:** <https://discourse.julialang.org/t/comparison-2-data-sets/78790>\
**Category:** Finance and Economics\
**Tags:** testing, data\
**Created:** [March 31, 2022, 9:38am UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790 "2022-03-31T09:38:22Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ab2z](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ab2z/32/34893_2.png) [@ab2z](https://discourse.julialang.org/u/ab2z)\
**Post date:** [March 31, 2022, 9:38am UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790/1 "2022-03-31T09:38:22Z")

</div>

Hi all,  
I am new in Julia so I would like to start with a simple question.  
In testing the migration activities from old to a new system/setting/etc, we mostly need to compare 2 output data sets and extract the deltas.  
Question: what is the best command for comparing 2 datasets - something like proc compare in SAS ?

Thanks+regards

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 31, 2022, 10:37am UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790/2 "2022-03-31T10:37:59Z")

</div>

It would be helpful if you could provide a bit more detail on what you want to happen - I’m not familiar with SAS. Something like:

```julia
julia> using DataFrames

julia> df1 = DataFrame(rand(1:5, 10, 5), :auto); df2 = DataFrame(rand(1:5, 10, 5), :auto)
10×5 DataFrame
 Row │ x1 x2 x3 x4 x5
     │ Int64 Int64 Int64 Int64 Int64
─────┼───────────────────────────────────
   1 │ 4 5 1 3 3
   2 │ 1 4 1 3 1
   3 │ 5 2 3 4 3
   4 │ 2 2 3 3 5
   5 │ 1 4 4 2 1
   6 │ 4 3 1 2 1
   7 │ 5 3 5 1 1
   8 │ 4 3 3 4 3
   9 │ 4 2 4 3 2
  10 │ 1 4 4 2 2

julia> df1 .== df2
10×5 DataFrame
 Row │ x1 x2 x3 x4 x5
     │ Bool Bool Bool Bool Bool
─────┼───────────────────────────────────
   1 │ false true true true false
   2 │ false false true true true
   3 │ false true false false false
   4 │ false true false false false
   5 │ false true false false true
   6 │ false false true true false
   7 │ false false false false false
   8 │ true false false true false
   9 │ false false false false false
  10 │ false true false false false

julia> df1 .- df2
10×5 DataFrame
 Row │ x1 x2 x3 x4 x5
     │ Int64 Int64 Int64 Int64 Int64
─────┼───────────────────────────────────
   1 │ -2 0 0 0 -2
   2 │ 3 -2 0 0 0
   3 │ -3 0 -2 1 1
   4 │ 3 0 1 -2 -2
   5 │ 2 0 -2 -1 0
   6 │ -2 2 0 0 2
   7 │ -2 1 -4 4 4
   8 │ 0 2 -1 0 -1
   9 │ -1 -1 -1 -1 2
  10 │ 1 0 -2 2 1

```

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [March 31, 2022, 12:31pm UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790/3 "2022-03-31T12:31:30Z")

</div>

Assuming you are talking about arrays or simple tables, a solution is to use `DeepDiffs.jl`:

```julia
julia> tbl_x = [(a=1, b=2), (a=3, b=4)]
julia> tbl_y = [(a=1, b=3), (a=3, b=4)]
julia> using Tables, DeepDiffs
julia> map(deepdiff, tbl_x |> columntable, tbl_y |> columntable)

```

gives the following colored output:  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/5/8/581c32319ebe8322b4b9235ecd4853f05f289ea3.png)

---

<div class="post-metadata">

**Author:** ![ab2z](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ab2z/32/34893_2.png) [@ab2z](https://discourse.julialang.org/u/ab2z)\
**Post date:** [March 31, 2022, 9:06pm UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790/4 "2022-03-31T21:06:36Z")

</div>

I have the following example

old=Dataset(Insurance\_Id=[1,2,3],Business\_Id=[10,20,30],Amount=[100,200,300])

new=Dataset(Ins\_Id=[1,3,2,4,3,2],B\_Id=[10,40,30,40,30,20],AMT=[100,200,300,-500,350,700])

The combination of insurance\_id\*Business\_Id gives a unique id.

The delta is on the amount absolute difference greater than 50.

The only matches here are (insurance\_id,Business\_Id) in {(1,10),(3,30)} so I expect to have all other combinations.

---

<div class="post-metadata">

**Author:** ![Frankiewaang](https://avatars.discourse-cdn.com/v4/letter/f/f9ae1b/32.png) [@Frankiewaang](https://discourse.julialang.org/u/Frankiewaang)\
**Post date:** [April 1, 2022, 1:06pm UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790/5 "2022-04-01T13:06:22Z")

</div>

My initial thought is to use innerjoin on `[:Insurance_Id => Ins_Id, :Business_Id=>:B_Id]`(using DataFrames.jl) and then compare the two amounts using filter function that are implemented in `TableOperations.jl` or other table packages.  
(I think you would like to post this in `data` topic, as this is something related to table operation, but one function to compare two tables with different shapes seems no exist right now)

---

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [April 1, 2022, 1:41pm UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790/6 "2022-04-01T13:41:07Z")

</div>

Please include code inside of backticks (```) so that it’s easier to read. Here’s how to do what Frankiewaang suggested:

```julia
using DataFrames

old = DataFrame(Insurance_Id = [1,2,3], Business_Id = [10,20,30], Amount = [100,200,300])

new = DataFrame(Ins_Id = [1,3,2,4,3,2], B_Id = [10,40,30,40,30,20], AMT = [100,200,300,-500,350,700])

joined = innerjoin(old, new, on=[:Insurance_Id => :Ins_Id, :Business_Id => :B_Id])

diffs = filter(row -> abs(row.Amount - row.AMT) > 50, joined)

1×4 DataFrame
 Row │ Insurance_Id Business_Id Amount AMT   
     │ Int64 Int64 Int64 Int64 
─────┼──────────────────────────────────────────
   1 │ 2 20 200 700

```

---

<div class="post-metadata">

**Author:** ![ab2z](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ab2z/32/34893_2.png) [@ab2z](https://discourse.julialang.org/u/ab2z)\
**Post date:** [April 5, 2022, 8:38am UTC](https://discourse.julialang.org/t/comparison-2-data-sets/78790/7 "2022-04-05T08:38:20Z")

</div>

I can not edit my post and put the code in backticks. maybe next time.