how to repeat row labels in pandas pivot table function and export it as excel

Question

I've a sample pivoted data using pandas pivot_table()

df = pd.DataFrame([['USA', 'LA', 'April', 2, '1:2'],
                           ['USA', 'FL', 'April', 5, '5:6'],
                           ['USA', 'TX', 'April', 7, '1:3'],
                           ['Canada', 'Ontario', 'April', 2, '1:3'],
                           ['Canada', 'Toronto', 'April', 3, '1:5'],
                           ['USA', 'LA', 'May', 3, '4:5'],
                           ['USA', 'FL', 'May', 6, '4:5'],
                           ['USA', 'TX', 'May', 2, '1:4'],
                           ['Canada', 'Ontario', 'May', 6, '8:9'],
                           ['Canada', 'Toronto', 'May', 9, '3:4']],
             columns=['Country', 'Cities', 'month', 'Count', 'Ratio'])


mux1 = pd.MultiIndex.from_product([data['month'].unique(), ['Count', 'Ratio']])
data = data.pivot_table(columns=['month'], values=['Count', 'Ratio'], index=['Country', 'Cities']).swaplevel(1, 0, axis=1).reindex(mux1, axis=1)


                                April              May
                         Count      Ratio    Count   Ratio
 Country    Cities
   USA       LA           2          1:2      3       4:5
             FL           5          5:6      6       4:5
             TX           7          1:3      2       1:4
   Canada    Ontario      2          1:3      6       8:9
             Toronto      3          1:5      9       3:4

How could I repeat my row labels in the pivot data which looks like below and export it as excel?

                                April              May
                         Count      Ratio    Count   Ratio
 Country    Cities
   USA        LA           2         1:2       3      4:5
   USA        FL           5         5:6       6      4:5
   USA        TX           7         1:3       2      1:4
   Canada     Ontario      2         1:3       6      8:9
   Canada     Toronto      3         1:5       9      3:4

I've tried pd.option_context('display.multi_sparse', False), as it only display the content, it does not export data as excel.

you have to reset the index. data.reset_index().to_excel(...) — anky
– anky, Commented Apr 6, 2021 at 16:01
But how do I remove excel index numbers which gets added in the first column0 1 2 3 4 5 6...? — astroboy1
– astroboy1, Commented Apr 6, 2021 at 16:06
I tried that already, but giving me an error NotImplementedError: Writing to Excel with MultiIndex columns and no index ('index'=False) is not yet implemented. — astroboy1
– astroboy1, Commented Apr 6, 2021 at 16:08
No you didnot try that, you tried df.to_excel(filename,index=False) , thats not what I sugested, I suggested you reset the index first — anky
– anky, Commented Apr 6, 2021 at 16:09

jarex · Accepted Answer · 2021-11-24 21:02:29Z

1

Adding the data.reset_index().to_excel('file.xlsx', index=False) after finishing the table actually worked

cfuper = np.round(pd.pivot_table(pndg_crss, columns = None,
                                        values = 'Results %',
                                        index = ['Email','Title','Manager']))
cfuper.reset_index().to_excel('test.xlsx',sheet_name = 'Sheet1',index=False)

answered Nov 24, 2021 at 21:02

jarex

111 bronze badge

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

Comments

Fernando Gamiño · Accepted Answer · 2021-10-25 16:33:26Z

0

The way I solved it, which may be not the optimal solution was:

Switching the index order. In your case it would be: index=[ 'Cities','Country'])

data = data.pivot_table(columns=['month'], values=['Count', 'Ratio'], index=[ 'Cities','Country']).swaplevel(1, 0, axis=1).reindex(mux1, axis=1)

answered Oct 25, 2021 at 16:33

Fernando Gamiño

611 silver badge3 bronze badges

Collectives™ on Stack Overflow

how to repeat row labels in pandas pivot table function and export it as excel

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related