Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk comparision between 2 files and substitution in third

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk comparision between 2 files and substitution in third
# 1  
Old 04-01-2013
awk comparision between 2 files and substitution in third
Hi All,
I have two files in the following format.

File 1 :
Code:
1044|1|20121031|2910039.4|MR|201210|G1044|E
1082|2|20121031|1664662.84|MR|201210|G1082|E
1696|3|20121031|190801.5|MR|201210|G1696|E
1824|4|20121031|196350|MR|201210|G1824|E
1900|5|20121031|221447.8|MR|201210|G1900|E

File 2 :
Code:
S00025106|1044|0|9.6|0|0| |0|0|0|0|0| |0|163.5|0|0|
S00026461|1082|0|-6.1|0|0| |0|0|0|0|0| |0|204|0|0|
S00026462|1696|0|-6.1|0|0| |0|0|0|0|0| |0|102|0|0|
S00029844|1824|0|2.2|0|0| |0|0|0|0|0| |0|123.1|0|0|
S00030343|1900|0|2.2|0|0| |0|0|0|0|0| |0|156.1|0|0|

Output desired :
File 3 :
Code:
S00025106|1044|0|9.6|0|1| |0|0|0|0|0| |20121231|163.5|0|0|
S00026461|1082|0|-6.1|0|2| |0|0|0|0|0| |20121231|204|0|0|
S00026462|1696|0|-6.1|0|3| |0|0|0|0|0| |20121231|102|0|0|
S00029844|1824|0|2.2|0|4| |0|0|0|0|0| |20121231|123.1|0|0|
S00030343|1900|0|2.2|0|5| |0|0|0|0|0| |20121231|156.1|0|0|

Basically I want to compare column 1 from File 1 and column2 from File 2. If they match , I want to replace column 6 and column 14 of File 2 with Coulmn 2 and Column3 of File1 giving File3,

Any help is really appreciated.
This User Gave Thanks to nua7 For This Post:
i150371485
# 2  
Old 04-01-2013
awk
Hey,

Try sth like this,

Code:
awk -F'\|' 'FNR==NR{a[$1]= $2"^"$3;next;}a[$2]{split(a[$2],b,"^");$6=b[1];$14=b[2];}1' OFS="|" file1 file2

Cheers,
RangaSmilie
This User Gave Thanks to rangarasan For This Post:
i150371485
# 3  
Old 04-01-2013
Small variation with two single arrays:
Code:
awk 'NR==FNR{A[$1]=$3; B[$1]=$2; next} $2 in A{$6=B[$2]; $14=A[$2]}1' FS=\| OFS=\| file1 file2

These 2 Users Gave Thanks to Scrutinizer For This Post:
i150371485 RavinderSingh13
# 4  
Old 04-02-2013
An approach using join
Code:
join -t"|" -1 2 -2 1 -o 1.1 1.2 1.3 1.4 1.5 2.2 1.7 1.8 1.9 1.10 1.11 1.12 1.13 2.3 1.15 1.16 1.17 1.18 file2 file1

This User Gave Thanks to Yoda For This Post:
i150371485
# 5  
Old 04-02-2013
@Scrutinizer :Could you please explain command used using the AWK associate arrays here . I am new bie to awk arrays.Plz help
# 6  
Old 04-02-2013
Sure:

Code:
awk '
  NR==FNR{                       # When the first file is being read (only then are FNR and NR equal) 
    A[$1]=$3                     # create an (associative) element in array A with the first field as the index and the 3rd field as value
    B[$1]=$2                     # create an (associative) element in array B with the first field as the index and the 2rd field as value
    next                         # start reading the next record (line)
  } 
  $2 in A{                       # while reading the second file, if field 2 is present in array A (we could also have chosen B)
    $6=B[$2]                     # then set field 6 to the element in array B with the second field as the index
    $14=A[$2]                    # and set field 14 to the element in array A with the second field as the index
  }
  1                              # print the record (line)
' FS=\| OFS=\| file1 file2       # Set the input and output field separator to the pipe symbol and read file 1 and then file 2

