# Counting in dataframes

**URL:** <https://discourse.julialang.org/t/counting-in-dataframes/99907>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [June 5, 2023, 6:56pm UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907 "2023-06-05T18:56:46Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![optimist](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/optimist/32/14797_2.png) [@optimist](https://discourse.julialang.org/u/optimist)\
**Post date:** [June 5, 2023, 6:56pm UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/1 "2023-06-05T18:56:47Z")

</div>

I have a dataframe in which I need to count the number of occurences of values in a given list. Suppose I have a dataframe df:

```julia
df= DataFrame(:B1 => [1,1,2,2,3,4,5], :B2 => [1,7,7,2,2,5,5],:B3 => [2,2,2,3,3,3,3] )

```

```julia
7×3 DataFrame
 Row │ B1 B2 B3    
     │ Int64 Int64 Int64 
─────┼─────────────────────
   1 │ 1 1 2
   2 │ 1 7 2
   3 │ 2 7 2
   4 │ 2 2 3
   5 │ 3 2 3
   6 │ 4 5 3
   7 │ 5 5 3

```

And I want to produce df2, where the column VAL has numbers 1 to 7 and I want to produce a count of them against the entries in columns of the dataframe df.

```julia
df2 = DataFrame(:VAL => collect(1:7),:B1 => [2,2,1,1,1,0,0], :B2 => [1,2,2,0,0,2,2],:B3 => [0,3,4,0,0,0,0])

```

```julia
7×4 DataFrame
 Row │ VAL B1 B2 B3    
     │ Int64 Int64 Int64 Int64 
─────┼────────────────────────────
   1 │ 1 2 1 0
   2 │ 2 2 2 3
   3 │ 3 1 2 4
   4 │ 4 1 0 0
   5 │ 5 1 0 0
   6 │ 6 0 2 0
   7 │ 7 0 2 0

```

How can I do this?

---

<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:** [June 5, 2023, 7:10pm UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/2 "2023-06-05T19:10:13Z")

</div>

You could probably write a custom function doing this, but here is a solution using predefined functions only:

```julia
julia> @chain [rename!(combine(groupby(df, "B$i"), nrow => :VAL), ["VAL", "B$i"]) for i in 1:3] begin
           outerjoin(_..., on=:VAL)
           coalesce.(0)
           sort!(:VAL)
       end
6×4 DataFrame
 Row │ VAL B1 B2 B3
     │ Int64 Int64 Int64 Int64
─────┼────────────────────────────
   1 │ 1 2 1 0
   2 │ 2 2 2 3
   3 │ 3 1 0 4
   4 │ 4 1 0 0
   5 │ 5 1 2 0
   6 │ 7 0 2 0

```

---

<div class="post-metadata">

