1

I have an application that receives a JSON in the following format:

{
  "name": "person name",
  "food": {
    "fruit": "apple",
    "meal": {
      "lunch": "burger",
      "dinner": "pizza"
    }
  }
}

However I need to create an Excel (or csv) with that data, and transform that Excel into this format.

I tried to create an dictionary like this:

name,food.fruit,food.meal.lunch,food.meal.dinner
person name,apple,burger,pizza

I am trying to use pandas, but I am not being able to deal with this multi level headers to transform into a python dictionary.

Options

  • What is the best way to write this data into an CSV or Excel?
  • How to use Pandas to split the headers and convert it into a multi-level nested JSON?
Lavínia Beghini
  • 165
  • 1
  • 11
  • 1
    what is your question? – deadshot Mar 03 '22 at 18:03
  • I have updated the question. I am sorry I was not clear, but I am confused if I should change the way the data is written on the CSV, or if Pandas is a good solution for my output... – Lavínia Beghini Mar 03 '22 at 18:08
  • I think [this post](https://stackoverflow.com/questions/54776916/inverse-of-pandas-json-normalize) may be useful for you – DecowVR Mar 03 '22 at 18:34
  • [this post](https://stackoverflow.com/questions/54776916/inverse-of-pandas-json-normalize) it's almost all I need but apparently it does not consider arrays – Lavínia Beghini Mar 03 '22 at 20:21

1 Answers1

0

You need to use json_normalize as below:

import pandas as pd

 jsn = {
  "name": "person name",
  "food": {
    "fruit": "apple",
    "meal": {
      "lunch": "burger",
      "dinner": "pizza"
    }
  }
}

# create the flat structure
df = pd.json_normalize(jsn,  errors='ignore')
# write to a csv file
df.to_csv('file_name.csv', index=False)
Vivek Puurkayastha
  • 466
  • 1
  • 9
  • 18
  • What if I want the opposite, it generates the same input JSON? I have tried to run the generated csv.to_json() but it does not convert the keys to inside objects, which is what I need. – Lavínia Beghini Mar 03 '22 at 19:14