时间戳

首页 > 解决方案 > 将数据从 api 保存到数据框

python - 将数据从 api 保存到数据框

问题描述

从 API 获取数据时，我有以下输出：

{'Textbook': [{'Type': 'Chapters', 'Case': 'Ch09', 'Rates': 
[{'Date': '2021- 04-23T00:00:00', 'Rate': 10.0}, {'Date': '2021-04-26T00:00:00', 'Rate': 10.0}, 
{'Date': '2021-04-27T00:00:00', 'Rate': 10.5}, {'Date': '2021-04-28T00:00:00', 'Rate': 10.5}, 
{'Date': '2021-04-29T00:00:00', 'Rate': 10.5}, {'Date': '2021-04-30T00:00:00', 'Rate': 10.0}]}]}

我正在尝试在数据框中获取以下输出：

Date                           Rate
2021- 04-23T00:00:00          10.0
2021-04-26T00:00:00           10.0
2021-04-27T00:00:00           10.5

ETC

我尝试了以下代码：

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['Rate'])
   d1['Date'] = reportDate
   d1['Rate'] = price
df = df.append(pd.DataFrame(d1))
df['Date'] = pd.to_datetime(df['Date'])

但是，我收到以下错误：string indices must be integers对于该行for j in i['Chapters']:

标签： pythonjsonapi

解决方案

下面修复您的代码将解决您的问题。虽然安德烈亚斯的回答是一种蟒蛇方式！

import ast

# Data setup
raw_data="""
{'Textbook': [{'Type': 'Chapters', 'Case': 'Ch09', 'Rates': 
[{'Date': '2021- 04-23T00:00:00', 'Rate': 10.0}, {'Date': '2021-04-26T00:00:00', 'Rate': 10.0}, 
{'Date': '2021-04-27T00:00:00', 'Rate': 10.5}, {'Date': '2021-04-28T00:00:00', 'Rate': 10.5}, 
{'Date': '2021-04-29T00:00:00', 'Rate': 10.5}, {'Date': '2021-04-30T00:00:00', 'Rate': 10.0}]}]}
"""
val=ast.literal_eval(raw_data) # eval to dictionary

解决方法是（请查看评论部分）

l=val ###this is the output from API, added val in this example 
reportDate = [] # moved out of loop to collect the data
price = [] # moved out of loop to collect the data
#df=pd.DataFrame() build the dataframe once all the data is ready
for i in l: # this is dictionary
    #d1 = {} not needed
    
    for j in l[i][0]['Rates']:
        reportDate.append(j['Date'])
        price.append(j['Rate'])
    #d1['Date'] = reportDate
    #d1['Rate'] = price
#df = df.append(pd.DataFrame(d1))
#df['Date'] = pd.to_datetime(df['Date'])
df=pd.DataFrame({'Date':reportDate,"Rate":price})

推荐阅读