R语言：提取含JSON格式列的万级客户数据框嵌套内容

Hey there! Let's break down how to turn that nested JSON-filled DataFrame into a clean, flat one with all your customer data and nested fields—this is a super common task, and pandas has all the tools we need to handle it smoothly, even for 10k+ rows.

Step 1: Set Up & Parse JSON Strings #

First, we need to convert the JSON-formatted strings in columns x, y, z into actual Python dictionaries (since pandas can't directly work with JSON strings for normalization). We'll use json.loads for this, with a safety net for missing or invalid JSON to avoid crashes.

import pandas as pd
import json

# Load your existing DataFrame (adjust this to match your data source, e.g., CSV, database)
df = pd.read_csv("your_customer_data.csv")

# Define a safe JSON parsing function to handle edge cases
def safe_parse_json(json_str):
    if pd.isna(json_str):
        return {}  # Return empty dict for missing values
    try:
        return json.loads(json_str)
    except json.JSONDecodeError:
        return {}  # Gracefully handle malformed JSON entries

# Apply parsing to each JSON column
json_cols = ["x", "y", "z"]
for col in json_cols:
    df[col] = df[col].apply(safe_parse_json)

Step 2: Normalize JSON Columns #

Next, we'll use pd.json_normalize to flatten each nested JSON column into a separate DataFrame. We'll add prefixes to the column names (like x_, y_) to avoid conflicts if different JSON columns share the same field names.

normalized_dfs = []
for col in json_cols:
    # Flatten the JSON structure and add a prefix to track the original column
    normalized_df = pd.json_normalize(df[col]).add_prefix(f"{col}_")
    normalized_dfs.append(normalized_df)

Step 3: Combine All Data into a Final Flat DataFrame #

Now we just need to stitch together the original customer_id column with all our flattened JSON DataFrames. We'll use pd.concat to merge them horizontally (since all rows align by index).

# Start with the customer_id column as our base
base_df = df[["customer_id"]]

# Combine base data with all normalized JSON DataFrames
final_flat_df = pd.concat([base_df] + normalized_dfs, axis=1)

Key Notes & Pro Tips #

• Deeply Nested JSON: json_normalize automatically handles nested structures by creating column names with dot separators. For example, if x has a nested field {"profile": {"age": 30}}, the resulting column will be x_profile.age.
• Performance: For 10k+ rows, this approach is efficient—pandas is optimized for these operations, so you shouldn't run into major slowdowns.
• Duplicate Column Names: The prefixes we added (x_, y_) are crucial here. Without them, if x and y both have a score field, you'd end up with duplicate score columns in your final DataFrame.

Quick Example Output #

If your original data looked like this:

Your final flat DataFrame would be:

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