Convert Pandas Dataframe to nested JSON for table records

user_3947269 0
Viewed 79

I am using Python and Pandas. Trying to convert a Pandas Dataframe to a nested JSON. The function .to_json() doesn't give me enough flexibility for my aim.

Here are some data points of the data frame (in CSV, comma separated):

Hotel_id,Room_id,Client_id,Loayalty_level,Price

1,100,1,Default,100
1,100,2,Default,98
1,101,1,Default,200
1,101,1,Discounted,196
1,101,2,Default,202
1,101,3,Default,204

There is a lot of repetitive information and I would like to have a JSON like this:

{
  "hotelId": 1,
  "rooms": [
    {
      "roomId": 100,
      "prices": [
        {
          "clientId": 1,
          "price": {
            "default": 100
          }
        },
        {
          "clientId": 2,
          "price": {
            "default": 98
          }
        }
      ]
    },
    {
      "roomId": 101,
      "prices": [
        {
          "clientId": 1,
          "price": {
            "default": 200,
            "discounted": 196
          }
        },
        {
          "clientId": 2,
          "price": {
            "default": 202
          }
        },
        {
          "clientId": 3,
          "price": {
            "default": 204
          }
        }
      ]
    }
  ]
}

How to achieve this?

pandas sqlite python json
1 Answers

Have a look at convtools library, it provides a lot of primitives for data processing. Here is the solution for your case:

import json

from convtools import conversion as c
from convtools.contrib.tables import Table


input_data = [
    ("Hotel_id", "Room_id", "Client_id", "Loayalty_level", "Price"),
    ("1", "100", "1", "Default", "100"),
    ("1", "100", "2", "Default", "98"),
    ("1", "101", "1", "Default", "200"),
    ("1", "101", "1", "Discounted", "196"),
    ("1", "101", "2", "Default", "202"),
    ("1", "101", "3", "Default", "204"),
]

# if reading from csv is needed
# rows = Table.from_csv("tmp/input.csv", header=True).into_iter_rows(tuple)

# convert to list of dicts
rows = list(Table.from_rows(input_data, header=True).into_iter_rows(dict))

# generate the converter (store somewhere and reuse, because this is where
# code-generation happens)
converter = (
    c.group_by(c.item("Hotel_id"))
    .aggregate(
        {
            "hotelId": c.item("Hotel_id").as_type(int),
            "rooms": c.ReduceFuncs.Array(c.this()).pipe(

                c.group_by(c.item("Room_id")).aggregate(
                    {
                        "roomId": c.item("Room_id").as_type(int),
                        "prices": c.ReduceFuncs.Array(c.this()).pipe(

                            c.group_by(c.item("Client_id")).aggregate(
                                {
                                    "clientId": c.item("Client_id").as_type(
                                        int
                                    ),
                                    "price": c.ReduceFuncs.DictFirst(
                                        c.item("Loayalty_level"),
                                        c.item("Price").as_type(float),
                                    ),
                                }
                            )
                        ),
                    }
                )
            ),
        }
    )
    .gen_converter()
)

print(json.dumps(converter(rows)))

The output is:

[
  {
    "hotelId": 1,
    "rooms": [
      {
        "roomId": 100,
        "prices": [
          { "clientId": 1, "price": { "Default": 100.0 } },
          { "clientId": 2, "price": { "Default": 98.0 } }
        ]
      },
      {
        "roomId": 101,
        "prices": [
          { "clientId": 1, "price": { "Default": 200.0, "Discounted": 196.0 } },
          { "clientId": 2, "price": { "Default": 202.0 } },
          { "clientId": 3, "price": { "Default": 204.0 } }
        ]
      }
    ]
  }
]

P.S. pay attention to the c.ReduceFuncs.DictFirst part, this is where it takes the first price per loyalty level, you may want to change this to DictLast / DictMax / DictMin / DictArray.

user_53877380
Related