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.
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.
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.
Pandas DataFrame.to_csv() struggling with encoding
Suppress UnicodeEncodeError when executing to_csv method
python - Pandas to_csv correct way to handle UnicodeEncodeError - Stack Overflow
python - Pandas df.to_csv("file.csv" encode="utf-8") still gives trash characters for minus sign - Stack Overflow
Why does utf-8 encoding fail for special characters in Pandas?
Can I use utf-8 encoding with Python 3 to resolve the issue?
How can I verify the encoding of my CSV file?
Heya,
I got a DataFrame filled with strings in the first column and the rest consisting of integers (except for the headers).
Now when I export this dataframe to a csv file, and the strings contain German Umlauts (ä,ö,ü or something like ß), the exported csv file has weird looking strings at these indices.
Like "für" became "für".
As far as I know the default encoding for to_csv() is utf-8, which means it should work fine? But I also tried the parameter encoding='utf-8', same results.
What am I doing wrong here?
Your "bad" output is UTF-8 displayed as CP1252.
On Windows, many editors assume the default ANSI encoding (CP1252 on US Windows) instead of UTF-8 if there is no byte order mark (BOM) character at the start of the file. While a BOM is meaningless to the UTF-8 encoding, its UTF-8-encoded presence serves as a signature for some programs. For example, Microsoft Office's Excel requires it even on non-Windows OSes. Try:
df.to_csv('file.csv',encoding='utf-8-sig')
That encoder will add the BOM.
encoding='utf-8-sig does not work for me. Excel reads the special characters fine now, but the Tab separators are gone! However, encoding='utf-16 does work correctly: special characters OK and Tab separators work. This is the solution for me.
read_csv takes an encoding option to deal with files in different formats. I mostly use read_csv('file', encoding = "ISO-8859-1"), or alternatively encoding = "utf-8" for reading, and generally utf-8 for to_csv.
You can also use one of several alias options like 'latin' or 'cp1252' (Windows) instead of 'ISO-8859-1' (see python docs, also for numerous other encodings you may encounter).
See relevant Pandas documentation, python docs examples on csv files, and plenty of related questions here on SO. A good background resource is What every developer should know about unicode and character sets.
To detect the encoding (assuming the file contains non-ascii characters), you can use enca (see man page) or file -i (linux) or file -I (osx) (see man page).
Simplest of all Solutions:
import pandas as pd
df = pd.read_csv('file_name.csv', engine='python')
Alternate Solution:
Sublime Text:
- Open the csv file in Sublime text editor or VS Code.
- Save the file in utf-8 format.
- In sublime, Click File -> Save with encoding -> UTF-8
VS Code:
In the bottom bar of VSCode, you'll see the label UTF-8. Click it. A popup opens. Click Save with encoding. You can now pick a new encoding for that file.
Then, you could read your file as usual:
import pandas as pd
data = pd.read_csv('file_name.csv', encoding='utf-8')
and the other different encoding types are:
encoding = "cp1252"
encoding = "ISO-8859-1"