在Jupyter Notebook中导入含嵌套JSON的CSV至Pandas DataFrame遇解析错误

Hey Manuel, I’ve run into this exact issue before—Pandas gets confused when CSV fields contain nested JSON because it treats every comma as a field separator, even the ones inside the JSON. Let’s walk through the solutions to get your data loaded correctly:

1. First, Verify Your CSV Format #

The root issue here is that your nested JSON fields need to be wrapped in quotes (double quotes are standard for CSV) so Pandas recognizes the entire JSON string as a single field. For example, a valid row should look something like this:

id,name,user_data
1,Alice,"{""age"":30,""hobbies"":[""reading"",""hiking""]}"
2,Bob,"{""age"":25,""hobbies"":[""gaming"",""cooking""]}"

If your CSV isn’t formatted this way, fixing the quotes is the simplest fix. If you can’t edit the CSV directly, use Pandas’ parsing parameters to handle it.

2. Use Pandas read_csv Parameters to Handle Quoted JSON #

Add these parameters to your pd.read_csv() call to tell Pandas how to interpret the quoted JSON fields:

import pandas as pd

# Basic fix for double-quoted JSON fields
df = pd.read_csv('your_file.csv', quotechar='"', escapechar='\\')

• quotechar='"': Tells Pandas that fields wrapped in double quotes are single values (even if they contain commas).
• escapechar='\\': If your JSON has escaped quotes (like "" inside the JSON), this lets Pandas ignore them as field delimiters.

If your JSON uses single quotes instead, swap quotechar to ':

df = pd.read_csv('your_file.csv', quotechar="'", escapechar='\\')

3. Manually Parse Rows If CSV Format Is Messy #

If the CSV is poorly formatted (no quotes around JSON), you can read the file line-by-line, split fields carefully, and parse the JSON manually:

import pandas as pd
import json

data_rows = []
with open('your_file.csv', 'r') as csv_file:
    # Read and split the header
    header = csv_file.readline().strip().split(',')
    expected_fields = 29  # From your error message
    
    for line in csv_file:
        # Split only up to the first 28 commas (so we keep the JSON as one chunk)
        split_line = line.strip().split(',', maxsplit=expected_fields - 1)
        # Combine the remaining parts back into a single JSON string
        json_field = ','.join(split_line[expected_fields - 1:])
        # Parse the JSON
        parsed_json = json.loads(json_field)
        # Create a dictionary for the row, merging the non-JSON fields and parsed JSON
        row_dict = dict(zip(header[:expected_fields - 1], split_line[:expected_fields - 1]))
        row_dict.update(parsed_json)
        data_rows.append(row_dict)

# Convert to DataFrame
df = pd.DataFrame(data_rows)

This approach ensures the JSON part isn’t split by commas, and you can expand the nested JSON into separate columns later if needed.

4. Parse JSON After Loading with converters #

If you know which column contains the nested JSON, use the converters parameter to parse it during loading:

import pandas as pd
import json

def parse_json_field(json_str):
    try:
        return json.loads(json_str)
    except ValueError:
        # Return the original string if parsing fails (for error handling)
        return json_str

# Replace 'json_column' with your actual column name
df = pd.read_csv('your_file.csv', converters={'json_column': parse_json_field})

# To expand the JSON into separate columns:
df = pd.concat([df.drop('json_column', axis=1), df['json_column'].apply(pd.Series)], axis=1)

Why This Error Happens #

Your error Expected 29 fields in line 3, saw 35 means Pandas split line 3 into 35 pieces using commas—this is because the nested JSON has commas that Pandas didn’t recognize as part of a single field. The fixes above tell Pandas to treat the JSON as a single value, so the field count matches across all rows.

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