Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Rows to Columns with match criteria

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Rows to Columns with match criteria
# 1  
Old 06-10-2014
Debian Rows to Columns with match criteria
Hello Friends,

I have a input file having hundreds of rows. I want them to translate in to columns if column 1 is same.

Input data:
Code:
zp06 xxx
zp06 rrr
zp06 hhh
zp06 aaa
zp06 ggg
zp06 qwer
zp06 ser
zl11 old3
zl11 old4
zl11 old5
zl11 old6
zl11 old7
zm14 luri
zm14 body
zm14 ucp
zm14 hanz
zm14 ghav
zm14 pal
zl16 old10
zl16 old11
zl16 old12
zl16 old13
zl16 old14

Desired Output:
Code:
zp06 xxx	zl11 old3	zm14 luri	zl16 old10
zp06 rrr	zl11 old4	zm14 body	zl16 old11
zp06 hhh	zl11 old5	zm14 ucp	zl16 old12
zp06 aaa	zl11 old6	zm14 hanz	zl16 old13
zp06 ggg	zl11 old7	zm14 ghav	zl16 old14
zp06 qwer			zm14 pal
zp06 ser

Thanks,
Suresh
Last edited by Scrutinizer; 06-10-2014 at 07:35 AM.. Reason: CODE tags
# 2  
Old 06-10-2014
Use Codetags!

Let us know, what you have tried so far ?
# 3  
Old 06-10-2014
What you describe and what you show as desired output does not match my understanding.
Please be more elaborate.
# 4  
Old 06-10-2014
a group of rows to be converted in to columns
# 5  
Old 06-10-2014
Okay try this, show your efforts next time

Code:
awk '        {
		if(!($1 in B))
		{
		 c[++k]=$1
		}
		++B[$1]
		A[$1 FS B[$1]]=$2
		max = (max!="" && max > B[$1]) ? max : B[$1]
		
             }
	  END{

		for(j=1;j<=max;j++)
		{
			s = ""
			for(i=1;i<=k;i++)
			{
				if((c[i] FS j) in A)
				{	
					s = length(s) ?  s OFS c[i] OFS A[c[i] FS j] : c[i] OFS A[c[i] FS j]
				}	
			}
			print s		
		}
             }
   ' file

# 6  
Old 06-10-2014
another example:
input:
Code:
z  2
z  4
x  2
x  5
y  1
y  3

Output:
Code:
z  2  x  2 y  1
z  4  x  5 y  3

---------- Post updated at 05:07 AM ---------- Previous update was at 05:04 AM ----------

Thanks Akshay...Smilie It is working..
Last edited by Scrutinizer; 06-10-2014 at 07:35 AM.. Reason: CODE tags
# 7  
Old 06-10-2014
Use codetags! atleast
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Match based on criteria to file

Trying to match $1 of target.txt to $5 of file.txt. If there is a match then in an output.txt file $1,$1 (row underneath),$6,$4,$7 from file.txt are printed on the same line as $1 of target.txt. The input is from excel and the output should be tab-deliminated. Thank you :). target.txt... (2 Replies)
Discussion started by: cmccabe
2 Replies

2. UNIX for Dummies Questions & Answers

Merge rows into one if first 2 columns match

Hi, I wanted to merge the content and below is input and required output info. Input: /hello,a,r /hello,a,L /hello,a,X /hi,b,v /hi,b,c O/p: /hello,a,r:L:X /hi,v,:v:c Use code tags, thanks. (6 Replies)
Discussion started by: ankitas
6 Replies

3. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

4. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

5. Shell Programming and Scripting

pull out rows with specific criteria

Hi, I have a file with multiple columns and for column 5 I want to extract that row into another file if it is equal to or greater than a certain value. For example: FAR 4 5 7 LOP GAT 3 3 3 POL I want values that are greater than 5 for column 4. So the final file will look like... (1 Reply)
Discussion started by: phil_heath
1 Replies

6. UNIX for Dummies Questions & Answers

delete rows with a criteria

Hi, I would like to know how can I delete rows of a text file if from the 3rd column onwards there is only zeros? Thanks in advance (7 Replies)
Discussion started by: fadista
7 Replies

7. Shell Programming and Scripting

Print lines that match certain criteria

Hi all I have a text file with the following format: id col1 col2 col3 col4 col5 col6 col7 ... row1 0 0 0 0 0 0 0 row2 0 0 0 0 0 0 0 row3 0 0 0 0 0 0.2 0 row4 0 0 0 0 0 0 0 row5 0 0 0 0 0 0 0 row6 0 0 0 0.1 0 0 0 row7 0 0 0 0 0 0 0 row8 0 0 0 0 0 0 0 row9 0 0 0 0 0 0 0 ... The file... (2 Replies)
Discussion started by: gautig
2 Replies

8. Shell Programming and Scripting

Need help in adding the column to set of lines that match the certain criteria

Hi, I need help to add a certain values to end of each line in the same file. Here is my input file name:aaa id :123 salary job qulification name:bbb id :124 salary job qulification name:ccc id :345 salary job qulification (2 Replies)
Discussion started by: jpkumar10
2 Replies

9. Shell Programming and Scripting

Replacing lines which match certain criteria

Hi, I have code which is like this <TABLE name="UsageDetail_24> <ROW> <Date24><!]></Date24> <Time24><!]></Time24> <Destination24><!]></Destination24> <Rate24><!]></Rate24> <Duration24><!]></Duration24> <Cost24><!]></Cost24> <Allowance24><!]></Allowance24> </ROW> <ROW>... (3 Replies)
Discussion started by: legolad
3 Replies

10. Shell Programming and Scripting

Selecting rows with a specific criteria

Hi, I want a UNIX command that can filter out rows with certain criteria. The file is tab deliminated. Row one is just a value. Basically what I want to do is select based on the name and character at the end (o). So lets lets say i want a row that has WashU and (o) then it would print... (2 Replies)
Discussion started by: phil_heath
2 Replies
Login or Register to Ask a Question