Transposing a DF by a certain number of lines

May 24, 2022

Suppose I have a data frame that looks like this:

mydf <- data.frame(col0=c("row1","row2","row3"),
                     col1=c(AAA1),
                     col2=c(BBB1),
                     col3=c(CCC1),
                     col4=c(AAA2),
                     col5=c(BBB2),
                     col6=c(CCC2))
mydf
# col0 col1
# row1 AAA1
# row2 BBB1
# row3 CCC1
# row4 AAA2
# row5 BBB2
# row6 CCC2

I’d like it to be transposed every 3 lines, so that it looks like this:

# col0 col1 col2 col3
# row1 AAA1 BBB1 CCC1
# row2 AAA2 BBB2 CCC2

How could I make this? Is there any function that does this kind of transposing? Something like a pivot_wider ou spread that could be set to repeat every X lines?

>Solution :

You need to add a pivot column and an end id column so it does not collapse:

df <- data.frame(
    col0 = c("a1", "b1", "c1", "a2", "b2", "c2", "a3", "b3", "c3")
)

print(df)

 col0
1   a1
2   b1
3   c1
4   a2
5   b2
6   c2
7   a3
8   b3
9   c3

Now, the transformation:

df <- df %>% 
    mutate(
        col_pivot = rep(1:3, length.out = nrow(.)),
        end_id = rep(1:(nrow(.)/3), each = 3) # this prevents the dataframe from collapsing to 1 row
        ) %>% 
    pivot_wider(
        names_from = col_pivot,
        names_prefix = "col_",
        values_from = col0
    )

print(df)

# A tibble: 3 × 4
  end_id col_1 col_2 col_3
   <int> <chr> <chr> <chr>
1      1 a1    b1    c1   
2      2 a2    b2    c2   
3      3 a3    b3    c3