Spread a dataframe and relabel column names

Probably a tidyr or a reshape2 question.

I have this type of a data.frame:

df <- data.frame(group = c("A","B","C","D"),
                 id1 = c("AV14D","BV29",NA,NA),
                 id2 = c(NA,"BD1",NA,NA),
                 id3 = c("AJ31","BJ1",NA,NA),
                 n = c(2,4,NA,NA),
                 stringsAsFactors = F)

which has a row with 3 IDs and a count for each group.

I want to transform it to a single row data.frame with #groups x 4 (id1,id2,id3,n) columns, where each group has 4 columns: <group>_id1, <group>_id2, <group>_id3, and <group>_n and the corresponding values.

So the resulting data.frame will be:

data.frame(A_id1 = "AV14D",A_id2 = NA,A_id3 = "AJ31",A_n = 2,
           B_id1 = "BV29",B_id2 = "BD1",B_id3 = "BJ1",B_n = 4,
           C_id1 = NA,C_id2 = NA,C_id3 = NA,C_n = NA,
           D_id1 = NA,D_id2 = NA,D_id3 = NA,D_n = NA,
           stringsAsFactors = F)

>Solution :

We may use pivot_wider after creating a row index column

library(dplyr)
library(tidyr)
library(stringr)
out1 <- df %>%
  mutate(rn = 1) %>% 
  pivot_wider(names_from = group, values_from = id1:n,
        names_glue = "{group}_{.value}") %>% 
  select(order(str_remove(names(.), "_.*")), -rn) %>%
  type.convert(as.is = TRUE)

-checking with OP’s output

> all.equal(out, out1, check.attributes = FALSE)
[1] TRUE