Reshaping data by ID number and parent response

[RN_R]

Hi all,

I wondered if anyone could shed some light on to the best package to re-shape my data. I have had a look at many blogs/posts online, but can not seem to get the data arranged in the way I want it.

I have a dataset which contains around 180 variables.
Of these variables I have a Child_ID code and a parent number. I want to layout the data so that each row is the data for 1 child (i.e. it will include all data from all variables for both parent 1 and parent 2).
I have tried using the dplyr package, as well as reshape2, but I haven't had any luck - I am relatively new to R.

For example the data currently looks like this (although many more variables, note some children do not have both parents responses.
Child_ID Parent X Y Z.... (180 variables)
1 1 3 4 5
1 2 1 3 6
2 1 3 5 7
3 1 6. 9. 2
3. 2 3. 2. 1

The end result I am after would be something like this

Child_ID X_1 Y_1. Z_1 X_2 Y_2 Z_2

1.           3.      4         5         1        4.      6
   

2 3. 5. 7. NA. NA. NA
3 6. 9. 2. 3. 2. 1

Any help or guidance would be greatly appreciated.
Thank you in advance

[woodward]

Personally I always put my data in long format, as this is easiest to work within dplyr and ggplot.

You can put it in long format first, create the new col names, and then use pivot_wider.

library(tidyr)
library(stringr)
library(dplyr)

df <- data.frame(
  stringsAsFactors = FALSE,
          Child_ID = c(1, 1, 2, 3, 3),
            Parent = c(1L, 2L, 1L, 1L, 2L),
                 X = c(3, 1, 3, 6, 3),
                 Y = c(4, 3, 5, 9, 2),
                 Z = c(5L, 6L, 7L, 2L, 1L))

df %>% 
  pivot_longer(cols = X:Z) %>% 
  mutate(new_col_name = str_c(name, "_", Parent)) %>% 
  pivot_wider(id_cols = Child_ID, 
              names_from = new_col_name, 
              values_from = value)
#> # A tibble: 3 x 7
#>   Child_ID   X_1   Y_1   Z_1   X_2   Y_2   Z_2
#>      <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1        1     3     4     5     1     3     6
#> 2        2     3     5     7    NA    NA    NA
#> 3        3     6     9     2     3     2     1

^{Created on 2020-05-21 by the reprex package (v0.3.0)}