# Counts of unique values per group in a DataFrame

**URL:** <https://discourse.julialang.org/t/counts-of-unique-values-per-group-in-a-dataframe/40088>\
**Category:** Data\
**Tags:** question, dataframes\
**Created:** [May 24, 2020, 7:08pm UTC](https://discourse.julialang.org/t/counts-of-unique-values-per-group-in-a-dataframe/40088 "2020-05-24T19:08:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kevin.squire](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kevin.squire/32/62_2.png) [@kevin.squire](https://discourse.julialang.org/u/kevin.squire)\
**Post date:** [May 24, 2020, 7:08pm UTC](https://discourse.julialang.org/t/counts-of-unique-values-per-group-in-a-dataframe/40088/1 "2020-05-24T19:08:42Z")

</div>

Hi,

I’m trying to determine unique counts of values in a column per group in a DataFrame.

As an example, given the following:

```julia
using DataFrames

julia> df = DataFrame(lab = [repeat(["Lab1"], 4)...; repeat(["Lab2"], 5)...], value = ['a','a','b','a','a','b','c','c','c'])
9×2 DataFrame
│ Row │ lab │ value │
│ │ String │ Char │
├─────┼────────┼───────┤
│ 1 │ Lab1 │ 'a' │
│ 2 │ Lab1 │ 'a' │
│ 3 │ Lab1 │ 'b' │
│ 4 │ Lab1 │ 'a' │
│ 5 │ Lab2 │ 'a' │
│ 6 │ Lab2 │ 'b' │
│ 7 │ Lab2 │ 'c' │
│ 8 │ Lab2 │ 'c' │
│ 9 │ Lab2 │ 'c' │

```

I would like to get

```julia
5×3 DataFrame
│ Row │ lab │ value │ count │
│ │ String │ Char │ Int64 │
├─────┼────────┼───────┼───────┤
│ 1 │ Lab1 │ 'a' │ 3 │
│ 2 │ Lab1 │ 'b' │ 1 │
│ 3 │ Lab2 │ 'a' │ 1 │
│ 4 │ Lab2 │ 'b' │ 1 │
│ 5 │ Lab2 │ 'c' │ 3 │

```

I’ve gotten as far as

```julia
julia> combine(grouped, :value => (vals -> keys(counter(vals))) => :value, :value => (vals -> values(counter(vals))) => :count)
2×3 DataFrame
│ Row │ lab │ value │ count │
│ │ String │ Base.KeySet… │ Base.Val… │
├─────┼────────┼─────────────────┼───────────┤
│ 1 │ Lab1 │ ['a', 'b'] │ [3, 1] │
│ 2 │ Lab2 │ ['a', 'c', 'b'] │ [1, 3, 1] │

```

However, I would prefer

1. not to call `counter` twice
2. actually split the counts into separate rows

Can someone help?

Thanks!  
Kevin

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [May 24, 2020, 7:23pm UTC](https://discourse.julialang.org/t/counts-of-unique-values-per-group-in-a-dataframe/40088/2 "2020-05-24T19:23:55Z")

</div>

```julia
julia> combine(groupby(df, [:lab, :value]), nrow => :count)
5×3 DataFrame
│ Row │ lab │ value │ count │
│ │ String │ Char │ Int64 │
├─────┼────────┼───────┼───────┤
│ 1 │ Lab1 │ 'a' │ 3 │
│ 2 │ Lab1 │ 'b' │ 1 │
│ 3 │ Lab2 │ 'a' │ 1 │
│ 4 │ Lab2 │ 'b' │ 1 │
│ 5 │ Lab2 │ 'c' │ 3 │

```

---

<div class="post-metadata">

**Author:** ![affans](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/affans/32/11911_2.png) [@affans](https://discourse.julialang.org/u/affans)\
**Post date:** [May 25, 2020, 3:16pm UTC](https://discourse.julialang.org/t/counts-of-unique-values-per-group-in-a-dataframe/40088/3 "2020-05-25T15:16:38Z")

</div>

See `Query.jl` solution with a `@groupby` command that lets you group by multiple columns:

```julia
julia> df |> @groupby({_.lab, _.value}) |> @map({lab=key(_)[1], val=key(_)[2], count=length(_)})
5x3 query result
lab │ val │ count
─────┼─────┼──────
Lab1 │ 'a' │ 3
Lab1 │ 'b' │ 1
Lab2 │ 'a' │ 1
Lab2 │ 'b' │ 1
Lab2 │ 'c' │ 3

```

---

<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 25, 2020, 3:25pm UTC](https://discourse.julialang.org/t/counts-of-unique-values-per-group-in-a-dataframe/40088/4 "2020-05-25T15:25:27Z")

</div>

In Query, how do would you put this in a function? With dataframes it’s

```julia
function combinecount(df, groupvars, newvar)
    @pipe df |> 
        groupby(df, groupvars) |> 
        combine(nrow => newvar)
end
```