I received this json from an API:
myjson = {
"Count": 203,
"Items": [
{
"Actions": [
"edit_",
"remove_",
"attachments_",
"auditTrail",
"offline_",
"linkRecord"
],
"Columns": [
{
"Label": "Workflow Name",
"Name": "__WorkflowName__",
"Value": "VOCNF00000208"
},
{
"Label": "Workflow Description",
"Name": "__WorkflowDescription__",
"Value": "Vendor Outsourcing CNF"
},
{
"Label": "Current Assignee",
"Name": "__CurrentAssignee__",
"Value": ""
},
{
"Label": "Last Updated",
"Name": "__DateLastUpdated__",
"Value": "8/5/2022 1:06:53 PM"
},
{
"Label": "Created",
"Name": "__DateCreated__",
"Value": "8/5/2022 1:01:45 PM"
},
{
"Label": "Date Signed",
"Name": "__DateSigned__",
"Value": ""
},
{
"Label": "Completed",
"Name": "__DateCompleted__",
"Value": "8/5/2022 1:06:53 PM"
},
{
"Label": "Status",
"Name": "__Status__",
"Value": "Contract Manager Notified"
},
{
"Label": "Document ID",
"Name": "__DocumentIdentifier__",
"Value": ""
},
{
"Label": "End Date",
"Name": "__EndDate__",
"Value": "12/31/2033 12:00:00 AM"
},
{
"Label": "Stage Progress",
"Name": "__FormProgress__",
"Value": "0"
},
{
"Label": "Next Signer",
"Name": "__NextSigner__",
"Value": ""
},
{
"Label": "Workflow ID",
"Name": "__WorkflowName__",
"Value": "VOCNF00000208"
},
{
"Label": "Current Assignee",
"Name": "__CurrentAssignee__",
"Value": ""
},
{
"Label": "Contract Manager",
"Name": "Contract_Manager",
"Value": "Burnham Lenore(lenore.burnham@gilead.com)"
},
{
"Label": "Status",
"Name": "__Status__",
"Value": "Contract Manager Notified"
},
{
"Label": "Last Updated",
"Name": "__DateLastUpdated__",
"Value": "8/5/2022 1:06:53 PM"
},
{
"Label": "Requestor Name",
"Name": "Requestor_Name",
"Value": "Tyler Southcott"
},
{
"Label": "Requestor Email",
"Name": "Requestor_Email",
"Value": "tyler.southcott@iconplc.com"
},
{
"Label": "Gilead or Kite",
"Name": "Gilead_or_Kite",
"Value": "Gilead"
},
{
"Label": "Vendor Name",
"Name": "Vendor_Name",
"Value": "ICON PLC"
},
{
"Label": "Protocol Number",
"Name": "Protocol_Number",
"Value": "GS-US-546-5920"
},
{
"Label": "Therapeutic Area",
"Name": "Therapeutic_Area",
"Value": "Oncology"
},
{
"Label": "Compount Product ID",
"Name": "Compount_Product_ID",
"Value": "GS-4721 Magrolimab (546)"
},
{
"Label": "Clinical trial Phase",
"Name": "Clinical_trial_Phase",
"Value": "P2"
},
{
"Label": "Date CNF requested",
"Name": "Date_CNF_requested",
"Value": "08/05/2022"
},
{
"Label": "CNF Number",
"Name": "CNF_Number",
"Value": "3"
},
{
"Label": "CNF Change Order",
"Name": "CNF_Change_Order",
"Value": "3"
},
{
"Label": "CNF Value",
"Name": "CNF_Value",
"Value": " 7,491.91"
},
{
"Label": "Rationale and Summary",
"Name": "Rationale_and_Summary",
"Value": "\"This CNF contains the following activities:\r\n\r\n- Protocol Amendment #5 has been announced, considered major. Relevant activities included for AUS and WEU where we will exceed the current budget.\r\n- Additional ARSAC Submissions have been requested. 20% of the initial submission effort was assumed for this minor submission.\"\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n"
}
],
"ResultSetId": "fd6aa800-479a-46c7-ac20-34c6b896ad07",
"TaskId": "fd6aa800-479a-46c7-ac20-34c6b896ad07",
"TokenId": "00000000-0000-0000-0000-000000000000"
}
}
When I run this block:
import json
import pandas as pd
from pandas import json_normalize
df2 = pd.json_normalize(myjson['Items'], "Columns", ['TaskId'], errors='ignore')
My output is this:
The json_normalize is orienting the data in a column format with 6000+ rows and assigning the metadata to each row.
What I would like to do is have each row be a record for each Taskid and transpose the data with each Label being a header and the Value being the data.
How can I go about doing that? Here is my desired output:
