I need to convert data from an api call into a dataframe. After calling the api I get the following json object:
{'responseID': 149882407,
'surveyID': 9711255,
'surveyName': 'NPS xx yy',
'ipAddress': '170.231.171.253',
'timestamp': '20 Aug, 2022 01:37:29 PM PET',
'location': {'country': None,
'region': '',
'latitude': -12.0439,
'longitude': -77.0281,
'radius': 0.0,
'countryCode': 'PE'},
'duplicate': False,
'timeTaken': 6,
'responseStatus': 'Completed',
'externalReference': '',
'customVariables': {'custom1': None,
'custom2': None,
'custom3': None,
'custom4': None,
'custom5': None},
'language': 'Spanish (Latin America)',
'currentInset': '',
'operatingSystem': 'ANDROID1',
'osDeviceType': 'MOBILE',
'browser': 'CHROME10',
'responseSet': [{'questionID': 106457509,
'questionDescription': '',
'questionCode': 'Q3',
'questionText': '¿El vendedor te recomendó algún producto adicional? ',
'imageUrl': None,
'answerValues': [{'answerID': 571204020,
'answerText': 'SI',
'value': {'scale': '1',
'other': '',
'dynamicExplodeText': '',
'text': '',
'result': '',
'fileLink': '',
'weight': 0.0}}]},
{'questionID': 106457510,
'questionDescription': '{detractor:Nada probable,promoter:Altamente probable}',
'questionCode': 'Q8',
'questionText': '¿Cuán probable es que recomiendes las tiendas Samsung a un familiar o amigo?',
'imageUrl': None,
'answerValues': [{'answerID': 571204032,
'answerText': '10',
'value': {'scale': '11',
'other': '',
'dynamicExplodeText': '',
'text': '',
'result': '',
'fileLink': '',
'weight': 0.0}}]},
{'questionID': 106457511,
'questionDescription': '',
'questionCode': 'Q6',
'questionText': '¿En qué fallamos?',
'imageUrl': None,
'answerValues': []},
{'questionID': 106457512,
'questionDescription': '',
'questionCode': 'Q4',
'questionText': '¿En qué debemos mejorar?',
'imageUrl': None,
'answerValues': []},
{'questionID': 106457513,
'questionDescription': '',
'questionCode': 'Q5',
'questionText': '¿Por qué nos felicitas?',
'imageUrl': None,
'answerValues': [{'answerID': 571204035,
'answerText': '',
'value': {'scale': '',
'other': '',
'dynamicExplodeText': '',
'text': '',
'result': '',
'fileLink': '',
'weight': 0.0}}]}],
'utctimestamp': 29}
The json file is a list of dictionaries, and each dictionary (as the one above) represents one response from a client. It is the response from a client, to a five question survey. The information that I want to scrape is: responseID, surveyID, ipAddress, timestamp, latitude, longitude, questionText (inside responseSet), scale and text (inside answerValues). So it would look something like this:
The json object I showed represents one of the rows from the dataframe. First I tried pd.json_normalize(), which correctly scrapes responseID, surveyID, ipAddress, timestamp, latitude, longitude and timeTaken, but since responseSet is a list, it just remains a list within the dataframe. I tried to use to_list() to expand this column of lists into multiple columns, but this quickly got out of hand since there are dicts, within dicts, within lists, within dicts. In other words, it's very heavily nested and I need to extract the answers to five questions, which answer may be in text or scale. So I figured this wasn't the most pythonic way to do so.
Lastly I used json_normalize with answerValues as the record path, which gave me a dataframe in which every row was an individual answer. So I had 5 rows per client (sometimes less, since it wouldn't scrape the answer if the client left the question unanswered). Next I used pivot to obtain a dataframe that was closer to what I wanted, and finally merged with my previous dataframe.
def transform_json(data):
flatten_json = pd.json_normalize(data)
answers_long = pd.json_normalize(data,
record_path= ["responseSet", "answerValues"],
meta= ["responseID",
["responseSet", "questionText"]])
answers_long["value"] = answers_long["value.text"] + answers_long["value.scale"]
answers = answers_long.pivot(index= "responseID",
columns= "responseSet.questionText",
values= "value").reset_index()
df = flatten_json.merge(answers,
how= "left",
on= "responseID")
return df
I wonder what would be the best way to achieve my task since I don't think this was the best, maybe there is a way to completely flatten the json file, including the nested lists and dictionaries.