python pandas read_excel returns UnicodeDecodeError on describe()

Question

I love pandas, but I am having real problems with Unicode errors. read_excel() returns the dreaded Unicode error:

import pandas as pd
df=pd.read_excel('tmp.xlsx',encoding='utf-8')
df.describe()

---------------------------------------------------------------------------
UnicodeDecodeError                        Traceback (most recent call last)
...
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc2 in position 259: ordinal not in range(128)

I figured out that the original Excel had (non-breaking space) at the end of many cells, probably to avoid conversion of long digit strings to float.

One way around this is to strip the cells, but there must be something better.

for col in df.columns:
    df[col]=df[col].str.strip()

I am using anaconda2.2.0 win64, with pandas 0.16

this worked for me once: df['somecol'].values.astype('unicode') github.com/pydata/pandas/issues/7758 — Gecko
– Gecko, Commented Jun 10, 2015 at 20:21
Do yourself a big favour and switch to python3 right away. Encoding problems are all solved in python3. — firelynx
– firelynx, Commented Jun 11, 2015 at 12:39

skytaker · Accepted Answer · 2019-05-28 20:38:59Z

3

Try this method suggested here:

df=pd.read_excel('tmp.xlsx',encoding=sys.getfilesystemencoding())

edited May 28, 2019 at 20:38

answered Apr 26, 2016 at 16:34

skytaker

4,5991 gold badge25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail_Sam · Accepted Answer · 2018-08-02 07:26:00Z

1

Hope this helps someone.

I had this error:

UnicodeDecodeError: 'ascii' codec can't decode byte ....

after reading an Excel File df = pd.read_excel... and trying to assign a new column to the dataframe like this df['new_col'] = 'foo bar'

After closer inspection, I found the problem to be. There were some 'nan' columns in the dataframe due to missing column headers. After dropping the 'nan' columns using the following code, everything else was ok.

df = df.dropna(axis=1,how='all')

edited Aug 2, 2018 at 7:26

Mikhail_Sam

11.3k11 gold badges80 silver badges112 bronze badges

answered Apr 8, 2017 at 18:32

ihightower

3,3036 gold badges39 silver badges51 bronze badges

Comments

dbc · Accepted Answer · 2024-05-04 18:52:29Z

0

df=pd.read_excel(r'paste path')
df.describe()

edited May 4, 2024 at 18:52

dbc

120k27 gold badges273 silver badges404 bronze badges

answered May 4, 2024 at 17:13

prathamesh kale

1

2 Comments

dbc Over a year ago

Might you please edit your answer and explain how your post answers the question, which is about reading an Excel file whose cells contain non-breaking spaces that are encoded in an unexpected manner? Thanks!

Richard Hesketh Dec 2, 2024 at 14:55

Not my answer, but from my limited experience, it's opening the excel file read-only, which may work if your problem is having the file open in Excel when you try this. That's the error I made, and this solution worked for me, but it's apparent from this thread that more than one cause can give rise to similar symptoms.

Collectives™ on Stack Overflow

python pandas read_excel returns UnicodeDecodeError on describe()

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related