Set values in dataframe A by iterating from values on dataframe B

February 8, 2023

Dataframe A is similar to this :

info2 = {'speed': [None]*80}
dfA = pd.DataFrame(info2)
dfA

Dataframe B is similar to this :

info={"IndexSpeed":[7,16,44,56,80],"speed":[25,50,25,50,90]}
dfB = pd.DataFrame(info)
dfB

I need to set the values in dfA[‘speed’] by using the values in dfB.
For instance, for each row in dfA of index <=7, speed should be set at 25.
for each row of index between 8 and 16, speed should be set at 50. and so on untill all 80 rows are set.

What would be the optimal way to do this?

>Solution :

You can use a merge_asof:

dfA['speed'] = pd.merge_asof(dfA.drop(columns='speed'), dfB,
                             left_index=True, right_on='IndexSpeed',
                             direction='forward',
                             )['speed']

NB. dfA must be sorted on its index and dfB on IndexSpeed.

Output:

    speed
0      25
1      25
2      25
3      25
4      25
..    ...
75     90
76     90
77     90
78     90
79     90

[80 rows x 1 columns]

Output as array:

array([25, 25, 25, 25, 25, 25, 25, 25, 50, 50, 50, 50, 50, 50, 50, 50, 50,
       25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25,
       25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25, 50, 50, 50, 50, 50, 50,
       50, 50, 50, 50, 50, 50, 90, 90, 90, 90, 90, 90, 90, 90, 90, 90, 90,
       90, 90, 90, 90, 90, 90, 90, 90, 90, 90, 90, 90])