Formatting specific rows in Pandas Dataframe with string formatting and heatmap

Question

Here I have a code for a function that creates a formatted table.It currently works but I want to optimise it considering "Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas."

import numpy as np
import dataframe_image as dfi

gs = fm.FontProperties(fname="FoundersGrotesk-Light.otf")


def table_creator(table,name):
    # Get the rows [0:10,1:] and leave others unformatted
    rows_to_style = table.iloc[0:12, 1:]
    row_12_subset = table.iloc[12, 1:]
    row_13_subset = table.iloc[13, 1:]

    # Function to convert decimal to percentage with the desired format
    def float_to_1_d_p(value):
        value = float(value)
        return f'{value:.1f}'
    
        

    def decimal_to_percentage(value):
        value = float(value)
        return f'{value:.1%}'

    
    
    # Apply the function to the selected columns and format the values
    formatted_rows_to_style = rows_to_style.map(float_to_1_d_p)
    formatted_row_12_subset = row_12_subset.apply(decimal_to_percentage)
    formatted_row_13_subset = row_13_subset.apply(float_to_1_d_p)

    
    
    table.iloc[0:12, 1:] = formatted_rows_to_style
    table.iloc[12, 1:] = formatted_row_12_subset
    table.iloc[13, 1:] = formatted_row_13_subset
    
    
    df = table.iloc[0:12]
    peer = table.iloc[[12]]
    delta = table.iloc[[13]]


    
    def color_white(val):
        color = 'background-color: white; color: white'
        if pd.isnull(val)==True or val == 0 or val == None or val == 'nan' or val == int(0) or val == '0.0' or val=='0' or val =='nan%' or val == '0.0%' or val == "" :
            return color
        
    def delta_white(val):
        color = 'background-color: white; color: white'
        if pd.isnull(val)==True or val == None or val == 'nan' or val =='nan%' or val == '0.0%' or val == "" :
            return color

    
    def create_custom_cmap(color1, color2, color3, cmap_name='custom_cmap'):
        # Define the RGB values for the three colors
        colors = [color1, color2, color3]
        # Normalize the color values to range between 0 and 1
        normalized_colors = [(r / 255, g / 255, b / 255) for (r, g, b) in colors]
        # Create a list of color tuples
        color_list = [normalized_colors[i] for i in range(len(normalized_colors))]
        # Create a colormap using LinearSegmentedColormap
        cmap = mcolors.LinearSegmentedColormap.from_list(cmap_name, color_list, N=256)
        cmap.set_bad(color='white')
        return cmap

    # Usage example: specify RGB values for three colors
    


    custom_cmap =create_custom_cmap((231, 114, 111), (255,255, 255), (122, 188, 129))
    
    # Apply the background gradient cmap to the styled rows
    
  
    df1 = df.style\
    .set_properties(subset=['Company Name'], **{'text-align': 'left', 'background-color': 'white', 'font-family': gs, 'font-size': '6', 'width': '3.6cm'}) \
    .set_properties(subset=df.columns.difference(['Company Name']), **{'text-align': 'center', 'font-family': gs, 'font-size': '6', 'color': 'black','background-color': 'white'}) \
    .background_gradient(cmap=custom_cmap,  vmin = 1, vmax =100,subset=formatted_rows_to_style.columns)\
    .set_properties(subset=df.columns.difference(['Company Name']),**{'color': 'black'},)
    df1 = df1.map(color_white)
peer1 = peer.style \
    .set_properties(subset=['Company Name'], **{'text-align': 'left', 'background-color': 'white', 'font-family': gs, 'font-size': '6','width': '3.6cm'}) \
    .set_properties(subset=peer.columns.difference(['Company Name']),**{'text-align': 'center', 'background-color': 'white', 'font-family': gs, 'font-size': '6'})
    peer1 = peer1.map(color_white)
    
    delta1 = delta.style.background_gradient(cmap=custom_cmap, vmin = -100, vmax = 100, subset=formatted_rows_to_style.columns)\
    .set_properties(subset=delta.columns.difference(['Company Name']),**{'text-align': 'center', 'font-family': gs, 'font-size': '6','color':'black','width': '3.6cm'})
    delta2 = delta1.set_properties(subset=['Company Name'],**{'text-align': 'left','background-color': 'white','font-family': gs, 'font-size': '6','color':'black'})
    delta2 = delta2.map(delta_white)
    concatenated_df = df1.concat(peer1)
    concatenated_df_2 = concatenated_df.concat(delta2)

    concatenated_df_3 = concatenated_df_2.hide(axis='index')\
    .set_table_styles([
    {'selector': '.col_heading',
     'props': [('text-align', 'center'), ('font-family', gs), ('font-size', '6'),('font-weight', 'bold'), ('border-bottom', 'solid rgb(157, 235, 219)'), ('border-width', '1pt')]
    },
    {'selector': 'th:first-child, th:first-of-type, td:first-child, td:first-of-type',
     'props': [('text-align', 'left')]
    },
    {'selector': f'tr:nth-child({11})',
     'props': [('border-bottom', 'solid rgb(157, 235, 219)'), ('border-width', '1pt')]
    },
    {'selector': f'tr:nth-child({12})',
     'props': [('border-bottom', 'solid rgb(157, 235, 219)'),('border-width', '0.5pt'),('background', 'white'),('text-align', 'center')]
    },
    {'selector': f'tr:nth-child({13})',
     'props': [('border-bottom', 'solid rgb(157, 235, 219)'), ('border-width', '1pt')] 
    },
    {'selector': '',
     'props': [('border','1px solid white')] 
    },
    {'selector': '.col0',
     'props': [('width', '3.9cm')]
    }],overwrite=False)
    
    concatenated_df_3 = concatenated_df_3 \
    .set_table_styles([{'selector': '', 'props': [('width', '16.8cm'), ('height', '5.8cm')]}], overwrite=False)
    concatenated_df_4 = concatenated_df_3.map(color_white)

    return dfi.export(concatenated_df_4 ,f'{name}.png',fontsize=8,dpi = 1200)
    

I am getting Pandas Warnings:

/var/folders/2c/xbstpd356hz_kgnd8d8tgpxh0000gn/T/ipykernel_84609/1197351821.py:27: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '['62.8' '48.0' '47.5' '40.3' '72.9' '69.0' '62.2' '48.0' '47.5' '77.1'
 '47.1' '55.9']' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.
  table.iloc[0:12, 1:] = formatted_rows_to_style

I tried this: Setting an item of incompatible dtype is deprecated and will raise in a future error of pandas and this: Pandas: Changing a specific row to percentages

What is the best way to do this and avoid future warnings?

Answer 1

I hope it can help you.

In the WARNING Pandas is asking to cast the types, in this case to float64.

# Convert formatted values ​​back to float before assigning
table.iloc[0:12, 1:] = formatted_rows_to_style.astype(float)
table.iloc[12, 1:] = formatted_row_12_subset.astype(float)
table.iloc[13, 1:] = formatted_row_13_subset.astype(float)