7

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 |
  • I've observed similar display issues with other languages. Have you tried adding :session header to your python code block? – Melioratus Nov 21 '16 at 4:36
  • @Melioratus yes, I have. That didn't help. – dangom Nov 26 '16 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 '17 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 '17 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. – dangom Mar 29 '17 at 20:58
6

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 |
  • 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... – dangom Mar 31 '17 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 '20 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 |
 |   |      |     |
  • 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. – dangom Mar 30 '17 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. – dangom Mar 30 '17 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 '17 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.

  • 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. – dangom Mar 31 '17 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 '17 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.

0

Update for people reading this in 2020

You can now use tabulate Python package and write:

#+begin_src python :results value raw :output :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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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