使用Pandas分析包含字典列表的案例结构化数据

Alright, let's walk through how to handle this—you’ve got a CSV with 20k rows that map to just 300 unique cases, and you want to restructure it into a clean 300-entry dataset (with nested lists/dicts like your sample) then analyze it using Pandas. Here’s a step-by-step approach that works for this kind of nested data:

First, we’ll load your raw CSV and group rows by CaseNumber to roll up each case’s data into the nested structure you need. We’ll use a custom aggregation function to handle different field types (single values, lists, and dictionaries):

import pandas as pd

# Load the raw CSV file
df = pd.read_csv('your_raw_data.csv')

# Define a function to aggregate rows into a single case entry
def build_case(group):
    # Assemble the case data with appropriate nested structures
    case = {
        # Collect unique treatment values into a list
        'Treatment': group['Treatment'].dropna().unique().tolist(),
        # Grab single-value fields (assuming consistency per case)
        'Year': group['Year'].iloc[0],
        'Reason': group['Reason'].iloc[0],
        'CaseNumber': group['CaseNumber'].iloc[0],
        'OutCome': group['OutCome'].iloc[0],
        # Collect unique symptoms into a list
        'Symptoms': group['Symptoms'].dropna().unique().tolist(),
        # Convert drug rows into a list of dictionaries
        'case_drugs': group[['Substance', 'Poisindex_Desc', 'SubstanceFormula_20c...']]
                      .dropna()
                      .to_dict('records')
    }
    return pd.Series(case)

# Group by CaseNumber and apply the aggregation to get 300 cases
case_level_df = df.groupby('CaseNumber').apply(build_case).reset_index(drop=True)

Quick breakdown of the aggregation: #

• For single-value fields (like Year or Reason), we take the first entry from the group—if your data has inconsistencies here (e.g., same case with different years), swap iloc[0] with mode().iloc[0] to use the most frequent value.
• For list fields (like Treatment or Symptoms), unique().tolist() ensures we only keep distinct values without duplicates.
• The case_drugs field uses to_dict('records') to turn each drug row into a dictionary, then wraps them all in a list—exactly matching your sample structure.

Since your data has nested structures, JSON is the ideal text format to preserve this structure cleanly. Use this line to save your 300-case dataset:

# Save as a formatted JSON text file (easy to read and reuse)
case_level_df.to_json('processed_cases.json', orient='records', indent=2)

The orient='records' parameter ensures each case is a separate JSON object, and indent=2 makes the file human-readable.

Once you have your case-level dataset, here are some useful Pandas operations to analyze it:

Count outcomes by category #

# Get the number of cases per outcome type
outcome_counts = case_level_df['OutCome'].value_counts()
print(outcome_counts)

Analyze substance frequency across all cases #

# Extract all substances from nested case_drugs lists
all_substances = case_level_df['case_drugs'].explode().apply(lambda x: x['Substance']).dropna()
# Count how often each substance appears
substance_popularity = all_substances.value_counts()
print(substance_popularity)

Filter cases with specific symptoms #

# Find all cases that include "Drowsy/lethargy / Related" in their symptoms
drowsy_cases = case_level_df[case_level_df['Symptoms'].apply(lambda x: "Drowsy/lethargy / Related" in x)]
print(f"Number of drowsy cases: {drowsy_cases.shape[0]}")

Explore outcomes by year #

# Cross-tabulate cases by year and outcome type
year_outcome_trends = case_level_df.groupby(['Year', 'OutCome']).size().unstack(fill_value=0)
print(year_outcome_trends)

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