I have the following long data that I want to transform to wide data based on a different column
id_1<-c(1,2,3,4,4,4,4,5,5,5,5,5,6)
h06b<-c(1,1,1,1,2,3,4,1,2,3,4,5,1)
h07<-c(1,2,3,4,5,6,7,8,9,10,11,12,13)
df1<-data.frame(id_1,h06b,h07)
i want to convert to wide based on h06b, replacing the final value from h07 my output should be
id_1<-c(1,2,3,4,5,6)
h06b_0<-c(1,2,3,5,8,13)
h06b_1<-c(NA,NA,NA,6,9,NA)
h06b_2<-c(NA,NA,NA,7,10,NA)
h06b_3<-c(NA,NA,NA,NA,11,NA)
h06b_4<-c(NA,NA,NA,NA,12,NA)
df2<-data.frame(id_1,h06b_0,h06b_1,h06b_2,h06b_3,h06b_4)
This should be what you are trying to do
library(tidyverse)
id_1<-c(1,2,3,4,4,4,4,5,5,5,5,5,6)
h06b<-c(1,1,1,1,2,3,4,1,2,3,4,5,1)
h07<-c(1,2,3,4,5,6,7,8,9,10,11,12,13)
df1<-data.frame(id_1,h06b,h07)
id_1<-c(1,2,3,4,5,6)
h06b_0<-c(1,2,3,5,8,13)
h06b_1<-c(NA,NA,NA,6,9,NA)
h06b_2<-c(NA,NA,NA,7,10,NA)
h06b_3<-c(NA,NA,NA,NA,11,NA)
h06b_4<-c(NA,NA,NA,NA,12,NA)
df2<-data.frame(id_1,h06b_0,h06b_1,h06b_2,h06b_3,h06b_4)
df1 %>%
mutate(h06b = h06b - 1) %>%
pivot_wider(names_from = h06b,values_from = h07,names_prefix = "h06b_")
#> # A tibble: 6 x 6
#> id_1 h06b_0 h06b_1 h06b_2 h06b_3 h06b_4
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 1 1 NA NA NA NA
#> 2 2 2 NA NA NA NA
#> 3 3 3 NA NA NA NA
#> 4 4 4 5 6 7 NA
#> 5 5 8 9 10 11 12
#> 6 6 13 NA NA NA NA
Created on 2020-08-02 by the reprex package (v0.3.0)