dcast/spread character-only dataframe with two columns in RHS

Question

I am trying to spread df on two columns in RHS long to wide. Dataframe I have:

dat <- data.frame(
  bm = LETTERS[seq(1,10)], 
  at4 = c("a1","a2","a3","a4","a5","a6","a7","a8","a9", "a10"),
  n1 = c("n1","n2","n3","n4","n5","n6","n7","n8","n9", "n10"),
  cat = c("x","y","z","x","y","z","x","y","z","z"),
  clas = c("k","l","k","l","k","l","k","l","k","l"))

What gives:

bm  at4 n1  cat clas
A   a1  n1  x   k
B   a2  n2  y   l
C   a3  n3  z   k
D   a4  n4  x   l
E   a5  n5  y   k
F   a6  n6  z   l
G   a7  n7  x   k
H   a8  n8  y   l
I   a9  n9  z   k
J   a10 n10 z   l

Desired dataframe:

#The approach that works with adding dummy columns:

dat$temp1 <- 1
dat <- dcast(dat, ... ~ cat, value.var = "temp1", fun.aggregate = sum)
dat$temp1 <- 1
dat <- dcast(dat, ... ~ clas, value.var = "temp1", fun.aggregate = sum)

bm  at4 n1  x   y   z   k   l
A   a1  n1  1   0   0   1   0
B   a2  n2  0   1   0   0   1
C   a3  n3  0   0   1   1   0
D   a4  n4  1   0   0   0   1
E   a5  n5  0   1   0   1   0
F   a6  n6  0   0   1   0   1
G   a7  n7  1   0   0   1   0
H   a8  n8  0   1   0   0   1
I   a9  n9  0   0   1   1   0
J   a10 n10 0   0   1   0   1

I am wondering if it can be done in one go in dcast or spread. I tried:

dat$temp1 <- 1
dcast(dat, ... ~ cat + clas, value.var = "temp1", fun.aggregate = sum)

but it gives:

bm  at4 n1  x_k x_l y_k y_l z_k z_l
A   a1  n1  1   0   0   0   0   0
B   a2  n2  0   0   0   1   0   0
C   a3  n3  0   0   0   0   1   0
D   a4  n4  0   1   0   0   0   0
E   a5  n5  0   0   1   0   0   0
F   a6  n6  0   0   0   0   0   1
G   a7  n7  1   0   0   0   0   0
H   a8  n8  0   0   0   1   0   0
I   a9  n9  0   0   0   0   1   0
J   a10 n10 0   0   0   0   0   1

bm column should be unique for each cell after casting so it can show instead of 1. tried similar approach:

dcast(dat, ... ~ cat + clas, value.var = "bm", fun.aggregate = toString)

But dcast mix RHS columns together:

at4 n1  x_k x_l y_k y_l z_k z_l
a1  n1  A                   
a10 n10                     J
a2  n2              B       
a3  n3                  C   
a4  n4      D               
a5  n5          E           
a6  n6                      F
a7  n7  G                   
a8  n8              H       
a9  n9                  I

score 1 · Answer 1 · answered Jul 24 '22 at 21:52

1

We can melt & dcast in one go:

library(data.table)
setDT(dat)

dcast(melt(dat, id = c("bm", "at4", "n1")), bm + at4 + n1 ~ value, fun = length)
#    bm at4  n1 k l x y z
# 1:  A  a1  n1 1 0 1 0 0
# 2:  B  a2  n2 0 1 0 1 0
# 3:  C  a3  n3 1 0 0 0 1
# 4:  D  a4  n4 0 1 1 0 0
# 5:  E  a5  n5 1 0 0 1 0
# 6:  F  a6  n6 0 1 0 0 1
# 7:  G  a7  n7 1 0 1 0 0
# 8:  H  a8  n8 0 1 0 1 0
# 9:  I  a9  n9 1 0 0 0 1
#10:  J a10 n10 0 1 0 0 1

In the reshape2 package there is a function called recast which "wraps melting and (d)casting a data frame into a single step.", see https://github.com/hadley/reshape/blob/master/R/recast.r

answered Jul 24 '22 at 21:52

markus

25,843
5
39
58

Thanks a lot. What If I needed 3 columns and more in RHS? – Korpo Jul 25 '22 at 14:31
@Korpo better ask a new question with reference to this one if you face another question. Consider to accept one of the answers that solved this problem. Thanks – markus Jul 26 '22 at 20:38

score 0 · Answer 2 · answered Jul 24 '22 at 21:49

Here is a solution but pivot_wider, not with spread/dcast.
Reshape both cat and clas to wide format, then join the two results.

suppressPackageStartupMessages({
  library(dplyr)
  library(tidyr)
})

dat <- data.frame(
  bm = LETTERS[seq(1,10)], 
  at4 = c("a1","a2","a3","a4","a5","a6","a7","a8","a9", "a10"),
  n1 = c("n1","n2","n3","n4","n5","n6","n7","n8","n9", "n10"),
  cat = c("x","y","z","x","y","z","x","y","z","z"),
  clas = c("k","l","k","l","k","l","k","l","k","l")) 

inner_join(
  dat %>%
    mutate(new = 1) %>% 
    pivot_wider(
      id_cols = 1:3,
      names_from = cat,
      values_from = new,
      values_fill = 0L
    ),
  dat %>%
    mutate(new = 1L) %>%
    pivot_wider(
      id_cols = 1:3,
      names_from = clas,
      values_from = new,
      values_fill = 0L
    ), 
  by = c("bm", "at4", "n1")
)
#> # A tibble: 10 × 8
#>    bm    at4   n1        x     y     z     k     l
#>    <chr> <chr> <chr> <dbl> <dbl> <dbl> <int> <int>
#>  1 A     a1    n1        1     0     0     1     0
#>  2 B     a2    n2        0     1     0     0     1
#>  3 C     a3    n3        0     0     1     1     0
#>  4 D     a4    n4        1     0     0     0     1
#>  5 E     a5    n5        0     1     0     1     0
#>  6 F     a6    n6        0     0     1     0     1
#>  7 G     a7    n7        1     0     0     1     0
#>  8 H     a8    n8        0     1     0     0     1
#>  9 I     a9    n9        0     0     1     1     0
#> 10 J     a10   n10       0     0     1     0     1

^{Created on 2022-07-24 by the reprex package (v2.0.1)}

dcast/spread character-only dataframe with two columns in RHS

2 Answers2