如何根据其他列查找一个列的不同值

Question

我有一个如下的数据框

col1    col2    col3
A       Z       10
A       Y       8
A       Z       15
B       X       11
B       Z       7
C       Y       10
D       Z       11
D       Y       14
D       L       16

我需要选择每个不同的col1中，哪些col2具有max(col3)

输出数据框应该如下所示，

col1    col2    col3
A       Z       15
B       X       11
C       Y       10
D       L       16

如何在R或SQL中完成此操作。

提前感谢。

- Manoj G

---

你想如何处理平局？ - talat

另外library(data.table) ; unique(setorder(setDT(df), -col3), by = "col1")，但正如提到的那样，不清楚您想如何处理并列情况。 - David Arenburg

对于一种仅在每个分组中返回单个行（即使存在平局的情况）的dplyr方法，请参见https://dev59.com/VWEi5IYBdhLWcg3wZ7q8。 - talat

@docendo-discimus：只要有人来参加，剩下的95%都不重要了。 - Manoj G

@DavidArenburg: 感谢您的解决方案 :) - Manoj G

Answer 1

我们可以使用data.table。将“data.frame”转换为“data.table”（setDT(df1)），按“col1”分组，根据“col3”的最大值的索引，我们可以在数据表中子集化（.SD）。

library(data.table)
setDT(df1)[, .SD[which.max(col3)], col1]
#     col1 col2 col3
#1:    A    Z   15
#2:    B    X   11
#3:    C    Y   10
#4:    D    L   16

---

或者我们可以在按'col1'分组后使用dplyr中的top_n函数。

library(dplyr)
df1 %>%
      group_by(col1) %>%
      top_n(1)

- akrun

---

非常感谢您的快速回复。 :) 由于您的回答速度太快，系统还没有允许我接受您的答案。 - Manoj G

@akrun：我在一个包含约700万行的数据框中应用了data.table解决方案，其中大约有470万个唯一的col1，但我得到的输出只有150万行。您有任何想法出了什么问题吗？ - Manoj G

@akrun：没有缺失值。数据很干净。col1和col2是有超过10位数字的数字，因此存储为e+。在我将它们都转换为字符并运行后，就没问题了。但速度非常慢。我认为使用字符时，data.table更慢！ - Manoj G

@ManojG 我认为你可以加载 library(bit64)，然后使用 data.table 中的 ?fread 读取数据集。该列将被转换为 integer64 类。 - akrun

@akrun：谢谢！我会试一下 :) - Manoj G

Answer 2

SQL答案：

使用NOT EXISTS，如果没有其他具有更高的col3值的相同col1值的行，则返回一行。

select *
from tablename t1
where not exists (select 1 from tablename t2
                  where t2.col1 = t1.col1
                    and t2.col3 > t1.col3)

如果c3的最大值相等，将返回col1的两行数据。

- jarlh

Answer 3

MySQL中另一种方法。

这里是SQLFiddle演示

输出 : =>

SELECT T1.*
FROM
table_name T1
INNER JOIN 
(SELECT col1,MAX(col3) AS Max_col3 FROM table_name GROUP BY col1) T2 
            ON T1.`col1` = T2.`col1` and T2.`Max_col3`=t1.`col3`

希望这能帮到您。

- Subin Chalil

---

如果你从子查询中删除col2，那么你的答案将符合ANSI SQL标准且可移植！ - jarlh

@SubinCPoonamgode：感谢您的解决方案，但是在出现并列情况时，它会给出多行吧！ - Manoj G

是的，它将返回多行。当出现平局时该怎么办？我们可以根据您的要求进行相应处理。 - Subin Chalil

@SubinCPoonamgode 如果有平局，我需要其中任意一行，无论是哪一行。 - Manoj G