CODEWITHSUNDEEP
python-3.xpandas
krizajb
krizajb

Reputation: 1814

Locate rows with 0 value columns and set them to none pandas

Data:

f a b
5 0 1
5 1 3
5 1 3
5 6 3
5 0 0
5 1 5
5 0 0

I know how to locate the rows with both columns being 0, setting them to None on the other hand is a mystery. 

df_o[(df_o['a'] == 0) & (df_o['d'] == 0)]
# set a and b to None

Expected result:

f a b 
5 0 1
5 1 3
5 1 3
5 6 3
5 None None
5 1 5
5 None None

Upvotes: 1

Views: 71

Answers (5)

Omar Cusma Fait
Omar Cusma Fait

Reputation: 321

This is how I would do it:

import pandas as pd


a = pd.Series([0, 1, 1, 6, 0, 1, 0])
b = pd.Series([1, 3, 3, 3, 0, 5 ,0])

data = pd.DataFrame({'a': a, 'b': b})


v = [[data[i][j] for i in data] == [0, 0] for j in range(len(data['a']))]   # spot null rows

a = [None if v[i] else a[i] for i in range(len(a))]
b = [None if v[i] else b[i] for i in range(len(b))]

data = pd.DataFrame({'a': a, 'b': b})


print(data)

Output:

     a    b
0  0.0  1.0
1  1.0  3.0
2  1.0  3.0
3  6.0  3.0
4  NaN  NaN
5  1.0  5.0
6  NaN  NaN

Upvotes: 0

Turbo_Penguin
Turbo_Penguin

Reputation: 1

It is surely not the most elegant way to do this, but maybe this helps.

import pandas as pd
data = {'a': [0,1,1,6,0,1,0],
        'b':[1,3,3,3,0,5,0]}

df_o = pd.DataFrame.from_dict(data)
df_None = df_o[(df_o['a'] == 0) & (df_o['b'] == 0)]
df_o.loc[df_None.index,:] = None
print(df_o)

Out:

    a    b
0  0.0  1.0
1  1.0  3.0
2  1.0  3.0
3  6.0  3.0
4  NaN  NaN
5  1.0  5.0
6  NaN  NaN

Upvotes: 0

BAC83
BAC83

Reputation: 891

df.replace(0, np.nan) -- to get NaNs (possibly more useful) df.replace(0, 'None') -- what you actually want

Upvotes: 0

Ankur Sinha
Ankur Sinha

Reputation: 6639

One way I could think of is like this. Create an extra copy of the dataframe and check both individually while setting the value to None on the main dataframe. Not the cleanest solutions but:

import pandas as pd
import numpy as np

df = pd.DataFrame()

df['f'] = [5,5,5,5,5,5,5]
df['a'] = [0,1,1,6,0,1,0]
df['b'] = [1,3,3,3,0,5,0]

df1 = df.copy()
df['a'] = np.where((df.a == 0) & (df.b == 0), None, df.a)
df['b'] = np.where((df1.a == 0) & (df1.b == 0), None, df.b)
print(df)

Output:

   f     a     b
0  5     0     1
1  5     1     3
2  5     1     3
3  5     6     3
4  5  None  None
5  5     1     5
6  5  None  None

Upvotes: 0

jezrael
jezrael

Reputation: 862611

If working with numeric values None is converted to NaN and integers to float by design:

df_o.loc[(df_o['a'] == 0) & (df_o['b'] == 0), ['a','b']] = None

print (df_o)
     a    b
0  0.0  1.0
1  1.0  3.0
2  1.0  3.0
3  6.0  3.0
4  NaN  NaN
5  1.0  5.0
6  NaN  NaN

Another solution with DataFrame.all for check if all Trues per rows with axis=1:

df_o.loc[(df_o[['a', 'b']] == 0).all(axis=1), ['a','b']] = None
print (df_o)
     a    b
0  0.0  1.0
1  1.0  3.0
2  1.0  3.0
3  6.0  3.0
4  NaN  NaN
5  1.0  5.0
6  NaN  NaN

Details:

print ((df_o[['a', 'b']] == 0))
       a      b
0   True  False
1  False  False
2  False  False
3  False  False
4   True   True
5  False  False
6   True   True

print ((df_o[['a', 'b']] == 0).all(axis=1))
0    False
1    False
2    False
3    False
4     True
5    False
6     True
dtype: bool

Upvotes: 4

Related Questions