I have two dataframes that contain time series data.
Dataframe A contains data of timestep 1, with index values getting incremented by 1 each time.
Dataframe B contains data of timestep n, with index values getting incremented by n each time.
I wish to do the following:
Add a column in Dataframe A and fill values from Dataframe B such that if the index value of that row in A lies between that of consecutive indexes in B, I fill the same value for all such rows in A.
I will illustrate this as below
A:
id val1
0 2
1 3
2 4
3 1
4 6
5 23
6 2
7 12
8 56
9 34
10 90
...
B:
id tval
0 1
3 5
6 9
9 34
12 3434
...
Now, my result should like the following:
A:
id val1 tval
0 2 1
1 3 1
2 4 1
3 1 5
4 6 5
5 23 5
6 2 9
7 12 9
8 56 9
9 34 34
10 90 34
...
I would like to automate this for any n. Any help will be appreciated. Thank you in advance.
>Solution :
Use merge_asof:
df = pd.merge_asof(A, B, left_index=True, right_index=True)
print (df)
val1 tval
id
0 2 1
1 3 1
2 4 1
3 1 5
4 6 5
5 23 5
6 2 9
7 12 9
8 56 9
9 34 34
10 90 34
If id is columns:
df = pd.merge_asof(A, B, on='id')
print (df)
id val1 tval
0 0 2 1
1 1 3 1
2 2 4 1
3 3 1 5
4 4 6 5
5 5 23 5
6 6 2 9
7 7 12 9
8 8 56 9
9 9 34 34
10 10 90 34