How to add pivot header into pandas dataframe?

Question

I'm tring to collect some data from my db, and using pandas to make a excel file. The dataframe structure I got like below:

>>> df

                                            2020-05-21~2020-05-27
input_cost   jim    bob     companyc            17770.65                17770.65 
                            companye            3392.73                 3392.73
                            companyd            2892.30                 2892.30
                            companyf            22843.67                22843.67
             casy   dave    company_a           160.93                  160.93                                                      
                            company_b           160.93                  160.93

And then I want to add a header to it , then export it to excel file by using df.to_excel

# expected output
                                                date
data_source  sale   leader  entity_name       2020-05-21~2020-05-27       sum 
input_cost   jim    bob     companyc            17770.65                17770.65
                            companye            3392.73                 3392.73
                            companyd            2892.30                 2892.30
                            companyf            22843.67                22843.67
             casy   dave    company_a           160.93                  160.93                                                                     
                            company_b           160.93                  160.93

Sorry that I'm a new bee to pandas, how can I make it work?

Thanks.

Quang Hoang · Accepted Answer · 2020-06-02 14:38:50Z

1

You can use rename_axis:

# rename index
df = df.rename_axis(['data_source', 'sale', 'leader', 'entity_name'])

# add level to columns
df.columns = pd.MultiIndex.from_product([['date'], df.columns])

answered Jun 2, 2020 at 14:38

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jia Jimmy Over a year ago

Thanks for reply, I tried but got some error like NotImplementedError: isna is not defined for MultiIndex

jia Jimmy Over a year ago

And my colums is like MultiIndex([('2020-05-21~2020-05-27',)],)

Collectives™ on Stack Overflow

How to add pivot header into pandas dataframe?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related