Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Print a key with its all values using awk/others

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Print a key with its all values using awk/others
# 1  
Old 03-27-2010
Print a key with its all values using awk/others
input
Code:
COL1    a1    b1    c1    d1    e1    f1
C1    10    10    10    100    100    1000
C2    20    20    200    200    200    2000

output
Code:
C1    a1    10    1
C1    b1    10    1
C1    c1    10    1
C1    d1    100    2    
C1    e1    100    2
C1    f1    1000    3
C2    a1    20    1
C2    b1    20    1
C2    c1    200    2
C2    d1    200    2
C2    e1    200    2
C2    f1    2000    3

Note: 3rd column representing 1st values as 1 (i.e. 10 of C1) and second one as 2(i.e. 100 of C1) and third one as 3 (i.e. 1000 of C1)
# 2  
Old 03-27-2010
Here's one way to do it with Perl -

Code:
$ 
$ cat f9
COL1    a1    b1    c1    d1    e1    f1
C1    10    10    10    100    100    1000
C2    20    20    200    200    200    2000
$ 
$ 
$ ##
$ perl -lne 'chomp;
>            if ($.==1) {@x=split/[ ]+/}
>            else {
>              @y=split/[ ]+/; $n=1; 
>              for ($i=1;$i<=$#y;$i++) {
>                $n++ if ($i>1 and $y[$i] != $y[$i-1]);
>                print $y[0],"\t",$x[$i],"\t",$y[$i],"\t",$n;
>              }
>            }' f9
C1      a1      10      1
C1      b1      10      1
C1      c1      10      1
C1      d1      100     2
C1      e1      100     2
C1      f1      1000    3
C2      a1      20      1
C2      b1      20      1
C2      c1      200     2
C2      d1      200     2
C2      e1      200     2
C2      f1      2000    3
$ 
$

tyler_durden
Last edited by durden_tyler; 03-28-2010 at 08:20 AM..
# 3  
Old 03-28-2010
perl scriptworking like majic
first of all many many thanx.
but I have a similar input but this code is not giving any put. may i know why please..

similar but not so similar input

Code:
IndID    a1    a2    a3    a6    a8    a9    
I_1    a/a    a/a    a/b    a/b    a/a    b/b    
I_2    b/b    b/b    b/b    b/b    b/c    b/d    
I_3    c/c    c/c    c/d    c/e    c/e    c/e

# 4  
Old 03-28-2010
awk:
Code:
awk '{ if (NR==1)
         for (i=1;i<=NF;i++) L[i]=$i
       else {
         v=1
         delete O
         for (i=2;i<=NF;i++) {
           if (!O[$i]) 
             O[$i]=v++
           print $1"\t"L[i]"\t"$i"\t"O[$i]
         }
       }
     }' infile

output:
Code:
I_1     a1      a/a     1
I_1     a2      a/a     1
I_1     a3      a/b     2
I_1     a6      a/b     2
I_1     a8      a/a     1
I_1     a9      b/b     3
I_2     a1      b/b     1
I_2     a2      b/b     1
I_2     a3      b/b     1
I_2     a6      b/b     1
I_2     a8      b/c     2
I_2     a9      b/d     3
I_3     a1      c/c     1
I_3     a2      c/c     1
I_3     a3      c/d     2
I_3     a6      c/e     3
I_3     a8      c/e     3
I_3     a9      c/e     3

Last edited by Scrutinizer; 03-28-2010 at 04:06 AM.. Reason: Corrected occurrence
# 5  
Old 03-28-2010
The awk code is awesome.
Thanx alot for you too scrutinizer.
# 6  
Old 03-29-2010
Quote:
Originally Posted by ruby_sgp
... I have a similar input but this code is not giving any put. may i know why please..

similar but not so similar input

Code:
IndID    a1    a2    a3    a6    a8    a9    
I_1    a/a    a/a    a/b    a/b    a/a    b/b    
I_2    b/b    b/b    b/b    b/b    b/c    b/d    
I_3    c/c    c/c    c/d    c/e    c/e    c/e

