saving data from api to dataframe

Question

I have the following output from an API:

[{'Chapters': {'id': book, 'name': firstbook'}, 'periodPrices': [{'reportDate': '2021-06-01T15:28:00', 'term': '3Q21', 'price': 10.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '4Q21', 'price': 10.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '1Q22', 'price': 10.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '2Q22', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '3Q22', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '4Q22', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '1Q23', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '2Q23', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '2H21', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '1H22', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '2H22', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': '1H23', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': 'Cal 22', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': 'Cal 23', 'price': 0.0}, {'reportDate': '2021-06-01T15:28:00', 'term': 'Cal 24', 'price': 0.0}]}]

I am trying to get the following output in a dataframe:

 Date                     id               Term            price      
2021- 06-01T00:00:00      book             3Q21             10.0
2021-06-01T00:00:00       book             4Q21             10.0
2021-06-01T00:00:00       book             1Q22             10.5
etc

I tried the following code:

l=parsed ###this is the output from API 
df=pd.DataFrame()
for i in l:
   d1 = {}
   reportDate = []
    price = []
 for j in i['Chapters']:
     reportDate.append(j['Date'])
     price.append(j['price'])
 d1['Date'] = reportDate
 d1['Rate'] = price
 df = df.append(pd.DataFrame(d1))
 df['Date'] = pd.to_datetime(df['Date'])

However, I get the following error: string indices must be integers for the line for j in i['Chapters']:

David · Accepted Answer · 2021-06-15 19:47:21Z

1

You can do this with two lines

df = pd.DataFrame(l[0]["periodPrices"])
df["id"] = l[0]["Chapters"]["id"]

beacuse the DataFrame method is so powerful!

answered Jun 15, 2021 at 19:47

David

405 bronze badges

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

Comments

not_speshal · Accepted Answer · 2021-06-15 19:31:01Z

0

I don't see what the id column is needed for. So see if you can work with this:

df = pd.DataFrame(l[0]["periodPrices"])
>>> df
             reportDate    term  price
0   2021-06-01T15:28:00    3Q21   10.0
1   2021-06-01T15:28:00    4Q21   10.0
2   2021-06-01T15:28:00    1Q22   10.0
3   2021-06-01T15:28:00    2Q22    0.0
4   2021-06-01T15:28:00    3Q22    0.0
5   2021-06-01T15:28:00    4Q22    0.0
6   2021-06-01T15:28:00    1Q23    0.0

You access the main dict using l[0]. And then access the dict of period prices using l[0]['periodPrices']

If you want the id column, you can add the following line:

df["id"] = l[0]["Chapters"]["id"]

answered Jun 15, 2021 at 19:31

not_speshal

23.2k2 gold badges18 silver badges33 bronze badges

Comments

DGMaxime · Accepted Answer · 2021-06-15 19:35:18Z

0

l = data  ###this is the output from API
df = pd.DataFrame()
for i in l:
    d1 = {}
    reportDate = []
    price = []
for j in i['periodPrices']: # error here
    reportDate.append(j['reportDate']) # error here
    price.append(j['price'])
d1['Date'] = reportDate
d1['Rate'] = price
df = df.append(pd.DataFrame(d1))
df['Date'] = pd.to_datetime(df['Date'])

answered Jun 15, 2021 at 19:35

DGMaxime

211 silver badge3 bronze badges

Collectives™ on Stack Overflow

saving data from api to 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