Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a string column that sometimes has carriage returns in the string:

import pandas as pd
from io import StringIO
datastring = StringIO("""\
country  metric           2011   2012
USA      GDP              7      4
USA      Pop.             2      3
GB       GDP              8      7
df = pd.read_table(datastring, sep='\s\s+')
df.metric = df.metric + '\r'  # append carriage return
print(df)
  country  metric  2011  2012
0     USA   GDP\r     7     4
1     USA  Pop.\r     2     3
2      GB   GDP\r     8     7
When writing to and reading from csv, the dataframe gets corrupted:
df.to_csv('data.csv', index=None)
print(pd.read_csv('data.csv'))
  country metric  2011  2012
0     USA    GDP   NaN   NaN
1     NaN      7     4   NaN
2     USA   Pop.   NaN   NaN
3     NaN      2     3   NaN
4      GB    GDP   NaN   NaN
5     NaN      8     7   NaN
Question
What's the best way to fix this? The one obvious method is to just clean the data first:
df.metric = df.metric.str.replace('\r', '')
                This is actually an argument for to_csv, not for read_csv. Trying to use this line gives an error.
– kilgoretrout
                Mar 9, 2020 at 13:24
                2023: Useful with Databricks somehow ignoring the \r\n while in Jupyter it works. Something to highlight is that Jupyter mentions that line_terminator is deprecated but in Databricks it complains and needs this way with underscore in the name.
– Gerlex
                Jan 25 at 10:26
To anyone else who is dealing with such an issue:
@mike-müller's answer doesn't actually fix the issue, and the file is still corrupted when it is read by other CSV readers (e.g. Excel). You need to fix this once you write the file rather than while reading it.
The problem lies in not quoting strings having newline characters (\r, \n, or \r\n depending on the OS style). This will not keep the CSV reader (e.g. pandas, Excel, etc.) from parsing the newline characters and then it messes up the loaded CSV file into having multiple lines per unquoted records.
The generalized newline char in Python is \r\n as you strip string by these chars e.g. str.strip('\r\n'). This will make Python identify and cover all OS newline styles.
In pandas, reading CSV file by line_terminator='\r\n' wraps all strings having either \n or \r into double quotes to preserve quoting and keep readers from parsing newline chars later.
Just to provide the code:
pd.to_csv('data.csv', line_terminator='\r\n'))
                stackoverflow.com/questions/454725/… suggests that '\n' is actually closer to something like a generalized newline character than "\r\n". However, I think there is nothing like a "generalized newline char in Python". "\r\n" is just the Windows line terminator, not a general one, and it seems to work for you everywhere, probably because you are mostly using certain OSes. And that it works in Excel is just because Excel is a Windows application. I think your answer is misleading.
– Daniel S.
                Dec 25, 2021 at 11:39
                @DanielS. generalized for the strip() function. As the Python documentation on strip() expresses "The chars argument is not a prefix or suffix; rather, all combinations of its values are stripped". So any combination of characters will be trimmed from both sides of the given string.
– Shayan Amani
                Dec 25, 2021 at 21:12
I got three working solutions. All of these look equally robust to me.
This one (credit should go to @Shayan Amani) works good because now read_csv considers only \n as the line separator, and therefore '\r' is just a character. Note that the behavior of to_csv will change by the platform; On windows, lines are separated by '\r\n'. This won't change the result though, thanks to the skip_blank_lines=True option of read_csv.
df.to_csv("tmp/test.csv", index=False)
pd.read_csv("tmp/test.csv", lineterminator="\n")
This one solves the problem by forcing the quote for text columns.
import csv
df.to_csv("tmp/test.csv", index=False, quoting=csv.QUOTE_NONNUMERIC)
pd.read_csv("tmp/test.csv")
Another option is to explicitly specify the line separator when saving.
With this, text with '\r' is now quoted.
df.to_csv("tmp/test.csv", index=False, line_terminator="\r\n")
pd.read_csv("tmp/test.csv")
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.