Collapsing and counting by key column in a sorted file Post: 302536947

Sponsored Content

Top Forums Shell Programming and Scripting Collapsing and counting by key column in a sorted file Post 302536947 by mirni on Wednesday 6th of July 2011 03:50:17 PM

07-06-2011

Registered User

Well, thanks for the detailed explanation and the pretty tables!
Here, try this awk solution:

Code:

awk '{
  if($1 in ps){
    ps[$1]=ps[$1]","$4; 
    fs[$1]=fs[$1]","$3
  } else {
     i[cnt++]=$1; 
     f2[$1]=$2; 
     ps[$1]=$4; 
     fs[$1]=$3
  } 
  mult[$1]++
}
END{
   n=asort(i);
   for(j=1; j<=n; j++) 
      print i[j] " " f2[i[j]] " " fs[i[j]] " " ps[i[j]]" "mult[i[j]];
}' mappings.txt 
HWI-EA332_0036:5:100:10131:16361#ATGC/1 GACTTGAGGTCTCCCCCGCA 1,1,1,1 TZRTMR_40497:317:+,YSXAZZ_40497:317:+,AZZOL148119:523:+,VCXT148119:523:+ 4
HWI-EA332_0036:5:100:10554:9799#ATGC/1 GACTCCTAAATTAACAACAA 1,1 YSXAZZ_35135:573:+,TZRTMR_35135:573:+ 2
HWI-EA332_0036:5:100:10791:13901#ATGC/1 GACTAGTGAGTGACCCGCTC 1,1 TZRTMR_7034:497:+,YSXAZZ_7034:497:+ 2
HWI-EA332_0036:5:100:11825:11517#ATGC/1 GACTAATATAAATAAGTCTC 1,1,1,1 YSXAZZ_3676:148:+,TZRTMR_3676:148:+,TZRTMR_2085:139:+,YSXAZZ_2085:139:+ 4
HWI-EA332_0036:5:100:13509:3643#ATGC/1 GACTACCCGCCAAACCCCAG 2,2 YTTSTZ_566255:526:-,YYTWQ_566255:526:- 2
HWI-EA332_0036:5:100:13837:1118#ATGC/1 GACTCCCGCATCCCGCAAAC 1,1 PZXVXZ_21909:999:+,TZRTMR_21909:999:+ 2

Use nawk on Solaris

Last edited by mirni; 07-06-2011 at 04:59 PM.. Reason: nawk comment

mirni

View Public Profile for mirni

Find all posts by mirni

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join 3 files using key column in a mapping file

I'm new of UNIX shell scripting. I'm recently generating a excel report in UNIX(file with delimiter is fine). How should I make a script to do it? 1 file to join comes from output of one UNIX command, the second from another UNIX command, and third from a database query. The key columes of all...

2. Shell Programming and Scripting

combine multiple files by column into one files already sorted!

I have multiple files; each file contains a certain data in a column view simply i want to combine all those files into one file in columns example file1: a b c d file 2: 1 2 3 4 file 3: G

3. Shell Programming and Scripting

need to remove duplicates based on key in first column and pattern in last column

Given a file such as this I need to remove the duplicates. 00060011 PAUL BOWSTEIN ad_waq3_921_20100826_010517.txt 00060011 PAUL BOWSTEIN ad_waq3_921_20100827_010528.txt 0624-01 RUT CORPORATION ad_sade3_10_20100827_010528.txt 0624-01 RUT CORPORATION ...

4. Shell Programming and Scripting

Counting occurences in column

