Issue while converting API output to Pandas Dataframe

Question

I have API output in the below format.

reponse.text

'{"position": {"lat": 1.352083, "lon": 103.819836}, "mapView": {"N": 1.4784001, "E": 104.0945001, "S": 1.1496, "W": 103.594}}\n'

I want to convert this output to pandas dataframe .

My code

import json
d = json.loads(response.text)
df = pd.DataFrame(d)

Current Output

    position    mapView
E   NaN            104.0945
N   NaN             1.4784
S   NaN             1.1496
W   NaN            103.5940
lat 1.352083        NaN
lon 103.819836      NaN

My Expected Output

lat       lon
1.352083  103.819836

How can this be achieved in python?

user96564 · Accepted Answer · 2019-11-14 14:36:02Z

2

You are very close. import json_normalize from pandas and then you can simply use

from pandas.io.json import json_normalize

jsonData = json.loads(response.text)

df = json_normalize(jsonData)

print(df)

position.lat  position.lon  mapView.N  mapView.E  mapView.S  mapView.W
    1.352083    103.819836     1.4784   104.0945     1.1496    103.594

then simply delete extra columns and rename it properly.

answered Nov 14, 2019 at 14:36

user96564

1,6175 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

qaiser · Accepted Answer · 2019-11-14 14:36:26Z

0

import pandas as pd
import json
d = json.loads(response.text)

df =pd.DataFrame([d['position']])

answered Nov 14, 2019 at 14:36

qaiser

2,9083 gold badges21 silver badges34 bronze badges

Comments

gru · Accepted Answer · 2019-11-14 14:38:48Z

0

you are passing as argument the whole dictionary created by the json parser, while you only want to use the position key.

please go check the DataFrame constructor documentation page here for additional parameters.

>>> import pandas as pd
>>> import json
>>> api_resp = json.loads('{"position": {"lat": 1.352083, "lon": 103.819836}, "mapView": {"N": 1.4784001, "E": 104.0945001, "S": 1.1496, "W": 103.594}}\n')
>>> api_resp
{'position': {'lat': 1.352083, 'lon': 103.819836}, 'mapView': {'N': 1.4784001, 'E': 104.0945001, 'S': 1.1496, 'W': 103.594}}
>>> df = pd.DataFrame(data=api_resp['position'], index=[0])
>>> df
        lat         lon
0  1.352083  103.819836

answered Nov 14, 2019 at 14:38

gru

4,5791 gold badge25 silver badges23 bronze badges

Collectives™ on Stack Overflow

Issue while converting API output to Pandas Dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related