Home > Back-end >  Convert JSON data to Pandas DataFrame where keys and values are in different sections of JSON
Convert JSON data to Pandas DataFrame where keys and values are in different sections of JSON

Time:03-04

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
  • Related