Python - Use key from nested csv as main key

Question

Apologies for the poorly worded title.

Basically, I have a csv file where rows look like this:

1234567, Install X Software on Y Machine, ServiceTicket,"{'id': 47, 'name': 'SERVICE BOARD', '_info': {'board_href': 'https://MY-URl'}}","{'id': 897, 'name': 'Completed', '_info': {'status_href': 'https://MY-URl'}}...

The first nested list's key name is 'board' and the second is 'status'. I am trying to format this such that the nested 'name' key is used in place of 'board' and 'status.' I would like it to be formatted like this:

1234567, Install X Software on Y Machine, ServiceTicket, SERVICE BOARD, Completed

I am completely lost on this. Any help would be greatly appreciated.

The following is my code for retrieving .json data from a get request and converting it to .csv:

data = requests.get(url=TICKET_URL, headers=HEADER_AUTH) ### Get data from
data = data.json()
count = 0;
csvData = open("test.csv", "w")
csvWriter = csv.writer(csvData)
with open("data.json", "w") as f:
        for item in data:
            if count == 0:
                header = item.keys()
                csvWriter.writerow(header)
                count += 1
            csvWriter.writerow(item.values())
            f.write("%s\n" % item)
csvData.close()
f.close()

Which data? I have in here how a row in the csv file looks like, and there are hundreds of rows. — Ian Pennebaker, Nov 05 '18 at 19:54

score 1 · Answer 1 · answered Nov 05 '18 at 19:57

1

It isn't so good solution, but should works. Also, you can look at itemgeter

with open('data.csv', 'w') as f:
    writer = csv.DictWriter(f, fieldnames=('foo1', 'foo2'))
    writer.writeheader()
    for i in data:
        record = []
        for item in i.values():
            if isinstance(item, dict):
                record.append(item['name'])
            else:
                record.append(item)
        writer.writerow(record)

answered Nov 05 '18 at 19:57

amarynets

1,765
10
27

careful when opening csv files for writing under windows with infamous blank lines (https://stackoverflow.com/questions/38808284/portable-way-to-write-csv-file-in-python-2-or-python-3) – Jean-François Fabre Nov 05 '18 at 20:03
Lol! Thank you for this! – Ian Pennebaker Nov 05 '18 at 20:07

score 0 · Answer 2 · answered Nov 05 '18 at 20:12

0

I achieved the desired format by flattening the json file prior to converting it to csv and then deleting unwanted keys. Not an ideal answer but it works.

answered Nov 05 '18 at 20:12

Ian Pennebaker

233
4
15

Python - Use key from nested csv as main key

2 Answers2