Scenario

A CSV file contains a column with comma-delimited data that needs to be split into separate columns for easier analysis.

Task

Write a Python script at /home/interview/split_columns.py using pandas that reads /home/interview/data.csv, splits the full_info column into separate columns (first_name, last_name, age), and saves the result to /home/interview/split_data.csv.

Note: pandas is already installed.

Example

Input (data.csv):

id,full_info
1,"John,Doe,30"
2,"Jane,Smith,25"

Expected output (split_data.csv):

id,first_name,last_name,age
1,John,Doe,30
2,Jane,Smith,25

Step 1: Examine the input data

head /home/interview/data.csv

The full_info column contains comma-delimited values that need to be split.

Step 2: Create the Python script

nano /home/interview/split_columns.py

Write a script using pandas to split the column:

import pandas as pd

# Read the CSV file
df = pd.read_csv('/home/interview/data.csv')

# Split the full_info column into separate columns
df[['first_name', 'last_name', 'age']] = df['full_info'].str.split(',', expand=True)

# Drop the original full_info column
df = df.drop('full_info', axis=1)

# Save to new CSV
df.to_csv('/home/interview/split_data.csv', index=False)

print(f"Split {len(df)} records into separate columns")

The str.split(',', expand=True) method splits the delimited string and returns a DataFrame with separate columns that can be assigned directly.

Step 3: Run the script

python3 /home/interview/split_columns.py

Step 4: Verify the output

head /home/interview/split_data.csv

Should show columns id, first_name, last_name, and age with split values.