熊猫 groupby merge_asof

我有两个数据框left and right我想根据分组进行合并df1.

df1:

ID              cumul_growth_perc
Nioz-TC-09-A1R  0
Nioz-TC-09-A1R  2.99881756777804
Nioz-TC-09-A1R  90.1974001442841
Nioz-TC-09-A1R  92.7010664317585
Nioz-TC-09-A1R  95.4937993952028
Nioz-TC-09-A1R  97.7300790074048
Nioz-TC-09-A1R  100
Nioz-TC-09-A2R  0
Nioz-TC-09-A2R  2.1989297984251
Nioz-TC-09-A2R  4.25561486642024
Nioz-TC-09-A2R  82.2910739802899
Nioz-TC-09-A2R  93.276493352502
Nioz-TC-09-A2R  95.5072381936874
Nioz-TC-09-A2R  97.5983443147713
Nioz-TC-09-A2R  100

df2:

day cumul_growth_perc
32  0.233297611918821
33  0.466595223837642
34  0.699892835756464
35  0.933190447675285
36  1.16648805959411
37  1.39978567151293
46  3.54027808151455
47  3.78173847397982
48  4.02319886644508
335 92.4313101347799
336 92.6888317371006
337 92.9463533394213
338 93.203874941742
339 93.4613965440627
340 93.7189181463834
361 99.0468989121531
362 99.2851741841149
363 99.5234494560766
364 99.7617247280384
365 100

cumul_growth_perc范围为 0-100，但为了演示而在此处进行了缩短。我想合并此列上的两个数据框，并且值与中的值不完全匹配df1 and df2。 此外，df1应按以下方式分组ID执行匹配之前的列。据我了解，熊猫merge_asof具体有by=关键字来执行此操作。但由于我没有ID列于df2，操作失败。df2对于所有组都是一样的df1.

这是我使用的：pd.merge_asof(df1, df2, on='cumul_growth_perc', left_by='ID', direction='nearest')

正如预期的那样，它告诉我right_by is missing。我怎样才能仍然执行“分组合并”？我可以延长df2通过为每个唯一值附加相同的值df1.ID，但这感觉很怪异。

EDIT:

预期输出：

                ID  cumul_growth_perc  day
0   Nioz-TC-09-A1R           0.000000   32
1   Nioz-TC-09-A1R           2.998818   46
2   Nioz-TC-09-A1R          90.197400  335
3   Nioz-TC-09-A1R          92.701066  336
4   Nioz-TC-09-A1R          95.493799  340
5   Nioz-TC-09-A1R          97.730079  361
6   Nioz-TC-09-A1R         100.000000  365
7   Nioz-TC-09-A2R           0.000000   32
8   Nioz-TC-09-A2R           2.198930   37
9   Nioz-TC-09-A2R           4.255615   48
10  Nioz-TC-09-A2R          82.291074  335
11  Nioz-TC-09-A2R          93.276493  338
12  Nioz-TC-09-A2R          95.507238  340
13  Nioz-TC-09-A2R          97.598344  361
14  Nioz-TC-09-A2R         100.000000  365

意思是我想分组df1.ID在执行合并之前。我通过“重复”让它发挥作用df2加上一个额外的ID每个 ID 的列df1:

for i, name in enumerate(df1.Shell_ID.unique()):
    if i==0:
        df2_long = df2.copy()
        df2_long['ID'] = name
    else:
        temp = df2.copy()
        temp['ID'] = name
        df2_long = df2_long.append(temp)

然后两个数据框都按cumul_growth_perc我将它们与pd.merge_asof(df1, df2_long, on='cumul_growth_perc', by='ID', direction='nearest')

但感觉有一个更简单的解决方案。

Using DataFrame.sort_values对数据框进行排序df1 and df2 on cumul_growth_perc然后执行merge_asof在排序后的数据帧上：

d1 = df1.sort_values('cumul_growth_perc')
d2 = df2.sort_values('cumul_growth_perc')

df = pd.merge_asof(d1, d2, on='cumul_growth_perc', direction='nearest').sort_values('ID')

Result:

                ID  cumul_growth_perc  day
0   Nioz-TC-09-A1R           0.000000   32
1   Nioz-TC-09-A1R           2.998818   46
2   Nioz-TC-09-A1R          90.197400  335
3   Nioz-TC-09-A1R          92.701066  336
4   Nioz-TC-09-A1R          95.493799  340
5   Nioz-TC-09-A1R          97.730079  361
6   Nioz-TC-09-A1R         100.000000  365
7   Nioz-TC-09-A2R           0.000000   32
8   Nioz-TC-09-A2R           2.198930   37
9   Nioz-TC-09-A2R           4.255615   48
10  Nioz-TC-09-A2R          82.291074  335
11  Nioz-TC-09-A2R          93.276493  338
12  Nioz-TC-09-A2R          95.507238  340
13  Nioz-TC-09-A2R          97.598344  361
14  Nioz-TC-09-A2R         100.000000  365