Group by column to row values

Question

I am trying to group the rows to columns. In my dataframe df, I need to group by column A1 and obtain the respective B1 values in new mutated columns X1, X2 and X3.

df <- data.frame(A1 = c("a is a cat","b is a basket","a is a cat","c for c2","b is a basket"),
                 B1 = c("alpha in","for beta","for gamma","for ceta","alpha in"))
df

        A1        B1
1    a is a cat  alpha in
2 b is a basket  for beta
3    a is a cat for gamma
4      c for c2  for ceta
5 b is a basket  alpha in

Expected Output:

        A1        X1           X2       X3
1    a is a cat  alpha in   for gamma
2 b is a basket  for beta   alpha in  
3      c for c2  for ceta

Possible duplicate of [Transpose / reshape dataframe without "timevar" from long to wide format](https://stackoverflow.com/questions/11322801/transpose-reshape-dataframe-without-timevar-from-long-to-wide-format) — camille, Nov 22 '19 at 19:53

score 2 · Answer 1 · answered Nov 22 '19 at 19:23

2

We create a sequence grouped by 'A1' and use pivot_wider

library(dplyr)
library(tidyr)
library(stringr)
df %>%
  group_by(A1) %>%
  mutate(new = str_c("X", row_number())) %>%
  pivot_wider(names_from = new, values_from = B1)

answered Nov 22 '19 at 19:23

akrun

874,273
37
540
662

Thanks akrun also is there an alternate for pivot_wider function? – prog Nov 22 '19 at 19:49

Rushabh Patel · Accepted Answer · 2019-11-22T20:24:56.687

1

You can use data.table:

> library(data.table)
> setDT(df)[,n:=paste0('X',1:.N),by=A1]
> dcast.data.table(df,A1~n,value.var = 'B1')

edited Nov 22 '19 at 20:24

answered Nov 22 '19 at 20:14

Rushabh Patel

2,672
13
34

score 0 · Answer 3 · answered Nov 24 '19 at 04:15

Base R Solution:

df_new <- setNames(aggregate(list(B1 = df$B1),

                             by = list(df$A1),

                             paste0,

                             collapse = "_-_"),

                   c("A1", "X1"))

df_new <- cbind(A1 = df_new$A1,

                setNames(data.frame(do.call(rbind,

                                            lapply(strsplit(df_new$X1, '\\_\\-\\_'),

                                                   function(x){length(x) = nrow(df_new)

                                                   return(x)

                                                        }
                                                   )
                                            )
                                    ),

         c(paste0("X", 1:nrow(df_new))))

         )
df_new

Data:

    df <- data.frame(A1 = c("a is a cat","b is a basket","a is a cat","c for c2","b is a basket"),
                 B1 = c("alpha in","for beta","for gamma","for ceta","alpha in"))

Group by column to row values

3 Answers3