# Efficiently finding the frequency of patterns in DataFrame columns

**URL:** <https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839>\
**Category:** New to Julia\
**Tags:** dataframes, dictionaries, splitapplycombine\
**Created:** [December 30, 2021, 10:05pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839 "2021-12-30T22:05:33Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matthew](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@Matthew](https://discourse.julialang.org/u/Matthew)\
**Post date:** [December 30, 2021, 10:05pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/1 "2021-12-30T22:05:33Z")

</div>

Hi all! I’m looking for help with what might be an easy task.

Say we have two vectors; A = [0,1] and B = [0,1,2]. Matrix C contains every possible combination of A and B such that it has dimensions 6x2.

Now consider a dataset containing N observations again with features A and B. I want to find  
the frequency of each pattern and store number in the same order as matrix C.

Below, I show that I can do this with nested for loops, however, in practice, this becomes time-intensive as the dimensions of matrix C increase (e.g. add another vector, `q = [0,1,2]`). I’m wondering if there is a more efficient way to accomplish this without nested for loops?

I’ve provided some reproducible code below:

```julia

using DataFrames
a = [0,1]
b = [0,1,2]

c = zeros(6,2)
k = 1
for i in 0:1
    for j in 0:2
        c[k,:] = [i,j]
        k+=1
    end
end

# all possible combinations of A,B
@show c

# new data
simple = hcat(rand(a,10), rand(b,10))
data = simple |>
    x->DataFrame(x, :auto)

counter = zeros(6,1);
for i in 0:1
    for j in 0:2
        row = 1 + 3*i + j
        counter[row] = sum( @. (data.x1==i && data.x2 == j))    
    end
end

@show counter

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [December 30, 2021, 11:04pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/2 "2021-12-30T23:04:28Z")

</div>

Not sure I understand you correctly but if a and b are columns in your DataFrame you might be looking for

```julia
combine(groupby(df, [:a, :b]), nrow)

```

Which will give a count of the number of rows for each distinct a/b combination

---

<div class="post-metadata">

**Author:** ![Matthew](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@Matthew](https://discourse.julialang.org/u/Matthew)\
**Post date:** [December 30, 2021, 11:37pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/3 "2021-12-30T23:37:52Z")

</div>

Thanks for the reply! I thought about this, but my desired output would be something like:

`final = hcat(c,counter)`

My problem with `combine(groupby(df, [:a, :b]), nrow)` is that it will likely result in a number of distinct groups that is less than the number of possible a/b combinations accounted for in matrix `c`.

It’s like I want to merge the result of the groupby/combine with c and fill the nrow with 0 if a certain a/b combo isn’t apparent in `data`

---

<div class="post-metadata">

**Author:** ![Matthew](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@Matthew](https://discourse.julialang.org/u/Matthew)\
**Post date:** [December 30, 2021, 11:41pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/4 "2021-12-30T23:41:30Z")

</div>

I think I found it…

```julia
y = combine(groupby(df, [:a, :b]), nrow)
final = outerjoin(c,y, on = [:x1,:x2])

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [December 31, 2021, 6:14am UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/5 "2021-12-31T06:14:18Z")

</div>

I see - in that case you probably want to create a new DataFrame from the result of `IterTools.product(df.x, df.y)` first and then `leftjoin` the result of the groupby operation onto that. Finally `coalesce` the nrow column in the joined DataFrame to replace missing with zero if desired.

---

<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:** [December 31, 2021, 9:28am UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/6 "2021-12-31T09:28:20Z")

</div>

Using `sort!()` ensures the same order but there must be a better way:

```julia
using DataFrames
a, b = [0,1], [0,1,2]
df = DataFrame(Iterators.product(a, b))
rename!(df,[:a,:b])
sort!(df, [:a, :b])

data = DataFrame(a = rand(a,10), b = rand(b,10)) # new data
dg = combine(groupby(data, [:a, :b]), nrow => :count)
dfc = outerjoin(dg, df, on = [:a, :b])
sort!(dfc, [:a, :b])

julia> dfc # dfc sorted by [:a, :b] as df

 Row │ a b count   
     │ Int64 Int64 Int64?
─────┼───────────────────────
   1 │ 0 0 4
   2 │ 0 1 1
   3 │ 0 2 missing
   4 │ 1 0 4
   5 │ 1 1 missing
   6 │ 1 2 1

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [December 31, 2021, 10:47am UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/7 "2021-12-31T10:47:09Z")

</div>

Yes, that ☝ is what I meant when answering from my phone this morning 🙂

To me `leftjoin` is conceptually the more correct join here, as `df` should have all combinations, i.e. the right table should not add new rows.

Also if you want to preserve the row order on the left hand side you can use `leftjoin!`:

```julia
julia> df = sort!(rename!(DataFrame(Iterators.product(a, b)), [:a, :b]), [:a, :b])
6×2 DataFrame
 Row │ a b     
     │ Int64 Int64 
─────┼──────────────
   1 │ 0 0
   2 │ 0 1
   3 │ 0 2
   4 │ 1 0
   5 │ 1 1
   6 │ 1 2

julia> leftjoin!(df, 
    combine(groupby(DataFrame(a = rand(a, 10), b = rand(b, 10)), [:a, :b]), nrow => :count), 
    on = [:a, :b])

6×3 DataFrame
 Row │ a b count   
     │ Int64 Int64 Int64?  
─────┼───────────────────────
   1 │ 0 0 2
   2 │ 0 1 2
   3 │ 0 2 3
   4 │ 1 0 1
   5 │ 1 1 2
   6 │ 1 2 missing 

```

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [December 31, 2021, 11:23am UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/8 "2021-12-31T11:23:19Z")

</div>

The target counts are easy to compute without going through dataframes. Starting with your arrays `a, b, c, simple`:

```julia
julia> using SplitApplyCombine
julia> get.(Ref(groupcount(eachrow(simple))), eachrow(c), 0)
6-element Vector{Int64}:
 2
 4
 0
 2
 1
 1

```

Further, it can be more convenient to work with `c` of the 2x3 shape instead of flat 6 length. Counts can then be obtained with the same shape:

```julia
julia> c = collect.(Iterators.product(a, b))
2×3 Matrix{Vector{Int64}}:
 [0, 0] [0, 1] [0, 2]
 [1, 0] [1, 1] [1, 2]

julia> get.(Ref(groupcount(eachrow(simple))), c, 0)
2×3 Matrix{Int64}:
 2 4 0
 2 1 1

```

---

<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:** [December 31, 2021, 12:02pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/9 "2021-12-31T12:02:21Z")

</div>

@aplavin, using SplitApplyCombine could we get a `Dict` like below without going through `get.(Ref(...), c, 0)`?

```julia
using SplitApplyCombine
a, b = [0,1], [0,1,2]
data = hcat(rand(a,10), rand(b,10))
c = collect.(Iterators.product(a, b))
d = get.(Ref(groupcount(eachrow(data))), c, 0)
Dict(c .=> d)

Dict{Vector{Int64}, Int64} with 6 entries:
  [0, 0] => 0
  [1, 1] => 1
  [0, 2] => 2
  [1, 0] => 0
  [1, 2] => 4
  [0, 1] => 3

```

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [December 31, 2021, 12:19pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/10 "2021-12-31T12:19:59Z")

</div>

`groupcount(eachrow(data))` is already a dictionary like the one you need. It only has values that are actually present in the dataset though, without zero counts - of course, how would the function know the set of all potentially possible values by itself.

---

<div class="post-metadata">

**Author:** ![Matthew](https://avatars.discourse-cdn.com/v4/letter/m/ed655f/32.png) [@Matthew](https://discourse.julialang.org/u/Matthew)\
**Post date:** [December 31, 2021, 4:14pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/11 "2021-12-31T16:14:20Z")

</div>

Thanks for this approach! I’m finding it to be marginally faster than the dataframes approach with dimensions of `c =7776x6` and `simple = 30274x6`

---

<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:** [January 1, 2022, 11:25am UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/12 "2022-01-01T11:25:57Z")

</div>

@aplavin, would it be a good idea to initialize the dictionary with all counters at zero, and then update it? For example:

```julia
using Dictionaries, SplitApplyCombine
a, b = [0,1], [0,1,2]
c = collect.(Iterators.product(a, b))
data = hcat(rand(a,10), rand(b,10))
dic = merge!(dictionary(c .=> 0), groupcount(eachrow(data)))

6-element Dictionary{Vector{Int64}, Int64}
 [0, 0] │ 4
 [1, 0] │ 4
 [0, 1] │ 0
 [1, 1] │ 0
 [0, 2] │ 0
 [1, 2] │ 2

```

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [January 1, 2022, 9:27pm UTC](https://discourse.julialang.org/t/efficiently-finding-the-frequency-of-patterns-in-dataframe-columns/73839/13 "2022-01-01T21:27:58Z")

</div>

Well, it can clearly be useful if you need the final counts as a dictionary with zero entries present. But you may want to keep the result “sparse”, especially if a small fraction of possible values is actually present. It’s still easy to access counts with `get(dict, key, 0)`.