1

I wanted to preserve the first row value while deleting all subsequent repeated values in the sequence column ($5) as in the MWE below:

| ID | M | D | P | sequence     | item        |
|----+---+---+---+--------------+-------------|
|  1 | 1 | 1 | 1 | CGGCCTT      | 38/96       |
|  2 | 1 | 1 | 1 | CGGCCTT      | ger24       |
|  3 | 1 | 1 | 1 | CGGCCTT      | 31/54       |
|  4 | 1 | 1 | 1 | CGGCCTT      | 34/34       |
|  5 | 1 | 1 | 1 | CGGCCTT      | 43/20       |
|  6 | 1 | 1 | 1 | CGGCCTT      | 45/134      |
| 23 | 1 | 1 | 2 | TTAAACCGG    | 53/36       |
| 23 | 1 | 1 | 2 | TTAAACCGG    | 53/47       |
| 24 | 1 | 1 | 2 | TTAAACCGG    | 13/31       |
| 25 | 1 | 1 | 2 | TTAAACCGG    | 33/72       |
| 26 | 1 | 1 | 2 | TTAAACCGG    | 49/31       |
| 27 | 1 | 1 | 2 | TTAAACCGG    | 47/62       |
| 28 | 1 | 1 | 2 | TTAAACCGG    | 53/3        |
| 29 | 1 | 1 | 2 | TTAAACCGG    | 59/29       |
| 30 | 1 | 1 | 2 | TTAAACCGG    | 47/24       |
| 31 | 1 | 1 | 3 | CCGGTTAAA    | 52/63       |
| 32 | 1 | 1 | 3 | CCGGTTAAA    | 35/90       |
| 33 | 1 | 1 | 3 | CCGGTTAAA    | 67/3        |
| 34 | 1 | 1 | 3 | CCGGTTAAA    | 38/76       |
| 35 | 1 | 2 | 1 | TTCCGGAATTTT | 41/30       |
| 36 | 1 | 2 | 1 | TTCCGGAATTTT | 18/10       |
| 37 | 1 | 2 | 1 | TTCCGGAATTTT | 42/35       |
| 38 | 1 | 2 | 1 | TTCCGGAATTTT | 43/33       |
| 39 | 1 | 2 | 1 | TTCCGGAATTTT | ger30       |
| 40 | 1 | 2 | 2 | TTCCGGAATTTT | 49/31       |

Desired Output

| ID | M | D | P | sequence     | item     |
|----+---+---+---+--------------+----------|
|  1 | 1 | 1 | 1 | CGGCCTT      | 38/96    |
|  2 | 1 | 1 | 1 |              | ger24    |
|  3 | 1 | 1 | 1 |              | 31/54    |
|  4 | 1 | 1 | 1 |              | 34/34    |
|  5 | 1 | 1 | 1 |              | 43/20    |
|  6 | 1 | 1 | 1 |              | 45/134   |
| 23 | 1 | 1 | 2 | TTAAACCGG    | 53/36    |
| 23 | 1 | 1 | 2 |              | 53/47    |
| 24 | 1 | 1 | 2 |              | 13/31    |
| 25 | 1 | 1 | 2 |              | 33/72    |
| 26 | 1 | 1 | 2 |              | 49/31    |
| 27 | 1 | 1 | 2 |              | 47/62    |
| 28 | 1 | 1 | 2 |              | 53/3     |
| 29 | 1 | 1 | 2 |              | 59/29    |
| 30 | 1 | 1 | 2 |              | 47/24    |
| 31 | 1 | 1 | 3 | CCGGTTAAA    | 52/63    |
| 32 | 1 | 1 | 3 |              | 35/90    |
| 33 | 1 | 1 | 3 |              | 67/3     |
| 34 | 1 | 1 | 3 |              | 38/76    |
| 35 | 1 | 2 | 1 | TTCCGGAATTTT | 41/30    |
| 36 | 1 | 2 | 1 |              | 18/10    |
| 37 | 1 | 2 | 1 |              | 42/35    |
| 38 | 1 | 2 | 1 |              | 43/33    |
| 39 | 1 | 2 | 1 |              | ger30    |
| 40 | 1 | 2 | 2 |              | 49/31    |

My unsuccessful trial was by trying:

#+TBLFM: $5='(delete-dups (list @2..@26));L

Side note: I noticed one can hit C-c ^ while the pointer is on the desired column header to invoke sorting options, according to date, alphabet, but I noticed also there is the [f]unction among them , I wonder how can one apply this function to eliminate duplicates in any column of the org-table. Your help is much appreciated.

More Experiments

When I try this in block code after adding quotations to the elements:

#+BEGIN_SRC emacs-lisp
(delete-dups (list "CGGCCTT"  "CGGCCTT"  "CGGCCTT"  "CGGCCTT"  "CGGCCTT"  "CGGCCTT" "TTAAACCGG" "TTAAACCGG" "TTAAACCGG" "TTAAACCGG" "TTAAACCGG" "TTAAACCGG" "TTAAACCGG" "TTAAACCGG" "TTAAACCGG" "CCGGTTAAA" "CCGGTTAAA" "CCGGTTAAA" "CCGGTTAAA" "TTCCGGAATTTT" "TTCCGGAATTTT" "TTCCGGAATTTT" "TTCCGGAATTTT" "TTCCGGAATTTT" "TTCCGGAATTTT"))
#+END_SRC

This will give me this result after evaluation:

("CGGCCTT" "TTAAACCGG" "CCGGTTAAA" "TTCCGGAATTTT")

Which is sort of what I want, but wait this is not the desired effect unless somehow there will be also empty elements in the list to replace those duplicate removed elements, am I right? Besides quotation marks should be added somehow in the table formula. It seems applying all this in table formula is far more complicated than I thought.

2

This can easily be done with a memory that remembers the last used sequence. I use a global variable my-var for that purpose in the following.

#+TBLFM: $5='(if (and (/= @# 2) (string= @0 my-var)) "" (setq my-var @0))

The failing test for (/= @# 2) at the beginning serves for the initialization of my-var and subsequent successful tests for (string= @0 my-var) replace duplicates by the empty string.

  • Great! but I think at the beginning (/= @# 2) was not failing, it was successful, the other part was failing (string= @0 my-var) and letting the my-var to be initialized, right? – doctorate Apr 11 '18 at 2:03
  • @doctorate No, the headline counts as 1 but is not iterated. The first sequence counts as 2 and therefore (/= @# 2) fails and (setq my-var @0)) is evaluated. In all other sequence rows (/= @# 2) is successful and (string= @0 my-var) counts. If the actual value is the old one then the empty string is returned and the value of (setq my-var @0) otherwise. – Tobias Apr 11 '18 at 7:04
  • I see, so in the first iteration for the header (@# = 1 here) so (/= @# 2) would be t and (string= @0 my-var) is f so in effect the and conditional is f and this will lead to the desired evaluation of the (setq my-var @0) to initialize my-var, right? – doctorate Apr 11 '18 at 10:07
  • @doctorate No the header is not evaluated. Please switch on Tbl -> Debug Formulas and press C-c C-c on the string TBLFM. You will see exactly the evaluation process step-by-step. – Tobias Apr 11 '18 at 10:27

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.