0
$\begingroup$

I have a json file which has multiple events, each event starts with EventVersion Key. The data looks similar to the following synthesized data.

{"Records":[{"eventVersion":"1.04","userIdentity":{"type":"R","principalId":"P:i","arn":"arn:aws:sts::5","accountId":"50","accessKeyId":"AW","sessionContext":{"attributes":{"mfaAuthenticated":"f","creationDate":"2013-09"},"sessionIssuer":{"type":"R","principalId":"WA","arn":"arn:aws:iam::6","accountId":"70","userName":"user1"}}},"eventTime":"2027-6","eventSource":"a.com","eventName":"DS","awsRegion":"UZ","sourceIPAddress":"2.1.3","userAgent":"li","requestParameters":null,"responseElements":null,"requestID":"OO","eventID":"09","eventType":"ABC","apiVersion":"2010-4","recipientAccountId":"78"},{"eventVersion":"1.04","userIdentity":{"type":"R","principalId":"P:i","arn":"arn:aws:sts::5","accountId":"50","accessKeyId":"AW","sessionContext":{"attributes":{"mfaAuthenticated":"f","creationDate":"2013-09"},"sessionIssuer":{"type":"R","principalId":"WA","arn":"arn:aws:iam::6","accountId":"70","userName":"user1"}}},"eventTime":"2027-6","eventSource":"a.com","eventName":"DS","awsRegion":"UZ","sourceIPAddress":"2.1.3","userAgent":"li","requestParameters":null,"responseElements":null,"requestID":"OO","eventID":"09","eventType":"ABC","apiVersion":"2010-4","recipientAccountId":"78"}]}

I'm using the following code in Python to convert this to Pandas Dataframe such that Keys are columns and values of each event is a row. 

with open('/Users/snehahonnappa/Documents/NLP_AWSlogs/Model/Data/505728423372_CloudTrail_ap-northeast-1_20160913T1700Z_yKA3wB5Nx6juR6Kg.json') as json_data:
    sample_object = json.load(json_data)

df = pd.io.json.json_normalize(sample_object)
df.columns = df.columns.map(lambda x: x.split(".")[-1])

print df.shape

When I print shape of the dataframe its 1X1. I'm expecting (Number of unique keys X Number of records)

Snippet of how I'm expecting the dataframe to be

eventVersion               userIdentity                          eventTime
             type   principalId P   arn     accountID userName  
1.04         R      P           i   arn:aws 50        user1      2027-6
1.06         Q      O           i   arn:aws 67        u2         2027-7

Appreciate any help.

Update :

I'm writing the json file into a csv and then trying to convert this to dataframe on which my models can be applied on. Following is my code.

import json
import csv
import sys

data_parsed = json.loads(open('/tmp/A.json').read())
log_data = data_parsed['Records']

# open a CSV file for writing
data = open('/tmp/log.csv', 'w')

# create the csv writer object
csvwriter = csv.writer(data)
count = 0

for i in log_data:
      if count == 0:
             header = i.keys()
             csvwriter.writerow(header)
             count += 1
      csvwriter.writerow(i.values())

data.close()

This is writing the keys as headers and values of each record as a separate row which is as expected. However the nested json objects are being written as one value.

Following is a snippet of my csv file which was obtained by executing the above code.

eventVersion eventID eventTime requestParameters                eventType
1.04         0       2016-20                                    AwsApiCall
1.04         8       2016-20    {u'tagKeys': [u'User Name']}    AwsApiCall
1.05         4       2016-30    {u'filterSet': {u'items': [{u'name': u'resource-type', u'valueSet': {u'items': [{u'value': u'*'}]}}, {u'name': u'tag:User Name', u'valueSet': {u'items': [{u'value': u'*'}]}}]}}    
                                                                AwsApiCall

Any suggestions to tackle this?

Improve this question
$\endgroup$

1 Answer 1

Reset to default
2
$\begingroup$

I once ran into a situation like this where i wanted a complex dataframe due to the original source having a complex data structure.

I solved my issue by simplifying my structure into multiple separate dataframes instead of one big complex multi structure dataframe.

With simple separate dataframes i was better positioned to apply complex algorithmic operations.

Looking at your specific data, you could get rid of userIdentity which results in a simple 2d dataframe. This should position you to do any complex operation.

I understand this doesn't answer your specific dataframe structure requirement.

But i hope this answers the spirit of your objective.

Improve this answer
$\endgroup$
4
  • $\begingroup$ Thank you Dendekker, I appreciate your response. I tried converting to a csv file and then to data frame. To a certain extent it worked (please see my updates to the question). However the nested json objects are as it is. As per your suggestion, since there are multiple nested objects if we separate each nested object into a separate dataframe then aren't we looking at a much complex solution given the fact that we would have to combine them later? Would you be able to elaborate on your approach? $\endgroup$
    – iprof0214
    Commented Oct 10, 2017 at 22:40
  • $\begingroup$ @Sneha dict = json.loads(js);df = pd.io.json.json_normalize(dict['Records']) Doesn't this flatten out your multi structure json into a 2d dataframe? You would need more than 2 records to see if the dataframe properly repeats the data within the child structures of your json. $\endgroup$
    – user40285
    Commented Oct 11, 2017 at 6:50
  • $\begingroup$ @Dendekker - df = pd.io.json.json_normalize(dict['log_data']) did the magic. However there is one json object that is a list and not dictionary, its failing to flatten this object requestParameters":{"filterSet":{"items":[{"name":"resource-type","valueSet":{"items":[{"value":""}]}},{"name":"tag:User Name","valueSet":{"items":[{"value":""}]}}]}} is being returned in the dataframe as follows requestParameters.filterSet.items [{u'name': u'resource-type', u'valueSet': {u'items":[{"u'value":"*"}] $\endgroup$
    – iprof0214
    Commented Oct 11, 2017 at 21:31
  • $\begingroup$ I recommend you split your dictionary into multiple dictionaries, thereby converting each one into separate dataframes. Pandas DataFrame conversions work by parsing through a list of dictionaries and converting them to df rows per dict. If there are too many child structures in your dicts, such as a "list of dicts containing another list of dicts" times 2, then you need to restructure you data model. You may need to include "foreign keys" as extra columns in your df so you can reference data properly. I hope this was helpful advice. $\endgroup$
    – user40285
    Commented Oct 11, 2017 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.