R: rbind a row replacing existing rows in a data frame

Question

I have the following df, with redundant column3 values "bbb" and "ddd"

  col1 col2 col3
    u1    1  aaa
    u1    1  bbb
    u1    1  bbb
    u1    1  bbb
    u1    1  ccc
    u1   -1  ddd
    u1   -1  ddd

I wish to create the following df, where col3-redundant rows are replace by a single row with col2 = SUM of replaced rows:

col1 col2 col3
    u1    1  aaa
    u1    3  bbb
    u1    1  ccc
    u1   -2  ddd

Thanks in advance

Answer 1

Try

library(dplyr)
df %>% 
      group_by(col1, col3) %>% 
      summarise(col2=sum(col2))

 #   col1 col3 col2
 #1   u1  aaa    1
 #2   u1  bbb    3
 #3   u1  ccc    1
 #4   u1  ddd   -2

Or using data.table

library(data.table)
setDT(df)[, list(col2=sum(col2)), by=list(col1, col3)]

Or using sqldf

library(sqldf)
sqldf('SELECT col1, col3, 
        sum(col2) as col2 
        from df
        group by col1, col3')
 #   col1 col3 col2
 #1   u1  aaa    1
 #2   u1  bbb    3
 #3   u1  ccc    1
 #4   u1  ddd   -2

Or using base R

aggregate(.~col1+col3, df, sum)
#   col1 col3 col2
#1   u1  aaa    1
#2   u1  bbb    3
#3   u1  ccc    1
#4   u1  ddd   -2