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
– Ian Pennebaker, Commented Nov 5, 2018 at 19:54

amarynets · Accepted Answer · 2018-11-05 19:57:40Z

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 5, 2018 at 19:57

amarynets

1,82511 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jean-François Fabre Over a year ago

careful when opening csv files for writing under windows with infamous blank lines (stackoverflow.com/questions/38808284/…)

Ian Pennebaker Over a year ago

Lol! Thank you for this!

Ian Pennebaker · Accepted Answer · 2018-11-05 20:12:32Z

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 5, 2018 at 20:12

Ian Pennebaker

2454 silver badges15 bronze badges

Collectives™ on Stack Overflow

Python - Use key from nested csv as main key

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related