11

For example, I want to filter table that make it shows the row which only contains "USA" string in the column 3 and 4.

19

You can use a multitude of solutions. I assume you want to produce a new table based on an existing one. This involves babel functionality where you define code blocks that produce the new table. The code blocks can be in many languages, and you even can define such a code block to be used afterwards normally in table formulas.

I am showing here just an example using emacs lisp. You can find many more examples in my example collection on github: https://github.com/dfeich/org-babel-examples

 *table filter

  #+NAME: table1
  | col1  | col2 | col3 | col4 | col5 |
  |-------+------+------+------+------|
  | row0  |    0 | CH   | CH   |    0 |
  | row1  |    2 | D    | CN   |    5 |
  | row2  |    4 | USA  | PL   |   10 |
  | row3  |    6 | CN   | D    |   15 |
  | row4  |    8 | JP   | USA  |   20 |
  | row5  |   10 | PL   | PL   |   25 |
  | row6  |   12 | USA  | JP   |   30 |
  | row7  |   14 | D    | CN   |   35 |
  | row8  |   16 | PL   | USA  |   40 |
  | row9  |   18 | CN   | D    |   45 |
  | row10 |   20 | CH   | CH   |   50 |

Now we define a filter function which produces a new table with the required values.

  • I am reading in the previous table by using the :var tbl=table1 argument in the BEGIN line.
  • I define the value to be filtered in the same :var assignment by setting val="USA"
  • Notice that I am using the :colnames argument in the BEGIN line in order to preserve the column headings.
  • I only filter column 4 in these examples, for simplicity. But it is trivial to extend. If you want the explicit solution, just ask.
  #+NAME: my-filter
  #+BEGIN_SRC elisp :var tbl=table1 val="USA" :colnames y
    (cl-loop for row in tbl
          if (equal (nth 3 row) val)
          collect row into newtbl
          finally return newtbl)
  #+END_SRC

  #+RESULTS: my-filter
  | col1 | col2 | col3 | col4 | col5 |
  |------+------+------+------+------|
  | row4 |    8 | JP   | USA  |   20 |
  | row8 |   16 | PL   | USA  |   40 |

I can also use this function with the org-mode CALL syntax

  #+CALL: my-filter(tbl=table1, val="CN") :colnames y

  #+RESULTS:
  | col1 | col2 | col3 | col4 | col5 |
  |------+------+------+------+------|
  | row1 |    2 | D    | CN   |    5 |
  | row7 |   14 | D    | CN   |   35 |

I also demonstrate here the SQLite approach where I use your original requirement of filtering all the rows which contain the string either in columns 3 or 4. A minor drawback of the sqlite approach is that we have some boilerplate code to read in the table and create a SQLite DB.

  #+NAME: my-filter2
  #+BEGIN_SRC sqlite :db table1.sqlite :var tbl=table1 val="USA" :colnames yes
    drop table if exists table1;
    create table table1 (col1 VARCHAR, col2 INTEGER, col3 VARCHAR,
    col4 VARCHAR, col5 INTEGER);
    .import "$tbl" table1
    select * from table1 where col3='$val' or col4='$val';
  #+END_SRC

  #+RESULTS:
  | col1 | col2 | col3 | col4 | col5 |
  |------+------+------+------+------|
  | row2 |    4 | USA  | PL   |   10 |
  | row4 |    8 | JP   | USA  |   20 |
  | row6 |   12 | USA  | JP   |   30 |
  | row8 |   16 | PL   | USA  |   40 |


  #+CALL: my-filter2(tbl=table1, val="CN") :colnames y

  #+RESULTS:
  | col1 | col2 | col3 | col4 | col5 |
  |------+------+------+------+------|
  | row1 |    2 | D    | CN   |    5 |
  | row3 |    6 | CN   | D    |   15 |
  | row7 |   14 | D    | CN   |   35 |
  | row9 |   18 | CN   | D    |   45 |

Hope that I understood your question correctly and that the links help you to find other variations of a solution.

| improve this answer | |
  • Great solution. With sqlite and gnuplot, multiple plots from a single source table can be generated with great economy. – Emacs User Feb 7 '16 at 10:21
  • Thank you for great solution! BTW, in my environment, I had to delete symbol-name function to succeed in Emacs Lisp solution. Just for mention. – RUserPassingBy Apr 17 '16 at 10:59
  • Thanks. I only realized now that I had prepared my original example from a table produced directly by a src block using the country names as symbols, so the filter was actually handed symbols and not strings. It is now corrected. – dfeich Jun 3 '16 at 19:46
0

I use q - Text as Data, and 2 functions in my library-of-babel(Conf-Example) to provide an easy interface to query/join org-inline tables and external .*sv files.

Under the hood, q (via ) also uses , like the second approach from @dfeich, but removes the need for noisy boilerplate-code specific to each individual source table. It just needs to be installed once via the system package manager, usually in python-q-text-as-data.

Once your library of babel is loaded with the 2 functions below, you only need a #+Call: like below in your org-file to use SQL queries.

#+CALL: Q[:stdin table1](where="col4=='USA'")

#+RESULTS:
| col1 | col2 | col3 | col4 | col5 |
|------+------+------+------+------|
| row4 |    8 | JP   | USA  |   20 |
| row8 |   16 | PL   | USA  |   40 |

This constructs a command-line like SELECT $select FROM $from WHERE $where, with defaults for the parameters to select all columns from stdin for output.

The code blocks to add to your library are:

** Add a header Row to tables
#+name: addhdr
#+begin_src emacs-lisp :var tbl=""
(cons (car tbl) (cons 'hline (cdr tbl)))
#+end_src

** Filtering with SQL
#+NAME: Q
#+HEADER: :results value table
#+HEADER: :var callOptsStd="-H -O -t" callOpts=""
#+HEADER: :post addhdr(*this*)
#+BEGIN_SRC shell :stdin Ethers :var select="*" from="-" where="1"
q $callOptsStd $callOpts "Select $select from $from where $where"
#+END_SRC
| improve this answer | |

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.