Sorting a log file using a connection

bash

is probably the wrong tool for this. Try Python:

import fileinput
import sys

oldTime = None
for line in fileinput.input():
    line = line.strip()
    pos = line.find(',')
    time = line[0:pos]
    if oldTime == time:
        sys.stdout.write(',')
        sys.stdout.write(line[pos+1:])
    else:
        if oldTime is not None:
            sys.stdout.write('\n')
        sys.stdout.write(line)

    oldTime = time

sys.stdout.write('\n')

      

        
        
        
      

    

If this is an operation that you would like to repeat often, I would choose a script utility written in a more "complete" scripting language. Then you can call the script in your own bash script, or use it on the command line as needed.

Here's a Python example:

#!/usr/bin/env python
# --- merge_groups.py ----
import fileinput, operator, itertools
lines = (line.strip() for line in fileinput.input())
data = (line.split(",") for line in lines if line)
for key, group in itertools.groupby(data, operator.itemgetter(0)):
  _, label, value =  zip(*group)
  print "%s,%s,%s" % (key, "|".join(label), "|".join(value))

      

        
        
        
      

    

Note that the script assumes that entries with the same timestamp are already grouped.

You can use a script to process existing data files or pipe data directly to it, for example:

[me@home]$ ./merge_groups.py data.txt  # parse existing data file
Time,Tag,Value
1,ABC,3
2,ABC|DEF,2.7|3.4
3,ABC|DEF|GHI|JKL,2.8|3.6|2.99|3.01
4,ABC|DEF|JKL,3.42|3.62|3.82

[me@home]$ cat data.txt | ./merge_groups.py  # post-process command output
Time,Tag,Value
1,ABC,3
2,ABC|DEF,2.7|3.4
3,ABC|DEF|GHI|JKL,2.8|3.6|2.99|3.01
4,ABC|DEF|JKL,3.42|3.62|3.82

      

        
        
        
      

    

@AaronDigulla and @Kent have some good solutions, but if you have / would like to go with bash, here is one:

for ts in `cat inputfile | cut --delimiter="," --fields=1 | uniq`
do
  p1="";
  p2="";
  for line in `grep "^${ts}," inputfile | cut --delimiter="," --fields=2-`
  do
    f1=`echo ${line} | cut --delimiter="," --fields=1`;
    f2=`echo ${line} | cut --delimiter="," --fields=2`;
    p1=${p1}"|"$f1;
    p2=${p2}"|"$f2;
  done
  echo ${ts}","${p1#?}","${p2#?};
done

      

        
        
        
      

    

Just in case, you want another awk solution!

function read() {
  split($0, buf, ",")
}

function write() {
  for (i = 1; i < length(buf); i++) {
    printf "%s,", buf[i]
  }
  print buf[length(buf)]
}

BEGIN {
  FS = ","
}

NR == 1 {
  print
  next
}

NR == 2 {
  read()
  next
}

{
  if ($1 != time) { # new time                                                                                                                                                                   
    time = $1
    write()
    read()
  } else { # repeated time                                                                                                                                                                       
    for (i = 2; i <= NF; i++) {
      buf[i] = buf[i] "|" $i
    }
  }
}

END {
  write()
}

      

        
        
        
      

    

I'm not that good with awk, so I had to emphasize readability!

Hmm, you really said "all the help", so this would include a Ruby solution?

require 'csv'

puts(CSV.read('f.csv').group_by(&:first).map do |k, v|
  t = v.transpose
  [k, t[1].join('|'), t[2].join('|')].join(',')
end.drop(1))

      

        
        
        
      

    

Perl is not represented.

use strict;
my $skip_header = <>;
my %d;
while(<>) {
    s/\s+$//;
    my ($no, $k, $v )  = split ",";
    push @{$d{int($no)}}, [ $k,  $v ];
}
END {
    foreach my $no (sort { $a <=> $b } keys %d  )  {
        print $no, ",";
        print join("|", map { $_->[0] } @{$d{$no}});
        print ",";
        print join("|", map { $_->[1] } @{$d{$no}});
        print "\n";
    }
}

      

        
        
        
      

    

gives:

1,ABC,3
2,ABC|DEF,2.7|3.4
3,ABC|DEF|GHI|JKL,2.8|3.6|2.99|3.01
4,ABC|DEF|JKL,3.42|3.62|3.82

      

        
        
        
      

    

For the first option, you can try:

awk -F, 'p x!=$1{if(p x)print s; p=s=$1} {sub($1,x); s=s $0} END{print s}' file

      

        
        
        
      

    

the first:

> awk -F, '{a[$1]=a[$1]","$2","$3}END{for(i in a)print i","substr(a[i],2)}' temp | sort
1,ABC,3
2,ABC,2.7,DEF,3.4
3,ABC,2.8,DEF,3.6,GHI,2.99,JKL,3.01
4,ABC,3.42,DEF,3.62,JKL,3.82

      

        
        
        
      

    

second:

> awk -F, '{a[$1]=a[$1]"|"$2;b[$1]=b[$1]"|"$3}END{for(i in a)print i","substr(a[i],2)","substr(b[i],2)}' temp | sort
1,ABC,3
2,ABC|DEF,2.7|3.4
3,ABC|DEF|GHI|JKL,2.8|3.6|2.99|3.01
4,ABC|DEF|JKL,3.42|3.62|3.82