Andrea Andrea - 6 months ago 24
Python Question

Saving to_csv read just the columns, not the rows

I'm stuck with reading all the rows of a csv file and save into a csv files (I'm using pandas 0.17.1).

I've a list of tickers inserted into a csv file: they are inserted into each column, like this:


Column A: AAPL / Column B:TSLA / Column C: EXPD... and so on.


Now, I've to add 3000 new tickers to this list, and so I change the orientation of the csv, bringing every ticker into each row of the first column, like this:


Column A

AAPL

TSLA

EXPD

...and so on.


The issue is: when I save the document into a csv file, it read only the first row, and nothing else.

In my example, if i have on the first row "AAPL", I will obtain a csv file that has only the data from AAPL.
This is my code:


symbols_list = pd.read_csv('/home/andrea/htrade/python/titoli_rows.csv')

symbols = []
for ticker in symbols_list:
r = DataReader(ticker, "yahoo",
start=datetime.datetime.now() - BDay(20),
end=datetime.datetime.now())
# add a symbol column
r['Symbol'] = ticker
symbols.append(r)
# concatenate all the dfs
df = pd.concat(symbols)
#define cell with the columns that i need
cell = df[['Symbol', 'Open', 'High', 'Low', 'Adj Close', 'Volume']]

cell.reset_index().sort_values(['Symbol', 'Date'], ascending=[1, 0]).set_index('Symbol').to_csv('/home/andrea/Dropbox/HT/stock20.csv', date_format='%d/%m/%Y')


Why if I paste a ticker in each column the csv contain all the data of every ticker, but if I paste a ticker in each row, it will read just the first row?

I already tried to see if the "read_csv" function was reading correctly the csv, and he is, so I don't understand why he's not elaborating them all.

Answer

I just ran the below and with a short list of symbols imported via read_csv it seemed to work fine:

from datetime import datetime
import pandas.io.data as web
from pandas.tseries.offsets import BDay

df = pd.read_csv(path_to_file).loc[:, ['symbols']].dropna().squeeze()

symbols = []
for ticker in df.tolist():
    r = web.DataReader(ticker, "yahoo",
                   start= datetime.now() - BDay(20),
                   end= datetime.now())

    r['Symbol'] = ticker
    symbols.append(r)
df = pd.concat(symbols).drop('Close', axis=1)
cell= df[['Symbol','Open','High','Low','Adj Close','Volume']]
cell.reset_index().sort_values(['Symbol', 'Date'], ascending=[1,0]).set_index('Symbol').to_csv(path_to_file, date_format='%d/%m/%Y')
Comments