Convert a directory of JSON files to one CSV file with a Py loop

Question

I have a directory with hundreds of JSON files, and would like to convert and merge them into one CSV file.

I found this question. One answer explains how to convert one JSON file to CSV:

import pandas as pd
with open('jsonfile.json', encoding='utf-8') as inputfile:
    df = pd.read_json(inputfile)
df.to_csv('csvfile.csv', encoding='utf-8', index=False)

Using that code, I tried to create a loop, but I can't make it work (I'm new to programming). This is what I tried:

import pandas as pd
import os
dir1 = 'jsfiles'
fileList = os.listdir(dir1)
for ffile in fileList:
    with open(ffile, encoding='utf-8') as inputfile:
        df = pd.read_json(inputfile)
df.to_csv('csvfile.csv', encoding='utf-8', index=False)

I get this error:

ValueError: Trailing data

anupamkrishna · Accepted Answer · 2022-06-28 15:12:08Z

1

You can accumulate all data frames in a list and use pd.concat() to merge them into one huge dataframe:

import pandas as pd
import os
dir1 = 'jsfiles'
dfs = []
fileList = os.listdir(dir1)
for ffile in fileList:
    with open(os.path.join(dir1, ffile), encoding='utf-8') as inputfile:
        dfs.append(pd.read_json(inputfile))
df = pd.concat(dfs, sort=False)
df.to_csv('csvfile.csv', encoding='utf-8', index=False)

edited Jun 28, 2022 at 15:12

answered Jun 28, 2022 at 5:11

anupamkrishna

764 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kasi Over a year ago

I get this error (even though the file definitely exists and is included in fileList when I print it): File "js_to_csv.py", line 8, in <module> with open(ffile, encoding='utf-8') as inputfile: FileNotFoundError: [Errno 2] No such file or directory: 'repos.json'

anupamkrishna Over a year ago

Your program is looking for a file called "repos.json" in the same folder as your program is located. Your repos.json exists in a different directory. You will have to provide a relative or absolute file path for it to work. Change with open(ffile) to with open(os.path.join(dir1, ffile))

Collectives™ on Stack Overflow

Convert a directory of JSON files to one CSV file with a Py loop

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related