How to reshape data from long to wide format in r?

Question

Now, I have the same input data frame:

AAA <- c('APIS', 'APIS', 'PIPIS', 'AGAROS', 'AGAROS', 'AGAROS', 'NOTHING')
BBB <- c('a', 'a', 'a', 'b', 'b', 'c', NA)
CCC <- c(1, 2, NA, 4, 5, 6, 7)
DDD <- c("Mat", "ASG", "MNT", "NBEH", "DJ", "EU", "DHR")
test.data <- data.frame(AAA, BBB, CCC, DDD)
test.data

I want to reshape it so that each unique "AAA" variable become single row. Multiple entry of the "AAA" variable automatically become new column with suffix or prefix 1,2,3..... Sort of like this:

AAA <- c('APIS', 'PIPIS', 'AGAROS', 'NOTHING')
BBB_1 <- c('a', 'a', 'b', NA)
CCC_1 <- c(1, NA, 4, 7)
DDD_1 <- c("Mat", "MNT", "NBEH", "DHR")

BBB_2 <- c('a', NA, 'b', NA)
CCC_2 <- c(2, NA, 5, NA)
DDD_2 <- c("ASG", NA, "DJ", NA)

BBB_3 <- c(NA, NA, 'c', NA)
CCC_3 <- c(NA, NA, 6, NA)
DDD_3 <- c(NA, NA, "EU", NA)

output <- data.frame(AAA, BBB_1, CCC_1, DDD_1, BBB_2, CCC_2, DDD_2, 
                        BBB_3, CCC_3, DDD_3)
output

I've looked at melt and cast and a few other things, but none seem to do the job.

Answer 1

You can create a unique row number for each unique value of AAA and then cast to wide format.

library(dplyr)
library(tidyr)

test.data %>%
  mutate(row = row_number(), .by = AAA) %>%
  pivot_wider(names_from = row, values_from = BBB:DDD, names_vary = "slowest")

# A tibble: 4 × 10
#  AAA     BBB_1 CCC_1 DDD_1 BBB_2 CCC_2 DDD_2 BBB_3 CCC_3 DDD_3
#  <chr>   <chr> <dbl> <chr> <chr> <dbl> <chr> <chr> <dbl> <chr>
#1 APIS    a         1 Mat   a         2 ASG   NA       NA NA   
#2 PIPIS   a        NA MNT   NA       NA NA    NA       NA NA   
#3 AGAROS  b         4 NBEH  b         5 DJ    c         6 EU   
#4 NOTHING NA        7 DHR   NA       NA NA    NA       NA NA   

Using data.table -

library(data.table)
setDT(test.data)

dcast(test.data[, row := seq_len(.N), AAA], 
      AAA ~ row, value.var = c("BBB", "CCC", "DDD"))