how to merge and sum in a table with multi-variable

Question

I am having problem with merging duplicate name in a table like:

Item    Class   A   B   C
AA  Apple   1   2   4
BB  Apple   2   8   9
CC  Pear    7   9   10
DD  Orange  8   9   0
EE  Pear    10  8   2
FF  Orange  20  12  3

The Item column is identical, the name in class is duplicate, I would like to merge the duplicate names in Class column and sum up their corresponding values in column A B C, like:

Class   A   B   C
Apple   3   10  13
Pear    17  15  12
Orange  28  21  3

Any suggestion or help is appreciate

Multiple ways to do, base R way is `aggregate(.~Class, df[-1], sum)` — Ronak Shah, May 16 '18 at 04:03

score 0 · Accepted Answer · answered May 16 '18 at 04:02

We can select out the 'Item', group by 'Class' and get the sum of all the columns with summarise_all

library(dplyr)
df1 %>% 
   select(-Item) %>% 
   group_by(Class) %>%
   summarise_all(sum)
# A tibble: 3 x 4
#  Class      A     B     C
#  <chr>  <int> <int> <int>
#1 Apple      3    10    13
#2 Orange    28    21     3
#3 Pear      17    17    12

Or with rowsum from base R

rowsum(df1[3:5], group = df1$Class)

how to merge and sum in a table with multi-variable

1 Answers1