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

Question

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",
        ]
      }]
}

user7864386 · Accepted Answer · 2022-03-03 20:51:06Z

1

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

answered Mar 3, 2022 at 20:51

user7864386

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related