Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Lookup horizontally and vertically and calculate counts
Top Forums Shell Programming and Scripting Lookup horizontally and vertically and calculate counts Post 302939054 by jianp83 on Sunday 22nd of March 2015 12:23:06 AM
Old 03-22-2015
Lookup horizontally and vertically and calculate counts
Hello,

Please help create the following report.

I have a matrix

Code:
-  S1 S2 S3 S4  
M1 AA AA TT -
M2 AG AG AA GG
M3 GG TT - -

a first lookup table

Code:
M3 chr7 4.456
M1 chr7 28.9
M2 chr8 129.678

a second lookup table

Code:
S1 GGHBBGG/DEDD(@DCCD)
S2 GGHBBGG/DEDD(@DCCD)//B-
S3 GGHBBGG/DEDD(@HH?)//B1@NNN
S4 GGHBBGG/DEDD(@DCCDH)#-BCF1

I want to count each nucleotide (As, Ts, Cs and Gs) for each row, and a few variables calculated as

Code:
total = NF-1
missing = total number of "-"
mono = total number of ( AA + GG + CC + TT)
mix = total number of ( AT + AC + AG + CT + CG + GT)
m = 2nd highest among (A,T,C,G) / total (A,T,C,G)
data = total - missing


An example calculation of m for M1 is there are 4 As and 2 Ts for M1. The rest are 0s
So m for M1 = total number of Ts, which is 2nd highest ( 2 ) / total A,T,G,C (6) = 0.33



Here is what is my report should look like
I cant seem to line up the table for some reason, it is space delimited.
Code:
NAME				                                  M1 M2 M3
CHR				                                  chr7 chr8 chr7 
POS				                                  28.9 129.678 4.456
A	                                                          4 4 0			        
T			                                          2 0 2			
G				                                  0 4 2				
C				                                  0 0 0
-				                                  1 0 2
m				                                  0.33 0.5 0.5
data				                                  3 4 2
mono				                                  3 2 2 
mixed				                                  0 2 0
total				                                  4 4 4
S1 GGHBBGG/DEDD(@DCCD)		                                  AA AG GG
S2 GGHBBGG/DEDD(@DCCD)//B-                                        AA AG TT
S3 GGHBBGG/DEDD(@HH?)//B1@NNN                                     TT AA -
S4 GGHBBGG/DEDD(@DCCDH)#-BCF1                                      - GG -

I tried this, please help

Code:
 awk 'NR==FNR{ l[$1]=$2 FS $3;next} $1 in l { $1=$1 FS l[$1]}1' lookup1 file |
	       	     awk '{ for (i=2;i++;i<=NF)
	       	     		if ($i=="AA" || $i=="GG" || $i=="CC" || $i=="TT")
	       	     			mono=mono+1
	       	     				if ($i=="AA")
	       	     					a=a+2
	       	     				else if ($i=="GG")
	       	     					g=g+2
	       	     				if ($i=="CC")
	       	     					c=c+2
	       	     				else if ($i=="TT")
	       	     					t=t+2
	       	     				fi
	       	     		else if ($i=="AT" || $i=="AG" || $i=="AC" || $i=="CT" || $i=="CG" || $i=="GT")
	       	     				mix=mix+1
	       	     		
	       	     		else if ($i="-")
	       	     				missing=missing+1	
	       	     		fi
	       	     		total=NF-1
	       	     		data=(NF-1)-missing
	       	     		$1= $1,a,c,t,g,mono,mix,missing,total,data
	       	     		}1' | awk '
		     { 
		         for (i=1; i<=NF; i++)  {
		             a[NR,i] = $i
		         }
		     }
		     NF>p { p = NF }
		     END {    
		         for(j=1; j<=p; j++) {
		             str=a[1,j]
		             for(i=2; i<=NR; i++){
		                 str=str" "a[i,j];
		             }
		             print str
		         }
	             }'  | awk  'NR==FNR{ l[$1]=$2 ;next} $1 in l { $1=$1 FS l[$1]}1' lookup2 - > final_report

Last edited by jianp83; 03-22-2015 at 12:19 PM..
 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Clueless about how to lookup and reverse lookup IP addresses under a file!!.pls help

