# Comparing two lists

**URL:** <https://discourse.julialang.org/t/comparing-two-lists/61049>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [May 12, 2021, 10:59pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049 "2021-05-12T22:59:04Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 12, 2021, 10:59pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/1 "2021-05-12T22:59:04Z")

</div>

I have two lists that I need to compare. The second list contains names of groups for the first list:

```julia
using DataFrames
list1 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324P:CDCL","2324R:CDCL","5666TB:GHLA","789789TSB:ASLT","79067P:OPRA","79067R:OPRA"])
list2 = DataFrame(ID =["0001P:ABCD","0001R:ABCD","2324R:CDCL","5666TB:GHLA"], Pair = ["PairOne","PairOne","Two","Two"])
list_compare = leftjoin(list1,list2, on = :ID)

```

I use `leftjoin` to produce the group / pair name in the first list.

```julia
julia> list_compare
8×2 DataFrame
 Row │ ID Pair    
     │ String String? 
─────┼─────────────────────────
   1 │ 0001P:ABCD PairOne
   2 │ 0001R:ABCD PairOne
   3 │ 2324R:CDCL Two
   4 │ 5666TB:GHLA Two
   5 │ 2324P:CDCL missing 
   6 │ 789789TSB:ASLT missing 
   7 │ 79067P:OPRA missing 
   8 │ 79067R:OPRA missing 

```

From here, I want to produce list\_comapre2.

There is one complexity. There are some IDs (in the first list) which are related-they have same characters except that one of them contains `P:` and other `R:` in the ID. Examples are rows 1 & 2, 3 & 5 and 6 & 7. `list2` may specify group name for one of these but not necessarily for other e.g., ID 2324R:CDCL is part of `Pair` `Two` but it’s related ID 2323P:CDCL is not listed as under this group. I want to add another column `Pair_1` to the combined dataframe that adds group name for the ID missing from list 2, so that I end up with `list2_compare2`

```julia
list_compare2 = list_compare
list_compare2.Pair_1 = ["PairOne","PairOne","Two","Two","Two","789789TSB:ASLT","79067P:OPRA&79067R:OPRA","79067P:OPRA&79067R:OPRA"]
list_compare2.Pair_original = ["True","True","True","True","True","False","False","False"]

```

```julia
julia> list_compare2
8×4 DataFrame
 Row │ ID Pair Pair_1 Pair_original 
     │ String String? String String        
─────┼─────────────────────────────────────────────────────────────────
   1 │ 0001P:ABCD PairOne PairOne True
   2 │ 0001R:ABCD PairOne PairOne True
   3 │ 2324R:CDCL Two Two True
   4 │ 5666TB:GHLA Two Two True
   5 │ 2324P:CDCL missing Two True
   6 │ 789789TSB:ASLT missing 789789TSB:ASLT False
   7 │ 79067P:OPRA missing 79067P:OPRA&79067R:OPRA False
   8 │ 79067R:OPRA missing 79067P:OPRA&79067R:OPRA False

```

Summary:

- for all IDs, I want to add an entry in Pair\_1. if an ID containing P: or R: has a reported Pair, but not for both of them then I would like to add create one for which it is missing as explained above
- If an ID contains P: or R: and both of these are reported as missing in `Pair` column then I would like to create an entry for them by concatenating their IDs (e.g. rows 7 and 8 above).
- if an ID does not contain either P: or R: and Pair is reported as missing for it then its ID is the entry under Pair\_1.
- Final column set outs whether the pair existed in the original list.

I tried the following to get the relevant IDs, but I am confused on next steps.

```julia
p_list = filter(s->occursin(r"P:",s),list1.ID)
r_list = filter(s->occursin(r"R:",s),list1.ID)

```

Any views on how I can produce list\_compare2 from list1 and list2?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [May 12, 2021, 11:04pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/2 "2021-05-12T23:04:52Z")

</div>

> [@mjanun](#):
>
> ```julia
> list_compare2 = list_compare
> 
> ```

One note, this line isn’t doing what you probably think it’s doing. In Julia, assignment does not automatically copy, so you are still modifying `list_compare` when you add columns to `list_compare2`.

I’m not totally following what you want, unfortunately. I think I would do separate joins, one for a “strict” comparison and one where you have gotten rid of the `P:` and `R:`s that are causing problems.

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 12, 2021, 11:13pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/3 "2021-05-12T23:13:48Z")

</div>

