Using pivot_wider instead of spread for R dataframe

Question

My understanding was that to an extend, pivot_wider(data, names_from, values_from) is roughly equivalent to spread(data, key, value). I'm not seeing that here.

library(tidyverse)

df = data.frame(
  A = sample(letters[1:3], 20, replace=T), 
  B = sample(LETTERS[1:3], 20, replace=T)
)

df
#>    A B
#> 1  c B
#> 2  b C
#> 3  b B
#> 4  b C
#> 5  c C
#> 6  b B
#> 7  c B
#> 8  b A
#> 9  c A
#> 10 b B
#> 11 c B
#> 12 b B
#> 13 b A
#> 14 c C
#> 15 c C
#> 16 a B
#> 17 c C
#> 18 b C
#> 19 b A
#> 20 a C

df %>% count(A,B)
#>   A B n
#> 1 a B 1
#> 2 a C 1
#> 3 b A 3
#> 4 b B 4
#> 5 b C 3
#> 6 c A 1
#> 7 c B 3
#> 8 c C 4

df %>% count(A,B) %>% spread(key=B, value=n)
#>    A B C
#> 1 NA 1 1
#> 2  3 4 3
#> 3  1 3 4

df %>% count(A,B) %>% pivot_wider(names_from=B, values_from=n)
#> Error: Failed to create output due to bad names.
#> * Choose another strategy with `names_repair`

^{Created on 2020-10-22 by the reprex package (v0.3.0)}

score 2 · Accepted Answer · answered Oct 23 '20 at 02:15

pivot_wider is equivalent to spread however, it is also more stricter. You need to be more explicit in doing transformations.

library(dplyr)
library(tidyr)

df %>% count(A,B)

#  A B n
#1 a A 3
#2 a B 1
#3 a C 1
#4 b A 2
#5 b B 4
#6 b C 2
#7 c A 2
#8 c B 4
#9 c C 1

Notice how A column above is silently overwritten when you use spread.

df %>% count(A,B) %>% spread(key=B, value=n)
#  A B C
#1 3 1 1
#2 2 4 2
#3 2 4 1

pivot_wider doesn't allow that. It wants you to explicitly mention what you want to do. Since you already have an A column and in names_from you specify B as column name which has 'A' value in it so you'll have a another A column. Tibbles don't allow duplicate column names hence you get an error.

An option would be to rename the original A column to something else.

df %>% 
  count(A,B) %>% 
  rename(A1 = A) %>%
  pivot_wider(names_from=B, values_from=n)

#   A1     A     B     C
#  <chr> <int> <int> <int>
#1 a         3     1     1
#2 b         2     4     2
#3 c         2     4     1

Using pivot_wider instead of spread for R dataframe

1 Answers1