1

expanding on Drop rows with all zeros in pandas data frame, how would I edit that solution to remove all rows from a data frame that contain only 0 and NAs.

I tried:

df.loc[~(df == 0 | df.isnan()).all(axis=1))]

but got ValueError: The truth value of a Series is ambiguous.

EDIT

df[(~(df == 0 | df.isna())).all(axis=1)] works for one of my dataframes but not the following: 

df = pd.DataFrame({'x': {'Total': -3.637978807091713e-09}, 'y': {'Total': -3.637978807091713e-09}, 'z': {'Total': -3.637978807091713e-09}, 'a': {'Total': 0.0}, 'b': {'Total': 1387241.5974624965}, 'c': {'Total': 1387241.5974624965}})

When I run df[(~(df == 0 | df.isna())).all(axis=1)], I get an empty dataframe when the row should not be dropped because there exists a value that is not equal to either 0 or NA.

cpage
  • 119
  • 6
  • 27

2 Answers2

6

IIUC, you need to wrap the invert under parenthesis :

df[~((df==0)|(df.isna())).all(1)]
anky
  • 74,114
  • 11
  • 41
  • 70
  • This doesn't seem to work for `df = pd.DataFrame({'x': {'Total': -3.637978807091713e-09}, 'y': {'Total': -3.637978807091713e-09}, 'z': {'Total': -3.637978807091713e-09}, 'a': {'Total': 0.0}, 'b': {'Total': 1387241.5974624965}, 'c': {'Total': 1387241.5974624965}})` – cpage Jan 08 '20 at 17:43
  • @cpage i didnt get your point, if you are looking to drop rows which has 0 or `NaN` in any columns and not all columns, replace `all(1)` with `any(1)` – anky Jan 08 '20 at 17:52
  • `df[(~(df == 0 | df.isna())).all(axis=1)]` drops the one row of this case when it should not be dropped because there is a col that is not either 0 or `NaN`. – cpage Jan 08 '20 at 18:02
  • @cpage probably better if you can edit your question to show why this doesnt work – anky Jan 08 '20 at 18:10
2

We can use the inverse logic of Morgan Laws to get rid of the operator ~. Sometimes it is good to use .mul instead of & to avoid the use of parentheses.

df[df.notna().mul(df.ne(0)).all(axis = 1)]
#df[(df.notna() & df.ne(0)).all(axis = 1)]
ansev
  • 30,322
  • 5
  • 17
  • 31