pythonpandasreportlab

Applying styles to different columns in table

I am getting an error when trying to apply a color to specific columns when creating a table with reportlab.

Here is my entire script for a reproducible example (you will need to put an output path on line 109):

import pandas as pd
from datetime import datetime
from reportlab.platypus import Frame
from reportlab.lib.pagesizes import A4, landscape
from reportlab.platypus import PageTemplate
from reportlab.platypus import BaseDocTemplate
from reportlab.platypus import Image
from reportlab.lib.units import inch
from reportlab.platypus import Table, Paragraph
from reportlab.lib import colors
from reportlab.platypus import NextPageTemplate, PageBreak
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle

def on_page(canvas, doc, pagesize=A4):
    page_num = canvas.getPageNumber()
    canvas.drawCentredString(pagesize[0]/2, 50, str(page_num))

    now = datetime.now()
    today = now.strftime("%B %d, %Y")  
    current_time = now.strftime("%I:%M %p") 

    canvas.drawString(50, pagesize[1] - 50, f"{today}")
    canvas.drawString(50, pagesize[1] - 70, f"{current_time}") 

def on_page_landscape(canvas, doc):

  return on_page(canvas, doc, pagesize=landscape(A4))

def format_nums(num):
    if num < 0: 
       x = '(${:.2f})'.format(abs(num))
    elif num > 0:
        x = '${:.2f}'.format(num)
    else:
       x = '${:.0f}'.format(num)

    return x

def df2table(df, custom_style):
    for col in df.columns:
        if df[col].dtype == 'float64':
            df[col] = df[col].apply(lambda x: format_nums(x)) 

    #('BACKGROUND', (start_col, start_row), (end_col, end_row), color)
    return Table(
        [[Paragraph(col, custom_style) for col in df.columns]] + df.values.tolist(),
        style = [
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),  # Header font
        ('FONTSIZE', (0, 1), (-1, -1), 8),  # Body font size
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),  # Header text color
        ('BACKGROUND', (0, 0), (-1, 0), colors.lightgrey),  # Header background color
        ('BACKGROUND', (0, 0), (0, 0), colors.lightblue),  # first Header background color
        ('BACKGROUND', (0, 1), (0, -1), colors.lightblue),  # First column background color
        ('LINEBELOW', (0, 0), (-1, 0), 1, colors.black),  # Line below header
        ('INNERGRID', (0, 0), (-1, -1), 0.25, colors.black),  # Inner grid lines
        ('BOX', (0, 0), (-1, -1), 1, colors.black)],  # Outer box
        hAlign = 'LEFT')

def df2table2(df, custom_style, commodity_cols):
    for col in df.columns:
        if df[col].dtype == 'float64':
            df[col] = df[col].apply(lambda x: format_nums(x))

    data = [[Paragraph(col, custom_style) for col in df.columns]] + df.values.tolist()
    
    #('BACKGROUND', (start_col, start_row), (end_col, end_row), color)
    style = [
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),  # Header font
        ('FONTSIZE', (0, 1), (-1, -1), 8),  # Body font size
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),  # Header text color
        ('BACKGROUND', (0, 0), (-1, 0), colors.lightgrey),  # Header background color
        ('LINEBELOW', (0, 0), (-1, 0), 1, colors.black),  # Line below header
        ('INNERGRID', (0, 0), (-1, -1), 0.25, colors.black),  # Inner grid lines
        ('BOX', (0, 0), (-1, -1), 1, colors.black)]  # Outer box
    
    # Apply colors based on column types
    for i, col in enumerate(df.columns): 
        if col in commodity_cols:
            style.append(('BACKGROUND', (i, 1), (i, -1), colors.lightgrey))  # Commodity column
        else:
            if col != 'Counterparty':
                style.append(('BACKGROUND', (i, 1), (i, -1), colors.lightgreen))  # Aggregation column

    return Table(data, style, hAlign='LEFT')

