I have an .xlsx file that contains 5 columns and 4500 rows of data. The column with the header "Symbols" is of interest. For this Question I made a Column containing five cell values from the "Symbols" column in the .xlsx file. Currently, I can request data that shows stock price changes over different periods of time per cell value in the column.
| Symbols |
|---|
| AAPL |
| AMZN |
| TSLA |
| MSFT |
| NVDA |
import pandas as pd
import json
import requests
data = pd.read_excel('allstockdata.xlsx')
tickers = list(data["Symbols"])
# Iterating through cell values in the "Symbol" Column
for i in tickers:
i = str(i)
# Adding cell value between two halves of incomplete url to form valid url
alldata = json.loads(requests.get(urlhalf1 i urlhalf2).text)
# prints metrics for current cell value in iteration
print(alldata)
Current Output:
[{'symbol': 'AAPL', '1D': 1.15, '5D': 3.09, '1M': 15.46, '3M': -9.03, '6M': -11.56, 'ytd': -17.49, '1Y': 1.14, '3Y': 192.73, '5Y': 301.63, '10Y': 592.67, 'max': 115415.38}]
[{'symbol': 'AMZN', '1D': 2.64, '5D': -0.46, '1M': 9.54, '3M': -25.68, '6M': -28.55, 'ytd': -33.36, '1Y': -37.46, '3Y': 12.37, '5Y': 124.85, '10Y': 951.39, 'max': 113450.0}]
[{'symbol': 'TSLA', '1D': 0.74, '5D': -4.77, '1M': 12.65, '3M': -28.29, '6M': -30.11, 'ytd': -39.97, '1Y': 10.7, '3Y': 1320.51, '5Y': 1026.9, '10Y': 9916.69, 'max': 14966.95}]
[{'symbol': 'MSFT', '1D': 1.04, '5D': -3.36, '1M': 4.8, '3M': -8.48, '6M': -15.18, 'ytd': -23.31, '1Y': -8.65, '3Y': 84.82, '5Y': 249.99, '10Y': 772.01, 'max': 256620.0}]
[{'symbol': 'NVDA', '1D': 2.54, '5D': 1.52, '1M': 1.03, '3M': -27.64, '6M': -39.14, 'ytd': -47.660000000000004, '1Y': -16.85, '3Y': 278.62, '5Y': 288.04, '10Y': 5353.98, 'max': 41378.95}]
Question:
- From here, how can I get the datasets from each iteration into one DataFrame with the Keys in the nested dictionary as Columns and the Values as the Rows?
- How can I export the final DataFrame into the original excel file (allstockdata.xlsx) so that the new data is aligned with the original data? (Should still have same # of rows) Let me know if further clarification is needed. I am fairly new to programming.
CodePudding user response：
I expect that you have the data in a list as
data = [
[{'symbol': 'AAPL', '1D': 1.15, '5D': 3.09, '1M': 15.46, '3M': -9.03, '6M': -11.56, 'ytd': -17.49, '1Y': 1.14, '3Y': 192.73, '5Y': 301.63, '10Y': 592.67, 'max': 115415.38}],
[{'symbol': 'AMZN', '1D': 2.64, '5D': -0.46, '1M': 9.54, '3M': -25.68, '6M': -28.55, 'ytd': -33.36, '1Y': -37.46, '3Y': 12.37, '5Y': 124.85, '10Y': 951.39, 'max': 113450.0}],
[{'symbol': 'TSLA', '1D': 0.74, '5D': -4.77, '1M': 12.65, '3M': -28.29, '6M': -30.11, 'ytd': -39.97, '1Y': 10.7, '3Y': 1320.51, '5Y': 1026.9, '10Y': 9916.69, 'max': 14966.95}],
[{'symbol': 'MSFT', '1D': 1.04, '5D': -3.36, '1M': 4.8, '3M': -8.48, '6M': -15.18, 'ytd': -23.31, '1Y': -8.65, '3Y': 84.82, '5Y': 249.99, '10Y': 772.01, 'max': 256620.0}],
[{'symbol': 'NVDA', '1D': 2.54, '5D': 1.52, '1M': 1.03, '3M': -27.64, '6M': -39.14, 'ytd': -47.660000000000004, '1Y': -16.85, '3Y': 278.62, '5Y': 288.04, '10Y': 5353.98, 'max': 41378.95}],
]
df = pd.DataFrame(data, columns = ['data'])
Once the values in lists are read as dictionary in dataframe we can convert the dictionaies obtained to columns using pandas.Series.apply or pandas.json_normalize
df = pd.concat([df, df.pop('data').apply(pd.Series)], axis = 1)
This gives us the expected output :
symbol 1D 5D 1M 3M ... 1Y 3Y 5Y 10Y max
0 AAPL 1.15 3.09 15.46 -9.03 ... 1.14 192.73 301.63 592.67 115415.38
1 AMZN 2.64 -0.46 9.54 -25.68 ... -37.46 12.37 124.85 951.39 113450.00
2 TSLA 0.74 -4.77 12.65 -28.29 ... 10.70 1320.51 1026.90 9916.69 14966.95
3 MSFT 1.04 -3.36 4.80 -8.48 ... -8.65 84.82 249.99 772.01 256620.00
4 NVDA 2.54 1.52 1.03 -27.64 ... -16.85 278.62 288.04 5353.98 41378.95
[5 rows x 12 columns]
Now you can write the data to the xlsx file
df.to_excel("allstockdata.xlsx")