Sure.
It doesn't work because the earlier input file had numeric values for the columns "a1", "a2", "a3", ... etc. and so I had used the numeric comparison operator, shown in red color below -

Code:
##
perl -lne 'chomp;
           if ($.==1) {@x=split/[ ]+/}
           else {
             @y=split/[ ]+/; $n=1; 
             for ($i=1;$i<=$#y;$i++) {
               $n++ if ($i>1 and $y[$i] != $y[$i-1]);
               print $y[0],"\t",$x[$i],"\t",$y[$i],"\t",$n;
             }
           }' f9

This time round, the column values are - "a/a", "a/b", "b/b" etc. which are not numeric. Hence the string comparison operator ne should be used instead of !=.

In fact, even after making that change, it won't work because this line:

Code:
               $n++ if ($i>1 and $y[$i] ne $y[$i-1]);

would simply increment $n if the current element of @y is unequal to the prior element of @y. So, the values would be like so -

Code:
IndID    a1    a2    a3    a6    a8    a9    
I_1    a/a    a/a    a/b    a/b    a/a    b/b  
$n =    1      1       2       2      3       4

Whereas Scrutinizer's script retains the value 1 instead of 3 since "a/a" has been encountered earlier -

Code:
IndID    a1    a2    a3    a6    a8    a9    
I_1    a/a    a/a    a/b    a/b    a/a    b/b  
$n =    1      1       2       2      1       3

Note that the value of the last column ($n) is the same for all values of "I_1:a/a", and it increments if "I_1:a/a" changes to "I_1:a/b". This can be achieved in Perl by keeping "I_1:a/a" as the key of a hash, and the incremented value of $n as the corresponding value.

Here's the modified script with all the ideas incorporated -

Code:
$
$
$ cat f9
IndID    a1    a2    a3    a6    a8    a9
I_1    a/a    a/a    a/b    a/b    a/a    b/b
I_2    b/b    b/b    b/b    b/b    b/c    b/d
I_3    c/c    c/c    c/d    c/e    c/e    c/e
$
$ ##
$ perl -lne 'chomp;
>            if ($.==1) {@x=split/[ ]+/}
>            else {
>              @y=split/[ ]+/; $n=0;
>              for ($i=1;$i<=$#y;$i++) {
>                if (!defined $z{$y[0].":".$y[$i]}) {
>                  $z{$y[0].":".$y[$i]} = ++$n;
>                  $d = $n;
>                } else {
>                  $d = $z{$y[0].":".$y[$i]};
>                }
>                print $y[0],"\t",$x[$i],"\t",$y[$i],"\t",$d;
>              }
>            }' f9
I_1     a1      a/a     1
I_1     a2      a/a     1
I_1     a3      a/b     2
I_1     a6      a/b     2
I_1     a8      a/a     1
I_1     a9      b/b     3
I_2     a1      b/b     1
I_2     a2      b/b     1
I_2     a3      b/b     1
I_2     a6      b/b     1
I_2     a8      b/c     2
I_2     a9      b/d     3
I_3     a1      c/c     1
I_3     a2      c/c     1
I_3     a3      c/d     2
I_3     a6      c/e     3
I_3     a8      c/e     3
I_3     a9      c/e     3
$
$

If the key of hash %z i.e. "I_1:a/a" or "I_1:a/b" etc. does not exist, then I increment the value of $n (which is reset to 0 at start of every line), and set this at the value of that key and display this value.
Otherwise, I just display the value of the hash key.

HTH,
tyler_durden
# 7  
Old 05-24-2010
Another requirement.
Don't hate me scrutinizer. I need a small update.
Consider 1st and 2nd columns as keys and 3rd column as their values.

The recent awk script here is turning all they keys with values ( similar letter pairs 1st occurrence of similar letters as 1 and 2nd occ.. as 3. and if they are diff then as 2 )
(ex: a/a as 1, a/b as 2 and b/b as 3)

Upgrade:

