代码之家  ›  专栏  ›  技术社区  ›  HJA24

迭代JSON并转换为CSV

parsing csv json python
  •  0
  • HJA24  · 技术社区  · 6 年前

    我有以下JSON对象: 

    {
  "h": [
    {
      "id": "242611",
      "minute": "2",
      "result": "MissedShots",
      "X": "0.9359999847412109",
      "Y": "0.534000015258789",
      "xG": "0.1072189137339592",
      "player": "Ari",
      "h_a": "h",
      "player_id": "2930",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "Head",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Wanderson",
      "lastAction": "Chipped"
    },
    {
      "id": "242612",
      "minute": "4",
      "result": "SavedShot",
      "X": "0.8059999847412109",
      "Y": "0.7069999694824218",
      "xG": "0.021672379225492477",
      "player": "Cristian Ram\u00edrez",
      "h_a": "h",
      "player_id": "5477",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "LeftFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "None"
    },
    {
      "id": "242613",
      "minute": "4",
      "result": "SavedShot",
      "X": "0.7780000305175782",
      "Y": "0.505",
      "xG": "0.023817993700504303",
      "player": "Mauricio Pereyra",
      "h_a": "h",
      "player_id": "2922",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Viktor Claesson",
      "lastAction": "Pass"
    },
    {
      "id": "242614",
      "minute": "17",
      "result": "MissedShots",
      "X": "0.9330000305175781",
      "Y": "0.41",
      "xG": "0.01863950863480568",
      "player": "Ari",
      "h_a": "h",
      "player_id": "2930",
      "situation": "FromCorner",
      "season": "2018",
      "shotType": "Head",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Mauricio Pereyra",
      "lastAction": "Aerial"
    },
    {
      "id": "242617",
      "minute": "21",
      "result": "SavedShot",
      "X": "0.710999984741211",
      "Y": "0.534000015258789",
      "xG": "0.015956614166498184",
      "player": "Ivan Ignatyev",
      "h_a": "h",
      "player_id": "6025",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Ari",
      "lastAction": "Pass"
    },
    {
      "id": "242621",
      "minute": "31",
      "result": "MissedShots",
      "X": "0.7959999847412109",
      "Y": "0.4640000152587891",
      "xG": "0.03898102045059204",
      "player": "Viktor Claesson",
      "h_a": "h",
      "player_id": "5478",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "None"
    },
    {
      "id": "242622",
      "minute": "36",
      "result": "MissedShots",
      "X": "0.759000015258789",
      "Y": "0.3509999847412109",
      "xG": "0.05237437039613724",
      "player": "Mauricio Pereyra",
      "h_a": "h",
      "player_id": "2922",
      "situation": "DirectFreekick",
      "season": "2018",
      "shotType": "LeftFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "Standard"
    },
    {
      "id": "242624",
      "minute": "42",
      "result": "BlockedShot",
      "X": "0.919000015258789",
      "Y": "0.37",
      "xG": "0.10843519121408463",
      "player": "Sergei Petrov",
      "h_a": "h",
      "player_id": "2920",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Viktor Claesson",
      "lastAction": "Pass"
    },
    {
      "id": "242625",
      "minute": "48",
      "result": "MissedShots",
      "X": "0.7719999694824219",
      "Y": "0.385",
      "xG": "0.023656079545617104",
      "player": "Aleksandr Martynovich",
      "h_a": "h",
      "player_id": "2790",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Yuri Gazinskiy",
      "lastAction": "Pass"
    },
    {
      "id": "242626",
      "minute": "49",
      "result": "MissedShots",
      "X": "0.715999984741211",
      "Y": "0.4879999923706055",
      "xG": "0.013118931092321873",
      "player": "Yuri Gazinskiy",
      "h_a": "h",
      "player_id": "2929",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Viktor Claesson",
      "lastAction": "Pass"
    },
    {
      "id": "242627",
      "minute": "54",
      "result": "BlockedShot",
      "X": "0.909000015258789",
      "Y": "0.3529999923706055",
      "xG": "0.09400425851345062",
      "player": "Magomed-Shapi Suleymanov",
      "h_a": "h",
      "player_id": "5926",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Viktor Claesson",
      "lastAction": "Pass"
    },
    {
      "id": "242628",
      "minute": "54",
      "result": "BlockedShot",
      "X": "0.8859999847412109",
      "Y": "0.31799999237060544",
      "xG": "0.061035316437482834",
      "player": "Magomed-Shapi Suleymanov",
      "h_a": "h",
      "player_id": "5926",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "LeftFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Yuri Gazinskiy",
      "lastAction": "Pass"
    },
    {
      "id": "242629",
      "minute": "55",
      "result": "Goal",
      "X": "0.9269999694824219",
      "Y": "0.46",
      "xG": "0.523554801940918",
      "player": "Magomed-Shapi Suleymanov",
      "h_a": "h",
      "player_id": "5926",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Viktor Claesson",
      "lastAction": "Pass"
    },
    {
      "id": "242630",
      "minute": "66",
      "result": "MissedShots",
      "X": "0.915",
      "Y": "0.5420000076293945",
      "xG": "0.3631550371646881",
      "player": "Christian Cueva",
      "h_a": "h",
      "player_id": "6799",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Cristian Ram\u00edrez",
      "lastAction": "Cross"
    },
    {
      "id": "242631",
      "minute": "71",
      "result": "BlockedShot",
      "X": "0.685",
      "Y": "0.485",
      "xG": "0.03188558667898178",
      "player": "Cristian Ram\u00edrez",
      "h_a": "h",
      "player_id": "5477",
      "situation": "DirectFreekick",
      "season": "2018",
      "shotType": "LeftFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "Standard"
    },
    {
      "id": "242632",
      "minute": "72",
      "result": "BlockedShot",
      "X": "0.8909999847412109",
      "Y": "0.4809999847412109",
      "xG": "0.09532035887241364",
      "player": "Sergei Petrov",
      "h_a": "h",
      "player_id": "2920",
      "situation": "FromCorner",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "None"
    },
    {
      "id": "242634",
      "minute": "75",
      "result": "Goal",
      "X": "0.794000015258789",
      "Y": "0.47900001525878905",
      "xG": "0.05203503370285034",
      "player": "Ivan Ignatyev",
      "h_a": "h",
      "player_id": "6025",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "None"
    },
    {
      "id": "242640",
      "minute": "87",
      "result": "BlockedShot",
      "X": "0.795",
      "Y": "0.7330000305175781",
      "xG": "0.01853240840137005",
      "player": "Christian Cueva",
      "h_a": "h",
      "player_id": "6799",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Sergei Petrov",
      "lastAction": "Pass"
    }
  ],
  "a": [
    {
      "id": "242615",
      "minute": "18",
      "result": "SavedShot",
      "X": "0.89",
      "Y": "0.4279999923706055",
      "xG": "0.3485192060470581",
      "player": "Andrei Panyukov",
      "h_a": "a",
      "player_id": "6138",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Nikolay Dimitrov",
      "lastAction": "Pass"
    },
    {
      "id": "242616",
      "minute": "18",
      "result": "MissedShots",
      "X": "0.8719999694824219",
      "Y": "0.4",
      "xG": "0.30197691917419434",
      "player": "Nikolay Dimitrov",
      "h_a": "a",
      "player_id": "5496",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "Rebound"
    },
    {
      "id": "242636",
      "minute": "79",
      "result": "BlockedShot",
      "X": "0.8530000305175781",
      "Y": "0.3920000076293945",
      "xG": "0.042678408324718475",
      "player": "Yuri Bavin",
      "h_a": "a",
      "player_id": "3085",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "None"
    },
    {
      "id": "242641",
      "minute": "92",
      "result": "MissedShots",
      "X": "0.705999984741211",
      "Y": "0.534000015258789",
      "xG": "0.01331254467368126",
      "player": "Eric Bicfalvi",
      "h_a": "a",
      "player_id": "5483",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "RightFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Andrey Egorychev",
      "lastAction": "Pass"
    }
  ]
}

    我想要的输出如下所示:

    enter image description here 

    def scrap_understat():
    csv_file_path = currentDir + '/output_shots' + current_time + '.csv'
    file = None
    writer = None

    file = io.open(csv_file_path, 'w', newline='', encoding='ISO-8859-1')
    writer = csv.writer(file)
    writer.writerow(
        ['ID', 'Result', 'X', 'Y', 'xG', 'Player', 'Home/Away', 'Player_ID', 'Situation', 'Season', 'Shot_Type',
         'Match_ID', 'xG', 'Home_Team', 'Away_Team', 'Home_Goals', 'Away_Goals', 'Date', 'Player_Assisted', 'Last_Action'])

    for i in range(10096, 10097):
       try:
           driver.get('https://understat.com/match/' + str(i))
           time.sleep(1)
           if try_find_Element(driver, By.CLASS_NAME, 'error-code') is not None:
               continue
           shots = driver.execute_script("return shotsData")
           for v in shots.items():
               writer.writerow(v)

    变量 镜头 和 . 但是,我的脚本提供以下输出:

    enter image description here

    我做错什么了?是否也可以指定我要在CSV中包含哪些特定项?

    2 回复  |  直到 6 年前
        1
  •  1
  •   roganjosh    6 年前

    如果您不想将整个JSON响应写入文件,那么应该通过键访问这些值。使用 json.loads() 

    import csv
