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.
CodePudding user response：
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()