Plot a suitable graph based on dataframe In R and convert it into HTML

Question

I have below mentioned dataframe:

DF_1>

    Month  # of A  M  Sum of A  M Med A  Mean A   # of B  M  Sum of B M  Median B  Mean B

    Mar-17 10      -  100000    -        -        6       -  15000    -  -         -      
    Feb-17 22      -  150000    -        -        8       -  22000    -  -         -
    Jan-17 25      -  200000    -        -        3       -  23000    -  -         -

Marked other Field as - because those value i'm not using to Plot Graph, below are the screenshot of same Dataframe in html format. (excluding above caption and 2 row rest of the dataframe are same)

With one additional row at the bottom which gives Total, How to Plot one graph for both Status of A and Status of B Using # of A,Sum of A,# of B and Sum of B data point.

Where month should be on Y axis, and other Data point on X axis with Heading of Graph like ABC.

And further, i want to convert that Graph into Html format which can easily mailed through mailR library.

I already have 2 html table which cover my mail body like below:

AAAA CCCC

BBBBBBBBB

Where AAAA is one table and BBBBBBBBB is second table, CCCC is blank space where i want to arrange this graph.

Answer 1

An alternate approach and an extension to that provided by @clemens is to prepare the email as rmarkdown.

example .Rmd

---
title: example
output: html_document
---

```{r setup, include=FALSE}
library(knitr)
library(kableExtra)
library(tidyverse)
library(data.table)
```

```{r status, echo=FALSE}
status <- tibble(
  Month = factor(
    c('Jan-17', 'Feb-17', 'Mar-17'),
    levels = c('Jan-17', 'Feb-17', 'Mar-17')
  ),
  '# of A' = c(100000, 150000, 200000),
  'Sum of A' = c(6, 8, 3),
  '# of B' = c(150000, 22000, 23000),
  'Sum of B' = c(2, 4, 6)
)
kable(status, format = "html") %>% kable_styling
```

```{r plot, echo=FALSE}
status_m <- melt(status, id.vars = "Month")

ggplot(status_m, aes(x = Month, y = value, group = variable, color = variable)) +
  geom_line() +
  theme(legend.title = element_blank())
```

```{r status2, echo=FALSE}
kable(status, format = "html") %>% kable_styling
```

This way you can render the html output in a straight forward and reproducible way without needing to cater for images or other content.

example.R

The send script is then a very minor alteration from the previous answer.

library(mailR)

send.mail(from = "someone@gmail.com",
          to = "someone.elser@mail.com",
          subject = "test report",
          body = rmarkdown::render("example.Rmd"), # note we render here
          html = TRUE,
          inline = TRUE,
          smtp = list(host.name = "smtp.gmail.com", 
                      port = 465, 
                      user.name = "...", 
                      passwd = "...", 
                      ssl = TRUE),
          authenticate = TRUE,
          send = TRUE)

HTML Output

This is the output of the rendered rmarkdown file to be sent inline in the email.

Answer 2

I have recreated the table since you did not provide it, this is what I am using (Note: I have added some fake numbers for sum of B since it is missing in the text):

status <- tibble::tibble(Month = factor(c('Jan-17', 'Feb-17', 'Mar-17'),
                                        levels = c('Jan-17', 'Feb-17', 'Mar-17')),
                         '# of A' = c(100000, 150000, 200000),
                         'Sum of A' = c(6, 8, 3),
                         '# of B' = c(150000, 22000, 23000),
                         'Sum of B' = c(2, 4, 6))

I then bring it from wide to long format using data.table::melt():

status_m <- data.table::melt(status, 
                             id.vars = "Month")

I then use ggplot2 to create a line chart (Note: # of A and # of B are on a similar scale, Sum of A and Sum of B are on a similar scale, but the difference between the #s and the Sums is too large):

p <- ggplot(data = status_m) +
  geom_line(aes(x = Month,
                y = value,
                group = variable,
                color = variable)) +
  theme(legend.title=element_blank())

Save that plot using ggsave():

ggsave("plot.png",
       plot = p,
       width = 10,
       height = 10)

Next, I create a 'dummy' tableHTML:

th <- status %>% 
  tableHTML(rownames = FALSE)

I then create some HTML that recreates the layout you specified, with 2 rows, where the first row has 2 columns (Note: <div>s and CSS flex, see here).

mail_html <-
  htmltools::HTML(paste("<!DOCTYPE html>\n<html>\n<body>", 
                        '<div style="display:flex;">',
                        '<div style="flex:50%;">',
                        th,
                        '</div>',
                        '<div style="flex:50%;alig">',
                        # change this path to the plot you saved using ggsave()
                        '<img src="/path/to/plot.png" height = "800" width = "800";">',
                        '</div>',
                        '</div>',
                        '<div>',
                        th,
                        '</div>',
                        "</body>\n</html>", sep = "\n"))

The last step is to send the email using mailR (Note: change the mail settings accordingly):

library(mailR)

send.mail(from = "someone@gmail.com",
          to = "someone.elser@mail.com",
          subject = "test report",
          body = mail_html,
          html = TRUE,
          inline = TRUE,
          smtp = list(host.name = "smtp.gmail.com", 
                      port = 465, 
                      user.name = "...", 
                      passwd = "...", 
                      ssl = TRUE),
          authenticate = TRUE,
          send = TRUE)

The test email looks like this: