如何将JSON字典中的问题转换为DataFrame的规范列名？

你的现有代码直接使用原始question["question"]文本作为DataFrame列名，容易出现格式混乱、特殊字符或过长等问题。以下是三种可行的优化方案，可根据实际需求选择：

方案1：手动映射规范列名 #

适用于问题数量较少、需要精准定义列名的场景：

import numpy as np
import pandas as pd
import json 

# 加载JSON文件
filepath = "C:/Users/osmi-survey-2016_1479139902.json"
with open(filepath,"r") as openFile:
    my_json_file_health = json.load(openFile)

# 提取问题与响应数据
questions = my_json_file_health.get("questions", [])
responses = my_json_file_health.get("responses", [])

# 定义问题ID到规范列名的映射（根据实际问题ID补充）
question_name_mapping = {
    "q1": "年龄范围",
    "q2": "所在行业",
    "q3": "心理健康自评",
    "q4": "职场支持满意度"
}

# 用规范列名构建响应字典
response_dicts = [{question_name_mapping.get(question["id"], f"未知问题_{question['id']}"): response["answers"].get(question["id"], None) 
                  for question in questions}
                  for response in responses]

# 转换为DataFrame
responses_df = pd.DataFrame(response_dicts)
responses_df

方案2：自动格式化原始问题文本 #

适用于问题数量较多、需要快速生成统一格式列名的场景：

import numpy as np
import pandas as pd
import json 
import re

# 加载JSON文件
filepath = "C:/Users/osmi-survey-2016_1479139902.json"
with open(filepath,"r") as openFile:
    my_json_file_health = json.load(openFile)

# 提取问题与响应数据
questions = my_json_file_health.get("questions", [])
responses = my_json_file_health.get("responses", [])

# 列名格式化函数：移除特殊字符、转小写、空格替换为下划线
def format_column_name(raw_name):
    cleaned = re.sub(r'[^\w\s]', '', raw_name)
    formatted = cleaned.strip().lower().replace(" ", "_")
    return formatted

# 用格式化后的列名构建响应字典
response_dicts = [{format_column_name(question["question"]): response["answers"].get(question["id"], None) 
                  for question in questions}
                  for response in responses]

# 转换为DataFrame
responses_df = pd.DataFrame(response_dicts)
responses_df

方案3：结合问题ID与简化文本 #

适用于需要保留问题溯源标识、同时保证列名清晰的场景：

import numpy as np
import pandas as pd
import json 
import re

# 加载JSON文件
filepath = "C:/Users/osmi-survey-2016_1479139902.json"
with open(filepath,"r") as openFile:
    my_json_file_health = json.load(openFile)

# 提取问题与响应数据
questions = my_json_file_health.get("questions", [])
responses = my_json_file_health.get("responses", [])

# 列名格式化函数：结合问题ID与文本关键部分
def format_column_name(question_id, raw_name):
    key_part = re.sub(r'[^\w\s]', '', raw_name).strip().split()[:3]
    key_str = "_".join(key_part).lower()
    return f"{question_id}_{key_str}"

# 用组合式列名构建响应字典
response_dicts = [{format_column_name(question["id"], question["question"]): response["answers"].get(question["id"], None) 
                  for question in questions}
                  for response in responses]

# 转换为DataFrame
responses_df = pd.DataFrame(response_dicts)
responses_df

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