8

In the following example, I create a very simple pandas DataFrame and export results verbatim:

#+BEGIN_SRC python :exports both :results verbatim :return test
import pandas as pd
test = pd.DataFrame({'A': [1000, 1000], 'B' : [60, 100]})
#+END_SRC

#+RESULTS:
:       A    B
: 0  1000   60
: 1  1000  100

If I try to have the output as a table, things get messed up:

#+BEGIN_SRC python :exports both :results value table :return test
import pandas as pd
test = pd.DataFrame({'A': [1000, 1000], 'B' : [60, 100]})
#+END_SRC

#+RESULTS:
| A    B |

Is there any way to fix this?

Expected output:

|   |    A |   B |
| 0 | 1000 |  60 |
| 1 | 1000 | 100 |
7
  • I've observed similar display issues with other languages. Have you tried adding :session header to your python code block?
    – Melioratus
    Nov 21, 2016 at 4:36
  • @Melioratus yes, I have. That didn't help.
    – Daniel
    Nov 26, 2016 at 8:16
  • Using Emacs 25.1.1 which includes org-mode version 8.2.10 I can see what looks like the same problem. Tracing within org shows that everything seems normal until org-babel-python-evaluate-external-process where if ":results table" is used I see the variable "raw" ending up containing None instead of the expected results. Co-incidentally this the second org-babel problem I've seen today so perhaps it might be worth trying an updated version of org? According to orgmode.org the latest stable version is 9.0.5 which may be somewhat improved...
    – stevoooo
    Mar 29, 2017 at 20:28
  • ...it's not improved. Tried 9.0.5 which proved to be a huge pain (turns out that you have to remove the org .elc files and restart Emacs otherwise it'll complain that every source code block type has been disabled, including emacs-lisp) and it still doesn't work :-(
    – stevoooo
    Mar 29, 2017 at 20:52
  • The problem is not really with Org Mode, I think. The problem is that the Pandas DataFrame always has an empty cell on the upper left corner, i.e., it is malformed as a table. I just need a solution that parses the pandas DataFrame output and deals with this problem.
    – Daniel
    Mar 29, 2017 at 20:58

5 Answers 5

8

Update for people reading this in 2020

You can now use tabulate Python package and write:

#+begin_src python :results value raw :return tabulate(df, headers=df.columns, tablefmt='orgtbl')
from tabulate import tabulate
import pandas as pd
df = pd.DataFrame({
    "a": [1,2,3],
    "b": [4,5,6]
})
#+end_src

#+RESULTS:
|   | a | b |
|---+---+---|
| 0 | 1 | 4 |
| 1 | 2 | 5 |
| 2 | 3 | 6 |

See the org-mode documentation.

7

Here is one way to achieve what you want I think. The key is constructing a list that has what you want in it.

#+BEGIN_SRC python :exports both :results value table :return test2
import pandas as pd
test = pd.DataFrame({'A': [1000, 1000], 'B' : [60, 100]})
test2 = [list(test)] + [None] + test.values.tolist()
#+END_SRC
#+RESULTS:
|    A |   B |
|------+-----|
| 1000 |  60 |
| 1000 | 100 |
2
  • Hi John, thanks for this. I wanted to avoid having to "massage" the DataFrame from within python so that it fits within org-mode, though. I wondered if a :post function could maybe do the trick? What I also dislike about the org/python integration is that tables that mix strings and numbers are read in by src_blocks as if they were composed of strings only...
    – Daniel
    Mar 31, 2017 at 19:45
  • @dangom: you have to provide the necessary impedance matching somewhere. Org mode expects a (well-formatted) list of lists in order to produce the table, so massaging the dataframe output in the python block is probably better than anything. As for strings, that's babel for you: you have to do that in any code blocks, not just python.
    – NickD
    Jul 3, 2020 at 19:43
4
+50

You probably have to convert the dataframe to a type that org knows what to do with. For example, I can get the table contents, but not the column names or the index column like this:

 #+BEGIN_SRC python :exports both :results table
 import pandas as pd
 import numpy
 test = pd.DataFrame({'A': [1000, 1000], 'B' : [60, 100]})
 return test.as_matrix()
 #+END_SRC

 #+RESULTS:
 | 1000 |  60 |
 | 1000 | 100 |

I didn't see options to as_matrix that would let you get at the missing elements, but there might be some other such conversion that is more complete.

EDIT: apparently test.values is preferred.

EDIT: ... and this almost does it (but it might be fragile):

 #+BEGIN_SRC python :exports both :results table
 import pandas as pd
 test = pd.DataFrame({'A': [1000, 1000], 'B' : [60, 100]})
 return [x.split(',') for x in test.to_csv().split('\n')]
 #+END_SRC

 #+RESULTS:
 |   |    A |   B |
 | 0 | 1000 |  60 |
 | 1 | 1000 | 100 |
 |   |      |     |
3
  • Hi Nick, thanks for that. This is a great step it the right direction, I think. In any case I want to avoid having the return statement within the source block now, but you can use a return header argument (as long as you also have :results value table. This would work :return "[x.split(\",\") for x in test.to_csv().split(\"\\n\")[:-1]]". For now I'll put this in a yassnippet, so that I can change test to whatever dataframe I want to display. I'll give it a test and see if there are any problems.
    – Daniel
    Mar 30, 2017 at 15:33
  • It's quite robust, actually. Only problem is if there are other , somewhere in the dataFrame. For greater generality would probably need a regex.
    – Daniel
    Mar 30, 2017 at 15:57
  • 1
    I would not recommend a regex: that would make it even more fragile. If the to_csv method does a good job, then you have to worry about commas as delimiters and commas inside quoted strings; regexes would have trouble with that: you'd need a (small) parser that recognizes a slightly more general grammar.
    – NickD
    Mar 30, 2017 at 16:37
1

You can fix for the formatting of the Pandas DataFrame so it includes a value in the top left corner by setting the column name:

test = pd.DataFrame({'A': [1000, 1000], 'B' : [60, 100]}, columns=list('AB'))
test.columns.name = 'foo'

This will then produce you a table:

#+RESULTS:
: foo     A    B
: 0    1000   60
: 1    1000  100

However, I still couldn't get Org to display the result correctly. Perhaps you'll have better luck though.

2
  • Thanks stevooo. You meant test.index.name right? I guess working a bit on your solution would solve my problem as well, but ideally I wouldn't want to do things from within Python, but rather have org-mode parse the output and display it correctly.
    – Daniel
    Mar 31, 2017 at 19:47
  • I don't think setting test.index.name does quite the right thing - it seems to put the column title in the correct column but in a second line after the first two which I think would confuse Org even more than it seems to be already :-/
    – stevoooo
    Mar 31, 2017 at 20:11
0

Found this thread whilst trying to work out how to do exactly this and have another solution that leverages the to_html() method.

#+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

I'm not sure why you have to return() the object, and the table formatting is fairly crude and doesn't match that of org tables but it works (just about!).

I'd be interested to know if there is an org-babel solution to formatting Pandas data frames though as ultimately I'd like to have one org document that I can render as HTML or LaTeX and the above solution would require a second code block for LaTeX and using the to_latex() DataFrame method.

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.