Count the sum of molecular markers and the respective centimorgan range from a list a sequences
5
0
Entering edit mode
3.5 years ago
gabri ▴ 60

Hi All,

I have an excel table with 3 tab-delimited columns and n rows like this:

**header marker cm**

101 4   0-7.195

103 8   38.582-49.653

103 5   43.096-46.534

103 1   49.653-49.653

103 1   51.676-51.676

104 2   22.454-37.061

104 4   23.351-37.061

105 2   83.619-84.178

106 1   36.307-36.307

106 1   40.62-40.62


I need an output like this:

**header    marker  cm**

101 4   0-7.195

103 15  38.582-51.676

104 6   22.454-37.061

105 2   83.619-84.178

106 2   36.307-40.62


So, I need to collapse the duplicates in the header column, keeping the sum of the markers obtained from every row of that duplicate, and extract from the cm column the lowest and the highest value among all the rows of that duplicate.

For example, for the duplicate 103 the sum is 8 + 5 + 1 + 1, the lowest cm value is 38.582 in the 2nd row and the highest cm value is 51.676 in the 5th row. In the table there are also not duplicate headers (like the 101) that have to be keep directly as they are.

Any idea for an automated solution?

Thank you

excel text manipulation python perl • 1.0k views
1
Entering edit mode

0
Entering edit mode

Hello,

This post looks off-topic for the site. Please substantiate how this question is related to bioinformatics.

0
Entering edit mode

Yes I'm going to explain it better. The "header" column represents my sequences, the "marker" column represents the number of molecular markers that I found on the sequences of the "header" column, the "cm" column represents the range position in centimorgan of these markers on my sequences.

0
Entering edit mode

2
Entering edit mode
3.5 years ago

$tail -n+2 test.txt | sed -e 's/-/\t/g' | datamash -s -g 1 sum 2 min 3 max 4 | awk -v OFS="\t" 'BEGIN {print "header","market","cm"} {print$1,$2,$3"-"$4}' header market cm 101 4 0-7.195 103 15 38.582-51.676 104 6 22.454-37.061 105 2 83.619-84.178 106 2 36.307-40.62  input: $ cat test.txt
101 4   0-7.195
103 8   38.582-49.653
103 5   43.096-46.534
103 1   49.653-49.653
103 1   51.676-51.676
104 2   22.454-37.061
104 4   23.351-37.061
105 2   83.619-84.178
106 1   36.307-36.307
106 1   40.62-40.62


datamash is in most of the gnu-linux repos (Use either apt or dnf/yum)

0
Entering edit mode

What's this datamash?

EDIT: Found it, never mind. IMO we should be using builtins or programming languages instead of utilities.

0
Entering edit mode

Thank you for your help! I didn't know about datamash, very useful. Thank you again.

2
Entering edit mode
3.5 years ago
Benn 8.2k

Or use R plyr package, but first make a forth column (split the last column by the "-" sign).

library(plyr)

marker <- c(4,8,5,1,1,2,4,2,1,1)
cM_min <- c(0,38.582,43.096,49.653,51.676,22.454,23.351,83.619,36.307,40.62)
cM_max <- c(7.195,49.653,46.534,49.653,51.676,37.061,37.061,84.178,36.307,40.62)

df <- as.data.frame(cbind(header, marker, cM_min, cM_max))
df
1   101    4  0.000  7.195
2   103    8 38.582 49.653
3   103    5 43.096 46.534
4   103    1 49.653 49.653
5   103    1 51.676 51.676
6   104    2 22.454 37.061
7   104    4 23.351 37.061
8   105    2 83.619 84.178
9   106    1 36.307 36.307
10  106    1 40.620 40.620

marker = sum(marker),
cM_min = min(cM_min),
cM_max = max(cM_max))

1  101   4  0.000  7.195
2  103  15 38.582 51.676
3  104   6 22.454 37.061
4  105   2 83.619 84.178
5  106   2 36.307 40.620

2
Entering edit mode
3.5 years ago
Gungor Budak ▴ 250

Don't use Excel for this task, use Python to read the input line by line, keep every line in a dictionary (key: header, value: list of marker and cm) and every time you read a line with header that is already in the dictionary, update the values accordingly and just output the content of the dictionary after you process all the lines. Try this and let us know how it goes! Good luck :)

1
Entering edit mode
3.5 years ago
Ram 35k
1
Entering edit mode
3.5 years ago

in line with above post using stringr and dplyr:

df1 = read.csv("file.txt",stringsAsFactors = F,strip.white = T,sep = "\t")
library(stringr)
df1[, c("min", "max")] = str_split_fixed(df1\$cm, "-", 2)
library(dplyr)
data.frame(df1 %>%
summarise(sum = sum(marker), range = paste(min(min), max(max), sep = "-")))

1    101   4       0-7.195
2    103  15 38.582-51.676
3    104   6 22.454-37.061
4    105   2 83.619-84.178
5    106   2  36.307-40.62

1
Entering edit mode

Even this solution works really well, thank you very much!

0
Entering edit mode

Nice trick with the string split!