如何将更复杂的 JSON 转换为 CSV?

杰瑞

我有更复杂的 JSON,我正在尝试将其转换为 CSV。

我从这里和其他页面尝试了几个例子,但我无法弄清楚一些细节。我尝试使用熊猫,但我不确定如何处理嵌套列表。

以下示例是 Zendesk 的一张票。我将有一个文件,其中每一行都是一张票。

我想获取 CSV 中每张票的字段,其中每一行代表该字段的值。基本扁平结构。例子:

id、via、创建于、主题、描述、自定义字段 id1、自定义字段 id2、请求者等。

JSON

[{
    "url": "https://test.zendesk.com/api/v2/tickets/6087.json",
    "id": 6087,
    "external_id": null,
    "via": {
        "channel": "web",
        "source": {
            "from": {},
            "to": {},
            "rel": null
        },
        "id": 0
    },
    "created_at": "2019-09-11T01:15:17.000Z",
    "updated_at": "2020-12-01T08:56:29.000Z",
    "type": null,
    "subject": "test ticket",
    "raw_subject": "test ticket",
    "description": "Test description:\n\n1. Trouble\n2. Trouble",
    "priority": "low",
    "status": "closed",
    "follower_ids": [],
    "email_cc_ids": [],
    "forum_topic_id": null,
    "problem_id": null,
    "has_incidents": false,
    "is_public": true,
    "due_at": null,
    "tags": [
        "1._pending_pm_review",
        "70_severity_4",
        "8._tested_and_closed_in_release",
        "bug",
        "deal_blocker",
        "denise",
        "escalated-engineering-yes",
        "escalated_github",
        "log_sev4",
        "rt_technical_issues_and_bugs"
    ],
    "custom_fields": [
        {
            "id": 360015283851,
            "value": "70_severity_4"
        },
        {
            "id": 360016758872,
            "value": null
        }
    ],
    "satisfaction_rating": {
        "score": "unoffered"
    },
    "sharing_agreement_ids": [],
    "fields": [
        {
            "id": 360015283851,
            "value": "70_severity_4"
        },
        {
            "id": 360016758872,
            "value": null
        }
    ],
    "followup_ids": [],
    "ticket_form_id": 360000474971,
    "brand_id": 1685747,
    "satisfaction_probability": null,
    "metric_set": {
        "url": "https://test.zendesk.com/api/v2/ticket_metrics/371633606312.json",
        "id": 371633606312,
        "ticket_id": 6087,
        "created_at": "2019-09-11T01:15:18.000Z",
        "updated_at": "2020-11-02T09:35:19.000Z",
        "group_stations": 1,
        "assignee_stations": 2,
        "reopens": 0,
        "replies": 1,
        "assignee_updated_at": null,
        "requester_updated_at": "2019-09-11T01:15:17.000Z",
        "status_updated_at": "2020-12-01T08:56:29.000Z",
        "initially_assigned_at": "2019-09-11T03:12:26.000Z",
        "assigned_at": "2020-03-24T04:51:38.000Z",
        "solved_at": "2020-11-02T09:35:18.000Z",
        "latest_comment_added_at": "2020-11-02T09:35:18.000Z",
        "reply_time_in_minutes": {
            "calendar": 602420,
            "business": 142560
        },
        "first_resolution_time_in_minutes": {
            "calendar": 602420,
            "business": 142560
        },
        "full_resolution_time_in_minutes": {
            "calendar": 602420,
            "business": 142560
        },
        "agent_wait_time_in_minutes": {
            "calendar": 0,
            "business": 0
        },
        "requester_wait_time_in_minutes": {
            "calendar": 602420,
            "business": 142560
        },
        "on_hold_time_in_minutes": {
            "calendar": 602420,
            "business": 142560
        }
    },
    "dates": {
        "assignee_updated_at": null,
        "requester_updated_at": "2019-09-11T01:15:17.000Z",
        "status_updated_at": "2020-12-01T08:56:29.000Z",
        "initially_assigned_at": "2019-09-11T03:12:26.000Z",
        "assigned_at": "2020-03-24T04:51:38.000Z",
        "solved_at": "2020-11-02T09:35:18.000Z",
        "latest_comment_added_at": "2020-11-02T09:35:18.000Z"
    },
    "allow_channelback": false,
    "allow_attachments": true,
    "generated_timestamp": 1606812989,
    "submitter": {
        "id": 9799529247,
        "url": "https://test.zendesk.com/api/v2/users/9799529247.json",
        "name": "Jan",
        "email": "[email protected]",
        "created_at": "2016-08-24T21:32:49.000Z",
        "updated_at": "2020-12-16T12:16:52.000Z",
        "time_zone": "Berlin",
        "iana_time_zone": "Europe/Berlin",
        "phone": null,
        "shared_phone_number": null,
        "photo": null,
        "locale_id": 1,
        "locale": "en-US",
        "organization_id": 360520516831,
        "role": "agent",
        "verified": true,
        "external_id": null,
        "tags": [],
        "alias": "",
        "active": true,
        "shared": false,
        "shared_agent": false,
        "last_login_at": "2020-12-16T12:16:15.000Z",
        "two_factor_auth_enabled": null,
        "signature": "",
        "details": "",
        "notes": "",
        "role_type": 1,
        "custom_role_id": 360007006111,
        "moderator": false,
        "ticket_restriction": null,
        "only_private_comments": false,
        "restricted_agent": false,
        "suspended": false,
        "chat_only": false,
        "default_group_id": 360005461052,
        "report_csv": false,
        "user_fields": {
            "agent_ooo": false,
            "country_user": null,
            "exclude_from_email_campaigns": false,
            "slack_member_id": null
        }
    },
    "requester": {
        "id": 9799529247,
        "url": "https://test.zendesk.com/api/v2/users/9799529247.json",
        "name": "Jan",
        "email": "[email protected]",
        "created_at": "2016-08-24T21:32:49.000Z",
        "updated_at": "2020-12-16T12:16:52.000Z",
        "time_zone": "Berlin",
        "iana_time_zone": "Europe/Berlin",
        "phone": null,
        "shared_phone_number": null,
        "photo": null,
        "locale_id": 1,
        "locale": "en-US",
        "organization_id": 360520516831,
        "role": "agent",
        "verified": true,
        "external_id": null,
        "tags": [],
        "alias": "",
        "active": true,
        "shared": false,
        "shared_agent": false,
        "last_login_at": "2020-12-16T12:16:15.000Z",
        "two_factor_auth_enabled": null,
        "signature": "",
        "details": "",
        "notes": "",
        "role_type": 1,
        "custom_role_id": 360007006111,
        "moderator": false,
        "ticket_restriction": null,
        "only_private_comments": false,
        "restricted_agent": false,
        "suspended": false,
        "chat_only": false,
        "default_group_id": 360005461052,
        "report_csv": false,
        "user_fields": {
            "agent_ooo": false,
            "country_user": null,
            "exclude_from_email_campaigns": false,
            "slack_member_id": null
        }
    },
    "assignee": {
        "id": 11435953547,
        "url": "https://test.zendesk.com/api/v2/users/11435953547.json",
        "name": "Denise",
        "email": "[email protected]",
        "created_at": "2016-09-15T20:44:22.000Z",
        "updated_at": "2020-12-17T01:42:51.000Z",
        "time_zone": "Pacific Time (US & Canada)",
        "iana_time_zone": "America/Los_Angeles",
        "phone": null,
        "shared_phone_number": null,
        "photo": null,
        "locale_id": 1,
        "locale": "en-US",
        "organization_id": 360513431831,
        "role": "agent",
        "verified": true,
        "external_id": null,
        "tags": [],
        "alias": "",
        "active": true,
        "shared": false,
        "shared_agent": false,
        "last_login_at": "2020-12-16T19:58:27.000Z",
        "two_factor_auth_enabled": null,
        "signature": "",
        "details": "",
        "notes": "",
        "role_type": 0,
        "custom_role_id": 6568467,
        "moderator": false,
        "ticket_restriction": null,
        "only_private_comments": false,
        "restricted_agent": false,
        "suspended": false,
        "chat_only": false,
        "default_group_id": 360008656551,
        "report_csv": true,
        "user_fields": {
            "agent_ooo": false,
            "country_user": null,
            "exclude_from_email_campaigns": false,
            "slack_member_id": null
        }
    },
    "collaborator": [],
    "recipient": null,
    "group": {
        "url": "https://test.zendesk.com/api/v2/groups/360005709631.json",
        "id": 360005709631,
        "name": "Product",
        "description": "",
        "default": false,
        "deleted": false,
        "created_at": "2019-08-13T21:13:35.000Z",
        "updated_at": "2019-08-13T21:13:35.000Z"
    },
    "organization": {
        "url": "https://test.zendesk.com/api/v2/organizations/360520516831.json",
        "id": 360520516831,
        "name": "Rancher Field",
        "shared_tickets": false,
        "shared_comments": false,
        "external_id": null,
        "created_at": "2019-06-27T22:39:39.000Z",
        "updated_at": "2020-11-06T17:25:10.000Z",
        "domain_names": [],
        "details": "",
        "notes": "",
        "group_id": null,
        "tags": [
            "show_premium_content"
        ],
        "organization_fields": {
            "1_6_estimated_roll_off_date": null,
            "1_6_profile_notes": null,
            "1_6_version": null,
            "account_executive": null,
            "alert_customer_success_upon_new_ticket": false,
            "cc_email_address": null,
            "customer_success_manager": null,
            "frt_add_on": null,
            "geo": null,
            "grace_period_until": null,
            "industry": null,
            "maintenance_end": null,
            "maintenance_start": null,
            "onboarding_wiki_url": null,
            "orchestration": null,
            "org_type": null,
            "partner": false,
            "pes_engineer": null,
            "platinum_slack_channel_url": null,
            "purchased_qty_dse_hours": null,
            "purchased_qty_hosted_rancher": null,
            "purchased_qty_longhorn_hosts": null,
            "purchased_qty_pes_hours": null,
            "purchased_qty_rancher_hosts": null,
            "purchased_qty_rancher_servers": null,
            "purchased_quantity": null,
            "rancheros_usage": null,
            "rancher_version": null,
            "region": null,
            "salesforce_id": null,
            "service_geo": null,
            "show_premium_content": "show_premium_content",
            "slack_notification_channel": null,
            "SN_Org_Data": null,
            "special_notes": null,
            "status": null,
            "support_type": null,
            "systems_summary_reports_box_url_": null
        }
    },
    "comments": [
        {
            "id": 753998530952,
            "type": "Comment",
            "author_id": 9799529247,
            "body": "comment here",
            "html_body": "comment here",
            "plain_body": "comment here",
            "public": false,
            "attachments": [],
            "audit_id": 753998530752,
            "ticket_id": 6087,
            "via": {
                "channel": "web",
                "source": {
                    "from": {},
                    "to": {},
                    "rel": null
                }
            },
            "metadata": {
                "system": {
                    "client": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.132 Safari/537.36",
                    "ip_address": "91.64.175.117",
                    "location": "Berlin, BE, Germany",
                    "latitude": 52.5305,
                    "longitude": 13.3215
                },
                "custom": {}
            },
            "created_at": "2019-09-11T01:15:17.000Z"
        },
        {
            "id": 1263504287872,
            "type": "Comment",
            "author_id": 401999933151,
            "body": "comment here",
            "html_body": "comment here",
            "plain_body": "comment here",
            "public": true,
            "attachments": [],
            "audit_id": 1263504287832,
            "ticket_id": 6087,
            "via": {
                "channel": "web",
                "source": {
                    "from": {},
                    "to": {
                        "name": "Jan",
                        "address": "[email protected]"
                    },
                    "rel": null
                }
            },
            "metadata": {
                "system": {
                    "client": "Mozilla/5.0 (X11; Linux x86_64; rv:82.0) Gecko/20100101 Firefox/82.0",
                    "ip_address": "203.129.25.181",
                    "location": "Sydney, NSW, Australia",
                    "latitude": -33.8591,
                    "longitude": 151.2002
                },
                "custom": {}
            },
            "created_at": "2020-11-02T09:35:18.000Z"
        }
    ]
}]

编辑:

我想出了如何打印 JSON 中的记录。

import pandas as pd

df = pd.read_json('./test.json', orient='records')

df.to_csv('json_converted_to_csv.csv', index = None, header=True)

现在我想弄清楚如何展平特定的数组。例如如何扁平化“custom_fields”并以扁平结构中的 id 和 value 结束。

谢谢您的帮助。

杰瑞

我发现这个解决方案是将更复杂的 JSON 转换为 CSV 的通用方法,包括展平 JSON。

import json
import pandas as pd

#read JSON from file
with open('./export-2021-02-03-0452-1094680-36000530163187bb.json') as f:
  data = json.load(f)
  
#flatten the JSON to any level. In this case level 2.
df = pd.json_normalize(data,max_level=2)

#save it to CSV
df.to_csv('file.csv', index = "none", header="true")

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章