时间戳

首页 > 解决方案 > How to map one dataframe to another (python pandas)?

python - How to map one dataframe to another (python pandas)?

问题描述

Given these two dataframes, how do I get the intended output dataframe? The long way would be to loop through the rows of the dataframe with iloc and then use the map function after converting df2 to a dict to map the x and y to their score.

This seems tedious and would take long to run on a large dataframe. I'm hoping there's a cleaner solution.

df1:

ID    A    B    C
1     x    x    y
2     y    x    y
3     x    y    y

df2:

ID    score_x    score_y
1          20         30
2          15         17
3          18         22

output:

ID    A     B     C
1     20    20    30
2     17    15    17
3     18    22    22

Note: the dataframes would have many columns and there would be more than just x and y as categories (possibly in the region of 20 categories).

Thanks!

标签： pythonpandasdataframe

解决方案

DataFrame.apply与列一起使用Series.map：

df1.set_index('ID', inplace=True)
df2.set_index('ID', inplace=True)
df2.columns = df2.columns.str.split('_').str[-1]

df1 = df1.apply(lambda x: x.map(df2.loc[x.name]), axis=1).reset_index()

print(df1)
   ID   A   B   C
0   1  20  20  30
1   2  17  15  17
2   3  18  22  22

print(df2)
     x   y
ID        
1   20  30
2   15  17
3   18  22

推荐阅读