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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk multiple lines with 3rd column onto a single line?
# 1  
Old 12-04-2008
Awk multiple lines with 3rd column onto a single line?

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

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


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?

Last edited by SoMoney; 12-04-2008 at 06:39 PM..
# 2  
Old 12-04-2008
Code:
nawk 'BEGIN {FS="|"}END{for(r in _)print r FS _[r]}{idx=$1 FS $2;_[idx]=_[idx]?_[idx] FS $3:$3}' myFile

# 3  
Old 12-04-2008
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 Smilie

Or is that outside the realm of these forums?

Last edited by SoMoney; 12-04-2008 at 07:49 PM..
# 4  
Old 12-05-2008
Code:
awk -F "|" '{arr[$1"|"$2]=arr[$1"|"$2]"|"$3} END {for(i in arr) print i"|" arr[i]}' filename

# 5  
Old 12-06-2008
Code:
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";
}

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Multiple lines to single line

I have code as below # create temporary table `temp4277`(key(waybill_no)) select waybill_no,concat_ws('',card_type,card_series_no) cardinfo from rfid_temp_ticket where waybill_no='4277' group by... (4 Replies)
Discussion started by: kaushik02018
4 Replies

2. UNIX for Beginners Questions & Answers

Merging multiple lines into single line based on one column

I Want to merge multiple lines based on the 1st field and keep into single record. SRC File: AAA_POC_DB.TAB1 AAA_POC_DB.TAB2 AAA_POC_DB.TAB3 AAA_POC_DB.TAB4 BBB_POC_DB.TAB1 BBB_POC_DB.TAB2 CCC_POC_DB.TAB6 OUTPUT ----------------- 'AAA_POC_DB','TAB1','TAB2','TAB3','TAB4'... (10 Replies)
Discussion started by: raju2016
10 Replies

3. Shell Programming and Scripting

Coverting multiple lines to a single line

Hi all, I have a requirement to covert multiple lines in a comma delimited file to a single line through shell scripting. We should compare the data in the first column in each line. If it is same, then the other data should be put in the same line.Below is the sample input and expected output:... (4 Replies)
Discussion started by: Bobby_2000
4 Replies

4. Shell Programming and Scripting

Combine multiple lines into single line

Hi All , I have a file with below data # User@Host: xyz @ # Query_time: t1 Lock_time: t2 Rows_sent: n1 Rows_examined: n2 SET timestamp=1396852200; select count(1) from table; # Time: 140406 23:30:01 # User@Host: abc @ # Query_time: t1 Lock_time: t2 Rows_sent: n1 Rows_examined:... (6 Replies)
Discussion started by: rakesh_411
6 Replies

5. Shell Programming and Scripting

Awk match multiple columns in multiple lines in single file

Hi, Input 7488 7389 chr1.fa chr1.fa 3546 9887 chr5.fa chr9.fa 7387 7898 chrX.fa chr3.fa 7488 7389 chr21.fa chr3.fa 7488 7389 chr1.fa chr1.fa 3546 9887 chr9.fa chr5.fa 7898 7387 chrX.fa chr3.fa Desired Output 7488 7389 chr1.fa chr1.fa 2 3546 9887 chr5.fa chr9.fa 2... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

6. Shell Programming and Scripting

Awk multiple lines with 4th column on to a single line

This is related to one of my previous post.. I have huge file currently I am using loop to read file and checking each line to build this single record, its taking much much time to parse those records.. I thought there should be a way to do this in awk or sed. I found this code in this forum... (7 Replies)
Discussion started by: Vasan
7 Replies

7. Shell Programming and Scripting

Multiple lines in a single column to be merged as a single line for a record

Hi, I have a requirement with, No~Dt~Notes 1~2011/08/1~"aaa bbb ccc ddd eee fff ggg hhh" Single column alone got splitted into multiple lines. I require the output as No~Dt~Notes 1~2011/08/1~"aaa<>bbb<>ccc<>ddd<>eee<>fff<>ggg<>hhh" mean to say those new lines to be... (1 Reply)
Discussion started by: Bhuvaneswari
1 Replies

8. Shell Programming and Scripting

Combine multiple lines in single line

This is related to one of my previous post but now with a slight difference: I need the "Updated:" to be in one line as well as the "Information:" on one line as well. These are in multiple lines right now as seen below. These can have 2 or more lines that needs to be in one line. System name:... (8 Replies)
Discussion started by: The One
8 Replies

9. Shell Programming and Scripting

Multiple lines into a single line

Hi, I've some files with the following data and i need to convert the lines between the separator ---, into a single line. I've tried with the paste cmd but my main problem is that the number of lines between the separator is not fix, it can very between 1-4 lines. Input --- 2010-02-22... (4 Replies)
Discussion started by: RickyC9999
4 Replies

10. Shell Programming and Scripting

Splitting a single line into multiple lines

I have a case where, I need to look into a file. Go to each line of the file, find the length of the line, if the length of the line is more than 75 chars, I need to split the line into multiple lines of 75chars max. If the length of the line is less than 75, we need not do anything. So at the... (4 Replies)
Discussion started by: thanuman
4 Replies
Login or Register to Ask a Question