Get pandas data-frame as a table in org-babel

Question

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 |

Answer 1

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.

Answer 2

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 |

Answer 3

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

Answer 4

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.

Answer 5

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.