如何将Pandas DataFrame转换为含JSON格式列的指定结构

原始数据 #

import pandas as pd

df_installation = pd.DataFrame({
    'InstallationID': ["Item 1", "Item 2", "Item 3","Item 1", "Item 2", "Item 3"],
    'Type': ["Metric", "Metric","Metric", "Imperial","Imperial","Imperial"],
    'Measure 1': [11998, 11076,12025,129145,119221,129],
    'Measure 2': [12000,12001,12002,129168,129178.764,129189.528],
    'Measure Type': ["Morning","Afternoon","Evening","Morning","Afternoon","Evening"],
})

目标结构 #

df_installation_new = pd.DataFrame({
    'InstallationID': ['ID no1', "ID no2", "ID no3"],
    'Metric': [
        """{"text 1": {"label": "Measure Time","value": "Morning"},"text 2": {"label": "Measure 1","value": 11998},"text 3": {"label": "Measure 2","value": 12000}}""",
        """{"text 1": {"label": "Measure Time","value": "Afternoon"},"text 2": {"label": "Measure 1","value": 11076},"text 3": {"label": "Measure 2","value": 12001}}""",
        """{"text 1": {"label": "Measure Time","value": "Evening"},"text 2": {"label": "Measure 1","value": 12025},"text 3": {"label": "Measure 2","value": 12002}}"""
    ],
    'Imperial': [
        """{"text 1": {"label": "Measure Time","value": "Morning"},"text 2": {"label": "Measure 1","value": 129145},"text 3": {"label": "Measure 2","value": 129168}}""",
        """{"text 1": {"label": "Measure Time","value": "Afternoon"},"text 2": {"label": "Measure 1","value": 119221},"text 3": {"label": "Measure 2","value": 129178.764}}""",
        """{"text 1": {"label": "Measure Time","value": "Evening"},"text 2": {"label": "Measure 1","value": 129},"text 3": {"label": "Measure 2","value": 129189.528}}"""
    ],
})

转换实现 #

1. 定义JSON生成函数 #

用json.dumps将每行数据转换成指定格式的JSON字符串：

import json

def generate_json(row):
    return json.dumps({
        "text 1": {"label": "Measure Time", "value": row['Measure Type']},
        "text 2": {"label": "Measure 1", "value": row['Measure 1']},
        "text 3": {"label": "Measure 2", "value": row['Measure 2']}
    })

2. 生成JSON列并重构结构 #

通过apply生成JSON列，再用透视表将Type转为列，最后重命名ID：

# 生成JSON字符串列
df_installation['json_col'] = df_installation.apply(generate_json, axis=1)

# 透视表转换宽表结构
result_df = df_installation.pivot(
    index='InstallationID',
    columns='Type',
    values='json_col'
).reset_index()

# 重命名InstallationID值
result_df['InstallationID'] = result_df['InstallationID'].map({
    "Item 1": "ID no1",
    "Item 2": "ID no2",
    "Item 3": "ID no3"
})

# 调整列顺序与目标一致
result_df = result_df[['InstallationID', 'Metric', 'Imperial']]

# 得到最终结果
df_installation_new = result_df

验证结果 #

打印输出即可确认是否与目标结构匹配：

print(df_installation_new)

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