Thursday, 28 March 2019

I have mixed level JSON to parse in python, Having trouble with Keys

I have a set of nested JSON and it I am doing the following thus far:

r = session.get(search_url, auth=HTTPKerberosAuth(mutual_authentication=OPTIONAL), verify=False)
json_data = json.loads(r.content)
flattened_data = json_normalize(json_data['documents'])
print(list(flattened_data))

This outputs the following results:

['affected_users', 'aggregatedLabels', 'aliases', 'assignedFolder', 'assigneeIdentity', 'attachments', 'authorizations', 'autoUpgrade.workingHours', 'conversation', 'createDate', 'dedupes', 'deleted', 'description', 'descriptionContentType', 'editCount', 'engagementList', 'extensions.backlog.priority', 'extensions.effort.effortEstimatedLocal.effort', 'extensions.effort.effortEstimatedLocal.unit', 'extensions.effort.effortEstimatedRecursiveSum.effort', 'extensions.effort.effortEstimatedRecursiveSum.unit', 'extensions.effort.effortRemainingLocalSum.effort', 'extensions.effort.effortRemainingLocalSum.unit', 'extensions.effort.effortRemainingRecursiveSum.effort', 'extensions.effort.effortRemainingRecursiveSum.unit', 'extensions.effort.effortSpentLocalSum.effort', 'extensions.effort.effortSpentLocalSum.unit', 'extensions.effort.effortSpentRecursiveSum.effort', 'extensions.effort.effortSpentRecursiveSum.unit', 'extensions.tt.assignedGroup', 'extensions.tt.building', 'extensions.tt.caseType', 'extensions.tt.category', 'extensions.tt.city', 'extensions.tt.endCode', 'extensions.tt.ecd', 'extensions.tt.impact', 'extensions.tt.item', 'extensions.tt.justification', 'extensions.tt.migrationStatus', 'extensions.tt.minImpact', 'extensions.tt.resolution', 'extensions.tt.rootCause', 'extensions.tt.rootCauseDetails', 'extensions.tt.status', 'extensions.tt.type', 'frames', 'id', 'identityTimestamped', 'inheritedLabels', 'isTicket', 'labels', 'lastAssignedDate', 'lastResolvedByIdentity', 'lastResolvedDate', 'lastUpdatedActualDate', 'lastUpdatedConversationDate', 'lastUpdatedDate', 'lastUpdatedIdentity', 'next_step.action', 'next_step.exceptions', 'next_step.owner', 'parentTasks', 'requesterIdentity', 'rootCauses', 'rulesReceipt', 'schedule.estimatedCompletionDate', 'schedule.estimatedStartDate', 'schedule.needByDate', 'schema', 'slaReceipts', 'status', 'stickyThreadId', 'submitterIdentity', 'subtasks', 'tags', 'threads', 'title', 'watchers']

From this list I am trying to get only certain keys and their values into the data frame:

    print(flattened_data['assigneeIdentity',
#                         'createDate',
#                         'description',
#                         'extensions.tt.assignedGroup',
#                         'extensions.tt.category',
#                         'extensions.tt.endCode',
#                         'extensions.tt.ecd',
#                         'extensions.tt.impact',
#                         'extensions.tt.item',
#                         'extensions.tt.justification',
#                         'extensions.tt.resolution',
#                         'extensions.tt.rootCause',
#                         'extensions.tt.rootCauseDetails',
#                         'extensions.tt.status',
#                         'extensions.tt.type',
#                         'id',
#                         'labels',
#                         'lastAssignedDate',
#                         'lastResolvedByIdentity',
#                         'lastResolvedDate',
#                         'lastUpdatedActualDate',
#                         'lastUpdatedConversationDate',
#                         'lastUpdatedDate',
#                         'lastUpdatedIdentity',
#                         'requesterIdentity',
#                         'submitterIdentity',
#                         'title',
#                         'watchers'])

When I do this I get a key error. so the base JSON that comes in is as follows for the fields I list above and an idea of the nesting level of each one; each 'item' is an integer under the documents element with then more nested elements I need:

documents:
          0:
             extensions:
                         tt:
                             category:
                             type:
                             item:
                             assignedGroup:
                             impact:
                             justification:
                             endCode:
                             rootCause:
                             rootCauseDetails:
                             status:
              id:
              title:
              lastAssignedDate:
              createDate:
              lastUpdatedActualDate:
              lastResolvedDate:
              lastResolvedByIdentity:
              lastUpdatedIdentity:
              assigneeIdentity:
              submitterIdentity:
              requesterIdentity:
              identityTimestamped:
              lastUpdatedConversationDate:
              lastUpdatedDate:
          1:
             extensions:
                         tt:
                             category:
                             type:
                             item:
                             assignedGroup:
                             impact:
                             justification:
                             endCode:
                             rootCause:
                             rootCauseDetails:
                             status:
              id:
              title:
              lastAssignedDate:
              createDate:
              lastUpdatedActualDate:
              lastResolvedDate:
              lastResolvedByIdentity:
              lastUpdatedIdentity:
              assigneeIdentity:
              submitterIdentity:
              requesterIdentity:
              identityTimestamped:
              lastUpdatedConversationDate:
              lastUpdatedDate:

How do I get this and the values into a dataframe.



from I have mixed level JSON to parse in python, Having trouble with Keys

No comments:

Post a Comment