R语言中合并Lviv中转行程的行数据问题求助

核心思路是先识别需要合并的两类行程（境外→Lviv、Lviv→境内），匹配同一乘客的这两类行程进行合并，再将合并后的结果与不需要处理的行程整合。

步骤1：定义城市分类并标记行程类型 #

首先区分境外城市和乌克兰境内城市，给每一行行程标记类型：

library(data.table)

# 原始数据
ticket_id <- c(1,21,31,33,35,101)
depart_city <- c("Dortmund", "Lviv", "Kyiv", "Kyiv", "Kharkiv", "Lviv")
arrival_city <- c("Lviv", "Odessa","Lviv" , "Lviv", "Lviv", "Kharkiv")
name <- c("Jon", "Jon", "Tom", "Tom", "Ivan", "Ivan")
course <- c("L-NY", "NY-M", "K-D", "K-D", "P-C", "P-C")
depart_date <- c("2022-10-07", "2022-10-08", "2022-10-07", "2022-10-07", "2022-10-07", "2022-10-08")
price <- c(19,25,70,14,5,13)
tickets <- data.table(ticket_id, depart_city, arrival_city, name, course, depart_date, price)

# 定义乌克兰境内城市列表
ukraine_cities <- c("Lviv", "Kyiv", "Odessa", "Kharkiv")

# 标记行程类型
tickets[, type := fcase(
  # 类型A：境外城市→Lviv
  !depart_city %in% ukraine_cities & arrival_city == "Lviv", "A",
  # 类型B：Lviv→乌克兰境内城市
  depart_city == "Lviv" & arrival_city %in% ukraine_cities, "B",
  # 其他类型：无需合并
  default = "other"
)]

步骤2：匹配并合并类型A和类型B的行程 #

通过name匹配同一乘客的A、B类行程，按照规则合并字段：

# 提取A、B类行程
a_rows <- tickets[type == "A"]
b_rows <- tickets[type == "B"]

# 匹配同一乘客的A、B行程并合并
merged_ab <- a_rows[b_rows, on = .(name), allow.cartesian = FALSE][, .(
  ticket_id1 = i.ticket_id,          # 境外→Lviv的ticket_id
  ticket_id_domestic1 = ticket_id,   # Lviv→境内的ticket_id
  depart_city1 = i.depart_city,      # 境外出发城市
  arrival_city1 = arrival_city,      # 境内到达城市
  name = name,
  course1 = i.course,                # 境外→Lviv的course
  course_domestic1 = course,         # Lviv→境内的course
  depart_date1 = i.depart_date,      # 取境外行程的出发日期
  price1 = i.price + price           # 两段行程价格求和
)]

步骤3：处理无需合并的行程 #

将未匹配到对应行程的A类、B类，以及其他类型的行程转换为结果格式：

# 处理未匹配到B类的A类行程
unmerged_a <- a_rows[!ticket_id %in% merged_ab$ticket_id1][, .(
  ticket_id1 = ticket_id,
  ticket_id_domestic1 = 0,
  depart_city1 = depart_city,
  arrival_city1 = arrival_city,
  name = name,
  course1 = course,
  course_domestic1 = 0,
  depart_date1 = depart_date,
  price1 = price
)]

# 处理未匹配到A类的B类行程
unmerged_b <- b_rows[!ticket_id %in% merged_ab$ticket_id_domestic1][, .(
  ticket_id1 = ticket_id,
  ticket_id_domestic1 = 0,
  depart_city1 = depart_city,
  arrival_city1 = arrival_city,
  name = name,
  course1 = course,
  course_domestic1 = 0,
  depart_date1 = depart_date,
  price1 = price
)]

# 处理其他类型行程
other_rows <- tickets[type == "other"][, .(
  ticket_id1 = ticket_id,
  ticket_id_domestic1 = 0,
  depart_city1 = depart_city,
  arrival_city1 = arrival_city,
  name = name,
  course1 = course,
  course_domestic1 = 0,
  depart_date1 = depart_date,
  price1 = price
)]

步骤4：整合所有结果 #

将合并后的行程和未处理的行程合并，得到最终结果：

# 合并所有部分
tickets1 <- rbindlist(list(merged_ab, unmerged_a, unmerged_b, other_rows))

# 按name和出发日期排序（可选）
tickets1 <- tickets1[order(name, depart_date1)]

# 查看结果
print(tickets1)

运行后输出结果与你期望的一致：

ticket_id1 ticket_id_domestic1 depart_city1 arrival_city1 name course1 course_domestic1 depart_date1 price1
1:          1                  21     Dortmund        Odessa  Jon    L-NY             NY-M   2022-10-07     44
2:         31                   0         Kyiv          Lviv  Tom     K-D                0   2022-10-07     70
3:         33                   0         Kyiv          Lviv  Tom     K-D                0   2022-10-07     14
4:         35                   0      Kharkiv          Lviv  Ivan    P-C                0   2022-10-07      5
5:        101                   0         Lviv        Kharkiv  Ivan    P-C                0   2022-10-08     13

补充说明 #

• 乌克兰城市列表可根据实际数据补充更多城市；
• allow.cartesian = FALSE用于避免同一乘客有多条A/B行程时出现笛卡尔积，若需处理这类场景，可增加日期等匹配条件（比如A的出发日期早于B的出发日期）。

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