I have the following Json file:
Json = json.loads(resp.text)
print pprint.pprint(Json)
Response:
{u'data': [{u'assignedUser': None,
u'author': {u'authorData': {u'blogId': u'4050964395'},
u'authorFullName': None,
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'2568086880',
u'externalLegacyId': None,
u'id': u'29011839',
u'influence': {u'score': 550},
u'socialNetworkTypeName': u'Twitter Profile Author',
u'tags': None,
u'title': None,
u'verified': None},
u'classification': None,
u'comments': [],
u'content': None,
u'engagement': None,
u'entities': None,
u'externalId': u'913186147185991680',
u'externalLink': u'coms:stwittercomss2568086880sstatusess913186147185991680',
u'firstEngagementDate': None,
u'harvestDate': u'2017-09-27T23:38:50Z',
u'id': u'688342654654',
u'languageId': None,
u'latestEngagementDate': None,
u'mediaProvider': {u'extendedMediaType': None,
u'id': u'10',
u'mediaTypeId': 8,
u'title': u'TWITTER'},
u'metadata': None,
u'parent': {u'externalId': u'912930603221127168'},
u'parentAuthor': {u'authorData': {u'blogId': u'6297935084'},
u'authorFullName': None,
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'4861615638',
u'externalLegacyId': None,
u'id': u'330423308',
u'influence': None,
u'socialNetworkTypeName': None,
u'tags': None,
u'title': None,
u'verified': None},
u'postDynamics': [],
u'postStatus': 0,
u'postStatusException': None,
u'priority': None,
u'publishedDate': u'2017-09-27T23:38:38Z',
u'regionId': None,
u'sentiment': [{u'overridden': False,
u'topicId': 1133213,
u'value': 0}],
u'source': {u'comments': [],
u'externalLink': u'twittercoms',
u'id': u'4050964395',
u'language': None,
u'region': None,
u'tags': [],
u'title': None,
u'verified': None},
u'sourceApplication': None,
u'sourceFilterMatches': None,
u'summaryContent': None,
u'tags': [],
u'title': None,
u'topics': [1133213],
u'workflowHistory': []},
{u'assignedUser': None,
u'author': {u'authorData': {u'blogId': u'4050964395'},
u'authorFullName': None,
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'2568086880',
u'externalLegacyId': None,
u'id': u'29011839',
u'influence': {u'score': 550},
u'socialNetworkTypeName': u'Twitter Profile Author',
u'tags': None,
u'title': None,
u'verified': None},
u'classification': None,
u'comments': [],
u'content': None,
u'engagement': None,
u'entities': {u'hashtags': None,
u'media': [{u'description': None,
u'display_url': u'pictwittercomsstbK8mF4vHO',
u'expanded_url': u'htptwittercomssigaitokamonesstatuss912684996690812928sphotos1',
u'id': u'912684878763659264',
u'indices': [27, 50],
u'media_url': u'coms:spbstwimgcomssmediasDKqCD8aUEAAX9qzjpg',
u'media_url_comss': u'htppbstwimgcomssmediasDKqCD8aUEAAX9qzjpg',
u'target_url': None,
u'title': None,
u'type': u'photo',
u'url': u'htptcostbK8mF4vHO',
u'video_info': None},
{u'description': None,
u'display_url': u'pictwittercomsstbK8mF4vHO',
u'expanded_url': u'htptwittercomssigaitokamonesstatuss912684996690812928sphotos1',
u'id': u'912684910049058816',
u'indices': [27, 50],
u'media_url': u'coms:spbstwimgcomssmediasDKqCFw9VYAAYsOdjpg',
u'media_url_comss': u'htppbstwimgcomssmediasDKqCFw9VYAAYsOdjpg',
u'target_url': None,
u'title': None,
u'type': u'photo',
u'url': u'htptcostbK8mF4vHO',
u'video_info': None},
{u'description': None,
u'display_url': u'pictwittercomsstbK8mF4vHO',
u'expanded_url': u'htptwittercomssigaitokamonesstatuss912684996690812928sphotos1',
u'id': u'912684937341394944',
u'indices': [27, 50],
u'media_url': u'coms:spbstwimgcomssmediasDKqCHWoVYAAqqMAjpg',
u'media_url_comss': u'htppbstwimgcomssmediasDKqCHWoVYAAqqMAjpg',
u'target_url': None,
u'title': None,
u'type': u'photo',
u'url': u'htptcostbK8mF4vHO',
u'video_info': None},
{u'description': None,
u'display_url': u'pictwittercomsstbK8mF4vHO',
u'expanded_url': u'htptwittercomssigaitokamonesstatuss912684996690812928sphotos1',
u'id': u'912684966760161281',
u'indices': [27, 50],
u'media_url': u'coms:spbstwimgcomssmediasDKqCJEOUIAEnVD-jpg',
u'media_url_comss': u'htppbstwimgcomssmediasDKqCJEOUIAEnVD-jpg',
u'target_url': None,
u'title': None,
u'type': u'photo',
u'url': u'htptcostbK8mF4vHO',
u'video_info': None}],
u'mentions': None,
u'urls': None},
u'externalId': u'913185982639304704',
u'externalLink': u'coms:stwittercomss2568086880sstatusess913185982639304704',
u'firstEngagementDate': None,
u'harvestDate': u'2017-09-27T23:38:11Z',
u'id': u'688342490373',
u'languageId': None,
u'latestEngagementDate': None,
u'mediaProvider': {u'extendedMediaType': None,
u'id': u'10',
u'mediaTypeId': 8,
u'title': u'TWITTER'},
u'metadata': None,
u'parent': {u'externalId': u'912684996690812928'},
u'parentAuthor': {u'authorData': {u'blogId': u'237430348'},
u'authorFullName': None,
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'2689515817',
u'externalLegacyId': None,
u'id': u'112855989',
u'influence': None,
u'socialNetworkTypeName': None,
u'tags': None,
u'title': None,
u'verified': None},
u'postDynamics': [],
u'postStatus': 0,
u'postStatusException': None,
u'priority': None,
u'publishedDate': u'2017-09-27T23:37:59Z',
u'regionId': None,
u'sentiment': [{u'overridden': False,
u'topicId': 1133213,
u'value': 0}],
u'source': {u'comments': [],
u'externalLink': u'twittercoms',
u'id': u'4050964395',
u'language': None,
u'region': None,
u'tags': [],
u'title': None,
u'verified': None},
u'sourceApplication': None,
u'sourceFilterMatches': None,
u'summaryContent': None,
u'tags': [],
u'title': None,
u'topics': [1133213],
u'workflowHistory': []},
{u'assignedUser': None,
u'author': {u'authorFullName': u'Harry M',
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'-1',
u'externalLegacyId': None,
u'id': u'-1',
u'socialNetworkTypeName': None,
u'tags': None,
u'title': u'Harry M',
u'verified': False},
u'classification': None,
u'comments': [],
u'content': u"we'll have to wait and see if this one is vapourware, like the FF91",
u'engagement': None,
u'entities': None,
u'externalId': u'z22osbpbsvj3hd1euacdp43aqlqerg0o1gv1t14dom1w03c010c',
u'externalLink': u'coms:swwwyoutubecomsswatch?v=ePJ8xcL3HAE#z22osbpbsvj3hd1euacdp43aqlqerg0o1gv1t14dom1w03c010c',
u'firstEngagementDate': None,
u'harvestDate': u'2017-09-27T23:17:17Z',
u'id': u'688337451911',
u'languageId': 1,
u'latestEngagementDate': None,
u'mediaProvider': {u'extendedMediaType': None,
u'id': u'777',
u'mediaTypeId': 11,
u'title': u'YouTube Comments'},
u'metadata': None,
u'parent': {u'externalId': u'ePJ8xcL3HAE',
u'id': u'655094359463'},
u'postDynamics': [],
u'postStatus': 0,
u'postStatusException': None,
u'priority': None,
u'publishedDate': u'2017-09-27T22:22:21Z',
u'regionId': 249,
u'sentiment': [{u'overridden': False,
u'topicId': 1133213,
u'value': 0}],
u'source': {u'comments': [],
u'externalLink': u'youtubecoms',
u'id': u'5631651605',
u'language': u'en',
u'region': None,
u'tags': [],
u'title': u'Harry M on Lucid Air (2019) Tesla Model S killer [YOUCAR] '},
u'sourceApplication': None,
u'sourceFilterMatches': None,
u'summaryContent': None,
u'tags': [],
u'title': u'Harry M on Lucid Air (2019) Tesla Model S killer [YOUCAR] ',
u'topics': [1133213],
u'workflowHistory': []},
{u'assignedUser': None,
u'author': {u'authorFullName': u'Bruce Magnus',
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'-1',
u'externalLegacyId': None,
u'id': u'-1',
u'socialNetworkTypeName': None,
u'tags': None,
u'title': u'Bruce Magnus',
u'verified': False},
u'classification': None,
u'comments': [],
u'content': u'Faraday Future has just released their debut 2018 FF91 at CES 2017 It is very much still in its beta phase with a lot of promises One of those is that the car can achieve 0 -60MPH in 239 seconds How does it feel to sit in a car with such a massive amount of power?',
u'engagement': None,
u'entities': None,
u'externalId': u'cFcYnSDRBMs',
u'externalLink': u'coms:swwwyoutubecomsswatch?v=cFcYnSDRBMs',
u'firstEngagementDate': None,
u'harvestDate': u'2017-09-27T21:28:19Z',
u'id': u'688311663482',
u'languageId': 1,
u'latestEngagementDate': None,
u'mediaProvider': {u'extendedMediaType': None,
u'id': u'2',
u'mediaTypeId': 2,
u'title': u'YouTube'},
u'metadata': None,
u'parent': None,
u'postDynamics': [{u'label': u'View Count', u'value': u'2'},
{u'label': u'Comment Count', u'value': u'0'},
{u'label': u'Unique Commenters',
u'value': u'0'},
{u'label': u'Engagement', u'value': u'0'},
{u'label': u'Likes and Votes',
u'value': u'0'},
{u'label': u'Inbound Links', u'value': u'0'}],
u'postStatus': 0,
u'postStatusException': None,
u'priority': None,
u'publishedDate': u'2017-09-27T03:09:07Z',
u'regionId': 249,
u'sentiment': [{u'overridden': False,
u'topicId': 1133213,
u'value': 10}],
u'source': {u'comments': [],
u'externalLink': u'youtubecoms',
u'id': u'7186804057',
u'language': u'en',
u'region': None,
u'tags': [],
u'title': u'FF91 Faraday Future - TESTING 0 - 60 MPH IN 24 SECONDS'},
u'sourceApplication': None,
u'sourceFilterMatches': None,
u'summaryContent': None,
u'tags': [],
u'title': u'FF91 Faraday Future - TESTING 0 - 60 MPH IN 24 SECONDS',
u'topics': [1133213],
u'workflowHistory': []},
{u'assignedUser': None,
u'author': {u'authorData': {u'blogId': u'4502740665'},
u'authorFullName': None,
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'2788814143',
u'externalLegacyId': None,
u'id': u'30430561',
u'influence': {u'score': 80},
u'socialNetworkTypeName': u'Twitter Profile Author',
u'tags': None,
u'title': None,
u'verified': None},
u'classification': None,
u'comments': [],
u'content': None,
u'engagement': None,
u'entities': {u'hashtags': None,
u'media': [{u'description': None,
u'display_url': u'pictwittercomsstGHUZ2ncMU',
u'expanded_url': u'htptwittercomssAllstonAutosstatuss913103968300969984sphotos1',
u'id': u'913103966023462912',
u'indices': [100, 123],
u'media_url': u'coms:spbstwimgcomssmediasDKv_OCHUMAA-QHSjpg',
u'media_url_comss': u'htppbstwimgcomssmediasDKv_OCHUMAA-QHSjpg',
u'target_url': None,
u'title': None,
u'type': u'photo',
u'url': u'htptcostGHUZ2ncMU',
u'video_info': None}],
u'mentions': None,
u'urls': [{u'display_url': u'dlvritsPqs7xk',
u'expanded_url': u'coms:sdlvritsPqs7xk',
u'indices': [76, 99],
u'url': u'htptcosM2xcaQ5Os0'}]},
u'externalId': u'913103968300969984',
u'externalLink': u'coms:stwittercomss2788814143sstatusess913103968300969984',
u'firstEngagementDate': None,
u'harvestDate': u'2017-09-27T18:12:11Z',
u'id': u'688263442101',
u'languageId': None,
u'latestEngagementDate': None,
u'mediaProvider': {u'extendedMediaType': None,
u'id': u'10',
u'mediaTypeId': 8,
u'title': u'TWITTER'},
u'metadata': None,
u'parent': None,
u'postDynamics': [],
u'postStatus': 0,
u'postStatusException': None,
u'priority': None,
u'publishedDate': u'2017-09-27T18:12:05Z',
u'regionId': None,
u'sentiment': [{u'overridden': False,
u'topicId': 1133213,
u'value': -10}],
u'source': {u'comments': [],
u'externalLink': u'twittercoms',
u'id': u'4502740665',
u'language': None,
u'region': None,
u'tags': [],
u'title': None,
u'verified': None},
u'sourceApplication': None,
u'sourceFilterMatches': None,
u'summaryContent': None,
u'tags': [],
u'title': None,
u'topics': [1133213],
u'workflowHistory': []},
{u'assignedUser': None,
u'author': {u'authorData': {u'blogId': u'6202921586'},
u'authorFullName': None,
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'2669983818',
u'externalLegacyId': None,
u'id': u'314584800',
u'influence': {u'score': 500},
u'socialNetworkTypeName': u'Twitter Profile Author',
u'tags': None,
u'title': None,
u'verified': None},
u'classification': None,
u'comments': [],
u'content': None,
u'engagement': None,
u'entities': None,
u'externalId': u'913089957010350080',
u'externalLink': u'coms:stwittercomss2669983818sstatusess913089957010350080',
u'firstEngagementDate': None,
u'harvestDate': u'2017-09-27T17:16:32Z',
u'id': u'688249615145',
u'languageId': None,
u'latestEngagementDate': None,
u'mediaProvider': {u'extendedMediaType': None,
u'id': u'10',
u'mediaTypeId': 8,
u'title': u'TWITTER'},
u'metadata': None,
u'parent': None,
u'postDynamics': [],
u'postStatus': 0,
u'postStatusException': None,
u'priority': None,
u'publishedDate': u'2017-09-27T17:16:24Z',
u'regionId': None,
u'sentiment': [{u'overridden': False,
u'topicId': 1133213,
u'value': 0}],
u'source': {u'comments': [],
u'externalLink': u'twittercoms',
u'id': u'6202921586',
u'language': None,
u'region': None,
u'tags': [],
u'title': None,
u'verified': None},
u'sourceApplication': None,
u'sourceFilterMatches': None,
u'summaryContent': None,
u'tags': [],
u'title': None,
u'topics': [1133213],
u'workflowHistory': []},
{u'assignedUser': None,
u'author': {u'authorData': {u'blogId': u'2987515632'},
u'authorFullName': None,
u'avatar': None,
u'bio': None,
u'comments': None,
u'externalId': u'2169387900',
u'externalLegacyId': None,
u'id': u'46147122',
u'influence': {u'score': 170},
u'socialNetworkTypeName': u'Twitter Profile Author',
u'tags': None,
u'title': None,
u'verified': None},
u'classification': None,
u'comments': [],
u'content': None,
u'engagement': None,
u'entities': {u'hashtags': None,
u'media': None,
u'mentions': [{u'id': u'10228272',
u'indices': [116, 124],
u'name': u'YouTube',
u'screen_name': u'YouTube',
u'url': None}],
u'urls': [{u'display_url': u'youtubesb6QrF59zLQU?a',
u'expanded_url': u'coms:syoutubesb6QrF59zLQU?a',
u'indices': [88, 111],
u'url': u'htptcosEGPB3xjmSm'}]},
u'externalId': u'913089735249268736',
u'externalLink': u'coms:stwittercomss2169387900sstatusess913089735249268736',
u'firstEngagementDate': None,
u'harvestDate': u'2017-09-27T17:15:41Z',
u'id': u'688249356612',
u'languageId': None,
u'latestEngagementDate': None,
u'mediaProvider': {u'extendedMediaType': None,
u'id': u'10',
u'mediaTypeId': 8,
u'title': u'TWITTER'},
u'metadata': None,
u'parent': None,
u'postDynamics': [],
u'postStatus': 0,
u'postStatusException': None,
u'priority': None,
u'publishedDate': u'2017-09-27T17:15:31Z',
u'regionId': None,
u'sentiment': [{u'overridden': False,
u'topicId': 1133213,
u'value': -10}],
u'source': {u'comments': [],
u'externalLink': u'twittercoms',
u'id': u'2987515632',
u'language': None,
u'region': None,
u'tags': [],
u'title': None,
u'verified': None},
u'sourceApplication': None,
u'sourceFilterMatches': None,
u'summaryContent': None,
u'tags': [],
u'title': None,
u'topics': [1133213],
u'workflowHistory': []}],
u'meta': {u'totalCount': 251}}
NoneI need to convert the elements in data['postDynamics'] into a pandas data frame. As you can see I cannot just do a loop for each label and it's corresponding value as some of the line items have empty lists. I want to be able to extract everything into a pandas data frame, sequentially. This is where i'm stuck!
for data in Json['data']:
print data['postDynamics']
Response:
[]
[]
[]
[{u'value': u'2', u'label': u'View Count'}, {u'value': u'0', u'label': u'Comment Count'}, {u'value': u'0', u'label': u'Unique Commenters'}, {u'value': u'0', u'label': u'Engagement'}, {u'value': u'0', u'label': u'Likes and Votes'}, {u'value': u'0', u'label': u'Inbound Links'}]
[]
[]
[]Ideally i'd like to put the data into a pandas data frame and write into a csv file that looks like this:
I would appreciate anyone's help.
