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.

18

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.

  • 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

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.