Remove duplicate element within a row in a specific column

user_12559770 0
Viewed 718

I have a dataframe such as ;

COL1  COL2
A,A,A 2
B     1
C,C   4
D,D,D 1
A     4
F     2
C,C   1

And I would like to first remove duplicate within COL1 and get:

COL1  COL2
A     2
B     1
C     4
D     1
A     4
F     2
C     1

and then sum the same COL1 letter by the COL2 values and get :

COL1  COL2
A     6
B     1
C     5
D     1
F     2

Does someone have an idea, please? Here is the dataframe if it can helps:

structure(list(COL1 = structure(c(2L, 3L, 4L, 5L, 1L, 6L, 4L), .Label = c("A", 
"A,A,A", "B", "C,C", "D,D,D", "F"), class = "factor"), COL2 = c(2, 
1, 4, 1, 4, 2, 1)), class = "data.frame", row.names = c(NA, -7L
))
dplyr subset dataframe r regex
9 Answers

A base R option

aggregate(
  COL2 ~ .,
  transform(
    df,
    COL1 = gsub(",.*", "", COL1)
  ),
  sum
)

gives

  COL1 COL2
1    A    6
2    B    1
3    C    5
4    D    1
5    F    2
user_121587570

You can use separate_rows to split the data on comma in different rows, keep only unique values and aggregate.

library(dplyr)
library(tidyr)

df %>%
  mutate(row = row_number()) %>%
  separate_rows(COL1, sep = ',\\s*') %>%
  distinct(row, COL1, .keep_all = TRUE) %>%
  group_by(COL1) %>%
  summarise(COL2 = sum(COL2, na.rm = TRUE))

#  COL1   COL2
#  <chr> <dbl>
#1 A         6
#2 B         1
#3 C         5
#4 D         1
#5 F         2
user_39629140

An optoin with trimws

library(dplyr)
df1 %>%
     group_by(COL1 = trimws(COL1, whitespace = ",.*")) %>% 
     summarise(COL2 = sum(COL2), .groups = 'drop')
# A tibble: 5 x 2
  COL1   COL2
  <chr> <dbl>
1 A         6
2 B         1
3 C         5
4 D         1
5 F         2
user_37322710

One option could be:

df %>%
 group_by(COL1 = gsub(",.*$", "", COL1)) %>%
 summarise(COL2 = sum(COL2))

  COL1   COL2
  <chr> <dbl>
1 A         6
2 B         1
3 C         5
4 D         1
5 F         2

If multiple non-duplicated elements could be present per rows:

df %>%
 group_by(COL1 = sapply(strsplit(as.character(COL1), ",", fixed = TRUE), function(x) toString(unique(x)))) %>%
 summarise(COL2 = sum(COL2))
user_59645570

You can str_extract based on [:alpha:]{1}, this will extract the first letter in the sequence, and then continue with group_by and summarise

data %>% 
        mutate(COL1 = str_extract(COL1,pattern = "[:alpha:]{1}")) %>% 
        group_by(COL1) %>% 
        summarise(COL2 = sum(COL2, na.rm = TRUE))

It gives the following output,

# A tibble: 5 x 2
  COL1   COL2
  <chr> <dbl>
1 A         6
2 B         1
3 C         5
4 D         1
5 F         2
user_104007910

We could use substring

library(dplyr)
df %>% 
    mutate(COL1 = substring(COL1, 1,1)) %>% 
    group_by(COL1) %>% 
    summarise(COL2 = sum(COL2))

Output:

  COL1   COL2
  <chr> <dbl>
1 A         6
2 B         1
3 C         5
4 D         1
5 F         2
user_133216470

You can use cSplit from splitstackshape as well

df %>% cSplit("COL1", ",", "long") %>%
  unique() %>% group_by(COL1) %>% summarise(COL2 = sum(COL2))

  COL1   COL2
  <chr> <dbl>
1 A         6
2 B         1
3 C         5
4 D         1
5 F         2
user_115939920

By using aggregate function in BaseR,

setNames(
    aggregate(df[,2] ,list(sub(",.*","",df[,1])),sum)
    , c("COL1","COL2"))

gives,

  COL1 COL2
1    A    6
2    B    1
3    C    5
4    D    1
5    F    2
user_72243540

Base R solution:

with(
  df, 
  aggregate(
    list(COL2 = COL2), 
    by = list(COL1 = gsub(
      "^(\\w).*", 
      "\\1", 
      COL1
      )
    ), 
    FUN = sum
  )
)
user_92031580
Related