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
5 answers
output (please cross check):
$ 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
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
datamash is in most of the gnu-linux repos (Use either apt or dnf/yum)
Or use R plyr package, but first make a forth column (split the last column by the "-" sign).
library(plyr)
header <- c(101,103,103,103,103,104,104,105,106,106)
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
header marker cM_min cM_max
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
ddply(df, .(header), summarize,
marker = sum(marker),
cM_min = min(cM_min),
cM_max = max(cM_max))
header marker cM_min 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
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 :)
- Export as CSV
- Use
awk(See: https://www.unix.com/302161956-post3.html?s=fc7fa674e474208a1b7167f70b966385) for an example of how to do this.
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 %>%
group_by(header) %>%
summarise(sum = sum(marker), range = paste(min(min), max(max), sep = "-")))
header sum range
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
Log in to answer this question.
-
How to solve DESeq2 Error in checkFullRank(modelMatrix) cause by biological replicates information?
written by ace •
-
Help with SCENIC+
written by Chris •
-
How to implement the code in MonkeyBread package
written by piotto •
-
Nebula 30x read length distribution
written by berndmann •
-
samtools merge not working
written by Ric
-
SAM CIGAR parsing and position counter in R
written by niu.shengyong •
-
It should be easy, but...How to keep best hits in blast result when encountering frame shift?
written by qingxiangg •
-
Index Vcf File
written by mad.cichlids
-
Extracting Set Of Numbers From A File
written by Cynthia •
-
Synonmous And Non Synonmous Snps
written by Payal
Hello,
This post looks off-topic for the site. Please substantiate how this question is related to bioinformatics.
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.
Please edit your question and add this information in there.