1

I have file looking like this:

pw1jc5ssyt6hx618,254343
ysezaratlycpuggl,254333
pht92h4adr3mrbz3,254343
hguvgstqxu3gowfg,254344
gqjp2rsjmk1a2v9c,254333
twdzyi2ddbnrfknd,254333
gcmj7krrx5x6nf8r,254341
tpqorqbyrg1nmm7s,254333
alnac47rt8d4ege3,254343

I want to merge this file based on 2nd column, with - as a delimiter, so that the result looks like this:

254343,pw1jc5ssyt6hx618-pht92h4adr3mrbz3-alnac47rt8d4ege3
254333,ysezaratlycpuggl-gqjp2rsjmk1a2v9c-twdzyi2ddbnrfknd-tpqorqbyrg1nmm7s
254344,hguvgstqxu3gowfg
254341,gcmj7krrx5x6nf8r
  • Please format the text. – sjsam Jul 31 '16 at 5:10
  • 1
    is blank lines are part of your file ? – Rahul Jul 31 '16 at 5:15
6

awk is your friend

$ cat 299360
ipw1jc5ssyt6hx618,254343
ysezaratlycpuggl,254333
pht92h4adr3mrbz3,254343
hguvgstqxu3gowfg,254344
gqjp2rsjmk1a2v9c,254333
twdzyi2ddbnrfknd,254333
gcmj7krrx5x6nf8r,254341
tpqorqbyrg1nmm7s,254333
alnac47rt8d4ege3,254343
$ awk -v FS="," '/^$/{next} # for empty line go to next record
                {if(NR==1){ # checking for first record
                f2[$2]=$1;next} # Adding $1 to array f2 at index $2
                else{
                if($2 in f2){ # Check if $2 is already an index in f2
                f2[$2]=f2[$2]"-"$1;next #appending "-$1" to current value
                }
                else{
                f2[$2]=$1;next
                }
                }}
                END{ # This line will be processed at the end
                for(i in f2){  # for all the indexes i in f2
                printf "%s,%s\n",i,f2[i] #printing in the desired format
                }
                }
                ' 299360
254341,gcmj7krrx5x6nf8r
254333,ysezaratlycpuggl-gqjp2rsjmk1a2v9c-twdzyi2ddbnrfknd-tpqorqbyrg1nmm7s
254343,pw1jc5ssyt6hx618-pht92h4adr3mrbz3-alnac47rt8d4ege3
254344,hguvgstqxu3gowfg

Explanation

  1. FS="," – FS is awk's built-in variable that stands for field separator. Setting field separator to , will set , as the delimiter.
  2. You access fields by $1, $2 and so on.
  3. The awk script is enclosed in single quotes; i.e., 'awk-script-goes-here'
  4. NR is an awk built-in variable that stands for record number (the number of the record currently being processed). By default, each line is a record.
  5. By f2[$2]=$1 we are setting up an associative array f2 with field2 (i.e., $2) as the index.
  6. $2 in f2 checks if the index is already present in the array.
  7. The if-else and printf are self explanatory.
  8. The END block in awk is executed only at the very end; i.e., after all the records have been processed.
  9. for(i in f2) is a for loop construct used to parse the associative arrays in awk. It is the other way of saying, for every index i in f2 do something
  10. Note that the above for loop may not print the array in an order. You may use the sort bash command to sort the array, though.
  11. next goes to the next record without processing the commands that follow.
  12. The /pattern/ checks for a pattern in awk; the pattern ^$ checks for empty line.

Reference

If you wish to get expert in awk, Effective awk Programming is a must read.

Ugly one-liner

awk -v FS="," '/^$/{next}{if(NR==1){f2[$2]=$1;next}else{if($2 in f2){f2[$2]=f2[$2]"-"$1;next}else{f2[$2]=$1;next}}}END{for(i in f2){printf "%s,%s\n",i,f2[i]}}' 299360

Note: Ideally, it's not a good idea to hard-code newlines in awk scripts, as in printf "%s,%s\n",i,f2[i]. You may replace it with printf "%s,%s\n",i,f2[i];print for extra portability.

  • Thanks I got the answer that i am expecting.It will be grateful if you explain the above command – Rock Jul 31 '16 at 5:13
  • @Rock . Thanks for accepting the answer, I have added comments -anything that follow # - after each line for better understanding – sjsam Jul 31 '16 at 5:41
  • To the downvoter : What is the downvote for? Any suggestion to improve? – sjsam Jul 31 '16 at 7:37
  • 1
    P.S. Congratulations on reaching 500 rep. – G-Man Jul 31 '16 at 20:10
  • 1
    I see. Alternatively, you could use the more compact printf "%s,%s%s", i, f2[i], ORS (although it is more cryptic). – G-Man Jul 31 '16 at 22:02
6

With GNU datamash:

datamash -t, -s -g 2 collapse 1 <data.txt | sed 's/,/-/2g'

Result:

254333,ysezaratlycpuggl-gqjp2rsjmk1a2v9c-twdzyi2ddbnrfknd-tpqorqbyrg1nmm7s
254341,gcmj7krrx5x6nf8r
254343,pw1jc5ssyt6hx618-pht92h4adr3mrbz3-alnac47rt8d4ege3
254344,hguvgstqxu3gowfg
3

In perl:

#! /usr/bin/perl

use strict;
my %mergecol = ();

