compare columns from seven files and print the output

smriti_shridhar · #1 (**permalink**) 06-02-2008

Hi guys,
I need some help to come out with a solution . I have seven such files but I am showing only three for convenience.

filea
a5 20
a8 16

fileb
a3 42
a7 14

filec
a5 23
a3 07

The output file shoud contain the data in table form showing first field of each file with their second field(score) in each file.

ID filea fileb filec
a5 20 00 23
a8 16 00 00
a3 00 42 07
a7 00 14 00

Your help is highly appretiated.

-Smriti

era · #2 (**permalink**) 06-03-2008

Perhaps the simple solution would be to write a simple script to canonicalize each of those files, so they all have the same labels. Then it's easy to e.g. paste them all side by side, and cut the columns you actually want.

jaduks · #3 (**permalink**) 06-03-2008

Not sure if this is the expected output.

Code:

$ cat filea
a5 20
a8 16

$ cat fileb
a3 42
a7 14

$ cat filec
a5 23
a3 07

$ cat filea fileb filec > filex

$ cat filex
a5 20
a8 16
a3 42
a7 14
a5 23
a3 07

$ awk '
!arr[$1] {arr[$1] = $0; next}
{arr[$1] = arr[$1] " " $2}
END {for(i in arr) {print arr[i]}}
' filex

a3 42 07
a5 20 23
a7 14
a8 16

or 

$ awk '{Arr[$1]=sprintf("%s %s",Arr[$1],$2)} END {for ( i in Arr) {printf("%s %s\n",i,Arr[i])}}' filex
a3  42 07
a5  20 23
a7  14
a8  16

//Jadu

smriti_shridhar · #4 (**permalink**) 06-05-2008

Quote:

Originally Posted by era

Perhaps the simple solution would be to write a simple script to canonicalize each of those files, so they all have the same labels. Then it's easy to e.g. paste them all side by side, and cut the columns you actually want.

Thanks era,

I suppose I am not getting what u want to convey. Plz make it more clear and I want to repeat that its important for me to know that second field i.e. the scores are coming from which file in the final output, that's why I want a -- or 00 showing absence of score from a particular file if the ID is repeated.

smriti_shridhar · #5 (**permalink**) 06-05-2008

Quote:

Originally Posted by jaduks

Not sure if this is the expected output.

Code:

$ cat filea
a5 20
a8 16

$ cat fileb
a3 42
a7 14

$ cat filec
a5 23
a3 07

$ cat filea fileb filec > filex

$ cat filex
a5 20
a8 16
a3 42
a7 14
a5 23
a3 07

$ awk '
!arr[$1] {arr[$1] = $0; next}
{arr[$1] = arr[$1] " " $2}
END {for(i in arr) {print arr[i]}}
' filex

a3 42 07
a5 20 23
a7 14
a8 16

or 

$ awk '{Arr[$1]=sprintf("%s %s",Arr[$1],$2)} END {for ( i in Arr) {printf("%s %s\n",i,Arr[i])}}' filex
a3  42 07
a5  20 23
a7  14
a8  16

//Jadu

Thanks for replying,

This won't solve my problem as I need an ordered way where it sholud be clear that which score belongs to which file n if I'll cat that identity will be lost and I wouln't knw if '42' belonged to file a,b or c in the following output.

a3 42 07
a5 20 23
a7 14

your help is really appretiated.

era · #6 (**permalink**) 06-05-2008

What I was trying to suggest was that you would change the input files so they have an explicit value for each possible label. So for example filec would become

Code:

(Note also the reordering of the fields a3 and a5.)

Once you have that, the rest should be trivial. But maybe modifying the files (or maintaining a modified duplicate for each input file) isn't a very elegant solution.

radoulov · #7 (**permalink**) 06-05-2008

Use nawk or /usr/xpg4/bin/awk on Solaris.

Code:

awk '{
if (!_[$1]++) id[++n] = $1
fid[FILENAME,$1] = $2
if (FNR == 1) fn[++c] = FILENAME
} END {
  printf "id\t"
  for (i=1; i<=c; i++)
    printf "%s\t", fn[i]
  print
  for (j=1; j<=n; j++) {
    printf "%s\t", id[j]
    for (i=1; i<=c; i++)
      printf "%s\t", (fn[i] SUBSEP id[j]) in fid ? fid[fn[i] SUBSEP id[j]] : "00"  
    print  
    }
}' file*

With your files:

Code:

$ head file*
==> filea <==
a5 20
a8 16

==> fileb <==
a3 42
a7 14

==> filec <==
a5 23
a3 07
$ nawk '{
if (!_[$1]++) id[++n] = $1
> if (!_[$1]++) id[++n] = $1
> fid[FILENAME,$1] = $2
> if (FNR == 1) fn[++c] = FILENAME
> } END {
    printf "%s\t", id[j]
>   printf "id\t"
>   for (i=1; i<=c; i++)
>     printf "%s\t", fn[i]
>   print
>   for (j=1; j<=n; j++) {
>     printf "%s\t", id[j]
>     for (i=1; i<=c; i++)
>   printf "%s\t", (fn[i] SUBSEP id[j]) in fid ? fid[fn[i] SUBSEP id[j]] : "00"
> print
> }
> }' file*
id      filea   fileb   filec
a5      20      00      23
a8      16      00      00
a3      00      42      07
a7      00      14      00

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
compare two files and print the last row into first	cdfd123	Shell Programming and Scripting	1	04-27-2008 08:23 AM
awk to compare lines of two files and print output on screen	chlfc	Shell Programming and Scripting	3	03-24-2008 04:16 AM
compare files by lines and columns	giviut	Shell Programming and Scripting	4	01-17-2008 06:00 AM
Compare two files and merge columns in a third	CM64	Shell Programming and Scripting	20	04-04-2007 01:41 PM
Compare multiple columns between 2 files	stevesmith	Shell Programming and Scripting	15	09-20-2006 12:04 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: