如何用Python的pd.read_csv正确读取含特殊格式列的表格？

Got it, this is a common issue when dealing with CSV fields that contain spaces or special characters that clash with pandas' default parsing behavior. Let's break down how to fix this:

1. Ensure You're Using the Correct Delimiter & Quoting Rules #

The most likely culprit is either an incorrect delimiter setting, or your target field isn't wrapped in quotes to signal it's a single value.

Example Scenario: Comma-Separated CSV with Unquoted Fields #

If your CSV rows look like this (no quotes around the field with spaces):

ID,[ 1s 1/2-1/2]+,Value
1,[ 2s 3/2-1/2]+,100

Pandas won't split the second column by default (since sep=',' is default), but if you accidentally set sep='\s+' or delim_whitespace=True, it will split on spaces. Double-check your delimiter first.

If your field is wrapped in quotes (the proper CSV standard), pandas will automatically recognize it as a single column:

ID,"[ 1s 1/2-1/2]+",Value
1,"[ 2s 3/2-1/2]+",100

To enforce this behavior explicitly, use the quoting parameter with the csv module:

import pandas as pd
import csv

df = pd.read_csv(
    "your_file.csv",
    sep=',',
    quoting=csv.QUOTE_ALL,  # Treat all quoted fields as single values
    engine='python'  # Required for full quoting support in some cases
)

2. Force Pandas to Treat Special Fields as Single Values #

If your CSV doesn't use quotes but you need to preserve fields with spaces, you can use quotechar to specify a unique character that starts/ends your target field (like the [ in your example):

import pandas as pd
import csv

df = pd.read_csv(
    "your_file.csv",
    sep=',',
    quotechar='[',  # Use '[' as the start quote for your special field
    escapechar=']',  # Use ']' as the end quote/escape
    quoting=csv.QUOTE_NONE
)

This tells pandas to treat everything between [ and ] as a single value, even if there are spaces inside.

3. Manually Merge Split Columns If All Else Fails #

If you can't modify the CSV file and the above methods don't work, read all columns first then merge the split ones:

import pandas as pd

# Read the CSV as-is (this will have extra columns from the split)
df = pd.read_csv("your_file.csv")

# Merge the split columns (assuming columns 1,2,3 are the split parts of your original second column)
df['original_second_col'] = df.iloc[:,1] + ' ' + df.iloc[:,2] + ' ' + df.iloc[:,3]

# Drop the original split columns
df = df.drop(df.columns[[1,2,3]], axis=1)

Alternatively, read the entire file as a single column first, then split it correctly with a limit:

# Read everything into one column using a rare delimiter that doesn't exist in your data
df = pd.read_csv("your_file.csv", sep='\x01', header=None)

# Split on your actual delimiter (e.g., comma) but only split twice to keep the target field intact
df = df[0].str.split(',', expand=True, n=2)

# Rename columns to match your original structure
df.columns = ['first_col', 'target_col', 'remaining_cols']

内容的提问来源于stack exchange，提问作者Jimmy Yao