如何按用户分组创建标识商品是否在前一日购买过的新列？
2026-6-13
问题描述
现有一个长格式表格，包含person和day两个分组列，需要新增一列was_purchased_yesterday，规则如下：
- 周一的行该列值为
NA - 其他日期，当某用户的某商品在前一日（特指前一天，非任意过去日期）购买过时，该列值为
TRUE，否则为FALSE
示例输入数据：
df_groceries <- tibble::tribble( ~person, ~day, ~groceries, "gary", "Mon", "tomatoes", "gary", "Mon", "milk", "gary", "Mon", "bread", "gary", "Mon", "yogurt", "gary", "Tue", "eggs", "gary", "Tue", "cheese", "gary", "Tue", "apples", "gary", "Wed", "chicken", "gary", "Wed", "rice", "gary", "Wed", "apples", "gary", "Thu", "lettuce", "gary", "Thu", "sauce", "gary", "Fri", "fish", "gary", "Fri", "potatoes", "gary", "Fri", "lettuce", "gary", "Sat", "cereal", "gary", "Sat", "bananas", "gary", "Sat", "juice", "gary", "Sun", "rice", "gary", "Sun", "bananas", "gary", "Sun", "cereal", "rachel", "Mon", "spinach", "rachel", "Mon", "mushrooms", "rachel", "Mon", "pasta", "rachel", "Tue", "mushrooms", "rachel", "Tue", "broccoli", "rachel", "Tue", "lemons", "rachel", "Tue", "olive oil", "rachel", "Wed", "avocados", "rachel", "Wed", "lemons", "rachel", "Thu", "chicken breast", "rachel", "Thu", "quinoa", "rachel", "Thu", "bell peppers", "rachel", "Fri", "yogurt", "rachel", "Fri", "berries", "rachel", "Fri", "granola", "rachel", "Sat", "yogurt", "rachel", "Sat", "avocados", "rachel", "Sun", "eggs", "rachel", "Sun", "orange juice", "rachel", "Sun", "whole wheat bread" )
期望输出：
df_groceries_desired_output <- tibble::tribble( ~person, ~day, ~groceries, ~was_purchased_yesterday, "gary", "Mon", "tomatoes", NA, "gary", "Mon", "milk", NA, "gary", "Mon", "bread", NA, "gary", "Mon", "yogurt", NA, "gary", "Tue", "eggs", FALSE, "gary", "Tue", "cheese", FALSE, "gary", "Tue", "apples", FALSE, "gary", "Wed", "chicken", FALSE, "gary", "Wed", "rice", FALSE, "gary", "Wed", "apples", TRUE, "gary", "Thu", "lettuce", FALSE, "gary", "Thu", "sauce", FALSE, "gary", "Fri", "fish", FALSE, "gary", "Fri", "potatoes", FALSE, "gary", "Fri", "lettuce", TRUE, "gary", "Sat", "cereal", FALSE, "gary", "Sat", "bananas", FALSE, "gary", "Sat", "juice", FALSE, "gary", "Sun", "rice", FALSE, "gary", "Sun", "bananas", TRUE, "gary", "Sun", "cereal", TRUE, "rachel", "Mon", "spinach", NA, "rachel", "Mon", "mushrooms", NA, "rachel", "Mon", "pasta", NA, "rachel", "Tue", "mushrooms", TRUE, "rachel", "Tue", "broccoli", FALSE, "rachel", "Tue", "lemons", FALSE, "rachel", "Tue", "olive oil", FALSE, "rachel", "Wed", "avocados", FALSE, "rachel", "Wed", "lemons", TRUE, "rachel", "Thu", "chicken breast", FALSE, "rachel", "Thu", "quinoa", FALSE, "rachel", "Thu", "bell peppers", FALSE, "rachel", "Fri", "yogurt", FALSE, "rachel", "Fri", "berries", FALSE, "rachel", "Fri", "granola", FALSE, "rachel", "Sat", "yogurt", TRUE, "rachel", "Sat", "avocados", FALSE, "rachel", "Sun", "eggs", FALSE, "rachel", "Sun", "orange juice", FALSE, "rachel", "Sun", "whole wheat bread", FALSE )
用户尝试的代码：
library(dplyr) df_groceries |> group_by(person) |> mutate(day_as_number = case_match(day, "Mon" ~ 1, "Tue" ~ 2, "Wed" ~ 3, "Thu" ~ 4, "Fri" ~ 5, "Sat" ~ 6, "Sun" ~ 7)) |> mutate(was_purchased_yesterday = groceries %in% groceries[day_as_number == day_as_number - 1])
错误结果：
df_groceries ## # A tibble: 41 × 5 ## # Groups: person [2] ## person day groceries day_as_number was_purchased_yesterday ## <chr> <chr> <chr> <dbl> <lgl> ## 1 gary Mon tomatoes 1 FALSE ## 2 gary Mon milk 1 TRUE ## 3 gary Mon bread 1 TRUE ## 4 gary Mon yogurt 1 TRUE ## 5 gary Tue eggs 2 FALSE ## 6 gary Tue cheese 2 TRUE ## 7 gary Tue apples 2 TRUE ## 8 gary Wed chicken 3 FALSE ## 9 gary Wed rice 3 TRUE ## 10 gary Wed apples 3 TRUE ## # ℹ 31 more rows ## # ℹ Use `print(n = ...)` to see more rows
错误原因分析
用户的实现逻辑存在两个核心问题：
- 日期关联错误：
groceries[day_as_number == day_as_number - 1]是在整个person分组里筛选行，没有针对当前行的日期，去精准匹配前一天的商品集合。比如处理周一的行时，day_as_number-1=0，虽无匹配行，但%in%会拿当前商品和空集合对比得到FALSE，而非要求的NA。 - 匹配范围错误：该写法会让当前行的商品和分组内所有满足
day_as_number == 当前day_as_number-1的商品对比，没有确保是同一用户的前一天数据（虽已group_by(person)，但日期对应关系未正确绑定）。
正确实现方法
以下提供两种简洁可靠的实现方式：
方法一：基于每日商品集合的连接匹配
先提取每个用户每天的商品列表，再通过日期关联前一天的商品集合，最后判断当前商品是否存在于前一天的列表中：
library(dplyr) # 第一步：生成每个用户每天的商品集合 daily_items <- df_groceries %>% group_by(person, day) %>% summarise(day_items = list(groceries), .groups = "drop") %>% # 给日期排序，生成前一天的映射键 mutate(day_order = match(day, c("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")), prev_day_order = day_order - 1) # 第二步：关联前一天的商品集合 daily_items_with_prev <- daily_items %>% left_join( select(daily_items, person, prev_day_order = day_order, prev_day_items = day_items), by = c("person", "prev_day_order") ) # 第三步：合并回原数据并计算目标列 df_result <- df_groceries %>% left_join(daily_items_with_prev, by = c("person", "day")) %>% mutate(was_purchased_yesterday = case_when( day == "Mon" ~ NA, TRUE ~ groceries %in% unlist(prev_day_items) )) %>% select(-day_order, -prev_day_order, -day_items, -prev_day_items)
方法二：基于分组窗口的滞后操作
利用dplyr的分组和lag函数，直接获取前一天的商品列表：
library(dplyr) df_result <- df_groceries %>% group_by(person) %>% # 将day转为有序因子，确保日期顺序正确 mutate(day = factor(day, levels = c("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"), ordered = TRUE)) %>% # 按日期分组，生成当日商品列表 group_by(person, day, .add = TRUE) %>% mutate(daily_items = list(groceries)) %>% group_by(person) %>% # 获取前一天的商品列表 mutate(prev_day_items = lag(daily_items), was_purchased_yesterday = case_when( day == "Mon" ~ NA, TRUE ~ groceries %in% unlist(prev_day_items) )) %>% # 清理中间列并恢复day的字符格式 select(-daily_items, -prev_day_items) %>% mutate(day = as.character(day)) %>% ungroup()
两种方法都能得到与期望输出完全一致的结果。
内容的提问来源于stack exchange，提问作者Emman
相关产品推荐
相关产品推荐
