Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Match Columns in one file and extract columns from another file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match Columns in one file and extract columns from another file
# 1  
Old 08-16-2017
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 the file column delimiter to make sure spaces (more than one)are converted to single tab.
awk preferred.
Many thanks
~GH
File foo:
Code:
CHR                 SNP   A1   A2          MAF  NCHROBS
   1          rs10005934    A    C       0.0038      452
   1          rs10015934    A    G       0.0038      452
   1            rs710870    A    G       0.4004      452
   1           rs2073105    G    A         0.25      452
   1            rs710871    A    G      0.01549      452
   1            exm25630    0    G            0      452

File bar:
Code:
CHROM     POS     rsID    cM      A1      A2   
   1       202183358    rs10005934    200.23    A    C
   1       202183358    rs10015934    200.23    A    G
   1       222445567     rs710870    51.21      A    G
   1       235658554     rs2073105    25.84      G    A
  10          27436462     rs1234566    1.52      D    I

required file foobar

Code:
CHR                 SNP   A1   A2          MAF  NCHROBS     CHROM     POS     rsID    cM      A1      A2  
   1          rs10005934    A    C       0.0038      452        1       202183358    rs10005934    200.23    A    C            
   1          rs10015934    A    G       0.0038      452        1       202183358    rs10015934    200.23    A    G     
   1            rs710870    A    G       0.4004      452        1       222445567     rs710870    51.21      A    G     
   1           rs2073105    G    A         0.25      452        1       235658554     rs2073105    25.84      G    A

# 2  
Old 08-16-2017
Code:
awk '
FNR==1  { printf $0 ((c++) ? "\n" : "\t") }
NR==FNR {a[$1,$2]=$0; next}

a[$1,$3] { print a[$1,$3] "\t" $0 }
' foo bar

# 3  
Old 08-17-2017
While rdrtx1's proposal works fine for the samples given, it doesn't for the duplicates mentioned as the samples don't have any. Nor is the request for <TAB> field separators in the result fulfilled. Try

Code:
awk '

                {IX1 = $1 OFS $2 OFS $3 OFS $4
                 IX2 = $1 OFS $3 OFS $5 OFS $6
                 $1 = $1
                }

FNR==1          {printf $0 ((c++) ? "\n" : "\t")
                }

NR==FNR         {a[IX1] = $0
                 next
                }

a[IX2]          {print a[IX2] "\t" $0
                }
' OFS="\t" file[12]

This User Gave Thanks to RudiC For This Post:
genehunter
# 4  
Old 08-22-2017
Can you please explain the code.
Does
Code:
$1=$1

help cleaning up the first column or print the first line?
Would be very useful to understand the usage and learn awk if you can write a few words of explanation.
Thanks
Quote:
Originally Posted by RudiC
While rdrtx1's proposal works fine for the samples given, it doesn't for the duplicates mentioned as the samples don't have any. Nor is the request for <TAB> field separators in the result fulfilled. Try

Code:
awk '

                {IX1 = $1 OFS $2 OFS $3 OFS $4
                 IX2 = $1 OFS $3 OFS $5 OFS $6
                 $1 = $1
                }

FNR==1          {printf $0 ((c++) ? "\n" : "\t")
                }

NR==FNR         {a[IX1] = $0
                 next
                }

a[IX2]          {print a[IX2] "\t" $0
                }
' OFS="\t" file[12]

# 5  
Old 08-22-2017
Neither ... nor. The $1 = $1 trick replaces ALL field separators (multiples as well) with the OFS char without modifying the fields' contents. man awk:
Quote:
Assignment to $0 causes the fields and NF to be recomputed. Assignment to NF or to a field causes $0 to be reconstructed by concatenating the $i's separated by OFS.
This User Gave Thanks to RudiC For This Post:
genehunter
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extract columns into seperate file

I have a comma delimited file as per the one below and I am currently extracting the values in 2 columns (COL1 & COL6) to produce a smaller trimmed down version of the file which only contains the columns we need; COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9... (1 Reply)
Discussion started by: Ads89
1 Replies

2. Shell Programming and Scripting

Match pattern from file 1 with any/all columns in file 2

Hi, I have been looking everywhere for an example so I can try and do this myself but I am having difficulty. I have 2 large files of different sizes and if the pattern in the 3rd column in file 1 is in "any" column in file 2 I want to print all of the line in file 1 and append that line with the... (5 Replies)
Discussion started by: kieranfoley
5 Replies

3. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

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

5. Shell Programming and Scripting

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 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 (3 Replies)
Discussion started by: newpro
3 Replies

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

7. Shell Programming and Scripting

Extract Columns from file

Hi All, Could you please help me with following: I have to parse a .csv file. For example: If the csv file contains 3 columns, then i have to print the column names. The field separator is (comma). example.csv (contains 2 lines as follows) This is,a test file, for validation... (2 Replies)
Discussion started by: vfrg
2 Replies

8. Shell Programming and Scripting

use of format file to extract columns from a source file

hi experts lets say my format file is B B ========= column no,name,type,length 1,ee,N,12 3,hj,N.4 4,kl,N,5 source file ======== d e f g h i 5 8 9 7 6 5 1 3 4 5 6 6 (2 Replies)
Discussion started by: subhendu81
2 Replies

9. Shell Programming and Scripting

Help, need to extract columns from file

I have huge fixed width, text file in unix box and I need to extract columns found between the width 105 and 200 and output it to a new file. Can anyone tell me how to extract it? Thanks for your help. (1 Reply)
Discussion started by: kiran2k
1 Replies

10. Shell Programming and Scripting

how to extract columns from a text file

Hi, In ksh, I have a file with similar rows as follows: Department = 1234 G/L Asset Acct No = 12.0000. 2/29/2008 Department = 1234 G/L Asset Acct No = 13.0000. 3/29/2008. I want to create a new text file that contains only the numbers and date: 1234 12.0000. 2/29/2008 1234 13.0000. ... (16 Replies)
Discussion started by: ihot
16 Replies
Login or Register to Ask a Question