Difference by Group using dplyr and data.table

Question

I want to calculate difference by groups. Although I referred R: Function “diff” over various groups thread on SO, for unknown reason, I am unable to find the difference. I have tried three methods : a) spread b) dplyr::mutate with base::diff() c) data.table with base::diff(). While a) works, I am unsure how I can solve this problem using b) and c).

Description about the data: I have revenue data for the product by year. I have categorized years >= 2013 as Period 2 (called P2), and years < 2013 as Period 1 (called P1).

Sample data:

dput(Test_File)
structure(list(Ship_Date = c(2010, 2010, 2012, 2012, 2012, 2012, 
2017, 2017, 2017, 2016, 2016, 2016, 2011, 2017), Name = c("Apple", 
"Apple", "Banana", "Banana", "Banana", "Banana", "Apple", "Apple", 
"Apple", "Banana", "Banana", "Banana", "Mango", "Pineapple"), 
    Revenue = c(5, 10, 13, 14, 15, 16, 25, 25, 25, 1, 2, 4, 5, 
    7)), .Names = c("Ship_Date", "Name", "Revenue"), row.names = c(NA, 
14L), class = "data.frame")

Expected Output

dput(Diff_Table)
structure(list(Name = c("Apple", "Banana", "Mango", "Pineapple"
), P1 = c(15, 58, 5, NA), P2 = c(75, 7, NA, 7), Diff = c(60, 
-51, NA, NA)), .Names = c("Name", "P1", "P2", "Diff"), class = "data.frame", row.names = c(NA, 
-4L))

Here's my code:

Method 1: Using spread [Works]

data.table::setDT(Test_File)
cutoff<-2013
Test_File[Test_File$Ship_Date>=cutoff,"Ship_Period"]<-"P2"
Test_File[Test_File$Ship_Date<cutoff,"Ship_Period"]<-"P1"

Diff_Table<- Test_File %>%
  dplyr::group_by(Ship_Period,Name) %>%
  dplyr::mutate(Revenue = sum(Revenue)) %>%
  dplyr::select(Ship_Period, Name,Revenue) %>%
  dplyr::ungroup() %>%
  dplyr::distinct() %>%
  tidyr::spread(key = Ship_Period,value = Revenue) %>% 
  dplyr::mutate(Diff = `P2` - `P1`)

Method 2: Using dplyr [Doesn't work: NAs are generated in Diff column.]

Diff_Table<- Test_File %>%
  dplyr::group_by(Ship_Period,Name) %>%
  dplyr::mutate(Revenue = sum(Revenue)) %>%
  dplyr::select(Ship_Period, Name,Revenue) %>%
  dplyr::ungroup() %>%
  dplyr::distinct() %>%
  dplyr::arrange(Name,Ship_Period, Revenue) %>%
  dplyr::group_by(Ship_Period,Name) %>%
  dplyr::mutate(Diff = diff(Revenue))

Method 3: Using data.table [Doesn't work: It generates all zeros in Diff column.]

Test_File[,Revenue1 := sum(Revenue),by=c("Ship_Period","Name")]
Diff_Table<-Test_File[,.(Diff = diff(Revenue1)),by=c("Ship_Period","Name")]

Question: Can someone please help me with method 2 and method 3 above? I am fairly new to R so I apologize if my work sounds too basic. I am still learning this language.

Does that mean that the accepted answer gives a wrong result? — jogo
@Jogo - Good observation! I wouldn't consider akrun's answer as wrong because NA-number = NA and number - NA = NA. So, I would believe that it doesn't matter much. I created those entries to ensure that the code doesn't blow up when one of P1 or P2 are missing. Does that help? — watchtower

akrun akrun · Accepted Answer · 2017-04-26T06:57:10

We can do this with data.table. Convert the 'data.frame' to 'data.table' (setDT(Test_File)), grouped by the run-length-id of 'Name' and 'Name', get the sum of 'Revenue', reshape it to 'wide' format with dcast, get the difference between 'P2' and 'P1' and assign (:=) it to 'Diff'

library(data.table)
dcast(setDT(Test_File)[, .(Revenue = sum(Revenue)),
   .(grp=rleid(Name), Name)], Name~ paste0("P", rowid(Name)), 
        value.var = "Revenue")[, Diff := P2 - P1][]
#        Name P1 P2 Diff
#1:     Apple 15 75   60
#2:    Banana 58  7  -51
#3:     Mango  5 NA   NA
#4: Pineapple  7 NA   NA

Or for third case, i.e. base R, we create a grouping column based on whether the adjacent elements in 'Name' are the same or not ('grp'), then aggregate the 'Revenue' by 'Name' and 'grp' to find the sum, create a sequence column, reshape it to 'wide' and transform the dataset to create the 'Diff' column

Test_File$grp <- with(Test_File, cumsum(c(TRUE, Name[-1]!=Name[-length(Name)])))
d1 <- aggregate(Revenue~Name +grp, Test_File, sum)
d1$Seq <- with(d1, ave(seq_along(Name), Name, FUN = seq_along))
transform(reshape(d1[-2], idvar = "Name", timevar = "Seq", 
            direction = "wide"), Diff = Revenue.2- Revenue.1)

The tidyverse method can also be done using

library(dplyr)
library(tidyr)
Test_File %>% 
       group_by(grp = cumsum(c(TRUE, Name[-1]!=Name[-length(Name)])), Name)  %>%
       summarise(Revenue = sum(Revenue)) %>%
       group_by(Name) %>% 
       mutate(Seq = paste0("P", row_number()))  %>% 
       select(-grp) %>% 
       spread(Seq, Revenue) %>% 
       mutate(Diff = P2-P1)
 #Source: local data frame [4 x 4]
 #Groups: Name [4]

#      Name    P1    P2  Diff
#      <chr> <dbl> <dbl> <dbl>
#1     Apple    15    75    60
#2    Banana    58     7   -51
#3     Mango     5    NA    NA
#4 Pineapple     7    NA    NA

Update

Based on the OP's comments to use only diff function

library(data.table)
setDT(Test_File)[, .(Revenue = sum(Revenue)), .(Name, grp = rleid(Name))
  ][, .(P1 = Revenue[1L], P2 = Revenue[2L], Diff = diff(Revenue)) , Name]
#        Name P1 P2 Diff
#1:     Apple 15 75   60
#2:    Banana 58  7  -51
#3:     Mango  5 NA   NA
#4: Pineapple  7 NA   NA

Or with dplyr

Test_File %>% 
   group_by(grp = cumsum(c(TRUE, Name[-1]!=Name[-length(Name)])), Name)  %>%
   summarise(Revenue = sum(Revenue)) %>%
   group_by(Name) %>% 
   summarise(P1 = first(Revenue), P2 = last(Revenue)) %>%
   mutate(Diff = P2-P1)

Difference by Group using dplyr and data.table

2 Answers

Update