DataFrame drop rows whose column has certain values

Question

For my question, I have found quite a few entries that explain how to drop rows with specific column values; however, I've not been able to find (I know a post might be out there) a post that addresses how to drop rows in a dataframe with specific column values across multiple columns (34 in this case).

1. How to drop rows of Pandas DataFrame whose value in certain columns is NaN
2. Drop Rows by Multiple Column Criteria in DataFrame
3. Drop rows in pandas dataframe based on columns value

baddata

zip        age    item1    item2    item3    item4    item5    item6    item7    item34

12345       10    1        0        1        1        0         0       1           0

23456       20   10       111       11       1        0         1       9           8

45678       60    1        0         1       1        0         1       0           1

I want to retain all those rows that has values of '1' or '0' (drop all rows for which col values in 34 cols are not '1' or '0'). This is what I tried so far:

baddata = pd.DataFrame(data=dirtydata, columns=['zip','age','item1','item2'...'item34'])

gooddata=baddata.dropna() # some rows have NaN; drops rows with NaN values

option-1:

gooddata[gooddata[['item1','item2'...'item34']].isin([0,1])] #this makes values for zip and age NaN; not sure why?

option-2:

gooddata[gooddata[['item1','item2'...'item34']].map(len) < 2).any(axis=1)] #also tried replacing 'any' with 'all'; did not work

option-3:

cols_of_interest=['item1','item2'...'item34'] gooddata[gooddata.drop(gooddata[cols_of_interest].map(len) < 2)] #doubtful about the syntax and usage of functions

Answer 1

Start by selecting all the columns after age

df[df.columns[2:]]

   item1  item2  item3  item4  item5  item6  item7  item34
0      1      0      1      1      0      0      1       0
1     10    111     11      1      0      1      9       8
2      1      0      1      1      0      1      0       1

check if their values are 0 or 1

df[df.columns[2:]].isin((0, 1))

   item1  item2  item3 item4 item5 item6  item7 item34
0   True   True   True  True  True  True   True   True
1  False  False  False  True  True  True  False  False
2   True   True   True  True  True  True   True   True

check if all values in the row are True

df[df.columns[2:]].isin((0, 1)).all(axis=1)

0    True
1    False
2    True
dtype: bool

select only these rows

df[df[df.columns[2:]].isin((0, 1)).all(axis=1)]

     zip  age  item1  item2  item3  item4  item5  item6  item7  item34
0  12345   10      1      0      1      1      0      0      1       0
2  45678   60      1      0      1      1      0      1      0       1

EDIT

Breaking this out a bit more clearly, we have

relevant_columns = df[df.columns[2:]]
values_as_ints = relevant_columns.convert_objects(convert_numeric=True)
values_valid = values_as_ints.isin((0, 1))
row_valid = values_valid.all(axis=1)
good_rows = df[row_valid]

Answer 2

Try this:

 print df
 zip     age  item1  item2  item3  item4  item5  item6  item7  item34
12345   10      1      0      1      1      0      0      1       0
23456   20     10    111     11      1      0      1      9       8
45678   60      1      0      1      1      0      1      0       1

dfSlice = df[df.columns[2:]]
def mapZeroOne(x):
    if x == 0 or x == 1:   
       return x

dfNa = dfSlice.applymap(mapZeroOne)
print dfNa

      item1  item2  item3  item4  item5  item6  item7  item34
12345    1.0    0.0    1.0      1      0      0    1.0     0.0
23456    NaN    NaN    NaN      1      0      1    NaN     NaN
45678    1.0    0.0    1.0      1      0      1    0.0     1.0

dfAge =  df[['zip',"age"]] 
print  dfAge

zip     age
12345   10
23456   20
45678   60


df_new = pd.concat([dfAge, dfNa], axis=1)
 print df_new 

zip     age  item1  item2  item3  item4  item5  item6  item7  item34
12345   10    1.0    0.0    1.0      1      0      0    1.0     0.0
23456   20    NaN    NaN    NaN      1      0      1    NaN     NaN
45678   60    1.0    0.0    1.0      1      0      1    0.0     1.0

print df_new.dropna()

zip    age  item1  item2  item3  item4  item5  item6  item7  item34
12345   10    1.0    0.0    1.0      1      0      0    1.0     0.0
45678   60    1.0    0.0    1.0      1      0      1    0.0     1.0

You may need to adjust 0 to "0" and 1 to "1".