0

i have 2 set of data in excel in different file with thousands of data, and i would like to match a given datetime with a range of date and time given, then save in a new file.

example as follow:

df = pd.DataFrame({'date1': ['2020-11-17 13:35:18', '2020-11-17 00:00:45','2020-11-17 00:18:18','2020-11-17 22:45:19'],
                   'date2': ['2020-11-17 13:36:50', '2020-11-17 00:01:53', '2020-11-17 00:19:27','2020-11-17 22:46:40'],
                   'item no' : ['X-317', 'ry-186', 'opq-468','999-xq'],
                   'status': ['Success','Cancel','Pending','Done']})

df1 = pd.DataFrame({'complete':['2020-11-17 00:01:29','2020-11-17 22:46:09', '2020-11-17 21:43:35']})

OUTPUT:
df               date1                date2  item no   status
0  2020-11-17 13:35:18  2020-11-17 13:36:50    X-317  Success
1  2020-11-17 00:00:45  2020-11-17 00:01:53   ry-186   Cancel
2  2020-11-17 00:18:18  2020-11-17 00:19:27  opq-468  Pending
3  2020-11-17 22:45:19  2020-11-17 22:46:40   999-xq     Done


df1           complete
0  2020-11-17 00:01:29
1  2020-11-17 22:46:09
2  2020-11-17 21:43:35

my intention is to find each datetime in df1 within range of the date1 and date2 in df. it is a random search based on the datetime range generate and must be more than date1, less than equal date2 of each row.

expected result will be like below:

             complete       item no       status
0  2020-11-17 00:01:29      ry-186        Cancel
1  2020-11-17 22:46:09      999-xq        Done
2  2020-11-17 21:43:35      None          None
BBBBBBBB
  • 165
  • 1
  • 10
  • Does this answer your question? [Filtering Pandas DataFrames on dates](https://stackoverflow.com/questions/22898824/filtering-pandas-dataframes-on-dates) – Umair Mubeen Nov 19 '20 at 11:11
  • you might also want to have a look at [between_time](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.between_time.html) – FObersteiner Nov 19 '20 at 11:15
  • @UmairMubeen no, [link](https://stackoverflow.com/questions/22898824/filtering-pandas-dataframes-on-dates) is just filtering out the date, mine is to match and find – BBBBBBBB Nov 19 '20 at 11:17
  • what if your "complete" date falls within multiple date1-to-date2 ranges? how should that be represented in the result? – FObersteiner Nov 19 '20 at 14:39
  • @MrFuppes if multiple date1 and date1 ranges, then result just shows None – BBBBBBBB Nov 20 '20 at 02:19

0 Answers0