Julia Dataframes Groupby chain using combine

Question

i'm trying to do the following :

For each columns i want to groupby the key with the column , count the number of occurence and keep only the biggest occurence by key (i don't want to keep the amount of occurence , just the value that correspond to it ). I have many columns to groupby with keys in a row and i wanted to know if there is a way to chain it together.

Here is an example :

│ Row      │ KEY                              │ A             │
│          │ String                           │ String        │
├──────────┼──────────────────────────────────┼───────────────┤
│ 1        │ 44473                            │ ROCK          │
│ 2        │ 4f4ef                            │ CLASSICAL     │
│ 3        │ 0b8bd                            │ POP           │
│ 4        │ 57c94                            │ POP           │
│ 5        │ a7070                            │ RAP - HIP HOP │
│ 6        │ 1d9a3                            │ JAZZ          │
│ 7        │ 947fd                            │ POP           │

Here i do :

 per_key =  DataFrames.groupby(test, [:KEY, :A])
 combine(per_key, nrow => :A)

which gives me :

│ Row  │ KEY                              │ A                  │ nrow  │
│      │ String                           │ String             │ Int64 │
├──────┼──────────────────────────────────┼────────────────────┼───────┤
│ 1    │ 44473ff                          │ ROCK               │ 2     │
│ 2    │ 4f4effc                          │ CLASSICAL          │ 12    │
│ 3    │ 0b8bd64                          │ POP                │ 2     │
│ 4    │ 57c94f5                          │ POP                │ 2     │
│ 5    │ a7070e4                          │ RAP - HIP HOP      │ 1     │
│ 6    │ 1d9a3c7                          │ JAZZ               │ 1     │

How do i do for each KEY , get the max "nrow" and keep the corresponding value in "A".

I have to do it with many other columns one by one also.

Thank you

Bogumił Kamiński Bogumił Kamiński · Accepted Answer · 2020-10-05T16:08:42

I am not 100% what you wanted, but I assume this is the thing that you are looking for:

julia> using DataFrames, StatsBase

julia> df = DataFrame(key=rand(1:10, 10^6),
                      A = rand(1:10, 10^6),
                      B = rand(1:10, 10^6),
                      C = rand(1:10, 10^6));

julia> gdf = groupby(df, :key);

julia> combine(gdf, valuecols(gdf) .=>
                    (x -> last(maximum(reverse, countmap(x)))) .=>
                    valuecols(gdf))
10×4 DataFrame
│ Row │ key   │ A     │ B     │ C     │
│     │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┼───────┤
│ 1   │ 1     │ 8     │ 8     │ 1     │
│ 2   │ 10    │ 1     │ 2     │ 9     │
│ 3   │ 2     │ 6     │ 7     │ 3     │
│ 4   │ 4     │ 10    │ 7     │ 4     │
│ 5   │ 3     │ 8     │ 3     │ 1     │
│ 6   │ 7     │ 9     │ 7     │ 8     │
│ 7   │ 8     │ 2     │ 3     │ 2     │
│ 8   │ 5     │ 4     │ 3     │ 9     │
│ 9   │ 9     │ 3     │ 2     │ 10    │
│ 10  │ 6     │ 8     │ 4     │ 10    │

(on master you can add renamecols=false kwarg to avoid last .=> valuecols(gdf)).

The key function here is countmap which gives you counts of occurences of different values in a vector, e.g.:

julia> countmap(gdf[1].A)
Dict{Int64,Int64} with 10 entries:
  7  => 10028
  4  => 10130
  9  => 10007
  10 => 9841
  2  => 10090
  3  => 9985
  5  => 10022
  8  => 10262
  6  => 10103
  1  => 10128

the rest is just a wrapper around it. You need reverse to change key => value to value => key order to make sure maximum picks a right group (note that your problem will not have a unique solution if there are two groups with the same count), and then we use last to extract the group (as you did not want to keep the count).

EDIT:

Now I realized that argmax works for dictionaries so you can just write:

julia> combine(gdf, valuecols(gdf) .=>
                    argmax∘countmap .=>
                    valuecols(gdf))
10×4 DataFrame
│ Row │ key   │ A     │ B     │ C     │
│     │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┼───────┤
│ 1   │ 1     │ 8     │ 8     │ 1     │
│ 2   │ 10    │ 1     │ 2     │ 9     │
│ 3   │ 2     │ 6     │ 7     │ 3     │
│ 4   │ 4     │ 10    │ 7     │ 4     │
│ 5   │ 3     │ 8     │ 3     │ 1     │
│ 6   │ 7     │ 9     │ 7     │ 8     │
│ 7   │ 8     │ 2     │ 3     │ 2     │
│ 8   │ 5     │ 4     │ 3     │ 9     │
│ 9   │ 9     │ 3     │ 2     │ 10    │
│ 10  │ 6     │ 8     │ 4     │ 10    │

Julia Dataframes Groupby chain using combine

1 Answers