# Group by Multi Column Dataframe to share ID? Maybe?

**URL:** <https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [October 13, 2022, 9:23pm UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692 "2022-10-13T21:23:54Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![theboiwholived](https://avatars.discourse-cdn.com/v4/letter/t/aeb1de/32.png) [@theboiwholived](https://discourse.julialang.org/u/theboiwholived)\
**Post date:** [October 13, 2022, 9:23pm UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692/1 "2022-10-13T21:23:54Z")

</div>

Hey ladies and gents!

I’ve been searching high and low for the best way to do a multi column groupby, i think.

Essentially i have a dataframe. with a multitude of columns but only 2 that matter in this contex.

I do a

```julia
g = groupby(df, :currencyA);
insertcols!(df, 1, :idA => g.groups)

```

which gets me a new column of id per currencyA column. something like

```julia
idA currencyA currency B
1 USD JPY
2 EUR USD
3 JPY EUR

```

I want to essentially create a second IdB column that follows the Id pattern generated from groupby of currencyA based on matching values in currencyB.

For example :

```julia
idA currencyA idB currency B
1 USD 3 JPY
2 EUR 1 USD
3 JPY 2 EUR

```

so IdB assigns the Id’s from A with the matching currency. Usd remains id 1 vs Eur remains id 2 etc. So i could track these currencies in whatever column by the same Id ref #.

if i were to groupby currencyB all over again japan would have in IdB a index of 1 and the others would follow sequentially.  
I need to be able to track the same Id value based on the value of the currency. It probably makes no sense why i need this but unfort i do 😕

REALLY appreciate the help. Been banging my head for a few days now.

---

<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:** [October 13, 2022, 9:28pm UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692/2 "2022-10-13T21:28:25Z")

</div>

I don’t understand. Whats wrong with grouping again?

---

<div class="post-metadata">

**Author:** ![digital\_carver](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/digital_carver/32/33818_2.png) [@digital\_carver](https://discourse.julialang.org/u/digital_carver)\
**Post date:** [October 13, 2022, 9:50pm UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692/3 "2022-10-13T21:50:04Z")

</div>

You can create a dictionary of currency to ID mapping outside of the dataframe:

```julia
julia> df = DataFrame(currencyA = rand(["USD", "EUR", "JPY"], 8), currencyB = rand(["USD", "EUR", "JPY"], 8))
8×2 DataFrame
 Row │ currencyA currencyB 
     │ String String    
─────┼──────────────────────
   1 │ JPY JPY
   2 │ USD JPY
   3 │ JPY JPY
   4 │ USD EUR
   5 │ JPY EUR
   6 │ EUR EUR
   7 │ USD USD
   8 │ JPY JPY

julia> curr_ids = Dict(map(enumerate(unique(df.currencyA))) do (idx, currency)
         currency => idx
       end)
Dict{String, Int64} with 3 entries:
  "EUR" => 3
  "JPY" => 1
  "USD" => 2

```

and then use that to generate the id columns in one go:

```julia
julia> transform(df, [:currencyA, :currencyB] .=> ByRow(c -> curr_ids[c]) .=> [:idA, :idB])
8×4 DataFrame
 Row │ currencyA currencyB idA idB   
     │ String String Int64 Int64 
─────┼────────────────────────────────────
   1 │ JPY JPY 1 1
   2 │ USD JPY 2 1
   3 │ JPY JPY 1 1
   4 │ USD EUR 2 3
   5 │ JPY EUR 1 3
   6 │ EUR EUR 3 3
   7 │ USD USD 2 2
   8 │ JPY JPY 1 1

```

---

<div class="post-metadata">

**Author:** ![theboiwholived](https://avatars.discourse-cdn.com/v4/letter/t/aeb1de/32.png) [@theboiwholived](https://discourse.julialang.org/u/theboiwholived)\
**Post date:** [October 14, 2022, 3:35am UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692/4 "2022-10-14T03:35:15Z")

</div>

Amazing ! Cheers legend, thank you so much ! 🙂

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [October 14, 2022, 7:20am UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692/5 "2022-10-14T07:20:56Z")

</div>

Just a complement to the nice solution above, which might be easier for some to read:

```julia
d = Dict(reverse.(enumerate(unique(df.currencyA))))
df.idA = [d[x] for x in df.currencyA]
df.idB = [d[x] for x in df.currencyB]

```

---

<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:** [October 14, 2022, 9:36am UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692/6 "2022-10-14T09:36:47Z")

</div>

using some internal …

```julia

df = DataFrame(cA = rand(["USD", "EUR", "JPY"], 8), cB = rand(["USD", "EUR", "JPY"], 8))

g=groupby(df,:cA)

df.idA=groupindices(g)

transform(df, :cB=>ByRow(r->g.keymap[tuple(r)])=>:idB)

```

Without paying too much attention to efficiency …

```julia
df.r=1:nrow(df)

df.idA=groupindices(groupby(sort!(df,:cA),:cA))

df.idB=groupindices(groupby(sort!(df,:cB),:cB))

sort!(df,:r)

```

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.julialang.org/u/jules)\
**Post date:** [October 14, 2022, 11:37am UTC](https://discourse.julialang.org/t/group-by-multi-column-dataframe-to-share-id-maybe/88692/7 "2022-10-14T11:37:04Z")

</div>

With [DataFrameMacros.jl](https://github.com/jkrumbiegel/DataFrameMacros.jl/) you can do both columns in one operation, it allows multi-column expressions.

```julia
julia> using DataFrames, DataFrameMacros

julia> df = DataFrame(
           currencyA = rand(["USD", "EUR", "JPY"], 8),
           currencyB = rand(["USD", "EUR", "JPY"], 8)
       )
8×2 DataFrame
 Row │ currencyA currencyB 
     │ String String    
─────┼──────────────────────
   1 │ JPY JPY
   2 │ USD USD
   3 │ USD JPY
   4 │ JPY USD
   5 │ EUR JPY
   6 │ JPY USD
   7 │ JPY USD
   8 │ JPY JPY

```

```julia
julia> d = Dict(reverse.(enumerate(unique(df.currencyA))))
Dict{String, Int64} with 3 entries:
  "EUR" => 3
  "JPY" => 1
  "USD" => 2

julia> @transform(df, [:idA, :idB] = d[{[:currencyA, :currencyB]}])
8×4 DataFrame
 Row │ currencyA currencyB idA idB   
     │ String String Int64 Int64 
─────┼────────────────────────────────────
   1 │ JPY JPY 1 1
   2 │ USD USD 2 2
   3 │ USD JPY 2 1
   4 │ JPY USD 1 2
   5 │ EUR JPY 3 1
   6 │ JPY USD 1 2
   7 │ JPY USD 1 2
   8 │ JPY JPY 1 1

```

Or even just:

```julia
julia> @transform(df, [:idA, :idB] = d[{All()}])
8×4 DataFrame
 Row │ currencyA currencyB idA idB   
     │ String String Int64 Int64 
─────┼────────────────────────────────────
   1 │ JPY JPY 1 1
   2 │ USD USD 2 2
   3 │ USD JPY 2 1
   4 │ JPY USD 1 2
   5 │ EUR JPY 3 1
   6 │ JPY USD 1 2
   7 │ JPY USD 1 2
   8 │ JPY JPY 1 1

```