如何将指定Pandas DataFrame导出为目标格式的JSON文件？

问题描述 #

我通过以下Python代码生成了一个DataFrame：

import pandas as pd
from tabulate import tabulate

data0 = {'dir':[0,'','',90,'','','']}
data1 = {'dist':['0 to 1h','1h to 2h','2h to 3h','0 to 1h','1h to 2h','2h to 3h','> 3h']}
data2 = {'max':[-0.271, -0.17 , -0.034, -0.322, -0.208, -0.057, 0.018]}
data3 = {'min':[-0.441, -0.339, -0.203, -0.491, -0.378, -0.227, -0.151]}
df0 = pd.DataFrame(data0)
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
df3 = pd.DataFrame(data3)
pressure = []
pressure.append(df0)
pressure.append(df1)
pressure.append(df2)
pressure.append(df3)
df = pd.concat(pressure, axis=1)
print(tabulate(df, headers='keys', showindex=False))

希望将这个DataFrame导出为如下结构的JSON文件：

{
  "0": [
    {
      "max": {
        "0 to 1h": -0.271
      },
      "min": {
        "0 to 1h": -0.441
      }
    },
    {
      "max": {
        "1h to 2h": -0.17
      },
      "min": {
        "1h to 2h": -0.339
      }
    },
    {
      "max": {
        "2h to 3h": -0.034
      },
      "min": {
        "2h to 3h": -0.203
      }
    }
  ],
  "90": [
    {
      "max": {
        "0 to 1h": -0.322
      },
      "min": {
        "0 to 1h": -0.491
      }
    },
    {
      "max": {
        "1h to 2h": -0.208
      },
      "min": {
        "1h to 2h": -0.378
      }
    },
    {
      "max": {
        "2h to 3h": -0.057
      },
      "min": {
        "2h to 3h": -0.227
      }
    },
    {
      "max": {
        "> 3h": 0.018
      },
      "min": {
        "> 3h": -0.151
      }
    }
  ]
}

实现方案 #

核心步骤 #

1. 填充dir列的空值：用前向填充法把空值替换为对应的0或90，确保分组逻辑正确
2. 按dir分组处理数据：遍历每个分组的行，组装成目标格式的字典列表
3. 导出为JSON文件：将处理后的结构写入文件

完整代码 #

import pandas as pd
import json

# 生成原DataFrame
data0 = {'dir':[0,'','',90,'','','']}
data1 = {'dist':['0 to 1h','1h to 2h','2h to 3h','0 to 1h','1h to 2h','2h to 3h','> 3h']}
data2 = {'max':[-0.271, -0.17 , -0.034, -0.322, -0.208, -0.057, 0.018]}
data3 = {'min':[-0.441, -0.339, -0.203, -0.491, -0.378, -0.227, -0.151]}
df = pd.concat([pd.DataFrame(d) for d in [data0, data1, data2, data3]], axis=1)

# 填充dir列空值
df['dir'] = df['dir'].ffill()

# 构建目标JSON结构
result = {}
for dir_val, group in df.groupby('dir'):
    dir_key = str(dir_val)
    result[dir_key] = []
    for _, row in group.iterrows():
        dist = row['dist']
        result[dir_key].append({
            'max': {dist: row['max']},
            'min': {dist: row['min']}
        })

# 保存为JSON文件
with open('pressure_data.json', 'w', encoding='utf-8') as f:
    json.dump(result, f, indent=2, ensure_ascii=False)

代码说明 #

• df['dir'].ffill()：用前一个非空值填充当前空值，解决原DataFrame中dir列只有开头值的问题
• df.groupby('dir')：按方向值分组，分别处理0和90对应的所有行数据
• 遍历分组内的每一行，将dist作为max/min字典的键，对应数值作为值，组装成列表元素
• json.dump()：将结构写入文件，indent=2保证格式美观，ensure_ascii=False支持特殊字符正确保存

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