Skip to content
Advertisement

Converting a Dataframe into a specific json format

I need to convert this dataframe into the json format below and I can’t get it to work

conv_item_id updated_item_value order_check
a 1.99 approved
b 2.99 approved
c 2.99 approved 
{
   "conversion_items":{
      "a":{
        "item_value":1.99,
        "status":"approved"
    },
      "b":{
        "item_value":2.99,
        "status":"approved"
    },
      "c":{
        "item_value":2.99,
        "status":"approved"
    }
  }
}

I currently have this function that converts the dataframe into a string

# build all our requests
def build_request(row):   
    return {
        row["conv_item_id"]: {
                "item_value": row["updated_item_value"],
                "status" : str.lower(row["order_check"])
            }
        }

request_payload = df.apply(build_request, axis=1).to_json(orient='records')[1:-1].replace('}{', '} {')

df2 = {"conversion_items": request_payload}

gives the following result as a string

{'conversion_items': '{"a":{"item_value":1.99,"status":"approved"}},{"b":{"item_value":2.99,"status":"approved"}},{"c":{"item_value":2.99,"status":"approved"}}'}

I need the format to be

{'conversion_items': {"a":{"item_value":1.99,"status":"approved"},"b":{"item_value":2.99,"status":"approved"},"c":{"item_value":2.99,"status":"approved"}}

Does anyone have any idea how to do this? I’ve attempted to use strip and replace but it either only removes the final } or all of them.

Thanks in advance,

Advertisement

Answer

Example df:

import pandas as pd
df = pd.DataFrame({"conv_item_id":["a","b","c"],"updated_item_value":[1.99,2.99,2.99],"order_check":["approved"]*3})
  • set index to conv_item_id using df.set_index()
  • rename column names using df.rename()
  • Convert df to dict with index as keys using df.to_dict("index")
  • dump dict to json using json.dumps()
import json
df = df.set_index("conv_item_id")
df = df.rename(columns={"updated_item_value":"item_value","order_check":"status"})
out = json.dumps({"conversion_items": df.to_dict("index")})
print(out)

>> {'conversion_items': {'a': {'item_value': 1.99, 'status': 'approved'},
  'b': {'item_value': 2.99, 'status': 'approved'},
  'c': {'item_value': 2.99, 'status': 'approved'}}}
Advertisement