Using pd.xlsxwriter to conditionally format an entire row based on a single cell in that row

user_2725103 0
Viewed 275

I have the following code:

import pandas as pd
from datetime import datetime
 writer = pd.ExcelWriter(
        f"~/{datetime.today().strftime('%Y.%m.%d')} - Report.xlsx",
        engine="xlsxwriter",
        date_format="dd/mm/yyyy",
        datetime_format="dd/mm/yyyy",
    )
worksheet = writer.sheets["Sheet1"]
comma_format = workbook.add_format(
        {"num_format": "#,###;[Red](#,###);-", "bold": True}
    )
worksheet.conditional_format(
        "$A$2:$J$1000",
        {
            "type": "cell",
            "criteria": "containing",
            "value": '"Total"',
            "format": comma_format,
        },
    )

I would like to, whenever a row contains the word "Total" in any cell in the row, format the entire cell with comma_format.

The above script, however, only formats the cell containing "Total", not the entire row.

Any ideas on how I can fix this?

Many thanks,

Mike

xlsxwriter pandas python
2 Answers

You can use DataFrame operations to get the row indices that contain the word Total like so. rows_with_total = df[df.applymap(lambda x: True if isinstance(x, str) and 'total' in x.lower() else False).any(1)].index.to_list().

applymap iterates over all of the values in a DataFrame and applies the passed in function to each one. In this case it returns True if the value is a string and contains the word total, otherwise it returns False.

The any method with 1 passed in checks if any value in a row is True and returns a Series of booleans (True if any column in the row is True and False otherwise). Wrapping that part in df['that part'] filters the rows that are returned based on the Series boolean values.

Then .index.to_list() returns the indices of the filtered rows as a list.

With this list you can then do what @drew_wood suggested and loop over the rows applying the format to each one like so.

for row in rows_with_total:
    worksheet.set_row(row, None, comma_format)

The full code

rows_with_total = df[df.applymap(lambda x: True if isinstance(x, str) and 'total' in x.lower() else False).any(1)].index.to_list()
for row in rows_with_total:
    worksheet.set_row(row, None, comma_format)
user_68551680

You can store each row in your DataFrame as a list using df.values

You can then loop through this list, checking to see if 'Total' in list:

If it is, you can then loop through every column in this row, and assign the (row_ind,col_ind) to your desired format.

user_172438350
Related