Summarising duplicates in dataframe in R

Question

I have a dateframe with the following data:

#sample data
Date    <-  c(  "2020-01-01",   "2020-01-01",   "2020-01-01",   "2020-01-01",   "2020-01-01",   "2020-01-02",   "2020-01-02",   "2020-01-02",   "2020-01-02")
Salesperson <-c (   "Sales1",   "Sales1",   "Sales1",   "Sales2",   "Sales2",   "Sales1",   "Sales1",   "Sales2",   "Sales2"    )
Clothing    <-c (   "5",    "2",    "8",    "3",    "3",    "4",    "7",    "3",    "4" )
Electronics <-c (   "6",    "9",    "1",    "2",    "1",    "2",    "2",    "1",    "2" )

data<-data.frame(Date,Salesperson,Clothing,Electronics, stringsAsFactors = FALSE)
data$Date<-as.Date(data$Date,"%Y-%m-%d")

There are rows in the df where a salesperson have recorded their sales multiple times for the same date rather than adding them up.

The result I want is shown by the dataframe below:

Date    <-  c   (   "2020-01-01",   "2020-01-01",   "2020-01-02",   "2020-01-02"    )
Salesperson <-  c   (   "Sales1",   "Sales2",   "Sales1",   "Sales2")
Clothing    <-  c   (   "15",   "6",    "11",   "7" )
Electronics <-  c   (   "16",   "3",    "4",    "3" )
data1<-data.frame(Date,Salesperson,Clothing,Electronics, stringsAsFactors = FALSE)

Does anyone know how to achieve this result?

score 3 · Accepted Answer · answered Jan 05 '21 at 12:33

To summarise your data, you need the numbers to be passed as numbers, not strings. See I added as.numeric() in front of your Clothing and Electronics variables:

Clothing    <-as.numeric(c (   "5",    "2",    "8",    "3",    "3",    "4",    "7",    "3",    "4" ))
Electronics <-as.numeric(c (   "6",    "9",    "1",    "2",    "1",    "2",    "2",    "1",    "2" ))

Now, to summarise using the sum, try:

library(dplyr)
data %>% 
 group_by(Date, Salesperson) %>%
 summarise(sum_cloth=(sum(Clothing)), sum_elec=sum(Electronics))

# Groups:   Date [2]
  Date       Salesperson sum_cloth sum_elec
  <chr>      <chr>           <dbl>    <dbl>
1 2020-01-01 Sales1             15       16
2 2020-01-01 Sales2              6        3
3 2020-01-02 Sales1             11        4
4 2020-01-02 Sales2              7        3

Summarising duplicates in dataframe in R

1 Answers1