Visit Our UNIX and Linux User Community


awk to update field in file2 if not the same as file1


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to update field in file2 if not the same as file1
# 1  
Old 12-30-2016
awk to update field in file2 if not the same as file1

Trying to use awk to:

update $2 in file2 with the $2 value in file1, if $1 in file1 matches $13 in file2, which is tab-delimeted. The $2values may already be the same so in that case nothing happens and the next line is processed.

There are exactly 4,605 unique $13 values. Thank you Smilie.

file1
Code:
MECP2 NM_004992.3
CBLB NM_170662.3

file2
Code:
173    NM_170662.3    chr3    -    105377108    105587887    105377813    105586421    19    105377108,105389076,105397274,105400322,105400567,105404163,105412337,105420937,105422831,105438890,105452852,105456014,105459337,105464760,105470305,105495239,105572257,105586253,105587579,    105378073,105389196,105397415,105400454,105400662,105404310,105412432,105421303,105423017,105439094,105452984,105456102,105459475,105464882,105470462,105495386,105572508,105586435,105587887,    0    CBLB    cmpl    cmpl    1,1,1,1,2,2,0,0,0,0,0,2,2,0,2,2,0,0,-1,
219    NM_001110792.1    chrX    -    153295685    153363188    153295817    153363122    3    153295685,153297657,153363060,    153296901,153298008,153363188,    0    MECP2    cmpl    cmpl    2,2,0,

desired output
Code:
173    NM_170662.3    chr3    -    105377108    105587887     105377813    105586421    19     105377108,105389076,105397274,105400322,105400567,105404163,105412337,105420937,105422831,105438890,105452852,105456014,105459337,105464760,105470305,105495239,105572257,105586253,105587579,      105378073,105389196,105397415,105400454,105400662,105404310,105412432,105421303,105423017,105439094,105452984,105456102,105459475,105464882,105470462,105495386,105572508,105586435,105587887,     0    CBLB    cmpl    cmpl    1,1,1,1,2,2,0,0,0,0,0,2,2,0,2,2,0,0,-1,
219   NM_004992.3     chrX    -    153295685    153363188    153295817     153363122    3    153295685,153297657,153363060,     153296901,153298008,153363188,    0    MECP2    cmpl    cmpl    2,2,0,

awk
Code:
awk 'FNR==NR { a[$1]=$13; next } { if(a[$2]){$2=a[$2] }; print }' OFS="\t" file1 file2 > out

# 2  
Old 12-30-2016
Let's remove a few things out and see if you see what's going on.

Code:
awk 'FNR==NR { a[$1]=$13; next }' file1


file1

Quote:
MECP2 NM_004992.3
CBLB NM_170662.3
How many fields does file1 has?: 2
What do you expect that it will be stored in a[$1] if you are asking for the 13th field in file1?
This User Gave Thanks to Aia For This Post:
# 3  
Old 12-30-2016
Quote:
What do you expect that it will be stored in a[$1] if you are asking for the 13th field in file1?
I see that there is no $13 in file1.

So the below in bold would store MECP2 in a[$1]. The the portion in italics would update $2 in file2 only if $13=a[$1]?

I had file1 and file2 confused in the awk, but am not quite sure about the second portion. Thank you Smilie.

Code:
awk 'FNR==NR { a[$1]=$1; next } { if(a[$2]){$13=a[$1] }; print }' OFS="\t" file1 file2 > out


Last edited by cmccabe; 12-30-2016 at 09:27 PM.. Reason: added details
# 4  
Old 12-31-2016
Quote:
Originally Posted by cmccabe
I see that there is no $13 in file1.

So the below in bold would store MECP2 in a[$1]. The the portion in italics would update $2 in file2 only if $13=a[$1]?

I had file1 and file2 confused in the awk, but am not quite sure about the second portion. Thank you Smilie.

Code:
awk 'FNR==NR { a[$1]=$1; next } { if(a[$2]){$13=a[$1] }; print }' OFS="\t" file1 file2 > out

Actually, field $2 is the value you need to store from file1
Give it a try to the following:
Code:
awk 'FNR==NR {a[$1]=$2; next} a[$13]{$2=a[$13]}1' OFS='\t' file1 file2

This User Gave Thanks to Aia For This Post:
# 5  
Old 01-04-2017
Thank you very much for your help Smilie

