Search code examples
pythonpandasnumpyreplaceseries

Pandas replace with value from another row

I have a table:

Object Col1 Col2 Col3 Col4
reference 10 14 7 29
Obj1 0 9 1 30
Obj2 1 16 0 17
Obj3 9 21 3 0
Obj4 11 0 4 22

I want to transform it by condition: if any cell (except the cells of the 1st row) is =0, then it must be replaced with an incremented (X+1) value from the 1st row of this column.

The resulting table is:

Object Col1 Col2 Col3 Col4
reference 10 14 7 29
Obj1 11 9 1 30
Obj2 1 16 8 17
Obj3 9 21 3 30
Obj4 11 15 4 22

I've tried this variant:

df = np.where(df[df == 0] == 0, df.iloc[0] + 1, df)

but the result is ndarray, not DataFrame and the performance is not well enough.

Is there a way to do this using only pandas' utils?

Solution

  • Use DataFrame.mask:

    out = df.mask(df == 0, df.iloc[0] + 1, axis=1)
print (out)
           Col1  Col2  Col3  Col4
Object                           
reference    10    14     7    29
Obj1         11     9     1    30
Obj2          1    16     8    17
Obj3          9    21     3    30
Obj4         11    15     4    22