How to delete all zero' values in excel using python

Question

I want to delete all cells contains zero in excel/csv file. It should not be like a column wise. Because if i keep all zeros it is reflecting in my pivot table. so, I have to delete all cells contains zero.

Below are my input excel :

S.No    Name    Module     Val1         Val2    Val3    Val4    Total
1      Name1    Module1    0.0004      1.874    0.9 0    0      2.7744
2      Name2    Module2    0.998       1.08     0.54     0.643  3.261
3      Name3    Module3    0           0        0        0.292  0.292
4      Name4    Module4    0.52    0        0.18     0      0.7
5      Name5    Module5    0           0        0.295    0      0.295
6      Name6    Module6    0           0.98     0        1.51   2.49
7      Name7    Module7    2.0876      0        0.8976   0      2.9852
8      Name8    Module8    0           1.0003   0        1.035  2.0353
9      Name9    Module9    0           0        0        2.09   2.09
10     Name10   Module10   0.52 0      0.18     0        0      0.7
11     Name11   Module11   0           1.874    0        0      1.874
12     Name12   Module12   0.292    0.998       0        0.2091 1.4991

Expected output :

Delete all zero cell values.

S.No    Name    Module  Val1    Val2    Val3    Val4    Total
1       Name1   Module1 0.0004  1.874   0.9             2.7744
2       Name2   Module2 0.998   1.08    0.54    0.643   3.261
3       Name3   Module3                         0.292   0.292
4       Name4   Module4 0.52            0.18            0.7
5       Name5   Module5                 0.295           0.295
6       Name6   Module6 0       0.98            1.51    2.49
7       Name7   Module7 2.0876          0.8976          2.9852
8       Name8   Module8         1.0003          1.035   2.0353
9       Name9   Module9                 2.09            2.09
10      Name10  Module10 0.52   0.18                    0.7
11      Name11  Module11 0      1.874                   1.874
12      Name12  Module12 0.292  0.998           0.2091  1.4991

jezrael · Accepted Answer · 2019-08-02 07:53:06Z

7

It is possible, but get mixed values - numeric with empty strings, so numeric operation failed:

df = df.replace(0, '')

So better is replace by missing values - all values are numeric, because NaN is float value:

df = df.replace(0, np.nan)

answered Aug 2, 2019 at 7:53

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anky · Accepted Answer · 2019-08-02 07:57:46Z

5

You can also try df.mask():

df=df.mask(df.eq(0),'')

Or:

df=df.mask(df.eq(0)) #this will replace 0 with NaN

Similarly df.where()

df=df.where(df.ne(0),'')

Or:

df=df.where(df.ne(0)) #this replaces with NaN

answered Aug 2, 2019 at 7:57

anky

75.3k11 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to delete all zero' values in excel using python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related