Previous Thread | Next Thread
Test Your Knowledge in Computers #508
Difficulty: Easy
A variable is basically a memory location in which a value can be stored.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to look up values in File 2 from File 1, & printingNth field of File1 based value of File2 $2

I have two files which are the output of a multiple choice vocab test (60 separate questions) from 104 people (there are some missing responses) and the question list. I have the item list in one file (File1) Item,Stimulus,Choice1,Choice2,Choice3,Choice4,Correct... (5 Replies)
Discussion started by: samonl
5 Replies

2. Shell Programming and Scripting

awk to update field using matching value in file1 and substring in field in file2

In the awk below I am trying to set/update the value of $14 in file2 in bold, using the matching NM_ in $12 or $9 in file2 with the NM_ in $2 of file1. The lengths of $9 and $12 can be variable but what is consistent is the start pattern will always be NM_ and the end pattern is always ;... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

awk to search field2 in file2 using range of fields file1 and using match to another field in file1

I am trying to use awk to find all the $2 values in file2 which is ~30MB and tab-delimited, that are between $2 and $3 in file1 which is ~2GB and tab-delimited. I have just found out that I need to use $1 and $2 and $3 from file1 and $1 and $2of file2 must match $1 of file1 and be in the range... (6 Replies)
Discussion started by: cmccabe
6 Replies

4. Shell Programming and Scripting

Replacing first field of file2 with the second filed of file1 for matching cases

Dear All, Need your help..:D I am not regular on shell scripts..:( I have 2 files.. Content of file1 cellRef 4};"4038_2_MTNL_KALAMBOLI" cellRef 1020};"4112_3_RAINBOW_BLDG" cellRef 134};"4049_2_TATA_HOSPITAL" cellRef 1003};"4242_3_HITESH_CONSTRUCTION" cellRef... (6 Replies)
Discussion started by: ailnilanjan
6 Replies

5. Shell Programming and Scripting

Retreive the records from file2 by using the first field in file1

Hi Freinds, i have a file1 as below file1 1|ndmf|fdd|d3484|34874 2|jdehf|wru7|478|w489 3|dfkj|wej|484|49894 file2 contains lakhs of records and not in sorted order i want to retrive only the records from file2 by searcing the first field of file 1 i used grep ^1 file2... (4 Replies)
Discussion started by: i150371485
4 Replies

6. UNIX for Dummies Questions & Answers

if matching strings in file1 and file2, add column from file1 to file2

I have very limited coding skills but I'm wondering if someone could help me with this. There are many threads about matching strings in two files, but I have no idea how to add a column from one file to another based on a matching string. I'm looking to match column1 in file1 to the number... (3 Replies)
Discussion started by: pathunkathunk
3 Replies

7. Shell Programming and Scripting

using field 2 in file2 to complete field 3 in file1

Hello, I was hoping someone could help me with this work related problem... basically what I want to do is the following: file2: 1 o 2 t 4 f 5 v 7 n 8 e 10 a file1: 1 : (8 Replies)
Discussion started by: smarones
8 Replies

8. Shell Programming and Scripting

AWK: Pattern match between 2 files, then compare a field in file1 as > or < field in file2

First, thanks for the help in previous posts... couldn't have gotten where I am now without it! So here is what I have, I use AWK to match $1 and $2 as 1 string in file1 to $1 and $2 as 1 string in file2. Now I'm wondering if I can extend this AWK command to incorporate the following: If $1... (4 Replies)
Discussion started by: right_coaster
4 Replies

9. Shell Programming and Scripting

Read Field from file1 and find and replace in file2

Hi All, I have file1 line below: $myName$|xxx Now I need to read the file1 and find for $myName$ in file2 and replace with xxx file1: $myName$|xxx file2: My name is $myName$ expected output in file2 after executing the script is below: my name is xxx Thanks, (8 Replies)
Discussion started by: gdevadas
8 Replies

10. Shell Programming and Scripting

Awk Compare f1,f2,f3 of File1 with f1 of File2

I have an Awk string-compare problem and have searched the internet and forums for a solution i could use but cannot find a solution i understand to make work with my particular problem: I need to compare (field1 field2 field3 of File1) against (field1 of File2) and if they match print out... (6 Replies)
Discussion started by: RacerX
6 Replies

Featured Tech Videos