LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 01-14-2013, 11:34 AM   #1
jv61
LQ Newbie
 
Registered: May 2012
Posts: 24

Rep: Reputation: Disabled
How to convert newline to tab for values in duplicate rows


I have a file which looks like this
Code:
Reference Value
Con1      BC1:10
Con1      BC2:2
Con2      BC1:80
Con2      BC2:40
Con3      BC1:3
Con4      BC1:1
Con4      BC2:12
I would like to print the duplicate values in column only once and their corresponding values converted to tab delimited. So I am expecting my results to look like this

Code:
Reference Value 
Con1       BC1:10 BC2:2
Con2       BC1:80 BC2:40 
Con3       BC1:3 
Con4       BC1:1  BC2:12
Any ideas of how to get this using awk/sed/perl ?

Thanks in advance,
 
Old 01-14-2013, 11:59 AM   #2
rmacd
LQ Newbie
 
Registered: Jan 2013
Location: Scotland
Distribution: Debian
Posts: 16

Rep: Reputation: 2
Code:
cat <filename> | awk -F' ' '{if($1 in a) {a[$1]=a[$1] " " $NF} else {a[$1]=$0}} END {asort(a); for(x in a) print a[x]}' | sort
HTH
 
1 members found this post helpful.
Old 01-14-2013, 06:29 PM   #3
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,604

Rep: Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241
Depending on the file size you could run out of memory.

As long as the input is sorted the following will do the work without saving the entire file in memory:

Code:
#!/bin/bash
awk '
BEGIN   {v=""; line=""}
        {
          if (v == $1) {
            line = line " " $2;
          } else {
            if (line != "") {print line};
            line = $0;
            v = $1;
          }
        }
END     { print line }'
Make the script executable and you can redirect input to it (or use a pipe from sort) and redirect output to another file.
 
1 members found this post helpful.
Old 01-15-2013, 04:44 AM   #4
jv61
LQ Newbie
 
Registered: May 2012
Posts: 24

Original Poster
Rep: Reputation: Disabled
Many thanks for the replies. Both the answers solved my problem. Could you please also explain what each section of the code does, that will be helpful for better understanding as I am a beginner in learning to program in awk. Thank you
 
Old 01-15-2013, 04:53 AM   #5
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,252

Rep: Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685
Maybe something like:
Code:
awk '$0=($1==a)?"\t"$2:"\n"$0;{a=$1}' ORS="" file
 
1 members found this post helpful.
Old 01-15-2013, 06:11 AM   #6
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,604

Rep: Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241
Code:
awk '
BEGIN	{v=""; line=""}	        # initialization - v is empty key, line empty
	{			# for every input record:
	  if (v == $1) {	# if field 1 (a key) matches saved key 
	    line = line " " $2;	# then append field 2 to the current line
	  } else {		# else a new key is seen
	    if (line != "") {print line};# print the current line if line not empty
	    line = $0;		# save the new line
	    v = $1;		# and the new key
	  }
	}
END	{ print line }'		# at end of file print the last line
 
2 members found this post helpful.
Old 01-15-2013, 01:45 PM   #7
jv61
LQ Newbie
 
Registered: May 2012
Posts: 24

Original Poster
Rep: Reputation: Disabled
Thanks for the explanation, that's helpful.
 
Old 01-15-2013, 04:34 PM   #8
syg00
LQ Veteran
 
Registered: Aug 2003
Location: Australia
Distribution: Lots ...
Posts: 14,839

Rep: Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822Reputation: 1822
I always enjoy (and learn from) grails attempts to turn awk into a (pale) imitation of perls minimalist approach ....

In this case an extra newline at the end might be appropriate.
 
Old 01-15-2013, 08:50 PM   #9
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,604

Rep: Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241
It is also nearly incomprehensible.
 
Old 01-16-2013, 02:23 AM   #10
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,252

Rep: Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685
I just like to play
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] sed command to replace 7th tab with newline tonyfreeman Programming 4 03-11-2011 05:36 PM
[SOLVED] Delete rows based on values in a column using sed captainentropy Linux - Newbie 6 01-19-2011 09:59 AM
convert columns to rows (tab separated file to csv) doug23 Programming 16 08-16-2009 10:14 PM
How to duplicate rows of text from one file to another? guest Programming 1 04-25-2009 09:14 AM


All times are GMT -5. The time now is 12:29 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration