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, Apr 06 '21 at 16:01
But how do I remove excel index numbers which gets added in the first column`0 1 2 3 4 5 6...`? — astroboy1, Apr 06 '21 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, Apr 06 '21 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, Apr 06 '21 at 16:09
Nope, I've reset the index and exporting to excel `with pd.option_context('display.multi_sparse', False): data.reset_index().to_excel('xyz.xlsx', index=False)` — astroboy1, Apr 06 '21 at 16:10
Hmm, can you just try `data.reset_index().to_excel(filename,index=False)` without the `with pd.option_context('display.multi_sparse', False):` , the `with` is just for display but I dont know if it effects the output to excel. — anky, Apr 06 '21 at 16:24
Gives me the same error. If you look into my question, I have used MultiIndexing columns for my data. It might be the reason, but I'm not sure. — astroboy1, Apr 06 '21 at 16:29
[dummy data](https://easyupload.io/0bt8p9). @anky, here you go. — astroboy1, Apr 06 '21 at 16:49
Sorry but no links please, if ossible create a dummy data, links here : https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — anky, Apr 06 '21 at 16:52
yes you guessed it right, because you have mutiindex in columns you cant use index = False, you need to use `data.reset_index().to_excel()` unfortunately one has to ignore the index generated as it is something that pandas hasnt implemented — anky, Apr 08 '21 at 15:28

score 1 · Answer 1 · answered Nov 24 '21 at 21:02

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)

score 0 · Answer 2 · answered Oct 25 '21 at 16:33

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)

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

2 Answers2

Linked