python - Add the same row multiple times from a pandas dataframe to a new one, each time altering a value in a specific column

Question

I have a df like this:

    MEMBER_ID FirstName LastName  I    MONTH
0   1          John      Doe      10   0
1   2          Mary      Jones    15   0
2   3          Andy      Right    8    0

I need to create a new df (df_new) which contains each row corresponding to a unique MEMBER_ID, replicated by the amount of times that is in the 'I' column, and the 'MONTH' column has to be filled from 0 and up to and including the value of 'I' in the original df. For example: first row (MEMBER_ID==1) has to be replicated 10 times (value of 'I') and the only difference would be the 'MONTH' column which will go from 0 to 10. After that the rows continue for the next unique value in the 'MEMBER_ID' column. So I need the df_new to look like this:

    MEMBER_ID FirstName LastName  I    MONTH
0   1          John      Doe      10   0
1   1          John      Doe      10   1
2   1          John      Doe      10   2
3   1          John      Doe      10   3
...
10  1          John      Doe      10   10
11  2          Mary      Jones    15   0
12  2          Mary      Jones    15   1
13  2          Mary      Jones    15   2
...
N-1 3          Andy      Right    8    7
N   3          Andy      Right    8    8 

I have tried this but it gives me gibberish:

df_new=pd.DataFrame(columns=['MEMBER_ID','FirstName','LastName','I','MONTH'])

for i in range(len(df)):
   max_i=df.iloc[i]["I"]  #this gets the value in the "I" column
   for j in range(0,max_i+1): #to append same row max_i+1 times since I need MONTH to start with 0
      df_new.loc[i]=df.iloc[i]  #this picks the whole row from the original df
      df_new["MONTH"]=j      #this assigns the value of each iteration to the MONTH column
      df_new=df_new.append(df_new.loc[i],ignore_index=True)

Thank you for your help, dear community!

question from:https://stackoverflow.com/questions/65909334/add-the-same-row-multiple-times-from-a-pandas-dataframe-to-a-new-one-each-time

Answer 1

I was able to fix the SettingWithCopyWarning with this:

index =0
for i in range(len(df)):
    for j in range(df.iloc[i]["I"]+1):
        row=df.iloc[i]
        df_new=df_new.append(row,ignore_index=True)
        df_new.at[index,'MONTH']=j
        index+=1

df.head()