合并具有相同多列值的行

Question

我是一名有用的助手，可以翻译文本。

我有一个包含多行和列的Excel文件（13232行和18列）。最后一列给出了某个值。我想做的是 - 找到具有相同列细节但最后一个不同的行并总结它们的最后一列的值。

例如： 如果输入是

+---------+---------+---------+---------+
| Column1 | Column2 | Column3 | Column4 |
+---------+---------+---------+---------+
| ABC     | DEF     | GHI     |       5 |
| XYZ     | PQR     | LMN     |       4 |
| ABC     | DEF     | GHI     |      11 |
| Test1   | Test2   | Test3   |      12 |
| XYZ     | PQR     | LMN     |      54 |
+---------+---------+---------+---------+

输出结果应该是

+---------+---------+---------+---------+
| Column1 | Column2 | Column3 | Column4 |
+---------+---------+---------+---------+
| ABC     | DEF     | GHI     |      16 |
| XYZ     | PQR     | LMN     |      58 |
| Test1   | Test2   | Test3   |      12 |
+---------+---------+---------+---------+

如何在R中实现此功能？

- user2823813

Answer 1

您可以使用来自基础R的aggregate

 aggregate(Column4~., df1, FUN=sum)
 #    Column1 Column2 Column3 Column4
 #1     ABC     DEF     GHI      16
 #2     XYZ     PQR     LMN      58
 #3   Test1   Test2   Test3      12

或者

 library(data.table)
 setDT(df1)[, list(Column4=sum(Column4)), by = c(names(df1)[1:3])]
 #     Column1 Column2 Column3 Column4
 #1:     ABC     DEF     GHI      16
 #2:     XYZ     PQR     LMN      58
 #3:   Test1   Test2   Test3      12

或者

 library(sqldf)
 sqldf('select Column1, Column2, Column3,
          sum(Column4) as Column4
          from df1 
          group by Column1, Column2, Column3')
 #   Column1 Column2 Column3 Column4
 #1     ABC     DEF     GHI      16
 #2   Test1   Test2   Test3      12
 #3     XYZ     PQR     LMN      58

或者

library(dplyr)
df1 %>% group_by(Column1, Column2, Column3) %>%
  summarize(Column4 = sum(Column4))
# Source: local data frame [3 x 4]
# Groups: Column1, Column2

#   Column1 Column2 Column3 Column4
# 1     ABC     DEF     GHI      16
# 2   Test1   Test2   Test3      12
# 3     XYZ     PQR     LMN      58

---

可重复的数据:

df1 <-
structure(list(Column1 = structure(c(1L, 3L, 1L, 2L, 3L), .Label = c("ABC", 
"Test1", "XYZ"), class = "factor"), Column2 = structure(c(1L, 
2L, 1L, 3L, 2L), .Label = c("DEF", "PQR", "Test2"), class = "factor"), 
    Column3 = structure(c(1L, 2L, 1L, 3L, 2L), .Label = c("GHI", 
    "LMN", "Test3"), class = "factor"), Column4 = c(5L, 4L, 11L, 
    12L, 54L)), .Names = c("Column1", "Column2", "Column3", "Column4"
), class = "data.frame", row.names = c(NA, -5L))

- akrun

---

我不知道你可以这么直接地使用聚合函数。太好了。我以为你需要给它一个更明确的变量。 - Brandon Bertelsen

@BrandonBertelsen 我们可以使用公式方法或者你展示的列表方法。感谢您的评论。 - akrun