Restate Values Using Forward Fill And Groupby With Condition (reduce Value By 1 At Each Row)

I have 2 columns: x and y. I want to restate the values of y and make it continuous like 10,9,8,7,6...0 so if there are missing values in y it should be filled by = (previous value

Solution 1:

If I understood correctly, you want to group over the x column and then starting from the first value in y to count down.

And in case when counting down you reach negative values, reset the counter to the corresponding y value.

This is what the code below does:

out = df.groupby('x').apply(lambda dd : pd.Series(range(int(dd['y'].iloc[0]), int(dd['y'].iloc[0]) - len(dd), -1)))
df['desired_output'] = out.reset_index(drop=True)

while (df['desired_output'] < 0).any():
    corr = df.groupby('x').apply(lambda dd : (dd['y'] +1).where(dd['desired_output'].eq(-1), 0).cumsum())
    df['correcting'] = corr.reset_index(drop=True).astype(int)
    df['desired_output'] = df['correcting'] + df['desired_output']

df.drop('correcting', axis=1, inplace=True)

The while loop allows to perform the same operation until there are no more negative values in 'desired_output' column, adding each time the correct value to set the first negative number (-1) to the value in y.

Assuming df is the sample dataframe you posted, the final result is:

    x     y  desired_output
0110.010119.0921NaN831NaN7416.06515.05614.04713.03812.02911.011010.001114.041210.031310.021411.011528.081626.071723.061825.051924.042023.03

The assumption here is that x is monothonic (no sparsed values) and that in the row where x increases, y is not NaN, like shown by your example. Also, y should not be NaN when the count down is -1.