Pivoting a Dataframe in R

Question

Suppose I am given a dataframe with multiple columns that are factors and a column of interest, colA.

For example, suppose the dataframe looks like the following:

colA | colB | colC | colD
--------------------------
  1  |  'a' |  1   |  2 
  1  |  'b' |  2   |  3
  4  |  'b' |  2   |  4
  2  |  'a' |  3   |  1
  3  |  'a' |  2   |  6
  3  |  'b' |  1   |  6

I would like to summarize every column based on a group-by with colA, but structure it in a way so that the values for colB, colC, colD are spread on the rows, and the values for colA are spread on the columns. That is, I would like the count of colB values (a row per value of colB) when colA has the value 1, when colA has the value 2, and so on. The same for colC and colD. The resulting dataframe will look as follows:

        colA_value1 | colA_value2 | colA_value3 | colA_value4
        -----------------------------------------------------
colB_a |     1      |      1      |      1      |      0
colB_b |     1      |      0      |      1      |      1
colC_1 |     1      |      0      |      1      |      0
colC_2 |     1      |      0      |      1      |      1
colC_3 |     0      |      1      |      0      |      0
colD_1 |     0      |      1      |      0      |      0
colD_2 |     1      |      0      |      0      |      0
colD_3 |     1      |      0      |      0      |      0
colD_4 |     0      |      0      |      0      |      1
colD_6 |     0      |      0      |      2      |      0

Preference given towards using the tidyverse packages.

it would be much easier to answer your question if you would provide a reproducible example what you tried already. Maybe your example data frame with dput(my_data) so people can just copy paste and not constructing your data again. — drmariod
@drmariod voila: alistaire47.github.io/read.so/reference/read.md.html — alistaire

alistaire alistaire · Accepted Answer · 2018-09-05T07:27:39

This can be done with a lot of tidyr:

library(tidyverse)

df <- data.frame(colA = c(1L, 1L, 4L, 2L, 3L, 3L), 
                 colB = c("a", "b", "b", "a", "a", "b"), 
                 colC = c(1L, 2L, 2L, 3L, 2L, 1L), 
                 colD = c(2L, 3L, 4L, 1L, 6L, 6L))

df %>% 
    gather(key, value, colA) %>% 
    unite(colA, key, value) %>% 
    gather(key, value, -colA) %>% 
    unite(col, key, value) %>% 
    count(colA, col) %>% 
    spread(colA, n, fill = 0)
#> Warning: attributes are not identical across measure variables;
#> they will be dropped
#> # A tibble: 10 x 5
#>    col    colA_1 colA_2 colA_3 colA_4
#>    <chr>   <dbl>  <dbl>  <dbl>  <dbl>
#>  1 colB_a      1      1      1      0
#>  2 colB_b      1      0      1      1
#>  3 colC_1      1      0      1      0
#>  4 colC_2      1      0      1      1
#>  5 colC_3      0      1      0      0
#>  6 colD_1      0      1      0      0
#>  7 colD_2      1      0      0      0
#>  8 colD_3      1      0      0      0
#>  9 colD_4      0      0      0      1
#> 10 colD_6      0      0      2      0

Pivoting a Dataframe in R

2 Answers