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 |