Merge several JSON files into one CSV file

Question

I have about 37 nested JSON files. The keys and the values are same in all the files. My objective is to combine the data in all the files into a single CSV file so I can analyze the output . I have tried several approaches, but could not obtain the expected result. When I run the code, only one of the files gets converted to csv. I am looking for a better way to achieve my objective.

Here is my approach:

import pandas as pd 
import glob
import os

folder_path = r"C:/Data"
file_path = r"C:/Data/Proj_files" 
files = r"C:/Data/Proj_files/Noise*.json"
json_files = os.path.join(folder_path, file_path, files)
out_csv = r"C:/Data/Noise.csv"
file_list = [] 
df_list = [] 

for file in glob.glob(json_files):
    allFiles = file_list.append(file)
    print(file_list)
for item in file_list:
    pd_files = pd.read_json(item, lines = True)
    df_list.append(item)
    print(df_list)
for i in df_list:
    pdf = pd.DataFrame(i)
pdf.to_csv(out_csv)

...

Here is the content of one of the files:

{
  "dataset": [
    {
      "lat": null,
      "lng": null,
      "db": "50.312",
      "date": "2020-07-10T18:15:00Z",
      "id": 65130542
    },
    {
      "lat": null,
      "lng": null,
      "db": "54.111",
      "date": "2020-07-10T18:15:01Z",
      "id": 65130543
    },
    {
      "lat": 5.100984404061876,
      "lng": -1.2783955592339389,
      "db": "53.1",
      "date": "2020-07-10T18:15:02Z",
      "id": 65130544
    }
  ],
  "tags": []
}

Answer 1

You are overwriting the pdf variable every iteration of your last loop, so you only ever write out the last dataframe.

Just append all the intermediate dataframes to a list, use pandas.concat to create a single dataframe from the list, then output that.

E.g. (note I used json.load as your example data did not work with your code)

df_list = []

for json_file in glob.glob(json_files):
    json_data = json.load(open(json_file))
    df = pd.DataFrame(json_data['dataset'])
    df_list.append(df)

pdf = pd.concat(df_list)

pdf.to_csv(out_csv, index=False)

Output:

lat,lng,db,date,id
,,50.312,2020-07-10T18:15:00Z,65130542
,,54.111,2020-07-10T18:15:01Z,65130543
5.100984404061876,-1.2783955592339389,53.1,2020-07-10T18:15:02Z,65130544