33

I cleaned 400 excel files and read them into python using pandas and appended all the raw data into one big df.

Then when I try to export it to a csv:

df.to_csv("path",header=True,index=False)

I get this error:

UnicodeEncodeError: 'ascii' codec can't encode character u'\xc7' in position 20: ordinal not in range(128)

Can someone suggest a way to fix this and what it means?

Thanks

collarblind
  • 4,549
  • 13
  • 31
  • 49
  • Please tell if you're using python 2 or 3, those two handle Unicode very differently. Then read [the Unicode howto](https://docs.python.org/3/howto/unicode.html), it gives a good background for this error. These problems are not difficult to solve, but you have to know the basics first. – roeland Jul 10 '15 at 02:13
  • https://docs.python.org/2.7/howto/unicode.html – marbel Apr 12 '16 at 18:38

2 Answers2

69

You have unicode values in your DataFrame. Files store bytes, which means all unicode have to be encoded into bytes before they can be stored in a file. You have to specify an encoding, such as utf-8. For example,

df.to_csv('path', header=True, index=False, encoding='utf-8')

If you don't specify an encoding, then the encoding used by df.to_csv defaults to ascii in Python2, or utf-8 in Python3.

unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • does it still matter in Python3 to explicitly add the argument 'encoding='utf-8'' as it is already the default option? – Jia Gao Jun 20 '18 at 23:36
  • @JasonGoal: It's not necessary if your code is only meant to be run with Python3, but explicitness here would allow (at least this line of) your code to run under both Python2 and Python3. – unutbu Jun 21 '18 at 19:54
19

Adding an answer to help myself google it later:

One trick that helped me is to encode a problematic series first, then decode it back to utf-8. Like:

df['crumbs'] = df['crumbs'].map(lambda x: x.encode('unicode-escape').decode('utf-8'))

This would get the dataframe to print correctly too.

tangfucius
  • 444
  • 7
  • 14
  • 6
    I like the idea of putting the helpful tips into an answer for later googling. Stack Overflow is at least a twice daily visit during my workday. – RufusVS Jul 28 '18 at 19:15