How to separate values in a dictionary to be put into CSV?

Question

I am trying to write my JSON output to CSV, but I'm not sure how to separate my values into individual columns

This is my current code

with open('dict.csv', 'w') as csv_file:
writer = csv.writer(csv_file)

for key, value in response.json().items():
   writer.writerow([value])
print(value)

This is the csv file I am getting: current csv file

This is the desired csv file/output I want to get: desired output

This is an example of my JSON Output

[{'id': '123', 'custom_id': '12', 'company': 28, 'company_name': 'Sunshine'}, {'id': '224', 'custom_id': '14', 'company': 38, 'company_name': 'Flowers'}, 
{'id': '888', 'custom_id': '10', 'company': 99, 'company_name': 'Fields'}]

how about this JSON format? (a more complicated one)

    [{'id': '777', 'custom_id': '000112', 'company': 28, 'company_name': 
   'Weddings Inc', 'delivery_address': '25 olive park terrace, 61234', 'delivery_timeslot': {'lower': '2019-12-06T10:00:00Z', 'upper': '2019-12-06T13:00:00Z', 'bounds': '[)'}, 'sender_name': 'Joline', 'sender_email': '', 'sender_contact': '91234567', 'removed': None, 'recipient_name': 'Joline', 'recipient_contact': '91866655', 'notes': '', 'items': [{'id': 21668, 'name': 'Loose hair flowers', 'quantity': 1, 'metadata': {}, 'removed': None}, {'id': 21667, 'name': "Groom's Boutonniere", 'quantity': 1, 'metadata': {}, 'removed': None}, {'id': 21666, 'name': 'Bridal Bouquet', 'quantity': 1, 'metadata': {}, 'removed': None}], 'latitude': '1.1234550920764211111', 'longitude': '103.864352476201000000', 'created': '2019-08-15T05:40:30.385467Z', 'updated': '2019-08-15T05:41:27.930110Z', 'status': 'pending', 'verbose_status': 'Pending', 'logs': [{'id': 56363, 'order': '50c402', 'order_custom_id': '000112', 'order_delivery_address': '25 olive park terrace, 61234', 'order_delivery_timeslot': {'lower': '2019-12-06T10:00:00Z', 'upper': '2019-12-06T13:00:00Z', 'bounds': '[)'}, 'message': 'Order was created.', 'failure_reason': None, 'success_code': None, 'success_description': None, 'created': '2019-08-15T05:40:30.431790Z', 'removed': None}, {'id': 56364, 'order': '50c402d8-7c76-45b5-b883-e2fb887a507e', 'order_custom_id': 'INV-000112', 'order_delivery_address': '25 olive park terrace, 61234', 'order_delivery_timeslot': {'lower': '2019-12-06T10:00:00Z', 'upper': '2019-12-06T13:00:00Z', 'bounds': '[)'}, 'message': 'Order is pending.', 'failure_reason': None, 'success_code': None, 'success_description': None, 'created': '2019-08-15T05:40:30.433139Z', 'removed': None}], 'reschedule_requests': [], 'signature': None}, 


{'id': '241', 'custom_id': '000123', 'company': 22, 'company_name': 'Pearl Pte Ltd', 'delivery_address': '90 Merchant Road, Hotel Royal, 223344', 'delivery_timeslot': {'lower': '2019-11-29T10:00:00Z', 'upper': '2019-11-29T13:00:00Z', 'bounds': '[)'}, 'sender_name': 'Vera Smith', 'sender_email': '', 'sender_contact': '81234567', 'removed': None, 'recipient_name': 'Vera Smith', 'recipient_contact': '81234561', 'notes': '', 'items': [{'id': 22975, 'name': 'Custom wrapped bouquet', 'quantity': 2, 'metadata': {}, 'removed': None}, {'id': 22974, 'name': "Parents' boutonniere x 3", 'quantity': 1, 'metadata': {}, 'removed': None}, {'id': 22973, 'name': "Groom's boutonniere", 'quantity': 1, 'metadata': {}, 'removed': None}, {'id': 22972, 'name': 'Loose hair flowers', 'quantity': 1, 'metadata': {}, 'removed': None}, {'id': 22971, 'name': 'Bridal Bouquet', 'quantity': 1, 'metadata': {}, 'removed': None}], 'latitude': '1.28821802835873000000', 'longitude': '103.84569230314800000000', 'created': '2019-08-30T03:20:17.477528Z', 'updated': '2019-08-30T03:29:25.307856Z', 'status': 'pending', 'verbose_status': 'Pending', 'logs': [{'id': 59847, 'order': '24117085-9104-4442-841b-4a734f801d39', 'order_custom_id': 'INV-000123', 'order_delivery_address': '90 Merchant Road, Hotel Royal, 223344', 'order_delivery_timeslot': {'lower': '2019-11-29T10:00:00Z', 'upper': '2019-11-29T13:00:00Z', 'bounds': '[)'}, 'message': 'Order was created.', 'failure_reason': None, 'success_code': None, 'success_description': None, 'created': '2019-08-30T03:20:17.511250Z', 'removed': None}, {'id': 59848, 'order': '24117085-9104-4442-841b-4a734f801d39', 'order_custom_id': 'INV-000123', 'order_delivery_address': '90 Merchant Road, Hotel Royal, 223344', 'order_delivery_timeslot': {'lower': '2019-11-29T10:00:00Z', 'upper': '2019-11-29T13:00:00Z', 'bounds': '[)'}, 'message': 'Order is pending.', 'failure_reason': None, 'success_code': None, 'success_description': None, 'created': '2019-08-30T03:20:17.513132Z', 'removed': None}], 'reschedule_requests': [], 'signature': None}]

