pandas 添加了"\ r"到csv文件 [英] Pandas adds "\r" to csv file

问题描述

这可以归结为一个更简单的问题，此处

我有一个熊猫数据框，如下所示:

In [1]: df
Out[1]:
   0        1
0  a  A\nB\nC
1  a  D\nE\nF
2  b  A\nB\nC

当我将其写入csv文件然后又将其读回时，我希望具有相同的数据框.情况并非如此:

In [2]: df.to_csv("out.csv")

In [3]: df = pd.read_csv("out.csv", index_col=0)

In [4]: df
Out[4]:
   0            1
0  a  A\r\nB\r\nC
1  a  D\r\nE\r\nF
2  b  A\r\nB\r\nC

在每个\n之前添加一个\r字符.再次编写和阅读它，会发生相同的事情:

In [5]: df.to_csv("out.csv")

In [6]: df = pd.read_csv("out.csv", index_col=0)

In [7]: df
Out[7]:
   0                1
0  a  A\r\r\nB\r\r\nC
1  a  D\r\r\nE\r\r\nF
2  b  A\r\r\nB\r\r\nC

如何阻止熊猫添加\r字符?

---

是的，我在窗户上.

---

pd.read_csv(pd.compat.StringIO(df.to_csv(index=False)))给了我相同的数据帧，所以问题似乎在写文件

---

像这样以二进制模式传递打开的文件对象:

with open("out.csv", "wb") as file:
    df.to_csv(file)

导致:

TypeError                                 Traceback (most recent call last)
<ipython-input-20-f31d52fb2ce3> in <module>()
      1 with open("out.csv", "wb") as file:
----> 2     df.to_csv(file)
      3

C:\Program Files\Anaconda3\lib\site-packages\pandas\core\frame.py in to_csv(self, path_or_buf, sep, na_rep, float_format, columns, header, index, index_label, mode, encoding, compression, quoting, quotechar, line_terminator, chunksize, tupleize_cols, date_format, doublequote, escapechar, decimal, **kwds)
   1342                                      doublequote=doublequote,
   1343                                      escapechar=escapechar, decimal=decimal)
-> 1344         formatter.save()
   1345
   1346         if path_or_buf is None:

C:\Program Files\Anaconda3\lib\site-packages\pandas\formats\format.py in save(self)
   1549
   1550             else:
-> 1551                 self._save()
   1552
   1553         finally:

C:\Program Files\Anaconda3\lib\site-packages\pandas\formats\format.py in _save(self)
   1636     def _save(self):
   1637
-> 1638         self._save_header()
   1639
   1640         nrows = len(self.data_index)

C:\Program Files\Anaconda3\lib\site-packages\pandas\formats\format.py in _save_header(self)
   1632
   1633         # write out the index label line
-> 1634         writer.writerow(encoded_labels)
   1635
   1636     def _save(self):

TypeError: a bytes-like object is required, not 'str'

---

使用常规写入无济于事

In [1]: with open("out.csv", "w") as file:
   ...:     df.to_csv(file)
   ...:

In [2]: df = pd.read_csv("out.csv")

In [3]: df
Out[3]:
   Unnamed: 0  0            1
0           0  a  A\r\nB\r\nC
1           1  a  D\r\nE\r\nF
2           2  b  A\r\nB\r\nC

---

我的python版本是Python 3.5.2 :: Anaconda 4.2.0 (64-bit)

---

我确定问题出在pandas.read_csv而不是pandas.to_csv

In [1]: df
Out[1]:
   0        1
0  a  A\nB\nC
1  a  D\nE\nF
2  b  A\nB\nC

In [2]: df.to_csv("out.csv")

In [3]: with open("out.csv", "r") as file:
    ...:     s = file.read()
    ...:

In [4]: s  # Only to_csv has been used, no \r's!
Out[4]: ',0,1\n0,a,"A\nB\nC"\n1,a,"D\nE\nF"\n2,b,"A\nB\nC"\n'

In [5]: pd.read_csv("out.csv")  # Now the \r's come in
Out[5]:
   Unnamed: 0  0            1
0           0  a  A\r\nB\r\nC
1           1  a  D\r\nE\r\nF
2           2  b  A\r\nB\r\nC

解决方案

就像上面的评论和帖子中已经提到的那样，您已经引用了#17365 上也报告了此问题. /p>

希望在Python3上，您可以指定换行符:

with open("out.csv", mode='w', newline='\n') as f:
    df.to_csv(f, sep=",", line_terminator='\n', encoding='utf-8')

