0

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)
Sam Mwenda
  • 150
  • 8
  • Please refer to other SO questions (this is a duplicate question) and other resources of google: https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format ; http://www.cookbook-r.com/Manipulating_data/Converting_data_between_wide_and_long_format/ ; https://www.datacamp.com/community/tutorials/long-wide-data-R – Dylan_Gomes Aug 02 '20 at 18:09
  • @Sam Mwenda, hello! Just a question, what happens to the variable `h07`? I don't see that variable in `df2`. – Alexis Aug 02 '20 at 18:12
  • 1
    What is the conversion to wide here? I'm sorry, but I'm having trouble making any sense of the transformation that leads from `df1` to `df2` – duckmayr Aug 02 '20 at 18:37
  • Thanks Alexis and duckmayr for asking. I have checked the link provided by @Dylan_Gomes and seen similarity to what I was looking for. I will have to use h07 for transformation and later rename to my value of choice. – Sam Mwenda Aug 02 '20 at 18:42

1 Answers1

2

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)

Bruno
  • 4,109
  • 1
  • 9
  • 27