0

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
Kasi
  • 235
  • 2
  • 11

1 Answers1

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)
anupamkrishna
  • 66
  • 3
  • 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 with open(ffile, encoding='utf-8') as inputfile: FileNotFoundError: [Errno 2] No such file or directory: 'repos.json' – Kasi Jun 28 '22 at 05:44
  • 1
    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))** – anupamkrishna Jun 28 '22 at 15:08