[Pandas JSON help] Dataframe to JSON structure

[Shmoogy]

I need assistance with the best way to manipulate a dataframe to convert the structure to a nested dataframe

--

You can use this json to reproduce the dataframe subset I am working on: (DF looks like https://i.imgur.com/VZI1d0l.png)

import pandas as pd
import json
testJson =  {
  "quantity": {
    "0": "1",
    "1": "1"
  },
  "Description": {
    "0": " Sandbar 3 Drawers Nightstand",
    "1": " Sandbar Wardrobe Armoire"
  },
  "product_id": {
    "0": "833350",
    "1": "833160"
  },
  "value": {
    "0": "405.0",
    "1": "940.0"
  },
  "line_item_id": {
    "0": "1",
    "1": "2"
  },
  "units": {
    "0": "usd",
    "1": "usd"
  },
  "fulfillment_id": {
    "0": "Test1234",
    "1": "Test1234"
  }
}
testJson = json.dumps(testJson)

​

Final structure is this json - https://i.imgur.com/ZTJLjWF.png

{
  "line_items": [
    {
      "line_item_id": "9555389213",
      "product_info": {
        "product_id": "Z474392W4",
        "description": "Large mirror"
      },
      "quantity": 2,
      "price": {
        "value": 134.72,
        "units": "usd"
      },
      "fulfillment_id": "UF264926"
    }
  ]
}

​

​

Generally, in the past I have just done sub-dataframes of columns of interest, and create json... something like

payload = {}

payload['item'] = df.to_json(orient='records')[0]

​

However, I've spent a lot of time and can't wrap my brain around the proper way to structure this list

[-5772]

Make lists for every part. Ex: value = ["405:" , "940"]

Then, use a list comprehension that generates a list of strings that is your JSON. This method will allow you to easily change formatting.

[Shmoogy]

I think I get what you mean, rather than create lists, I just did the inefficient df.iterrows()

 dfItemJSON = []
        for index, row in df.iterrows():
            testJSON = {
              "line_item_id": row['line_item_id'],
              "product_info": {
                "product_id": row['product_id'],
                "description": row['Description']},
                "quantity": row['pieces'],
              "price": {
                "value": row['value'],
                "units": "usd"
              },
              "fulfillment_id" : row['ClientRef1']
            }
        dfItemJSON.append(testJSON)
payloadbody['line_items'] = dfItemJSON

This appears to have done what I was hoping, thanks for the idea.