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

user_15086844 0
Viewed 974

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!

pandas dataframe python
2 Answers

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()
user_150868440

The problem is, that you overwrite df_new many times. This should work. df ist the old DataFrame

df_new = pd.DataFrame()

for member in range(len(df)): #iterate over every member
    for count in range(df.iloc[member]['I']+1): # you want to add 'I'+1 rows
        row = df.iloc[member] # select the row you want to add
        row['MONTH'] = count #change the month-vale of the row to add
        df_new = df_new.append(row,ignore_index=True) # add the row to the new DataFrame
df_new

Otherwise please show, what's wrong with the output.

user_150909350
Related