使用dplyr和data.table按组计算差异

Question

我想按组计算差异。虽然我参考了SO上的R: Function “diff” over various groups线程，但出于未知原因，我无法找到差异。我已经尝试了三种方法：a）spread b）使用dplyr::mutate和base::diff() c）使用data.table和base::diff()。虽然a）可以工作，但我不确定如何使用b）和c）解决这个问题。

关于数据的描述： 我有产品按年份分类的收入数据。我将年份大于等于2013年的数据归类为第二阶段（称为P2），而年份小于2013年的数据归类为第一阶段（称为P1）。

样本数据：

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

预期输出

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

这是我的代码：

方法1：使用spread [有效]

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

方法2：使用dplyr [不起作用：在Diff列中生成NAs。]

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

方法三：使用 data.table 【无效：会在 Diff 列中生成全零。】

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

问题：请问有人能帮我解决上述的第二种方法和第三种方法吗？我对R语言还比较陌生，如果我的工作听起来太基础，请见谅。我仍在学习这门语言。

- watchtower

---

Pineapple 的 P1 值应该是 NA 吗？ - jogo

是的，如果不存在。 - watchtower

这是否意味着被接受的答案给出了错误的结果？ - jogo

@Jogo - 很好的观察！我不认为akrun的答案是错误的，因为NA-number=NA和number-NA=NA。所以，我相信这并不重要。我创建这些条目是为了确保当P1或P2中的一个缺失时，代码不会崩溃。这有帮助吗？ - watchtower

Answer 1

我们可以使用 data.table 来完成这个任务。将 'data.frame' 转换为 'data.table' (setDT(Test_File))，以 'Name' 和 'Name' 的运行长度 ID 为分组条件，获取 'Revenue' 的 sum，使用 dcast 将其重塑为 'wide' 格式，获取 'P2' 和 'P1' 之间的差值并将其赋值给 '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

---

对于第三种情况，即使用原生R语言，我们根据“Name”列中相邻元素是否相同创建一个分组列（“grp”），然后按照“Name”和“grp”列对“Revenue”列进行聚合操作以求得总和，接着创建一个序列列，将其转换为宽格式，并对数据集进行转换以创建“Diff”列。

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)

---

tidyverse方法也可以使用以下方式实现：

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

更新

根据楼主的评论，只使用 diff 函数进行操作。

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

---

或者使用 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)

- akrun

---

感谢您的帮助，akrun。您真是太棒了。我有两个问题：a) 看起来您在计算差异时依赖于宽格式（wide format）。我很抱歉，但我正在寻找一种不使用宽格式的解决方案。例如，通过使用“diff”。现在，更大的问题是是否可以在不转换为宽格式的情况下实现这一点。我很感激您的想法。b) 请问您能否解释一下代码中的“.(grp=rleid(Name), Name)], Name~ paste0("P", rowid(Name))”？如果这个问题太基础了，我很抱歉。 - watchtower

如果可以使用diff并得到相同的结果，那么它会扩展我的理解。我最初想通过宽格式来解决这个问题，但我被我发现的SO线程分散了注意力。再次感谢您的所有帮助。我总是从您的帖子中学到很多。 - watchtower

快速问题：在您的“diff”解决方案中，您能否解释一下“.(Name, grp = rleid(Name)”是什么意思？我理解了“rleid”的目的，但我不确定是否会创建新列，或者您是否正在指定分组。我相信使用“by”来指定分组。提前感谢您的指导。 - watchtower

@watchtower 这是一个汇总步骤，分组列不会添加到原始数据中。您可以指定“by”，但一般格式为“data.table[i, j, by]”。 - akrun

@watchtower 不用道歉，我们都从零开始。关于你的代码，我建议使用summarise而不是mutate(Revenue = sum(Revenue)) %>%，这也将为下一步处理提供大型数据集，并使其变慢。基本原则是，如果你正在进行任何过滤等操作，则应首先执行此操作，然后进行按组和其他处理（但这取决于您想要的输出类型）。 - akrun

Answer 2

这样做可以：

library("data.table")
setDT(Test_File)
T <- Test_File[, .(P=sum(Revenue)),by=.(Ship_Date, Name)]
T[Ship_Date>=2013][T[Ship_Date<2013][CJ(Name=T$Name, unique=TRUE), on="Name"], on="Name"][,`:=`(P1=i.P, P2=P, Diff=P-i.P)][] 
#    Ship_Date      Name  P i.Ship_Date i.P P1 P2 Diff
# 1:      2017     Apple 75        2010  15 15 75   60
# 2:      2016    Banana  7        2012  58 58  7  -51
# 3:        NA     Mango NA        2011   5  5 NA   NA
# 4:      2017 Pineapple  7          NA  NA NA  7   NA

或仅使用所需的列：

T[Ship_Date>=2013][T[Ship_Date<2013][CJ(Name=T$Name, unique=TRUE), on="Name"], on="Name"][,`:=`(P1=i.P, P2=P, Diff=P-i.P)][,.(Name, P1, P2, Diff)]
#         Name P1 P2 Diff
# 1:     Apple 15 75   60
# 2:    Banana 58  7  -51
# 3:     Mango  5 NA   NA
# 4: Pineapple NA  7   NA

这里是使用setnames()的一种变体：

setnames(T[Ship_Date>=2013][T[Ship_Date<2013][CJ(Name=T$Name, unique=TRUE), on="Name"], on="Name"], 
         c("P", "i.P"), c("P2", "P1"))[, Diff:=P2-P1][]

- jogo