[FIXED] pandas pivot_table column name should not be present and how to order a text column

Issue

I'm learning pandas.

I have created this test dataframe:

dfdict = {'product':['ruler', 'pencil', 'case', 'rubber'],'sold':[4,23,0,14],'Quarter':['Q1/22','Q2/23','Q3/22','Q1/23']}
dftest=pd.DataFrame(dfdict)
dftemp=dftest.pivot_table(index=['product'],columns=['Quarter'],values=['sold'],aggfunc=sum,fill_value=0)
print(f"{dftemp}")

which produces:

         sold
Quarter Q1/22 Q1/23 Q2/23 Q3/22
product
case        0     0     0     0
pencil      0     0    23     0
rubber      0    14     0     0
ruler       4     0     0     0

Two points I need help with:

1. How do I remove only the sold column name? I don't want it there as when I write it to a csv each Quarter column has a "sold" name above it.

2. How can I order the Quarter columns in date order, right now they are text. What's the best way? Is it to somehow convert them into a date and then order them and write it out in the same style?

Solution

You can convert column Quarter to quarters, so pivot_table sorting correct ouput, for remove MultiIndex in columns for sold change one element lists like [sold] to sold:

dftest['Quarter'] = pd.PeriodIndex(dftest['Quarter'].str[-2:] +
                                   dftest['Quarter'].str[:2], freq='Q')

dftemp=dftest.pivot_table(index='product',
                          columns='Quarter', 
                          values='sold',
                          aggfunc=sum,
                          fill_value=0)
print(f"{dftemp}")
Quarter  2022Q1  2022Q3  2023Q1  2023Q2
product                                
case          0       0       0       0
pencil        0       0       0      23
rubber        0       0      14       0
ruler         4       0       0       0

Last if need original or another formating use rename with strftime:

dftemp = dftemp.rename(columns=lambda x: x.strftime('Q%q/%y'))
print(f"{dftemp}")
Quarter  Q1/22  Q3/22  Q1/23  Q2/23
product                            
case         0      0      0      0
pencil       0      0      0     23
rubber       0      0     14      0
ruler        4      0      0      0

Answered By - jezrael