How to find difference between two dates in different dataframes

Question

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

Answer 1

Prepare Data

import pandas as pd
import numpy as np

data1 = {"ID_": [26,27,28, 4236, 4237],
       "Max_Date": ["2020-11-01", "2020-11-01", "2020-11-01", "2020-11-01", "2020-11-01"]}

data2 = {"ID_": [26, 569, 570, 4236, 9172],
       "PAYMENTDATE": ["2011-07-01", np.nan, "2004-05-01", "2021-05-01", "2021-07-01"]}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

df1["Max_Date"] = pd.to_datetime(df1["Max_Date"], format="%Y-%m-%d")

df2["PAYMENTDATE"] = pd.to_datetime(df2["PAYMENTDATE"], format="%Y-%m-%d")

df1

    ID_     Max_Date
0   26      2020-11-01
1   27      2020-11-01
2   28      2020-11-01
3   4236    2020-11-01
4   4237    2020-11-01

df2

    ID_     PAYMENTDATE
0   26      2011-07-01
1   569     NaT
2   570     2004-05-01
3   4236    2021-05-01
4   9172    2021-07-01

Data Manipulation

Some ID's from the second dataframe doesn’t exist in first and I should avoid them.

result = pd.merge(df1, df2, on=["ID_", "ID_"])

result

    ID_     Max_Date    PAYMENTDATE
0   26      2020-11-01  2011-07-01
1   4236    2020-11-01  2021-05-01

result["Difference_in_months"] = ((result.Max_Date - result.PAYMENTDATE)/np.timedelta64(1, 'M'))
result["Difference_in_months"] = result["Difference_in_months"].astype(int)

result

    ID_      Max_Date   PAYMENTDATE      Difference_in_months
0   26      2020-11-01  2011-07-01             112
1   4236    2020-11-01  2021-05-01             -5

new_df = result[["ID_", "Difference_in_months"]]

new_df

    ID_       Difference_in_months
0   26                112
1   4236              -5