Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Extract columns where header matches a given string
Top Forums Shell Programming and Scripting Extract columns where header matches a given string Post 302506182 by sk1418 on Friday 18th of March 2011 06:32:46 PM
Old 03-18-2011
Quote:
Originally Posted by flotsam
Hi,
Given the following input files:

file1.txt

Code:
41109297 
41109706 
43162207
41109808
41109377
41110441
41111192
43163011
43162367

file2.txt

Code:
I Name    41109297 41109297 41109706 41109706 41110441 41110441 41111192 41111192 41112086 41112086 41113889 41113889 41114003 41114003 41114656 41114656 41115162 41115162 41115561 41115561

In the first case it seems like it's using file2 to order the output? Any thoughts on how I can keep the output in the order of file1?
hi there, i think i understood what you mean. You want to get those relevant columns AND change the column order against the order defined in file1. E.g in your pasted output, 43162207 column should be before 41110441. right?

then take a look this, if it is what you want. (with output)


Code:
ArchT60 /tmp/test
kent$ awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' f2 |awk 'BEGIN{i=j=m=n=1;}
NR==FNR{a[$1]=$1;aa[i++]=$1;} 
NR>FNR{if ($1 in a ){b[n++]=$0;bb[m++]=$1;}} 
END{
la=length(a); lb=length(b); for(i=1;i<=la;i++){ for(j=1;j<=lb;j++){ if (bb[j] == aa[i]) print b[j]; } } 
}' f1 - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' 
b=length(b); for(i=1;i<=la;i++){ for(j=1;j<=lb;j++){ if (bb[j] == aa[i]) print b[j]; } } }' f1 - |awk '{for (i=1;i<=NF;i++)a[i,NR]=$i; }END{for(i=1;i<=NF;i++) {for(j=1;j<=NR;j++)printf a[i,j]" ";print ""} }' 

41109297 41109297 41109706 41109706 43162207 43162207 41110441 41110441 41111192 41111192 43163011 43163011 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
B B B B B B B B B B B B 
A A A A A A A A A A A A 
B B B B B B B B B B B B 
A A A A A B A B A B A A

Last edited by sk1418; 03-18-2011 at 07:47 PM.. Reason: reformat
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extract if pattern matches

Hi All, I have an input below. I tried to use the awk below but it seems that it ;s not working. Can anybody help ? My concept here is to find the 2nd field of the last occurrence of such pattern " ** XXX ccc ccc cc cc ccc 2007 " . In this case, the 2nd field is " XXX ". With this "XXX" term... (20 Replies)
Discussion started by: Raynon
20 Replies

2. Shell Programming and Scripting

Joining columns from two files, if the key matches

I am trying to join/paste columns from two files for the rows with matching first field. Any help will be appreciated. Files can not be sorted and may not have all rows in both files. Thanks. File1 aaa 111 bbb 222 ccc 333 File2 aaa sss mmmm ccc kkkk llll ddd xxx yyy Want to... (1 Reply)
Discussion started by: sk_sd
1 Replies

3. Shell Programming and Scripting

Need awk help to print specific columns with as string in a header

awk experts, I have a big file of 4000 columns with header. Would like to print the columns with string value of "Commands" in header. File has "," separator. This file is on ESX host with Bash. Thanks, Arv (21 Replies)
Discussion started by: arv_cds
21 Replies

4. Shell Programming and Scripting

Merge two columns from two files into one if another column matches

I have two text files that look something like this: A:B:C 123 D:E:F 234 G:H:I 345 J:K:L 123 M:N:O 456 P:Q:R 567 A:B:C 456 D:E:F 567 G:H:I 678 J:K:L 456 M:N:O 789 P:Q:R 890 I want to find the line where the first column matches and then combine the second columns into a single... (8 Replies)
Discussion started by: pbluescript
8 Replies

5. Shell Programming and Scripting

Extract columns based on header

Hi to all, I have two files. File1 has no header, two columns: sample1 A sample2 B sample3 B sample4 C sample5 A sample6 D sample7 D File2 has a header, except for the first 3 columns (chr,start,end). "sample1" is the header for the 4th ,5th ,6th columns, "sample2" is the header... (4 Replies)
Discussion started by: aec
4 Replies

6. Shell Programming and Scripting

Blocks of text in a file - extract when matches...

I sat down yesterday to write this script and have just realised that my methodology is broken........ In essense I have..... ----------------------------------------------------------------- (This line really is in the file) Service ID: 12345 ... (7 Replies)
Discussion started by: Bashingaway
7 Replies

