My original data looks like this: original data
It's a very long dataset, the # of sections repeat every 10 times. I tried to pivot the data to a format like this: desired format
When I use pivot-wider, it gives me Nas all over.... How can I get the data pivoted? Here's the original data:
df1 <- structure(list(ID = 1:60, Num_of_section = c(1L, 2L, 3L, 4L, 5L,
6L, 7L, 8L, 9L, 10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L,
1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 1L, 2L, 3L, 4L, 5L,
6L, 7L, 8L, 9L, 10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L,
1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L), Data = c(123L, 434L,
545L, 2323L, 54L, 23L, 54L, 24L, 53L, 44L, 132L, 44L, 543L, 335L,
53L, 33L, 42L, 24L, 35L, 55L, 12L, 143L, 533L, 344L, 232L, 5332L,
23L, 333L, 56L, 75L, 123L, 434L, 545L, 2323L, 54L, 23L, 54L,
24L, 53L, 44L, 132L, 44L, 543L, 335L, 53L, 33L, 42L, 24L, 35L,
55L, 12L, 143L, 533L, 344L, 232L, 5332L, 23L, 333L, 56L, 75L)), class = "data.frame", row.names = c(NA,
-60L))
This is the input I'm getting now:
noa <- structure(list(X..of.section = 1:10, `1` = c(123L, NA, NA, NA,
NA, NA, NA, NA, NA, NA), `2` = c(NA, 434L, NA, NA, NA, NA, NA,
NA, NA, NA), `3` = c(NA, NA, 545L, NA, NA, NA, NA, NA, NA, NA
), `4` = c(NA, NA, NA, 2323L, NA, NA, NA, NA, NA, NA), `5` = c(NA,
NA, NA, NA, 54L, NA, NA, NA, NA, NA), `6` = c(NA, NA, NA, NA,
NA, 23L, NA, NA, NA, NA), `7` = c(NA, NA, NA, NA, NA, NA, 54L,
NA, NA, NA), `8` = c(NA, NA, NA, NA, NA, NA, NA, 24L, NA, NA),
`9` = c(NA, NA, NA, NA, NA, NA, NA, NA, 53L, NA), `10` = c(NA,
NA, NA, NA, NA, NA, NA, NA, NA, 44L), `11` = c(132L, NA,
NA, NA, NA, NA, NA, NA, NA, NA), `12` = c(NA, 44L, NA, NA,
NA, NA, NA, NA, NA, NA), `13` = c(NA, NA, 543L, NA, NA, NA,
NA, NA, NA, NA), `14` = c(NA, NA, NA, 335L, NA, NA, NA, NA,
NA, NA), `15` = c(NA, NA, NA, NA, 53L, NA, NA, NA, NA, NA
), `16` = c(NA, NA, NA, NA, NA, 33L, NA, NA, NA, NA), `17` = c(NA,
NA, NA, NA, NA, NA, 42L, NA, NA, NA), `18` = c(NA, NA, NA,
NA, NA, NA, NA, 24L, NA, NA), `19` = c(NA, NA, NA, NA, NA,
NA, NA, NA, 35L, NA), `20` = c(NA, NA, NA, NA, NA, NA, NA,
NA, NA, 55L), `21` = c(12L, NA, NA, NA, NA, NA, NA, NA, NA,
NA), `22` = c(NA, 143L, NA, NA, NA, NA, NA, NA, NA, NA),
`23` = c(NA, NA, 533L, NA, NA, NA, NA, NA, NA, NA), `24` = c(NA,
NA, NA, 344L, NA, NA, NA, NA, NA, NA), `25` = c(NA, NA, NA,
NA, 232L, NA, NA, NA, NA, NA), `26` = c(NA, NA, NA, NA, NA,
5332L, NA, NA, NA, NA), `27` = c(NA, NA, NA, NA, NA, NA,
23L, NA, NA, NA), `28` = c(NA, NA, NA, NA, NA, NA, NA, 333L,
NA, NA), `29` = c(NA, NA, NA, NA, NA, NA, NA, NA, 56L, NA
), `30` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 75L), `31` = c(123L,
NA, NA, NA, NA, NA, NA, NA, NA, NA), `32` = c(NA, 434L, NA,
NA, NA, NA, NA, NA, NA, NA), `33` = c(NA, NA, 545L, NA, NA,
NA, NA, NA, NA, NA), `34` = c(NA, NA, NA, 2323L, NA, NA,
NA, NA, NA, NA), `35` = c(NA, NA, NA, NA, 54L, NA, NA, NA,
NA, NA), `36` = c(NA, NA, NA, NA, NA, 23L, NA, NA, NA, NA
), `37` = c(NA, NA, NA, NA, NA, NA, 54L, NA, NA, NA), `38` = c(NA,
NA, NA, NA, NA, NA, NA, 24L, NA, NA), `39` = c(NA, NA, NA,
NA, NA, NA, NA, NA, 53L, NA), `40` = c(NA, NA, NA, NA, NA,
NA, NA, NA, NA, 44L), `41` = c(132L, NA, NA, NA, NA, NA,
NA, NA, NA, NA), `42` = c(NA, 44L, NA, NA, NA, NA, NA, NA,
NA, NA), `43` = c(NA, NA, 543L, NA, NA, NA, NA, NA, NA, NA
), `44` = c(NA, NA, NA, 335L, NA, NA, NA, NA, NA, NA), `45` = c(NA,
NA, NA, NA, 53L, NA, NA, NA, NA, NA), `46` = c(NA, NA, NA,
NA, NA, 33L, NA, NA, NA, NA), `47` = c(NA, NA, NA, NA, NA,
NA, 42L, NA, NA, NA), `48` = c(NA, NA, NA, NA, NA, NA, NA,
24L, NA, NA), `49` = c(NA, NA, NA, NA, NA, NA, NA, NA, 35L,
NA), `50` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 55L), `51` = c(12L,
NA, NA, NA, NA, NA, NA, NA, NA, NA), `52` = c(NA, 143L, NA,
NA, NA, NA, NA, NA, NA, NA), `53` = c(NA, NA, 533L, NA, NA,
NA, NA, NA, NA, NA), `54` = c(NA, NA, NA, 344L, NA, NA, NA,
NA, NA, NA), `55` = c(NA, NA, NA, NA, 232L, NA, NA, NA, NA,
NA), `56` = c(NA, NA, NA, NA, NA, 5332L, NA, NA, NA, NA),
`57` = c(NA, NA, NA, NA, NA, NA, 23L, NA, NA, NA), `58` = c(NA,
NA, NA, NA, NA, NA, NA, 333L, NA, NA), `59` = c(NA, NA, NA,
NA, NA, NA, NA, NA, 56L, NA), `60` = c(NA, NA, NA, NA, NA,
NA, NA, NA, NA, 75L)), row.names = c(NA, -10L), class = c("tbl_df",
"tbl", "data.frame"))
And this is the desired output:
output <- structure(list(X = 1:10, `1` = c(123L, 434L, 545L, 2323L, 54L,
23L, 54L, 24L, 53L, 44L), `2` = c(132L, 44L, 543L, 335L, 53L,
33L, 42L, 24L, 35L, 55L), `3` = c(12L, 143L, 533L, 344L, 232L,
5332L, 23L, 333L, 56L, 75L), `4` = c(123L, 434L, 545L, 2323L,
54L, 23L, 54L, 24L, 53L, 44L), `5` = c(132L, 44L, 543L, 335L,
53L, 33L, 42L, 24L, 35L, 55L), `6` = c(12L, 143L, 533L, 344L,
232L, 5332L, 23L, 333L, 56L, 75L), `7` = c(123L, 434L, 545L, 2323L,
54L, 23L, 54L, 24L, 53L, 44L), `8`= c(132L, 44L, 543L, 335L,
53L, 33L, 42L, 24L, 35L, 55L), `9` = c(12L, 143L, 533L, 344L,
232L, 5332L, 23L, 333L, 56L, 75L), `10` = c(123L, 434L, 545L,
2323L, 54L, 23L, 54L, 24L, 53L, 44L), `11` = c(132L, 44L, 543L,
335L, 53L, 33L, 42L, 24L, 35L, 55L), `12` = c(12L, 143L, 533L,
344L, 232L, 5332L, 23L, 333L, 56L, 75L)), class = "data.frame", row.names = c(NA,
-10L))
CodePudding user response：
You can try this; it will pivot your data wider, and name the columns X plus X1 to X6.
pivot_wider(
df1 %>% mutate(ID = ceiling(ID/10)) %>% rename(X = Num_of_section),
names_from=ID,values_from = Data,names_prefix = "X"
)
Output:
X X1 X2 X3 X4 X5 X6
<int> <int> <int> <int> <int> <int> <int>
1 1 123 132 12 123 132 12
2 2 434 44 143 434 44 143
3 3 545 543 533 545 543 533
4 4 2323 335 344 2323 335 344
5 5 54 53 232 54 53 232
6 6 23 33 5332 23 33 5332
7 7 54 42 23 54 42 23
8 8 24 24 333 24 24 333
9 9 53 35 56 53 35 56
10 10 44 55 75 44 55 75
If you really want X1 through X6 to be replicated, you can assign the result from above to df1_wide, and do this:
bind_cols(
df1_wide,
df1_wide %>% select(-X) %>% rename_with(~paste0("X",7:12))
)
Output:
X X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12
<int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 1 123 132 12 123 132 12 123 132 12 123 132 12
2 2 434 44 143 434 44 143 434 44 143 434 44 143
3 3 545 543 533 545 543 533 545 543 533 545 543 533
4 4 2323 335 344 2323 335 344 2323 335 344 2323 335 344
5 5 54 53 232 54 53 232 54 53 232 54 53 232
6 6 23 33 5332 23 33 5332 23 33 5332 23 33 5332
7 7 54 42 23 54 42 23 54 42 23 54 42 23
8 8 24 24 333 24 24 333 24 24 333 24 24 333
9 9 53 35 56 53 35 56 53 35 56 53 35 56
10 10 44 55 75 44 55 75 44 55 75 44 55 75