CODEWITHSUNDEEP
pythonpandas
Alessandro Ceccarelli
Alessandro Ceccarelli

Reputation: 1945

Replace zeros with last value different from zero

I have the following dataframe:

print(inventory_df)

      dt_op  Prod_1  Prod_2  Prod_n
1  10/09/18       5      50       2
2  11/09/18       4       0       0
3  12/09/18       2       0       0
4  13/09/18       0       0       0
5  14/09/18       4      30       1

I would like to change the values equal to zero, with the last value != from zero, in each columns, as:

print(final_inventory_df)

      dt_op  Prod_1  Prod_2  Prod_n
1  10/09/18       5      50       2
2  11/09/18       4      50       2
3  12/09/18       2      50       2
4  13/09/18       2      50       2
5  14/09/18       4      30       1

How could I do it?

Upvotes: 2

Views: 269

Answers (2)

Joe
Joe

Reputation: 12417

Just another option:

df.iloc[:,1:] = df.iloc[:,1:].replace(0, np.nan).ffill().astype(int)

Upvotes: 2

jezrael
jezrael

Reputation: 862581

Idea is replace 0 to NaNs by mask and then forward filling them by previous non missing values:

cols = df.columns.difference(['dt_op'])
df[cols] = df[cols].mask(df[cols] == 0).ffill().astype(int)

Similar solution with numpy.where:

df[cols] = pd.DataFrame(np.where(df[cols] == 0, np.nan, df[cols]), 
                        index=df.index, 
                        columns=cols).ffill().astype(int)


print (df)
      dt_op  Prod_1  Prod_2  Prod_n
1  10/09/18       5      50       2
2  11/09/18       4      50       2
3  12/09/18       2      50       2
4  13/09/18       2      50       2
5  14/09/18       4      30       1

Solution for fun - convert to integer all columns without dt_op:

d = dict.fromkeys(df.columns.difference(['dt_op']), 'int')
df = df.mask(df == 0).ffill().astype(d)

Upvotes: 5

Related Questions