I want to match the information in two dataframes conditional on dates. For instance, my first dataframe has a column date:
| DATE |
|---|
| 2022-10-01 |
| 2022-10-22 |
| 2022-11-14 |
My second dataframe (dates may not coincide with start and end of month):
| CATEGORY | START | END |
|---|---|---|
| 1 | 2022-09-01 | 2022-09-30 |
| 2 | 2022-10-01 | 2022-10-31 |
| 3 | 2022-11-01 | 2022-11-30 |
The resulting dataframe should look like:
| DATE | CATEGORY | START | END |
|---|---|---|---|
| 2022-10-01 | 2 | 2022-09-01 | 2022-09-30 |
| 2022-10-22 | 2 | 2022-10-01 | 2022-10-31 |
| 2022-11-14 | 3 | 2022-11-01 | 2022-11-30 |
Notice that DATE must be between START and END. I managed to find the result I wanted by merging the two dataframes (using other variables). However, merging generates additional rows that must be filtered afterwards, and this can be a problem if the data size is large.
