如何在python数据框中为选定的列着色 [英] how to color selected columns in python dataframe?
本文介绍了如何在python数据框中为选定的列着色的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用以下代码将df导出到excel,我需要为输出excel中的特定列着色.
I am using the below code to export my df to excel, I have a requirement to color the particular columns in the output excel.
# DF TO EXCEL
from pandas import ExcelWriter
writer = ExcelWriter('Output.xlsx')
df.to_excel(writer,'sheet1')
writer.save()
请给我建议一种方法.
推荐答案
您可以使用:
import string
np.random.seed(100)
df = pd.DataFrame(np.random.randint(10, size=(5,5)), columns=list('GHIJK'))
print (df)
G H I J K
0 8 8 3 7 7
1 0 4 2 5 2
2 2 2 1 0 8
3 4 0 9 6 2
4 4 1 5 3 4
如果需要将所有选定的列设置为相同的颜色:
If need set all selected columns to same color:
writer = pd.ExcelWriter('file1.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
#set format
workbook = writer.book
format = workbook.add_format({'bg_color': 'red'})
worksheet = writer.sheets['Sheet1']
#dict for map excel header, first A is index, so omit it
d = dict(zip(range(25), list(string.ascii_uppercase)[1:]))
print (d)
{0: 'B', 1: 'C', 2: 'D', 3: 'E', 4: 'F', 5: 'G', 6: 'H', 7: 'I', 8: 'J',
9: 'K', 10: 'L', 11: 'M', 12: 'N', 13: 'O', 14: 'P', 15: 'Q', 16: 'R',
17: 'S', 18: 'T', 19: 'U', 20: 'V', 21: 'W', 22: 'X', 23: 'Y', 24: 'Z'}
#select columns of df
cols = ['G','J','K']
#in loop set background color where are data
#http://xlsxwriter.readthedocs.io/working_with_conditional_formats.html
for col in cols:
excel_header = str(d[df.columns.get_loc(col)])
len_df = str(len(df.index) + 1)
rng = excel_header + '2:' + excel_header + len_df
worksheet.conditional_format(rng, {'type': 'no_blanks',
'format': format})
writer.save()
如果需要将所有选定的列设置为不同的颜色:
If need set all selected columns to different color:
writer = pd.ExcelWriter('file1.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
#set format
workbook = writer.book
worksheet = writer.sheets['Sheet1']
#dict for map excel header, first A is index, so omit it
d = dict(zip(range(25), list(string.ascii_uppercase)[1:]))
print (d)
{0: 'B', 1: 'C', 2: 'D', 3: 'E', 4: 'F', 5: 'G', 6: 'H', 7: 'I', 8: 'J',
9: 'K', 10: 'L', 11: 'M', 12: 'N', 13: 'O', 14: 'P', 15: 'Q', 16: 'R',
17: 'S', 18: 'T', 19: 'U', 20: 'V', 21: 'W', 22: 'X', 23: 'Y', 24: 'Z'}
#select columns of df
cols = ['G','J','K']
colors = ['red','yellow','blue']
#in loop set background color where are data
#http://xlsxwriter.readthedocs.io/working_with_conditional_formats.html
for i, col in enumerate(cols):
format = workbook.add_format({'bg_color': colors[i]})
excel_header = str(d[df.columns.get_loc(col)])
len_df = str(len(df.index) + 1)
rng = excel_header + '2:' + excel_header + len_df
worksheet.conditional_format(rng, {'type': 'no_blanks',
'format': format})
writer.save()
这篇关于如何在python数据框中为选定的列着色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文