Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a file like below.

47196436 47723284 name1 1.77273

42672249 52856963 name2 1.06061
52856963 430695 name2 1.16667

55094959 380983 name3 1.55613

17926380 55584836 name4 1.02461
3213456 34211 name4 1.11
54321 34211 name4 1.23

The first 2 columns correspond to the primary keys in my table. I am trying to merge the rows in such a way that if there is same name all the keys will be in the same row.

I am trying to get the output as,

47196436 47723284 name1
42672249 52856963 430695 name2
55094959 380983 name3
17926380 55584836 3213456 34211 54321 name4

I was able to achieve it partially using the below command.

awk '{ x[$3]=x[$3] " " $2; } 
END { 
   for (k in x) print k,x[k] >"OUTPUT1";  
}' ccc.txt

However, it is not giving me the output correctly. I need some assistance in further modifying the above command.

share|improve this question
    
is the data sorted by the name field? –  1_CR Apr 11 at 0:21
    
Yeah, I have applied sort -k3 on the file. –  Ramesh Apr 11 at 0:23
    
Are the blank lines actually part of the file? –  terdon Apr 11 at 15:38
    
Yeah, they are part of my input file. –  Ramesh Apr 11 at 15:40

4 Answers 4

up vote 2 down vote accepted

A perl solution:

$ perl -ane '$h{$F[2]} .= " ".$F[0]." ".$F[1];
    END {
        for $k (sort keys %h) {
            print $_," " for grep {!$seen{$_}++} split(" ",$h{$k});
            print "$k\n";
        }
    }' file

47196436 47723284 name1
42672249 52856963 430695 name2
55094959 380983 name3
17926380 55584836 3213456 34211 54321 name4
share|improve this answer
    
Thanks for providing the perl solution. I will look into it as soon as I get into the server :) –  Ramesh Apr 11 at 3:28

Ungainly, but seems to do the job

awk '$3 != prev {if (NR != 1) print prev; prev=$3; delete a};
!($1 in a){a[$1]++; printf "%s ", $1};
!($2 in a){a[$2]++; printf "%s ", $2}; 
END {print prev}' ccc.txt
47196436 47723284 name1
42672249 52856963 430695 name2
55094959 380983 name3
17926380 55584836 3213456 34211 54321 name4
share|improve this answer
    
seems promising. I will check it as soon as I get a chance to get into the server :) –  Ramesh Apr 11 at 1:05
    
your answer worked the way I expected :) –  Ramesh Apr 11 at 17:05

Here's another Perl approach:

$ perl -ane 'foreach(@F[0..1]){$k{$F[2]}{$_}++}
           END{
                foreach $v (sort keys(%k)){
                    print "$_ " foreach(keys(%{$k{$v}})); 
                    print "$v\n"
                }; 
            } ' file

This produces:

47723284 47196436 name1
42672249 430695 52856963 name2
380983 55094959 name3
34211 55584836 17926380 54321 3213456 name4

Explanation

OK, I admit, the Perl script above is not an example of easy to understand Perl. I'm using a lot of tricks and they obfuscate the code. I am presenting the same solution here but formatted as a script and using a more verbose approach:

#!/usr/bin/perl 

## This is the hash that will store our values. 
my %k;

## Read through the input file line by line
## saving each line as $line. This is what the -n
## switch to perl means, only there each line is saved
## in the special variable $_.
while (my $line=<>) {
    ## Split the line into the @F array. This is
    ## what the -a switch does.
    #chomp($line);
    my @F=split(/\s+/,$line);


    ## Populate the %k hash that we defined at the beginning.
    ## This is a hash of hashes, it looks like this:
    ##   $hash{key1}{key2}=value
    ## In this case, we are saying:
    ##   $hash{3rd field}{1st field}=1 
    ##   $hash{3rd field}{2nd field}=1 
    ## This just serves to add the 1st and 2nd fields
    ## to the list of fields for this $F[2] (the 3rd field, the name).
    ## A side effect of this is that hash keys are unique so duplicates
## are automatically removed.
    $k{$F[2]}{$F[0]}=1;
    $k{$F[2]}{$F[1]}=1;

}

## We have now finished processing the file
## (this is the END{} block above), so let's print.

## This saves the keys of the hash %k in the @names array
## sorted alphabetically.
my @names=(sort keys(%k));


## Go through each of the names, saving
## them as $name
foreach my $name (@names) {
    ## Now, iterate through the values associated 
    ## with the current $name. These are saved as the
    ## keys of the hash %k{$name}
    foreach my $value ( (keys(%{$k{$name}})) ){
      print "$value ";
    } 
    ## Now print the name as well
    print "$name\n";

}

The script above does exactly the same thing as the one liner I posted, it is just expanded to use a clearer syntax.

share|improve this answer
    
Thanks a lot for providing the solution :) –  Ramesh Apr 11 at 17:01
    
your solution was perfect :) I wish there is a way to mark 2 answers as correct ones :) –  Ramesh Apr 11 at 17:05
    
@Ramesh no worries, you should always accept the one you prefer, for whichever reason you prefer it and anyway, Gnouc's answer is great and he needs the rep more than I do :) –  terdon Apr 11 at 17:07
    
exactly that was the reason I accepted his answer :) However, your answer gave me more insights onto what actually the perl script does. I really appreciate it :) –  Ramesh Apr 11 at 17:08

If you don't mind using gawk >= 4.0, this (which is pretty much the same as terdon's) will produce the desired output, with optional name and key ordering:

NF {
    Names[$3][$1] = 1;
    Names[$3][$2] = 1;
} 
END {
    PROCINFO["sorted_in"] = "@ind_str_asc"; # if you want `Name` ordered
    for (Name in Names) { 
        PROCINFO["sorted_in"] = "@ind_num_asc"; # if you want `Key` ordered
        for (Key in Names[Name]) {
            printf("%s ", Key);
        }
        print Name;
    }
}

Gives:

47196436 47723284 name1
430695 42672249 52856963 name2
380983 55094959 name3
34211 54321 3213456 17926380 55584836 name4
share|improve this answer
    
What do you mean "Key ordered"? The OP seems want to preserve the original order, not alphabet order. –  cuonglm Apr 12 at 9:22
    
@Gnouc They keys in the above example are ordered ascending numerically as this seems to be the most logical thing to do if weeding out duplicate primary keys by printing them in a linear manner. It appears that the output is for humans, so ordering that way will make it easier, but you are right that I have made the assumption which is why I noted so in the comments. If OP wants a strictly first seen order, then the code will need to be changed. OP didn't mention order, only putting keys on the same row. –  user61786 Apr 12 at 9:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.