xlsxwriter pandas frame: to highlight rows if there are blank cells within a column

Question

I'm having a pandas frame with column T that has some blank cells. I want to highlight any rows that have blank cells

I've been trying to use .format but it only highlight the blank cells instead of the whole row.

worksheet.conditional_format('A1:T18', {'type':'no_blank'
                                       'format':green_fmt}

)

Expected: the whole row gets highlighted in light green Actual Results: only the blank cells got highlighted

Answer 1

If blanks values are missing values use pandas styles with custom function:

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)

Answer 2

This works for me:

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()