Converting Json file to Dataframe Python 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?
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.