**Author:** ![optimist](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/optimist/32/14797_2.png) [@optimist](https://discourse.julialang.org/u/optimist)\
**Post date:** [June 5, 2023, 7:18pm UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/3 "2023-06-05T19:18:15Z")

</div>

> [@bkamins](#):
>
> ```julia
> @chain [rename!(combine(groupby(df, "B$i"), nrow => :VAL), ["VAL", "B$i"]) for i in 1:3] begin
> outerjoin(_..., on=:VAL)
> coalesce.(0)
> sort!(:VAL)
> end
> 
> ```

Thank you!

---

<div class="post-metadata">

**Author:** ![juliohm](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juliohm/32/215266_2.png) [@juliohm](https://discourse.julialang.org/u/juliohm)\
**Post date:** [June 5, 2023, 10:54pm UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/4 "2023-06-05T22:54:02Z")

</div>

Whenever you need to count entries in a table to produce another table with these counts, you are after what is called contingency tables:

> **[GitHub - nalimilan/FreqTables.jl: Frequency tables in Julia](https://github.com/nalimilan/FreqTables.jl)**
>
> Frequency tables in Julia. Contribute to nalimilan/FreqTables.jl development by creating an account on GitHub.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [June 6, 2023, 12:10am UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/5 "2023-06-06T00:10:22Z")

</div>

Another method is to use `countmap` from `StatsBase` which is a good function to know:

```julia
using StatsBase

df2 = DataFrame([
  1:7 get.(permutedims(countmap.(eachcol(df))),1:7,0)], 
  ["VAL",names(df)...])

```

giving `df2` as in the OP.

---

<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:** [June 6, 2023, 9:38pm UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/6 "2023-06-06T21:38:58Z")

</div>

```julia
rng=(:)(extrema(union(eachcol(df)...))...)

m=[count(==(j), df[:,i]) for j in rng , i in 1:ncol(df)] #used the function called in the title :smile:

DataFrame([rng;;m],["val";names(df)...])

```

---

<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:** [June 7, 2023, 7:08am UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/7 "2023-06-07T07:08:51Z")

</div>

just for fun

```julia
udf=stack(df,Cols(:))
push!(udf,("",6))
gudf=groupby(udf,:value)
cgudf=combine(gudf, x->unique(combine(groupby(x,:variable),:variable=>length=>:count, :value)))
select(unstack(cgudf, :variable, :count, fill=0), Not(""))

```

or

```julia

udf=stack(df,Cols(:))
cgudf=combine(groupby(udf,:value), x->combine(groupby(x,:variable),:variable=>length=>:count, :value))
push!(unstack(cgudf, :variable, :count, combine=first,fill=0),(6,0,0,0))

#-----------------

udf=stack(df,Cols(:))
cgudf=combine(groupby(udf,:value), x->combine(groupby(x,:variable),:variable=>length=>:count, :value))
dfu=unstack(cgudf, :variable, :count, combine=first,fill=0)
lac=[(i,0,0,0) for i in (:)(extrema(dfu.value)...) if i ∉ dfu.value]
push!(dfu,lac...)

```

The following one is perhaps less convoluted than the others

```julia
#-----------------

  udf=stack(df,Cols(:))
  cg=combine(groupby(udf, [:value,:variable]),nrow)
  ucg=unstack(cg,:variable,:nrow, fill=0)
  push!(ucg,(6,0,0,0))

#------------------------

```

---

<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:** [June 7, 2023, 1:49pm UTC](https://discourse.julialang.org/t/counting-in-dataframes/99907/8 "2023-06-07T13:49:21Z")

</div>

a comparison between the different proposals for the small example df

```julia
function tr17(df)
    m=[length(findall(==(j), df[:,i])) for j in 1:7 , i in 1:3]
    #m=(count(==(j), df[:,i]) for j in 1:7 , i in 1:ncol(df))
    DataFrame([1:7;;m],["val";names(df)...])
end

julia> @btime tr17(df)
  5.550 μs (155 allocations: 9.26 KiB)
7×4 DataFrame
 Row │ val B1 B2 B3    
     │ Int64 Int64 Int64 Int64
─────┼────────────────────────────
   1 │ 1 2 1 0
   2 │ 2 2 2 3
   3 │ 3 1 0 4
   4 │ 4 1 0 0
   5 │ 5 1 2 0
   6 │ 6 0 0 0
   7 │ 7 0 2 0
julia> using DataFramesMeta

julia> @btime @chain [rename!(combine(groupby(df, "B$i"), nrow => :VAL), ["VAL", "B$i"]) for i in 1:3] begin
                  outerjoin(_..., on=:VAL)
                  coalesce.(0)
                  sort!(:VAL)
              end
  185.800 μs (1306 allocations: 79.44 KiB)
6×4 DataFrame
 Row │ VAL B1 B2 B3    
     │ Int64 Int64 Int64 Int64
─────┼────────────────────────────
   1 │ 1 2 1 0
   2 │ 2 2 2 3
   3 │ 3 1 0 4
   4 │ 4 1 0 0
   5 │ 5 1 2 0
   6 │ 7 0 2 0

julia> using StatsBase

julia> @btime df2 = DataFrame([
         1:7 get.(permutedims(countmap.(eachcol(df))),1:7,0)],
         ["VAL",names(df)...])
  3.943 μs (64 allocations: 5.29 KiB)
7×4 DataFrame
 Row │ VAL B1 B2 B3    
     │ Int64 Int64 Int64 Int64
─────┼────────────────────────────
   1 │ 1 2 1 0
   2 │ 2 2 2 3
   3 │ 3 1 0 4
   4 │ 4 1 0 0
   5 │ 5 1 2 0
   6 │ 6 0 0 0
   7 │ 7 0 2 0

julia> function stunst(df)
         udf=stack(df,Cols(:))
         cg=combine(groupby(udf, [:value,:variable]),nrow)
         ucg=unstack(cg,:variable,:nrow, fill=0)
         push!(ucg,(6,0,0,0))
       end
stunst (generic function with 1 method)

julia> @btime stunst(df)
  80.400 μs (531 allocations: 34.33 KiB)
7×4 DataFrame
 Row │ value B1 B2 B3    
     │ Int64 Int64 Int64 Int64
─────┼────────────────────────────
   1 │ 1 2 1 0
   2 │ 2 2 2 3
   3 │ 3 1 0 4
   4 │ 4 1 0 0
   5 │ 5 1 2 0
   6 │ 7 0 2 0
   7 │ 6 0 0 0

```