使用Python 3将JSON转换为CSV [英] Convert JSON to CSV with Python 3

问题描述

我需要从Meetup API中获取一些数据，将获取的JSON转换为CSV，所有这些都需要在Python 3中进行.我从未使用过JSON或Python，因此遇到了一些问题.获取数据不是问题，但是我似乎无法将JSON对象解析为适当的CSV文件.我的代码已匿名:

I need to get some data from the Meetup API, convert the JSON I get into a CSV, all of that in Python 3. I've never worked with JSON or Python, so I've run into some issues. Getting the data is not a problem, but I can't seem to parse the JSON object into a proper CSV file. My code, anonymized:

import json
import requests
import csv


def main():
    # initialize variables
    output_file = 'result_meetup.csv'
    api_key = "YOUR_MEETUP_API_KEY"
    params = {'key': api_key}
    url_path = 'http://api.meetup.com/pro/:URLNAME/groups'

    # get data from API, parse to JSON
    data = requests.get(url_path, params=params)
    data_parsed = data.json()
    length_data = len(data_parsed) - 1

    data_to_file = open(output_file, 'w')
    csv_writer = csv.writer(data_to_file)

    for i in range(0, length_data):
        meetup = data_parsed[i]
        csv_writer.writerow([meetup])
    data_to_file.close()


if __name__ == "__main__":
    main()

现在，如您所见，我将CSV作为列表写入.这样会导致输出看起来非常丑陋(再次，大写字母中的值被匿名化了):

Now, as you can see, I write into the CSV as a list. This leads to a really ugly output, looking like this (again, the values in caps are anonymized):

{u'rsvps_per_event': 0.0, u'organizers': [{u'permission': u'organizer', u'name': u'NAME', u'member_id': ID}], u'repeat_rsvpers': 0, u'topics': [{u'lang': u'en_US', u'id': ID, u'urlkey': u'socialnetwork', u'name': u'Social Networking'}, {u'lang': u'en_US', u'id': ID, u'urlkey': u'education', u'name': u'Education'}, {u'lang': u'en_US', u'id': ID, u'urlkey': u'newtech', u'name': u'New Technology'}, {u'lang': u'en_US', u'id': ID, u'urlkey': u'business-referral-networking', u'name': u'Business Referral Networking'}], u'upcoming_events': 0, u'gender_female': 0.3499999940395355, u'pro_join_date': DATE, u'id': ID, u'category': [{u'shortname': u'career-business', u'sort_name': u'Career & Business', u'id': 2, u'name': u'Career & Business'}], u'city': u'CITY', u'member_count': 73, u'lon': LON, u'organizer_photo': {u'thumb_link': u'LINK.JPEG', u'base_url': u'URL', u'id': ID, u'type': u'member', u'photo_link': u'LINK.JPEG', u'highres_link': u'LINK.JPEG'}, u'average_age': 35.555599212646484, u'status': u'Active', u'description': u'DESCRIPTION' u'founded_date': DATE, lat': LAT, u'urlname': u'NAME', u'gender_male': 0.6000000238418579, u'name': u'NAME', u'country': u'Portugal', u'gender_unknown': 0.05000000074505806, u'past_events': 0, u'gender_other': 0.0, u'past_rsvps': 0}

因此，基本上，整个JSON对象位于单个CSV字段中，列表中带有奇怪的"u"，依此类推.但是，如果我不将其写为列表，那么我只会得到JSON对象的字段，而没有数据，因此它只是组织者"，名称"等，而没有实际名称. 就像我说的那样，我是python的新手，还没有找到任何可以帮助我的库，但是我确定它们存在.我们非常感谢您提供的任何帮助，如果它与Python3兼容，那就太好了.

So basically, the whole JSON object in a single CSV field, with weird 'u's, in lists and so on. However, if I don't write it as a list, I only get the fields of the JSON object, without the data, so it would just be 'Organizers', 'Name' etc, without the actual name. As I said, I am a python beginner and haven't found any libraries to help me with it, but I'm sure they exist. Any help is really appreciated, and it would be great if it was Python3 compatible.

我希望它最终看起来像什么: 我在回复中得到了多个Meetup组，它们的结构都与上面所示的相同.因此，该值的描述应仅作为标题列出一次，并在下面列出这些值(新行和竖线表示csv工作表的新字段):

What I would like it to look like in the end: I get multiple Meetup groups in the reply, all having the same structure as shown above. Therefore, the description of the value should be listed just once, as a header, and the values listed beneath (new lines and pipes indicating a new field of a csv sheet):

RSVPs_per_event | Organizer | ID 
5               | Tom       | 1
20              | Jack      | 2
35              | Anne      | 3

推荐答案

如果其他人也遇到同样的问题，我虽然不是很优雅地解决了问题，但是我需要数据. JSON过于嵌套且难以解析，因此现在我只需从数据中读取所需的字段即可.

If anyone else has the same problem, I solved it, not very elegantly, but I needed the data. The JSON was too nestled and complex to parse, so now I just read the fields I need from the data.

import json
import requests
import csv


def main():
    # initialize variables
    output_file = 'result_meetup.csv'
    api_key = "YOUR_API_KEY"
    params = {'key': api_key}
    url_path = 'http://api.meetup.com/pro/:URLNAME/groups'

    # get data from API, parse to JSON
    data = requests.get(url_path, params=params)
    data_parsed = data.json()
    length_data = len(data_parsed) - 1

    data_to_file = open(output_file, 'w', newline='')
    csv_writer = csv.writer(data_to_file, delimiter=";")
    csv_writer.writerow(["id","name","city","country","member count","average age","founded_date","past_rsvps","rsvps_per_event","repeat_rsvpers","gender_unknown","gender_female","gender_male","gender_other"])

    for i in range(0, length_data):
        meetup = data_parsed[i]
        id = meetup['id']
        name = meetup['name']
        city = meetup['city']
        country = meetup['country']
        member_count = meetup['member_count']
        average_age = meetup['average_age']
        founded_date = meetup['founded_date']
        past_rsvps = meetup['past_rsvps']
        rsvps_per_event = meetup['rsvps_per_event']
        repeat_rsvpers = meetup['repeat_rsvpers']
        gender_unknown = meetup['gender_unknown']
        gender_female = meetup['gender_female']
        gender_male = meetup['gender_male']
        gender_other = meetup['gender_other']
        csv_writer.writerow([id,name,city,country,member_count,average_age,founded_date,past_rsvps,rsvps_per_event,repeat_rsvpers,gender_unknown,gender_female,gender_male,gender_other])
    data_to_file.close()


if __name__ == "__main__":
    main()

这篇关于使用Python 3将JSON转换为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！