prepend a header to .csv file with python

Question

my current code able to fetch historical data from Google finance but missing the first row to label each column. I want the output as below

Stock,Date,Time,Open, High,Low,Close,Volume CCK,2015-12-30,00:00:00,1.05,1.05,0.99,1.00,157800 CCK,2015-12-31,00:00:00,0.98,1.03,0.98,1.02,55300

 stocklist=['CCK','MSFT','AA','AAPL']
        stocklen=len(stocklist)

for x in range(1,stocklen,1):
    q = GoogleQuote(stocklist[x],'2015-12-21')
    header = ['Stock','Date','Time','Open','High','Low','Close']
    print header                                             
    print q                                                   
    q.write_csv(stocklist[x]+".csv")
    with open(stocklist[x]+".csv",'a') as f:
        w = csv.writer(f)
        w.writerow(['Stock','Date','Time','Open','High','Low','Close'])

However, the output is at the last row as such

CCK,2015-12-30,00:00:00,1.05,1.05,0.99,1.00,157800 CCK,2015-12-31,00:00:00,0.98,1.03,0.98,1.02,55300 Stock,Date,Time,Open, High,Low,Close,Volume

You are appending so it makes sense it is at the end, if you want to prepend you will have to overwrite the file storing all the content and prepending or writing to a tempfile — Padraic Cunningham, Jan 01 '16 at 12:39
i need to do the data analysis with the label in first row. Since I have many .csv files, how to add the label in first row? — bkcollection, Jan 01 '16 at 12:47
@bkcollection Padraic probably wants to say, that since you are appendig to a file (you open it with `a`), but want to place headers at the top, you may want to use temporary file to dump appendings. But if you need just write to csv file, you can do this: with open(stocklist[x]+".csv",'w') as f: w = csv.DictWriter(f, fieldnames=header) w.writerow(...) — vrs, Jan 01 '16 at 13:08
@bkcollection, something like http://stackoverflow.com/questions/31888222/adding-a-new-column-on-csv-with-python/31888320#31888320, after `wr = csv.writer(...` you would write the header then do whatever you want with the rest of teh data — Padraic Cunningham, Jan 01 '16 at 13:13

mhawke · Accepted Answer · 2016-01-02T04:23:31.803

Where does GoogleQuote come from? Is it from quotes.py ???

If so then your code will already produce CSV files with the header line, the only difference being that the first column is labelled Symbol rather than Stock. If you object to that label you can change it like this:

import csv
import datetime
from itertools import repeat

header = ['Stock', 'Date', 'Time', 'Open', 'High', 'Low', 'Close', 'Volume']
stocklist=['CCK','MSFT','AA','AAPL']
start = datetime.datetime(2015, 12, 21)
#end = datetime.datetime(2015, 12, 31)
for stock in stocklist:
    q = GoogleQuote(stock, start.strftime('%F'))
    data = zip(repeat(q.symbol), q.date, q.time, q.open_, q.high, q.low, q.close, q.volume)
    with open('{}.csv'.format(stock), 'w') as f:
        w = csv.writer(f)
        w.writerow(header)
        w.writerows(data)

You could use pandas:

import datetime
import pandas.io.data as web

stocklist=['CCK','MSFT','AA','AAPL']
start = datetime.datetime(2015, 12, 21)
end = datetime.datetime(2015, 12, 31)
for stock in stocklist:
    data = web.DataReader(stock, 'google', start, end)
    data.to_csv('{}.csv'.format(stock))

This will create separate CSV files for each stock for the range of dates inclusive, with header line:

Date,Open,High,Low,Close,Volume

The differences in output between pandas and your code is:

Stock not present as first column in CSV. Since each file is named according to the stock it is not required in the file itself.
Time column not present. Quotes are for a date so the time may not be relevant.

If you really must have stock and time columns these can be added to the data frame.

for stock in stocklist:
    data = web.DataReader(stock, 'google', start, end)
    data.insert(0, 'Time', [ts.time() for ts in data.index])
    data.reset_index(inplace=True)    # make Date a proper column
    data.insert(0, 'Stock', stock)
    data.to_csv('{}.csv'.format(stock), index=False)

Note that the date column is a timestamp, so adding the time column may not be necessary. Instead you could omit addition of the Time column and format the date with:

    data.to_csv('{}.csv'.format(stock), index=False, date_format='%F %T')

I wish I can use pandas. I need to download some stocks from foreign stocks exchange and pandas only support US stocks. — bkcollection, Jan 01 '16 at 14:52
I think that you can access foreign stocks by prefixing the stock code with an exchange code, e.g. LON:BARC pulls quotes for Barclays bank from the LSE. — mhawke, Jan 02 '16 at 00:25
@bkcollection: updated answer to use `GoogleQuote` class... I think. — mhawke, Jan 02 '16 at 02:32
@mhwake, it works!!Change q = GoogleQuote(stock,start.strftime("%Y-%m-%d")) working perfectly. Thanks — bkcollection, Jan 03 '16 at 13:00
@bkcollection: good. You might want to upvote and accept the answer then ;) — mhawke, Jan 03 '16 at 13:02

prepend a header to .csv file with python

1 Answers1