使用python将表保存到不同的Excel工作表 [英] Save table to different Excel sheets using python

问题描述

我需要将此表写入excel到不同的sheet，而不是不同的文件

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

和

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

我尝试

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')

但是它返回错误，我不知道如何在循环中指定sheet的数量

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

推荐答案

我认为您可以使用

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

示例:

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()

旧版本:

#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()

这篇关于使用python将表保存到不同的Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！