BUG: escapechar=',' Causes Double Commas in Output in Pandas 2.2.2

[SamuelManaay]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

# Example DataFrame
df = pd.DataFrame({'column': ['value1, with comma', 'value2, with another comma']})

# Exporting DataFrame to CSV with escapechar=','
df.to_csv('output.csv', escapechar=',', index=False)

Issue Description

When using escapechar=',' in Pandas version 2.2.2, the resulting output includes double commas ,, in places where the data originally contained a comma. This behavior is inconsistent with Pandas version 1.4.3, where the escapechar did not cause this issue.

Steps to Reproduce:

1. Create a DataFrame with a column containing commas in its values.
2. Export the DataFrame to a CSV file using escapechar=','.
3. Observe the output file and note the double commas ,, where there was originally a single comma.

Expected Behavior

The output CSV should properly escape commas without doubling them. The behavior should be consistent with previous Pandas versions where the escape character was used correctly.

Installed Versions

2.2.2

[rhshadrach]

Thanks for the report, confirmed on main. Further investigations and PRs to fix are welcome.

[wooseogchoi]

take

[wooseogchoi]

The pandas relies on the writer in cpython's csv in order to save the data frame to the given csv file. The escapechar also passes to the writer as escapechar. And the quoting is set to the csv's default value, QUOTE_MINIMAL, if it is not given. Therefore the bug, if it is bug, is not in pandas, but in csv of cpython.
I confirmed that csv in cpython also has the same issue.

'''
import csv
data = [['column'],
['value1, with comma'],
['value2, with another comma']]
with open('output-escape.csv'
, 'w') as csvfile:
writer = csv.writer(csvfile, escapechar=',')
writer.writerows(data)
'''
column
value1,, with comma
value2,, with another comma

So I reported it to cpython community.

python/cpython#123109

After investigating a little bit more, I found that the usage in this case seems to be incorrect as well.
Since the delimeter and the character we want to escape are the same, we can get the desired result by ommiting the escapechar from to_csv function call.

...
import pandas as pd
df = pd.DataFrame({'column':['value1, with comma', 'value2, with another comma']})
df.to_csv('pandas-no-escapedchar.csv', index=False)
'''
(base) PS C:\Users\User> cat pandas-no-escapedchar.csv
column
"value1, with comma"
"value2, with another comma"

[wooseogchoi]

I will keep an eye on the issue in cpython and post updates.

[rhshadrach]

Thanks for investigating! Closing this as an upstream issue.