Using R to group data by class then get sum of all columns

user_8044213 0
Viewed 123

I have data that looks like this:

NAME     TYPE        REP     PRODUCT1   PRODUCT2  ...  PRODUCT30
Name1    Wholesale   HA      4500.00    0.00      ...  760.00
Name2    Retail      BM      89.00      0.00      ...  0.00
Name3    Wholesale   HA      1000.00    800.00    ...  0.00
Name4    Wholesale   BM      3000.00    100.00    ...  0.00
Name5    Wholesale   BM      2000.00    0.00      ...  0.00

I want to create a summary table that has the total sales of PRODUCT1 thru PRODUCT30 for each REP and TYPE. So, the result would be this

REP   TYPE       TOTAL1    TOTAL2  ...  TOTAL30
HA    Wholesale  5500.00   800.00       760.00
BM    Retail     89.00     0.00         0.00
BM    Wholesale  5000.00   100.00       0.00

I have tried different variations of the code below to no avail

groupedsales <- group_by(sales, REP, TYPE) %>% summarise_all(sum())

I know that I can get it to work by using 

groupedsales <- group_by(sales, REP, TYPE) %>%    summarise(TOTAL1=sum(PRODUCT1), TOTAL2=sum(PRODUCT2), ... TOTAL30=sum(PRODUCT30)

But I am thinking there must be an easier way to just get a sum of every column. Please help!

dplyr r
2 Answers

We can use aggregate from base R

aggregate(.~ REP + TYPE, df[-1], sum)

Or with summarise_at from dplyr

library(dplyr)
df %>%
   group_by(REP, TYPE) %>%
   summarise_at(vars(starts_with("PRODUCT")), sum) %>%
   rename_at(3:ncol(.), ~ paste0("TOTAL", seq_len(ncol(df) - 3)))
user_37322710

Using data.table you can try

df <- read.table(header = TRUE, stringsAsFactors = FALSE, text = "NAME     TYPE        REP     PRODUCT1   PRODUCT2   PRODUCT30
Name1    Wholesale   HA      4500.00    0.00      760.00
                 Name2    Retail      BM      89.00      0.00       0.00
                 Name3    Wholesale   HA      1000.00    800.00     0.00
                 Name4    Wholesale   BM      3000.00    100.00     0.00
                 Name5    Wholesale   BM      2000.00    0.00       0.00")
library(data.table)
setDT(df)[,lapply(.SD,sum),by=.(TYPE, REP),.SDcols = names(df)[4:6]]

#returns
        TYPE REP PRODUCT1 PRODUCT2 PRODUCT30
1: Wholesale  HA     5500      800       760
2:    Retail  BM       89        0         0
3: Wholesale  BM     5000      100         0
user_43633150
Related