from dataframe to the body of Email automatically,several formatting issues: thousand separator, color(red for negative number and green for positive)

I have a dataframe look like this

                 date       value    value 2  daily value change
shortCode                                                       
TD1        2023-01-06       38.67    15162.0               -1.00
TD2        2023-01-06       53.42    33952.0               -0.40
TD3C       2023-01-06       52.91    30486.0               -0.36
TD6        2023-01-06      169.61    90824.0               -3.83
TD7        2023-01-06      168.56    66685.0               -1.25
TD8        2023-01-06      244.29    71413.0               -2.42
TD9        2023-01-06      129.38    24498.0               -2.50
TD14       2023-01-06      251.19    81252.0               -0.81
TD15       2023-01-06       54.03    32382.0               -0.56
TD18       2023-01-06      425.08    71615.0               -2.42

I wish to send it as the BODY of the Email with Outlook, it would be great to automate it in the future (as daily report without human intervention) but for the moment I just struggle to achieve some formatting

1. how to get it directly to the body of Email or I have to go via Excel?
2. to have all the column headers shown properly, when go through Excel they are partly hidden and have to click manually to show the full title
3. add thousand separator without adding the unnecessary .0 to the “TCE value” column, not sure why it has .0 now
4. in the columns like “daily value change”(I have a few more columns not shown due to size),
   having green color for positive numbers and red for negatives.

what I did: for thousand separator

df_bdti_final[['value', 'TCE value',
        ]] = df_bdti_final[['value', 'TCE value']].iloc[:, :].applymap('{:,}'.format)

Answer

You can use some excel writer engine like xlsxwriter and its formatting APIs. The documentation links:

• https://xlsxwriter.readthedocs.io/example_conditional_format.html
• https://xlsxwriter.readthedocs.io/worksheet.html

A sample usage for your use case is as follows. You can use above links to customize the formattings:

import xlsxwriter

with pd.ExcelWriter("report.xlsx", mode="w", engine="xlsxwriter") as writer:
    # Format "TCE value" as int
    df["TCE value"] = pd.to_numeric(df["TCE value"], errors='coerce').fillna(0).astype('Int64')

    df.to_excel(writer, sheet_name="report", index=False)
    wb  = writer.book
    ws = writer.sheets["report"]

    # Auto-fit width
    for c in df:
        max_width = max(df[c].astype(str).map(len).max(), len(c))
        col_idx = df.columns.get_loc(c)
        ws.set_column(col_idx, col_idx, max_width)

    # Format for integers with thousand separator
    fmt_int_with_th_sep = wb.add_format({'num_format': '#,###'})

    # Format for red text
    fmt_red = wb.add_format({'font_color': '#9C0006'})

    # Format for green text
    fmt_green = wb.add_format({'font_color': '#006100'})

    ##### Format columns as required #####

    # Set thousand separator for "TCE value"
    col_idx = df.columns.get_loc("TCE value")
    ws.set_column(col_idx, col_idx, None, fmt_int_with_th_sep)

    # Set reg and green for negative and positive values
    col_idx = df.columns.get_loc("daily value change")
    ws.conditional_format(1, col_idx, len(df) + 1, col_idx, {'type': 'cell', 'criteria': '<', 'value': 0, 'format': fmt_red})
    ws.conditional_format(1, col_idx, len(df) + 1, col_idx, {'type': 'cell', 'criteria': '>', 'value': 0, 'format': fmt_green})

Output is truncated due to sensitive nature of data as requested.