Scenario

A Parquet file contains customer data with nested struct columns that need to be flattened for easier analysis and export to CSV format.

Task

Write a Python script at /home/interview/flatten_data.py using pandas that reads /home/interview/customers_nested.parquet, flattens all nested struct columns into separate columns with underscore naming (e.g., address_street, contact_phone), and writes the result to /home/interview/flattened_data.csv.

Note: pandas and pyarrow are already installed.

Example

Input (nested structure):

id | first_name | address                                    | contact
1  | John       | {street: "123 Main", city: "NYC", ...}    | {phone: "555-0100", ...}

Expected output (flattened):

id | first_name | address_street | address_city | contact_phone | ...
1  | John       | 123 Main       | NYC          | 555-0100      | ...

Step 1: Examine the nested structure

python3 -c "import pandas as pd; df = pd.read_parquet('/home/interview/customers_nested.parquet'); print(df.columns.tolist()); print(df.dtypes); print(df.head(1))"

Shows the columns including nested struct columns.

Step 2: Create the Python script

nano /home/interview/flatten_data.py

Write a script to flatten nested columns:

import pandas as pd

# Read the Parquet file
df = pd.read_parquet('/home/interview/customers_nested.parquet')

# Flatten nested 'address' column
address_df = pd.json_normalize(df['address'])
address_df.columns = ['address_' + col for col in address_df.columns]

# Flatten nested 'contact' column
contact_df = pd.json_normalize(df['contact'])
contact_df.columns = ['contact_' + col for col in contact_df.columns]

# Drop original nested columns and concatenate flattened columns
df_flat = df.drop(['address', 'contact'], axis=1)
df_flat = pd.concat([df_flat, address_df, contact_df], axis=1)

# Write to CSV
df_flat.to_csv('/home/interview/flattened_data.csv', index=False)

print(f"Flattened {len(df_flat)} records with {len(df_flat.columns)} columns")

The pd.json_normalize() function flattens nested dictionaries into separate columns.

Step 3: Run the script

python3 /home/interview/flatten_data.py

Step 4: Verify the output

head -n 2 /home/interview/flattened_data.csv

Should show flattened columns like address_street, address_city, contact_phone, etc.