I don’t want to get rid of `P:` and `R:`. If the above sound confusing, I can give on example on what I am trying to do. ID 2324R:CDCL has Pair listed Two under the Pair column. It has a related ID 2324P:CDCL which is not listed as Pair Two. I want to create an entry of Two in Pair\_1 column for 2324P:CDCL . So that I end up with Two in Pair column for both of them. Does that make sense?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [May 12, 2021, 11:16pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/4 "2021-05-12T23:16:22Z")

</div>

Yes. I’m not proposing deleting that information entirely. But if two things are a pseudo-match, then you should be able to, at least temporarily, transform the pseudo-matches so they are exact matches.

---

<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 17, 2021, 6:44am UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/5 "2021-05-17T06:44:56Z")

</div>

The rules to be applied are not precisely linear (perhaps setting the previous steps differently, it would not be necessary to do these stunts), but, waiting for a “clean” solution, maybe you can settle for a little patched one.

```julia
function pairing(s1,s2)
    if length(s1)==length(s2)
        idx=findall(x->x!=0,cmp.(collect(s1),collect(s2)))
        return Set([s1[idx],s2[idx]])
    else
        return Set([])
    end
end

list.twin=[[i, something(findfirst(x->x==1,[Set(["P","R"])==pairing(list1.ID[i],list1.ID[j]) for j in 1:8]),i)] for i in 1:8]
list.pair1=[t[2] != nothing ? coalesce(list.Pair[t[1]],list.Pair[t[2]]) : coalesce(list.Pair[t[1]],list.ID[t[1]]) for t in list.twin ]
list.pair_1=[coalesce(list.pair1[r],join(Set(list.ID[list.twin[r]])," & ")) for r in 1:8 ]

```

```julia
julia> list
8×5 DataFrame
 Row │ ID Pair twin pair1 pair_1
     │ String String? Array… String? String
─────┼─────────────────────────────────────────────────────────────────────
   1 │ 0001P:ABCD PairOne [1, 2] PairOne PairOne
   2 │ 0001R:ABCD PairOne [2, 1] PairOne PairOne
   3 │ 2324P:CDCL missing [3, 4] Two Two
   4 │ 2324R:CDCL Two [4, 3] Two Two
   5 │ 5666TB:GHLA Two [5, 5] Two Two
   6 │ 789789TSB:ASLT missing [6, 6] missing 789789TSB:ASLT
   7 │ 79067P:OPRA missing [7, 8] missing 79067P:OPRA & 79067R:OPRA
   8 │ 79067R:OPRA missing [8, 7] missing 79067P:OPRA & 79067R:OPRA

```

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 17, 2021, 11:00am UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/6 "2021-05-17T11:00:59Z")

</div>

Thank you. Unless I am missing something, but I don’t get the same results as the one you have shown. For `2324P:CDCL`, pair\_1 is showing up as `2324P:CDCL`

Here is the complete code:

```julia
using DataFrames
list1 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324P:CDCL","2324R:CDCL","5666TB:GHLA","789789TSB:ASLT","79067P:OPRA","79067R:OPRA"])
list2 = DataFrame(ID =["0001P:ABCD","0001R:ABCD","2324R:CDCL","5666TB:GHLA"], Pair = ["PairOne","PairOne","Two","Two"])

function pairing(s1,s2)
    if length(s1)==length(s2)
        idx=findall(x->x!=0,cmp.(collect(s1),collect(s2)))
        return Set([s1[idx],s2[idx]])
    else
        return Set([])
    end
end
list = leftjoin(list1,list2, on = :ID)
list.twin=[[i, something(findfirst(x->x==1,[Set(["P","R"])==pairing(list1.ID[i],list1.ID[j]) for j in 1:8]),i)] for i in 1:8]
list.pair1=[t[2] != nothing ? coalesce(list.Pair[t[1]],list.Pair[t[2]]) : coalesce(list.Pair[t[1]],list.ID[t[1]]) for t in list.twin ]
list.pair_1=[coalesce(list.pair1[r],join(Set(list.ID[list.twin[r]])," & ")) for r in 1:8 ]

```

```julia
julia> list
8×5 DataFrame
 Row │ ID Pair twin pair1 pair_1
     │ String String? Array… String? String
─────┼─────────────────────────────────────────────────────────────────────
   1 │ 0001P:ABCD PairOne [1, 2] PairOne PairOne
   2 │ 0001R:ABCD PairOne [2, 1] PairOne PairOne
   3 │ 2324R:CDCL Two [3, 4] Two Two
   4 │ 5666TB:GHLA Two [4, 3] Two Two
   5 │ 2324P:CDCL missing [5, 5] missing 2324P:CDCL
   6 │ 789789TSB:ASLT missing [6, 6] missing 789789TSB:ASLT
   7 │ 79067P:OPRA missing [7, 8] missing 79067P:OPRA & 79067R:OPRA
   8 │ 79067R:OPRA missing [8, 7] missing 79067P:OPRA & 79067R:OPRA

```

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [May 17, 2021, 11:01am UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/7 "2021-05-17T11:01:34Z")

