将数据帧转换为json？

Question

我有一个这样定义的示例数据帧：

df = pd.DataFrame(
    [["167517", "TOCOTOCO25k","122.31"], ["167517", "PHUCLONG30","100.5"],["87691","TAITHU","150"]],
    index=["1", "2","3"],
    columns=["user_id", "voucher_code","scoring"],
)

看起来是这样的：

    user_id voucher_code scoring
1   167517  TOCOTOCO25k  122.31
2   167517  PHUCLONG30   100.5
3   87691   TAITHU       150

当我使用df.json()时，输出如下所示：

{
   "user_id":{
      "1":"167517",
      "2":"167517",
      "3":"87691"
   },
   "voucher_code":{
      "1":"TOCOTOCO25k",
      "2":"PHUCLONG30",
      "3":"TAITHU"
   },
   "scoring":{
      "1":"122.31",
      "2":"100.5",
      "3":"150"
   }
}

但是我想喜欢这个。如何将dataframe转换为这种格式

  [
        {
          "user_id": "167517",
          "vouchers": [
            {
              "voucher_code": "PHUCLONG30",
              "scoring": "122.31"
            },
            {
              "voucher_code": "TOCOTOCO",
              "scoring": "100"
            }
          ]
        },
    {
          "user_id": "87691",
          "vouchers": [
            {
              "voucher_code": "TAITHU",
              "scoring": "150"
            }
          ]
        }
    ]

我知道这很简单，但我不知道如何转换这个JSON格式。多谢各位

Answer 1

我将尝试按用户id对dataframe进行分组，并将分组结果映射到所需的结构：

import json

grouped = df.groupby('user_id').agg({"voucher_code": lambda x: list(x), "scoring": lambda x: list(x) })
result = []
for t in grouped.itertuples():
  result.append({'user_id': t.Index, 'vouchers': [{'voucher': v, 'scoring': s} for v,s in zip(t.voucher_code, t.scoring)]})

json.dumps(r)

结果:

[
    {
        "user_id": "167517",
        "vouchers": [
            {
                "voucher": "TOCOTOCO25k",
                "scoring": "122.31"
            },
            {
                "voucher": "PHUCLONG30",
                "scoring": "100.5"
            }
        ]
    },
    {
        "user_id": "87691",
        "vouchers": [
            {
                "voucher": "TAITHU",
                "scoring": "150"
            }
        ]
    }
]