Hi guys! I have a problem writing script that would convert this input into this output: I have an awk script that counts occurences of a sign in a column, but don't know how to change it so that I would give me desired output. script awk '{count++}END{for(j in count)...

5. Shell Programming and Scripting

Help with awk in counting characters based on a column

Hello, I am using Awk in UBUNTU 12.04. I have a file as follows with 2172 rows and 44707 columns. ABO and GPO are the names of my populations. ABO_1 1 2 ABO_1 1 2 ABO_2 1 1 ABO_2 1 2 GPO_1 1 1 GPO_1 2 2 GPO_2 1 0 GPO_2 2 0I want to count the number of 1s and 2s in...

6. Shell Programming and Scripting

Counting no of spl character occurance column wise

Hi i have a file delimited with ","as below and i need to handle scenario like col1,col2 fields have special character '|', i need count of special character value column wise as given in col3 and col4. pls help me to reslove this. Source file name,col1,col2,col3,col4 one,2,3 two,2|3,2|3...

7. Shell Programming and Scripting

Counting the number of element in each column

Hello, I have a file as follows: ENSGALG00000000189 ENSGALG00000000189 ENSGALG00000000189 ENSGALG00000000215 ENSGALG00000000215 ENSGALG00000000218 ...

8. Shell Programming and Scripting

Counting a consecutive number in column 2

Hi, I have a input file which contains following data 0 1 0 2 0 3 0 4 0 8 0 9 0 11 1 1 1 2 1 6 1 7 1 8 1 9 2 1 2 11 2 12

9. Shell Programming and Scripting

Need help of counting no of column of a file

Hi All , I got stuck on the below scenario.If anyone can help me ,that will be really helpful. I have a target hdfs file layout.I need to know the no of column in that file. Target_RECRD_layout { ABC_ID EN NOTNULLABLE, ABC_COUNTRY CHARACTER ENCODING ASCII NOTNULLABLE, ...

10. UNIX for Dummies Questions & Answers

Collapsing similar strings

I have a file that looks like this: BC00001 GA 2 2 3 3 2 5 1 5 3 3 2 4 ...

LEARN ABOUT CENTOS

dlamrg.f

dlamrg.f(3)							      LAPACK							       dlamrg.f(3)

NAME

       dlamrg.f -

SYNOPSIS

   Functions/Subroutines
       subroutine dlamrg (N1, N2, A, DTRD1, DTRD2, INDEX)
	   DLAMRG creates a permutation list to merge the entries of two independently sorted sets into a single set sorted in ascending order.

Function/Subroutine Documentation
   subroutine dlamrg (integerN1, integerN2, double precision, dimension( * )A, integerDTRD1, integerDTRD2, integer, dimension( * )INDEX)
       DLAMRG creates a permutation list to merge the entries of two independently sorted sets into a single set sorted in ascending order.

       Purpose:

	    DLAMRG will create a permutation list which will merge the elements
	    of A (which is composed of two independently sorted sets) into a
	    single set which is sorted in ascending order.

       Parameters:
	   N1

		     N1 is INTEGER

	   N2

		     N2 is INTEGER
		    These arguements contain the respective lengths of the two
		    sorted lists to be merged.

	   A

		     A is DOUBLE PRECISION array, dimension (N1+N2)
		    The first N1 elements of A contain a list of numbers which
		    are sorted in either ascending or descending order.  Likewise
		    for the final N2 elements.

	   DTRD1

		     DTRD1 is INTEGER

	   DTRD2

		     DTRD2 is INTEGER
		    These are the strides to be taken through the array A.
		    Allowable strides are 1 and -1.  They indicate whether a
		    subset of A is sorted in ascending (DTRDx = 1) or descending
		    (DTRDx = -1) order.

	   INDEX

		     INDEX is INTEGER array, dimension (N1+N2)
		    On exit this array will contain a permutation such that
		    if B( I ) = A( INDEX( I ) ) for I=1,N1+N2, then B will be
		    sorted in ascending order.

       Author:
	   Univ. of Tennessee

	   Univ. of California Berkeley

	   Univ. of Colorado Denver

	   NAG Ltd.

       Date:
	   September 2012

       Definition at line 100 of file dlamrg.f.

Author
       Generated automatically by Doxygen for LAPACK from the source code.

Version 3.4.2							  Tue Sep 25 2012						       dlamrg.f(3)