How do I write this piece of data to a csv file?

Question

I'm trying to write data to a csv file. The data is in a confusing format and I cannot change it. This is how the data dictionary looks like. How do i write this data in a csv file?

[{'box': [277, 90, 48, 63], 
  'confidence': 0.99, 
  'keypoints': {
    'left_eye': (291, 117), 
    'right_eye': (314, 114), 
    'nose': (303, 131), 
    'mouth_left': (296, 143), 
    'mouth_right': (313, 141)}
}]

I tried using the code below however, using this code only writes the field names in the csv file and not the values.

import csv 
with open('peak.csv', 'w') as csvFile:
    fields = ['box ', 'confidence', 'keypoints' ]
    writer = csv.DictWriter(csvFile, fieldnames=fields)
    writer.writeheader()
    writer.writerows(result)

print("writing completed")

csvFile.close()

This is the result I'm getting. Results

@rachanadugana, just for clarification, the data that you are showing is a list that contains a dictionary not a dictionary. — lmiguelvargasf, Apr 01 '19 at 03:35
@rachanadugana, do you want just the keys of the dictionary without any data? — lmiguelvargasf, Apr 01 '19 at 03:41
Is there a specific reason you need CSV? A nested dictionary structure like this is not easy to represent. The format it's in now is probably easier to read as-is instead of converting it to a flat form with fields like `keypoints.left_eye[0]` — Will T, Apr 01 '19 at 03:43
I need to save the data somewhere and csv file seems like the best option. @rylan — rachana dugana, Apr 01 '19 at 03:46
Do you want to be able to get this format of the structure back? If so, I recommend just saving it to a file using `json.dump()`, then just using `json.load()`. That would keep the structure intact. CSV is more suited to values that look like a spreadsheet. (note: you'll have to `import json` if you decide to do this) — Will T, Apr 01 '19 at 03:51
Does it sound stupid to save it in a json and then convert it to csv? I want to do this because CSV is preferred by my lecturer @rylan — rachana dugana, Apr 01 '19 at 03:57
If you're going to convert it to csv anyway, might as well convert it before saving. I'll see if I can come up with an answer. — Will T, Apr 01 '19 at 03:59
**pandas** is too good in this work. If you are okay to use any kind of Python library then I have a suggestion for this with a quick example. — hygull, Apr 01 '19 at 04:32

score 1 · Answer 1 · answered Apr 01 '19 at 04:36

Assuming you want dict data of keypoints in csv

import csv

results = [{'box': [277, 90, 48, 63],
           'confidence': 0.99,
           'keypoints': {
               'left_eye': (291, 117),
               'right_eye': (314, 114),
               'nose': (303, 131),
               'mouth_left': (296, 143),
               'mouth_right': (313, 141)}
}]

with open('peak.csv', 'w') as csv_file:
    writer = csv.writer(csv_file)
    writer.writerow(result[0].keys())
    for resultDict in results:
        writer.writerow(resultDict.values())
csv_file.close()

CSV file content:

box,                 confidence,    keypoints
"[277, 90, 48, 63]",  0.99,         "{'left_eye': (291, 117), 'mouth_left': (296, 143), 'nose': (303, 131), 'mouth_right': (313, 141), 'right_eye': (314, 114)}"

score 0 · Answer 2 · answered Apr 01 '19 at 03:46

0

Just extract the dic from the list and write its keys and values with items():

with open('peak.csv', 'w') as csv_file:
    writer = csv.writer(csv_file)
    for key, value in list[0].items():
       writer.writerow([key, value])

answered Apr 01 '19 at 03:46

gregory

10,969
2
30
42

I have tried doing it like this but it does not work. – rachana dugana Apr 01 '19 at 03:58
@rachanadugana So you've tried extracting the dictionary from the from list -- are you sure? – gregory Apr 01 '19 at 04:01

walgarch · Answer 3 · 2019-04-01T04:19:30.643

This might work for you. Though I'm not sure how you want to represent the key value pairs in 'keypoints'

import csv

myDict = [{'box': [277, 90, 48, 63],
           'confidence': 0.99,
           'keypoints': {
    'left_eye': (291, 117),
    'right_eye': (314, 114),
    'nose': (303, 131),
    'mouth_left': (296, 143),
    'mouth_right': (313, 141)}
}]

with open('peak.csv', 'w') as csv_file:
    writer = csv.writer(csv_file)
    writer.writerow(myDict[0].keys())
    for item in myDict:
        writer.writerow(item.values())

print("writing completed")

csv_file.close()

Output looks like:

box,confidence,keypoints
"[277, 90, 48, 63]",0.99,"{'left_eye': (291, 117), 'mouth_left': (296, 143), 'nose': (303, 131), 'mouth_right': (313, 141), 'right_eye': (314, 114)}"

R. Arctor · Accepted Answer · 2019-04-01T17:02:44.337

Couple of quick things. The function you posted has an extra space in 'box' entry of the fields list, and in python the with keyword will automatically close a file for you. No need to do it explicitly.

I have tested this on my machine, running Python 3.6. I included a duplicate in the list because I assume your data has more than a single output list? If not it shouldn't make a difference.

Are you sure the following code doesn't work?

import csv

data = [{'box': [277, 90, 48, 63],
         'confidence': 0.99,
         'keypoints': {
             'left_eye': (291, 117),
             'right_eye': (314, 114),
             'nose': (303, 131),
             'mouth_left': (296, 143),
             'mouth_right': (313, 141)}
         },
        {'box': [277, 90, 48, 63],
         'confidence': 0.99,
         'keypoints': {
             'left_eye': (291, 117),
             'right_eye': (314, 114),
             'nose': (303, 131),
             'mouth_left': (296, 143),
             'mouth_right': (313, 141)}
         }
        ]
# Write to csv
with open('data.csv', 'w') as f:
    fields = ['box', 'confidence', 'keypoints']
    w = csv.DictWriter(f, fieldnames=fields)
    w.writeheader()
    w.writerows(data)

# Append to existing csv
with open('data.csv', 'a') as f:
    fields = ['box', 'confidence', 'keypoints']
    w = csv.DictWriter(f, fieldnames=fields)
    w.writerows(data)

With the output:

box,confidence,keypoints
"[277, 90, 48, 63]",0.99,"{'left_eye': (291, 117), 'right_eye': (314, 114), 'nose': (303, 131), 'mouth_left': (296, 143), 'mouth_right': (313, 141)}"
"[277, 90, 48, 63]",0.99,"{'left_eye': (291, 117), 'right_eye': (314, 114), 'nose': (303, 131), 'mouth_left': (296, 143), 'mouth_right': (313, 141)}"

What the output looks like in LibreCalc

If you want the keys of keypoints in their own columns:

import csv
data = [{'box': [277, 90, 48, 63],
         'confidence': 0.99,
         'keypoints': {
             'left_eye': (291, 117),
             'right_eye': (314, 114),
             'nose': (303, 131),
             'mouth_left': (296, 143),
             'mouth_right': (313, 141)}
         },
        {'box': [277, 90, 48, 63],
         'confidence': 0.99,
         'keypoints': {
             'left_eye': (291, 117),
             'right_eye': (314, 114),
             'nose': (303, 131),
             'mouth_left': (296, 143),
             'mouth_right': (313, 141)}
         }
        ]
with open('data2.csv', 'w') as f:
    # get a list of the keys in the values of 'keypoints' key
    kps = data[0].get('keypoints').keys()
    # make the list for the fieldnames, * just expands a list to its components
    fields = ['box', 'confidence', *kps]
    w = csv.DictWriter(f, fieldnames=fields)
    w.writeheader()
    out_dict = {}

    # Loop over however many data dictionaries are in the list data
    for data_dict in data:
        # Loop over keys and values in the dictionary 
        for k, v in data_dict.items():
            if k != 'keypoints':
                out_dict.update({k: v})
            elif k == 'keypoints':
                # Loop over the keys that correspond to 'keypoints' key
                for k2, v2 in data_dict['keypoints'].items():
                    out_dict.update({k2: v2})
        # Write the created out dictionary to the file
        w.writerow(out_dict)

# Appending to an already existing csv
with open('data2.csv', 'a') as f:
    kps = data[0].get('keypoints').keys()
    # make the list for the fieldnames, * just expands a list to its components
    fields = ['box', 'confidence', *kps]
    w = csv.DictWriter(f, fieldnames=fields)

    out_dict = {}
    # Loop over however many data dictionaries are in the list data
    for data_dict in data:
        # Loop over keys and values in the dictionary
        for k, v in data_dict.items():
            if k != 'keypoints':
                out_dict.update({k: v})
            elif k == 'keypoints':
                # Loop over the keys that correspond to 'keypoints' key
                for k2, v2 in data_dict['keypoints'].items():
                    out_dict.update({k2: v2})
        # Write the created out dictionary to the file
        w.writerow(out_dict)

With this output:

box,confidence,left_eye,right_eye,nose,mouth_left,mouth_right
"[277, 90, 48, 63]",0.99,"(291, 117)","(314, 114)","(303, 131)","(296, 143)","(313, 141)"
"[277, 90, 48, 63]",0.99,"(291, 117)","(314, 114)","(303, 131)","(296, 143)","(313, 141)"

flattened output in LibreCalc

This second function is effectively the same as flattening a json as one of the other answers mentioned, just without a dependency.

How do you append a row to the csv file using this method? The method I'm trying to use either does not work or makes another header. — rachana dugana, Apr 01 '19 at 09:12
When you open the file using the `with` keyword instead of the second parameter being `'w'` use `'a'`. This appends to file rather than overwriting it. In this case you would omit the call to `w.writeheader()`. I'll add in the code in an edit. — R. Arctor, Apr 01 '19 at 16:59
See this answer for more explanation about appending to a file. [How do you append to a file in Python?](https://stackoverflow.com/questions/4706499/how-do-you-append-to-a-file-in-python) — R. Arctor, Apr 01 '19 at 17:09

score 0 · Answer 5 · answered Apr 01 '19 at 04:15

If you don't mind using a package for it, you can install the flatten_json package from pypi, usage found here: https://github.com/amirziai/flatten#usage

If so, then your code becomes something like this:

import csv
import flatten_json

result = [
    {
        'box': [277, 90, 48, 63],
        'confidence': 0.99,
        'keypoints': {
            'left_eye': (291, 117),
            'right_eye': (314, 114),
            'nose': (303, 131),
            'mouth_left': (296, 143),
            'mouth_right': (313, 141)}
    }
]

flattened_result = flatten_json.flatten(result[0])
with open('peak.csv', 'w') as csvFile:
    fields = list(flattened_result.keys())
    writer = csv.DictWriter(csvFile, fieldnames=fields)
    writer.writeheader()
    writer.writerow(flattened_result)

print("writing completed")

Output looks like:

box_0,box_1,box_2,box_3,confidence,keypoints_left_eye,keypoints_right_eye,keypoints_nose,keypoints_mouth_left,keypoints_mouth_right

277,90,48,63,0.99,"(291, 117)","(314, 114)","(303, 131)","(296, 143)","(313, 141)"

I should mention it doesn't expand tuples in the correct list form as of version 0.1.6. I have a pull request submitted to expand them in the same manner as lists for (hopefully) version 0.1.7. You can feel free to use the code as it's distributed under the MIT License https://github.com/amirziai/flatten/blob/master/LICENSE — Will T, Apr 01 '19 at 05:24

How do I write this piece of data to a csv file?

5 Answers5

Linked