CODEWITHSUNDEEP
pythonjsonpandasapirequest
2837a
2837a

Reputation: 53

Python JSON multiple requests get response save to dataframe

Input:

I send multiple get requests with two parameters as lists (Year and Month) using API to import data

My sample code:

import grequests
import json
import requests 
import io
import pandas as pd
from pandas import json_normalize 

api_key = <api_key>
url = <url>
headersAuth = {
    'Authorization': 'Bearer ' + api_key,
}

years_list = ['2020', '2021']
month_list = ['1','2']

#Create an array of urls
urls = [url + "Month=" + str(i) + "&Year=" + str(j) for i in month_list for j in years_list]

#Preapare requests
rs = (grequests.get(u, headers={'Authorization': 'Bearer ' + api_key}) for u in urls)
response = grequests.map(rs)
json_ = [r.json() for r in response]

But after json_ step I stuck, because I'm not sure how to parse this further in the right way

After running my script I get a format that looks like this

[
{'ID': 3473, 'Month': 1, 'Year': 2020, 'Sold': 1234}, 
{'ID': 3488, 'Month': 1, 'Year': 2020, 'Sold': 1789}]
... etc.

Output:

I would like to export it to pandas dataframe and then to xlsx or csv file as a normal column view I'm probably missing some basics here, but can't process it further

I tried several things after that:

  1. Use json.dumps jsonStr = json.dumps(json_)
  2. Then convert it to listOfDictionaries = json.loads(jsonStr)
  3. Then data_tuples = list(zip(listOfDictionaries))
  4. df = pd.DataFrame(data_tuples)

But I couldn't get a desired output. I would appreciate your help on this.

Upvotes: 0

Views: 701

Answers (1)

piRSquared
piRSquared

Reputation: 294238

pandas.DataFrame.from_records

import pandas as pd

data = [
{'ID': 3473, 'Month': 1, 'Year': 2020, 'Sold': 1234}, 
{'ID': 3488, 'Month': 1, 'Year': 2020, 'Sold': 1789}]

pd.DataFrame.from_records(data)

     ID  Month  Year  Sold
0  3473      1  2020  1234
1  3488      1  2020  1789

Upvotes: 2

Related Questions