</div>

Nice challenge 🙂 Here’s another solution.

```julia
using DataFrames

list1 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324P:CDCL","2324R:CDCL","5666TB:GHLA","789789TSB:ASLT","79067P:OPRA","79067R:OPRA"])
list2 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324R:CDCL","5666TB:GHLA"], Pair=["PairOne","PairOne","Two","Two"])

l = leftjoin(list1, list2, on=:ID, source=:Pair_original)

```

We define one helper function for parsing the IDs:

```julia
function parse_id(id)
    i = findfirst(':', id)
    return (type=id[[i-1]], untyped=id[1:i-2] * '_' * id[i:end])
end

julia> l2 = transform(l, :ID => ByRow(parse_id) => AsTable,
                         :Pair_original => c->c.=="both", renamecols=false)
8×5 DataFrame
 Row │ ID Pair Pair_original type untyped        
     │ String String? Bool String String         
─────┼────────────────────────────────────────────────────────────────
   1 │ 0001P:ABCD PairOne true P 0001_:ABCD
   2 │ 0001R:ABCD PairOne true R 0001_:ABCD
   3 │ 2324R:CDCL Two true R 2324_:CDCL
   4 │ 5666TB:GHLA Two true B 5666T_:GHLA
   5 │ 2324P:CDCL missing false P 2324_:CDCL
   6 │ 789789TSB:ASLT missing false B 789789TS_:ASLT
   7 │ 79067P:OPRA missing false P 79067_:OPRA
   8 │ 79067R:OPRA missing false R 79067_:OPRA

```

and one for choosing the pair value using data from all relevant rows:

```julia
make_pair(ids, pairs) = coalesce(pairs..., join(ids, " & "));

julia> select(groupby(l2, :untyped), :ID, :Pair_original,
              [:ID, :Pair] => make_pair => :Pair, keepkeys=false)
8×3 DataFrame
 Row │ ID Pair_original Pair                      
     │ String Bool String                    
─────┼──────────────────────────────────────────────────────────
   1 │ 0001P:ABCD true PairOne
   2 │ 0001R:ABCD true PairOne
   3 │ 2324R:CDCL true Two
   4 │ 5666TB:GHLA true Two
   5 │ 2324P:CDCL false Two
   6 │ 789789TSB:ASLT false 789789TSB:ASLT
   7 │ 79067P:OPRA false 79067P:OPRA & 79067R:OPRA
   8 │ 79067R:OPRA false 79067P:OPRA & 79067R:OPRA

```

The `parse_id` function stores the “type” character (`R`/`P`/`B`) in a column but it’s not used actually, so you could simplify the function to just return `(untyped=id[1:i-2] * '_' * id[i:end],)`.

Edit: the solution above assigns the same “pair” to all related IDs. If it is possible that related IDs have pairs that are different (not missing), you can adapt `make_pairs` like this:

```julia
make_pair(ids, pairs) = coalesce.(pairs, pairs..., join(ids, " & "))

```

---

<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 17, 2021, 12:42pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/8 "2021-05-17T12:42:39Z")

</div>

![cmp_list](https://global.discourse-cdn.com/julialang/original/3X/3/6/3638e8a83509aa1c736a75a32b5bd542a04a607a.png)

I’m sorry but I can’t reproduce your result.  
This is what I get by copying and pasting the code used by you.  
Try starting from a clean situation, restarting Julia.

Even if list1 is not sorted, the result appears to be as expected. Or not?

 ![cmp_list_not_sorted](https://global.discourse-cdn.com/julialang/original/3X/9/8/98242cc24c9b12e5845900cad28a9df5583e4872.png)

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 17, 2021, 4:23pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/9 "2021-05-17T16:23:27Z")

</div>

I have checked on a fresh Julia session, and I still get different results. My version of DataFrames may be different from yours which may result in different results

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 17, 2021, 4:24pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/10 "2021-05-17T16:24:31Z")

</div>

Thank you

