how to remove 0's from a string without impacting other cells in pandas data frame?

user_10157587 0
Viewed 63

I have a data frame which has "0's" and looks as below:

df = pd.DataFrame({
    'WARNING':['4402,43527,0,7628,54337',4402,0,0,'0,1234,56437,76252',0,3602],
    'FAILED':[0,0,'5555,6753,0','4572,0,8764,8753',9876,0,'0,4579,7514']
})

I want to remove the zeroes from the strings where there are multiple values such that the results df looks like this:

df = pd.DataFrame({
    'WARNING':['4402,43527,7628,54337',4402,0,0,'1234,56437,76252',0,3602],
    'FAILED':[0,0,'5555,6753','4572,8764,8753',9876,0,'4579,7514']
})

However the ones which have individual 0's in a cell should remain intact. How do I achieve this?

pandas python
3 Answers
df = pd.DataFrame({
    'WARNING':['0,0786,1230,01234,0',4402,0,0,'0,1234,56437,76252',0,3602],
    'FAILED':[0,0,'5555,6753,0','4572,0,8764,8753',9876,0,'0,4579,7514']
})
df.apply(lambda x: x.str.strip('0,|,0')).replace(",0,", ",")

Output:

            WARNING            FAILED
0    786,1230,01234               NaN
1               NaN               NaN
2               NaN         5555,6753
3               NaN  4572,0,8764,8753
4  1234,56437,76252               NaN
5               NaN               NaN
6               NaN         4579,7514
user_57201960

I would solve it with a list comprehension.

In [1]: df.apply(lambda col: col.astype(str).apply(lambda x: ','.join([y for y in x.split(',') if y != '0']) if ',' in x else x), axis=0)
Out[1]:  
           FAILED                WARNING
0               0  4402,43527,7628,54337
1               0                   4402
2       5555,6753                      0
3  4572,8764,8753                      0
4            9876       1234,56437,76252
5               0                      0
6       4579,7514                   3602

Breaking it down:

  1. Iterate over all columns with df.apply(lambda col: ..., axis=0)
  2. Convert each column's values to string with col.astype(str)
  3. Apply a function to each "cell" of col with .apply(lambda x: ...)
  4. The lambda function first checks if ',' exists in x, otherwise returns the original value of x
  5. If ',' in x, it splits x by ',', which creates a list of y's
  6. It keeps only the y != '0'
  7. It joins everything at the end with a ','.join(...)
user_27000720

You can use regex with a negative look behind to replace 0, only if it not preceded by another digit.

import re

df.applymap(lambda x: re.sub(r'(?<![0-9])0,', '', str(x)))

                 WARNING          FAILED
0  4402,43527,7628,54337               0
1                   4402               0
2                      0     5555,6753,0
3                      0  4572,8764,8753
4       1234,56437,76252            9876
5                      0               0
6                   3602       4579,7514

For the test case W-B points out:

s = '0,0999,9990,999'
re.sub(r'(?<![0-9])0,', '', s)
#'0999,9990,999'
user_43333590
Related