如何在同一DataFrame内按组对比行数据？附R语言示例数据

看起来你需要在DataFrame里按组（比如客户编号+订单编号）来对比行数据，我结合你给出的示例数据（先补全了不完整的returned列），用你加载的dplyr和data.table两种工具来演示具体实现方法：

首先先补全示例数据（原数据的returned列缺失部分值，这里做了合理补充，方便演示）：

library(dplyr)
library(data.table)

df <- data.frame(
  customernumber = c("111", "111", "111", "111", "111","222", "222", "222", "222", "222", "222", "222"),
  ordernumber = c("1", "1", "1", "2", "2", "1", "1", "1", "1", "2", "2", "3"),
  article = c("JeansA", "JeansA", "ShirtA", "JeansA", "JeansB", "ShirtA", "ShirtB", "ShirtB", "JeansA", "JeansB", "ShirtA", "JeansB"),
  size = c("40", "42", "40", "42", "44", "36", "36", "40", "40", "38", "44", "36"),
  returned = c("1", "1", "0", "0", "1", "0", "1", "0", "0", "1", "0", "1")
)

方法一：用dplyr实现分组对比 #

1. 找出组内重复商品的属性差异 #

如果你想知道同一客户同一订单下，同一个商品是否有不同的尺码或退货状态，可以按customernumber+ordernumber+article分组，然后添加标记列：

df %>%
  group_by(customernumber, ordernumber, article) %>%
  mutate(
    # 统计该商品在当前订单里的出现次数
    duplicate_count = n(),
    # 标记该商品在订单里是否有多个尺码
    has_size_variation = n_distinct(size) > 1,
    # 标记该商品在订单里是否有不同的退货状态
    has_return_variation = n_distinct(returned) > 1
  ) %>%
  ungroup()

这段代码会给每一行新增三个列，帮你快速判断组内的属性差异情况。

2. 把每行和组内基准行对比 #

如果你想直接把组内的每一行和组内的基准行（比如组内第一行）做对比，看看哪些属性不一样，可以这么写：

df %>%
  group_by(customernumber, ordernumber) %>%
  mutate(
    # 取组内第一行的属性作为基准
    base_article = first(article),
    base_size = first(size),
    base_returned = first(returned),
    # 对比当前行和基准行的差异
    article_differs = article != base_article,
    size_differs = size != base_size,
    returned_differs = returned != base_returned
  ) %>%
  ungroup()

这样就能清晰看到每一行和同订单内第一行的属性差异了。

方法二：用data.table实现分组对比 #

如果你的数据量很大，data.table的效率会更高，实现类似逻辑的代码如下：

1. 标记组内商品的属性差异 #

setDT(df)

df[, c("duplicate_count", "has_size_variation", "has_return_variation") := .(
  .N,
  uniqueN(size) > 1,
  uniqueN(returned) > 1
), by = .(customernumber, ordernumber, article)]

2. 每行与组内基准行对比 #

df[, c("base_article", "base_size", "base_returned", "article_differs", "size_differs", "returned_differs") := .(
  first(article),
  first(size),
  first(returned),
  article != first(article),
  size != first(size),
  returned != first(returned)
), by = .(customernumber, ordernumber)]

扩展：筛选出有差异的记录 #

如果你只想看存在属性差异的记录，比如同一订单同一商品有不同尺码的行，可以在上述代码基础上添加筛选：

用dplyr：

df %>%
  group_by(customernumber, ordernumber, article) %>%
  mutate(has_size_variation = n_distinct(size) > 1) %>%
  filter(has_size_variation) %>%
  ungroup()

用data.table：

df[, has_size_variation := uniqueN(size) > 1, by = .(customernumber, ordernumber, article)]
df[has_size_variation == TRUE]

同理，你可以把size换成returned，筛选退货状态有差异的记录。

内容的提问来源于stack exchange，提问作者Ditzgewizzle