Generate a json file

Question

I need to generate a .json file which has data in the following format:

{"cnt":[1950,1600,400,1250,995],
  "dt":["2020-01","2020-02","2020-03","2020-04","2020-05"]}

I would prefer it getting generated by querying a table or using a CSV to JSON conversion. The format data I will have after querying or in my CSV file will be:

How to do this?

Does this answer your question? [How do I write JSON data to a file?](https://stackoverflow.com/questions/12309269/how-do-i-write-json-data-to-a-file) — mkrieger1, Sep 23 '20 at 12:44
And https://stackoverflow.com/questions/41585078/how-do-i-read-and-write-csv-files-with-python — mkrieger1, Sep 23 '20 at 12:45

S.D. · Answer 1 · 2020-09-23T15:10:11.350

0

This example will turn your row-based data into a dict-based version. Please keep in mind that I didn't test this - but it should work fine.

In essence this is what's happening:

Read the source data
Determine the headings you need for the dict
Fill the new data-format from your source_data
Dump this new format to a json file.

Code:

import csv
import json

# Read the source data
with open('path_to_csv_file') as f:
    source_data = [i for i in csv.DictReader(f)]

# Discover headers, and prep dict framework.
target_data = {key: [] for key in source_data[0].keys()}

# Iterate over the source_data and append the values to the righ key in target_data
for row in source_data:
    for k, v in row.items():
        target_data[k].append(v)

# Write target data to json file
with open('path_to_json_file', 'w') as f:
    json.dump(data, f)

edited Sep 23 '20 at 15:10

answered Sep 23 '20 at 10:57

S.D.

2,486
1
16
23

@Sarala, just try this: json.dump(data, f) – Mahesh Anakali Sep 23 '20 at 11:31
It is not giving expected output :( – Sarala Sep 23 '20 at 11:32
[{"\u00ef\u00bb\u00bfCNT": "1950", "DT": "2020-01"}, {"\u00ef\u00bb\u00bfCNT": "1600", "DT": "2020-02"}, {"\u00ef\u00bb\u00bfCNT": "400", "DT": "2020-03"}, {"\u00ef\u00bb\u00bfCNT": "1250", "DT": "2020-04"}, {"\u00ef\u00bb\u00bfCNT": "995", "DT": "2020-05"}] I am getting this as output – Sarala Sep 23 '20 at 11:33
@S.D, The first parameter for json.dump() should be data object and second parameter should file_obj. Please edit your code. – Mahesh Anakali Sep 23 '20 at 11:33
@Sarala, can you update [i for i in csv.DictReader(f)] to [i for i in csv.DictReader(f, fieldnames=['CNT','DT'])] and try? and after this try print(data) and check the data format. – Mahesh Anakali Sep 23 '20 at 11:42
1

@MaheshAnakali I am getting this now : [OrderedDict([('CNT', 'ï»¿CNT'), ('DT', 'DT')]), OrderedDict([('CNT', '1950'), ('DT', '2020-01')]), OrderedDict([('CNT', '1600'), ('DT', '2020-02')]), OrderedDict([('CNT', '400'), ('DT', '2020-03')]), OrderedDict([('CNT', '1250'), ('DT', '2020-04')]), OrderedDict([('CNT', '995'), ('DT', '2020-05')])] – Sarala Sep 23 '20 at 12:06
with open('cnt.csv') as f: data = [i for i in csv.DictReader(f, fieldnames=['CNT','DT'])] print(data) – Sarala Sep 23 '20 at 12:06
@Sarala, I added a answer, check it out! – Mahesh Anakali Sep 23 '20 at 12:59

score 0 · Answer 2 · answered Sep 23 '20 at 12:39

import csv
import json

with open('csv_file_path') as f:
    dict_reader = csv.DictReader(f)
    dicts = [dict(i) for i in dict_reader]
    field_names = dict_reader.fieldnames #get the column headings # CNT,DT etc..
output_dict = {}
for item in field_names:
    output_dict.setdefault(item,[])
for d in dicts:
    for key in d:
        output_dict[key].append(d[key])

with open('josn_file_path', 'w+') as f:
    f.write(json.dumps(output_dict, indent=4))

Tested and works fine.

Generate a json file

2 Answers2