0

I have two pandas dataframes, like so:

Table A:

Country Item Start Date
France Croissant 2020-03-01
England Scone 2020-04-03
France Croissant 2020-05-01
England Scone 2020-06-03

Table B:

Country Item End Date
France Croissant 2020-05-02
England Scone 2020-06-04
France Croissant 2020-03-02
England Scone 2020-04-05

I'd like to join these two tables together so that for each country-item pairing and unique start date, the subsequent end date is appended. Like so:

Country Item Start Date End Date
France Croissant 2020-03-01 2020-03-02
England Scone 2020-04-03 2020-04-04
France Croissant 2020-05-01 2020-05-02
England Scone 2020-06-03 2020-06-04

Right now, I'm doing the following:

  1. Left join of the end date data onto the start date data
  2. Group by the start date and collecting the end dates into a sorted list
  3. Finding the date within this list that is the closest subsequent date after the start date

I feel like there's got to be a better way. Any suggestions?

mdrishan
  • 469
  • 3
  • 15
  • Does this answer your question? [pandas: merge (join) two data frames on multiple columns](https://stackoverflow.com/questions/41815079/pandas-merge-join-two-data-frames-on-multiple-columns) – Paul Brennan Feb 11 '21 at 01:44
  • No, I'm able to join on multiple columns. I guess I'm just wondering if there's a way to join using some other condition besides "=". So that the dates correspond to each other as shown above without me having to do the left join first and then filter. – mdrishan Feb 12 '21 at 15:18

0 Answers0