Errors while Creating and Exporting DataFrame to Excel

Question

I have an excel file with 5 columns and 4500 rows of data. The image is a sample of 5 rows from the file that will be iterated through in this code. ' allstockdata.xlsx

import pandas as pd
import json
import requests





file = pd.read_excel('allstockdata.xlsx')
data = []
tickers = list(file["Symbol"])
for i in tickers:

    i = str(i)
    # adds symbol between 2 halves of an incomplete url to form a valid url to request data from
    alldata = json.loads(requests.get(urlhalf1 + i + urlhalf2).text)
# nests dataset in list
data.append(alldata)

df = pd.DataFrame(data, columns = ['data'])
df = pd.concat([df, df.pop('data').apply(pd.Series)], axis = 1)
df.to_excel('allstockdata.xlsx')

Error: columns = _validate_or_indexify_columns(contents, columns)

Error: f"{len(columns)} columns passed, passed data had " AssertionError: 1 columns passed, passed data had 5 columns

Question: How do I fix these errors in my code? How can I skip iterations that form invalid urls(no available data on specific symbol)? Considering the size of the excel file (5 columns, 4500 rows), should I iterate in increments?

Please ask for clarification if necessary

Can you share the excel file please from which you are reading! — Himanshu Poddar
– Himanshu Poddar, Commented Jul 17, 2022 at 22:06

Himanshu Poddar · Accepted Answer · 2022-07-18 10:48:56Z

1

You need to append allData to the data list. For testing purpose you can try my code for first 10 rows, I have edited accordingly.

import pandas as pd
import json
import requests

file = pd.read_excel('allstockdata.xlsx')
file = file.iloc[:10] # For testing purpose try with first 10 rows 
data = []
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)
    data.append(alldata)

df = pd.DataFrame(data, columns = ['data'])
df = pd.concat([df, df.pop('data').apply(pd.Series)], axis = 1)
df.to_excel('allstockdata.xlsx')

This gives us the expected output

answered Jul 18, 2022 at 10:48

Himanshu Poddar

7,90116 gold badges60 silver badges120 bronze badges

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

1 Comment

noviceprogramer Over a year ago

it has been done

Collectives™ on Stack Overflow

Errors while Creating and Exporting DataFrame to Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related