How to set merge and normalize multple dataframes for pd.merge_as_of

I am trying to merge multiple dataframes using pd.merge_asof.

They all contain 2 columns with datetime as index column and a variable column with floating values. They are not balanced in their indexes and times so I have to normalize the values.

Date                    value1                                 
2021-10-22 19:22:25      23.5     
2021-10-22 19:22:40      23.4     
2021-10-22 19:22:55      23.5     
2021-10-22 19:30:12      23.6     
2021-10-22 19:30:42      23.5

Date                      value2                
2021-10-22 19:22:25        12
2021-10-22 19:22:40        12
2021-10-22 19:22:55        12
2021-10-22 19:30:12        16
2021-10-22 19:30:42        16

I can succesfully merge the dfs and normalize the values like this using

 merged = pd.merge_asof(data_frames[0],data_frames[1], left_index=True,right_index=True,direction='nearest')

Date                           value1  value2        
2021-10-22 19:22:25             23.5     12
2021-10-22 19:22:40             23.4     12
2021-10-22 19:22:55             23.5     12
2021-10-22 19:30:12             23.6     16
2021-10-22 19:30:42             23.5     16

Now what I want to do is to merge more than 2 dataframes. I tried doing this:

    merged = pd.merge_asof(data_frames[0],data_frames[1],data_frames[2],left_index=True,right_index=True,direction='nearest')

but I am getting the error

pandas.errors.MergeError: Can only pass argument "on" OR "left_index" and "right_index", not a combination of both.

I am not sure what it is indicating. I removed the one of the index arguments and it still said the same thing. Any way I can get what I need to do?

I want to be able to append dataframe3 which has value3 column to the right of value2 column.

Answer

According to the documentation, merge_asof can only accept two dataframes, the error is caused because in the third argument of the function it expects some other parameter. As @Quang Hoang mentions you can use the reduce function to apply a two-parameter function cumulatively. The way in your case would be:

merged = reduce(lambda left, right: pd.merge_asof(left, right,left_index=True,right_index=True, direction='nearest'), data_frames)

Advertisement

Answer