group columns in R dataframe based on values in another column

Question

Consider the following pair of lists

 ID<-c("A", "B")
 Var2<-c("T_X", "X_F", "R_X", "T_Y", "Y_F", "R_Y")

I have created the following dataframe

df2<-expand.grid(ID, Var2)

The resultant dataframe is as follows

   Var1 Var2
1     A  T_X
2     B  T_X
3     A  X_F
4     B  X_F
5     A  R_X
6     B  R_X
7     A  T_Y
8     B  T_Y
9     A  Y_F
10    B  Y_F
11    A  R_Y
12    B  R_Y

I would like to reorder the dataframe by Var1 column so that all values corresponding to A are together and likewise with B. (Note this is a toy dataset and the real number of unique values in Var1 can be much higher).

The following is the expected output

   Var1 Var2
1     A  T_X
3     A  X_F
5     A  R_X
7     A  T_Y
9     A  Y_F
11    A  R_Y
2     B  T_X
4     B  X_F
6     B  R_X
8     B  T_Y
10    B  Y_F
12    B  R_Y

I have tried df2%>% group_by(Var1). this has left the dataframe unchanged.

I request someone to help me here.

The output of df2 %>% arrange(Var1) will do the job right? — Mohan Govindasamy
– Mohan Govindasamy, Commented Feb 2, 2021 at 7:21

akrun · Accepted Answer · 2021-02-02 07:22:40Z

2

We can do it in reverse. No need of any ordering afterwards or any packages

setNames(expand.grid(Var2, ID)[2:1], c("Var1", "Var2"))

-output

#    Var1 Var2
#1     A  T_X
#2     A  X_F
#3     A  R_X
#4     A  T_Y
#5     A  Y_F
#6     A  R_Y
#7     B  T_X
#8     B  X_F
#9     B  R_X
#10    B  T_Y
#11    B  Y_F
#12    B  R_Y

Or use crossing from tidyr

library(tidyr)
crossing(ID, Var2 = factor(Var2, levels = Var2))

-output

#   ID    Var2 
#   <chr> <chr>
# 1 A     T_X  
# 2 A     X_F  
# 3 A     R_X  
# 4 A     T_Y  
# 5 A     Y_F  
# 6 A     R_Y  
# 7 B     T_X  
# 8 B     X_F  
# 9 B     R_X  
# 10 B     T_Y  
# 11 B     Y_F  
# 12 B     R_Y

edited Feb 2, 2021 at 7:22

answered Feb 2, 2021 at 7:16

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ronak Shah · Accepted Answer · 2021-02-02 07:19:51Z

1

You can use tidyr's expand_grid which works as expected here.

tidyr::expand_grid(ID, Var2)

#   ID    Var2 
#   <chr> <chr>
# 1 A     T_X  
# 2 A     X_F  
# 3 A     R_X  
# 4 A     T_Y  
# 5 A     Y_F  
# 6 A     R_Y  
# 7 B     T_X  
# 8 B     X_F  
# 9 B     R_X  
#10 B     T_Y  
#11 B     Y_F  
#12 B     R_Y

However, you can always order df2 output to get output in required format.

df2 <- expand.grid(ID, Var2)
df2[order(df2$Var1), ]

answered Feb 2, 2021 at 7:19

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Collectives™ on Stack Overflow

group columns in R dataframe based on values in another column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related