How to efficiently join a small table to a large one on python-CodePudding

I have two tables similar to these:

df_1 = pd.DataFrame({'id': [1,1,1,1,1,2,2,2,2,2], 'x': [0,1,2,3,4,5,6,7,8,9]})

   id  x
0   1  0
1   1  1
2   1  2
3   1  3
4   1  4
5   2  5
6   2  6
7   2  7
8   2  8
9   2  9

df_2 = pd.DataFrame({'y': [10,100]}, index=[1,2])

     y
1   10
2  100

My goal is to multiply df['x'] by df['y'] based on id. My current solution works, but it seems to me that there should be a more efficient/elegant way of doing this.

This is my code:

df_comb = pd.merge(df_1, df_2, left_on='id', right_index=True)
x_new = df_comb['x'] * df_comb['y']
df_1['x_new'] = x_new.to_numpy()

   id  x  x_new
0   1  0      0
1   1  1     10
2   1  2     20
3   1  3     30
4   1  4     40
5   2  5    500
6   2  6    600
7   2  7    700
8   2  8    800
9   2  9    900

CodePudding user response：

You can use map to recover the multiplication factor from df_2, then mul to the x column:

df_1['x_new'] = df_1['id'].map(df_2['y']).mul(df_1['x'])

output:

   id  x  x_new
0   1  0      0
1   1  1     10
2   1  2     20
3   1  3     30
4   1  4     40
5   2  5    500
6   2  6    600
7   2  7    700
8   2  8    800
9   2  9    900