在R语言中比较两个DataFrame，获取第二个中首个更大值的索引

问题描述 #

现有两个DataFrame：

df1 = data.frame(df1_col = c(0.00002,0.00010,0.00020,0.00100,0.00200))

df2 = data.frame(df2_col1=c(0.00001702406727,0.00002002614159,0.00002018336933,0.0000977206871,0.00010785618371,0.00018966630497,0.00020173904639,0.00099759142132,0.00104912583361,0.00194016482197,0.00200732582737))

需求是将df1的每一行与df2的所有行比较，找到df2中首个大于df1对应值的元素，并将两者合并为新的DataFrame。此前尝试循环遍历+which函数未成功，希望找到更简便的方法。

期望输出：

df3 = data.frame(df1_col1 = c(0.00002,0.00010,0.00020,0.00100,0.00200), df2_col2=c(0.00002002614159,0.00010785618371,0.00020173904639,0.00104912583361,0.00200732582737))

后续补充的新数据：

df1=data.frame(df1_col1=c(0.00002,
0.00010,
0.00020,
0.00100,
0.00200,
0.00250,
0.00400,
0.00500,
0.01000,
0.02000,
0.03000))

df2=data.frame(df2_col1=c(0.00014940969624,
0.00015836812803,
0.00016803247695,
0.00017844541097,
0.00018966630497,
0.00020173904639,
0.00021473722873,
0.00022871767705,
0.00024376616425,
0.00025995387854,
0.00027740350512,
0.00029622853518,
0.00031659411339,
0.00033867002512,
0.00036270484799,
0.00038895378722,
0.00041774363175,
0.00044938435908,
0.00048425557455,
0.00052270555240,
0.00056516734709,
0.00058803959079,
0.00061213685774,
0.00063753303271,
0.00066436020792,
0.00069271759254,
0.00072278590327,
0.00075471258665,
0.00078871891980,
0.00082495135506,
0.00086370796092,
0.00090520244315,
0.00094974375995,
0.00099759142132,
0.00104912583361,
0.00110468513550,
0.00116471796237,
0.00122961947450,
0.00129997381226,
0.00137633261223,
0.00145941148983,
0.00150367150729,
0.00154993475674,
0.00164886456582,
0.00170177926583,
0.00175721404389,
0.00181530011799,
0.00187625634148,
0.00194016482197,
0.00200732582737,
0.00207790286653,
0.00215216527238,
0.00223029743932,
0.00231263720905,
0.00239942910043,
0.00249104495897,
0.00258772950799,
0.00268995918708,
0.00279808620674,
0.00291260618091,
0.00303393957317,
0.00316270550389,
0.00329945165204,
0.00344491415323,
0.00359970294452,
0.00376478162054,
0.00394100416242,
0.00412946943549,
0.00433124287660,
0.00454769286700,
0.00466181286329,
0.00478015613269,
0.00490287219562,
0.00503023860438,
0.00516231516987,
0.00529948405762,
0.00544190977016,
0.00558989424000,
0.00574363032708,
0.00590348262538,
0.00606970711776,
0.00624269632708,
0.00642267044700,
0.00661012222834,
0.00680537865824,
0.00700890517615,
0.00722105704960,
0.00744238908118,
0.00767333589656,
0.00791450623655,
0.00816629638134,
0.00842946839953,
0.00870457800002,
0.00899236514987,
0.00929347419740,
0.00960877976327,
0.00993906664874,
0.01028531629401,
0.01064836560114,
0.01102939995868,
0.01142946986211,
0.01184984756038,
0.01229174638031,
0.01275666542031,
0.01324605799479,
0.01376164646858,
0.01430505034994,
0.01487834806441,
0.01517680797833,
0.01548355354501,
0.01579883472796,
0.01612301677381,
0.01645629840316,
0.01679913158769,
0.01715180723470,
0.01751473695387,
0.01788823000264,
0.01827276128134,
0.01866870741769,
0.01907658694645,
0.01949670526428,
0.01992970835114,
0.02037603813266,
0.02083628226895,
0.02131092986923,
0.02180065884019,
0.02230604531786,
0.02282781673939,
0.02336654192662,
0.02392307856762,
0.02449812744245,
0.02509254651992,
0.02570710318883,
0.02634276037547,
0.02700040459895,
0.02768108720588,
0.02838568468843,
0.02911543211252,
0.02987138853965,
0.03065480656468,
0.03146687637367,
0.03230902465341,
0.03318262955971,
0.03408928114548,
0.03503049532094,
0.03600813039164,
0.03702399530373,
0.03808014377487,
0.03917864953982,
0.04032188867370,
0.04151227866069,
0.04275252873894,
0.04404534794164,
0.04539391077701,
0.04609007134882,
0.04680142876348))

解决方案 #

观察到df2的列是单调递增的，可利用R内置的findInterval函数快速定位，无需循环，效率更高。该函数能返回每个df1元素在df2中的插入位置，加1即可得到首个大于它的元素索引。

代码实现 #

针对初始数据

# 提取df2的向量
df2_vals <- df2$df2_col1

# 找到每个df1值对应的首个大于它的df2元素索引
indices <- findInterval(df1$df1_col, df2_vals) + 1

# 提取对应元素并合并为df3
df3 <- data.frame(
  df1_col1 = df1$df1_col,
  df2_col2 = df2_vals[indices]
)

# 查看结果
print(df3)

针对新数据

# 提取df2的向量
df2_vals_new <- df2$df2_col1

# 找到每个df1值对应的首个大于它的df2元素索引
indices_new <- findInterval(df1$df1_col1, df2_vals_new) + 1

# 提取对应元素并合并为df3
df3_new <- data.frame(
  df1_col1 = df1$df1_col1,
  df2_col2 = df2_vals_new[indices_new]
)

# 查看结果
print(df3_new)

结果验证 #

初始数据运行后输出与期望完全一致：

> print(df3)
  df1_col1     df2_col2
1   0.00002 0.00002002614
2   0.00010 0.00010785618
3   0.00020 0.00020173905
4   0.00100 0.00104912583
5   0.00200 0.00200732583

新数据运行后，以部分值为例：

• df1中的0.00250对应df2中首个大于它的0.00258772950799
• df1中的0.03000对应df2中首个大于它的0.03065480656468
  完全符合需求。

补充说明 #

如果df2不是单调递增的，可先对其排序并记录原索引，或者使用purrr包的map_dbl结合which.min：

library(purrr)

df3 <- data.frame(
  df1_col1 = df1$df1_col