python根据index合并_Python Pandas-根据索引顺序合并两个数据帧

我有两个pandas数据帧.首先是：

df1 = pd.DataFrame({"val1" : ["B2","A1","B2","A1","B2","A1"]})

第二个数据框是：

df2 = pd.DataFrame({"val1" : ["A1","A1","A1","B2","B2","B2"],

"val2" : [10, 13, 16, 11, 20, 22]})

我想将两者合并在一起,使用df1的行排序,df2的值遵循这个顺序.理想情况下,我希望它看起来像这样：

df_final = pd.DataFrame({"val1" : ["B2","A1","B2","A1","B2","A1"],

"val2" : [11, 10, 20, 13, 22, 16]})

我尝试使用left_on和right_on的合并函数,但我没有得到我正在寻找的输出.任何帮助将不胜感激.

最佳答案 你可以这样做：

>按[‘val1′,’val2’]对df2中的值进行排序,将其分组为val1并将其存储为g2？

>将idx列添加到df1,将用于从df2中选择值

码：

In [176]: df1['idx'] = 1

In [177]: df1['idx'] = df1.groupby('val1')['idx'].cumsum()-1

In [178]: df1

Out[178]:

val1 idx

0 B2 0

1 A1 0

2 B2 1

3 A1 1

4 B2 2

5 A1 2

In [179]: g2 = df2.sort_values(['val1', 'val2']).groupby('val1')

In [180]: g2.groups

Out[180]: {'A1': [0, 1, 2], 'B2': [3, 4, 5]}

In [181]: df2.iloc[g2.groups['A1'][1]]

Out[181]:

val1 A1

val2 13

Name: 1, dtype: object

In [182]: df1.apply(lambda x: df2.iloc[g2.groups[x['val1']][x['idx']]], axis=1)

Out[182]:

val1 val2

0 B2 11

1 A1 10

2 B2 20

3 A1 13

4 B2 22

5 A1 16