How to sum rows by group in R

Question

I want to consolidate rows that have the same material, date in a data set

tibble::tribble(
     ~Item,        ~Date,   ~Description, ~Quantity,
     "Saw", "01/02/2020",      "outdoor",        2L,
     "Saw", "01/02/2020",      "outdoor",        3L,
     "Saw", "01/04/2020",      "outdoor",        4L,
   "nails", "01/02/2020", "construction",        2L,
   "nails", "01/04/2020", "construction",        3L,
   "nails", "01/06/2020", "construction",        4L,
  "hammer", "01/01/2020",         "home",        1L,
  "hammer", "01/01/2020",         "home",        2L,
  "hammer", "01/01/2020",         "home",        3L
  )

Desired Result:

tibble::tribble(
     ~Item,        ~Date,   ~Description, ~Quantity,
     "Saw", "01/02/2020",      "outdoor",        5L,
     "Saw", "01/04/2020",      "outdoor",        4L,
   "nails", "01/02/2020", "construction",        2L,
   "nails", "01/04/2020", "construction",        3L,
   "nails", "01/06/2020", "construction",        4L,
  "hammer", "01/01/2020",         "home",        6L
  )

Does this answer your question? [Grouping functions (tapply, by, aggregate) and the \*apply family](https://stackoverflow.com/questions/3505701/grouping-functions-tapply-by-aggregate-and-the-apply-family) — langtang, Jul 22 '22 at 12:22
Does this answer your question? [How to sum a variable by group](https://stackoverflow.com/questions/1660124/how-to-sum-a-variable-by-group) — user438383, Jul 22 '22 at 12:56

score 2 · Accepted Answer · answered Jul 22 '22 at 12:53

I think you are just looking for

library(dplyr)

df %>% group_by(Item, Date, Description) %>% summarize(Quantity = sum(Quantity))
#> # A tibble: 6 x 4
#> # Groups:   Item, Date [6]
#>   Item   Date       Description  Quantity
#>   <chr>  <chr>      <chr>           <int>
#> 1 hammer 01/01/2020 home                6
#> 2 nails  01/02/2020 construction        2
#> 3 nails  01/04/2020 construction        3
#> 4 nails  01/06/2020 construction        4
#> 5 Saw    01/02/2020 outdoor             5
#> 6 Saw    01/04/2020 outdoor             4

^{Created on 2022-07-22 by the reprex package (v2.0.1)}

score 2 · Answer 2 · answered Jul 22 '22 at 13:53

base R option using aggregate:

library(tibble)
df <- tibble::tribble(
  ~Item,        ~Date,   ~Description, ~Quantity,
  "Saw", "01/02/2020",      "outdoor",        2L,
  "Saw", "01/02/2020",      "outdoor",        3L,
  "Saw", "01/04/2020",      "outdoor",        4L,
  "nails", "01/02/2020", "construction",        2L,
  "nails", "01/04/2020", "construction",        3L,
  "nails", "01/06/2020", "construction",        4L,
  "hammer", "01/01/2020",         "home",        1L,
  "hammer", "01/01/2020",         "home",        2L,
  "hammer", "01/01/2020",         "home",        3L
)

aggregate(Quantity ~ Item + Date + Description, df, sum)
#>     Item       Date  Description Quantity
#> 1  nails 01/02/2020 construction        2
#> 2  nails 01/04/2020 construction        3
#> 3  nails 01/06/2020 construction        4
#> 4 hammer 01/01/2020         home        6
#> 5    Saw 01/02/2020      outdoor        5
#> 6    Saw 01/04/2020      outdoor        4

^{Created on 2022-07-22 by the reprex package (v2.0.1)}

How to sum rows by group in R

2 Answers2