如何将含嵌套对象的JSON转换为Pandas DataFrame？

Hey there! I get what you're dealing with—those duplicate value/stringValue columns from json_normalize can be a real hassle. Instead of cleaning up after the fact, we can directly build the exact DataFrame you want by mapping aliases to their labels and extracting only the stringValue upfront. Here's how:

Step 1: Create a Column Alias-to-Label Mapping #

First, we'll make a dictionary to translate those cryptic aliases (like c3) to human-readable column names (like Person ID):

import pandas as pd

# Your JSON data
my_json = {
 "columns": [
 { "alias": "c3", "label": "Person ID", "dataType": "integer" },
 { "alias": "c36", "label": "Position ID", "dataType": "string" },
 { "alias": "c40", "label": "Job ID", "dataType": "integer", "entityType": "job" },
 { "alias": "c19", "label": "Manager", "dataType": "integer" },
 ],
 "data": [
 { "c3": { "value": 192, "stringValue": "192" },
 "c36": { "value": "936", "stringValue": "936" },
 "c40": { "value": 93, "stringValue": "93" },
 "c19": { "value": 12412453, "stringValue": "Tom" }
 }
 ]
}

# Build alias to label mapping
col_mapping = {col['alias']: col['label'] for col in my_json['columns']}

Step 2: Process Data to Extract String Values #

Next, we'll iterate over each entry in the data array, pull out the stringValue for each alias, and rename keys using our mapping:

processed_data = [
    {col_mapping[alias]: entry[alias]['stringValue'] for alias in entry}
    for entry in my_json['data']
]

Step 3: Create the Clean DataFrame #

Now just pass the processed data to pd.DataFrame()—no post-cleanup required!

df = pd.DataFrame(processed_data)
print(df)

This outputs exactly the DataFrame you're aiming for:

Person ID Position ID Job ID Manager
0       192         936     93      Tom

Bonus: Using json_normalize (If You Prefer) #

If you still want to stick with json_normalize, you can filter and rename columns in one go (though it's a bit more verbose):

df = pd.json_normalize(my_json['data'], sep='_')
# Keep only stringValue columns and rename them to their labels
df = df.filter(regex='_stringValue$').rename(columns=lambda x: col_mapping[x.split('_')[0]])

This method cuts out the value columns and renames the stringValue columns using our mapping, but the first approach is more efficient since it avoids creating extra columns entirely.

Hope this helps you get the clean DataFrame you need without the extra grunt work!

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