all dataframe rows to new columns

user_10952140 0
Viewed 51

I want to move an arbitrary number of dataframe rows to columns, not as key-value pairs (like with tidyr::pivot_wider), just as new columns.

enter image description here

So here's a dataframe

df <- data.frame(A = c(1, 2, 3),
                 B = c(4, 5, 6),
                 C = c(7, 8, 9),
                 ID = c("ID", "ID", "ID"))

I can use c like this, but I have to specify each row, which I'd like to avoid. I'm not concerned about the column naming convention. X1, X2 etc. is fine.

l <- c(df[,1], df[,2], df[,3], df[,4])

data.frame(matrix(unlist(l), ncol=length(l), byrow=T))
  X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15 X16
1  1  2  3  4  5  6  7  8  9  10  11  12  ID  ID  ID  ID

This also leaves duplicated values in X13, X14, X15, X16, which I'd also like to avoid.

Is there a better way, ideally either in base or dplyr/purrr and without a for loop?

purrr dplyr dataframe r
3 Answers

Try this. You can use tidyverse functions in order to reshape data to long, keep the IDs in id variable, create the new names using row_number() and then reshape to wide using pivot_wider(). Here the code using the data you shared:

library(ggplot2)
library(dplyr)
library(tidyr)
#Code
newdf <- df %>% pivot_longer(-ID) %>%
  group_by(ID,name) %>%
  mutate(name=paste0(name,'_',row_number())) %>%
  pivot_wider(names_from = name,values_from=value)

Output:

# A tibble: 1 x 10
# Groups:   ID [1]
  ID      A_1   B_1   C_1   A_2   B_2   C_2   A_3   B_3   C_3
  <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 ID        1     4     7     2     5     8     3     6     9

Or try this:

#Code 2
newdf <- df %>% pivot_longer(-ID) %>%
  group_by(ID,name) %>%
  mutate(name=paste0(name,'_',row_number())) %>%
  pivot_wider(names_from = name,values_from=value,names_sort = T)

Output:

# A tibble: 1 x 10
# Groups:   ID [1]
  ID      A_1   A_2   A_3   B_1   B_2   B_3   C_1   C_2   C_3
  <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 ID        1     2     3     4     5     6     7     8     9
user_20808480

We could do this with dcast

library(data.table)
dcast(setDT(df), ID ~rowid(ID), value.var = c('A', 'B', 'C'))
#   ID A_1 A_2 A_3 B_1 B_2 B_3 C_1 C_2 C_3
#1: ID   1   2   3   4   5   6   7   8   9

Or with just pivot_wider after creating a sequence column

library(dplyr)
library(tidyr)
df %>% 
    mutate(rn = rowid(ID)) %>%
    pivot_wider(names_from = rn, values_from = A:C)
# A tibble: 1 x 10
#  ID      A_1   A_2   A_3   B_1   B_2   B_3   C_1   C_2   C_3
#  <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1 ID        1     2     3     4     5     6     7     8     9
user_37322710

Here is a base R option using reshape

reshape(
  cbind(df,q = 1:nrow(df)),
  direction = "wide",
  idvar = "ID",
  timevar = "q"
)

which gives

  ID A.1 B.1 C.1 A.2 B.2 C.2 A.3 B.3 C.3
1 ID   1   4   7   2   5   8   3   6   9
user_121587570
Related