pivot_wider when there's no value column

Question

I'm trying to reshape a dataset from long to wide. The following code works, but I'm curious if there's a way not to provide a value column and still use pivot_wider. In the following example, I have to create a temporary column "val" to use pivot_wider, but is there a way I can do it without it?

a <- data.frame(name = c("sam", "rob", "tom"),
                 type = c("a", "b", "c"))
a
  name type
1  sam    a
2  rob    b
3  tom    c

I want to convert it as the following.

name      a     b     c
1 sam       1     0     0
2 rob       0     1     0
3 tom       0     0     1

This can be done by the following code, but can I do it without creating "val" column (and still using tidyverse language)?

a <- data.frame(name = c("sam", "rob", "tom"),
                type = c("a", "b", "c"), 
                val = rep(1, 3)) %>%
  pivot_wider(names_from = type, values_from = val, values_fill = list(val = 0))

Ritchie Sacramento · Accepted Answer · 2022-12-02T06:21:33.597

11

You can use the values_fn argument to assign 1 and values_fill to assign 0:

library(tidyr)

pivot_wider(a, names_from = type, values_from = type, values_fn = ~1, values_fill = 0)

# A tibble: 3 x 4
  name      a     b     c
  <fct> <dbl> <dbl> <dbl>
1 sam       1     0     0
2 rob       0     1     0
3 tom       0     0     1

edited Dec 02 '22 at 06:21

answered Mar 04 '20 at 22:51

Ritchie Sacramento

29,890
4
48
56

any intuition as to why this works? i would not have thought to do this. – rrs Feb 02 '22 at 20:27

score 9 · Answer 2 · edited Mar 05 '20 at 07:55

We can mutate with a column of 1s and use that in pivot_wider

library(dplyr)
library(tidyr)
a %>%
     mutate(n = 1) %>% 
     pivot_wider(names_from = type, values_from = n, values_fill = list(n = 0))
# A tibble: 3 x 4
#  name      a     b     c
#  <fct> <dbl> <dbl> <dbl>
#1 sam       1     0     0
#2 rob       0     1     0
#3 tom       0     0     1

In base R, it would be easier..

table(a)

score 6 · Answer 3 · answered Mar 04 '20 at 23:00

Going older school, reshape2::dcast, or the thriving data.table::dcast, let you do this by specifying an aggregate function:

reshape2::dcast(a, name ~ type, fun.aggregate = length)
#   name a b c
# 1  rob 0 1 0
# 2  sam 1 0 0
# 3  tom 0 0 1

data.table::dcast(setDT(a), name ~ type, fun.aggregate = length)
#    name a b c
# 1:  rob 0 1 0
# 2:  sam 1 0 0
# 3:  tom 0 0 1

pivot_wider when there's no value column

3 Answers3