Indices of non-number and zero values cells in dataframe

user_7082164 0
Viewed 586

I have dataset (bigger than this part) where I store float numbers. But some of the data is missing. How can I find all of the indices where data is missing or has non-number value? I was looking for similar questions on SO, but mostly it was about dropping rows, but probably similar question was asked before but I couldn't find it. I need to replace these values, so I need to identify them all.

I want to get indices of cells [86,2], [87,2], [87,3]. How can I easily retrieve them?

         0       1       2       3       4
85  1645.0  1596.0  1578.0  1567.0  1580.0
86  1554.0  1506.0     0.0  1466.0  1469.0
87  1588.0  1510.0    'ff'       0  1489.0

I include JSON if anyone needs to recreate example:

{"0":{"85":1645.0,"86":1554.0,"87":1588.0},"1":{"85":1596.0,"86":1506.0,"87":1510.0},"2":{"85":1578.0,"86":0.0,"87":'ff'},"3":{"85":1567.0,"86":1466.0,"87":0},"4":{"85":1580.0,"86":1469.0,"87":1489.0}}
pandas dataset numpy python
1 Answers

You can use pd.to_numeric with optional parameter errors='coerce' to convert each series in dataframe to numeric type if possible otherwise the values which can not be converted are substituted with NaN values. Then you can create a mask m on the conditions where d is equal to 0 or NaN. Now using DataFrame.stack you can stack the columns in mask m to multilevel index creating a series s. Now, filter this series s where the values are True. Thereafter you can get the required indices using Series.index.tolist().

Use:

d = df.apply(lambda s: pd.to_numeric(s, errors="coerce"))
m = d.eq(0) | d.isna()
s = m.stack()
indices = s[s].index.tolist()

Intermediate Steps:

# print(d)
         0       1       2       3       4
85  1645.0  1596.0  1578.0  1567.0  1580.0
86  1554.0  1506.0     0.0  1466.0  1469.0
87  1588.0  1510.0     NaN     0.0  1489.0

# print(m)
       0      1      2      3      4
85  False  False  False  False  False
86  False  False   True  False  False
87  False  False   True   True  False

# print(s)
85  0    False
    1    False
    2    False
    3    False
    4    False
86  0    False
    1    False
    2     True
    3    False
    4    False
87  0    False
    1    False
    2     True
    3     True
    4    False
dtype: bool

# print(s[s])
86  2    True
87  2    True
    3    True
dtype: bool

Result:

# print(indices)

[('86', '2'), ('87', '2'), ('87', '3')]
user_128331660
Related