-1

I wish to convert many variables from long to wide in R. I have clustered data with family as a cluster, and in each family individuals. For each individual several measured variables, each measured more than once. In the example below, you can see two variables x and y, each measured twice.

I know how to do this for one variable, using the reshape2 package and the dcast function, but it doesn't work for more than one.

I am attaching a picture of example data and the code that generated it.

family = c(1,1,1,2,2,2,3,3,3,1,1,1,2,2,2,3,3,3)
id = c(1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3)
time = c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
x = round(runif(18,1,10))
y = round(runif(18,1,10))

D = data.frame(family, id, time, x, y)

enter image description here

The output should be a data frame with the following variables: family, id, x_1, x_2, y_1, y_2.

Edit:

You can see an example here:

enter image description here

Thank you !

user2899944
  • 249
  • 2
  • 11

1 Answers1

2

You can create a unique identifier row for every family, id and time variable and then use pivot_wider.

library(dplyr)

D %>% 
   group_by(family, id, time) %>% 
   mutate(row = row_number()) %>% 
   tidyr::pivot_wider(names_from = time, values_from = c(x, y))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Your code has created two rows for every id, if I am not mistaken. I am no expert of pipes, but does your code created a unique id, or is this my mistake ? – user2899944 Jan 24 '20 at 08:49
  • @user2899944 Can you update your post with expected output for the given example? – Ronak Shah Jan 24 '20 at 08:55
  • Yes of course, thanks ! – user2899944 Jan 24 '20 at 08:58
  • From where do you get 3 and 1 in `y_1` and `y_2` for 1st row? Are those random numbers? Can you add exact expected output atleast for first few rows? Also always use `set.seed` for generation of random numbers so that we can reproduce the same numbers at our end. – Ronak Shah Jan 24 '20 at 09:01
  • You are correct, I re-ran the code and got different numbers...I'll fix it now – user2899944 Jan 24 '20 at 09:09
  • @user2899944 Please try `D %>% group_by(family, id, time) %>% mutate(row = row_number()) %>% tidyr::pivot_wider(names_from = time, values_from = c(x, y)) ` – Ronak Shah Jan 24 '20 at 09:16
  • Seems to be working, thanks ! Maybe you can change the main response so I can mark it as solved and thank you appropriately. :-) – user2899944 Jan 24 '20 at 09:33