-4

Whats the best way to convert the below JSON to CSV in python?

[{
    "header": "_id|_n|_p|_e|_v|_d|_r",
    "data": "1_qweqwe|sometitle|320|0|0|0|0;1_asdasdasd|sometitle2|130|0|0|0|0",
    "totalCount": 13
},
{
    "header": "_id|_n|_p|_e|_v|_d|_r",
    "data": "1_abccd|sometitle3|360|0|0|0|0;1_asdasdasd|sometitle4|320|0|0|0|0",
    "totalCount": 23
},
{
    "header": "_id|_n|_p|_e|_v|_d|_r",
    "data": "1_sdsfsd|sometitle5|600|0|0|0|0;1_asdasdasd|sometitle6|100|0|0|0|0",
    "totalCount": 33
}]
martineau
  • 119,623
  • 25
  • 170
  • 301
Vinod
  • 503
  • 4
  • 8
  • See https://docs.python.org/3/library/csv.html as reference. – Xinhe Wang May 31 '21 at 14:31
  • 2
    Does this answer your question? [How can I convert JSON to CSV?](https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv) – Sven Eberth May 31 '21 at 14:34
  • Need more clarification. Does `data` holds values for all columns? Is `header` fixed for all items and is the expected header of the CSV file? What `totalCount` means? Is it another column value? – Roy May 31 '21 at 15:18

1 Answers1

0

This is the best i could do:

import csv
import json

a=[]
x = """[{"header": "_id|_n|_p|_e|_v|_d|_r","data": "1_qweqwe|sometitle|320|0|0|0|0;1_asdasdasd|sometitle2|130|0|0|0|0","totalCount": 13},{"header": "_id|_n|_p|_e|_v|_d|_r","data": "1_abccd|sometitle3|360|0|0|0|0;1_asdasdasd|sometitle4|320|0|0|0|0","totalCount": 23},{"header": "_id|_n|_p|_e|_v|_d|_r","data": "1_sdsfsd|sometitle5|600|0|0|0|0;1_asdasdasd|sometitle6|100|0|0|0|0","totalCount": 33}]"""

x = json.loads(x)

f = csv.writer(open("test.csv", "w"))

f.writerow(["_id", "_n", "_p", "_e", "_v", "_d", "_r","_tt"])

for x in x:
    temp=x["totalCount"]
    for x in x["data"].split(";"):
        print(x)
        f.writerow([x.split('|')[0],x.split('|')[1],x.split('|')[2],x.split('|')[3],x.split('|')[4],x.split('|')[5],x.split('|')[6],temp])
    f.writerow([])

Let me know if we could improve it further.

Vinod
  • 503
  • 4
  • 8
  • This question is closed now. So could not post any answer. You can try copying the below code. However, I am not sure if the formatting will be preserved. – Roy May 31 '21 at 15:48
  • import numpy json_obj = [{ "header": "_id|_n|_p|_e|_v|_d|_r", "data": "1_qweqwe|sometitle|320|0|0|0|0;1_asdasdasd|sometitle2|130|0|0|0|0", "totalCount": 13 }, { "header": "_id|_n|_p|_e|_v|_d|_r", "data": "1_abccd|sometitle3|360|0|0|0|0;1_asdasdasd|sometitle4|320|0|0|0|0", "totalCount": 23 }, { "header": "_id|_n|_p|_e|_v|_d|_r", "data": "1_sdsfsd|sometitle5|600|0|0|0|0;1_asdasdasd|sometitle6|100|0|0|0|0", "totalCount": 33 }] header = ["_id", "_n", "_p", "_e", "_v", "_d", "_r","_tt"] print(header) – Roy May 31 '21 at 15:49
  • table = [header] # initialise table with the header for item in json_obj: data = item["data"].split(";") # get individual row data from json for row_data_text in data: row_data = row_data_text.split("|") row_data.append(item["totalCount"]) table.append(row_data) # add the row data to the table print(table) np_table = numpy.asarray(table) numpy.savetxt("foo.csv", np_table, delimiter=",", fmt="%s") – Roy May 31 '21 at 15:49