基于CO_1_Name匹配填充Ticker2LP列的NA值

嘿，针对你提出的需求——当CO_1_Name列的公司名称匹配时，用组内其他行的非NA值填充Ticker2LP列的缺失值，这里给你提供两种简洁的data.table实现方案：

首先先确认你的输入数据（我已经把它转换成可直接运行的data.table对象了）：

library(data.table)
dt <- structure(list(CO_1_Name = c("Sonic Corp", "Sonic Corp", "Baxter International Inc", "Baxter International Inc", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Baxter International Inc", "Baxter International Inc", "Lockheed Martin Corporation", "Sonic Corp", "Sonic Corp", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Baxter International Inc", "Sonic Corp", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Baxter International Inc", "Sonic Corp", "Baxter International Inc", "Lockheed Martin Corporation", "Sonic Corp", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Lockheed Martin Corporation", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc", "Lockheed Martin Corporation", "Sonic Corp", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Lockheed Martin Corporation", "Baxter International Inc", "Baxter International Inc", "Lockheed Martin Corporation", "Baxter International Inc", "Lockheed Martin Corporation", "Baxter International Inc", "Baxter International Inc", "Baxter International Inc" ), Ticker2LP = c("SONC", NA, NA, "BAX", "LMT", "LMT", "MLM", "AP", "BAX", "LMT", "SONC", "SONC", "BAX", "BAX", NA, "LMT", "LMT", "LMT", NA, NA, "LMT", NA, "NOC", NA, "BAX", NA, NA, "LMT", "SONC", NA, "LMT", NA, NA, "BAX", "NOC", "BAX", NA, NA, NA, "BAX", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), .internal.selfref = NULL, row.names = c(NA, -60L), class = c("data.table", "data.frame"))

方案一：双向填充（适合组内有效值分散的情况） #

如果组内的有效值分布在不同位置（比如有的在开头，有的在结尾），可以先用向前填充再向后填充，确保所有NA都被覆盖：

# 按公司名称分组，先向前填充NA（用上一个非NA值）
dt[, Ticker2LP := nafill(Ticker2LP, type = "locf"), by = CO_1_Name]
# 再向后填充剩余的NA（用下一个非NA值）
dt[, Ticker2LP := nafill(Ticker2LP, type = "nocb"), by = CO_1_Name]

方案二：统一用组内第一个有效值填充（适合同一公司代码唯一的场景） #

如果同一公司的Ticker2LP应该是唯一的（只是有缺失），可以直接取组内第一个非NA值来填充整个组的NA：

dt[, Ticker2LP := first(na.omit(Ticker2LP)), by = CO_1_Name]

代码说明 #

• 方案一的nafill函数：

  • locf是"Last Observation Carried Forward"的缩写，会把最近的上一个非NA值向下填充
  • nocb是"Next Observation Carried Backward"的缩写，会把最近的下一个非NA值向上填充
  • 两者结合可以完美处理组内任意位置的NA

• 方案二的逻辑：

  • na.omit(Ticker2LP)会剔除组内所有NA值，剩下的都是有效代码
  • first(...)取这些有效值的第一个，然后将整个组的Ticker2LP替换为这个值，确保同一公司的代码完全一致

效果验证 #

处理后，你示例中的缺失值都会被正确填充：

CO_1_Name Ticker2LP
 1:       Sonic Corp      SONC
 2:       Sonic Corp      SONC
 3: Baxter International Inc      BAX
 4: Baxter International Inc      BAX
 5: Lockheed Martin Corporation      LMT
 ...

这样就完美解决了按公司名称填充缺失股票代码的需求啦！

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