Convert JSON data to Pandas DataFrame where keys and values are in different sections of JSON-CodePudding

I'm trying to create a python pandas DataFrame out of the JSON file but my eventual DataFrame column headers are in a different section of the JSON file to the values that will fill the columns.

I have simplified the json, but it basically looks like below. There is only one section of column headers and multiple sections of data.

I need each column filled with the values that relates to it. So value 1 in each case will fill the column under heading 1.

{"my_data": {
    "my_data_columns_headers": [
      "heading 1",
      "heading 2",
      "heading 3",
      "heading 4",
      "heading 5",
    ]
    },
    "values": [
      {
        "data": [
          "value 1",
          "value 2",
          "value 3",
          "value 4",
          "value 5",
        ]
      },
      {
        "data": [
          "value 1",
          "value 2",
          "value 3",
          "value 4",
          "value 5",
        ]
      }]
}

CodePudding user response：

Construct a DataFrame by extracting the values under the "values" key; assign column names using the list under "my_data_columns_headers" key, which is under the "my_data" key.

out = pd.DataFrame(pd.Series(data['values']).str.get('data').tolist(), columns=data['my_data']['my_data_columns_headers'])

Output:

  heading 1 heading 2 heading 3 heading 4 heading 5
0   value 1   value 2   value 3   value 4   value 5
1   value 1   value 2   value 3   value 4   value 5