This boils down to a simpler problem here

I have a pandas dataframe that looks like this:

In [1]: df
Out[1]:
   0        1
0  a  A\nB\nC
1  a  D\nE\nF
2  b  A\nB\nC

When I write it to a csv file then read it back, I expect to have the same dataframe. This is not the case:

In [2]: df.to_csv("out.csv")

In [3]: df = pd.read_csv("out.csv", index_col=0)

In [4]: df
Out[4]:
   0            1
0  a  A\r\nB\r\nC
1  a  D\r\nE\r\nF
2  b  A\r\nB\r\nC

A \r character is added before each \n. Writing and reading it again, the same thing happens:

In [5]: df.to_csv("out.csv")

In [6]: df = pd.read_csv("out.csv", index_col=0)

In [7]: df
Out[7]:
   0                1
0  a  A\r\r\nB\r\r\nC
1  a  D\r\r\nE\r\r\nF
2  b  A\r\r\nB\r\r\nC

How can I stop pandas from adding a \r character?

---

Edits:
Yes I am on windows.

---

pd.read_csv(pd.compat.StringIO(df.to_csv(index=False))) gives me the same dataframe, so the problem seems to be writing to a file

---

Passing an open file object in binary mode like this:

with open("out.csv", "wb") as file:
    df.to_csv(file)

results in:

TypeError                                 Traceback (most recent call last)
<ipython-input-20-f31d52fb2ce3> in <module>()
      1 with open("out.csv", "wb") as file:
----> 2     df.to_csv(file)
      3

C:\Program Files\Anaconda3\lib\site-packages\pandas\core\frame.py in to_csv(self, path_or_buf, sep, na_rep, float_format, columns, header, index, index_label, mode, encoding, compression, quoting, quotechar, line_terminator, chunksize, tupleize_cols, date_format, doublequote, escapechar, decimal, **kwds)
   1342                                      doublequote=doublequote,
   1343                                      escapechar=escapechar, decimal=decimal)
-> 1344         formatter.save()
   1345
   1346         if path_or_buf is None:

C:\Program Files\Anaconda3\lib\site-packages\pandas\formats\format.py in save(self)
   1549
   1550             else:
-> 1551                 self._save()
   1552
   1553         finally:

C:\Program Files\Anaconda3\lib\site-packages\pandas\formats\format.py in _save(self)
   1636     def _save(self):
   1637
-> 1638         self._save_header()
   1639
   1640         nrows = len(self.data_index)

C:\Program Files\Anaconda3\lib\site-packages\pandas\formats\format.py in _save_header(self)
   1632
   1633         # write out the index label line
-> 1634         writer.writerow(encoded_labels)
   1635
   1636     def _save(self):

TypeError: a bytes-like object is required, not 'str'

---

Using regular write does not help

In [1]: with open("out.csv", "w") as file:
   ...:     df.to_csv(file)
   ...:

In [2]: df = pd.read_csv("out.csv")

In [3]: df
Out[3]:
   Unnamed: 0  0            1
0           0  a  A\r\nB\r\nC
1           1  a  D\r\nE\r\nF
2           2  b  A\r\nB\r\nC

---

My python version is Python 3.5.2 :: Anaconda 4.2.0 (64-bit)

---

I have determined that the problem is with pandas.read_csv and not pandas.to_csv

In [1]: df
Out[1]:
   0        1
0  a  A\nB\nC
1  a  D\nE\nF
2  b  A\nB\nC

In [2]: df.to_csv("out.csv")

In [3]: with open("out.csv", "r") as file:
    ...:     s = file.read()
    ...:

In [4]: s  # Only to_csv has been used, no \r's!
Out[4]: ',0,1\n0,a,"A\nB\nC"\n1,a,"D\nE\nF"\n2,b,"A\nB\nC"\n'

In [5]: pd.read_csv("out.csv")  # Now the \r's come in
Out[5]:
   Unnamed: 0  0            1
0           0  a  A\r\nB\r\nC
1           1  a  D\r\nE\r\nF
2           2  b  A\r\nB\r\nC

解决方案

As some have already said in comments above and on the post you have put in reference here, this is a typical windows issue when serializing newlines. The issue has been reported on pandas-dev github #17365 as well.

Hopefully on Python3, you can specify the newline:

with open("out.csv", mode='w', newline='\n') as f:
    df.to_csv(f, sep=",", line_terminator='\n', encoding='utf-8')

这篇关于 pandas 添加了"\ r"到csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！