如何基于n列值复制特定行并调整关联列编号与字段值

问题背景 #

现有如下航班行程数据集：

ID     departure_airport   arrival_airport   journey id  flight  is_outbound n
1      ZRH                 MSY               1           1       TRUE        2
2      MSY                 IAD               1           2       TRUE        2
3      ZRH                 LAX               2           1       TRUE        2
4      ZRH                 BUD               3           1       TRUE        1 
5      VIE                 ZRH               4           1       TRUE        1
6      ZRH                 KEF               5           1       TRUE        2
7      KEF                 BOS               5           2       TRUE        2

需要完成以下操作：

• 复制n列值为2的所有行
• 交换复制行的departure_airport与arrival_airport字段值
• 将新生成行的is_outbound设为FALSE
• 确保同一journey id下的flight列编号连续递增

期望输出结果：

ID     departure_airport   arrival_airport   journey id  flight  is_outbound n
1      ZRH                 MSY               1           1       TRUE        2
2      MSY                 IAD               1           2       TRUE        2
3      IAD                 MSY               1           3       FALSE       2
4      MSY                 ZRH               1           4       FALSE       2
5      ZRH                 LAX               2           1       TRUE        1
6      ZRH                 BUD               3           1       TRUE        1
7      VIE                 ZRH               4           1       TRUE        1
8      ZRH                 KEF               5           1       TRUE        2
9      KEF                 BOS               5           2       TRUE        2
10     BOS                 KEF               5           3       FALSE       2
11     KEF                 ZRH               5           4       FALSE       2

解决方案 #

方案1：使用R语言 + dplyr包 #

library(dplyr)

# 构造原始数据框
df <- data.frame(
  ID = 1:7,
  departure_airport = c("ZRH", "MSY", "ZRH", "ZRH", "VIE", "ZRH", "KEF"),
  arrival_airport = c("MSY", "IAD", "LAX", "BUD", "ZRH", "KEF", "BOS"),
  journey_id = c(1,1,2,3,4,5,5),
  flight = c(1,2,1,1,1,1,2),
  is_outbound = c(TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE),
  n = c(2,2,2,1,1,2,2)
)

# 生成反向行程行：筛选n=2的行，交换机场，设置is_outbound为FALSE，倒序排列保证行程顺序正确
reverse_rows <- df %>%
  filter(n == 2) %>%
  mutate(
    departure_airport = arrival_airport,
    arrival_airport = departure_airport,
    is_outbound = FALSE
  ) %>%
  group_by(journey_id) %>%
  arrange(desc(flight)) %>%
  ungroup()

# 合并原始数据与反向行程，重新分配flight和ID编号
result_df <- bind_rows(df, reverse_rows) %>%
  group_by(journey_id) %>%
  mutate(flight = row_number()) %>%
  ungroup() %>%
  mutate(ID = row_number()) %>%
  arrange(ID)

# 输出结果
print(result_df, row.names = FALSE)

方案2：使用Python语言 + pandas库 #

import pandas as pd

# 构造原始数据框
data = {
    "ID": [1,2,3,4,5,6,7],
    "departure_airport": ["ZRH", "MSY", "ZRH", "ZRH", "VIE", "ZRH", "KEF"],
    "arrival_airport": ["MSY", "IAD", "LAX", "BUD", "ZRH", "KEF", "BOS"],
    "journey id": [1,1,2,3,4,5,5],
    "flight": [1,2,1,1,1,1,2],
    "is_outbound": [True, True, True, True, True, True, True],
    "n": [2,2,2,1,1,2,2]
}
df = pd.DataFrame(data)

# 生成反向行程行：筛选n=2的行，交换机场列，设置is_outbound为False，倒序排列行程
reverse_rows = df[df['n'] == 2].copy()
reverse_rows[['departure_airport', 'arrival_airport']] = reverse_rows[['arrival_airport', 'departure_airport']]
reverse_rows['is_outbound'] = False
reverse_rows = reverse_rows.sort_values(by=['journey id', 'flight'], ascending=[True, False])

# 合并数据，重新分配flight和ID编号
result_df = pd.concat([df, reverse_rows], ignore_index=True)
result_df['flight'] = result_df.groupby('journey id').cumcount() + 1
result_df['ID'] = range(1, len(result_df) + 1)
result_df = result_df.sort_values('ID').reset_index(drop=True)

# 输出结果
print(result_df.to_string(index=False))

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