如何读取含多JSON值列的DataFrame并提取指定条件行

Hey there! Let's break down how to solve your two pandas challenges step by step:

1. Reading a DataFrame Where One Column Contains Multiple JSON Values #

First, let's cover two common scenarios for your Info column—how you read the data depends on how the JSON is stored in your source file:

Scenario A: Info is a nested JSON object in the source file #

If your original JSON file has the Info field as a nested object (not a string), you can read it directly with pd.read_json—pandas will automatically parse the nested JSON into dictionary-like objects in the Info column:

import pandas as pd

# Load the entire JSON file into a DataFrame
df = pd.read_json("your_data_file.json")

# Verify the Info column contains dictionaries
print(df['Info'].head())

Scenario B: Info is stored as a JSON string #

If the Info column in your source file is saved as a raw string (e.g., "{'name': 'john', 'lname': 'buck', ...}"), you'll need to parse these strings into actual dictionaries after loading the DataFrame:

import pandas as pd
import json

# Load the base DataFrame first
df = pd.read_json("your_data_file.json")

# Convert each JSON string in Info to a dictionary
df['Info'] = df['Info'].apply(json.loads)

2. Extracting Rows Where lname in Info Equals 'buck' #

You have a couple of straightforward options here, depending on your workflow needs:

Option 1: Filter directly on the nested Info column #

This is perfect if you only need to filter rows and don't plan to use other fields inside Info later. Use apply() with a lambda function to check the lname value:

# Filter rows where lname in Info is 'buck'
filtered_rows = df[df['Info'].apply(lambda x: x.get('lname') == 'buck')]

Pro tip: Using x.get('lname') instead of x['lname'] prevents errors if some rows don't have an lname key.

Option 2: Expand Info into separate columns first #

If you want to work with other fields from Info (like name or address) in your analysis, expanding the nested column into individual columns simplifies future operations:

# Expand the Info column into separate columns, then merge with the original DataFrame
expanded_df = pd.concat(
    [df.drop('Info', axis=1), pd.json_normalize(df['Info'])],
    axis=1
)

# Now filter directly on the standalone lname column
filtered_rows = expanded_df[expanded_df['lname'] == 'buck']

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