I am trying to read some data using REST API and write that on a DB table. I have written the below code. But unfortunately, I am kind of stuck with the flattened JSON. Can you please help with a way to convert JSON to Data frame.
I have tried the below code
import requests import json import pandas from pandas.io.json import json_normalize from flatten_json import flatten j_username = 'ABCD' j_password = '12456' query = '"id = 112233445566"' print query r=requests.get('Url' % query, auth= (j_username,j_password)) print r.json() first_response = r.json() string_data = json.dumps(r.json()) normalized_r = json_normalize(r.json()) print flatten(r.json()) r_flattened = flatten(r.json()) r_flattened_str = json.dumps(flatten(r.json())) print type (flatten(r.json()))Output is coming as below
Output is as below { 'data_0_user-35': u'Xyz', 'data_0_user-34': None, 'data_0_user-37': u'CC', 'data_0_user-36': None, 'data_0_user-31': u'Regular', 'data_0_user-33': None, 'data_0_user-32': None, 'data_0_target-rcyc_id': 0101, 'data_0_to-mail': None, 'data_0_closing-version': None, 'data_0_user-44': None, 'data_0_test-reference': None, 'data_0_request-server': None, 'data_0_target-rcyc_type': u'regular type', 'data_0_project': None, 'data_0_user-01': u'Application Name', 'data_0_user-02': None, 'data_0_user-03': None, ....... ....... ...... ..... }Expected out put is as below
data_0_user-35 data_0_user-34 data_0_user-37 ......... XYZ None CC
I have tried the below code
print pandas.DataFrame(r_flattened.items())And I am getting the output as below. This is not what expected but I feel a step closer.
0 1 0 data_0_user-35 Xyz 1 data_0_user-34 None 2 data_0_user-37 CC 3 data_0_user-36 None 4 data_0_user-31 Regular