Suppose I have two pandas dataframes: A first dataframe with some data (reflected in columns Col1 and Col2 in example below) across both Code and Time. A second dataframe detailing a set of date bounds. The idea is to exclude any rows (in df_table) that have a date that falls between any bounds (in df_dates). Each Code has one or more (and different) bounds or may not have bounds at all. The code below works well and does the task correctly but it takes way too much time (the first dataframe df_table has several million rows). import pandas as pd import numpy as np def filter_date(main_table, table_dates): idx = table_dates.Code == main_table.Code table_dates = table_dates.loc[idx] filtering = (table_dates['Start Date'] <= main_table.Timestamp) & (table_dates['End Date'] >= main_table.Timestamp) return filtering.any() # Example below df_table = pd.DataFrame({'Code':['A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B', 'C', 'C'], 'Timestamp':[1,2,3,4,5,6,1,2,3,4,5,6,1,2], 'Col1':np.arange(14), 'Col2':np.arange(14)}) df_dates = pd.DataFrame({'Code':['A', 'A', 'B'], 'Start Date':[1, 5, 1], 'End Date':[2, 6, 3]}) df_table['Exclude Date'] = df_table.apply(lambda x: filter_date(x, df_dates), axis=1) df_table = df_table[df_table['Exclude Date'] == False] print(df_table) Is there a smarter way to do this and avoid this .apply()? Continue reading...
