0

I am using R to try to transform my data frame from "long-ish" to "wide-ish" and I have searched in vain for an answer that uses data similar in structure to mine. Here are my data:

| ID   | NAME | V1    |  V2 |   V3 |
|------|------|-------|----:|-----:|
| 1001 | Bob  | Red   | 302 | 0.50 |
| 1001 | Bob  | Blue  | 737 | 0.50 |
| 1002 | Jim  | Red   | 432 | 0.14 |
| 1002 | Jim  | Blue  | 643 | 0.60 |
| 1002 | Jim  | Green |  34 | 0.46 |
| 1006 | Dan  | Red   | 876 | 1.25 |

And this is how I would like the final data (wide) to look:

| ID   | NAME | V2.Red | V2.Blue | V2.Green | V3.Red | V3.Blue | V3.Green |
|------|------|-------:|--------:|---------:|-------:|--------:|---------:|
| 1001 | Bob  |    302 |     737 |      N/A |   0.50 |    0.50 |      N/A |
| 1002 | Jim  |    432 |     643 |       34 |   0.14 |    0.60 |     0.46 |
| 1006 | Dan  |    876 |     N/A |      N/A |   1.25 |     N/A |      N/A |

So, basically, I'm collapsing all of the same ID rows into one row (with accompanying NAME) so that the total number of rows is equal to the number of unique ID values.
I am then using the unique values of V1 to create as many columns as there are unique values in V1 times the number of "extra variables"--V2, V3. (I have many more variables of the V2 and V3 type.

Thanks in advance!

user3029412
  • 99
  • 8

2 Answers2

2

We can use pivot_wider from the new tidyr

tidyr::pivot_wider(df, names_from = V1, values_from = c(V2, V3))

#     ID NAME  V2_Red V2_Blue V2_Green V3_Red V3_Blue V3_Green
#  <int> <fct>  <int>   <int>    <int>  <dbl>   <dbl>    <dbl>
#1  1001 Bob      302     737       NA   0.5      0.5    NA   
#2  1002 Jim      432     643       34   0.14     0.6     0.46
#3  1006 Dan      876      NA       NA   1.25    NA      NA

data

df <- structure(list(ID = c(1001L, 1001L, 1002L, 1002L, 1002L, 1006L
), NAME = structure(c(1L, 1L, 3L, 3L, 3L, 2L), .Label = c("Bob", 
"Dan", "Jim"), class = "factor"), V1 = structure(c(3L, 1L, 3L, 
1L, 2L, 3L), .Label = c("Blue", "Green", "Red"), class = "factor"), 
V2 = c(302L, 737L, 432L, 643L, 34L, 876L), V3 = c(0.5, 0.5, 
0.14, 0.6, 0.46, 1.25)), class = "data.frame", row.names = c(NA, -6L))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • 1
    Thank you so much. It worked perfectly! I'm almost ashamed to admit how much time I spent this weekend trying to figure this out on my own. – user3029412 Nov 18 '19 at 06:57
0

For completeness sake, here is a data.table solution

library( data.table )
dcast( setDT(df), ID + NAME ~ V1, value.var = c("V2","V3"), sep = "." )

#      ID NAME V2.Blue V2.Green V2.Red V3.Blue V3.Green V3.Red
# 1: 1001  Bob     737       NA    302     0.5       NA   0.50
# 2: 1002  Jim     643       34    432     0.6     0.46   0.14
# 3: 1006  Dan      NA       NA    876      NA       NA   1.25
Wimpel
  • 26,031
  • 1
  • 20
  • 37