R语言按Player列合并DataFrame时新增列全为NA的问题排查

问题详情 #

我有两个R语言DataFrame：BSN_stats1（261行、36列）和BSN_info1（249行、6列），两者均包含Player列。希望通过匹配Player列，将BSN_info1的5列合并到BSN_stats1中，保留BSN_stats1的所有行。

尝试执行以下代码：

dataframe1 <- merge(x = dataframe1, y = dataframe2, by = "Player", all.x = TRUE)

执行后新DataFrame的行数（261行）和列数（40列）符合预期，但从BSN_info1引入的列所有值均为NA。曾怀疑是否因为BSN_stats1比BSN_info1多12行导致该问题。

数据框头部结构 #

BSN_stats1的dput(head)结果：

structure(list(`#` = c(0, 2, 7, 9, 10, 14), Team = c("Capitanes", "Capitanes", "Capitanes", "Capitanes", "Capitanes", "Capitanes"), Player = c("Thomas Robinson", "Aaron Harrison", "Marlon Hargis", "Perry Jones lll", "Víctor Liz", "Jorge Luis Torres"), GP = c(9,14,13,1,31,11), Minutes = c(160,377,47,22,871,108),MPG = c(17.8,26.9,3.6,22,28.1,9.8), FG = c(37,57,5,7,127,16), FGA = c(62,143,13,11,301,22), `FG%` = c(0.597,0.399,0.385,0.636,0.422,0.727), `TS%` = c(0.602,0.536,0.44,0.918,0.539,0.71), `2P` = c(37,25,4,1,92,16),`2PA` = c(62,55,5,3,203,22), `2P%` = c(0.597,0.455,0.8,0.333,0.453,0.727), `3P` = c(0,32,1,6,35,0),`3PA` = c(0,88,8,8,98,0), `3P%` = c(0,0.364,0.125,0.75,0.357,0), FT = c(17,21,2,1,94,8), FTA = c(31,29,4,1,123,14), `FT%` = c(0.548,0.724,0.5,1,0.764,0.571), DRB = c(25,45,9,7,117,17), DRBPG = c(2.8,3.2,0.7,7,3.8,1.5), ORB = c(24,9,2,0,52,15), ORBPG = c(2.7,0.6,0.2,0,1.7,1.4), TRB = c(49,54,11,7,169,32),RPG = c(5.4,3.9,0.8,7,5.5,2.9), Assists = c(11,24,0,1,74,5), APG = c(1.2,1.7,0,1,2.4,0.5), Steals = c(1,19,0,0,41,7), SPG = c(0.1,1.4,0,0,1.3,0.6), Blocks = c(2,4,1,1,4,3), BPG = c(0.2,0.3,0.1,1,0.1,0.3), TO = c(14,21,2,1,66,8), TOPG = c(1.6,1.5,0.2,1,2.1,0.7), Points = c(91,167,13,21,383,40), PPG = c(10.1,11.9,1,21,12.4,3.6), `USG%` = c(24.9,20.8,15.8,25.1,21.5,14.9)), row.names = c(NA,-6L), class = c("tbl_df", "tbl", "data.frame"))

BSN_info1的dput(head)结果：

structure(list(Player = c("Raymond Cintrón Cortés", "Thomas Robinson ","Aaron Harrison ", "Stephen Zimmerman ", "Víctor Liz ", "Chinemelu Elonu "), Age = c(33,32,29,27,36,36), Position = c("Escolta", "Delantero/Centro","Escolta", "Centro", "Escolta", "Centro"), Height = c("6'0","6'10", "198", "213", "6'2", "6'10"), Weight = c(200L,237L,90L,109L,180L,235L), Team = c("Capitanes", "Capitanes", "Capitanes","Capitanes", "Capitanes", "Capitanes")), row.names = c(NA,-6L), class = c("tbl_df", "tbl", "data.frame"))

原因分析 #

核心问题并非行数差异，而是**Player列的字符串存在隐形空格**：观察dput结果，BSN_info1的Player值末尾都带有空格（比如"Thomas Robinson "），而BSN_stats1的Player值无末尾空格（比如"Thomas Robinson"），导致merge时无法匹配，最终引入的列全为NA。

解决步骤 #

1. 清理Player列的首尾空格 #

先对两个数据框的Player列执行去空格操作：

# 清理BSN_stats1的Player列
BSN_stats1$Player <- trimws(BSN_stats1$Player)
# 清理BSN_info1的Player列
BSN_info1$Player <- trimws(BSN_info1$Player)

2. 执行合并操作 #

使用原生merge函数：

merged_df <- merge(x = BSN_stats1, y = BSN_info1, by = "Player", all.x = TRUE)

或者使用dplyr的left_join（需先加载dplyr包）：

library(dplyr)
merged_df <- BSN_stats1 %>%
  mutate(Player = trimws(Player)) %>%
  left_join(BSN_info1 %>% mutate(Player = trimws(Player)), by = "Player")

3. 验证结果 #

查看合并后的数据头部，确认引入的列是否有非NA值：

head(merged_df)

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