Constructing Markov Transition Frequency Matrix with DataFrames

I am cross-posting this question from StackOverflow since that seems a bit slow.

I want to use Julia DataFrames to construct a 3x3 Markov transition matrix i.e. a frequency matrix that tells me the likelihood of transitioning from each of 3 states to the others. I am trying to learn data frames and I would like to learn the best way to do this. This is more for general learning than about this particular example.

Here’s some code I tried so far with some example data but I am not really familiar enough with how to think about dataframes to know how to proceed.

Any suggestions? Thank you.


state=[2,2,3,1,1,3,3,2,1,1,3,1,2,3,2,3,1,2,3,3,1]
statelag=[1,2,2,3,1,1,3,3,2,1,1,3,1,2,3,2,3,1,2,3,3]
df = DataFrame(state=state, statelag=statelag)

markov = combine(groupby(df, [:statelag, :state]), nrow => :cat_countmar)
sort!(markov, :statelag, :state) # this gives the number of occurences of each tranistion

total = combine(groupby(df, :statelag), nrow => :cat_count) 
# this gives the number of occurences of each state


trans = Array{Float64}(undef, (3,3))
# trans should give probability of transitioning between different states

I need to basically “divide” catcountmar of by cat_count so that I’m dividing the number of occurrences of a transition from state i to state j by the number of occurences of state i. This will give the desired transition frequency. But I don’t see how to put markov and total together in one data frame and easily carry out this computation.