Excel Write可在具有多个工作表的同一工作表上获取多个数据框，并设置数据格式

Question

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

我还试图将所有数据帧的第2列、第3列和第4列分别格式化为逗号(1000(、美元($1000.00(和百分比(50.12%(我真的需要任何人的帮助。提前感谢

with pd.ExcelWriter(r'C:UsersSS012420DesktopLoanPool_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)

Answer 1

对于给定的数据帧，请尝试作为示例。。。

#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%