How to convert a pandas data frame to nested json

Question

Currently have a data frame contain laptop info and the aim is to transform the data to a nested json structure. As the laptop's brand, price and weight are related info, thus would like to group them together under the laptop field. Any pointer on how to convert the data frame would be appreciated.

data frame

Target json structure

Answer 1

Consider df:

In [2729]: df = pd.DataFrame({'Store code':[1, 12, 132], 'Laptop brand':['Lenovo', 'Apple', 'HP'], 'Laptop price':[1000, 2000, 1200], 'Laptop weight':[1.2, 1.5, 1.4], 'star':[3, 5, 4]})

In [2730]: df
Out[2730]: 
   Store code Laptop brand  Laptop price  Laptop weight  star
0           1       Lenovo          1000            1.2     3
1          12        Apple          2000            1.5     5
2         132           HP          1200            1.4     4

Use df._to_dict with orient='records':

In [2734]: x = df.to_dict('records') # Create a list of dicts from df
In [2738]: l = [] # Empty list for storing your output

In [2763]: for i in x: # Iterate every item in list of dicts
      ...:     d = {}
      ...:     d1 = {}
      ...:     for k,v in i.items(): # Iterate each individual dict
      ...:         if 'Laptop' in k: # Check if word `Laptop` is present in key 
      ...:             d1[k] = v     # If yes, create a separate dict for all laptop keys
      ...:         else:
      ...:             d[k] = v      
      ...:     d['Laptop'] = [d1]    # Add another key `Laptop` which holds a `list` of dicts of Laptop
      ...:     l.append(d)           # Append this dict in list
      ...: 

Output:

In [2774]: print(json.dumps(l, indent=2))
[
  {
    "Store code": 1,
    "star": 3,
    "Laptop": [
      {
        "Laptop brand": "Lenovo",
        "Laptop price": 1000,
        "Laptop weight": 1.2
      }
    ]
  },
  {
    "Store code": 12,
    "star": 5,
    "Laptop": [
      {
        "Laptop brand": "Apple",
        "Laptop price": 2000,
        "Laptop weight": 1.5
      }
    ]
  },
  {
    "Store code": 132,
    "star": 4,
    "Laptop": [
      {
        "Laptop brand": "HP",
        "Laptop price": 1200,
        "Laptop weight": 1.4
      }
    ]
  }
]