Convert data from API to Dataframe then to excel

Question

I am iterating through a single column of an excel file. Per iteration, a cell value of the column is added between 2 halves of an invalid API URL to form a valid URL that I can then request data from. See first 9 rows of data below.

Current Code:

import pandas as pd
import json
import requests

file = pd.read_excel('stockgroups.xlsx')
# Iterating the 9 rows of data shown in the image (Total of 4500 rows in file)
file = file.iloc[:9]


tickers = list(file["Symbol"])

for i in tickers:
    response = requests.get('https://financialmodelingprep.com/api/v3/stock-price-change/' + str(i) + '?apikey=YOURAPIKEY')
alldata = json.loads(response.text)


print(alldata)

Current Output

[{'symbol': 'ISR', '1D': -7.66, '5D': -3.23, '1M': 7.14, '3M': -9.09, '6M': -21.05, 'ytd': -26.83, '1Y': -55.22, '3Y': -26.83, '5Y': -50.82, '10Y': -71.15, 'max': -94.12}]
[{'symbol': 'CVM', '1D': -13.02, '5D': -25.11, '1M': -17.94, '3M': 3.41, '6M': -43.58, 'ytd': -56.620000000000005, '1Y': -56.34, '3Y': -46.9, '5Y': 38.02, '10Y': -96.29, 'max': -99.89}]
[{'symbol': 'NNVC', '1D': -1.01, '5D': -6.19, '1M': -10.45, '3M': 12.57, '6M': -24.81, 'ytd': -49.74, '1Y': -55.73, '3Y': -58.96, '5Y': -93.06, '10Y': -95.06, 'max': -94.37}]
[{'symbol': 'AIM', '1D': -3.05, '5D': -4.32, '1M': -9.05, '3M': -34.22, '6M': -16.28, 'ytd': -28.470000000000002, '1Y': -64.58, '3Y': -81.81, '5Y': -96.51, '10Y': -99.63, 'max': -99.94}]
[{'symbol': 'NAVB', '1D': 1.43, '5D': 7.68, '1M': -7.91, '3M': -29.3, '6M': -30.7, 'ytd': -41.67, '1Y': -61.12, '3Y': 20.67, '5Y': -92.71, '10Y': -99.23, 'max': -99.46}]
[{'symbol': 'OGEN', '1D': 0.0, '5D': -1.23, '1M': 15.23, '3M': -9.03, '6M': -24.85, 'ytd': -26.450000000000003, '1Y': -46.82, '3Y': -26.45, '5Y': -91.14, '10Y': -98.62, 'max': -99.96}]
[{'symbol': 'PLX', '1D': 1.85, '5D': 0.0, '1M': 1.85, '3M': -17.29, '6M': 35.8, 'ytd': 20.880000000000003, '1Y': -27.15, '3Y': -71.79, '5Y': -85.53, '10Y': -98.07, 'max': -99.87}]
[{'symbol': 'PTN', '1D': -0.37, '5D': -6.55, '1M': -6.55, '3M': -38.41, '6M': -33.9, 'ytd': -49.81, '1Y': -46.86, '3Y': -72.63, '5Y': -38.41, '10Y': -61.83, 'max': -99.71}]
[{'symbol': 'SYN', '1D': 2.27, '5D': -30.54, '1M': -12.26, '3M': -33.32, '6M': -35.88, 'ytd': -44.43, '1Y': -66.66, '3Y': -69.69, '5Y': -99.1, '10Y': -99.79, 'max': -99.96}]

Problem: I would like to get this data into a dataframe with the keys as columns and the values as rows. Then, export the dataframe to a new excel file. I have been experiencing difficulty formatting the data as described.

user_13525512 · Answer

I think there is an indentation problem in your question and that your print statement is actually in the loop.

You can append all your responses in one list of dict and convert the whole into a df:

import pandas as pd
import json
import requests

file = pd.read_excel('stockgroups.xlsx')
# Iterating the 9 rows of data shown in the image (Total of 4500 rows in file)
file = file.iloc[:9]


tickers = list(file["Symbol"])

alldata = []

for i in tickers:
    response = requests.get('https://financialmodelingprep.com/api/v3/stock-price-change/' + str(i) + '?apikey=YOURAPIKEY')
    alldata += json.loads(response.text)

df = pd.DataFrame(alldata)
print(df)

Output:

  symbol     1D     5D     1M     3M     6M    ytd     1Y     3Y     5Y    10Y    max
0    ISR  -7.66  -3.23   7.14  -9.09 -21.05 -26.83 -55.22 -26.83 -50.82 -71.15 -94.12
1    CVM -13.02 -25.11 -17.94   3.41 -43.58 -56.62 -56.34 -46.90  38.02 -96.29 -99.89
2   NNVC  -1.01  -6.19 -10.45  12.57 -24.81 -49.74 -55.73 -58.96 -93.06 -95.06 -94.37
3    AIM  -3.05  -4.32  -9.05 -34.22 -16.28 -28.47 -64.58 -81.81 -96.51 -99.63 -99.94
4   NAVB   1.43   7.68  -7.91 -29.30 -30.70 -41.67 -61.12  20.67 -92.71 -99.23 -99.46
5   OGEN   0.00  -1.23  15.23  -9.03 -24.85 -26.45 -46.82 -26.45 -91.14 -98.62 -99.96
6    PLX   1.85   0.00   1.85 -17.29  35.80  20.88 -27.15 -71.79 -85.53 -98.07 -99.87
7    PTN  -0.37  -6.55  -6.55 -38.41 -33.90 -49.81 -46.86 -72.63 -38.41 -61.83 -99.71
8    SYN   2.27 -30.54 -12.26 -33.32 -35.88 -44.43 -66.66 -69.69 -99.10 -99.79 -99.96

I think you can even replace alldata += json.loads(response.text) with alldata += response.json()

Convert data from API to Dataframe then to excel

1 Answers