<p>OK. This is a bit convoluted, but it works. There might be simpler solutions...</p>

<p>First we'll write a function that takes a data frame and a character position (ie; first or second position in the string), groups the data frame by the second and third columns, counts occurrences of each character in the specified position of the 18th column, and returns a vector according to your ranking scheme (this doesn't include counts of N since it doesn't occur in your example data, but it's a simple extension of the <code>case_when</code> line to add it):</p>

<pre><code>library(dplyr)
library(tidyr)
library(stringr)

summarise_characters &lt;- function(df, pos) {
  df %&gt;% mutate(character=str_sub(X18, pos, pos)) %&gt;% 
  group_by(X2, X3, character) %&gt;% 
  summarise(n=n()) %&gt;% 
  ungroup() %&gt;% 
  spread(character, n, fill=0) %&gt;% 
  mutate(pos1=case_when(`@` &gt; ß ~ '@', 
                        `@` == ß ~ 'N', 
                        `@` &lt; ß ~ 'ß')) %&gt;%
  `$`(pos1)
}
</code></pre>

<p>Next we remove duplicate lines from the data frame, then create a new column, pasting together the output of our function called on the first and second character:</p>

<pre><code>df %&gt;% group_by(X2, X3) %&gt;%  
  slice(1) %&gt;%
  ungroup() %&gt;%
  mutate(counts=paste0(summarise_characters(df, 1), summarise_characters(df, 2))) %&gt;% 
  select(X1, X2, X3, X4, X11, X12, X13, counts)

# A tibble: 3 x 8
     X1        X2        X3                   X4       X11                  X12       X13 counts
  &lt;int&gt;     &lt;int&gt;     &lt;int&gt;                &lt;chr&gt;     &lt;int&gt;                &lt;chr&gt;     &lt;int&gt;  &lt;chr&gt;
1     1  90042011  90042031 AGTGCCACCAGGTGGGGCCG  90184354 GAGGCCACAAGAGGGCACAA  90184337     @N
2     1  94853396  94853416 GCCGCCACTAGATGGTGCTA  94969274 CAGTCCTCTAGAGGGAGCCC  94969266     ß@
3     2 103584283 103584303 TGGGCCACTAGGAGGCACTG 103841456 CATGCCACAAGAGGGCATCA 103841456     NN
</code></pre>

CC BY-SA 3.0
2018-01-18T13:01:46.343Z
This looks good. One question, what is the position input in the first function? Because I want to do this to all such repeating lines in my dataset
rishi
CC BY-SA 3.0
2018-01-18T13:07:07.843Z
position in the character string in column 18. As I understand it you want to use the same logic on both the first and second position in this field
heathobrien
CC BY-SA 3.0
2018-01-18T13:19:41.203Z
It gives me this error:  Error in mutate_impl(.data, dots) : 
  C stack usage  7970884 is too close to the limit
rishi
CC BY-SA 3.0
2018-01-18T13:25:08.887Z
Interesting. What are the names of the columns in your data frame?
heathobrien
CC BY-SA 3.0
2018-01-18T13:27:42.913Z
It's V1,V2,V3. But I replaced the Xs in your code with Vs already. And changed 18 to 20 since it was actually $20 as mentioned in my question.
rishi
CC BY-SA 3.0
2018-01-18T13:30:51.403Z
Also I noticed that X11, X12 and X13 are in not the same order as X2, X3 and X4 as how it would be ideal. Is this causing the error?
rishi
CC BY-SA 3.0
2018-01-18T13:32:47.630Z
It's column 18 in your example data. I'm not going to be able to figure out the source of the error if you're working with different data than the example
heathobrien
CC BY-SA 3.0
2018-01-18T13:33:04.083Z
Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/71859/discussion-between-maharshi-chakravortee-and-heathobrien).
rishi
CC BY-SA 3.0
2018-01-18T12:52:14.460Z
2018-01-18T13:08:53.410Z
2018-01-18T13:08:53.410Z
heathobrien
<p>I have a file with sample data like this:</p>

