Python pandas pivot_table multiple time indices

Question

I have the following dataframe:

import pandas as pd
import datetime

df = pd.DataFrame({'T': [datetime.datetime.now(), datetime.datetime.now()], 'V': [1, 2]})

I want to pivot using both the year and month as indices, which I can do as:

df.pivot_table(index = [df['T'].dt.year, df['T'].dt.month], values = 'V')

However, since that gives me two columns named T, I am unable to set margins = True (throws the error: The name T occurs multiple times, use a level number). How can I fix this issue? Ideally I would like to name the columns manually while doing the pivot, so that I have Yearand Monthinstead of Tand T.

Any ideas?

Answer 1

IIUC, you can use rename to rename your series,

df.pivot_table(index = [df['T'].dt.year.rename('Year'),
                        df['T'].dt.month.rename('Month')], 
                values='V',
                aggfunc='sum', 
                margins=True)

Output:

              V
Year Month     
2020 1        3
All           3