how to edit the json by pandas dataframe after than get back the json?-CodePudding

I am following this tutorial https://towardsdatascience.com/how-to-convert-json-into-a-pandas-dataframe-100b2ae1e0d8 and build something like this.

The sample json data:

{
    "product":{
          "a":"apple",
          "b":"banana"
    },
    "order":[{
          "code":"123",
          "qty":"1",
          "food":"apple x10|banana x2",
          "amount":"200"},
          {
            "code":"bundle"
            "qty":"1"
           }
]
}

There is my code:

df = pd.json_normalize(
    data, 
    record_path =['order'], 
    meta=[
        ['product', 'a'],
        ['product', 'b']
    ]
)

df = df.fillna(0)

And this will output:

code,qty,food,amount,a,b
123,1,apple x10|banana x2,200,apple,banana
bundle,1,0,0,apple,banana

Because I need to fill zero in the missing elements columns, I let the json to be dataframe first. But when I finish this, I don't know how to get back the origin json format.

I need the output like sample json:

{
    "product":{
          "a":"apple",
          "b":"banana"
    },
    "order":[{
          "code":"123",
          "qty":"1",
          "food":"apple x10|banana x2",
          "amount":"200"},
          {
            "code":"bundle"
            "qty":"1",
            "food":"0",
            "amount":"0"
           }
]
}

Any help would be appreciated

CodePudding user response：

If I understood your question, try something like this. However, it is not typically the same as the original json.

import json

result = df.to_json(orient="records")
parsed = json.loads(result)

the output:

[{'code': '123',
  'qty': '1',
  'food': 'apple x10|banana x2',
  'amount': '200',
  'product.a': 'apple',
  'product.b': 'banana'},
 {'code': 'bundle',
  'qty': '1',
  'food': 0,
  'amount': 0,
  'product.a': 'apple',
  'product.b': 'banana'}]

CodePudding user response：

To convert a pandas.DataFrame to json, you can use the to_json function as described in the docs here

data = {
"product":{
      "a":"apple",
      "b":"banana"
},
"order":[{
      "code":"123",
      "qty":"1",
      "food":"apple x10|banana x2",
      "amount":"200"},
      {
        "code":"bundle",
        "qty":"1"
       }
 ]
}

df = pd.json_normalize(
    data,
    record_path =['order'],
    meta=[
        ['product', 'a'],
        ['product', 'b']
     ]
 )
 df = df.fillna(0)
 df.to_json()
 > '{"code":{"0":"123","1":"bundle"},"qty":{"0":"1","1":"1"},"food":{"0":"apple x10|banana x2","1":0},"amount":{"0":"200","1":0},"product.a":{"0":"apple","1":"apple"},"product.b":{"0":"banana","1":"banana"}}'

Since you're trying to get back to your original json, this might be helpful. Using the code provided there, the output I get is:

[{'code': '123',
  'qty': '1',
  'food': 'apple x10|banana x2',
  'amount': '200',
  'product': {'a': 'apple', 'b': 'banana'}},
 {'code': 'bundle',
  'qty': '1',
  'food': 0,
  'amount': 0,
  'product': {'a': 'apple', 'b': 'banana'}}]