1

I am doing my first project with python and I am trying to have one column become one row.

I don't have access to the good data so I use this one instead : https://www.quandl.com/product/WIKIP/WIKI/PRICES-Quandl-End-Of-Day-Stocks-Info)

What I did : 

os.chdir('path')
df = pd.read_csv('WIKI_PRICES_212b326a081eacca455e13140d7bb9db.csv',usecols=['ticker','date','close','volume'])
df.index = df.date
del df['date']

And my df looks like this :

index             ticker                close  
1999-11-18        A                      44
1999-11-19        A                      40,4
.....            ....                    ....
2005-12-20        AA                      33
.....             Z                       37
2003-5-20         Z                       51

And like that for 15 millions rows. I would like to have 

Index             A                 AA        .....      ZZZ
1999-11-18       44                 ....                 price
1999-11-19       40,4               ....                 price

Edit : the following part is not "beautiful" but it works, if you have any idea to improve it i would like to read it.

#format date python
df.date = pd.to_datetime(df.date)
del df['volume']
#my columns stocks in a new df
df2 = df['ticker']
#to have theim only one time (3193 stocks)
df2 = list(set(df2))
df3 = pd.DataFrame(np.array(df2).reshape(3193,1))
df3.columns = ['Stocks']
#now i create a dates times index big enough
dates = pd.date_range('1975-01', '2017-08', freq='d')
#.T
df4 = df3.set_index('Stocks').T

#a dataframe with stock as column et date as index
df5 = pd.DataFrame(pd.np.empty((15554, 3193)))
df5.index = dates
df5.columns = df2

Now i have my first df with all the price and i would like to take the price and have theim in my df5 (the empty one with stocks names as columns and date as index).

To have something like that ( not the real number)

dates     A       AA        ....        ZZZ
1975-01   nan     nan       ...         nan
....      100     3         ...         nan
2017-08   nan     5         ...         12
D.B
  • 21
  • 4
  • That is not quite a "column-to-row" problem, anyway you can try to solve it using: https://stackoverflow.com/a/4937526/1162467 (maybe with the izip version). – Andrea Rastelli Oct 25 '17 at 16:44

1 Answers1

0

Try

new_df = df.set_index(['index', 'ticker']).close.unstack()

Or

new_df = df.pivot('index', 'ticker', 'close')

You get

ticker      A       AA
index       
1999-11-18  44      None
1999-11-19  40,4    None
2005-12-20  None    33
Vaishali
  • 37,545
  • 5
  • 58
  • 86