python - Pandas 格式列多张

Question

使用以下代码，我设法为每个国家/地区创建了 excel 文件，但我无法格式化 excel 列:

df=TOT.reset_index()
for n, g in df.groupby('Country'):
    X = n.strip(" ")
    out_path = "C:/temp/" + n.strip(" ") + ".xlsx" 
    C =TOTSPPerc.reset_index(level=0)[TOTSP.reset_index(level=0).Country==n].drop('Country', axis=1)  
    B =TOTPerc.reset_index(level=0)[TOTPerc.reset_index(level=0).Country==n].drop('Country', axis=1) 
    A= TOTcntPerc.drop('Country', axis=1) 
    writer = pd.ExcelWriter(out_path , engine='xlsxwriter')

    format2 = workbook.add_format({'num_format': '0%'})

    A.to_excel(writer, sheet_name="Country")
    B.to_excel(writer, sheet_name="Stores")
    C.to_excel(writer, sheet_name="SPs")

    writer.save()
    print(n)

我需要为每个创建的文件的 3 个工作表中的每一个设置 B:F 列的格式。

Answer 1

您可以 set custom format每张纸:

for n, g in df.groupby('Country'):
    print (g)
    #your code

    writer = pd.ExcelWriter(out_path , engine='xlsxwriter')

    A.to_excel(writer, sheet_name="Country")
    B.to_excel(writer, sheet_name="Stores")
    C.to_excel(writer, sheet_name="SPs")

    workbook  = writer.book
    worksheet1 = writer.sheets['Country']
    worksheet2 = writer.sheets['Stores']
    worksheet3 = writer.sheets['SPs']

    format2 = workbook.add_format({'num_format': '0%'})
    worksheet1.set_column('B:F', 18, format2)
    worksheet2.set_column('B:F', 18, format2)
    worksheet3.set_column('B:F', 18, format2)

    writer.save()

和更动态的解决方案:

for n, g in df.groupby('Country'):
    print (g)

    #your code

    writer = pd.ExcelWriter(out_path , engine='xlsxwriter')

    dfs = [A,B,C]
    sheetnames = ['Country','Stores','SPs']
    for i, df1 in enumerate(dfs):
        df1.to_excel(writer, sheet_name=sheetnames[i])
        workbook  = writer.book
        worksheet1 = writer.sheets[sheetnames[i]]
        format2 = workbook.add_format({'num_format': '0%'})
        worksheet1.set_column('B:F', 18, format2)

    writer.save()