Save Json data to csv file

Question

r3 = requests.get('www.website.com/api', params=headers)

struct = r3.json()

for k,v in struct.items():
    print (str(k) + str(v))

output :

FoundCategories[]
PageSize1
Page1
List[{'ExteriorColour': None, 'CategoryPath': '/Trade-Me-Motors/Cars/Bentley', 'Subtitle': 'sdf', 'BestContactTime': None, 'StartPrice': 100.0, 'Doors': 0, 'Fuel': None, 'BodyStyle': 'Coupe', 'WofExpires': '/Date(0)/', 'NumberPlate': None, 'ImportHistory': None, 'Transmission': 'Manual', 'EngineSize': 0, 'ListingLength': None, 'StereoDescription': None, 'Category': '0001-0268-7081-', 'Title': 'Bentley Continental 1999', 'Owners': 0, 'IsDealer': False, 'Cylinders': 0, 'AsAt': '/Date(1457728757951)/', 'Odometer': 2000, 'Vin': None, 'Year': 1999, 'StartDate': '/Date(1457326119847)/', 'Region': 'Manawatu', 'Model': 'Continental', 'PriceDisplay': '$100.00', 'Suburb': 'Palmerston North', 'EndDate': '/Date(1457930919847)/', 'RegistrationExpires': '/Date(0)/', 'NoteDate': '/Date(0)/', 'ListingId': 4550689, 'Make': 'Bentley'}]
TotalCount1

How do i extract the say, odometer reading from the entry and add it to a csv file

Expected output screenshot:

struct = r3.json()

print(struct)

{'TotalCount': 1, 'PageSize': 1, 'FoundCategories': [], 'Page': 1, 'List': [{'AsAt': '/Date(1457733660023)/', 'Model': 'Continental', 'Suburb': 'Palmerston North', 'NoteDate': '/Date(0)/', 'PriceDisplay': '$100.00', 'EndDate': '/Date(1457930919847)/', 'RegistrationExpires': '/Date(0)/', 'StartPrice': 100.0, 'Owners': 0, 'ListingLength': None, 'CategoryPath': '/Trade-Me-Motors/Cars/Bentley', 'ListingId': 4550689, 'Subtitle': 'sdf', 'Category': '0001-0268-7081-', 'StartDate': '/Date(1457326119847)/', 'Year': 1999, 'WofExpires': '/Date(0)/', 'ExteriorColour': None, 'Vin': None, 'EngineSize': 0, 'Doors': 0, 'BodyStyle': 'Coupe', 'Title': 'Bentley Continental 1999', 'IsDealer': False, 'Make': 'Bentley', 'Transmission': 'Manual', 'Fuel': None, 'ImportHistory': None, 'Odometer': 2000, 'StereoDescription': None, 'Region': 'Manawatu', 'BestContactTime': None, 'Cylinders': 0, 'NumberPlate': None}]}

Are you looking to only have those headers? Or are you looking to make everything from your json in there where the keys are the headers with their corresponding value? — idjaw, Mar 11 '16 at 20:49
@idjaw oh it'll have all of them, sorry, was just showing a small extract of what i need — musss, Mar 11 '16 at 20:51
read [this](http://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv-with-python). Does this solve your problem? — idjaw, Mar 11 '16 at 20:52
@idjaw f.writerow(["ExteriorColour", "BestContactTime", "StartPrice", "Doors", "Fuel"]) TypeError: a bytes-like object is required, not 'str' — musss, Mar 11 '16 at 21:03

idjaw · Accepted Answer · 2016-03-11T22:12:51.517

1

I will leave the data sanitization to you, however, this overall approach should help you with what you are trying to achieve.

The first thing, you seem to only want what is in the List key. So in your data structure that you are receiving, simply get the data inside the List key:

car_data = struct.get('List')

Now that you have your data from List, and assuming that every dictionary entry in that list has the same 'keys', simply do a car_data[0].keys(), for your fieldnames.

fieldnames=car_data[0].keys()

Then, simply set up your DictWriter with those fieldnames and below is pretty self explanatory:

struct = {'TotalCount': 1, 'PageSize': 1, 'FoundCategories': [], 'Page': 1, 'List': [{'AsAt': '/Date(1457733660023)/', 'Model': 'Continental', 'Suburb': 'Palmerston North', 'NoteDate': '/Date(0)/', 'PriceDisplay': '$100.00', 'EndDate': '/Date(1457930919847)/', 'RegistrationExpires': '/Date(0)/', 'StartPrice': 100.0, 'Owners': 0, 'ListingLength': None, 'CategoryPath': '/Trade-Me-Motors/Cars/Bentley', 'ListingId': 4550689, 'Subtitle': 'sdf', 'Category': '0001-0268-7081-', 'StartDate': '/Date(1457326119847)/', 'Year': 1999, 'WofExpires': '/Date(0)/', 'ExteriorColour': None, 'Vin': None, 'EngineSize': 0, 'Doors': 0, 'BodyStyle': 'Coupe', 'Title': 'Bentley Continental 1999', 'IsDealer': False, 'Make': 'Bentley', 'Transmission': 'Manual', 'Fuel': None, 'ImportHistory': None, 'Odometer': 2000, 'StereoDescription': None, 'Region': 'Manawatu', 'BestContactTime': None, 'Cylinders': 0, 'NumberPlate': None}]}



car_info = struct.get('List')
with open('car_info.csv', 'w') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=car_info[0].keys())

    writer.writeheader()
    for row in car_info:
        writer.writerow(row)

edited Mar 11 '16 at 22:12

answered Mar 11 '16 at 21:05

idjaw

25,487
7
64
83

Hey, i get this error when i try that: writer = csv.DictWriter(csvfile, fieldnames=struct[0].keys()) KeyError: 0 – musss Mar 11 '16 at 21:13
@musss I assumed your data structure was `[{}]`. If it is simply a dictionary can you try: `struct.keys()`. Does that work? – idjaw Mar 11 '16 at 21:14
x = struct.keys() gives me dict_keys(['PageSize', 'TotalCount', 'Page', 'FoundCategories', 'List']) – musss Mar 11 '16 at 21:44
OK. I know what is going on. You only want what is in the `List` key. Let me update my answer – idjaw Mar 11 '16 at 21:51
@musss Ok. I updated my answer. Can you confirm that you only want the data from `List` or are you looking for all the data in the entire structure including what is in `List`. – idjaw Mar 11 '16 at 21:53
I get : AttributeError: 'Response' object has no attribute 'get' – musss Mar 11 '16 at 21:58
OK. At this point you need to update your question what what your data looks like at this line `struct = r3.json()`. Give the output of `struct` please. The full output. – idjaw Mar 11 '16 at 21:59
@musss please put this in your original question. And delete from these comments. – idjaw Mar 11 '16 at 22:03
posted up in original – musss Mar 11 '16 at 22:06
Ok. Great. Now Can you confirm what exactly you want to put in excel? Is it that entire structure, or only the value of 'List':? From your original question it seems like you only want that part. – idjaw Mar 11 '16 at 22:08
I need what's in list. i.e the info about the listing – musss Mar 11 '16 at 22:09
OK. @musss This has to work now that I have the right data structure. I tested this code and it works. Answer updated – idjaw Mar 11 '16 at 22:13

Save Json data to csv file

1 Answers1