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
Advertisement
Answer
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