0

I have a 5 column data frame and I want to take that long data frame and make it into a wide data frame by only the last two variables. I would like to keep the first 3 variables in a long format.

I want to go from something like this

Var1     Var2    Var3      ID1        ID2
Seven    April   Hunger    Very       3
Seven    April   Tired     Very       3
Seven    May     Hunger    Moderate   3
Eight    May     Hunger    Very       1
Eight    May     Hunger    Hardly     1
Eight    May     Tired     Very       2

And turn it into:

Var1     Var2    Var3       Very       Moderate    Hardly
Seven    April   Hunger    3          0           0
Seven    April   Tired     3          0           0
Seven    May     Hunger    1          3           1
Eight    May     Tired     2          0           0

How can I do that?

Alokin
  • 461
  • 1
  • 4
  • 22

1 Answers1

2

reshape2

reshape2::dcast(dat, Var1 + Var2 + Var3 ~ ID1, value.var = "ID2", fill = 0)
#    Var1  Var2   Var3 Hardly Moderate Very
# 1 Eight   May Hunger      1        0    1
# 2 Eight   May  Tired      0        0    2
# 3 Seven April Hunger      0        0    3
# 4 Seven April  Tired      0        0    3
# 5 Seven   May Hunger      0        3    0

tidyverse

library(tidyr)
pivot_wider(dat, Var1:Var3, names_from = ID1, values_from = ID2, values_fill = 0)
# # A tibble: 5 x 6
#   Var1  Var2  Var3    Very Moderate Hardly
#   <chr> <chr> <chr>  <int>    <int>  <int>
# 1 Seven April Hunger     3        0      0
# 2 Seven April Tired      3        0      0
# 3 Seven May   Hunger     0        3      0
# 4 Eight May   Hunger     1        0      1
# 5 Eight May   Tired      2        0      0

base R

out <- reshape(dat, idvar = c("Var1", "Var2", "Var3"), timevar = "ID1", direction = "wide")
out[,4:6] <- lapply(out[,4:6], function(z) ifelse(is.na(z), 0, z))
out
#    Var1  Var2   Var3 ID2.Very ID2.Moderate ID2.Hardly
# 1 Seven April Hunger        3            0          0
# 2 Seven April  Tired        3            0          0
# 3 Seven   May Hunger        0            3          0
# 4 Eight   May Hunger        1            0          1
# 6 Eight   May  Tired        2            0          0

Data

dat <- structure(list(Var1 = c("Seven", "Seven", "Seven", "Eight", "Eight", "Eight"), Var2 = c("April", "April", "May", "May", "May", "May"), Var3 = c("Hunger", "Tired", "Hunger", "Hunger", "Hunger", "Tired"), ID1 = c("Very", "Very", "Moderate", "Very", "Hardly", "Very"), ID2 = c(3L, 3L, 3L, 1L, 1L, 2L)), class = "data.frame", row.names = c(NA, -6L))
r2evans
  • 141,215
  • 6
  • 77
  • 149