Formatting the index while converting daily data to weekly data, using Pandas

Question

I have managed to convert my daily data to weekly data by looking at a previous answer, but I am setting date as the index. My goal is to keep 'Symbol' as the index and include 'Date' as a column.

I tried including 'Date' in the dictionary and Symbol as the index, but it is resulting in an error that the index needs to be Datetime.

This is my code:

if ( data_duration == 'w' ):

    df['Date'] = pd.to_datetime(df['Date'])
    df.set_index('Date', inplace=True)
    df.sort_index(inplace=True)



def take_first(array_like):
    return array_like[0]

def take_last(array_like):
    return array_like[-1]

output = df.resample('W',                                 # Weekly resample
                    how={'Open': take_first, 
                         'High': 'max',
                         'Low': 'min',
                         'Close': take_last,
                         'Volume': 'sum'}, 
                    loffset=pd.offsets.timedelta(days=-6))  # to put the labels to Monday

df = output[['Open', 'High', 'Low', 'Close', 'Volume']]

But I want to retain my index as 'Symbol', like it is in the daily data, while including date in 'Output'.

This is how the daily data looks like:

             Date       Close      High       Low      Open    Volume
Symbol                                                            
AAPL        2017-05-25  153.87  154.3500   153.0300      153.7300      19235598
AAPL        2017-05-26  153.61  154.2400  153.3100      154.0000      21927637

However, after the weekly formatting, everything remains the same, apart from 'Symbol'. How can I fix this?

Answer 1

You want unstack(): https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html

It will move one of the index levels to be a column in the DataFrame. Something like this:

 df.unstack('Date')