QST: CSV: export, import and new lines

[kuraga]

• I have searched the [pandas] tag on StackOverflow for similar questions.

• I have asked my usage related question on StackOverflow.

---

Question about pandas

df = pd.DataFrame([ ['a\rb'] ], columns=['A'])
df.to_csv('df.csv', index=False, sep=';', encoding='utf-8')
df_imported = pd.read_csv('df.csv', sep=';', encoding='utf-8')
(len(df), len(df_imported)) # => (1, 2)

On Linux, symbol \r is not escaped by pd.to_csv. But seems like pd.read_csv expects something else.

Is it bug or feature? How to avoid this?

[MarcoGorelli]

How about

df = pd.DataFrame([ [r'a\rb'] ], columns=['A'])
df.to_csv('df.csv', index=False, sep=';', encoding='utf-8')
df_imported = pd.read_csv('df.csv', sep=';', encoding='utf-8')
(len(df), len(df_imported)) # => (1, 1)

?

[kuraga]

@MarcoGorelli , that's a synthetic example. \r comes from wild data. Excel, AFAIR.

Fact is:

symbol \r is not escaped by pd.to_csv

[MarcoGorelli]

Ah, I see. How about

In [5]: df = pd.DataFrame([ ['a\rb'] ], columns=['A']) 
   ...: df.to_csv('df.csv', index=False, sep=';', encoding='utf-8', escapechar='\r') 
   ...: df_imported = pd.read_csv('df.csv', sep=';', encoding='utf-8') 
   ...: (len(df), len(df_imported))                                                     
Out[5]: (1, 1)

then?

[kuraga]

@MarcoGorelli , ok, it works.

But why escapechar?!

escapechar - str, default None
    String of length 1. Character used to escape sep and quotechar when appropriate.

[MarcoGorelli]

Sorry, ignore me, my answer doesn't make sense. I think @gfyoung would know about this

[asishm]

add a quoting=1 in the to_csv. Had run into this before as well during our production jobs where ingests are excel files generated on Windows but the processing was running in a debian based docker.

In [1]: import pandas as pd

In [2]: df = pd.DataFrame([ ['a\rb'] ], columns=['A'])

In [3]: df.to_csv('df.csv', index=False, sep=';', encoding='utf-8', quoting=1)

In [4]: df_imported = pd.read_csv('df.csv', sep=';', encoding='utf-8')

In [5]: (len(df), len(df_imported))
Out[5]: (1, 1)

In [6]: df
Out[6]:
      A
0  a\rb

In [7]: df_imported
Out[7]:
      A
0  a\rb

[MarcoGorelli]

Thanks @asishm ! For completeness, 1 corresponds to csv.QUOTE_ALL.

@kuraga is this OK to close?

[kuraga]

@MarcoGorelli , @asishm, well... First of all, thanks!

quoting defaults to csv.QUOTE_MINIMAL. So I thought that behavior is by csv module:

csv.QUOTE_MINIMAL
Instructs writer objects to only quote those fields which contain special characters such as delimiter, quotechar or any of the characters in lineterminator.

Dialect.lineterminator
The string used to terminate lines produced by the writer. It defaults to '\r\n'.

And csv.writer escapes \r with quoting=csv.QUOTE_MINIMAL. Why Pandas doesn't?

[asishm]

You are correct. What I mentioned was a workaround (should've been explicit there). I'd like the default behavior to work as csv.writer does with QUOTE_MINIMAL. I also forgot that I had raised this issue at that time!

xref #10018, #22678

Probably can close as duplicates of those. thoughts @kuraga ?

[kuraga]

Now exhaustively, thanks!