How to sum rows by group in R-CodePudding

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
  )

CodePudding user response：

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)}

CodePudding user response：

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)}