df = pd.DataFrame({
        'Counterparty': ['foo', 'fizz', 'fizz', 'fizz','fizz', 'foo'],
        'Commodity': ['bar', 'bar', 'bar', 'bar','bar', 'ab cred'],
        'DealType': ['Buy', 'Buy', 'Buy', 'Buy', 'Buy', 'Buy'],
        'StartDate': ['07/01/2024', '09/01/2024', '10/01/2024', '11/01/2024', '12/01/2024', '01/01/2025'],
        'FloatPrice': [18.73, 17.12, 17.76, 18.72, 19.47, 20.26],
        'MTMValue':[10, 10, 10, 10, 10, 10]
        })

commodity_cols = df['Commodity'].unique()

out = pd.pivot_table(df, values = 'MTMValue', index='Counterparty', columns = 'Commodity', aggfunc='sum').reset_index().rename_axis(None, axis=1).fillna(0)
out['Cumulative Exposure'] = out[out.columns[1:]].sum(axis = 1)

path = <INSERT PDF OUTPUT FILE HERE>

padding = dict(
    leftPadding=72, 
    rightPadding=72,
    topPadding=72,
    bottomPadding=18)

portrait_frame = Frame(0, 0, *A4, **padding)
landscape_frame = Frame(0, 0, *landscape(A4), **padding)
            
portrait_template = PageTemplate(
    id='portrait', 
    frames=portrait_frame,
    onPage=on_page, 
    pagesize=A4)

landscape_template = PageTemplate(
    id='landscape', 
    frames=landscape_frame, 
    onPage=on_page_landscape, 
    pagesize=landscape(A4))

doc = BaseDocTemplate(
    path,
    pageTemplates=[
    #portrait_template
    landscape_template
    ]
)

styles = getSampleStyleSheet()
custom_style = ParagraphStyle(name='CustomStyle', fontSize=8)

# NOT WOKRING
#story = [
#    Paragraph('Title 1', styles['Title']),
#    Paragraph('Title 2', styles['Title']),
#    Paragraph("<br/><br/>", styles['Normal']),
#    Paragraph('Current Total Positions', styles['Heading2']),
#    df2table2(out, custom_style, commodity_cols)
#]

# WORKING
story = [
    Paragraph('Title 1', styles['Title']),
    Paragraph('Tilte 2', styles['Title']),
    Paragraph("<br/><br/>", styles['Normal']),
    Paragraph('Current Total Positions', styles['Heading2']),
    df2table(out, custom_style)
]

doc.build(story)

My issue arises in the df2table2 function somewhere with the full error message:

unsupported operand type(s) for -=: 'float' and 'tuple'

I have been looking at this for hours and cannot for the life of me figure out what is possibly going wrong. Any ideas what the issue could be?

Solution

  • The problem lies with your return statement:

    def df2table2(df, custom_style, commodity_cols):

    # ...

    data = [[Paragraph(col, custom_style) for col in df.columns]] + df.values.tolist()
    
    #('BACKGROUND', (start_col, start_row), (end_col, end_row), color)
    style = [
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),  # Header font
        ('FONTSIZE', (0, 1), (-1, -1), 8),  # Body font size
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),  # Header text color
        ('BACKGROUND', (0, 0), (-1, 0), colors.lightgrey),  # Header background color
        ('LINEBELOW', (0, 0), (-1, 0), 1, colors.black),  # Line below header
        ('INNERGRID', (0, 0), (-1, -1), 0.25, colors.black),  # Inner grid lines
        ('BOX', (0, 0), (-1, -1), 1, colors.black)]  # Outer box
    
    # ...

    return Table(data, style, hAlign='LEFT')

    Note that you are using style as a positional argument. Here's part of the signature of Table:

    Table(
    data,
    colWidths=None,
    rowHeights=None,
    style=None,
    ...
    )

    Meaning: here style is the fourth parameter, but when you pass style as a positional argument, it gets passed to the second parameter, i.e., colWidths, which apparently expects floats, rather than tuples, such as ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'), etc.

    So, the fix is to use keyword arguments:

    def df2table2(df, custom_style, commodity_cols):

    # ...

    return Table(data=data, style=style, hAlign='LEFT')

    Or, if you want to use positional arguments, to add values for colWidths and rowHeights before adding style:

        return Table(data, None, None, style, hAlign='LEFT')

    Snippet from result after fix:

    pdf