I have a dataframe look like this

                 date       value  TCE value  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)

CodePudding user response：

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