CODEWITHSUNDEEP
pythonpandaspandas-merge
RDJ
RDJ

Reputation: 1

Python Pandas Dataframe - Best Join and filter condition

Input:

Dt From To
2/2/22 X Y
2/2/22 Y X
2/2/22 X Y
2/2/22 A B
2/2/22 P Q
2/3/22 Q P
2/2/22 C D
2/3/22 Y X

Output:

Dt From To Dt From To
2/2/22 X Y 2/2/22 Y X
2/2/22 X Y 2/3/22 Y X
2/2/22 P Q 2/3/22 Q P

Note: 4th and 7th row is ignored as there is no counterparts. Each row should join with their counterparts only once. Join should happen for the same day and the following day only.

Upvotes: 0

Views: 245

Answers (2)

BeRT2me
BeRT2me

Reputation: 13242

df2 = df.merge(df, left_on=['From', 'To'], right_on=['To', 'From'])
df2[df2.Dt_x < df2.Dt_y] 
# Or, we could be more exact if they were proper datetimes:
# df2[df2.Dt_x == (df2.Dt_y - pd.Timedelta(days=1))

Output:

     Dt_x From_x To_x    Dt_y From_y To_y
1  2/2/22      X    Y  2/3/22      Y    X
3  2/2/22      X    Y  2/3/22      Y    X
8  2/2/22      P    Q  2/3/22      Q    P

Upvotes: 0

Zero
Zero

Reputation: 1899

Try this,

pd.concat([df.iloc[::2, :].reset_index(drop = True), df.iloc[1::2, :].reset_index(drop = True)], axis = 1)

Output -

Dt From To Dt From To
0 2/2/22 X Y 2/2/22 Y X
1 2/2/22 X Y 2/3/22 Y X

Upvotes: 1

Related Questions