在R中按产品价格层级生成指定格式Data Frame的技术问询

我们可以利用dplyr的分组与条件判断逻辑来实现需求，核心是按产品、价格分组后，针对每个字段分别应用规则：

library(dplyr)

df2 <- df %>%
  group_by(Product) %>%
  # 标记当前产品的最低价格
  mutate(min_price = min(Price)) %>%
  # 按产品+价格分组，标记每组内的行号
  group_by(Product, Price, .add = TRUE) %>%
  mutate(price_group_row = row_number()) %>%
  ungroup() %>%
  # 逐个字段处理
  mutate(
    # Product字段：仅在产品首行、价格变更行显示
    Product = case_when(
      row_number() == 1 | lag(Product) != Product | lag(Price) != Price ~ Product,
      TRUE ~ ""
    ),
    # Size字段：最低价格组全为空，非最低价格组保留原始Size
    Size = case_when(
      Price != min_price ~ Size,
      TRUE ~ ""
    ),
    # Price字段：最低价格组仅首行显示，非最低价格组全部显示
    Price = case_when(
      Price == min_price & price_group_row == 1 ~ Price,
      Price != min_price ~ Price,
      TRUE ~ ""
    )
  ) %>%
  # 清理辅助计算列
  select(-min_price, -price_group_row)

规则对应说明 #

1. 最低价格首行显示Product和Price，Size为空：通过Price == min_price & price_group_row == 1控制Price显示，最低价格组的Size统一设为空，Product在产品首行显示。
2. 同产品同最低价格的后续行全为空：最低价格组中price_group_row > 1的行，Product和Price设为空，Size保持为空。
3. 价格变更后显示Product、Size和Price：价格变更时（lag(Price) != Price）显示Product，非最低价格组保留原始Size和Price。
4. 每个产品重复执行规则：通过group_by(Product)实现分组独立处理。

原始数据集 #

# original data set
df <- data.frame(
  Product = c("6816","6816","6816", 
              "6817","6817","6817","6817","6817","6817", 
              "6818","6818","6818","6818","6818", 
              "6819","6819","6819","6819","6819","6819", 
              "6820","6820","6820","6820","6820","6820", 
              "6821","6821","6821","6821","6821", 
              "6822","6822","6822","6822","6822", 
              "6823","6823","6823","6823","6823","6823","6823", 
              "6824","6824","6824","6824","6824"),
  Size = c("1","2","3",
           "1","2","3","4","5","6",
           "1","2","3","4","5", 
           "1","2","3","4","5","6",
           "1","2","3","4","5","6", 
           "1","2","3","4","5", 
           "1","2","3","4","5", 
           "1","2","3","4","5","6","7", 
           "1","2","3","4","5"),
  Price = c("200","200","200", 
            "400","400","400","500","500","550", 
            "300","300","300","600","650",
            "100", "100", "100", "100","200","200", 
            "150","150","150","150","250","250", 
            "400","400","400","700","750",
            "400","400","400","700","750", 
            "200","200","200","400","400","400","500", 
            "100", "100", "100","100","200")
)

期望结果 #

# expected result 
df2_expected <- data.frame(
  Product = c("6816","","",
              "6817","","","6817","6817","6817",
              "6818","","","6818","6818",
              "6819","","","","6819","6819",
              "6820","","","","6820","6820",
              "6821","","","6821","6821",
              "6822","","","6822","6822",
              "6823","","","6823","6823","6823","6823",
              "6824","","","","6824"),
  Size = c("","","",
           "","","","4","5","6",
           "","","","4","5",
           "","","","","5","6",
           "","","","","5","6",
           "","","","4","5",
           "","","","4","5",
           "","","","4","5","6","7",
           "","","","","5"),
  Price = c("200","","",
            "400","","","500","500","550",
            "300","","","600","650",
            "100","","","","200","200",
            "150","","","","250","250",
            "400","","","700","750",
            "400","","","700","750",
            "200","","","400","400","400","500",
            "100","","","","200") 
)

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