Awk multiple lines with 3rd column onto a single line?

SoMoney · December 4, 2008, 5:33pm

I have a H U G E file with over 1million entries in it.
Looks something like this:

I would like a way to output only the uniq veriables onto a single line like so:

USER0001|DEVICE001|VAR1|VAR2|VAR3|VAR4|VAR5|VAR6
USER0001|DEVICE002|VAR1|VAR2|VAR3|VAR4|VAR5
USER0002|DEVICE003|VAR1|VAR2|VAR3|VAR4|VAR5
USER0002|DEVICE004|VAR1|VAR2|VAR3|VAR4|VAR5
USER0003|DEVICE005|VAR1|VAR2|VAR3|VAR4|VAR5|VAR6
USER0003|DEVICE006|VAR1|VAR2|VAR6

Is this doable with awk or do I need to use perl array?

vgersh99 · December 4, 2008, 5:41pm

nawk 'BEGIN {FS="|"}END{for(r in _)print r FS _[r]}{idx=$1 FS $2;_[idx]=_[idx]?_[idx] FS $3:$3}' myFile

SoMoney · December 4, 2008, 6:43pm

okay that's awesome, I never expected it to be a one liner!
I sure would like to learn how you determined nawk was the best tool for this job.

I know I can rtfm and figure this out but if possible, I sure would appreciate a translation of how FS and idx play into this final solution

Or is that outside the realm of these forums?

freelong · December 5, 2008, 4:13pm

awk -F "|" '{arr[$1"|"$2]=arr[$1"|"$2]"|"$3} END {for(i in arr) print i"|" arr}' filename

summer_cherry · December 6, 2008, 7:59am

open FH,"<a.txt";
while(<FH>){
	tr/\n//d;
	my @tmp=split("[|]",$_);
	$key=$tmp[0]."|".$tmp[1];
	if(exists $hash{$key}){
		$hash{$key}=sprintf("%s|%s",$hash{$key},$tmp[2]);
	}
	else{
		$hash{$key}=$tmp[2];
	}
}
close FH;
for $key (sort keys %hash){
	print $key,"|",$hash{$key},"\n";
}