Could you post a sample dict file that I could test on before posting the answer? — Tarun Kolla, Sep 04 '19 at 04:33
@DamiánRafaelLattenero this is JSON example [{'id': '123', 'custom_id': '12', 'company': 28, 'company_name': 'Sunshine'}, {'id': '224', 'custom_id': '14', 'company': 38, 'company_name': 'Flowers'}, {'id': '888', 'custom_id': '10', 'company': 99, 'company_name': 'Fields'}] — Yuna, Sep 04 '19 at 04:39
@TarunKolla this is the sample dict in JSON format [{'id': '123', 'custom_id': '12', 'company': 28, 'company_name': 'Sunshine'}, {'id': '224', 'custom_id': '14', 'company': 38, 'company_name': 'Flowers'}, {'id': '888', 'custom_id': '10', 'company': 99, 'company_name': 'Fields'}] — Yuna, Sep 04 '19 at 04:39

Tarun Kolla · Answer 1 · 2019-09-04T04:53:16.580

0

Try:

import csv

csv_file = 'my_file.csv'
csv_columns = ['id', 'custom_id', 'company', 'company_name']
dict_data = [{'id': '123', 'custom_id': '12', 'company': 28, 'company_name': 'Sunshine'}, {'id': '224', 'custom_id': '14', 'company': 38, 'company_name': 'Flowers'}, {'id': '888', 'custom_id': '10', 'company': 99, 'company_name': 'Fields'}]

try:
    with open(csv_file, 'w') as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=csv_columns)
        writer.writeheader()
        for data in dict_data:
            writer.writerow(data)
except IOError:
    print("I/O error")

edited Sep 04 '19 at 04:53

answered Sep 04 '19 at 04:47

Tarun Kolla

917
1
10
30

`csv_columns = dict_data[0].keys()` this works too based on how you would like to use the code. – Tarun Kolla Sep 04 '19 at 04:50
thanks, that works, but what about a more complicated JSON where the list contains different variables, hence the code above won't be able to cater to it, (i added the more complicated JSON in the question) – Yuna Sep 04 '19 at 07:53

score 0 · Answer 2 · answered Sep 04 '19 at 04:50

Use pandas library:

df.to_csv() - Write object to a comma-separated values (csv) file.

Ex.

import pandas as pd

data = [{'id': '123', 'custom_id': '12', 'company': 28, 'company_name': 'Sunshine'},
 {'id': '224', 'custom_id': '14', 'company': 38, 'company_name': 'Flowers'},
{'id': '888', 'custom_id': '10', 'company': 99, 'company_name': 'Fields'}]

df = pd.DataFrame(data)
df.to_csv('sample.csv')

fabda01 · Answer 3 · 2019-09-04T05:13:59.460

Given your response data in json format

response =  [{'id': '123', 'custom_id': '12', 'company': 28, 'company_name': 'Sunshine'}, 
            {'id': '224', 'custom_id': '14', 'company': 38, 'company_name': 'Flowers'}, 
            {'id': '888', 'custom_id': '10', 'company': 99, 'company_name': 'Fields'}]

You can convert it to a list of lists using

header = [response[0].keys()]
data = [row.values() for row in response]
csv_list = header + data

And then save it to csv using

with open('dict.csv', "w") as f:
    for row in csv_list:
        f.write("%s\n" % ','.join(str(col) for col in row))

This should yield your desired output

How to separate values in a dictionary to be put into CSV?

3 Answers3