7. UNIX for Dummies Questions & Answers

Print Matches to New Columns

Hi all, I have a problem that I'm struggling to resolve. I have two files that look like this: File 1 654654654 3 987987987 2 321321321 1 File 2 14NS0064 654654654 14NS0054 654654654 14NS0032 654654654 14NS0090 987987987 14NS0093 987987987 14NS0056 321321321 As you may notice,... (2 Replies)
Discussion started by: winkleman
2 Replies

8. UNIX for Beginners Questions & Answers

Extract the whole set if a pattern matches

Hi, I have to extract the whole set if a pattern matches.i have a file called input.txt input.txt ------------ CREATE TABLE ABC ( A, B, C ); CREATE TABLE XYZ ( X, Y, Z, P, Q ); (6 Replies)
Discussion started by: raju2016
6 Replies

9. UNIX for Beginners Questions & Answers

Matches columns from two different files in shell script

Hi friends, i want to compare first columns from two different files ,if equal print the file2's second column else print the zero.Please help me... file1: a b c d efile2: a 1 c 20 e 30 desired output: 1 0 20 0 30 Please use CODE tags as required by forum rules! Please post in... (1 Reply)
Discussion started by: bhaskar illa
1 Replies

10. Shell Programming and Scripting

awk add all columns if column 1 name matches

Hi - I want to add all columns if column1 name matches. TOPIC1 5 1 4 TOPIC2 3 2 1 TOPIC3 7 2 5 TOPIC1 6 3 3 TOPIC2 4 1 3 TOPIC3 9 5 4 . . . . . . . . . . . . Result should look like TOPIC1 11 4 7 TOPIC2 7 3 4 (1 Reply)
Discussion started by: oraclermanpt
1 Replies

LEARN ABOUT OPENSOLARIS

comm

comm(1) 							   User Commands							   comm(1)

NAME

       comm - select or reject lines common to two files

SYNOPSIS

       comm [-123] file1 file2

DESCRIPTION

       The comm utility reads file1 and file2, which must be ordered in the current collating sequence, and produces three text columns as output:
       lines only in file1; lines only in file2; and lines in both files.

       If the input files were ordered according to the collating sequence of the current locale, the lines  written  will  be	in  the  collating
       sequence of the original lines. If not, the results are unspecified.

OPTIONS

       The following options are supported:

       -1    Suppresses the output column of lines unique to file1.

       -2    Suppresses the output column of lines unique to file2.

       -3    Suppresses the output column of lines duplicated in file1 and file2.

OPERANDS

       The following operands are supported:

       file1	A path name of the first file to be compared. If file1 is -, the standard input is used.

       file2	A path name of the second file to be compared. If file2 is -, the standard input is used.

USAGE

       See largefile(5) for the description of the behavior of comm when encountering files greater than or equal to 2 Gbyte ( 2^31 bytes).

EXAMPLES

       Example 1 Printing a list of utilities specified by files

       If file1, file2, and file3 each contain a sorted list of utilities, the command

	 example% comm -23 file1 file2	| comm -23 - file3

       prints a list of utilities in file1 not specified by either of the other files. The entry:

	 example% comm -12 file1 file2 | comm -12 - file3

       prints a list of utilities specified by all three files. And the entry:

	 example% comm -12  file2 file3 | comm -23 -file1

       prints a list of utilities specified by both file2 and file3, but not specified in file1.

ENVIRONMENT VARIABLES

       See  environ(5)	for  descriptions  of  the  following  environment  variables that affect the execution of comm: LANG, LC_ALL, LC_COLLATE,
       LC_CTYPE, LC_MESSAGES, and NLSPATH.

EXIT STATUS

       The following exit values are returned:

       0     All input files were successfully output as specified.

       >0    An error occurred.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE	     |	    ATTRIBUTE VALUE	   |
       +-----------------------------+-----------------------------+
       |Availability		     |SUNWesu			   |
       +-----------------------------+-----------------------------+
       |CSI			     |enabled			   |
       +-----------------------------+-----------------------------+
       |Interface Stability	     |Standard			   |
       +-----------------------------+-----------------------------+

SEE ALSO

       cmp(1), diff(1), sort(1), uniq(1), attributes(5), environ(5), largefile(5), standards(5)

SunOS 5.11							    3 Mar 2004								   comm(1)
All times are GMT -4. The time now is 12:42 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy