Save table to different Excel sheets using python

Question

I need to write this tables to excel to different sheet, not to different files

used_at                                     4      
address                          10ruslake.ru 1c.ru
ID                                                 
0025977ab2998580d4559af34cc66a4e          0.0   0.0
00c651e018cbcc8fe7aa57492445c7a2          0.0   0.0
0120bc30e78ba5582617a9f3d6dfd8ca          0.0   0.0

and

used_at                               5          
address                          vk.com yandex.ru
ID                                               
0025977ab2998580d4559af34cc66a4e    152       465
00c651e018cbcc8fe7aa57492445c7a2     23       213
0120bc30e78ba5582617a9f3d6dfd8ca      0       100

I try

dfs[0].to_excel("group_edit.xlsx", sheet_name='Sheet1')
dfs[1].to_excel("group_edit.xlsx", sheet_name='Sheet2')

But it doesnt't work. I try to write a loop:

for i in dfs:
    df[i].to_excel("group_edit.xlsx", sheet_name='Sheet')

but It returns error and I don't know, how can I specify the number of sheet in loop

jezrael · Accepted Answer · 2016-04-25 12:12:47Z

1

I think you can use rename_axis (new in pandas 0.18.0) with to_excel:

Sample:

df = pd.DataFrame({('5', 'vk.com'): {'0120bc30e78ba5582617a9f3d6dfd8ca': 0, '00c651e018cbcc8fe7aa57492445c7a2': 23, '0025977ab2998580d4559af34cc66a4e': 152}, ('5', 'yandex.ru'): {'0120bc30e78ba5582617a9f3d6dfd8ca': 100, '00c651e018cbcc8fe7aa57492445c7a2': 213, '0025977ab2998580d4559af34cc66a4e': 465}, ('4', '10ruslake.ru'): {'0120bc30e78ba5582617a9f3d6dfd8ca': 0.0, '00c651e018cbcc8fe7aa57492445c7a2': 0.0, '0025977ab2998580d4559af34cc66a4e': 0.0}, ('4', '1c.ru'): {'0120bc30e78ba5582617a9f3d6dfd8ca': 0.0, '00c651e018cbcc8fe7aa57492445c7a2': 0.0, '0025977ab2998580d4559af34cc66a4e': 0.0}, ('6', 'youtube.com'): {'0120bc30e78ba5582617a9f3d6dfd8ca': 109, '00c651e018cbcc8fe7aa57492445c7a2': 354, '0025977ab2998580d4559af34cc66a4e': 45}, ('6', 'facebook.com'): {'0120bc30e78ba5582617a9f3d6dfd8ca': 0, '00c651e018cbcc8fe7aa57492445c7a2': 44, '0025977ab2998580d4559af34cc66a4e': 56}})

#remove column names and index name
df = df.rename_axis(None).rename_axis((None,None), axis=1)

writer = pd.ExcelWriter('test.xlsx')
for i, g in df.groupby(axis=1, level=0):
    #print i
    #remove top level 4,5,6
    g.columns = g.columns.droplevel(0)
    #reset index, change column name
    g = g.reset_index().rename(columns={'index':'aaa'})
    #print g
    #sheet name is 'sheet_' + name of group (4,5,6)
    g.to_excel(writer,'sheet_%s' % i)
writer.save()

Old version:

#remove column names and index name
df = df.rename_axis(None).rename_axis((None,None), axis=1)
print df
                                            4            5            \
                                 10ruslake.ru 1c.ru vk.com yandex.ru   
0025977ab2998580d4559af34cc66a4e          0.0   0.0    152       465   
00c651e018cbcc8fe7aa57492445c7a2          0.0   0.0     23       213   
0120bc30e78ba5582617a9f3d6dfd8ca          0.0   0.0      0       100   

                                           6               
                                 youtube.com facebook.com  
0025977ab2998580d4559af34cc66a4e          45           56  
00c651e018cbcc8fe7aa57492445c7a2         354           44  
0120bc30e78ba5582617a9f3d6dfd8ca         109            0  

dfs = []
for i, g in df.groupby(axis=1, level=0):
    #print i
    #remove top level 4,5,6
    g.columns = g.columns.droplevel(0)
    #reset index, change column name
    g = g.reset_index().rename(columns={'index':'aaa'})
    #print g
    dfs.append(g)

print dfs[0]
                                aaa  10ruslake.ru  1c.ru
0  0025977ab2998580d4559af34cc66a4e           0.0    0.0
1  00c651e018cbcc8fe7aa57492445c7a2           0.0    0.0
2  0120bc30e78ba5582617a9f3d6dfd8ca           0.0    0.0

writer = pd.ExcelWriter('test.xlsx')
for n, df in enumerate(dfs):
    df.to_excel(writer,'sheet%s' % n)
writer.save()

edited Apr 25, 2016 at 12:12

answered Apr 25, 2016 at 11:47

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

5 Comments

jezrael Over a year ago

Please check solution, I try improve output.

jezrael Over a year ago

Phuuu, I test it with excel 2010 and it works very well.

jezrael Over a year ago

I add sample DataFrame to answer, please check my solution.

user6241246 Over a year ago

I had some problem with excel, now it works! Thank you!

jezrael Over a year ago

Glad can help you! Good luck!

Collectives™ on Stack Overflow

Save table to different Excel sheets using python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related