0

I have a multi-index pivot table that I am making from a dataframe as shown below:

pivot_summary = pd.pivot_table(df, index=['Team', 'Associate Name'], columns=['Date'], values=['Call Transfers'], aggfunc=sum, fill_value=0, margins=True, margins_name='Grand Total')

I then have a function called "SubTotals" which calculates the Totals for each Team and adds them to level 1 in the multi-index.

def SubTotals(df):
    '''
    The purpose of this function is to add sub-totals to each individual team 
    '''
    #create a dataframe with the totals of each team
    sub_totals = df.sum(level=0)
    #create variable that stores string of team # + 'Total'
    sub_totals_str = sub_totals.index.str[:] + ' Total'
    
    #add the subtotals for each team to the main dataframe
    final_df = df.append(sub_totals.assign(Team=sub_totals_str).set_index('Team', append=True)).sort_index()
    
    #drop the duplicate "Grand-Total" row at the bottom
    final_df.drop(final_df.tail(1).index, inplace=True)
    
    #create string variables of both index(Multi-level index) to zip into tuples
    index_level_0 = sub_totals.index.tolist()
    index_level_1 = sub_totals_str.tolist()
    
    #remove duplicate "Grand Total" from second index
    index_level_1[-1] = ''     
    
    #zip index to pass through formatting function
    team_totals_index = list(zip(index_level_0,index_level_1))
    
    return final_df, team_totals_index

So far everything is good, I get the subtotals for each team exactly how I want. Where I am stuck is that I want to highlight all the rows that are in the "teams_totals_index" variable. I am trying to create a function "highlight_rows" that takes the list of multi-index as parameter and highlights if there is a match. I have seen many examples where lambda is used instead and it has served me as a crude check, for example:
final_df.style.apply(lambda x: ['background: lightgreen' if x.name==('Grand Total','') else '' for i in x], axis=1)

produces the result I am looking for, it highlights the Multi-Index ('Grand Total', '') row entirely however I also want to highlight all the other teams. I do not want to hard-code each index into a lambda function since the amount of teams is dynamic.

I have tried to use the lambda function is an example to create my own but I am having no success. below is what I have attempted.

def highlight_rows(x, index_list):
    '''
    highlight the maximum Totals of each team and Grand Total.
    '''
    df_styler = x.copy()
    
    b_color = 'background: lightgreen'
    
    for index_0, index_1 in index_list:
        df_styler.loc[index_0, index_1] = b_color
        
    return df_styler

I then try to apply final_df.style.apply(highlight_rows, index_list=team_totals_index, axis=1)

but I get a ValueError: Length Mismatch, expected value 32, new axis has 22 elements

I have also tried:

def highlight_rows(x, index_list):
    '''
    highlight the maximum Totals of each team and Grand Total.
    '''
    
      
    for index_0, index_1 in index_list:
        if x.name== (index_0, index_1):
            return ['background: lightgreen']
        else:
            return ''

But I also receive the same ValueError: Length Mismatch, expected value 1 element, new value has 22 elements.

Essentially I want to iterate over my list 'total_teams_index' which contains the multi-level index that I want to highlight. Any insight on how I could accomplish this would be greatly appreciated.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
GeeSama
  • 39
  • 4

1 Answers1

0

Although I can't seem to structure this as a function and make it work, I have found a workable solution without having to hard-code each index pair. I am using method chains to build up the style piece-wise as suggested by the documentation.

final_df.style.\
    apply(lambda x: ['background-color: #FF9900'
                     if x.name in team_totals_index else 'background-color:#FFCC99' for i in x], axis=1).\
    apply(lambda x: ['font-weight: bold' if x.name in team_totals_index else '' for i in x], axis=1).\
    apply(lambda x: ['border-top-style:solid' if x.name in team_totals_index else '' for i in x], axis=1).\
    apply(lambda x: ['border-bottom-style:solid' if x.name in team_totals_index else '' for i in x], axis=1)

I personally don't like the repetitive use of the same loop but it serves my purpose and styles everything exactly as needed. The key to getting all this to work was if x.name in [list containing index pairs] instead of trying to iterate over each index pair as shown in my examples above. I hope this may help anyone that comes across and can still potentially chime in on a less verbose/function friendly way of structuring this.

GeeSama
  • 39
  • 4