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
   }
}

CodePudding user response：

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:

• Documentation on json library
• Documentation on to_csv method

CodePudding user response：

df = pd.DataFrame(df).T.reset_index()
df = df.rename(columns= {'index':'s_name'})
df.to_excel('dataframe.xlsx', index=False)