> [@sijo](#):
>
> `(type=id[[i-1]], untyped=id[1:i-2] * '_' * id[i:end])`

I do not understand what this part of the code is doing. Could you please explain?

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [May 17, 2021, 4:44pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/11 "2021-05-17T16:44:40Z")

</div>

It’s creating a named tuple. You can execute each part independently to see what it’s doing:

```julia
julia> id = "0001P:ABCD";

julia> i = findfirst(':', id)
6

julia> id[[i-1]]
"P"

julia> id[1:i-2]
"0001"

julia> id[i:end]
":ABCD"

julia> (type=id[[i-1]], untyped=id[1:i-2] * '_' * id[i:end])
(type = "P", untyped = "0001_:ABCD")

```

Some remarks:

- I used `id[[i-1]]` instead of `id[i-1]` to get a string `"P"` rather than a char `'P'` (I needed a string when experimenting with another solution, but in the present solution this `type` value is not used so it doesn’t matter).

- `*` is the concatenation operator for strings:

- The last line is making the named tuple, same syntax as `(a=1, b=2)`.

---

<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 17, 2021, 5:00pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/12 "2021-05-17T17:00:20Z")

</div>

> [@mjanun](#):
>
> I have checked on a fresh Julia session, and I still get different results. My version of DataFrames may be different from yours which may result in different results

try this

```julia
list1 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324P:CDCL","5666TB:GHLA","789789TSB:ASLT","2324R:CDCL","79067P:OPRA","79067R:OPRA"])
list2 = DataFrame(ID =["0001P:ABCD","0001R:ABCD","2324R:CDCL","5666TB:GHLA"], Pair = ["PairOne","PairOne","Two","Two"])

function pairing(s1,s2)
    if length(s1)==length(s2)
        idx=findall(x->x!=0,cmp.(collect(s1),collect(s2)))
        return Set([s1[idx],s2[idx]])
    else
        return Set([])
    end
end
list = leftjoin(list1,list2, on = :ID)
list.twin=[[i, something(findfirst(x->x==1,[Set(["P","R"])==pairing(list.ID[i],list.ID[j]) for j in 1:8]),i)] for i in 1:8]
list.pair1=[coalesce(list.Pair[t[1]],list.Pair[t[2]]) for t in list.twin ]
list.pair_1=[coalesce(list.pair1[r],join(Set(list.ID[list.twin[r]])," & ")) for r in 1:8 ]

```

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 17, 2021, 5:14pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/13 "2021-05-17T17:14:38Z")

</div>

> [@rocco\_sprmnt21](#):
>
> ```julia
> list1 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324P:CDCL","5666TB:GHLA","789789TSB:ASLT","2324R:CDCL","79067P:OPRA","79067R:OPRA"])
> list2 = DataFrame(ID =["0001P:ABCD","0001R:ABCD","2324R:CDCL","5666TB:GHLA"], Pair = ["PairOne","PairOne","Two","Two"])
> 
> function pairing(s1,s2)
> if length(s1)==length(s2)
> idx=findall(x->x!=0,cmp.(collect(s1),collect(s2)))
> return Set([s1[idx],s2[idx]])
> else
> return Set([])
> end
> end
> list = leftjoin(list1,list2, on = :ID)
> list.twin=[[i, something(findfirst(x->x==1,[Set(["P","R"])==pairing(list.ID[i],list.ID[j]) for j in 1:8]),i)] for i in 1:8]
> list.pair1=[coalesce(list.Pair[t[1]],list.Pair[t[2]]) for t in list.twin ]
> list.pair_1=[coalesce(list.pair1[r],join(Set(list.ID[list.twin[r]])," & ")) for r in 1:8 ]
> 
> ```

Thanks. This works

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 17, 2021, 10:42pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/14 "2021-05-17T22:42:38Z")

</div>

Thanks for the explanation. I just noticed that it is concatenating some IDs which do not contain `P:` or `R:` on another dataset. If you have two IDs that have most characters same except one e.g. `222367_1:B421_GHL` and `222367_2:B421_GHL` then it creates a pair  
`222367_1:B421_GHL & 222367_2:B421_GHL`

Is there anything I am doing incorrectly?

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [May 18, 2021, 12:47am UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/15 "2021-05-18T00:47:59Z")

</div>

This is another solution: (your last column is not clear since for example `2324P:CDCL` was not in original data but it is marked as `true`)

```julia
# list2 ID and Pair
ref = Dict(list2[!, 1] .=> list2[!, 2])

# rules:
contain_PR(x) = occursin("P:", x) || occursin("R:", x)
change_PR(x) = occursin("P:", x) ? replace(x, "P:"=>"R:") : replace(x, "R:"=>"P:")
concat_them(x) = occursin("P:", x) ? x*change_PR(x) : change_PR(x)*x
function compute_pair_1(x, y; ref = ref)
    if ismissing(y) && contain_PR(x)
        return get(ref, change_PR(x), concat_them(x))
    elseif ismissing(y)
        return x
    else
        return y
    end
end

# apply the function
transform(list_compare, [:ID, :Pair] => ((x,y) -> compute_pair_1.(x, y, ref = ref)) => :Pair_1)

```

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [May 18, 2021, 6:43am UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/16 "2021-05-18T06:43:43Z")

