Match and print columns in second file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match and print columns in second file
# 1  
Old 04-20-2012
Match and print columns in second file

Hi All,

I have to match each row in file 1 with 1st row in file 2 and print the corresponding column from file2. I am trying to use an awk script to do this. For example

cat File1
Code:
X1
X3
X4

cat File2
Code:
ID	X1	X2	X3	X4
A	1	6	2	1
B	2	7	3	3
C	3	8	4	1
D	4	9	1	1

Desired output is

cat File3
Code:
X1	X3	X4
1	2	1
2	3	3
3	4	1
4	1	1

It will be really helpful if anyone can suggest how to do this in awk.

Thank you for your time.

Regards,
R
# 2  
Old 04-20-2012
Hi newpro,

One way:
Code:
$ cat file1
X1
X3
X4
$ cat file2
ID      X1      X2      X3      X4
A       1       6       2       1
B       2       7       3       3
C       3       8       4       1
D       4       9       1       1
$ cat script.awk 
BEGIN { 
        OFS = "\t" 
} 

FNR == NR { 
        f1[ $1 ] = 1
        next
} 

FNR < NR && FNR == 1 { 
        for ( i = 1; i <= NF; i++ ) { 
                if ( f1[ $i ] ) { 
                        header = header i 
                } 
        }
}

{ 
        for ( i = 1; i <= NF; i++ ) {
                if ( index( header, i ) > 0 ) {
                        printf $i OFS
                }
        }
        printf ORS
} 
$ awk -f script.awk file1 file2
X1      X3      X4
1       2       1
2       3       3
3       4       1
4       1       1

This User Gave Thanks to birei For This Post:
# 3  
Old 04-21-2012
Code:
awk 'BEGIN{OFS="\t"}
    NR == FNR{a[$0]; next} 
    FNR == 1{ for(i=1;i<=NF;++i){if($i in a){a[$i]=i}} for(e in a){printf e OFS} printf ORS; next}
    {for(i in a){printf $a[i] OFS} printf ORS}' file1 file2

This User Gave Thanks to kevintse For This Post:
# 4  
Old 04-23-2012
Birei, Kevintse,

Thanks a lot for you help.

-R
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am... (4 Replies)
Discussion started by: axis88
4 Replies

2. Shell Programming and Scripting

Match Columns in one file and extract columns from another file

Kindly help merging information from two files with the following data structure. I want to match for the CHR-SNP in Foo and get the columns that match from CHROM-rsID Fields 1 & 2 of foo may have duplicates, however, a joint key of Fields $1$2$3$4 is unique. Also would be helpful to clean up... (4 Replies)
Discussion started by: genehunter
4 Replies

3. Shell Programming and Scripting

Match columns and print specific field

Hello, I have data in following format. ... (6 Replies)
Discussion started by: Pushpraj
6 Replies

4. UNIX for Advanced & Expert Users

Match and print based on columns

HI, I have 2 different questions in this thread. Consider 2 files as input (input file have different line count ) File 1 1 1 625 56 1 12 657 34 1 9 25 45 1 2 20 54 67 3 25 35 27 4 45 73 36 5 125 56 45 File2 1 1 878 76 1 9 83 67 2 20 73 78 4 47 22 17 3 25 67 99 (4 Replies)
Discussion started by: rossi
4 Replies

5. 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

6. Shell Programming and Scripting

Match two columns from two files and print output

Hello, I have two files which are of the following format File 1 which has two columns Protein_ID Substitution NP_997239 T53R NP_060668 V267M NP_058515 P856A NP_001206 T55M NP_006601 D371Y ... (2 Replies)
Discussion started by: nans
2 Replies

7. Shell Programming and Scripting

match two key columns in two files and print output (awk)

I have two files... file1 and file2. Where columns 1 and 2 of file1 match columns 1 and 2 of file2 I want to create a new file that is all file1 + columns 3 and 4 of file2 :b: Many thanks if you know how to do this.... :b: file1 31-101 106 0 92 31-101 106 29 ... (2 Replies)
Discussion started by: pelhabuan
2 Replies

8. Shell Programming and Scripting

print when column match with other file

Hello all, please help. There are two file like this: file1: 1197510.0 294777.7 9666973.0 21.6 1839.8 1197510.0 294777.7 9666973.0 413.2 2075.9 1197510.0 294777.7 9666973.0 689.3 2260.0 ... (1 Reply)
Discussion started by: attila
1 Replies

9. Shell Programming and Scripting

Strings from one file which exactly match to the 1st column of other file and then print lines.

Hi, I have two files. 1st file has 1 column (huge file containing ~19200000 lines) and 2nd file has 2 columns (small file containing ~6000 lines). ################################# huge_file.txt a a ab b ################################## small_file.txt a 1.5 b 2.5 ab ... (4 Replies)
Discussion started by: AshwaniSharma09
4 Replies

10. Shell Programming and Scripting

Get columns from another file for match in col 2 in 1st file

Hi, My first file has 592155 9 rs16916098 1 592156 19 rs7249604 1 592157 4 rs885156 1 592158 5 rs350067 12nd file has 9 rs16916098 0 113228129 2 4 19 rs7249604 0 58709070 4 2 2 rs17042833 0 113558750 4 2... (2 Replies)
Discussion started by: genehunter
2 Replies
Login or Register to Ask a Question

Featured Tech Videos