<pre><code>1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042017    2   5008    T:5006  C:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184337    2   5008    C:5007  T:1     ß@  5
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042017    2   5008    T:5006  C:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184336    2   5008    A:5007  G:1     ßß  2
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042030    2   5008    C:5007  A:1     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184337    2   5008    C:5007  T:1     @@  1
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042030    2   5008    C:5007  A:1     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184336    2   5008    A:5007  G:1     @ß  3
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042029    2   5008    G:5006  T:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184337    2   5008    C:5007  T:1     @@  1
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042029    2   5008    G:5006  T:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184336    2   5008    A:5007  G:1     @ß  3
1   94853396    94853416    GCCGCCACTAGATGGTGCTA    94853398    2   5008    T:5007  C:1     94969254    94969274    CAGTCCTCTAGAGGGAGCCC    94969266    2   5008    G:5006  A:2     ß@  5
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584300   2   5008    C:5006  T:2     103841436   103841456   CATGCCACAAGAGGGCATCA    103841456   2   5008    A:5006  G:2     @ß  3
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584300   2   5008    C:5006  T:2     103841436   103841456   CATGCCACAAGAGGGCATCA    103841445   2   5008    C:5006  T:2     @@  1
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584284   2   5008    A:5007  G:1     103841436   103841456   CATGCCACAAGAGGGCATCA    103841456   2   5008    A:5006  G:2     ßß  2
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584284   2   5008    A:5007  G:1     103841436   103841456   CATGCCACAAGAGGGCATCA    103841445   2   5008    C:5006  T:2     ß@  5
</code></pre>

<p>As seen, some lines have repeating values for <code>$2</code> and <code>$3</code>, line 1-6 and line 8-11 in this case.</p>

<p><code>$20</code> of my file contains codes of two characters representing each line, in the format "@@","ßß", "@ß","@N","ß@","ßN","N@","Nß" or "NN". Each line can have one of these 9 possible codes.</p>

<p>I want to write a script, which splits the characters of <code>$20</code> (@,ß or N) of the lines which are repeated (1-6 &amp; 8-11 here), and reduces them into one line in the following manner:</p>

<p>Count the number of @, ß or N in the first and second positions respectively of the repeated lines. For the first and second characters of each repeated line, if:</p>

<ol>
<li>@ occurs most, print @</li>
<li>ß occurs most, print ß</li>
<li>N occurs most, print N</li>
<li>@ occurs equal to ß, print N</li>
<li>@ occurs equal to N, print @</li>
<li>ß occurs equal to N, print ß</li>
</ol>

<p>In this sample dataset with repeated lines, the output file generated should be something like this, only columns <code>$1</code>, <code>$2</code>, <code>$3</code>, <code>$4</code>, <code>$11</code>,<code>$12</code>,<code>$13</code>, and a new column with the new "@","ß","N" codes after said counting is reported:</p>

<pre><code>1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90184334    90184354    GAGGCCACAAGAGGGCACAA    @N  
1   94853396    94853416    GCCGCCACTAGATGGTGCTA    94969254    94969274    CAGTCCTCTAGAGGGAGCCC    ß@  
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103841436   103841456   CATGCCACAAGAGGGCATCA    NN  
</code></pre>

<p>In the first repeated case(1-6), for the first character @ occurs four times and ß occurs twice and for the second character @ and ß occur equal number of times, so the final code is @N. In the second repeated case(8-11), @ and ß for both character positions occur equally, so the final code is NN.</p>

<r><bioawk><text-processing>
Splitting characters of a column in repeated lines and reducing them to one line
# Splitting characters of a column in repeated lines and reducing them to one line

(Asked by: username_0 on 2018-01-18)

I have a file with sample data like this:
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042017    2   5008    T:5006  C:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184337    2   5008    C:5007  T:1     ß@  5
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042017    2   5008    T:5006  C:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184336    2   5008    A:5007  G:1     ßß  2
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042030    2   5008    C:5007  A:1     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184337    2   5008    C:5007  T:1     @@  1
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042030    2   5008    C:5007  A:1     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184336    2   5008    A:5007  G:1     @ß  3
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042029    2   5008    G:5006  T:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184337    2   5008    C:5007  T:1     @@  1
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90042029    2   5008    G:5006  T:2     90184334    90184354    GAGGCCACAAGAGGGCACAA    90184336    2   5008    A:5007  G:1     @ß  3
1   94853396    94853416    GCCGCCACTAGATGGTGCTA    94853398    2   5008    T:5007  C:1     94969254    94969274    CAGTCCTCTAGAGGGAGCCC    94969266    2   5008    G:5006  A:2     ß@  5
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584300   2   5008    C:5006  T:2     103841436   103841456   CATGCCACAAGAGGGCATCA    103841456   2   5008    A:5006  G:2     @ß  3
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584300   2   5008    C:5006  T:2     103841436   103841456   CATGCCACAAGAGGGCATCA    103841445   2   5008    C:5006  T:2     @@  1
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584284   2   5008    A:5007  G:1     103841436   103841456   CATGCCACAAGAGGGCATCA    103841456   2   5008    A:5006  G:2     ßß  2
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103584284   2   5008    A:5007  G:1     103841436   103841456   CATGCCACAAGAGGGCATCA    103841445   2   5008    C:5006  T:2     ß@  5

