pandas return rows when certain columns are null or zero-CodePudding

I have a Dataframe that looks like this.

df = pd.DataFrame(data=[['Stephen','Oslo','NaN','NaN'],
                        ['Jane','Stockholm','osgar',0],
                        ['shan',0,0,0],
                        ['van','NaN','NaN','saf']],
                  columns=['Name','City','fas','san'])

I want all rows when both columns (City and fas) are either 0 or NaN

OUTPUT I AM LOOKING FOR

Name	City	fas	san
shan	0	0	0
van	NaN	NaN	saf

CodePudding user response：

We could use isin all:

out = df[df[['City','fas']].isin(['NaN', 0]).all(axis=1)]

Output:

   Name City  fas  san
2  shan    0    0    0
3   van  NaN  NaN  saf

CodePudding user response：

This will replace all the NaN in your database as np.nan (if they aren't already didn't know if that was just for us to see as an example), then it will replace all np.nan with a 0 then do a lookup which finds only results were both fas and san are 0

df = pd.DataFrame(data=[['Stephen','Oslo','NaN','NaN'],['Jane','Stockholm','osgar',0],['shan',0,0,0],['van','NaN','NaN','saf']],columns=['Name','City','fas','san'])
df = df.apply(lambda x : x.replace('NaN', np.nan))
df.fillna(0, inplace = True)
df.loc[(df['fas'] == 0) & (df['san'] == 0)]

CodePudding user response：

We can also use 'and' 'or' combination to filter

df[((df["City"] == 0)|(df["City"] == 'NaN')) & ((df["fas"] == 0)|(df["fas"] == 'NaN'))]