Pandas读取含重复值列的数据集时误判列布局问题求助

Hey there! Sorry to hear you're hitting this weird parsing issue with your Kickstarter dataset—trust me, we've all been there when dealing with messy CSV files. Let's break down what's probably happening and how to fix it:

First, diagnose the root cause #

The most common culprit here is unproperly escaped delimiters in your CSV. For example, if a cell contains the same character as your column separator (like a comma) but isn't wrapped in quotes, Pandas will split that cell into extra columns. When two column values are the same, it might just be coincidence that this escaping issue surfaces there.

Start by manually checking a few problematic rows to confirm:

with open('your_kickstarter_2016.csv', 'r') as f:
    # Print the first 20 lines to spot anomalies
    for _ in range(20):
        print(f.readline().strip())

Look for rows where values have commas (or your separator) without quotes, or inconsistent quoting between rows.

Fix 1: Force correct parsing rules in pd.read_csv #

Since you know your dataset has exactly 12 columns, explicitly define the parsing parameters to match your file's structure:

import pandas as pd

# Replace these with your actual column names (grab them from a valid row if needed)
column_names = ['id', 'name', 'category', 'main_category', 'currency', 'deadline', 'goal', 'launched', 'pledged', 'state', 'backers', 'country']

df = pd.read_csv(
    'your_kickstarter_2016.csv',
    names=column_names,
    header=0,  # Use the first row as header if it exists
    sep=',',  # Swap to '\t' if your file uses tabs as separators
    quotechar='"',  # Tell Pandas to treat quoted text as single cells
    on_bad_lines='warn'  # Warn about bad rows instead of crashing; use 'skip' to ignore them
)

If some rows still misalign, add escapechar='\\' if your file uses backslashes to escape quotes.

Fix 2: Preprocess with Python's built-in csv module #

If Pandas' parser is still struggling, use the standard csv library to ensure every row gets split into exactly 12 columns before converting to a DataFrame:

import csv
import pandas as pd

rows = []
with open('your_kickstarter_2016.csv', 'r') as f:
    reader = csv.reader(f, delimiter=',', quotechar='"')
    header = next(reader)  # Grab the header row
    for idx, row in enumerate(reader, 1):
        if len(row) != 12:
            print(f"Row {idx} has {len(row)} columns (expected 12): {row}")
            # Fix the row—truncate extra columns or pad with empty strings
            row = row[:12] + [''] * (12 - len(row))
        rows.append(row)

# Convert to DataFrame
df = pd.DataFrame(rows, columns=header)

This gives you full control over handling messy rows, so you can tweak the fix logic based on what you see in the error prints.

Fix 3: Check encoding and line endings #

Sometimes weird parsing happens due to non-standard encoding (like latin-1 instead of utf-8) or mixed line endings. Try adding these parameters to pd.read_csv:

df = pd.read_csv(
    'your_kickstarter_2016.csv',
    encoding='utf-8',  # Switch to 'latin-1' if utf-8 fails
    lineterminator='\n',  # Force Unix-style line endings
    sep=',',
    quotechar='"'
)

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