Merging two dataframes based on a date between two other dates without a common column

by

Create data and format to datetimes:

df_A = pd.DataFrame({'start_date':['2017-03-27','2017-01-10'],'end_date':['2017-04-20','2017-02-01']})
df_B = pd.DataFrame({'event_date':['2017-01-20','2017-01-27'],'price':[100,200]})

df_A['end_date'] = pd.to_datetime(df_A.end_date)
df_A['start_date'] = pd.to_datetime(df_A.start_date)
df_B['event_date'] = pd.to_datetime(df_B.event_date)

Create keys to do a cross join:

New in pandas 1.2.0+ how='cross' instead of assigning psuedo keys:

df_merge = df_A.merge(df_B, how='cross')

Else, with pandas < 1.2.0 use psuedo key to merge on ‘key’

df_A = df_A.assign(key=1)
df_B = df_B.assign(key=1)
df_merge = pd.merge(df_A, df_B, on='key').drop('key',axis=1)

Filter out records that do not meet criteria of event dates between start and end dates:

df_merge = df_merge.query('event_date >= start_date and event_date <= end_date')

Join back to original date range table and drop key column

df_out = df_A.merge(df_merge, on=['start_date','end_date'], how='left').fillna('').drop('key', axis=1)

print(df_out)

Output:

              end_date           start_date           event_date price
0  2017-04-20 00:00:00  2017-03-27 00:00:00                           
1  2017-02-01 00:00:00  2017-01-10 00:00:00  2017-01-20 00:00:00   100
2  2017-02-01 00:00:00  2017-01-10 00:00:00  2017-01-27 00:00:00   200

More Related Contents:

  1. Count the frequency that a value occurs in a dataframe column
  2. SettingWithCopyWarning even when using .loc[row_indexer,col_indexer] = value
  3. Pretty Printing a pandas dataframe
  4. Check if a string in a Pandas DataFrame column is in a list of strings
  5. Pandas: Multiple columns into one column
  6. FailedPreconditionError: Attempting to use uninitialized in Tensorflow
  7. Pandas: Subindexing dataframes: Copies vs views
  8. Get column index from column name in python pandas
  9. Plot bar graph from Pandas DataFrame
  10. pandas DataFrame output end of csv
  11. Comparing floats in a pandas column
  12. Pandas sparse dataFrame to sparse matrix, without generating a dense matrix in memory
  13. Dictionary column in pandas dataframe
  14. How do I split a string into several columns in a dataframe with pandas Python?
  15. Pandas: combining header rows of a multiIndex DataFrame
  16. pandas groupby and join lists
  17. Selecting pandas column by location
  18. Row-wise average for a subset of columns with missing values
  19. Plot multiple columns of pandas DataFrame using Seaborn
  20. Get first and second highest values in pandas columns
  21. python dask DataFrame, support for (trivially parallelizable) row apply?
  22. Python How to use ExcelWriter to write into an existing worksheet
  23. replace value by using regex to np.nan
  24. Plotting Pandas into subplots
  25. python-pandas and databases like mysql
  26. Applying uppercase to a column in pandas dataframe
  27. How to group and count rows by month and year using Pandas?
  28. Plotting multiple boxplots in seaborn
  29. Search and replace dots and commas in pandas dataframe
  30. Python Pandas – Combining Multiple Columns into one Staggered Column

Leave a Comment