import pandas as pd
import numpy as np
import xlsxwriter
# Create a test dataframe (borrowed by jezrael)
df = pd.DataFrame({'T':[np.nan, np.nan, 1, 5],
'A':range(4),
'B':list('abcd')})
# Create a Pandas Excel writer using XlsxWriter as the engine
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object
df.to_excel(writer, sheet_name='Sheet1', index=False)
# Get the xlsxwriter workbook and worksheet objects
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Define the format for the row
cell_format = workbook.add_format({'bg_color': 'yellow'})
# Grab the index numbers of the rows where specified column has blank cells (in this case column T)
rows_with_blank_cells = df.index[pd.isnull(df['T'])]
# For loops to apply the format only to the rows which have blank cells
for col in range(0,df.shape[1]): # iterate through every column of the df
for row in rows_with_blank_cells:
if pd.isnull(df.iloc[row,col]): # if cell is blank you ll get error, that's why write None value
worksheet.write(row+1, col, None, cell_format)
else:
worksheet.write(row+1, col, df.iloc[row,col], cell_format)
# Finally output the file
writer.save()
df = pd.DataFrame({'T':[np.nan, np.nan, 1, 5],
'A':range(4),
'B':list('abcd')})
print (df)
T A B
0 NaN 0 a
1 NaN 1 b
2 1.0 2 c
3 5.0 3 d
def highlight(x):
c = 'background-color: lime'
df1 = pd.DataFrame('', index=x.index, columns=x.columns)
m = x.isna().any(axis=1)
df1 = df1.mask(m, c)
return df1
df.style.apply(highlight, axis=None).to_excel('styled.xlsx', engine='openpyxl', index=False)
这对我有用:
如果空白值缺少值,请将pandas styles与自定义函数一起使用:
1。构建一个函数,如果找到NaN,则突出显示行。在
2.数据帧.样式.应用(函数名,轴=1)
相关问题 更多 >
编程相关推荐