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?
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
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
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))