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