import json

string = """
{
  "h": [
    {
      "id": "242611",
      "minute": "2",
      "result": "MissedShots",
      "X": "0.9359999847412109",
      "Y": "0.534000015258789",
      "xG": "0.1072189137339592",
      "player": "Ari",
      "h_a": "h",
      "player_id": "2930",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "Head",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": "Wanderson",
      "lastAction": "Chipped"
    }],
  "c": [
    {
      "id": "242612",
      "minute": "4",
      "result": "SavedShot",
      "X": "0.8059999847412109",
      "Y": "0.7069999694824218",
      "xG": "0.021672379225492477",
      "player": "Cristian Ram\u00edrez",
      "h_a": "h",
      "player_id": "5477",
      "situation": "OpenPlay",
      "season": "2018",
      "shotType": "LeftFoot",
      "match_id": "9071",
      "h_team": "FC Krasnodar",
      "a_team": "Ural",
      "h_goals": "2",
      "a_goals": "0",
      "date": "2018-12-02 11:00:00",
      "player_assisted": null,
      "lastAction": "None"
    }
  ]
}
"""

response = json.loads(string)

field_names = response['h'][0].keys() # I'm assuming keys are consistent between h and c for brevity

with open('my_output.csv', 'w', newline='') as outfile:
    writer = csv.DictWriter(outfile, fieldnames=field_names)
    for key, values in response.items():
        writer.writerows(values)

    在这种情况下,我使用了 DictWriter 类 csv 模块来处理列的排序方式。我们需要传递一个参数, fieldnames response['h'][0].keys() ,但您可以自由地传递关键字名称列表。它充当所有行的模板。在Python<3.6版本中,不能保证这种顺序,并且可能在代码运行之间(以及在3.6中)发生变化。它在技术上没有保证,但有一个实现细节)。

        2
  •  0
  •   iGian    6 年前

    data : 

    data = {
  "h": [
    {
      "id": "242611",
      "minute": "2",
      "result": "MissedShots",
      "X": "0.9359999847412109",
      ....


    for items in data.values():
  for item in items:
    writer.writerow(item.values())
    推荐文章
    Sweepy Dodo  ·  JSON lite的格式化
    9 月前
    SebMa  ·  为什么我用mikefarah/yq得到两倍的JSON输出?
    10 月前
    Dione Saturn  ·  JSON、fetch、HTML中的javascript和未定义或空字符串[重复]
    10 月前
    user24242514  ·  将嵌套查询字符串请求转换为字典
    10 月前
    Shane Harley  ·  通过这些JSON元素进行映射
    10 月前
    Milenko Markovic  ·  如何使用Python读取JSON?操作系统错误:[Errno 36]文件名太长:
    10 月前
    Shane Harley  ·  将HTML作为JSON字符串的一部分?
    10 月前
    user6631314  ·  使用PHP从api响应中提取视频数据[复制]
    10 月前
    Matt Miles  ·  如何将嵌套的JSON转换为数据帧?
    10 月前
    ololo  ·  不支持目录导入
    10 月前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号