CODEWITHSUNDEEP
pandas
vsoler
vsoler

Reputation: 1183

Series to DataFrame

I have a Series that looks like this:

                     Adj Close
Date        minor   
2017-09-22  AFK      23.500000
            ASHR     29.530001
            ECH      49.259998
            EGPT     28.139999
            EIDO     26.950001

That is, for several ETFs, I have the Adj Close, day by day.

I want to convert it to a DataFrame sush as:

              AFK         ASHR       ECH
Date            
2017-09-22    23.500000   29.530001  49.259998 ...
2017-09-23    ...

I have tried with pivot():

h.pivot(index="Date", columns = "minor")

But I get an error message.

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
C:\Users\USUARIO\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2392             try:
-> 2393                 return self._engine.get_loc(key)
   2394             except KeyError:


KeyError: 'Date'

During handling of the above exception, another exception occurred:

 ...

KeyError: 'Date'

What am I doing wrong?

Upvotes: 1

Views: 47

Answers (1)

jezrael
jezrael

Reputation: 863741

I think you need Series.unstack, select the column by indexing with [] to get the series:

df1 = df['Adj Close'].unstack()
print (df1)
minor        AFK       ASHR        ECH       EGPT       EIDO
Date                                                        
2017-09-22  23.5  29.530001  49.259998  28.139999  26.950001

If want use pivot first create columns from MultiIndex by reset_index:

print (df.reset_index())
         Date minor  Adj Close
0  2017-09-22   AFK  23.500000
1  2017-09-22  ASHR  29.530001
2  2017-09-22   ECH  49.259998
3  2017-09-22  EGPT  28.139999
4  2017-09-22  EIDO  26.950001

df1 = df.reset_index().pivot(index='Date', columns='minor', values='Adj Close')
print (df1)
minor        AFK       ASHR        ECH       EGPT       EIDO
Date                                                        
2017-09-22  23.5  29.530001  49.259998  28.139999  26.950001

Upvotes: 2

Related Questions