3

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

enter image description here

Target json structure

enter image description here

Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58
Codezzz
  • 235
  • 1
  • 3
  • 14
  • Similar question and answer you can find here: [https://stackoverflow.com/questions/40470954/convert-pandas-dataframe-to-nested-json](https://stackoverflow.com/questions/40470954/convert-pandas-dataframe-to-nested-json) – Ena Dec 29 '20 at 14:11
  • Please don't post images. Images are discouraged on SO. Please take time to read [`how-to-make-good-reproducible-pandas-examples`](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – Shubham Sharma Dec 29 '20 at 14:26

1 Answers1

3

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
      }
    ]
  }
]
Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58
  • thank you, it'd be great if you could add some explanation – Codezzz Dec 29 '20 at 14:40
  • You're welcome. I've added explanation in form of comments in my answer. Please have a look. – Mayank Porwal Dec 29 '20 at 14:45
  • thank you. May i ask you've checked if word `Laptop` is present in key, so if the key doesnt contain the word 'Laptop', is there a way to include the key to the separate dict? – Codezzz Dec 29 '20 at 14:50
  • If the key does not contain the word `Laptop`, it will be stored in the same way, like `Store code` and `star`. – Mayank Porwal Dec 29 '20 at 14:52
  • okay got it, if in the future, there's a new key that doesn't contain the word Laptop yet need to be included in the separate dict 'Laptop', could this be done? – Codezzz Dec 29 '20 at 14:54
  • Yes, it can. You would need to tweak the `if` statement in that case. – Mayank Porwal Dec 29 '20 at 14:55
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/226569/discussion-between-codezzz-and-mayank-porwal). – Codezzz Dec 29 '20 at 14:57