I want to create a difference between two date columns from different dataframes based on customer ID from the first dataframe. For example: I find max date from the first dataframe for all customers:
df.groupby('ID_')['MONTH'].max()
ID_
26 2020-11-01
27 2020-11-01
28 2020-11-01
...
4236 2020-11-01
4237 2020-11-01
Second dataframe has also ID for customers and date column:
ID_ PAYMENTDATE
0 26 2011-07-01
1 569 NaT
2 570 2004-05-01
... ... ...
5260 4236 2021-05-01
5261 9172 2021-07-01
Some ID's from the second dataframe doesn’t exist in first and I should avoid them.
How to create a new dataframe that contains the right difference between max date and date of payment in months for each customer ID from the first dataframe?
For example
ID_ Difference in months
26 111