Python adding row into dataframe using while loop

So there are a few reasons your loop did not work, I will explain them as we go!

def add_row(x):
    while x['end_date'].max() < x['lapsed_date'].max():
        next_month = x['end_date'].max() + pd.DateOffset(months=1)
        last_row = x.iloc[-1]
        last_row['start_date'] = x['end_date'].max()
        last_row['end_date'] = next_month
        return x.append(last_row)
    return x 

In the above, you call return which returns the result to the code that called the function. This essentially stops your loop from iterating multiple times and returns the result of the first append.

return x.append(last_row) Another caveat here is that dataframe.append() does not actually append to the dataframe, you need to call x = x.append(last_row)

Pandas Append

Secondly, I noted that it may be required to do this over multiple, unique user_id rows. Due to this, in the code below, I have split the dataframe into multiple frames, dictated by the total unique user_id's stored in the frame.

Here is how you can get this to work;

import pandas as pd
from pandas import DataFrame

def add_row(df):

    while df['end_date'].max() < df['lapsed_date'].max():

        new_row = {'user_id': df['user_id'][0],
                   'lapsed_date': df['lapsed_date'].max(),
                   'start_date': df['end_date'].max(),
                   'end_date': df['end_date'].max() + pd.DateOffset(months=1),
                   }

        df = df.append(new_row, ignore_index = True)

    return df ## Note the return is called OUTSIDE of the while loop, ensuring only the final result is returned.


sample = {'user_id': ['A123','A123','B456','B456'],
        'lapsed_date': ['2020-01-02', '2020-01-02', '2019-10-01', '2019-10-01'],
        'start_date' : ['2019-01-02', '2019-02-02', '2019-08-01', '2019-09-01'],
        'end_date' : ['2019-02-02', '2019-03-02', '2019-09-01', '2019-10-01']
        }

df = pd.DataFrame(sample,columns= ['user_id', 'lapsed_date', 'start_date', 'end_date'])

df['lapsed_date'] = pd.to_datetime(df['lapsed_date'])
df['start_date'] = pd.to_datetime(df['start_date'])
df['end_date'] = pd.to_datetime(df['end_date']) 


ids = df['user_id'].unique()

g = df.groupby(['user_id'])

result = pd.DataFrame(columns= ['user_id', 'lapsed_date', 'start_date', 'end_date'])

for i in ids:
    group = g.get_group(i)
    result = result.append(add_row(group), ignore_index=True)


print(result)

1. Split the frames based on unique user id's
2. Create empty data frame to store result in under result
3. Iterate over all user_id's
4. Run the same while loop, ensuring that df is updated with the append rows
5. Return the result and print

Hope this helps!