Matrix to 3 col sorted

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Matrix to 3 col sorted
# 1  
Old 09-21-2017
Matrix to 3 col sorted

Hello experts, I have matrices sorted by position, there are 400k rows, 3000 columns.

ID      CHR     POS     M1      M2      M3      M4      M5
ID1     1       1       4.6     2.6     2.1     3.5     4.2
ID2     1       100     3.6     2.9     3.2     2.6     2.5
ID3     1       1000    4.1     2.9     2.4     2.8     2.5
ID4     1       2000    4.2     2.4     2.8     3.7     2.8
ID5     1       3000    4.6     2.9     3.7     3.5     2.7
ID6     1       4000    5.2     3       3.2     4.2     3.7
ID7     1       5000    5.8     2.2     2.8     3.1     2.7

I`m looking for a sorted long-format output by position, grouped by markers (Ms)

M1 ID1__1__1 4.6
M1 ID2__1__100 3.6
M1 ID3__1__1000 4.1
M1 ID4__1__2000 4.2
M1 ID5__1__3000 4.6
M1 ID6__1__4000 5.2
M1 ID7__1__5000 5.8
M2 ID1__1__1 2.6
M2 ID2__1__100 2.9
M2 ID3__1__1000 2.9
M2 ID4__1__2000 2.4
M2 ID5__1__3000 2.9
M2 ID6__1__4000 3
M2 ID7__1__5000 2.2
M3 ID1__1__1 2.1
M3 ID2__1__100 3.2
M3 ID3__1__1000 2.4
M3 ID4__1__2000 2.8
M3 ID5__1__3000 3.7
M3 ID6__1__4000 3.2
M3 ID7__1__5000 2.8
M4 ID1__1__1 3.5
M4 ID2__1__100 2.6
M4 ID3__1__1000 2.8
M4 ID4__1__2000 3.7
M4 ID5__1__3000 3.5
M4 ID6__1__4000 4.2
M4 ID7__1__5000 3.1

I have two working methods

1. This step requires a final sorting which for 400000x3000 datapoints will be very row or run out of memory.

awk -F"\t" 'NR==1{split($0,lines);}NR>1{for(i=4;i<length(lines);i++) print lines[i],$1"__"$2"__"$3,$i}' tmp | sort -k1,1

2. This step is very very slow as it requires to take the entire matrix into memory before making splits.

awk -F"\t" '{a[NR]=$0}END{split(a[1],lines); for(i=4;i<=length(lines);i++) { for(j=2;j<=length(a);j++) { split(a[j],haps); print lines[i],haps[1]"__"haps[2]"__"haps[3],haps[i]}}}'  tmp

Is there a more efficient way to get the output I need? Thanks in advance.
# 2  
Old 09-21-2017
rm -rf output
mkdir output
awk '
NR==1 { for (i=1; i<=NF; i++) a[i]=$i ;}
NR > 1 {
   for (i=4; i<=NF; i++) print "touch output/\"" a[i], $1 "__" $2 "__" $3, $i "\"";
' infile | sh

ls -1 output > outfile

This User Gave Thanks to rdrtx1 For This Post:
# 3  
Old 09-21-2017
Interesting idea! Let the filesystem do a part of the sorting, and let ls sort the other parts.
More fine-tuned so ls sorts less:
mkdir -p $outdir
awk '
NR==1 {
  for (i=4; i<=NF; i++) { line[i]=$i; printf "mkdir -p \"%s\"\n",$i }
  for (i=4; i<=NF; i++) printf ">\"%s/%s__%s__%s %s\"\n",line[i],$1,$2,$3,$i
' infile > tmpfile
cd $outdir
sh tmpfile
for i in *
  ls -1 "$i" | sed "s#^#${i} #"
) | tee outfile

Last edited by MadeInGermany; 09-21-2017 at 02:37 PM..
This User Gave Thanks to MadeInGermany For This Post:
# 4  
Old 09-21-2017
Me too, I found this idea at least creative, but 1,2E9 directory entries, albeit in 3000 directories, might be somewhat, hmm, demanding on the file system...
On the other hand, I thought of awk distributing the columns into 3000 files, one column each, and then concatening them, but opening and appending 3000 files 400k times also would not be too fast. Right now, I'm lost...
# 5  
Old 09-22-2017
Whatever strategy you choose, according to your expectation, you will get an output file that is near 5 times bigger than your input file.

input file :
rows = 400k
columns = 3000
Total amount of datas = 400k x 3000 = 1,2 billions

output file :
rows = (400k -1) x ( 3000 -3 )
"-1" because of the header row
"-3" because the 3 first contains the datas you want
columns = 5 (i take into account the datas, not the formatting and "__" stuff)
Total amount of datas = 5 x (400k -1) x ( 3000 -3 ) = 5,99...billions

Indeed, since you will be repeating the datas of the first 3 column for each and every subsequent column. (so (3 + 2 ) x (3000 - 3) ) rather than having it once for all of them ( "+ 2" is because you even want to add the header and the values of the subsequent column).

So just because of your prerequisits and expectations, for sure you will have to write more data, and thus you will need the corresponding number of I/O writing, independently of the strategy you choose.

If you have a huge amount of data to write, then there is and uncompressible amount of time to do it.
Of course I/O operations are quicker to do in RAM or SSD than on a standard hard drive, but still ...

It would be cheaper to save the transposed matrix and request on it
So you will have it twice : in line and in columns ... of course it will cost you 1,2 billions of datas more.
But 1,2 billions x 2 = 2,4 billions is still more than twice smaller than 5,9 billions of datas !

Anyway, processing such an amount of data using files is inappropriate : this is what Database have been designed for...
My 2 cents ...


PS// The reality might even be worse since i didn't even take into account the size of the datas, just the number of them ...

Last edited by ctsgnb; 09-22-2017 at 12:18 PM..
This User Gave Thanks to ctsgnb For This Post:
# 6  
Old 09-24-2017
Hi senhia83,
maybe a little bit faster than your second solution:
awk 'NR==1 {C=NF;Y=$0;Z=$4;next}{X[NR]=$0;print Z,$1"__"$2"__"$3,$4}END{for(i=5;i<C;++i){$0=Y;Z=$i;j=1;while(++j<=NR){$0=X[j];print Z,$1"__"$2"__"$3,$i}}}'

awk 'NR==1 {C=NF;Y=$0;Z=$4;next}
{X[NR]=$0;print Z,$1"__"$2"__"$3,$4}
     $0=X[j];print Z,$1"__"$2"__"$3,$i
}' file

This User Gave Thanks to disedorgue For This Post:
# 7  
Old 09-27-2017
Thanks ! the filesystem ended up crashing with the files approach ..but a very innovative idea nonetheless !
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Modifying col values based on another col

Hi, Please help with this. I have several excel files (with and .xlsx format) with 10-15 columns each. They all have the same type of data but the columns are not ordered in the same way. Here is a 3 column example. What I want to do add the alphabet from column 2 to column 3, provided... (9 Replies)
Discussion started by: newbie83
9 Replies

2. Shell Programming and Scripting

Printing from col x to end of line, except last col

Hello, I have some tab delimited data and I need to move the last col. I could hard code it, awk '{ print $1,$NF,$2,$3,$4,etc }' infile > outfile but it would be nice to know the syntax to print a range cols. I know in cut you can do, cut -f 1,4-8,11- to print fields 1,... (8 Replies)
Discussion started by: LMHmedchem
8 Replies

3. Shell Programming and Scripting

awk? adjacency matrix to adjacency list / correlation matrix to list

Hi everyone I am very new at awk but think that that might be the best strategy for this. I have a matrix very similar to a correlation matrix and in practical terms I need to convert it into a list containing the values from the matrix (one value per line) with the first field of the line (row... (5 Replies)
Discussion started by: stonemonkey
5 Replies

4. UNIX for Advanced & Expert Users

Print line based on highest value of col (B) and repetion of values in col (A)

Hello everyone, I am writing a script to process data from the ATP world tour. I have a file which contains: t=540 y=2011 r=1 p=N409 t=540 y=2011 r=2 p=N409 t=540 y=2011 r=3 p=N409 t=540 y=2011 r=4 p=N409 t=520 y=2011 r=1 p=N409 t=520 y=2011 r=2 p=N409 t=520 y=2011 r=3 p=N409 The... (4 Replies)
Discussion started by: imahmoud
4 Replies

5. Ubuntu

How to convert full data matrix to linearised left data matrix?

Hi all, Is there a way to convert full data matrix to linearised left data matrix? e.g full data matrix Bh1 Bh2 Bh3 Bh4 Bh5 Bh6 Bh7 Bh1 0 0.241058 0.236129 0.244397 0.237479 0.240767 0.245245 Bh2 0.241058 0 0.240594 0.241931 0.241975 ... (8 Replies)
Discussion started by: evoll
8 Replies

6. Shell Programming and Scripting

how to add new col in a file

Hi, Experts, I have a requirement as following: my source file: a a a b b c c c c I need add one more colume as following: 1 a 2 a 3 a 1 b 2 b 1 c 2 c (4 Replies)
Discussion started by: ken002
4 Replies

7. Shell Programming and Scripting

i can't cut the third col

SW_dist_intr false Enable SW distribution of interrupts True autorestart true Automatically REBOOT OS after a crash True boottype disk N/A False capacity_inc 1.00 ... (7 Replies)
Discussion started by: maxim42
7 Replies

8. Shell Programming and Scripting

diagonal matrix to square matrix

Hello, all! I am struggling with a short script to read a diagonal matrix for later retrieval. 1.000 0.234 0.435 0.123 0.012 0.102 0.325 0.412 0.087 0.098 1.000 0.111 0.412 0.115 0.058 0.091 0.190 0.045 0.058 1.000 0.205 0.542 0.335 0.054 0.117 0.203 0.125 1.000 0.587 0.159 0.357... (11 Replies)
Discussion started by: yifangt
11 Replies

9. Ubuntu

Match col 1 of File 1 with col 1 File 2 and create a 3rd file

Hello, I have a 1.6 GB file that I would like to modify by matching some ids in col1 with the ids in col 1 of file2.txt and save the results into a 3rd file. For example: File 1 has 1411 rows, I ignore how many columns it has (thousands) File 2 has 311 rows, 1 column Would like to... (7 Replies)
Discussion started by: sogi
7 Replies

10. UNIX for Advanced & Expert Users

Help On col command

Hello Can Any1 tell me the difference between the col command and the col command with the -f option. I tried running both of them but i can't see any difference. Please guide me. (1 Reply)
Discussion started by: rahulrathod
1 Replies
Login or Register to Ask a Question

Featured Tech Videos