Question: I have a cross-join-like operation that I have implemented using a for loop. I need to make it fast and preferably elegant. It creates a block entry per day with a date range condition. This works fine for small datasets but completely stalls into a very slow runtime for larger datasets. I know that it can be vectorized. My implementation is very bad. I have looked at the other posts on how to vectorize loops in DataFrames. I read 10 minutes to pandas as per suggested by this post How to iterate over rows in a DataFrame in Pandas, tried using lambda functions. Messed with Cython. I just can't get it. I tried implementing [pandas.MultiIndex.to_frame] and I have a strong feeling this, or one of it's cousins, is a good way to go. I have also tried a bunch of other things and nothing. I want to learn to code elegantly. All suggestions, variations on the solution and, comments are welcome. from datetime import datetime import pandas as pd beginning = pd.to_datetime('14/09/2021', dayfirst=True) today = pd.to_datetime(datetime.today()) date_range = pd.date_range(start=beginning, end=today) # .tolist() frame = pd.DataFrame(columns=['Record_Date', 'Identifier', 'start_date', 'end_date', 'color']) block = pd.DataFrame( {'Identifier': ['4913151F', 'F4E9124A', '31715888', 'D0C57FCA', '57B4D7EB', 'E46F1E5D', '99E0A2F8', 'D77E342E', 'C596D233', 'D0EED63F', 'D0C57FCA'], 'start_date': ['03/11/2020', '05/07/2022', '22/12/2016', '17/03/2024', '14/10/2022', '08/08/2022', '04/11/2020', '13/03/2023', '05/11/2021', '12/27/2022', '13/06/2022'], 'end_date': ['11/07/2023', '11/04/2023', '14/12/2018', '20/01/2025', '15/06/2023', '09/01/2023', '16/07/2022', '19/05/2024', '24/09/2022', '17/11/2023', '13/06/2023'], 'color': ['red', 'green', 'magenta', 'yellow', 'light_blue', 'dark_blue', 'black', 'white', 'pink', 'orange', 'yellow']}) block.start_date = pd.to_datetime(block.start_date, dayfirst=True, format='mixed') block.end_date = pd.to_datetime(block.end_date, dayfirst=True, format='mixed') block_uniques = block.drop_duplicates(['Identifier', 'start_date']) for x in date_range: temp_df = block_uniques[(block_uniques.start_date <= x) & (block_uniques.end_date >= x)] temp_df.insert(0, 'Record_Date', x) frame = pd.concat([frame, temp_df]) frame = frame.sort_values(['Record_Date', 'Identifier']) frame = frame.reset_index().drop('index', axis=1) print(frame)  Output and solution:  Record_Date Identifier start_date end_date color 0 2021-09-14 4913151F 2020-11-03 2023-07-11 red 1 2021-09-14 99E0A2F8 2020-11-04 2022-07-16 black 2 2021-09-15 4913151F 2020-11-03 2023-07-11 red 3 2021-09-15 99E0A2F8 2020-11-04 2022-07-16 black 4 2021-09-16 4913151F 2020-11-03 2023-07-11 red ... ... ... ... ... ... 2641 2023-07-05 D0EED63F 2022-12-27 2023-11-17 orange 2642 2023-07-05 D77E342E 2023-03-13 2024-05-19 white 2643 2023-07-06 4913151F 2020-11-03 2023-07-11 red 2644 2023-07-06 D0EED63F 2022-12-27 2023-11-17 orange 2645 2023-07-06 D77E342E 2023-03-13 2024-05-19 white [2646 rows x 5 columns] 

Answer: Looks like some form of inequality join; conditional_join offers an efficient way to handle this. Note that if your dates in block are not overlapping, then pd.IntervalIndex is suitable and performant. # pip install pyjanitor import janitor import pandas as pd # convert date_range to either a named series, or a dataframe date_range = pd.Series(date_range, name = 'date') (block .conditional_join( date_range, # column from the left, # column from the right, # operator ('start_date', 'date', '<='), ('end_date', 'date', '>='), # in some scenarios, # numba might offer a perf boost use_numba=False, ) ) Identifier start_date end_date color date 0 4913151F 2020-11-03 2023-07-11 red 2021-09-14 1 4913151F 2020-11-03 2023-07-11 red 2021-09-15 2 4913151F 2020-11-03 2023-07-11 red 2021-09-16 3 4913151F 2020-11-03 2023-07-11 red 2021-09-17 4 4913151F 2020-11-03 2023-07-11 red 2021-09-18 ... ... ... ... ... ... 2644 D0C57FCA 2022-06-13 2023-06-13 yellow 2023-06-09 2645 D0C57FCA 2022-06-13 2023-06-13 yellow 2023-06-10 2646 D0C57FCA 2022-06-13 2023-06-13 yellow 2023-06-11 2647 D0C57FCA 2022-06-13 2023-06-13 yellow 2023-06-12 2648 D0C57FCA 2022-06-13 2023-06-13 yellow 2023-06-13 [2649 rows x 5 columns]