技术求助：从JSON文件提取以jive_label为列名的DataFrame

Hey there! Let's fix this up properly. I'll walk you through extracting the DataFrame you need and troubleshooting that broken code snippet you shared.

First, let's start with the obvious fix in your code: you cut off the JSON parsing step—r.j... should be r.json() to properly convert the API response into a Python dictionary.

Step 1: Assume a Typical JSON Structure #

Since you didn't share the full JSON, let's use a realistic example that matches your description (adjust this if your actual JSON has nested layers):

{
  "metadata": "some extra info",
  "profile": [
    {"jive_label": "Full Name", "value": "Mamtha Pillai"},
    {"jive_label": "Email Address", "value": "mamtha@example.com"},
    {"jive_label": "Job Title", "value": "Data Analyst"}
  ]
}

Step 2: Full Working Code #

Here's the revised code that will extract the profile data into the DataFrame you want:

import requests
import pandas as pd
import sys

# Replace these with your actual API details
url = "your_target_api_url"
payload = {}
headers = {"Content-Type": "application/json"}
querystring = {}

try:
    # Send the request and handle potential errors
    r = requests.get(url, data=payload, headers=headers, params=querystring, verify=False)
    r.raise_for_status()  # Throw an error if the API request fails
    json_data = r.json()  # Fix: Properly parse the JSON response
except requests.exceptions.RequestException as e:
    print(f"Request failed: {str(e)}")
    sys.exit(1)

# Convert the profile array into a dictionary (jive_label as keys, values as values)
profile_map = {item["jive_label"]: [item["value"]] for item in json_data["profile"]}
# Using [item["value"]] ensures pandas recognizes it as a column of values

# Create the DataFrame
df = pd.DataFrame(profile_map)

# Check the result
print(df)

Step 3: If You Have Multiple Profile Entries #

If your JSON returns a list of objects (each with its own profile array), adjust the code to loop through all entries:

# Example JSON structure for multiple entries:
# [
#   {"profile": [{"jive_label": "Full Name", "value": "Alice"}, ...]},
#   {"profile": [{"jive_label": "Full Name", "value": "Bob"}, ...]}
# ]

profile_entries = []
for entry in json_data:
    profile_dict = {item["jive_label"]: item["value"] for item in entry["profile"]}
    profile_entries.append(profile_dict)

df = pd.DataFrame(profile_entries)

Common Troubleshooting Tips #

• Double-check the JSON path: If json_data["profile"] throws a KeyError, your profile array is probably nested deeper (e.g., json_data["data"]["profile"]). Use print(json_data.keys()) to explore the structure.
• Handle missing values: If some jive_label entries are missing in certain profiles, add .fillna(np.nan) when creating the DataFrame to avoid gaps.
• Verify API response: Always check r.status_code before parsing—if it's not 200, the API might be returning an error instead of valid JSON.

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