Python Pandas mapping cabinet index from another Dataframe
df.index = 10,100,1000
df2.index = 1,2,11,50,101,500,1001
Just sample
I need to match the cabinet index from df2 compare with df by these conditions
- df2.index needed> df.index
- only one cabinet value
for example the output
df | df2
10 | 11
100 | 101
1000 | 1001
Now I can do it with a for-loop and very slowly
And I used new_df2 to store the index instead of df2
new_df2 = pd.DataFrame(columns = ["value"])
for col in df.index:
for col2 in df2.index:
if(col2 > col):
new_df2.loc[col2] = df2.loc[col2]
break
else:
df2 = df2[1:] #delete first row for index speed
How to avoid the loop in this case Thank you.
+3
source to share
1 answer
Not sure how robust it is, but you can sort df2
to make the index decrement and use asof
to find the most recent index mark corresponding to each key in the index df
:
df2.sort_index(ascending=False, inplace=True)
df['closest_df2'] = df.index.map(lambda x: df2.index.asof(x))
df
Out[19]:
a closest_df2
10 1 11
100 2 101
1000 3 1001
+4
source to share