How to make dataframe from complex json?

user_12877988 0
Viewed 85

I have data as json below. I want to convert it to dataframe that I can export to excel. However, I can't find a way to make correct dataframe. I attached example of dataframe below. I am using Pandas but I can use any other library also.

{
   "Street1":{
      "a":1,
      "b":2,
      "c":3
   },
   "Street2":{
      "a":1,
      "b":2,
      "c":3
   },
   "Street3":{
      "a":1,
      "b":2,
      "c":3
   }
}

enter image description here

pandas dataframe python-3.x python
2 Answers
df = pd.DataFrame(df).T.reset_index()
df = df.rename(columns= {'index':'s_name'})
df.to_excel('dataframe.xlsx', index=False)
user_127754320

You can read the JSON file using builtin json library. It will create a dictionary from the JSON data. Then convert the dictionary to CSV file using Pandas to_csv method.

Code:

import json
import pandas as pd


data = json.load(open("data.json"))
df = pd.DataFrame.from_dict(data, orient='index')
df.to_csv("output.csv")

data.json:

{
   "Street1":{
      "a":1,
      "b":2,
      "c":3
   },
   "Street2":{
      "a":1,
      "b":2,
      "c":3
   },
   "Street3":{
      "a":1,
      "b":2,
      "c":3
   }
}

Output:

output.csv:

,a,b,c
Street1,1,2,3
Street2,1,2,3
Street3,1,2,3

References:

user_31294140
Related