How to create a dataframe out from API result in Python

Question

My Request.getcall from Python gives me output like below

response.text

'{"results":[{"place":{"type":"coord","value":"44.164:28.641","lat":44.164,"lon":28.641,"tz":"Europe/Bucharest"},"measures":[{"ts":1575331200000,"date":"2019-12-03","temperature_2m":11.78,"temperature_2m_min":11.75,"temperature_2m_max":12.46,"windspeed":3.25,"direction":"SSW","wind_gust":5.43,"relative_humidity_2m":88,"sea_level_pressure":1014,"sky_cover":"cloudy","precipitation":0.0,"snow_depth":0,"thunderstorm":"N","fog":"M"}]},{"place":{"type":"coord","value":"53.546:9.98","lat":53.546,"lon":9.98,"tz":"Europe/Berlin"},"measures":[{"ts":1575331200000,"date":"2019-12-03","temperature_2m":-0.55,"temperature_2m_min":-0.8,"temperature_2m_max":-0.35,"windspeed":3.65,"direction":"WSW","wind_gust":8.62,"relative_humidity_2m":88,"sea_level_pressure":1025,"sky_cover":"mostly_clear","precipitation":0.0,"snow_depth":0,"thunderstorm":"N","fog":"M"}]}]}'

I want to make dataframe df out this

coord   date    direction   fog precipitation   relative_humidity_2m    sea_level_pressure  sky_cover   snow_depth  temperature_2m  temperature_2m_max  temperature_2m_min  thunderstorm    ts  wind_gust   windspeed
44.164:28.641   3/12/2019   SSW M                0                            88            1014        cloudy          0             11.78           12.46                  11.75              N      1.57533E+12     5.43           3.25
53.546:9.98 3/12/2019       WSW M                0                            88            1025        mostly_clear    0             -0.55           -0.35                     -0.8                    N      1.57533E+12    8.62            3.65

Also I two scenarios :

response.status==200 the API gives values in response.textthen I want store this dataframe response.status!=200 the API gives no values response.text then I want populate allother fields as null except the coord

How can this be done?

Normal data gives the following dict:

{'results': [{'place': {'type': 'coord',
    'value': '44.164:28.641',
    'lat': 44.164,
    'lon': 28.641,
    'tz': 'Europe/Bucharest'},
'measures': [{'ts': 1575331200000,
    'date': '2019-12-03',
    'temperature_2m': 11.78,
    'temperature_2m_min': 11.75,
    'temperature_2m_max': 12.46,
    'windspeed': 3.25,
    'direction': 'SSW',
    'wind_gust': 5.43,
    'relative_humidity_2m': 88,
    'sea_level_pressure': 1014,
    'sky_cover': 'cloudy',
    'precipitation': 0.0,
    'snow_depth': 0,
    'thunderstorm': 'N',
    'fog': 'M'}]},
{'place': {'type': 'coord',
    'value': '53.546:9.98',
    'lat': 53.546,
    'lon': 9.98,
    'tz': 'Europe/Berlin'},
'measures': [{'ts': 1575331200000,
    'date': '2019-12-03',
    'temperature_2m': -0.55,
    'temperature_2m_min': -0.8,
    'temperature_2m_max': -0.35,
    'windspeed': 3.65,
    'direction': 'WSW',
    'wind_gust': 8.62,
    'relative_humidity_2m': 88,
    'sea_level_pressure': 1025,
    'sky_cover': 'mostly_clear',
    'precipitation': 0.0,
    'snow_depth': 0,
    'thunderstorm': 'N',
    'fog': 'M'}]}]}

Pyd · Accepted Answer · 2019-11-26 06:14:01Z

5

import json
from pandas.io.json import json_normalize
import pandas as pd

response_data = json.loads(responese.text)
df = json_normalize(response_data['results'])

df1 = json_normalize([df["measures"][i][0] for i in range(0, df.shape[0])])

final_df = pd.concat([df, df1], axis=1)

final_df.drop("measures", axis=1, inplace=True)

edited Nov 26, 2019 at 6:14

Pyd

6,16919 gold badges59 silver badges117 bronze badges

answered Nov 26, 2019 at 6:12

sukhwal13

662 bronze badges

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

Comments

chmielcode · Accepted Answer · 2019-11-26 06:19:55Z

4

You could try DataFrame.from_records. This solution works as long as there are no common keys in "place" and "measures".

import json

d = json.loads(responese.text)

def update(a, b):
    c = dict()
    c.update(a)
    c.update(b)
    return c


pd.DataFrame.from_records(
    (
        update(result['place'], measure)
        for result in d['results']
        for measure in result['measures']
    )
)

Result:

    type    value   lat lon tz  ts  date    temperature_2m  temperature_2m_min  temperature_2m_max  windspeed   direction   wind_gust   relative_humidity_2m    sea_level_pressure  sky_cover   precipitation   snow_depth  thunderstorm    fog
0   coord   44.164:28.641   44.164  28.641  Europe/Bucharest    1575331200000   2019-12-03  11.78   11.75   12.46   3.25    SSW 5.43    88  1014    cloudy  0.0 0   N   M
1   coord   53.546:9.98 53.546  9.980   Europe/Berlin   1575331200000   2019-12-03  -0.55   -0.80   -0.35   3.65    WSW 8.62    88  1025    mostly_clear    0.0 0   N   M

edited Nov 26, 2019 at 6:19

answered Nov 26, 2019 at 6:10

chmielcode

1111 silver badge5 bronze badges

4 Comments

sneha nair Over a year ago

Will this code break when response.status !=200. I want to populate the dataframe with NULL values when there is no response from the API as well

chmielcode Over a year ago

I think it will. You should check the response status and handle it separately. I also updated the answer with json.loads instead of eval, which I used to quickly work out the DF creation.

sneha nair Over a year ago

for the reponse code !=200 , it breaks with below reason response.text 'Invalid coordinates'. Do you know how can handle that. Because I need run this set of values

chmielcode Over a year ago

For the second case, if response.code != 200 I would do pd.DataFrame([[np.nan] * len(columns)], columns=columns), where np is numpy imported as np and columns is a list of desired column names. Alternatively try json.dumps and react with the above to json.JSONDecodeError exception.

Pyd · Accepted Answer · 2019-11-26 06:08:39Z

2

d = eval(response.text)

columns_needed = ['coord', 'date', 'direction', 'fog', 'precipitation',
               'relative_humidity_2m', 'sea_level_pressure', 'sky_cover', 'snow_depth',
               'temperature_2m', 'temperature_2m_max', 'temperature_2m_min',
           'thunderstorm', 'ts', 'wind_gust', 'windspeed']

li = []
for i in d.get('results'):
    temp_d = dict()
    place = i.get('place')
    measure = i.get('measures')[0]
    temp_d[place.get('type')] = place.get('value')
    temp_d.update(measure)
    li.append(temp_d)
final_df = pd.DataFrame(li)
final_df = final_df.loc[:,columns_needed]

print(final_df)

answered Nov 26, 2019 at 6:08

Pyd

6,16919 gold badges59 silver badges117 bronze badges

4 Comments

sneha nair Over a year ago

Will this code break when response.status !=200. I want to populate the dataframe with NULL values when there is no response from the API as well

Pyd Over a year ago

post a sample response.text when the response.status !=200

sneha nair Over a year ago

response.text 'Invalid coordinates'

Pyd Over a year ago

if response.text is differen, you will not get a dictionary in eval(response.text) , better add a condition like like the function only when response.status == 200 for second case create a duplicate dataframe with np.nan values

Collectives™ on Stack Overflow

How to create a dataframe out from API result in Python

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related