[Python] How to Write a Pandas DataFrame to CSV With Strings Quoted and Integers/Empty Cells...

I am working on a Python script to write a DataFrame to a CSV file. My goal is to:

• Enclose all string values in double quotes (").
• Keep numeric values unchanged (no quotes).
• Leave empty cells as they are.

Here’s the code I’m currently using:

import pandas as pd
import csv

# Sample DataFrame
data = {'col1': ['hello, world', 'test', '', None], 'col2': [123, 456, 789, None]}
report_df = pd.DataFrame(data)

# Add quotes only to non-numeric, non-empty cells
for col in report_df.select_dtypes(include=['object', 'bool']).columns:
report_df[col] = report_df[col].apply(
lambda x: f'"{x}"' if pd.notnull(x) and str(x).strip() != "" else ""
)

# Write to CSV without quoting numeric columns
report_df.to_csv("output.csv", index=False, quoting=csv.QUOTE_NONE, escapechar=" ")



The problem I face is that when a string contains a comma (e.g., "hello, world"), the CSV writer adds an escape character () before the comma, resulting in an output like this:

"hello\, world",123
"test",456
,789
,


I want to avoid this behavior and have the output directly include the comma within the quoted string, like so:

"hello, world",123
"test",456
,789
,


I have tried csv.QUOTE_MINIMAL: When I set quoting=csv.QUOTE_MINIMAL, quotes are only added around strings containing commas, and other strings are left unquoted, which does not meet my requirement.

Custom Logic Issue: When combining csv.QUOTE_MINIMAL with my custom logic to quote all strings, the resulting output has extra double quotes, like:

""""hello, world""""
""""test""""
,


I need a solution that:

• Quotes all string values consistently, including those with commas, without adding escape characters or extra quotes.
• Keeps numeric values and empty cells unaltered.

Continue reading...