0

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

isgood
  • 1
  • 1
  • You have first flattened the json by using json_normalise. So pandas would not know how to nest it back in your required format. You will have to specify is yourself. You can refer this https://stackoverflow.com/questions/40470954/convert-pandas-dataframe-to-nested-json – Vivek Anand Jun 23 '22 at 09:38

2 Answers2

1

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'}]
Ola Galal
  • 162
  • 1
  • 11
  • this is very close. Sorry for my confused question. I want the result can build back my 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"} } ] }``` – isgood Jun 23 '22 at 09:20
  • I think you need to modify something in the conversion of the original json to df so you can get it back correctly. – Ola Galal Jun 23 '22 at 09:22
0

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'}}]
genhernandez
  • 453
  • 1
  • 5
  • 19
  • Thank for your help. But I hope I can get back the json format like the sample json. I just edit my question. – isgood Jun 23 '22 at 09:27
  • I don't think there is a built in pandas way to do this, you'll have to create custom logic to take your dataframe back to your original json. – genhernandez Jun 23 '22 at 09:35