Long to wide based on different columns in R

Question

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)

Answer 1

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