R转Python（data.table→pandas）：列值替换逻辑迁移问题排查

背景 #

我正在将R中针对data.table的转换逻辑迁移到Python（pandas），14行的测试集验证通过，但3万行的全量数据集执行后，prop_avg列出现18.37861%的数值差异。

原R代码（含测试数据生成） #

# create test data.table
dt <- data.table(
  prop_avg = c(runif(5), 1/3, 1/3, NA, NA, runif(5)),  # 14 values, with two 1/3 and two NAs
  prop = runif(14),  # 14 random values
  year = sample(2000:2023, 14, replace = TRUE),  # 14 random years between 2000 and 2023
  month = sample(1:12, 14, replace = TRUE),  # 14 random months
  quarter = sample(1:4, 14, replace = TRUE),  # 14 random quarters
  winner = sample(LETTERS, 14, replace = TRUE),  # 14 random winners from capital letters
  loser = sample(LETTERS, 14, replace = TRUE)  # 14 random losers from capital letters
)

# write the data to CSV
location <- '/tmp/test_in.csv'
write.csv(dt, location, row.names = FALSE)

# Notice: THIS IS THE TRANSFORMATION TO REPLICATE IN PYTHON
# where the proportion average is one-third or NA we instead use the proportion
dt[
  is.na(prop_avg)
  |prop_avg == 1/3
  ,prop_avg := prop
]

# write the result data to CSV
location <- '/tmp/test_out.csv'
write.csv(dt, location, row.names = FALSE)

Python测试实现（测试集验证通过） #

# read the data used as input to the R code
df = pd.read_csv('/tmp/test_in.csv')

# Notice: THIS IS WHERE THE RE-IMPLEMENTATION FROM R IS IN DOUBT
# transformation
filter_nans = df['prop_avg'].isna()
filter_thirds = np.isclose(df['prop_avg'], 1/3)
df.loc[filter_nans | filter_thirds, 'prop_avg'] = df['prop']

# compare against the R result data
expected_df = pd.read_csv('/tmp/test_out.csv')

# sort both by the same columns
sort_cols = ['year', 'month', 'quarter', 'winner', 'loser']
df = df.sort_values(by=sort_cols, ascending=True)
expected_df = expected_df.sort_values(by=sort_cols, ascending=True)

# eliminate the index
df = df.reset_index(drop=True)
expected_df = expected_df.reset_index(drop=True)

# fill NA in order to avoid NaN != NaN issues etc.
df = df.fillna(value=0)
expected_df = expected_df.fillna(value=0)

# test assertion
pd.testing.assert_frame_equal(
    left=expected_df,
    right=df,
    check_like=True,
    check_dtype=False,
)

全量数据执行错误 #

DataFrame.iloc[:, 7] (column name="prop_avg") values are different (18.37861 %)

尝试过的其他Python实现（结果一致） #

# method 2
df['prop_avg'] = df['prop'].where(np.isclose(df['prop_avg'], 1/3) | df['prop_avg'].isna(), df['prop_avg'])

# method 3
df.loc[df['prop_avg'].isna(), 'prop_avg'] = df['prop']
df.loc[np.isclose(df['prop_avg'], 1/3), 'prop_avg'] = df['prop']

已排除的可能性 #

• 浮点数精度问题（已用np.isclose匹配1/3）
• NaN处理逻辑差异
• 排序导致的行顺序不一致

进一步排查建议 #

• 提取差异样本对比：从全量数据中筛选出两边prop_avg不一致的行，对比原始数据中的prop_avg和prop值，明确是R替换了但Python没替换，还是反过来：

  # 假设full_df是Python处理结果，expected_full是R处理的全量结果
  sort_cols = ['year', 'month', 'quarter', 'winner', 'loser']
  full_df_sorted = full_df.sort_values(sort_cols).reset_index(drop=True)
  expected_full_sorted = expected_full.sort_values(sort_cols).reset_index(drop=True)
  
  # 生成差异掩码（包含NaN的情况）
  diff_mask = ~np.isclose(full_df_sorted['prop_avg'], expected_full_sorted['prop_avg'], equal_nan=True)
  # 合并差异行，对比两边值
  diff_rows = pd.concat(
      [full_df_sorted[diff_mask], expected_full_sorted[diff_mask]],
      axis=1,
      keys=['Python', 'R']
  )
  print(diff_rows.head(20))
  

• 对比R和Python的匹配行数：在R中统计is.na(prop_avg)和prop_avg == 1/3的行数，在Python中分别统计df['prop_avg'].isna().sum()、np.isclose(df['prop_avg'],1/3).sum()、(df['prop_avg'] == 1/3).sum()的数值，看是否存在匹配数量差异。
• 检查CSV读写的精度损耗：R的write.csv默认保留6位小数，而pandasread_csv读取时可能解析出更多小数位，导致原本在R中精确等于1/3的值，在Python中变成近似值，无法被np.isclose匹配（或反过来）。可以在R中查看全量数据prop_avg列的原始值，再对比Python读取后的值。
• 验证行对齐准确性：若存在完全重复的行（所有sort_cols值都相同），R和Python的排序结果可能不一致，导致对比错误。可以给原始数据添加唯一行号列，确保每行一一对应后再对比。
• 分步验证替换逻辑：在Python中先执行NaN替换，统计替换行数；再执行1/3匹配替换，统计替换行数，和R中dt[is.na(prop_avg), .N]、dt[prop_avg == 1/3, .N]的结果对比，确认替换步骤的行数一致。

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