0

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">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>x</th>
      <th>y</th>
    </tr>
  </thead>
  <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?

2 Answers 2

2

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 |
4
  • 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, 2020 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, 2020 at 20:26
  • 1
    Added two small examples to illustrate what Org Babel expects. I hope it helps to clarify.
    – NickD
    Jul 4, 2020 at 13:03
  • Thats great, thank you very much for your time and assistance @NickD
    – slackline
    Jul 6, 2020 at 8:55
0

First install jupyter package from emacs and add these two lines to your init:

(add-to-list 'load-path "~/path/to/jupyter")
(require 'jupyter)

Then, when you need to see a Pandas dataframe just add :display plain to your source block.

Example:

#+begin_src jupyter-python :session py :display plain 
  import pandas as pd
  pd.read_csv("https://raw.githubusercontent.com/cs109/2014_data/master/countries.csv")
#+end_src

#+RESULTS:
#+begin_example
         Country         Region
  0      Algeria         AFRICA
  1       Angola         AFRICA
  2        Benin         AFRICA
  3     Botswana         AFRICA
  4      Burkina         AFRICA
  ..         ...            ...
  189   Paraguay  SOUTH AMERICA
  190       Peru  SOUTH AMERICA
  191   Suriname  SOUTH AMERICA
  192    Uruguay  SOUTH AMERICA
  193  Venezuela  SOUTH AMERICA

  [194 rows x 2 columns]
#+end_example  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.