Convert sequences in a data frame to a matrix in R

user_5571686 0
Viewed 45

My data represents the last two purchases for each member and are ordered by FROM -> TO

df <- data.frame(member_id = c(1,1,2,2,3,3,4,4,5,5), direction = c('FROM','TO','FROM','TO','FROM','TO','FROM','TO','FROM','TO'),product = c('A','A','A','B','C','D','C','C','A','A'))

I am trying to build a transition matrix to represent how members move between products.

Based on the data above I hope to populate the matrix with the count of events from -> to as follows:

   A   B   C   D
A  2   1   0   0
B  0   0   0   0
C  0   0   1   1
D  0   0   0   0

How can I achieve this using dplyr syntax?

dplyr r matrix
1 Answers

With couple of pivot_wider statements you can do :

library(dplyr)
library(tidyr)

df %>%
  mutate(product = factor(product, unique(product))) %>%
  pivot_wider(names_from = direction, values_from = product) %>%
  count(FROM, TO, .drop = FALSE) %>%
  pivot_wider(names_from = TO, values_from = n)

#  FROM      A     B     C     D
#  <fct> <int> <int> <int> <int>
#1 A         2     1     0     0
#2 B         0     0     0     0
#3 C         0     0     1     1
#4 D         0     0     0     0
user_39629140
Related