How can I filter table in org mode

Question

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

Answer 1

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.

Answer 2

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

Under the hood, q (via python) also uses sqlite, 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 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