Payment schedule simulation in Pandas-CodePudding

I'm trying to make a payment schedule based on Start Date and End Date but I'm stucking.

Below is my sample code:

import pandas as pd
from datetime import datetime as dt
import numpy as np
from dateutil.relativedelta import relativedelta
from tqdm import tqdm
tqdm.pandas()

df = pd.DataFrame({
    'REF_NO': ['VN1211001'],
    'From': ['2022-3-31'],
    'To': ['2024-4-12'],
    'Frequency':[6],
    'Amount':[600000]})
df['From'] = pd.to_datetime(df['From'])
df['To'] = pd.to_datetime(df['To'])

df['Date Diff'] = (df['To'] - df['From']).dt.days
df['Month Diff'] = ((df['To'] - df['From'])/np.timedelta64(1,'M'))
df['Repayment Times'] = np.ceil(df['Month Diff']/df['Frequency'])
df['Repayment Amount'] = df['Amount']/df['Repayment Times']

def add_months(start_date,delta_period):
    end_date = start_date   relativedelta(months=delta_period)
    return end_date

df['Next Payment'] = df.progress_apply(lambda row: add_months(row['From'],row['Frequency']),axis=1)

This is what I get:

What I want is all of the next payment will be showed. Ex: 2023-03-31,2023-09-30...

Is there any way to make this or sample same with it that I can learn?

Thank you.

CodePudding user response：

Prepare a new empty data frame and add processing for each row of the data frame. Get a list of start and end dates and payment intervals from the resulting rows. The first start date is not needed, so add rows in a loop process starting with the second payment date.

new_df = pd.DataFrame()
for idx, row in df.iterrows():
    pay_list = pd.date_range(row['From'], row['To'], freq=str(row['Frequency']) 'M')
    if row['To'] > pay_list[-1]:
        pay_list = pay_list.append(pd.date_range(row['To'], row['To'], periods=1)) 
        for p in pay_list[1:]:
            row['Next Payment'] = p
            new_df = new_df.append(row.T, ignore_index=True)

  new_df

    REF_NO  From    To  Frequency   Amount  Date Diff   Month Diff  Repayment Times     Repayment Amount    Next Payment
0   VN1211001   2022-03-31  2024-04-12  6.0     600000.0    743.0   24.411179   5.0     120000.0    2022-09-30
1   VN1211001   2022-03-31  2024-04-12  6.0     600000.0    743.0   24.411179   5.0     120000.0    2023-03-31
2   VN1211001   2022-03-31  2024-04-12  6.0     600000.0    743.0   24.411179   5.0     120000.0    2023-09-30
3   VN1211001   2022-03-31  2024-04-12  6.0     600000.0    743.0   24.411179   5.0     120000.0    2024-03-31
4   VN1211001   2022-03-31  2024-04-12  6.0     600000.0    743.0   24.411179   5.0     120000.0    2024-04-12