1

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": []
}
5
  • 1
    An "and" in the title is an indication that you are asking multiple questions.
    – Vince
    Sep 14, 2022 at 2:23
  • 2
    Don't forget to explain what goes wrong when you run your code. Eg, what happens that shouldn't happen, or what does not happen that should happen, or what is different in the results to what you exected. Sep 14, 2022 at 3:29
  • I have addressed the concerns raised
    – Edudzi
    Sep 14, 2022 at 18:38
  • Your code has a syntax error (jspn_file vice json_file). You should probably add diagnostic print statements in the processing loops, listing the objects as they are processed, since it doesn't look like the non-GIS parts of the code are correct.
    – Vince
    Sep 14, 2022 at 20:33
  • I have reworked on the code
    – Edudzi
    Sep 16, 2022 at 11:25

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.