I have some categorical data which also contains '?' as data in some rows. I need to filter those rows depending on '?', that which row contain that instances will be deleted.

enter image description here enter image description here

I tried to drop those rows by applying these command but I failed.

train = train.drop[~train.str.contains('\?')]
train = train.drop[train['?']]

How could I identify those rows which contain '?' instance and drop those row ?


You can replace ? with nan and use dropna(). This will work if you don't already have rows with nan entries that you want to keep.

train = train.replace('?', np.nan).dropna()

Another option is to filter rows where any value is ?.

train = train[~(train == '?').any(axis=1)]


After looking at your data I found the problem: Your csv file has spaces after the commas, so the rows containing ? have a leading space.

If you use train = pd.read_csv('adult.data', sep=', ', engine='python') to read your data then either of the above methods will work.

  • $\begingroup$ Sorry. It's not working. I applied both command. import numpy as np train_new = train.replace('?', np.nan).dropna() for this code there are no change in dataset. train_new2 = train[~(train == '?').any(axis=1)] TypeError: Could not compare ['?'] with block values and when I applied this code its shows that error. $\endgroup$
    – IS2057
    Jan 10 '18 at 15:18
  • $\begingroup$ Can you copy-paste some rows of your data into the question so I can test myself? $\endgroup$
    – Imran
    Jan 10 '18 at 15:26
  • $\begingroup$ You will find the data from the given link (drive.google.com/file/d/1mGPTFWbvcrQZfuhRkd4yYHxzhWNk6XNx/…) and for adding column please run this code train.columns = ['age','workclass','fnlwgt','education','education-num', 'marital-status','occupation','relationship','race','sex', 'capital-gain','capital-loss','hours-per-week','native-country','label'] $\endgroup$
    – IS2057
    Jan 10 '18 at 15:43
  • $\begingroup$ Yes, its working. I didn't notice that space is counted there. I applied your suggested command just giving a space. train= train.replace(' ?', np.nan).dropna() Thank you so much. $\endgroup$
    – IS2057
    Jan 10 '18 at 15:58
  • $\begingroup$ OK, great! Feel free to accept my answer (click the check mark) if it solved your problem! $\endgroup$
    – Imran
    Jan 10 '18 at 15:59

The answer given by Imran is correct and more general. It will allow you to drop any row containing '?' in any column.

Posting here only to explain why your two code attempts don't work. In general, for sub-setting rows, you need boolean labels for each row indicating whether to include it or not. So, you should write your expression such that it returns those boolean masks. Your attempt was almost correct, but you ran .str.contains on the entire data frame. Instead, you should run it on individual columns. For conditions on more than one columns, combine. I demonstrate in the code below.

# create sample data containing missing values    
tmp =  pd.DataFrame({'a' : ['Craft-repair', 'Sales', '?'], 'b' : [40, '?', 60]})
print ~(tmp['a'].str.contains('\?')) # how boolean mask is generated
tmp = tmp[~(tmp['a'].str.contains('\?'))] # filter on single column
print tmp[~ ((tmp['a'].str.contains('\?')) | (tmp['b'].str.contains('\?')))]

If the '?' was present in the data while reading it, you can also replace it by np.nan at the time of reading, making it easier to drop those rows using dropna later.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.