How to add rows based on a condition with another dataframe

I have two dataframes as follows:

agreement

  agreement_id activation  term_months  total_fee  0            A 2020-12-01           24       4800  1            B 2021-01-02            6        300  2            C 2021-01-21            6        600  3            D 2021-03-04            6        300  

payments

    cust_id agreement_id       date  payment  0         1            A 2020-12-01      200  1         1            A 2021-02-02      200  2         1            A 2021-02-03      100  3         1            A 2021-05-01      200  4         1            B 2021-01-02       50  5         1            B 2021-01-09       20  6         1            B 2021-03-01       80  7         1            B 2021-04-23       90  8         2            C 2021-01-21      600  9         3            D 2021-03-04      150  10        3            D 2021-05-03      150  

I want to add another row in the payments dataframe when the total payments for the agreement_id in the payments dataframe is equal to the total_fee in the agreement_id. The row would contain a zero value under the payments and the date will be calculated as min(date) (from payments) plus term_months (from agreement).

Here's the results I want for the payments dataframe:

payments

    cust_id agreement_id       date  payment  0         1            A 2020-12-01      200  1         1            A 2021-02-02      200  2         1            A 2021-02-03      100  3         1            A 2021-05-01      200  4         1            B 2021-01-02       50  5         1            B 2021-01-09       20  6         1            B 2021-03-01       80  7         1            B 2021-04-23       90  8         2            C 2021-01-21      600  9         3            D 2021-03-04      150  10        3            D 2021-05-03      150  11        2            C 2021-07-21      150  12        3            D 2021-09-04      150        

The additional rows are row 11 and 12. The agreement_id 'C' and 'D' where equal to the total_fee shown in the agreement dataframe.

https://stackoverflow.com/questions/66927264/how-to-add-rows-based-on-a-condition-with-another-dataframe April 03, 2021 at 10:42AM