CODEWITHSUNDEEP
pythonpandasdataframemissing-data
OS95
OS95

Reputation: 35

Inserting rows in dataframe based on previous rows

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)```

Upvotes: 2

Views: 278

Answers (1)

Corralien
Corralien

Reputation: 120409

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

Upvotes: 1

Related Questions