unexpected output with pandas merge_asof (missing rows)

Question

I want to merge the two data frames based on the nearest timestamp, the case is as following

Time stamp  Value1  Time Stamp 2    Value 2
13:30   A   13:32   40
13:30   B       
13:30   C       
13:30   D       
13:30   E       
13:30   F

the above is the dataframe where value 1 is (A,B,C,D,E) and value 2 is 40 with a different time stamp, i want to merge the 40 with (A,B,C,D,E) so i get the following result:

13:30   A   40
13:30   B   40
13:30   C   40
13:30   D   40
13:30   E   40
13:30   F   40

I have already used merge_asof method and i got this result

13:30   F   40

I used the following code:

df = pd.merge_asof(df_downtime,df_centerline,on='Time',direction='nearest',by='Desc Variable',allow_exact_matches=False)

You don't define all the inputs, your example should be reproducible, minimal, and self-sufficent — mozway, Sep 28 '22 at 12:00
you can read the help on how to format your question, also check my corrections — mozway, Sep 28 '22 at 12:02
@mozway i think the table is correct, i was missconfused, what corrections? — ala mazahreh, Sep 28 '22 at 12:06
Welcome to StackOverflow. Please take the [tour](https://stackoverflow.com/tour) and learn [How to Ask](https://stackoverflow.com/help/how-to-ask). In order to get help, you will need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). If your question include a pandas dataframe, please provide a [reproducible pandas example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — alec_djinn, Sep 28 '22 at 12:09
What are the values of the other rows in the dataframe? Is is NaN? What is the "nearest time stamp"? Can you make a more useful example? — alec_djinn, Sep 28 '22 at 12:09
@alec_djinn the other rows of value 2 will be the same but different time stamp, for example Time stamp Value1 Time Stamp 2 Value 2 13:30 A 13:32 40 13:30 B 14:15 50 13:30 C 13:30 D 13:30 E 13:30 F 14:00 A 14:00 B 14:00 C 14:00 D 14:00 E 14:00 F desired result 13:30 A 40 13:30 B 40 13:30 C 40 13:30 D 40 13:30 E 40 13:30 F 40 14:00 A 50 14:00 B 50 14:00 C 50 14:00 D 50 14:00 E 50 14:00 F 50 — ala mazahreh, Sep 28 '22 at 12:14
@alamazahreh Then please, post a proper example, do not skip values or columns — alec_djinn, Sep 28 '22 at 12:16

mozway · Accepted Answer · 2022-09-28T12:18:12.117

0

Although your example is ambiguous and not reproducible, if you obtained the result you provided it is likely that you have used an incorrect order of the parameters.

merges_asof is a left merge, so you should probably use:

df = pd.merge_asof(df_centerline, df_downtime,
                   on='Time', direction='nearest',
                   by='Desc Variable', allow_exact_matches=False)

edited Sep 28 '22 at 12:18

answered Sep 28 '22 at 12:10

mozway

194,879
13
39
75

unexpected output with pandas merge_asof (missing rows)

1 Answers1