Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

python df into specific json format

I need to create a json following this model from a df using python.
I tried to load this json into a df to find the correct layout but did not succeed.
Can anyone help me or at list give me some hints?

Thanks in advance

{
  "products": [
    {
      "productId": 123456790,
      "quantaty": 10,
      "orderLine": "10",
      "buyerRef": "my ref"
    },
    {
      "productId": 123456791,
      "quantaty": 15,
      "orderLine": "20",
      "buyerRef": "my ref"
    }
  ],
  "costId": 109,
  "paymentMethod": "termTransferWire",
  "totalPriceTaxIncl": 1200,
  "deliveryAddressId": 218,
  "buyerOrderNumber": "Test",
  "documentationRequestedName": [
    "document 1",
    "document 2"
  ]
}
Tags:

3

Answers


  1. 0

    First create a dictionary of your json object

    json_obj = {
"products": [
    {
    "productId": 123456790,
    "quantaty": 10,
    "orderLine": "10",
    "buyerRef": "my ref"
    },
    {
    "productId": 123456791,
    "quantaty": 15,
    "orderLine": "20",
    "buyerRef": "my ref"
    }
],
"costId": 109,
"paymentMethod": "termTransferWire",
"totalPriceTaxIncl": 1200,
"deliveryAddressId": 218,
"buyerOrderNumber": "Test",
"documentationRequestedName": [
    "document 1",
    "document 2"
]
}

    Now in this dictionary, the json_obj.values can be considered as rows, and json_obj.keys can be considered as columns.

    Create a new list for columns and rows

    cols = list(json_obj.keys())
rows = list(json_obj.values())

    Now, import the pandas library and create your data frame as follows:

    import pandas as pd

pd_df = pd.DataFrame([rows], columns = cols)
    Login or Signup to reply.
  2. 0

    Try this:

    import pandas as pd
import json

data = {
    "productId": [123456790, 123456791],
    "quantity": [10, 15],
    "orderLine": ["10", "20"],
    "buyerRef": ["my ref", "my ref"],
    "costId": [109, 109],
    "paymentMethod": ["termTransferWire", "termTransferWire"],
    "totalPriceTaxIncl": [1200, 1200],
    "deliveryAddressId": [218, 218],
    "buyerOrderNumber": ["Test", "Test"],
    "documentationRequestedName": [["document 1", "document 2"], ["document 1", "document 2"]]
}

df = pd.DataFrame(data)

# Convert DataFrame to JSON
json_data = {
    "products": df[["productId", "quantity", "orderLine", "buyerRef"]].to_dict(orient="records"),
    "costId": int(df["costId"].iloc[0]),
    "paymentMethod": df["paymentMethod"].iloc[0],
    "totalPriceTaxIncl": int(df["totalPriceTaxIncl"].iloc[0]),
    "deliveryAddressId": int(df["deliveryAddressId"].iloc[0]),
    "buyerOrderNumber": df["buyerOrderNumber"].iloc[0],
    "documentationRequestedName": df["documentationRequestedName"].iloc[0]
}

# Convert dictionary to JSON
result_json = json.dumps(json_data, indent=2)
print(result_json)
    Login or Signup to reply.
  3. 0

    Try like this

    import pandas as pd

# Assuming you have a DataFrame df with the necessary columns
df = pd.DataFrame({
    'productId': [123456790, 123456791],
    'quantity': [10, 15],
    'orderLine': ['10', '20'],
    'buyerRef': ['my ref', 'my ref']
})

# Other information
cost_id = 109
payment_method = 'termTransferWire'
total_price_tax_incl = 1200
delivery_address_id = 218
buyer_order_number = 'Test'
documentation_requested_name = ['document 1', 'document 2']

# Convert DataFrame to JSON
json_data = {
    'products': [
        {
            'productId': row['productId'],
            'quantity': row['quantity'],
            'orderLine': row['orderLine'],
            'buyerRef': row['buyerRef']
        }
        for _, row in df.iterrows()
    ],
    'costId': cost_id,
    'paymentMethod': payment_method,
    'totalPriceTaxIncl': total_price_tax_incl,
    'deliveryAddressId': delivery_address_id,
    'buyerOrderNumber': buyer_order_number,
    'documentationRequestedName': documentation_requested_name
}

print(json_data)

    The resulting json_data will be a dictionary following the structure you provided. You can use the json.dumps method to convert it to a JSON string if needed:
    import json

    json_string = json.dumps(json_data, indent=2)
print(json_string)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search