Assigning data less than 5 in one group in r

user_16525356 0
Viewed 171

I have data set like this:

ID  col1
1   A
2   A
3   A
4   A
5   A
6   B
7   B
8   C
9   D
10  A
11  A
12  A

I want to create a new column that keeps "A" as it is (since its number above 5) and gather all the letters that has number less than 5 in one group called "other" as this example:

ID  col1 col2
1   A     A
2   A     A
3   A     A
4   A     A
5   A     A
6   B     other
7   B     other
8   C     other
9   D     other
10  A      A
11  A      A
12  A      A

can you please help me?

The dataframe: df<-data.frame(Id=seq(1,12),col1=c(rep('A',5),'B','B','C','D',rep('A',3)))

r
6 Answers

This uses the tidyverse functions to group by col1, and then assign col2 based on that grouping.

library(tidyverse)
DF <- data.frame(ID = 1:12, col1 = c(rep("A", 5), "B", "B", "C", "D", rep("A", 3)))

DF %>% group_by(col1) %>% mutate(col2 = ifelse(n() >= 5, col1, "other"))
user_26795180 
library(data.table)
Data <- as.data.table(Data)
Data[, N := .N, col1]
Data[, col2 := ifelse(N < 5, "other", col1)]
user_82267970

Something using the base package only:

count <- table(df$col1) > 5
col2 <- ifelse(count[df$col1], df$col1, "Other")
cbind(df, col2)

#   Id col1  col2
#1   1    A     A
#2   2    A     A
#3   3    A     A
#4   4    A     A
#5   5    A     A
#6   6    B Other
#7   7    B Other
#8   8    C Other
#9   9    D Other
#10 10    A     A
#11 11    A     A
#12 12    A     A

The dataframe:

df <- data.frame(
    Id   = seq(1, 12),
    col1 = c(rep('A', 5), 'B', 'B', 'C', 'D', rep('A', 3))
    )
user_84847790

With base R you can do it simply like this:

dat$col2 <- with(dat, replace(col1, (table(col1) < 5)[col1], 'Other'))

#dat
#    ID col1  col2
# 1   1    A     A
# 2   2    A     A
# 3   3    A     A
# 4   4    A     A
# 5   5    A     A
# 6   6    B Other
# 7   7    B Other
# 8   8    C Other
# 9   9    D Other
# 10 10    A     A
# 11 11    A     A
# 12 12    A     A
user_18613280

This would be a case where we can use if/else

library(dplyr)
DF %>%
    group_by(col1) %>%
    mutate(col2 = if(n() > 5) col1 else 'other') %>%
    ungroup

-output

# A tibble: 12 x 3
      ID col1  col2 
   <int> <chr> <chr>
 1     1 A     A    
 2     2 A     A    
 3     3 A     A    
 4     4 A     A    
 5     5 A     A    
 6     6 B     other
 7     7 B     other
 8     8 C     other
 9     9 D     other
10    10 A     A    
11    11 A     A    
12    12 A     A
user_37322710

Here is dplyr solution with an ifelse statement:

library(dplyr)
df %>% 
    mutate(col2 = ifelse(col1 =="A", "A", "Other"))

   ID col1  col2
1   1    A     A
2   2    A     A
3   3    A     A
4   4    A     A
5   5    A     A
6   6    B Other
7   7    B Other
8   8    C Other
9   9    D Other
10 10    A     A
11 11    A     A
12 12    A     A
user_133216470
Related