As seen, some lines have repeating values for $2 and $3, line 1-6 and line 8-11 in this case.
$20 of my file contains codes of two characters representing each line, in the format "@@","ßß", "@ß","@N","ß@","ßN","N@","Nß" or "NN". Each line can have one of these 9 possible codes.
I want to write a script, which splits the characters of $20 (@,ß or N) of the lines which are repeated (1-6 & 8-11 here), and reduces them into one line in the following manner:
Count the number of @, ß or N in the first and second positions respectively of the repeated lines. For the first and second characters of each repeated line, if:

@ occurs most, print @
ß occurs most, print ß
N occurs most, print N
@ occurs equal to ß, print N
@ occurs equal to N, print @
ß occurs equal to N, print ß

In this sample dataset with repeated lines, the output file generated should be something like this, only columns $1, $2, $3, $4, $11,$12,$13, and a new column with the new "@","ß","N" codes after said counting is reported:
1   90042011    90042031    AGTGCCACCAGGTGGGGCCG    90184334    90184354    GAGGCCACAAGAGGGCACAA    @N  
1   94853396    94853416    GCCGCCACTAGATGGTGCTA    94969254    94969274    CAGTCCTCTAGAGGGAGCCC    ß@  
2   103584283   103584303   TGGGCCACTAGGAGGCACTG    103841436   103841456   CATGCCACAAGAGGGCATCA    NN  

In the first repeated case(1-6), for the first character @ occurs four times and ß occurs twice and for the second character @ and ß occur equal number of times, so the final code is @N. In the second repeated case(8-11), @ and ß for both character positions occur equally, so the final code is NN.

**Tags:** r, bioawk, text-processing

---

## 1 answers

### Answer by username_1 on 2018-01-18. Score: 2 | Accepted answer

OK. This is a bit convoluted, but it works. There might be simpler solutions...
First we'll write a function that takes a data frame and a character position (ie; first or second position in the string), groups the data frame by the second and third columns, counts occurrences of each character in the specified position of the 18th column, and returns a vector according to your ranking scheme (this doesn't include counts of N since it doesn't occur in your example data, but it's a simple extension of the case_when line to add it):
library(dplyr)
library(tidyr)
library(stringr)

summarise_characters <- function(df, pos) {
  df %>% mutate(character=str_sub(X18, pos, pos)) %>% 
  group_by(X2, X3, character) %>% 
  summarise(n=n()) %>% 
  ungroup() %>% 
  spread(character, n, fill=0) %>% 
  mutate(pos1=case_when(`@` > ß ~ '@', 
                        `@` == ß ~ 'N', 
                        `@` < ß ~ 'ß')) %>%
  `$`(pos1)
}

Next we remove duplicate lines from the data frame, then create a new column, pasting together the output of our function called on the first and second character:
df %>% group_by(X2, X3) %>%  
  slice(1) %>%
  ungroup() %>%
  mutate(counts=paste0(summarise_characters(df, 1), summarise_characters(df, 2))) %>% 
  select(X1, X2, X3, X4, X11, X12, X13, counts)

# A tibble: 3 x 8
     X1        X2        X3                   X4       X11                  X12       X13 counts
                                                        
1     1  90042011  90042031 AGTGCCACCAGGTGGGGCCG  90184354 GAGGCCACAAGAGGGCACAA  90184337     @N
2     1  94853396  94853416 GCCGCCACTAGATGGTGCTA  94969274 CAGTCCTCTAGAGGGAGCCC  94969266     ß@
3     2 103584283 103584303 TGGGCCACTAGGAGGCACTG 103841456 CATGCCACAAGAGGGCATCA 103841456     NN

> **Comments:**
> **username_0**: This looks good. One question, what is the position input in the first function? Because I want to do this to all such repeating lines in my dataset  
> **username_1**: position in the character string in column 18. As I understand it you want to use the same logic on both the first and second position in this field  
> **username_0**: It gives me this error:  Error in mutate_impl(.data, dots) :    C stack usage  7970884 is too close to the limit  
> **username_1**: Interesting. What are the names of the columns in your data frame?  
> **username_0**: It's V1,V2,V3. But I replaced the Xs in your code with Vs already. And changed 18 to 20 since it was actually $20 as mentioned in my question.  
> **username_0**: Also I noticed that X11, X12 and X13 are in not the same order as X2, X3 and X4 as how it would be ideal. Is this causing the error?  
> **username_1**: It's column 18 in your example data. I'm not going to be able to figure out the source of the error if you're working with different data than the example  
> **username_0**: Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/71859/discussion-between-maharshi-chakravortee-and-heathobrien).  

---

