Creating and exporting a dataframe from a requested dataset in Python

Question

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.

so you need the dataframe with the columns from your dictionary key right? — Himanshu Poddar
– Himanshu Poddar, Commented Jul 17, 2022 at 12:58

Himanshu Poddar · Accepted Answer · 2022-07-17 13:06:34Z

1

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")

answered Jul 17, 2022 at 13:06

Himanshu Poddar

7,90116 gold badges60 silver badges120 bronze badges

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

5 Comments

noviceprogramer Over a year ago

so when I applied this code to the actual excel file I got a few errors: columns = _validate_or_indexify_columns(contents, columns) and f"{len(columns)} columns passed, passed data had " AssertionError: 1 columns passed, passed data had 5 columns What could be the issue?

Himanshu Poddar Over a year ago

That's because in the answer, as I said the dataframe contains only one column which is a list of dictionary, and you may have had multiple columns

Himanshu Poddar Over a year ago

Can you ask a separate question with your original excel file uploaded and the error code that you are getting. I ll answer it there

noviceprogramer Over a year ago

Ok I will do that

noviceprogramer Over a year ago

stackoverflow.com/questions/73015186/…

Collectives™ on Stack Overflow

Creating and exporting a dataframe from a requested dataset in Python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related