How to use table() with dplyr group by, map from purrr and a list of dataframes/tibbles)? (In R)

Question

Question How to create tables using table() with a list of data frames/tibbles, while grouping by two variables (example: a sequence of days (eg. {1,2,...,10}) and a factor {0,1,2,3,4})

Data example

example:
ldf<-lapply(1:30, function(x) as.data.frame(cbind(sample(1:3,10,replace=T), sample(1:3,10,replace=T), seq(1:5), sample(0:4,10,replace=T))))

example:

  [[1]]
   V1 V2 V3 V4
1   3  1  1  4
2   1  3  2  2
3   2  2  3  3
4   3  1  4  1
5   1  1  5  3
6   1  1  1  4
7   1  1  2  2
8   3  3  3  3
9   2  2  4  1
10  1  1  5  3

[[2]]
   V1 V2 V3 V4
1   2  1  1  2
2   3  1  2  0
3   1  1  3  4
4   3  1  4  0
5   2  1  5  0
6   2  2  1  2
7   2  2  2  0
8   2  2  3  4
9   2  1  4  0
10  2  3  5  3
...

Where V1 and V2 are transition states which I want to table eg. table(df$V1, df$V2), & V3 (the day) and V4 (a factor between 0-4) which I want to group by.

Expected output

I would like to get a table grouped by V3 and V4 for each data.frame/tibble in the list of data.frame/tibbles and save it back into another list of objects.

visual example (not actual data)

data.frame 1 
group by v3=1 & v4=0
  1 2 3
1 0 1 2
2 0 3 4
3 4 5 6 

data.frame 1
group by v3=1 & v4=1
  1 2 3
1 1 7 8
2 2 6 9
3 4 5 0 

...

data.frame 1
group by v3=2 & v4=0
  1 2 3
1 5 4 4
2 6 5 3
3 7 8 4

...

data.frame 2

...

data.frame 3

...

etc...

akrun akrun · Accepted Answer · 2020-02-22T22:41:45

We can split the data.frame by 'V3', 'V4' and get the table of 'V1', 'V2'

lst2 <- lapply(ldf[1:2], function(dat) lapply(split(dat[1:2], 
               dat[3:4], drop = TRUE), function(x) {
                 lvls <- sort(unique(unlist(x)))
                 table(factor(x[[1]], levels = lvls), factor(x[[2]], levels = lvls))
                  }))

With tidyverse, here is an option

library(purrr)
library(tidyr)
library(dplyr)
map(ldf[1:2], ~
        .x %>%
           group_split(V3, V4) %>%
           map(~ .x %>% 
                 unite(V3V4, V3, V4) %>%
                 group_by_all %>% 
                 summarise(n = n()) %>% 
                 ungroup %>% 
                 complete(V1 = sort(unique(unlist(select(., V1, V2)))),
                          V2 = sort(unique(unlist(select(., V1, V2)))), 
                      fill = list(n = 0) )  %>%
                 pivot_wider(names_from = V2, values_from = n, 
                    values_fill = list(n = 0)) %>% 
                 fill(V3V4, .direction = "updown")))

How to use table() with dplyr group by, map from purrr and a list of dataframes/tibbles)? (In R)

1 Answers