CODEWITHSUNDEEP
pythonexcelpandasdataframe
user2883681
user2883681

Reputation: 3

PANDAS - how to color entire row based on value in Index

I have the following MultiIndex Dataframe:

pt = df[df['Top Task Work Type'].isin(['Customer Billable','Customer Non-Billable', 'Work at risk'])].pivot_table(
    index = ['Resource id', 'End Customer Name', 'Top Task Work Type'],
    columns= ['Fiscal Quarter','Fiscal Week In Qtr Num Int'],
    values= ['Total Hours'],
    aggfunc = {'Total Hours' : np.sum}
)

as you can see above, I create a Pivot table from an excel sheet using 'Top Task Work Type' as my filter. Pivot table it creates has a 40 columns.

Excel output looks like:

https://i.sstatic.net/OFD5J.jpg

I want to highlight in red (color) all rows that have value "Customer Non-Billable" like:

https://i.sstatic.net/ySq7L.jpg

I have tried to apply style to dt in several ways like: 

def highlight_col(x):
    #copy df to new - original data are not changed
    df = x.copy()
    #set by condition
    mask = pt.index.get_level_values('Top Task Work Type') == 'Customer Non-Billable'
    df.loc[mask, :] = 'background-color: yellow'
    df.loc[~mask,:] = 'background-color: ""'
    return df    

pt.style.apply(highlight_col, axis=1)

without any success. I have been able to color row based on column for certain value, but I want to color all rows that have a value of "Customer Non-Billable"

any help is greatly appreciated

Upvotes: 0

Views: 3247

Answers (2)

user2883681
user2883681

Reputation: 3

I made it worked using XMLwriter

I first created style to use:

########## Format definition for CAP #########
format_cap = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

Then applied it:

worksheet_all.conditional_format('C5:BC500', {'type': 'formula',
                                         'criteria': '=LEFT($C5, 250)="Customer Non-Billable"', 
                                          'format': format_cap})

it worked

Upvotes: 0

Rich Andrews
Rich Andrews

Reputation: 1670

Here is styling using the Pandas > 0.20 strategy.

import pandas as pd
from pandas.compat import StringIO
import xlsxwriter
import xlwt
import openpyxl

csvdata = StringIO("""date,LASTA,LASTB,LASTC
1999-03-15,-2.5597,8.20145,16.900
1999-03-17,2.6375,8.12431,17.125
1999-03-18,2.6375,-8.27908,16.950
1999-03-19,2.6634,8.54914,17.325
1999-04-06,2.8537,7.63703,17.750""")

df = pd.read_csv(csvdata, sep=",", index_col="date", parse_dates=True, infer_datetime_format=True)

def color_negative_red(val):
    color = 'red' if val < 0 else 'white'
    return 'background-color: %s' % color            

df.style.\
    applymap(color_negative_red).\
    to_excel('styletest.xlsx', engine='openpyxl')

Styling with the XlsxWriter Strategy. Maybe this provides easier access to the data to be styled.

import pandas as pd
from pandas.compat import StringIO
import xlsxwriter
import xlwt
import openpyxl

csvdata = StringIO("""date,LASTA,LASTB,LASTC
1999-03-15,-2.5597,8.20145,16.900
1999-03-17,2.6375,8.12431,17.125
1999-03-18,2.6375,-8.27908,16.950
1999-03-19,2.6634,8.54914,17.325
1999-04-06,2.8537,7.63703,17.750""")

df = pd.read_csv(csvdata, sep=",", index_col="date", parse_dates=True, infer_datetime_format=True)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter("styletest.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})
format3 = workbook.add_format({'bg_color': 'yellow'})

# Set the column width and format.
worksheet.set_column('B:B', 18, format2)
worksheet.set_row(2, 5, format3)

# Set the format but not the column width.
worksheet.set_column('C:C', None, format2)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Going after a subset of the data in a DataFrame with a Multiindex.

import pandas as pd
import numpy as np

midx = pd.MultiIndex.from_product([['A0','A1'], ['B0','B1','B2','B3']])
columns = ['foo', 'bar']
df = pd.DataFrame(np.arange(16).reshape((len(midx), len(columns))),index=midx, columns=columns)

def style(val):
    return 'background-color: yellow'

idx = pd.IndexSlice[:, 'B0':'B1']
print(df.loc[idx,:])

df.style.applymap(style, subset=idx).to_excel('styletest.xlsx', engine='openpyxl')

Yet the specified IndexSlice isn't working. But this should be the pattern.

Upvotes: 1

Related Questions