CODEWITHSUNDEEP
pythonpandasxlsxwriter
Mike
Mike

Reputation: 1069

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

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

Upvotes: 3

Views: 1383

Answers (3)

Shahidul Islam Molla
Shahidul Islam Molla

Reputation: 640

Below code is working for me. I am using 'type': 'formula' and INDIRECT() function from Excel

import pandas as pd

excel_path=r'C:\Users\smolla\Desktop\Python\Split National 2024Q1.xlsx'

df_sheet1=pd.read_excel(excel_path,sheet_name='Sheet1')

with pd.ExcelWriter(excel_path,engine='xlsxwriter') as writer:
    df_sheet1.to_excel(writer, sheet_name='Sheet1',index=False)
    
    wb = writer.book  # get workbook
    ws2 = writer.sheets['Sheet1']   
    
    bg_color = wb.add_format({'bg_color':   'yellow','font_color': 'red'})    
    ws2.conditional_format('A1:AF8000',{'type': 'formula',
                              'criteria': '=INDIRECT("Y"&ROW())=15',
                              'format': bg_color})

Output enter image description here

Upvotes: 1

phil
phil

Reputation: 423

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)

Upvotes: 1

drew wood
drew wood

Reputation: 335

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.

Upvotes: 0

Related Questions