while(<>) {
  s/#.*//;            # strip comments
  next if (m/^\s*$/); # skip empty lines
  chomp;
  my ($val,$key) = split ',';
  push @{ $mergecol{$key} }, $val;
};

foreach my $k (keys %mergecol) { 
  printf "%s,%s\n", $k, join('-', @{ $mergecol{$k} } );
}

The while loop reads the input and creates a Hash-of-Arrays (HoA) structure - an associative array where each element is an array (aka "list"). The keys to the hash are the second field, while the elements of each list are the first fields of lines with the same second field.

At the end of the script, the HoA is printed out one line per record, with the key name, a comma, and then the elements joined with a -.

run as:

$ ./rock.pl rock.txt
254341,gcmj7krrx5x6nf8r
254333,ysezaratlycpuggl-gqjp2rsjmk1a2v9c-twdzyi2ddbnrfknd-tpqorqbyrg1nmm7s
254344,hguvgstqxu3gowfg
254343,pw1jc5ssyt6hx618-pht92h4adr3mrbz3-alnac47rt8d4ege3

Or shortened and embedded in a shell command or script as a "one-liner":

$ perl -n -e '
    s/#.*//;
    next if (m/^\s*$/);
    chomp; ($v,$k)=split ","; push @{ $mc{$k} }, $v;
    END {
     foreach $k (keys %mc) { printf "%s,%s\n", $k, join("-",@{$mc{$k}}) }
    }' rock.txt 

Or

$ perl -e 'while(<>) {s/#.*//;next if (m/^\s*$/);chomp;($v,$k)=split ",";push @{$mc{$k}}, $v};
    foreach $k (keys %mc) {printf "%s,%s\n",$k,join("-",@{$mc{$k}})}' rock.txt

Note that a hash or associative array is inherently unordered, if you want sorted output, either pipe into sort or use (sort keys %f) on the foreach my $k lines above.

2

Building on sjsam’s answer, but simpler:

awk -v FS="," '
    {
            f2[$2] = f2[$2] "-" $1   # append "-" and $1 to the current value
    }
    END {   # This code will be processed at the end
            for (i in f2) {  # for all the indexes i in f2
                             # (i.e., each unique value from column 2)
                             # print one line in the desired format
                printf "%s,%s\n", i, gensub("-", "", 1, f2[i])
            }
    }
'

This simple-mindedly builds strings that look like this:

   -       pw1jc5ssyt6hx618      -       pht92h4adr3mrbz3      -       alnac47rt8d4ege3
(hyphen)       (value)        (hyphen)       (value)        (hyphen)       (value)

(but without the spaces; they’re just for illumination).  Then, when it comes time to print the data, it removes the first - using the general substitution function, gensub().  Unfortunately, this isn’t supported in the POSIX specification for awk; it requires GNU Awk.  Alternatively, you could change gensub("-", "", 1, f2[i]) to substr(f2[i], 2) and get the substring of f2[i] from the second character to the end (i.e., all but the first character, which is -), in a POSIX-compliant way.

This will (attempt to) process every line in the input.  If there are some lines in the input that should be ignored (e.g., blank lines), you could change the first part to

    /./ {
            f2[$2] = f2[$2] "-" $1   # …
    }

or

    NF==2 {
            f2[$2] = f2[$2] "-" $1   # …
    }

When I run sjsam’s answer or my own, I get

254333,ysezaratlycpuggl-gqjp2rsjmk1a2v9c-twdzyi2ddbnrfknd-tpqorqbyrg1nmm7s
254341,gcmj7krrx5x6nf8r
254343,pw1jc5ssyt6hx618-pht92h4adr3mrbz3-alnac47rt8d4ege3
254344,hguvgstqxu3gowfg

i.e., sorted by the value of the column 2 key.  You show the desired output in order of first appearance of the column 2 key in the input.  If that’s important to you, try:

awk -v FS="," '
    {
            if (! ($2 in f2)) appearance[++x] = $2
            f2[$2] = f2[$2] "-" $1   # append "-" and $1 to the current value
    }
    END {   # This code will be processed at the end
            for (ix in appearance) {
                             # for all the indexes i in f2
                             # (i.e., each unique value from column 2)
                             # print one line in the desired format
                i = appearance[ix]
                printf "%s,%s\n", i, gensub("-", "", 1, f2[i])
            }
    }
'

which uses the appearance array to keep track of the order of appearance.

  • Nice, but you might need a small note that this will work only on gnu-awk which is fine as per the problem. Also this won't remove the blank lines in the input( though the op has mentioned nothing about it). Otherwise ++ :) – sjsam Jul 31 '16 at 20:50
  • Yeah, I keep forgetting what GNU Awk features are extensions. Regarding blank lines, (1) I did mention the fact that I wasn’t handling them specially, and how the script could be adjusted to do that, and (2) the OP is a newbie.  SE newbies often type blank lines in data just to get hard returns, because they don’t understand how to do code / preformatted blocks.  That seems to be what happened here. – G-Man Jul 31 '16 at 22:01
1

With two-dimensional arrays found in GNU awk

awk -F, '{
  a[$2][$1]
  }
  END{
    for (i in a) {
      c=0; printf "%s,", i; 
        for (j in a[i]) {
          ++c; printf "%s%s", j, length(a[i]) == c? "\n": "-"
          }
     }
   }' file
  • Hardcoding a newline will makes the script less portable, though in this case it nbd – sjsam Jul 31 '16 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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