Group by and row wise custom arrange in R

user_11375063 0
Viewed 88

I have the following table with more than 10k+ rows:

set.seed(123)
df <- data.frame(col1 = c('A','A','A','A', 'B','B','B','B', 'C','C','C','C'),
                  col2 = c('flower','pet','bird','tree','flower','pet','bird','tree','flower','pet','bird','tree'),
                  col3 = sample(0:25, 12, replace = TRUE))

   col1   col2 col3
1     A flower   14
2     A    pet   18
3     A   bird   13
4     A   tree    2
5     B flower    9
6     B    pet   17
7     B   bird   21
8     B   tree   10
9     C flower    4
10    C    pet   19
11    C   bird   13
12    C   tree   21

I want to arrange each row by grouping by col1 and then arranging in order as pet, tree,bird,flower from col2 and then keep only single occurrence of values in col1.

Sample desired data frame:

   col1   col2 col3
1     A    pet   18
2         tree    2
3         bird   13
4       flower   14
5     B    pet   17
6         tree   10
7         bird   21
8       flower    9
9     C    pet   19
10        tree   21
11        bird   13
12      flower    4

I could not find how to arrange the rows as I have mentioned above. How to do achieve this?

dplyr tidyverse dataframe r
2 Answers

I am not sure if this is a good idea to get rid of the values, because a loss of information. But this is getting rid of duplicates:

df %>% arrange(col1) %>%
  mutate_at(vars(col1), funs(replace(., duplicated(.), "")))
user_77750430

In base R, we can do with order on the 'col1' and the factor converted 'col2' with levels specified in custom order, then use duplicated to replace the duplicate elements in 'col1' to ""

df1 <-  df[order(df$col1, factor(df$col2, 
     levels = c('pet', 'tree', 'bird', 'flower'))),]
row.names(df1) <- NULL
df1$col1[duplicated(df1$col1)] <- ""

-output

df1
#   col1   col2 col3
#1     A    pet   18
#2         tree    2
#3         bird   13
#4       flower   14
#5     B    pet   17
#6         tree   10
#7         bird   21
#8       flower    9
#9     C    pet   19
#10        tree   21
#11        bird   13
#12      flower    4
user_37322710
Related