Hope this helps...
This User Gave Thanks to Scrutinizer For This Post:
i150371485
# 7  
Old 04-03-2013
@Scrutinizer : Thank you so much .. I got clear idea why we use NR=FNR. Thanks once again .
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Comparision of two text files

Dear all, I am having two files big files i need an output file as first occurance of file1 field in file2 example: file1:raju ranifile2:raju|123 raju|879 rani|623 rani|253result:raju|123 rani|623pls help me in this regard (3 Replies)
Discussion started by: suryanarayana
3 Replies

2. Shell Programming and Scripting

Awk comparision

Hi Everyone I am new to Unix shell scripting Can anyone please explain me in detail how this command works awk -F@ 'NR==FNR{A=$2;next}$3 in A{$3=A}1' file2 file1 The above command I got it from this forum, but unable to implement it as I am not getting how this works:mad: I... (3 Replies)
Discussion started by: Vijay90
3 Replies

3. Shell Programming and Scripting

Comparision of fields in 2 files.

Hi Experts, I have two huge files in the format as shown below.I need to open a file1 and file 2 , cut first 24 characters of file 1 and search if the key exists in file 2 first field (delimted by *). If the value exists , copy the third field from file 2 and replace the 5th field in file 1 .... (4 Replies)
Discussion started by: nua7
4 Replies

4. Shell Programming and Scripting

awk: string followed by tab comparision

Hi all, Currently i am using if( $0~/ NOT / && $0~/ NULL /) { ................. } to check if the input record contains "NOT" and "NULL". But in some cases "NOT" and "NULL" are preceded and followed by tab. How do i find compare for these fields as well? (3 Replies)
Discussion started by: ysvsr1
3 Replies

5. Shell Programming and Scripting

File comparision with AWK / SED

Hi all I need to compare two separate product lists that are changed weekly. New products are added, old products are removed and prices change. I have found various Windows programs that do this function but it's not as clean as I like and just wondered if there was a simpler way with... (1 Reply)
Discussion started by: mrpugster
1 Replies

6. UNIX for Advanced & Expert Users

Comparision of two files.

File Structure file1.txt.arch 029429288,1,,,02087400376,N,02087400376,N,0,02087400376,N,0,0,8010,08000151736,U,N,,08000151736,U,20100726111237,20100726111237,0,20100726111651,00004140,16,16,10,N;... (1 Reply)
Discussion started by: ravigupta2u
1 Replies

7. Shell Programming and Scripting

Range of records using comparision \awk

Hi Gurus, I have to fetch the records from a logs as per the time stamp . I am comfortable to use awk and sed in the script . But the logic to fetch the records as per comparison is the problem. $cat my_log.log <Jul 30, 2010 7:01:12 AM EEST> <Error> <WebLogicServer> <Jul 30, 2010 8:04:12 AM... (3 Replies)
Discussion started by: posix
3 Replies

8. Shell Programming and Scripting

Column comparision in two files

Hi, I need to compare a column in two different csv files file1 xyz.com,2/2/12,a,b,c eg.com,2/2/23,a,b,ga file2 1,2,ua,xyz.com 1,2,ua,abc.com 1,2,ua,eg.com 1,2,ua,easg.com 1,2,ua,zth.com Read all entries in file1(which has 1000+) and compare column1 of file1 with the column4... (13 Replies)
Discussion started by: nuthalapati
13 Replies

9. Shell Programming and Scripting

comparision of string in various files

i want to take position 19-24(only first line) from all files and need to compare any duplication is there or not. If duplication, then i have to print the file names. I have written to take the characters from 19-24 from all files. but how to compare ? ... (1 Reply)
Discussion started by: senthil_is
1 Replies

10. UNIX for Dummies Questions & Answers

Number comparision in AWK

Hi, I have a file like this. "2006","10",25,"U","1129","32","C",0,0,0,0,0,0,0,0,0,0,0,0,352,16,4,0,0,0,0,0,"80",,1 "2006","11",25,"U","1148","32","C",0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"80",,2 "2006","14",25,"U","1149","10","C",0,0,0,0,0,0,0,0,0,0,0,0,560,12,0,0,0,0,0,0,"80",,3... (1 Reply)
Discussion started by: vskr72
1 Replies
Login or Register to Ask a Question