Add new columns based on a lookup table in pandas-CodePudding

I have two DataFrames:

df1:

block, name
A, X
B, Y
C, X

and df2:

type, name, area
G1, X, 0.10
G1, Y, 0.20
G2, X, 0.50
G2, Y, 0.75

The end result I want to achieve is:

block, name, G1_area, G2_area
A, X, 0.1, 0.5
B, Y, 0.2, 0.75
C, X, 0.1, 0.5

I am not sure how to go about doing this lookup and adding these two columns in Pandas. I tried different variations of pd.merge on name without any success. Any ideas?

CodePudding user response：

You could merge pivot:

out = (df1.merge(df2, on='name')
       .pivot(['block', 'name'], 'type', 'area')
       .add_suffix('_area')
       .reset_index().rename_axis([None], axis=1))

Output:

  block name  G1_area  G2_area
0     A    X      0.1     0.50
1     B    Y      0.2     0.75
2     C    X      0.1     0.50