Pandas data frame as table

I'm enjoying learning org-babel to write literate documents and am trying to include summary tables produced by Pandas describe().

I found an old thread with some answers but none of the provided solutions seemed to satisfy the aim of the original poster and they seem quite clunky so I thought I'd try playing around.

Starting with the working solution I have

#+BEGIN_SRC python :exports results :results value table :return summary
import pandas as pd
import numpy as np

n = 1000
low = 0
high = 100
df = pd.DataFrame({'x': np.random.random_integers(low, high, size=n),
'y': np.random.random_integers(low, high, size=n)})

summary = df.describe()
summary = [list(summary)] + [None] + summary.values.tolist()
#+END_SRC

#+RESULTS:
|                  x |                  y |
|--------------------+--------------------|
|             1000.0 |             1000.0 |
|             49.743 |             49.326 |
| 29.186517500445365 | 29.128580435685468 |
|                0.0 |                0.0 |
|               26.0 |               24.0 |
|               49.0 |               48.0 |
|               76.0 |               75.0 |
|              100.0 |              100.0 |



And the table renders, however it has lost the index which defines what the rows are (count, mean, sd, min, 25%, 50%, 75%, max).

Pandas DataFrames have a to_html() method so I figured that might be a viable option to return that, and it works...

#+BEGIN_SRC python :exports results :results html
import pandas as pd
import numpy as np

n = 1000
low = 0
high = 100
df = pd.DataFrame({'x': np.random.random_integers(low, high, size=n),
'y': np.random.random_integers(low, high, size=n)})

summary = df.describe()
return(summary.to_html())
#+END_SRC

#+RESULTS:
#+begin_export html
<table border="1" class="dataframe">
<tr style="text-align: right;">
<th></th>
<th>x</th>
<th>y</th>
</tr>
<tbody>
<tr>
<th>count</th>
<td>1000.00000</td>
<td>1000.00000</td>
</tr>
<tr>
<th>mean</th>
<td>51.51800</td>
<td>49.76100</td>
</tr>
<tr>
<th>std</th>
<td>29.75643</td>
<td>28.97149</td>
</tr>
<tr>
<th>min</th>
<td>0.00000</td>
<td>0.00000</td>
</tr>
<tr>
<th>25%</th>
<td>26.00000</td>
<td>25.00000</td>
</tr>
<tr>
<th>50%</th>
<td>52.00000</td>
<td>48.00000</td>
</tr>
<tr>
<th>75%</th>
<td>78.00000</td>
<td>76.00000</td>
</tr>
<tr>
<th>max</th>
<td>100.00000</td>
<td>100.00000</td>
</tr>
</tbody>
</table>
#+end_export


But, what if I wanted to compile the document to LaTeX? HTML tables wouldn't be rendered correctly and I'd need to leverage the to_latex() method instead...

#+BEGIN_SRC python :exports results :results latex
import pandas as pd
import numpy as np

n = 1000
low = 0
high = 100
df = pd.DataFrame({'x': np.random.random_integers(low, high, size=n),
'y': np.random.random_integers(low, high, size=n)})

summary = df.describe()
return(summary.to_latex())
#+END_SRC

#+RESULTS:
#+begin_export latex
\begin{tabular}{lrr}
\toprule
{} &            x &            y \\
\midrule
count &  1000.000000 &  1000.000000 \\
mean  &    48.942000 &    50.595000 \\
std   &    28.681026 &    28.868848 \\
min   &     0.000000 &     0.000000 \\
25\%   &    24.000000 &    25.000000 \\
50\%   &    48.000000 &    50.000000 \\
75\%   &    73.000000 &    76.000000 \\
max   &   100.000000 &   100.000000 \\
\bottomrule
\end{tabular}
#+end_export


One of the appealing aspects, to me at least, of literate programming and org-babel is the ability to have one source file that can be output to multiple different formats on execution/compilation, but I can't work out (mainly through lack of knowledge/understanding) how to include Pandas DataFrames in a generic manner in the resulting documents.

Is it possible to have a block of code return a generic table that is rendered depending on the target output?

It depends on what you want to do with the tables of course, but assuming that you want to produce generic Org mode tables that can be exported in the standard way, you will have to make the python block produce the structure that Org Babel expects in order to produce the generic Org mode table. That structure is a list of lists: first a list for the headers, then a list like this: [None] in order to produce the hline and then a list of lists for the rows.

So the header list should be:

[ ' ', 'x', 'y']


We have to add an empty entry since pandas does not label the index column.

To get the rows, we'll get the index column and the values columns and knit them together appropriately: we zip them together and then use a list comprehension to construct each row. Most of this is just pythonisms to get the lists correct:

desc = df.describe()
summary =  [list(' ') + list(desc)] + [None] + [ [x[0]] + x[1] for x in zip(desc.index.array, desc.values.tolist())]


Here's what it looks like when you put it all together:

#+BEGIN_SRC python :exports results :results value table :return summary
import pandas as pd
import numpy as np

n = 1000
low = 0
high = 100
df = pd.DataFrame({'x': np.random.random_integers(low, high, size=n),
'y': np.random.random_integers(low, high, size=n)})

desc = df.describe()
summary =  [list(' ') + list(desc)] + [None] + [ [x[0]]+x[1] for x in zip(desc.index.array, desc.values.tolist())]
#+END_SRC

#+RESULTS:
|       |                  x |                 y |
|-------+--------------------+-------------------|
| count |             1000.0 |            1000.0 |
| mean  |             50.384 |            50.238 |
| std   | 28.442354183708364 | 29.16724334050244 |
| min   |                0.0 |               0.0 |
| 25%   |               26.0 |             24.75 |
| 50%   |               51.0 |              50.0 |
| 75%   |               74.0 |              76.0 |
| max   |              100.0 |             100.0 |



EDIT: Just to clarify: this has nothing much to do with Pandas (or Python or elisp or ...); it has more to do with what Org Babel expects in order to produce a table. So here are two small examples with constant tables, one in python and one in elisp. I hope that clarifies what the result should be in each case in order to satisfy Org Babel's requirements. It is then up to you to arrange your program to produce a result of the proper form:

#+begin_src python :results value table :return summary
summary = [ ['x', 'y'], None, [1,1], [2,4], [3, 9] ]
#+end_src

#+RESULTS:
| x | y |
|---+---|
| 1 | 1 |
| 2 | 4 |
| 3 | 9 |

#+begin_src emacs-lisp :results value table
(setq summary '(("x" "y") hline (1 1) (2 4) (3 9)))
#+end_src

#+RESULTS:
| x | y |
|---+---|
| 1 | 1 |
| 2 | 4 |
| 3 | 9 |

• Thanks for the explanation and example, thats really useful. Its neat that a little bit of list comprehension does the trick. I'll see if I can build on this and come up with a generic function that outputs any arbitrary table in the required format. I'm also going to need to do similar for R which I also use and would like to format the tables so that Org recognises them too. Some searching has led me to orgreport which I might be able to leverage too. – slackline Jul 3 '20 at 19:53
• It's basically a matter of impedance matching between what pandas produces and what Org babel expects in order to be able to output a table. – NickD Jul 3 '20 at 20:26
• Added two small examples to illustrate what Org Babel expects. I hope it helps to clarify. – NickD Jul 4 '20 at 13:03
• Thats great, thank you very much for your time and assistance @NickD – slackline Jul 6 '20 at 8:55

Install tabulate (conda install -c conda-forge tabulate or pip install tabulate), then do

from tabulate import tabulate