I have one dataframe in the form (with some other columns which aren't very relevant to the question)-
| Max Date | Stock |
|---|---|
| 28-09-2026 | A |
| 22-03-2024 | B |
| 17-10-2023 | C |
| 17-05-2025 | D |
I have another dataframe with only a list of dates. For each date in the 2nd dataframe, I want to create a repeat of each stock and each "max date" from 1st dataframe in order to compare against that date and assign a classification based on whether the date is earlier or later.
Output should be like -
| Date (2nd df) | Stock | Max Date | Classification |
|---|---|---|---|
| 01-02-2023 | A | 28-09-2026 | Lower |
| 10-03-2025 | A | 28-09-2026 | Lower |
| 01-02-2023 | B | 22-03-2024 | Lower |
| 10-03-2025 | B | 22-03-2024 | Higher |
I tried using pd.merge() after bringing in the Stock names into the 2nd dataframe and doing a left or an outer join.
df2['Stock'] = df1['Stock'][:1]
new_df = pd.merge(df2, df1, on = 'Stock', how = 'outer')
print(new_df)
However, the "Max Dates" from 1st dataframe don't populate in the merged dataframe. Is there an easier way to do this?
Also tried the following-
new_df = pd.concat([df2, df1, axis =1, join = 'outer')
The above is a simple join, but i want rows from df1 repeated for every date entry in df2.
So let's say in the 2nd dataframe, there are n dates, the new dataframe should have these dates and all columns from 1st dataframe repeated n times for each date in 2nd dataframe (for each Stock).
Edit: Please note that the 2nd dataframe has only a list of dates. It doesn't have the list of Stocks, which could potentially be used as common key to do a merge. However, I'm not sure how to bring in Stock name and make it repeat for every date in the 2nd df.
