在R中基于其他变量匹配调整销售日期并聚合销售数据

我有一个包含销售信息的数据集，其中负数的salesvalue代表贷项通知单。需要将这些贷项通知单的salesdate替换为其他所有字段完全匹配的原始销售记录（salesvalue>0）的salesdate，之后进行数据聚合。

示例数据：

data <- data.frame(number = 1:4,               
                   Date=c("02-01-2022", "06-01-2022", "07-01-2022", "06-01-2022"),
                   state = c("filet", "filet", "whole", "filet"),
                   specie = c("haddock", "cod", "haddock", "cod"),
                   salesvalue= c(300,500,200,-400),
                   salesdate=c("01-01-2022", "05-01-2022", "06-01-2022", "12-01-2022"))

原代码的问题 #

原代码分组时包含了salesvalue（示例中写为value），导致正负值被分到不同组，负数组内没有salesvalue>0的记录，因此无法匹配到对应的原始销售日期，最终返回NA。

解决方案 #

步骤1：修正贷项通知单的销售日期 #

按Date、state、specie（即除salesvalue和salesdate外的匹配字段）分组，为每组内的负数记录替换salesdate为组内正数记录的日期：

library(dplyr)

# 修正销售日期
data_fixed <- data %>%
  group_by(Date, state, specie) %>%
  mutate(
    # 提取组内所有正数记录的销售日期，取第一个（若有多个可根据需求调整）
    original_sales_date = first(salesdate[salesvalue > 0]),
    # 替换负数记录的销售日期，正数记录保留原日期
    salesdate = if_else(
      salesvalue < 0 & !is.na(original_sales_date),
      original_sales_date,
      salesdate
    )
  ) %>%
  ungroup()

运行后，编号4的salesdate会被修正为05-01-2022，与编号2的原始记录匹配。

步骤2：数据聚合 #

按修正后的salesdate及匹配字段分组，汇总销售金额：

# 聚合数据
data_aggregated <- data_fixed %>%
  group_by(Date, state, specie, salesdate) %>%
  summarise(total_salesvalue = sum(salesvalue), .groups = "drop")

最终聚合结果：

# A tibble: 3 × 5
  Date       state specie  salesdate  total_salesvalue
  <chr>      <chr> <chr>   <chr>                 <dbl>
1 02-01-2022 filet haddock 01-01-2022              300
2 06-01-2022 filet cod      05-01-2022              100
3 07-01-2022 whole haddock 06-01-2022              200

特殊情况处理 #

如果存在没有对应正数记录的负数贷项通知单，可以保留其原销售日期，避免出现NA：

data_fixed_safe <- data %>%
  group_by(Date, state, specie) %>%
  mutate(
    positive_dates = salesdate[salesvalue > 0],
    salesdate = if_else(
      salesvalue < 0 & length(positive_dates) > 0,
      first(positive_dates),
      salesdate
    )
  ) %>%
  select(-positive_dates) %>%
  ungroup()

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