时间戳

首页 > 解决方案 > Excel写入以在具有多个工作表的同一张工作表上获取多个数据框并格式化数据

pandas - Excel写入以在具有多个工作表的同一张工作表上获取多个数据框并格式化数据

问题描述

每张工作表需要像什么我试图将 6 个数据帧写入一个 Excel 工作表，然后将 6 个不同的数据帧写入同一个工作簿中的另一个 Excel 工作表。我知道下面的代码不是很好，但它可以将数据框放入同一张表中并隔开。

我还尝试将所有数据框的第 2、第 3 和第 4 列分别格式化为带有逗号 (1,000)、美元 ($1,000.00) 和百分比 (50.12%) 的数字，我真的可以使用任何人的帮助。先感谢您

with pd.ExcelWriter(r'C:\Users\SS012420\Desktop\LoanPool_07082021_.xlsx',engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Pooled_Loans')
    summary.to_excel(writer, sheet_name='Summary')
    GEO8.to_excel(writer, sheet_name = 'Strat_Table8')
    OTM8.to_excel(writer, sheet_name = 'Strat_Table8', startcol = (2 + len(GEO8.columns)), startrow = 0)
    RTM8.to_excel(writer, sheet_name = 'Strat_Table8', startcol = (2+ len(GEO8.columns) + 2 + len(OTM8.columns)), startrow = 0)
    FICO8.to_excel(writer, sheet_name = 'Strat_Table8', startcol = (2+len(GEO8.columns) + 2 + len(OTM8.columns) +2 + len(RTM8.columns)), startrow = 0)
    APR8.to_excel(writer, sheet_name = 'Strat_Table8', startcol = (2+len(GEO8.columns) + 2 + len(OTM8.columns) +2 + len(RTM8.columns)+ 2 + len(FICO8.columns)), startrow = 0)
    PB8.to_excel(writer, sheet_name = 'Strat_Table8', startcol = (2+len(GEO8.columns) + 2 + len(OTM8.columns) +2 + len(RTM8.columns)+ 2 + len(FICO8.columns) + 2 + len(APR8.columns)), startrow = 0)
    GEO12.to_excel(writer, sheet_name = 'Strat_Table12')
    OTM12.to_excel(writer, sheet_name = 'Strat_Table12', startcol = (2 + len(GEO12.columns)), startrow = 0)
    RTM12.to_excel(writer, sheet_name = 'Strat_Table12', startcol = (2+ len(GEO12.columns) + 2 + len(OTM12.columns)), startrow = 0)
    FICO12.to_excel(writer, sheet_name = 'Strat_Table12', startcol = (2+len(GEO12.columns) + 2 + len(OTM12.columns) +2 + len(RTM12.columns)), startrow = 0)
    APR12.to_excel(writer, sheet_name = 'Strat_Table12', startcol = (2+len(GEO12.columns) + 2 + len(OTM12.columns) +2 + len(RTM12.columns)+ 2 + len(FICO12.columns)), startrow = 0)
    PB12.to_excel(writer, sheet_name = 'Strat_Table12', startcol = (2+len(GEO12.columns) + 2 + len(OTM12.columns) +2 + len(RTM12.columns)+ 2 + len(FICO12.columns) + 2 + len(APR12.columns)), startrow = 0) 
    GEO19.to_excel(writer, sheet_name = 'Strat_Table19')
    OTM19.to_excel(writer, sheet_name = 'Strat_Table19', startcol = (2 + len(GEO19.columns)), startrow = 0)
    RTM19.to_excel(writer, sheet_name = 'Strat_Table19', startcol = (2+ len(GEO19.columns) + 2 + len(OTM19.columns)), startrow = 0)
    FICO19.to_excel(writer, sheet_name = 'Strat_Table19', startcol = (2+len(GEO19.columns) + 2 + len(OTM19.columns) +2 + len(RTM19.columns)), startrow = 0)
    APR19.to_excel(writer, sheet_name = 'Strat_Table19', startcol = (2+len(GEO19.columns) + 2 + len(OTM19.columns) +2 + len(RTM19.columns)+ 2 + len(FICO19.columns)), startrow = 0)
    PB19.to_excel(writer, sheet_name = 'Strat_Table19', startcol = (2+len(GEO19.columns) + 2 + len(OTM19.columns) +2 + len(RTM19.columns)+ 2 + len(FICO19.columns) + 2 + len(APR19.columns)), startrow = 0)

标签： pandasdataframeexport-to-excel

解决方案

对于给定的数据框尝试作为一个例子......

#add $ (dollar) and commas to column
df['aNumberSeries'] = df.apply(lambda x: "${:,}".format(x['aNumberSeries']), axis=1)

#add % to a column of floats
df['aFloatSeries'] = df['aFloatSeries'].astype(float).map("{:.2%}".format)

示例输入：

    aNumberSeries     aFloatSeries
0   1404338840        0.5670
1   1366938189        0.2345
2   330267887         0.5000
3   269603400         0.4220

示例输出：

    aNumberSeries     aFloatSeries
0   $1,404,338,840.0  56.70%
1   $1,366,938,189.0  23.45%
2   $330,267,887.0    50.00%
3   $269,603,400.0    42.20%

推荐阅读