</div>

> [@mjanun](#):
>
> I just noticed that it is concatenating some IDs which do not contain `P:` or `R:` on another dataset.

Yes, I was not sure what was desired in this case. Here’s a version that gives special treatment only for `P` and `R`:

```julia
using DataFrames
list1 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324P:CDCL","2324R:CDCL","5666TB:GHLA","789789TSB:ASLT","79067P:OPRA","79067R:OPRA"])
list2 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324R:CDCL","5666TB:GHLA"], Pair=["PairOne","PairOne","Two","Two"])

l = leftjoin(list1, list2, on=:ID, source=:source)
l.Pair_original = l.source .== "both"

function id_family(id)
    i = findfirst(':', id)
    if id[i-1] in "PR"
        return id[1:i-2] * '_' * id[i:end]
    else
        return id
    end
end

l.family = id_family.(l.ID)

make_pair(ids, pairs) = coalesce.(pairs, pairs..., join(ids, " & "))

select(groupby(l, :family), :ID, :Pair_original,
       [:ID, :Pair] => make_pair => :Pair, keepkeys=false)

```

---

<div class="post-metadata">

**Author:** ![mjanun](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mjanun](https://discourse.julialang.org/u/mjanun)\
**Post date:** [May 18, 2021, 4:49pm UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/17 "2021-05-18T16:49:56Z")

</div>

Thank you

> [@DataFrames](#):
>
> (your last column is not clear since for example `2324P:CDCL` was not in original data but it is marked as `true` )

`2324P:CDCL` is missing as under Pair, but `2324R:CDCL` has `Two` under Pair. If only one of the related ID has a reported Pair then for the other that one automatically applies. All IDs which have same characters except `P:` or `R:` are related.

Your code is not creating all the pairs of `P:` and `R:` where both of them are reported as missing under Pair

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [May 19, 2021, 2:23am UTC](https://discourse.julialang.org/t/comparing-two-lists/61049/18 "2021-05-19T02:23:28Z")

</div>

Make sure that you didn’t miss any code (“.” are important)

```julia
using DataFrames
list1 = DataFrame(ID=["0001P:ABCD","0001R:ABCD","2324P:CDCL","2324R:CDCL","5666TB:GHLA","789789TSB:ASLT","79067P:OPRA","79067R:OPRA"])
list2 = DataFrame(ID =["0001P:ABCD","0001R:ABCD","2324R:CDCL","5666TB:GHLA"], Pair = ["PairOne","PairOne","Two","Two"])
list_compare = leftjoin(list1,list2, on = :ID)

```

```julia
# list2 ID and Pair
ref = Dict(list2[!, 1] .=> list2[!, 2])

# rules:
contain_PR(x) = occursin("P:", x) || occursin("R:", x)
change_PR(x) = occursin("P:", x) ? replace(x, "P:"=>"R:") : replace(x, "R:"=>"P:")
concat_them(x) = occursin("P:", x) ? x*change_PR(x) : change_PR(x)*x
function compute_pair_1(x, y; ref = ref)
    if ismissing(y) && contain_PR(x)
        return get(ref, change_PR(x), concat_them(x))
    elseif ismissing(y)
        return x
    else
        return y
    end
end
function compute_original(x, y; ref = ref)
       if ismissing(y)
            contain_PR(x) && haskey(ref, change_PR(x)) ? true : false
       else
            return true
        end
end

# apply the function
transform(list_compare, 
                 [:ID, :Pair] => ((x,y) -> compute_pair_1.(x, y, ref = ref)) => :Pair_1, 
                 [:ID, :Pair] => ((x,y) -> compute_original.(x, y, ref = ref)) => :original)

```

```julia
8×4 DataFrame
 Row │ ID Pair Pair_1 original 
     │ String String? String Bool     
─────┼───────────────────────────────────────────────────────────
   1 │ 0001P:ABCD PairOne PairOne true
   2 │ 0001R:ABCD PairOne PairOne true
   3 │ 2324R:CDCL Two Two true
   4 │ 5666TB:GHLA Two Two true
   5 │ 2324P:CDCL missing Two true
   6 │ 789789TSB:ASLT missing 789789TSB:ASLT false
   7 │ 79067P:OPRA missing 79067P:OPRA79067R:OPRA false
   8 │ 79067R:OPRA missing 79067P:OPRA79067R:OPRA false

```