Panda's not reading data from excel file

Agp 0
Viewed 27

I posted a similar question last week but have come to the conclusion that it's not a problem with the code as such but a loss of data and I was wondering if anyone knows a fix or has experienced similar problems. I have tried saving the document as csv, xlsx and xls to see if this would resolve it but no change.

So my excel file is just a straight forward fairly small table with "Yes" and "No" values.

I know the code works because when I input this:

   df.loc[(df["Unnamed: 18"] == "Yes")]

I get the desired output of:

return of code

(If you cannot see the photo its returned the rows that were True)

However when I enter

df.loc[(df["Unnamed: 17"] == "Yes")]

return of code

(it returns no data if you cannot access the photo)

I get the same if I switch it to == "No".

It just does not want to read data from that column. I get sent several of these spreadsheets a day and its always the same columns that it won't read. Any advice or help would be greatly appreciated.

pandas excel python
1 Answers

It looks like there is some white spaces in the Yes/No values.

Select all the object (/string) columns and use pandas.Series.str.strip :

df_obj = df.select_dtypes(['object'])

df[df_obj.columns] = df_obj.apply(lambda x: x.str.strip())

df.loc[(df["Unnamed: 17"] == "Yes")]
abokey0
Related