CODEWITHSUNDEEP
rmatrix
Astor
Astor

Reputation: 37

how to make a matrix table by counting the transitions in two columns by groups in r

Here is my data

id<- c("1", "1", "1", "1", "2", "2", "2", "2", "3", "3", "3", "3")
behav1<- c("A", "C", "C", "B", "C", "C", "A", "A", "A", "B", "B", "A")
behav2<- c("C", "A", "A", "B", "A", "B", "A", "B", "C", "B", "B", "C")
df <- data.frame(id, cond1, cond2)

I want to make a transition table to see how many times people will do from behav1>behav2 for example, subj no. 1 did A to C (one time), C to A(two times), B to B(one time) enter image description here

I'd like to make a transition table by counts for each subject

Here is my code

df %>% 
group_by(id) %>% 
summarise(c =  as.data.frame.matrix(table(df$behav1, df$behav2)))

However, what I got is total counts by all subjects enter image description here

What did I do wrong? Thanks for the help in advance!

Upvotes: 0

Views: 55

Answers (2)

Brenton M. Wiernik
Brenton M. Wiernik

Reputation: 1306

It's probably easier to not use table() here. Instead, group_by() all 3 columns (giving you groups for each unique person and transition), then summarize:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

id <- c("1", "1", "1", "1", "2", "2", "2", "2", "3", "3", "3", "3")
behav1 <- c("A", "C", "C", "B", "C", "C", "A", "A", "A", "B", "B", "A")
behav2 <- c("C", "A", "A", "B", "A", "B", "A", "B", "C", "B", "B", "C")
df <- data.frame(id, behav1, behav2)

(long_result <- df |> 
  group_by(id, behav1, behav2) |> 
  summarize(n = n(), .groups = "drop")
)
#> # A tibble: 9 x 4
#>   id    behav1 behav2     n
#>   <chr> <chr>  <chr>  <int>
#> 1 1     A      C          1
#> 2 1     B      B          1
#> 3 1     C      A          2
#> 4 2     A      A          1
#> 5 2     A      B          1
#> 6 2     C      A          1
#> 7 2     C      B          1
#> 8 3     A      C          2
#> 9 3     B      B          2

It might be easier to present this in wide format:

(wide_result <- long_result |> 
  tidyr::pivot_wider(
    id_cols = "id",
    names_from = c("behav1", "behav2"),
    names_sep = " -> ",
    values_from = "n"
  ) |> 
  mutate(across(everything(), tidyr::replace_na, replace = 0))
)
#> # A tibble: 3 x 7
#>   id    `A -> C` `B -> B` `C -> A` `A -> A` `A -> B` `C -> B`
#>   <chr>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
#> 1 1            1        1        2        0        0        0
#> 2 2            0        0        1        1        1        1
#> 3 3            2        2        0        0        0        0

Created on 2021-05-30 by the reprex package (v2.0.0)

Upvotes: 0

Karthik S
Karthik S

Reputation: 11596

Will this work:

library(dplyr)
library(purrr)
map(df %>% group_by(id) %>% group_split(.keep = 0), table)
[[1]]
      behav2
behav1 A B C
     A 0 0 1
     B 0 1 0
     C 2 0 0

[[2]]
      behav2
behav1 A B
     A 1 1
     C 1 1

[[3]]
      behav2
behav1 B C
     A 0 2
     B 2 0

Upvotes: 1

Related Questions