如何在R中按条件拆分跨月订单数据集行并分配收入

需求说明 #

• 对跨月的住宿订单，按覆盖的每个月份生成单独记录，将Revenue按对应月份的入住天数比例分配，其余字段（如channel、room_stay_status等）保持不变
• 未跨月的订单，保留原记录不变

输入数据集 #

input_data <- structure(list(
  channel = c("109", "109", "Agent"),
  room_stay_status = c("ENQUIRY", "ENQUIRY", "CHECKED_OUT"),
  start_date = structure(c(1637971200, 1640995200, 1640995200), tzone = "UTC", class = c("POSIXct", "POSIXt")),
  end_date = structure(c(1643155200, 1642636800, 1641168000), tzone = "UTC", class = c("POSIXct", "POSIXt")),
  los = c(60, 19, 2),
  booker = c("Anuj", "Anuj", "Anuj"),
  area = c("Goa", "Goa", "Goa"),
  property_sku = c("Amna-3b", "Amna-3b", "Amna-3b"),
  Revenue = c(90223.666, 5979, 7015.9),
  Booking_ref = c("aed97", "b497h9", "bde65")
), row.names = c(NA, -3L), class = c("tbl_df", "tbl", "data.frame"))

预期输出数据集 #

expected_output <- structure(list(
  channel = c("109", "109", "109", "109", "Agent"),
  room_stay_status = c("ENQUIRY", "ENQUIRY", "ENQUIRY", "ENQUIRY", "CHECKED_OUT"),
  start_date = structure(c(1637971200, 1638316800, 1640995200, 1640995200, 1640995200), tzone = "UTC", class = c("POSIXct", "POSIXt")),
  end_date = structure(c(1638230400, 1640908800, 1643155200, 1642636800, 1641168000), tzone = "UTC", class = c("POSIXct", "POSIXt")),
  los = c(4, 31, 25, 19, 2),
  booker = c("Anuj", "Anuj", "Anuj", "Anuj", "Anuj"),
  area = c("Goa", "Goa", "Goa", "Goa", "Goa"),
  property_sku = c("Amna-3b", "Amna-3b", "Amna-3b", "Amna-3b", "Amna-3b"),
  Revenue = c(6014.91106666667, 46615.5607666667, 37593.1941666667, 5979, 7015.9),
  Booking_ref = c("aed97", "aed97", "aed97", "b497h9", "bde65")
), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame"))

解决方案代码 #

使用tidyverse和lubridate包处理日期与数据拆分，代码如下：

library(tidyverse)
library(lubridate)

processed_data <- input_data %>%
  # 按单个订单处理，生成覆盖的所有月份区间
  rowwise() %>%
  mutate(
    # 生成订单覆盖的所有月份起始日期
    month_starts = seq(
      floor_date(start_date, "month"),
      floor_date(end_date, "month"),
      by = "month"
    ),
    # 确定每个月份的实际入住起始日期
    interval_start = pmax(month_starts, start_date),
    # 确定每个月份的实际入住结束日期
    interval_end = pmin(ceiling_date(month_starts, "month") - days(1), end_date)
  ) %>%
  # 将生成的多月份区间拆分为单独行
  unnest(c(month_starts, interval_start, interval_end)) %>%
  # 计算区间内入住天数与收入分配比例
  mutate(
    los_interval = as.duration(interval(interval_start, interval_end)) / days(1) + 1,
    revenue_proportion = los_interval / los,
    Revenue = Revenue * revenue_proportion
  ) %>%
  # 替换原字段为当前区间对应值，整理输出结构
  select(
    channel, room_stay_status,
    start_date = interval_start,
    end_date = interval_end,
    los = los_interval,
    booker, area, property_sku,
    Revenue, Booking_ref
  ) %>%
  # 转换入住天数为整数（按需调整）
  mutate(los = as.integer(los)) %>%
  # 按订单编号和日期排序
  arrange(Booking_ref, start_date)

# 查看处理结果
print(processed_data)

代码说明 #

• 日期区间生成：通过floor_date定位月份起始点，结合原订单起止日期，确定每个月份的实际入住区间
• 收入分配逻辑：计算每个区间的实际入住天数，按天数占总入住时长的比例分配订单总收入
• 字段整理：替换原订单的起止日期和入住时长为当前区间对应值，保留其余字段不变

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