Search code examples
pythonpandasdataframemulti-index

Replacing zeros with last non-zero value for each column on multi-index dataframe

I have the following multi-index dataframe:

        0   1   2   3
0   0   0   0   0   0
0   1   36  19  2   4
0   2   233 21  2   4
0   3   505 25  1   4
0   4   751 27  1   4
0   5   976 28  1   4
0   9   0   0   0   0
0   10  0   0   0   0
0   11  0   0   0   0
0   12  0   0   0   0
1   0   40  19  2   4
1   1   323 18  1   4
1   2   595 24  1   4
1   3   844 26  1   4
1   4   0   0   0   0
1   5   0   0   0   0
1   9   0   0   0   0
1   10  0   0   0   0
1   11  0   0   0   0
1   12  0   0   0   0

What is the easiest way that I can I repeat the last value more than zero until the end of the first group? The desired outcome is:

        0   1   2   3
0   0   0   0   0   0
0   1   36  19  2   4
0   2   233 21  2   4
0   3   505 25  1   4
0   4   751 27  1   4
0   5   976 28  1   4
0   9   976 28  1   4
0   10  976 28  1   4
0   11  976 28  1   4
0   12  976 28  1   4
1   0   40  19  2   4
1   1   323 18  1   4
1   2   595 24  1   4
1   3   844 26  1   4
1   4   844 26  1   4
1   5   844 26  1   4
1   9   844 26  1   4
1   10  844 26  1   4
1   11  844 26  1   4
1   12  844 26  1   4

Thanks

Solution

  • You can use replace with method='ffill' for each group:

    out = df.groupby(level=0).apply(lambda x: x.replace(0, method='ffill'))
print(out)

# Output
        0   1  2  3
0 0     0   0  0  0
  1    36  19  2  4
  2   233  21  2  4
  3   505  25  1  4
  4   751  27  1  4
  5   976  28  1  4
  9   976  28  1  4
  10  976  28  1  4
  11  976  28  1  4
  12  976  28  1  4
1 0    40  19  2  4
  1   323  18  1  4
  2   595  24  1  4
  3   844  26  1  4
  4   844  26  1  4
  5   844  26  1  4
  9   844  26  1  4
  10  844  26  1  4
  11  844  26  1  4
  12  844  26  1  4