Pandas data frame as table

Question

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?

Answer 1

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 |

Answer 2

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