Home > Back-end >  JSON to CSV in Specific Format
JSON to CSV in Specific Format

Time:04-26

I am trying to Convert a Json file to Csv

I am not able to build the logic. This is what i tried

CODE

import json
import csv
import os

with open('truck_trips.json') as json_file:
    data = json.load(json_file)

file_path = 'json_data.csv'
    
if not os.path.exists(file_path):
    print("No File")
    header = ['Truck_Id', 'Trip_Id', 'Start', 'End']
    with open(file_path, 'w') as f:
        writer = csv.writer(f)
        writer.writerow(header)
    
    for item in data:
        print(item)
        for t in data[item]:
            print(t)
            print(data[item][t])
        print("\n")

CodePudding user response:

This does the job,

# Getting the JSON content.
with open("/content/truck_trips.json", "r") as f:
  json_output = f.read()

output_dict = json.loads(json_output)

# Storing the relevant data from the JSON file into a list.
trucks_detail = []

for truck in output_dict:
  for trip in output_dict[truck]:
    trips = output_dict[truck][trip]

    for i in range(len(trips)):
      start, end = trips[i][0], trips[i][1]
      truck_detail = [truck, f"{trip}.{i   1}", start, end]
      trucks_detail.append(truck_detail)

# Converting it into a dataframe.
df = pd.DataFrame(trucks_detail, columns = ["Truck_Id", "Trip_Id", "Start", "End"])
df.to_csv("Truck Details.csv", index = False)

The dataframe,

Truck_Id Trip_Id Start End
0 truck_1 trip_1.1 Toronto Kitchener
1 truck_1 trip_1.2 Kitchener Cobourg
2 truck_1 trip_1.3 Cobourg Aylmer
3 truck_1 trip_1.4 Aylmer Owen Sound
4 truck_1 trip_1.5 Owen Sound Orillia
5 truck_1 trip_1.6 Orillia Toronto
6 truck_1 trip_2.1 Toronto Ottawa - Gatineau
7 truck_1 trip_2.2 Ottawa - Gatineau Welland - Pelham
8 truck_1 trip_2.3 Welland - Pelham Toronto
9 truck_1 trip_3.1 Toronto Fort Frances
  • Related