从API获取数据至Dataframe后，嵌套列展开及数组长度不一致错误解决咨询

1. Handling Dictionary Columns (Preferences Case) #

For columns containing single dictionaries (like preferences), we can directly use pd.json_normalize to expand the dict into separate columns, then merge back with the original DataFrame.

Code Example: #

import pandas as pd

# Sample data
data = [
    {"name": "billy", "preferences": {"email": False, "print": False, "charge": False}, "zip_code": "12345"},
    {"name": "sam", "preferences": {"email": False, "print": False, "charge": False}, "zip_code": "55555"},
    {"name": "edward", "preferences": {"email": False, "print": False, "charge": False}, "zip_code": "68954"}
]
df = pd.DataFrame(data)

# Normalize the preferences column into separate columns
normalized_prefs = pd.json_normalize(df['preferences'])

# Combine with original DataFrame (drop the original preferences column first)
final_df = pd.concat([df.drop('preferences', axis=1), normalized_prefs], axis=1)

print(final_df)

Output: #

2. Handling List-of-Dictionaries Columns (Phone Numbers Case) #

The All arrays must be of the same length error occurs because some rows have multiple entries in the phone_numbers list, while others have only one. We need to handle this by either exploding the list into multiple rows or concatenating multiple values into a single string.

Option A: Explode into Multiple Rows (Keep All Phone Numbers) #

This approach creates a separate row for each phone number, which is ideal if you need to analyze each number individually.

Code Example:

import pandas as pd

# Sample data
data = [
    {"name": "billy", "phone_numbers": [{"phone_number": "1234567890"}], "zip_code": "12345"},
    {"name": "sam", "phone_numbers": [{"phone_number": "1234567890"}, {"phone_number": "2222222222"}], "zip_code": "55555"},
    {"name": "edward", "phone_numbers": [{"phone_number": "4444444444"}], "zip_code": "68954"}
]
df = pd.DataFrame(data)

# Explode the list into separate rows
df_exploded = df.explode('phone_numbers', ignore_index=True)

# Normalize the exploded dictionary entries
normalized_phones = pd.json_normalize(df_exploded['phone_numbers'])

# Combine with original DataFrame
final_df = pd.concat([df_exploded.drop('phone_numbers', axis=1), normalized_phones], axis=1)

print(final_df)

Output:

Option B: Concatenate into a Single String (Keep One Row Per Person) #

If you prefer to keep one row per individual, you can join multiple phone numbers into a single comma-separated string.

Code Example:

import pandas as pd

# Sample data
data = [
    {"name": "billy", "phone_numbers": [{"phone_number": "1234567890"}], "zip_code": "12345"},
    {"name": "sam", "phone_numbers": [{"phone_number": "1234567890"}, {"phone_number": "2222222222"}], "zip_code": "55555"},
    {"name": "edward", "phone_numbers": [{"phone_number": "4444444444"}], "zip_code": "68954"}
]
df = pd.DataFrame(data)

# Define a helper function to extract and join phone numbers
def extract_phones(phone_list):
    return ', '.join([entry['phone_number'] for entry in phone_list])

# Apply the function to create a new column
df['phone_number'] = df['phone_numbers'].apply(extract_phones)

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

print(final_df)

Output:

Why the Error Happened #

When you try to use pd.json_normalize directly on a column of lists with varying lengths, pandas can't align the elements into consistent columns—each row has a different number of entries, hence the All arrays must be of the same length error. Exploding the list first ensures each row contains a single dictionary, making normalization possible.

内容的提问来源于stack exchange，提问作者Chicken Sandwich No Pickles