Elegant way to read emacs org-mode tables into a python pandas dataframe

user_406686 0
Viewed 974

When working with python pandas I often like to create tables with emacs org-mode. To read the table I do something like

import pandas as pd
from numpy import *

D = pd.read_csv('file.dat',sep='|')
D = D.drop(D.columns[0], axis=1)
D = D.drop(D.columns[-1], axis=1)
D = D.rename(columns=lambda x: x.strip())

Is there a more elegant (in particular shorter) way to read the org-mode table into a pandas dataframe? Maybe there is also an elegant way to keep table and python source in the same org-file.

pandas dataframe org-mode emacs python
2 Answers

Try with

D = pd.read_csv('file.dat', sep='\s*\|\s*').iloc[:, 1:-1]
user_42384080

Here's an answer to the modified question (keeping the table and the source code in the Org mode file). I've stolen the pandas part from Quang Hoang's answer:

* foo

Here's a table:

  #+NAME: foo
  |  a |   b |    c |
  |----+-----+------|
  |  1 |   1 |    1 |
  |  2 |   4 |    8 |
  |  3 |   9 |   27 |
  |  4 |  16 |   64 |
  |  5 |  25 |  125 |
  |  6 |  36 |  216 |
  |  7 |  49 |  343 |
  |  8 |  64 |  512 |
  |  9 |  81 |  729 |
  | 10 | 100 | 1000 |
#+TBLFM: $2=pow($1, 2) :: $3 = pow($1, 3)

Here's a source block that initializes the variable `tbl' with the table `foo' above 
and does to it some pandas things as suggested by Quang Hoang in his answer.
To evaluate the code block, press `C-C C-c' in the code block.
You will then get the result below:

  #+begin_src python :var tbl=foo :results output

    import pandas as pd

    D = pd.DataFrame(tbl).iloc[:, 1:-1]
    print(D)
  #+end_src

  #+RESULTS:
  #+begin_example
       1
  0    1
  1    4
  2    9
  3   16
  4   25
  5   36
  6   49
  7   64
  8   81
  9  100
  #+end_example

See the Org manual for (much) more information about source blocks.

EDIT: To preserve the column names (the first row of the table), you can add :colnames no to the source block header. The column names themselves are obtained inside the source block as tbl[0] and one can use that in the DataFrame constructor to label the columns as follows (n.b. In contrast to above, the DataFrame is the complete table. I just use a couple of different methods to select pieces of it to print out, including the D.c method you asked about in a comment):

  #+begin_src python :var tbl=foo :results output :colnames no

    import pandas as pd

    D = pd.DataFrame(tbl, columns=tbl[0])
    print(D.c)
    print("===========")
    print(D.iloc[1:, 0:-1])
  #+end_src

  #+RESULTS:
  #+begin_example
  0        c
  1        1
  2        8
  3       27
  4       64
  5      125
  6      216
  7      343
  8      512
  9      729
  10    1000
  Name: c, dtype: object
  ===========
       a    b
  1    1    1
  2    2    4
  3    3    9
  4    4   16
  5    5   25
  6    6   36
  7    7   49
  8    8   64
  9    9   81
  10  10  100
  #+end_example

Why :colnames no (and not :colnames yes) is needed to add the column names is something that I have never understood: one of these days, I should post a question on the Org mode mailing list about it...

user_25617140
Related