Save Table To Different Excel Sheets Using Python

I need to write this tables to excel to different sheet, not to different files used_at 4 address 10ruslake.ru 1c

Solution 1:

I think you can use rename_axis (new in pandas0.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
                                            45            \
                                 10ruslake.ru 1c.ru vk.com yandex.ru   
0025977ab2998580d4559af34cc66a4e          0.00.0152465   
00c651e018cbcc8fe7aa57492445c7a2          0.00.023213   
0120bc30e78ba5582617a9f3d6dfd8ca          0.00.001006               
                                 youtube.com facebook.com  
0025977ab2998580d4559af34cc66a4e          4556  
00c651e018cbcc8fe7aa57492445c7a2         35444  
0120bc30e78ba5582617a9f3d6dfd8ca         1090  

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, dfin enumerate(dfs):
    df.to_excel(writer,'sheet%s' % n)
writer.save()