python - 如何扁平化来自 Surveymonkey API 的 JSON 响应

Question

我正在设置一个 Python 函数来使用 Surveymonkey API 从 Surveymonkey 获取调查回复。

API 返回具有深度递归文件结构的 JSON 格式的响应。

我在尝试扁平化此 JSON 以便它可以进入 Google Cloud Storage 时遇到问题。

我尝试使用以下代码来展平响应。哪个有效；但是，它不会将其转换为我正在寻找的格式。

{
  "per_page": 2,
  "total": 1,
  "data": [
    {
      "total_time": 0,
      "collection_mode": "default",
      "href": "https://api.surveymonkey.com/v3/responses/5007154325",
      "custom_variables": {
        "custvar_1": "one",
        "custvar_2": "two"
      },
      "custom_value": "custom identifier for the response",
      "edit_url": "https://www.surveymonkey.com/r/",
      "analyze_url": "https://www.surveymonkey.com/analyze/browse/",
      "ip_address": "",
      "pages": [
        {
          "id": "73527947",
          "questions": [
            {
              "id": "273237811",
              "answers": [
                {
                  "choice_id": "1842351148"
                },
                {
                  "text": "I might be text or null",
                  "other_id": "1842351149"
                }
              ]
            },
            {
              "id": "273240822",
              "answers": [
                {
                  "choice_id": "1863145815",
                  "row_id": "1863145806"
                },
                {
                  "text": "I might be text or null",
                  "other_id": "1863145817"
                }
              ]
            },
            {
              "id": "273239576",
              "answers": [
                {
                  "choice_id": "1863156702",
                  "row_id": "1863156701"
                },
                {
                  "text": "I might be text or null",
                  "other_id": "1863156707"
                }
              ]
            },
            {
              "id": "296944423",
              "answers": [
                {
                  "text": "I might be text or null"
                }
              ]
            }
          ]
        }
      ],
      "date_modified": "1970-01-17T19:07:34+00:00",
      "response_status": "completed",
      "id": "5007154325",
      "collector_id": "50253586",
      "recipient_id": "0",
      "date_created": "1970-01-17T19:07:34+00:00",
      "survey_id": "105723396"
    }
  ],
  "page": 1,
  "links": {
    "self": "https://api.surveymonkey.com/v3/surveys/123456/responses/bulk?page=1&per_page=2"
  }
}

answers_df = json_normalize(data=response_json['data'],
                        record_path=['pages', 'questions', 'answers'],
                        meta=['id', ['pages', 'questions', 'id'], ['pages', 'id']])

我需要它为每个问题 ID、choice_id 和文本字段返回一列，而不是为每个问题 ID 返回一行。

我想查看的列是 total_time、collection_mode、href、custom_variables.custvar_1、custom_variables.custvar_2、custom_value、edit_url、analyze_url、ip_address、pages.id、pages.questions.0.id、pages.questions.0.answers。 0.choice_id、pages.questions.0.answers.0.text、pages.questions.0.answers.0.other_id

而不是每个问题 ID、Choice_id、文本和答案位于单独的行中。我想为每个人写一个专栏。这样每个survey_id或数据中的索引只有1行

标签： pythonjsonrestsurveymonkey