Group column according to categories in a list

user_13767410 0
Viewed 75

I have data frame like this:

library(dplyr)
set.seed(5)
# generate sample data
df <- data.frame(value = 1:10,
                 type = sample(LETTERS, 10))

   value type
1      1    B
2      2    K
3      3    O
4      4    Y
5      5    I
6      6    U
7      7    G
8      8    S
9      9    C
10    10    F

I want to group the column "type" according categories defined in a list:

groups <- list(LETTERS[1:7],
               LETTERS[8:15],
               LETTERS[16:20],
               "other")
print(groups)
# [[1]]
# [1] "A" "B" "C" "D" "E" "F" "G"
# 
# [[2]]
# [1] "H" "I" "J" "K" "L" "M" "N" "O"
#
# [[3]]
# [1] "P" "Q" "R" "S" "T"
#
# [[4]]
# [1] "other"

The output should be like:

   value type group
1      1    B     1
2      2    K     2
3      3    O     2
4      4    Y other
5      5    I     2
6      6    U other
7      7    G     1
8      8    S     3
9      9    C     1
10    10    F     1

My approach works as follows:

# group data
df_grouped <- df %>%
    mutate(group = ifelse(type %in% groups[[1]], 1, 
                          ifelse(type %in% groups[[2]], 2,
                                 ifelse(type %in% groups[[3]], 3, "other"))))

Since I have many more groups, I do not like the ifelse loop in the code. It is not easy to maintain the code. Is there any more efficiently way to achieve this?

dplyr r
4 Answers

A simple way to do this would be to convert groups to a data frame using reshape2::melt and perform a left_join:

library(dplyr)
library(tidyr)
library(reshape2)

left_join(df, melt(groups), by = c(type = "value")) %>%
  replace_na(list(L1 = "other")) %>%
  rename(group = L1)

#>    value type group
#> 1      1    B     1
#> 2      2    K     2
#> 3      3    O     2
#> 4      4    Y other
#> 5      5    I     2
#> 6      6    U other
#> 7      7    G     1
#> 8      8    S     3
#> 9      9    C     1
#> 10    10    F     1

A base R method that gives the same result would be

df$group <- sapply(type, function(s) {
              i <- which(sapply(groups, function(g) s %in% g))
              if(length(i) < 1)  "other" else i
              }))
user_125003150

We can use enframe with join

library(dplyr)
library(tibble)
library(tidyr)
enframe(groups,  value = 'type') %>%
      unnest(c(type)) %>% 
      right_join(df)
user_37322710

Here is a base R option using stack + merge

out <- type.convert(merge(df,stack(setNames(groups,seq_along(groups))),by.x = "type",by.y = "values",all.x = TRUE))
replace(out,is.na(out),"other")[match(df$value,out$value),]

which gives

   type value   ind
1     B     1     1
6     K     2     2
7     O     3     2
10    Y     4 other
5     I     5     2
9     U     6 other
4     G     7     1
8     S     8     3
2     C     9     1
3     F    10     1
user_121587570

Convert the list to a named vector and use a standard lookup:

df$group = replace(v <- setNames(rep(seq_along(groups), lengths(groups)),
                                 unlist(groups))[df$type], is.na(v), "other")

Another base alternative: The levels of a factor are renamed using a named list:

df$group = factor(df$type)
levels(df$group) = setNames(groups, seq_along(groups))

Now the "other" group is represented by NA. If you wish to change it:

df$group = as.character(df$group)
df$group[is.na(df$group)] = "other"
user_18517120
Related