Link to home
Start Free TrialLog in
Avatar of Wm Allen Smith
Wm Allen Smith

asked on

Use Python to Save json to excel file

Good Day,


I have python code that loops through a directoryof invidual json files, reads tehjson content of each file and prints the output. I want to save the output as one excel file with aggregated output. Please advise as I get errors when I attempt it.


Here is the code:


 import openpyxl
 import json
 import pandas as pd
 
 
  
 from glob import glob
 
 data = []
 for file_name in glob('/Volumes/GoogleDrive/My Drive/Downloads/*.json'):
     with open(file_name) as f:
         data.append(json.load(f))
  
 df=data
  
 df = json.dumps((df))
 print(df)

Open in new window


SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

As some examples show above you can use the Pandas Dataframe to read and collect the data from each JSON file using the read_json( ..) method (giving that each json file read into the same datafraem have the same structure) Then use pandas aggregation functionality and finally save the resulting dataframe to Excel using the ExcelWriter method.

https://pandas.pydata.org/pandas-docs/version/1.1.3/reference/frame.html
https://pandas.pydata.org/pandas-docs/version/1.1.3/reference/api/pandas.read_json.html
https://pandas.pydata.org/pandas-docs/version/1.1.3/reference/api/pandas.ExcelWriter.html

Regards,
    Tomas Helgi