1
$\begingroup$

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
$\endgroup$

1 Answer 1

1
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.