How to write one array value at a time (dataframe to csv)?

Question

This is working great, but I have thousands of rows to write to csv. It takes hours to finish and sometimes my connection will drop and prevent the query from finishing.

import pandas as pd
from yahooquery import Ticker

symbols = ['AAPL','GOOG','MSFT'] 
faang = Ticker(symbols)
faang.summary_detail
df = pd.DataFrame(faang.summary_detail).T
df.to_csv('output.csv', mode='a', index=True, header=True)

Above is only three symbols: symbols = ['AAPL','GOOG','MSFT'], but imagine there are 50,000 symbols. What I am currently doing is breaking it down into 500 symbols at a time:

import pandas as pd
from yahooquery import Ticker

symbols = ['AAPL','GOOG','MSFT'] #imagine here are 500 symbols.
faang = Ticker(symbols)
faang.summary_detail
df = pd.DataFrame(faang.summary_detail).T
df.to_csv('summary_detailsample.csv', mode='a', index=True, header=True)

symbols = ['BABA','AMD','NVDA'] #imagine here are 500 symbols.
faang = Ticker(symbols)
faang.summary_detail
df = pd.DataFrame(faang.summary_detail).T
df.to_csv('output.csv', mode='a', index=True, header=True)

#Repeat the last five lines 100+ times for 50,000 symbols (500 symbols x 100 blocks of code).

So the last five lines of code I copy 100+ times to append/write all the symbols' data. It works great, but I would like to not have 500 lines of code. I would like it to append a record one symbol at a time and throw all the 50,000 symbols in there one time (not have to copy code over and over).

Perhaps most importantly I would like the first symbol's column headers to be followed by the rest of the symbols. Some of the symbols will have 20 columns and others will have 15 or so. The data ends up not matching. The rows won't match other rows, etc.

You can store the dataframes created from .summary_detail into a list and then as a last step of your script you can do pd.concat to concatenate them into one dataframe (and then save that dataframe) — Andrej Kesely
– Andrej Kesely, Commented Nov 30, 2022 at 22:53
Thank you @Andrej Kesely. Got it to work. Now trying to figure this out how to error handle null/empty values: stackoverflow.com/q/74645315 If my question was clear, please upvote it :) — HTMLHelpMe
– HTMLHelpMe, Commented Dec 1, 2022 at 16:43

Carson Whitley · Accepted Answer · 2022-12-01 14:57:30Z

2

Try the below looping through the list of tickers, appending the dataframes as you loop onto the CSV.

import pandas as pd
from yahooquery import Ticker


symbols = [#All Of Your Symbols Here]
for tick in symbols:
    faang = Ticker(tick)
    faang.summary_detail
    df = pd.DataFrame(faang.summary_detail).T
            
    df.to_csv('summary_detailsample.csv', mode='a', index=True, header=False)

edited Dec 1, 2022 at 14:57

answered Dec 1, 2022 at 1:45

Carson Whitley

1339 bronze badges

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

4 Comments

HTMLHelpMe Over a year ago

Thank you so much, @Carson Whiteley. The code did run, but got an ValueError: If using all scalar values, you must pass an index error when trying to run 30k symbols and no output file was created. Then I got TypeError: cannot concatenate object of type '<class 'list'>'; only Series and DataFrame objs are valid when trying to run 10 symbols. Just to clarify, I am trying to get the file to be created on the first ticker and each ticker be appended one at a time. So a .csv file will be created right away and every second or so tickers will be appended to the .csv file over hours.

Carson Whitley Over a year ago

Hey @HTMLHelpMe, if you're looking to do it one at a time in terms of writing the df to a CSV. Try putting the to_csv() method inside the for loop. That way, each ticker will be appended onto the CSV as it's looping through. Note, you may want to set header to False in that case to avoid duplicate header rows.

HTMLHelpMe Over a year ago

Thank you so much @Carson Whitley! Works great! Now I have a related question. I altered the code to grab only certain columns (header = ["Column1", "Column2"...] below the symbols = [] line and columns=header in the df.to_csv line) and am getting KeyError: "['Column2'] not in index". So it writes to csv until that symbols that does not have Column2 data. How do I have it continue and just put nulls/blanks for empty/missing data?

HTMLHelpMe Over a year ago

Here's another post: stackoverflow.com/q/74645315 for above. Much appreciate your help. If my question was clear, please upvote it :)

Collectives™ on Stack Overflow

How to write one array value at a time (dataframe to csv)?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related