python - 将 DataFrame 格式化为面向行的 JSON 对象(电子表格样式数据透视表)

Question

我有一个带有 MultiIndex 级别的 pandas DataFrame，如下所示:

现在我尝试将此数据帧转换为面向行的 JSON 对象(或 spreadsheet-style pivot table ？)，结果应如下所示:

[{"date":"2016-11-08T23:00:00.000Z", "flag":0, "value":545.0, "moduleId":"module_A","parameterId":"parameter_B", "locationId":"location_1" },  
 {"date":"2016-11-09T23:00:00.000Z", "flag":0, "value":545.0, "moduleId":"module_A","parameterId":"parameter_B", "locationId":"location_1" },
 {"date":"2016-11-10T23:00:00.000Z", "flag":8, "value":-999.0, "moduleId":"module_A","parameterId":"parameter_B", "locationId":"location_1" },
 {"date":"2016-11-11T23:00:00.000Z", "flag":0, "value":680.0, "moduleId":"module_A","parameterId":"parameter_B", "locationId":"location_1" },
 {"date":"2016-11-12T23:00:00.000Z", "flag":8, "value":-999.0, "moduleId":"module_A","parameterId":"parameter_B", "locationId":"location_1" }]

我尝试使用以下命令

df.reset_index().to_json(orient='records', date_format='iso')

但是 MultiIndex 结构保持不变。

上面使用的虚拟集可以按如下方式创建:

import pandas as pd
from pandas import Timestamp

input_dict = {('module_A',
  'parameter_B',
  'location_1',
  'flag'): {  Timestamp('2016-11-09 05:30:00+0630', tz='Asia/Yangon'): 0,
  Timestamp('2016-11-10 05:30:00+0630', tz='Asia/Yangon'): 0,
  Timestamp('2016-11-11 05:30:00+0630', tz='Asia/Yangon'): 8,
  Timestamp('2016-11-12 05:30:00+0630', tz='Asia/Yangon'): 0,
  Timestamp('2016-11-13 05:30:00+0630', tz='Asia/Yangon'): 8},
 ('module_A',
  'parameter_B',
  'location_1',
  'value'): {  Timestamp('2016-11-09 05:30:00+0630', tz='Asia/Yangon'): 545.0,
  Timestamp('2016-11-10 05:30:00+0630', tz='Asia/Yangon'): 545.0,
  Timestamp('2016-11-11 05:30:00+0630', tz='Asia/Yangon'): -999.0,
  Timestamp('2016-11-12 05:30:00+0630', tz='Asia/Yangon'): 680.0,
  Timestamp('2016-11-13 05:30:00+0630', tz='Asia/Yangon'): -999.0}}

df = pd.DataFrame(input_dict)

如有任何建议，我们将不胜感激。

Answer 1

您需要stack + rename_axis第一:

df = df.stack([0,1,2]).rename_axis(['date','moduleId','parameterId','locationId'])
d = df.reset_index().to_json(orient='records', date_format='iso')

print (d)

[{"date":"2016-11-08T23:00:00.000Z","moduleId":"module_A","parameterId":"parameter_B","locationId":"location_1","flag":0,"value":545.0},
 {"date":"2016-11-09T23:00:00.000Z","moduleId":"module_A","parameterId":"parameter_B","locationId":"location_1","flag":0,"value":545.0},
 {"date":"2016-11-10T23:00:00.000Z","moduleId":"module_A","parameterId":"parameter_B","locationId":"location_1","flag":8,"value":-999.0},
 {"date":"2016-11-11T23:00:00.000Z","moduleId":"module_A","parameterId":"parameter_B","locationId":"location_1","flag":0,"value":680.0},
 {"date":"2016-11-12T23:00:00.000Z","moduleId":"module_A","parameterId":"parameter_B","locationId":"location_1","flag":8,"value":-999.0}]