Render pandas table in R markdown

user_9434790 0
Viewed 1160

I am python noob, but I am trying to render a table generated with the python code below inside of an R Markdown chunk. THe python code outputs a nice formatted table when run inside of Jupyter but I can't seem to replcate that inside of a Markdown document.

{python, engine.path = '/usr/bin/python3'}
import pandas as pd
import numpy as np

df1 = pd.read_csv(r'./data/crest_results_table.txt', sep='\t')
df2 = pd.read_csv(r'./data/crest_formats_table.txt', sep='\t')

results_table = df1.pivot_table(values=['Result'],index=['Anlys_Mthd','CAS','AnalTParam','RDCSRS','NRDCSRS','IGWSRS'],columns=['SampNum','LabID','SampDate'],aggfunc=np.max)

formats_table = df2.pivot_table(values=['Result'],index=['Anlys_Mthd','CAS','AnalTParam','RDCSRS','NRDCSRS','IGWSRS'],columns=['SampNum','LabID','SampDate'],aggfunc=np.max)

def color_cells(s):
    if s == -1:
        return 'color:{0}; background-color: white; font-weight:bold; font-style:italic; font-size:small'.format('black')
    elif s == -2:
        return 'color:{0}; background-color: orange; font-weight:bold; font-style:italic; font-size:small'.format('black')
    elif s == -3:
        return 'color:{0}; background-color: yellow; font-weight:bold; font-size:small'.format('black')
    elif s == -4:
        return 'color:{0}; background-color: beige; font-weight:bold; font-size:small'.format('black')
    else:
        return 'color:{0}; font-size:small'.format('grey')

t = results_table.style.apply(lambda x: formats_table.applymap(color_cells), axis=None)

ht = t.render()

I've tried to then use the render() method to just save it as html, and then inside of an R chunk save or print it

{r}
htmltools::save_html(py$ht, "table.html")
htmltools::html_print(py$ht)

However, when I view the saved html file it doesn't render correctly even in the browser.

The pandas docs say something about 'wrapping it in an IPython.display.HTML' but not knowing any python I'm not sure what this means.

Ideally I would like the markdown chunk to render the same table formatted as jupyter does with the exact same code.

Thanks

r-markdown pandas python-3.x
1 Answers

print(df.to_markdown()) works quite well but the result is still ugly and without interactivity. Within an R block, you can use DT::datatable(py$df)

EDIT:

found a way to display pandas df within a single python chunk with rpy2:

  1. Create R script 'pydisplaydf.r'
library(DT)

PyDisplayDf <- function(dataframe) {
    dt <- DT::datatable(dataframe, 
                  options = list(lengthChange = FALSE, 
                  sDom  = '<"top">lrt<"bottom">ip', 
                  paging = FALSE))
    dt
}]
  1. Source R script from Python and display df
from IPython.display import display

import rpy2.rinterface as rinterface
rinterface.initr()

from rpy2.robjects import pandas2ri
pandas2ri.activate()

import rpy2.robjects as robjects

r = robjects.r
pydisplaydf = r.source('pydisplaydf.r')[0]

display(pydisplaydf(df))
  1. (optional) As it is quite annoying to see the python messages popping up each time you display a df, I found this script very useful:
@contextmanager
def suppress_stdout():
    with open(os.devnull, "w") as devnull:
        old_stdout = sys.stdout
        sys.stdout = devnull
        try:  
            yield
        finally:
            sys.stdout = old_stdout

then in the Python chunk, use it like this:

with suppress_stdout():
    pandas2ri.activate()
    r = robjects.r
    pydisplaydf = r.source('pydisplaydf.r')[0]

display(pydisplaydf(df))

The df is nicely displayed in the Viewer pane of RStudio :)

user_108017000
Related