# How to find frequency count for consecutive actions?

**URL:** <https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582>\
**Category:** Data\
**Tags:** dataframes, inmemorydatasets\
**Created:** [March 28, 2022, 2:20am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582 "2022-03-28T02:20:59Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![xinchin](https://avatars.discourse-cdn.com/v4/letter/x/54ee81/32.png) [@xinchin](https://discourse.julialang.org/u/xinchin)\
**Post date:** [March 28, 2022, 2:20am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/1 "2022-03-28T02:20:59Z")

</div>

I have a few sizable data sets with rows contains actions of a person in different days. I want to summarize the data as a frequency table that shows the number of times that a specific action followed by another action. for example in the following data set ‘A’ followed by ‘A’ 3 times, etc.

```julia
data=Dataset(id=[1,2,3,4],day1=['A','B','A','A'],day2=['C','A','D','A'],day3=[missing,'A','A','A'])
expected=Dataset(action1=['A','C','B','A','A','D'],action2=['C',missing,'A','A','D','A'],count=[1,1,1,3,1,1])

```

I prefer `InMemoryDatasets` solution but open to answers using `DataFrames`.

---

<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:** [March 28, 2022, 4:31am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/2 "2022-03-28T04:31:24Z")

</div>

I also tried a version with IMD even if I’m not familiar with the various possibilities and maybe it can be improved

```julia

df=DataFrame(data)

dfres=DataFrame(vcat([df.day1 df.day2], [df.day2 df.day3]),[:act1, :act2])

DataFrames.combine(DataFrames.groupby(dfres, [:act1, :act2]), nrow)

```

```julia

dfres=Dataset(vcat([df.day1 df.day2], [df.day2 df.day3]),[:act1, :act2])

InMemoryDatasets.combine(InMemoryDatasets.groupby(dfres, [:act1, :act2]),:act1=>length)

```

---

<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:** [March 28, 2022, 4:40pm UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/3 "2022-03-28T16:40:38Z")

</div>

```julia

function countActChg(x,y,z)
d=countmap([Pair.(x,y);Pair.(y,z)])
(k=collect(keys(d)),v=collect(values(d)))
end

DataFrames.combine(df, 2:4 =>countActChg=>AsTable)

ulia> DataFrames.combine(df, 2:4 =>countActChg=>AsTable)
6×2 DataFrame
 Row │ k v     
     │ Pair… Int64
─────┼─────────────────────
   1 │ 'A'=>'C' 1
   2 │ 'A'=>'D' 1
   3 │ 'C'=>missing 1
   4 │ 'B'=>'A' 1
   5 │ 'A'=>'A' 3
   6 │ 'D'=>'A' 1

```

```julia
DataFrames.combine(df, 2:4 =>countAC=>AsTable)

function countAC(x,y,z)
d=countmap([tuple.(x,y);tuple.(y,z)])
(k=collect(keys(d)),v=collect(values(d)))
end

```

why in this solution it is necessary to use the collect function (without it does not work)?  
is there a more synthetic way to simulate the same transformation?

---

<div class="post-metadata">

**Author:** ![monopolynomial](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/monopolynomial/32/34782_2.png) [@monopolynomial](https://discourse.julialang.org/u/monopolynomial)\
**Post date:** [March 28, 2022, 11:02pm UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/4 "2022-03-28T23:02:09Z")

</div>

what happens if there are more columns in data set, your solution supposes only 3 days are in data set?

---

<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:** [March 29, 2022, 4:42am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/5 "2022-03-29T04:42:41Z")

</div>

this is the first way that comes to mind, but I think it can also be done through reduce or with a recursive function

```julia
df=DataFrame(id=[1,2,3,4],day1=['A','B','A','A'],day2=['C','A','D','A'],day3=[missing,'A','A','A'],day4=['A','B','A',missing],day5=['C',missing,'D','A'])

 reshape(Matrix(df[:,2:end]),:,2) 

```

or

```julia
using IterTools
vcat([[f l] for (f,l) in partition(eachcol(df[:,r"day"]),2,1)]...)

```

```julia

cols=eachcol(df)
n=ncol(df)
reduce((s,c)->vcat(s,[cols[c-1] cols[c]]), 4:n,init=[cols[2] cols[3]])

```

```julia

reduce((s,c)->vcat(s,[s[end-3:end,2] c]), eachcol(df)[4:end];init=[df[:,2] df[:,3]])

```

---

<div class="post-metadata">

**Author:** ![mostafa1342004](https://avatars.discourse-cdn.com/v4/letter/m/9de0a6/32.png) [@mostafa1342004](https://discourse.julialang.org/u/mostafa1342004)\
**Post date:** [March 29, 2022, 7:09am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/6 "2022-03-29T07:09:57Z")

</div>

Do like this:

-transpose each row

-add lag of days for each id

-remove the first obs for each id

-count each action

---

<div class="post-metadata">

**Author:** ![monopolynomial](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/monopolynomial/32/34782_2.png) [@monopolynomial](https://discourse.julialang.org/u/monopolynomial)\
**Post date:** [March 29, 2022, 10:29pm UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/7 "2022-03-29T22:29:53Z")

</div>

```julia
julia> using Chain
julia> function f(x)
            result=ones(Bool,length(x))
            result[1]=false
            result
         end
julia> @chain data begin
           groupby(:id)
           transpose(r"day")
           groupby(:id)
           modify!(:_c1=>lag=>:lagd, :_c1=>f=>:temp)
           filter(:temp)
           groupby([:_c1, :lagd])
           combine(:_c1=>length)
       end

```

---

<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:** [March 30, 2022, 7:45pm UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/8 "2022-03-30T19:45:51Z")

</div>

```julia
result[end]=false

modify!(:_c1=>lead=>:lagd, :_c1=>f=>:temp)

```

shoul give the rigth order

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [March 31, 2022, 4:11am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/9 "2022-03-31T04:11:20Z")

</div>

use

```julia
@chain data begin
     gatherby(:id, eachrow=true)
     transpose((2:ncol(data)-1, 3:ncol(data)))
     gatherby(r"_c")
     combine(1=>length)
end

```

---

<div class="post-metadata">

**Author:** ![xinchin](https://avatars.discourse-cdn.com/v4/letter/x/54ee81/32.png) [@xinchin](https://discourse.julialang.org/u/xinchin)\
**Post date:** [April 14, 2022, 2:12am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/10 "2022-04-14T02:12:12Z")

</div>

thanks, great!

---

<div class="post-metadata">

**Author:** ![Kia\_Kia](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kia_kia/32/35863_2.png) [@Kia\_Kia](https://discourse.julialang.org/u/Kia_Kia)\
**Post date:** [May 1, 2022, 10:09am UTC](https://discourse.julialang.org/t/how-to-find-frequency-count-for-consecutive-actions/78582/11 "2022-05-01T10:09:09Z")

</div>

great trick!