将数据框转换为嵌套的JSON

Question

将数据框转换为嵌套的JSON

4

我正在使用

pd.read_sql_query()

从数据库中获取数据，然后使用。

to_json(orient='records')

这是数据框架：

(1)
  price_formula_id  premium  product_id  exchange  product_name  product_code   weight  
0            30064      0.0        c001       CME          2018            CL      0.3
1            30064      0.0        c002       CME          2018            CL      0.7

(2)
price_formula_id  premium  product_id  exchange  product_name  product_code   weight  
0            30064      NONE        c001       CME          2018            CL      0.3
1            30064      NONE        c002       CME          2018            CL      0.7

转换为此格式。

[{
    "price_formula_id": "30064",
    "premium": "0.0",
    "product_id": "c001",
    "exchange": "CME",
    "product_name": "2018",
    "product_code": "CL",
    "weight": "0.3"
},
{
    "price_formula_id": "30064",
    "premium": "0.0",
    "product_id": "c002",
    "exchange": "CME",
    "product_name": "2018",
    "product_code": "CL",
    "weight": "0.7"
}]

但是我真正想要的应该是这样的：

 { 
   "price_formula_id": "30064",
   "premium": "0.0",
   "basket": 
    [
     {"product_id": "c001",
      "exchange": "CME",
      "product_name": "2018",
      "product_code": "CL",
      "weight": "0.3"
     },
     {
      "product_id": "c002",
      "exchange": "CME",
      "product_name": "2018",
      "product_code": "CL",
      "weight": "0.7"
     }
    ]
 }

我需要将相同的信息进行分组，并为剩余部分设置一个新的索引“basket”。我怎样才能实现呢？非常感谢。

- Alex

请发布您的数据框。 - Pyd

嗨，刚刚添加了。谢谢。 - Alex

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jezrael · Accepted Answer

使用groupby函数和自定义函数，配合使用to_dict函数，对通过difference函数筛选出的所有列进行分组操作，然后使用reset_index函数将结果重置索引，并最终使用to_json函数将其转换为Json格式。

cols = df.columns.difference(['price_formula_id','premium'])
j = (df.groupby(['price_formula_id','premium'])[cols]
       .apply(lambda x: x.to_dict('r'))
       .reset_index(name='basket')
       .to_json(orient='records'))
print (j)

[{
    "price_formula_id": 30064,
    "premium": 0.0,
    "basket": [{
            "exchange": "CME",
            "product_code": "CL",
            "product_id": "c001",
            "product_name": 2018,
            "weight": 0.3
        },
        {
            "exchange": "CME",
            "product_code": "CL",
            "product_id": "c002",
            "product_name": 2018,
            "weight": 0.7
        }
    ]
}]