Inserting rows in dataframe based on previous rows

Question

I need to insert rows based on the column week, in some cases i have missing weeks in the middle of the dataframe and i want to insert rows to fill in the missing rows as copies of the last existing row, in this case copies of week 8 but with incremental value for the column week : on this table you can see the jump from week 8 to 12

the perfect output would be as follow: the final table with incremental values in column week the correct way

Below is the code i have, it inserted only one row which is 11

    for f in range(1, 52 , 1):
        if final.iat[i,8]==  f and final.iat[i-1,8] != f-1 :
             if final.iat[i,8] > final.iat[i-1,8] and  final.iat[i,8] != (final.iat[i-1,8] - 1):
                    line = final.iloc[i-1]
                    c1 = final[0:i]
                    c2 = final[i:]
                    c1.loc[i]=line
                    concatinated = pd.concat([c1, c2])
                    concatinated.reset_index(inplace=True)
                    concatinated.iat[i,11] = concatinated.iat[i-1,11]
                    concatinated.iat[i,9]= f-1
                    finaltemp = finaltemp.append(concatinated)```

Corralien · Accepted Answer · 2021-07-20 11:58:21Z

1

Build the full list of weeks and use pd.merge then fill forward to replace NaN:

weeks = range(df['Week'].min(), df['Week'].max()+1)
out = pd.merge(df, pd.Series(weeks, name='Week'), how='right').ffill()

>>> out
  index  type Project  Week  Cumulated Hours  Remaining hours
0   XXY   1.0       A     7             18.0           2000.0
1   XXY   1.0       A     8             20.0           1900.0
2   XXY   1.0       A     9             20.0           1900.0
3   XXY   1.0       A    10             20.0           1900.0
4   XXY   1.0       A    11             20.0           1900.0
5   XXY   1.0       A    12             24.0           1500.0
6   XXY   1.0       A    13             36.0           1400.0

answered Jul 20, 2021 at 11:58

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting rows in dataframe based on previous rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related