If any key (for ex: I_3-a9 has different letters in c/d name it as always 2. if this different letter pair repeats with another set (ex: c/e) name it with "?". And remaining same letter pairs (first occurrence) a/a and (second..) b/b remains as 1 and 3.

UpgradeOutput:

Code:
I_1     a1      a/a     1
I_1     a2      a/a     1
I_1     a3      a/b     2
I_1     a6      a/b     2
I_1     a8      a/a     1
I_1     a9      b/b     3
I_2     a1      b/b     1
I_2     a2      b/b     1
I_2     a3      b/b     1
I_2     a6      b/b     1
I_2     a8      b/c     2
I_2     a9      b/d     ?
I_3     a1      c/c     1
I_3     a2      c/c     1
I_3     a3      c/d     2
I_3     a6      c/e     ?
I_3     a8      c/e     ?

Last edited by ruby_sgp; 05-24-2010 at 03:45 AM.. Reason: format problem
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Print values within groups of lines with awk

Hello to all, I'm trying to print the value corresponding to the words A, B, C, D, E. These words could appear sometimes and sometimes not inside each group of lines. Each group of lines begins with "ZYX". My issue with current code is that should print values for 3 groups and only is... (6 Replies)
Discussion started by: Ophiuchus
6 Replies

2. Shell Programming and Scripting

Fetch the values based on a Key using awk from single file

Hi, Please help to fetch the values for a key from below data format in linux. Sample Input Data Format 11055005|PurchaseCondition|GiftQuantity|1 11055005|PurchaseCondition|MinimumPurchase|400 11055005|GiftCatalogEntryIdentifier|Id|207328014 11429510|PurchaseCondition|GiftQuantity|1... (2 Replies)
Discussion started by: mohanalakshmi
2 Replies

3. UNIX for Dummies Questions & Answers

awk - Print lines if only matching key is found

I am looking to move matching lines (01 - 07) from File1 and 77 tab the matching string from File2, to File3.txt. I am almost done but - Currently, script is not printing lines to File3.txt in order. Thanks a lot. Any help is appreciated. Script I am using: awk 'FNR == NR && ! /^]*$/ {... (9 Replies)
Discussion started by: High-T
9 Replies

4. Shell Programming and Scripting

awk - Print whole string ending with a Tab if key matched

Hi , I am looking to print the whole string from file2.txt but it is only printing 77 but not the whole matched string from File2.txt Any help is appreciated. Thanks, Script awk ' BEGIN { OFS="\t" out = "a.txt"} NR==FNR && NF {a=$0; next} function print_65_11() { if... (11 Replies)
Discussion started by: High-T
11 Replies

5. Shell Programming and Scripting

awk print odd values

value=$(some command) for all in `echo $value` do awk checks each value (all) to see if it is a odd number. if so, prints the value done sounds easy enough but i've been unable to find anything on google. (2 Replies)
Discussion started by: SkySmart
2 Replies

6. Shell Programming and Scripting

How to print in awk matching $1 values ,to $1,$4 example given.?

Hi Experts, I am trying to get the output from a matching pattern but unable to construct the awk command: file : aa bb cc 11 dd aa cc 33 cc 22 45 68 aa 33 44 44 dd aa cc 37 aa 33 44 67 I want the output to be : ( if $1 match to "aa" start of the line,then print $4 of that line, and... (3 Replies)
Discussion started by: rveri
3 Replies

7. Shell Programming and Scripting

match two key columns in two files and print output (awk)

I have two files... file1 and file2. Where columns 1 and 2 of file1 match columns 1 and 2 of file2 I want to create a new file that is all file1 + columns 3 and 4 of file2 :b: Many thanks if you know how to do this.... :b: file1 31-101 106 0 92 31-101 106 29 ... (2 Replies)
Discussion started by: pelhabuan
2 Replies

8. Shell Programming and Scripting

Extract key words and print their values

Input file (HTTP request log file): GET... (2 Replies)
Discussion started by: buptwy
2 Replies

9. UNIX for Advanced & Expert Users

Awk to print values of second file

Hello, I have a data file with 300,000 records in it, and another file which contains only the line numbers of roughly 13,000 records in the data file which have data integrity issues. I'm trying to find a way to print the original data by line number identified in the second file. How can I do... (2 Replies)
Discussion started by: peteroc
2 Replies

10. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies
Login or Register to Ask a Question