如何读取含列内逗号分隔元素的CSV文件到Pandas DataFrame

Got it, let's break down why your pd.read_csv('test2.csv') isn't giving the expected result. The core issue is that your name field includes a comma plus space (like "David, Johnson"), but pandas uses commas as the default delimiter. This splits the full name into two separate columns, which throws off all the subsequent columns—city and country end up in the wrong positions as a result.

Here are two reliable ways to fix this:

Method 1: Clean the data after reading (no need to modify the original CSV) #

If you can't edit the test2.csv file directly, read all columns first, then combine the name parts and restructure the DataFrame:

import pandas as pd

# Read the file without using the header row first, skip the original header line
raw_df = pd.read_csv('test2.csv', header=None, skiprows=1)

# Assign temporary column names (note the extra empty column at the end of each row)
raw_df.columns = ['id', 'first_name', 'last_name', 'city', 'country', 'unused_col']

# Combine first and last name into a single 'name' column, stripping extra spaces
raw_df['name'] = raw_df['first_name'].str.strip() + ' ' + raw_df['last_name'].str.strip()

# Build the final DataFrame with correct columns, convert id to integer type
final_df = raw_df[['id', 'name', 'city', 'country']].copy()
final_df['id'] = final_df['id'].astype(int)

# Verify the result
print(final_df)

This will produce a DataFrame where:

• id is stored as an integer
• name contains the full combined name (e.g., "David Johnson")
• city and country hold their correct, aligned values

Method 2: Modify the original CSV for cleaner reading #

If you can edit test2.csv, wrap the name values in double quotes. This tells pandas that the comma inside is part of the field, not a delimiter. Your updated CSV should look like this:

id,name,city,country
1,"David, Johnson",London,UK
2,"Mary, Gueta",NY,USA
3,"Matthias, Nile",Munich,Germany

Now your original command will work perfectly:

import pandas as pd
df = pd.read_csv('test2.csv')

Pandas automatically recognizes quoted fields and ignores commas inside them when using the default delimiter.

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