Write a quick shell snippet to find all of the IPV4 IP addresses in any and all of the files under /var/lib/output/*, ignoring whatever else may be in those files. Perform a reverse lookup on each, and format the output neatly, like "IP=192.168.0.1, ... (0 Replies)
Discussion started by: choco4202002
0 Replies

2. Shell Programming and Scripting

Print a horizontal word vertically.

Say I have the word: zinger I want to change it to print z i n g e r This is for a sorting algorithm that I am testing out. I will then use sort on the vertical and change it back to horizontal printing using tr. Once it is horizontal again, I can compare that sorted jumble... (6 Replies)
Discussion started by: JimJ
6 Replies

3. Shell Programming and Scripting

Appending two files vertically

Hi Need ur help for the below question. I have two files File-1 & File-2. File-1(This is a fixed file i.e. the content of this file is not going to change over a period of time) ------ a b c d e File-2 (This is a file which changes daily but the record count remains the same)... (1 Reply)
Discussion started by: 46019
1 Replies

4. Shell Programming and Scripting

Need help in reading a file horizontally and printing vertically

Hi Every body, I have file which has enttries, with each 5 entries as a set of entries, I would like to read the file (line by line) and print five entries of a set vertically, the next entry should come in the next line. Example: cat sample_file I am a Unix Adminsitrator new to shell... (6 Replies)
Discussion started by: aruveiv
6 Replies

5. Shell Programming and Scripting

SQL output vertically aligned?

I used the SQL query (taken from other threads here) to get the expected values to be written into a file. myQuery=`sqlplus -s cr_appsrvr/appsrvr@qwi << EndofFile set heading off; set tab off; set wrap off; set pages 0; set feedback off; SELECT CLEARINGHOUSE_TRACE_NUM, INSURED_ID FROM... (4 Replies)
Discussion started by: swame_sp
4 Replies

6. Shell Programming and Scripting

Calculate age of a file | calculate time difference

Hello, I'm trying to create a shell script (#!/bin/sh) which should tell me the age of a file in minutes... I have a process, which delivers me all 15 minutes a new file and I want to have a monitoring script, which sends me an email, if the present file is older than 20 minutes. To do... (10 Replies)
Discussion started by: worm
10 Replies

7. Shell Programming and Scripting

Counting characters vertically

I do have a big file in the following format >A1 ATGCGG >A2 TCATGC >A3 -TGCTG The number of characters will be same under each subheader and only possible characters are A,T,G,C and - I want to count the number of A's, T's,G's, C's & -'s vertically for all the positions so that I... (5 Replies)
Discussion started by: Lucky Ali
5 Replies

8. Shell Programming and Scripting

Calculation of column both horizontally and vertically

Hi ALL I have this data ail,UTT,id1_0,COMBO,21,24,21,19 al,UTHAST,id1_0,COMBO,342,390,361,361 dmo,UTST,id1_0,COMBO,21,15,22,23 vne,UAST,id1_0,COMBO,345,372,390,393 I wan the sum of column 5,6,7 & 8 both horizontal and vertical. There is one more prob the column keeps on increasing... (9 Replies)
Discussion started by: nikhil jain
9 Replies

9. Shell Programming and Scripting

Grep Delimited Line and Display Vertically

Hello All, I am trying to take a colon-delimited line from a bunch of lines such as apple:green:5cents:CA apple:red:4cents:FL orange:green:6cents:HI ...and display it vertically with label prefixes such as the following; Fruit: apple Color: green Price: 5cents Origin: CA So... (3 Replies)
Discussion started by: techieg
3 Replies

10. UNIX for Beginners Questions & Answers

Orienting select choices vertically

From my script #!/bin/bash echo "Which of these does not belong in the group?"; \ select choice in Mercedes Audi Chevrolet Audi Porsche BMW Volkswagen; do if ]; then echo "Correct! Chevrolet is not a German marque."; break; fi echo "Errr...no. Try again." doneI'm... (3 Replies)
Discussion started by: Xubuntu56
3 Replies
All times are GMT -4. The time now is 05:01 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy