Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to look up values in File 2 from File 1, & printingNth field of File1 based value of File2 $2
# 1  
Old 03-02-2018
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)

Code:
Item,Stimulus,Choice1,Choice2,Choice3,Choice4,Correct
3100004,HATCHET,tomahawk,pagoda,prosecution,soot,1
3100011,MORSEL,bite,host,marmalade,extortion,1
3100012,PASTIME,interest,miracle,position,homesick,1
3100016,GIST,essence,contempt,policy,fig,1

etc
The actual data is in another (File2)

Code:
Item,Response,TrialOrderRelative,ItemOrder,Accuracy,ID,RT
3100004,1,184,1,1,90915,2243.46
3100004,1,702,1,1,1207MY,3355.45
3100004,3,1031,1,0,120908,5832.49
3100004,1,1405,1,1,123321,2577.53
3100004,4,1963,1,0,17440293,6490.75
3100004,2,2326,1,0,200198,19480.67
3100012,4,2722,1,0,202017,2073.00

etc
All I want to do is print the answer chosen for each item in the File 2. This requires that I a) match $1 of File 2 to File 1, and b) then take the value of $2 in File 2, and print the $2+2 th field of File 1 for the matching record. I have got as far as working out that I need to start with reading both the files in as arrays at the same time but got really lost reading the array section in a 30 year old copy of Aho book from undergraduate days. I would like to learn how to do this but do not understand how to use the $2 to look up a field in File 1. I know how to do the a) part of this but that's not really helping. The output should look like this:
Code:
3100004,1,184,1,1,90915,2243.46,tomahawk
3100004,1,702,1,1,1207MY,3355.45,tomahawk
3100004,3,1031,1,0,120908,5832.49,prosecution
3100004,1,1405,1,1,123321,2577.53,tomahawk
3100004,4,1963,1,0,17440293,6490.75,soot
3100004,2,2326,1,0,200198,19480.67,pagoda
3100012,4,2722,1,0,202017,2073.00,homesick

I would be grateful for all suggestions including pointers to helpful worked examples so I can solve this kind of thing for myself. This post also represents huge frustration with attempts to do this kind of thing with Excel and its lookup functions (it hung and broke). Thank you.
# 2  
Old 03-02-2018
Read file1 into a reference multidimensional array; standard procedure and thus copious examples (for onedimensional arrays) in these fora. Then, print file2's respective lines adding the array element referenced by field 2; also numerous examples found in here.
# 3  
Old 03-04-2018
Code:
  awk -F, 'NR==FNR { for (i=3;i<=NF;i++) arr[$1,i] = ","$i; next } { print $0 arr[$1,($2+2)] }' file1 file2

Last edited by abdulbadii; 03-04-2018 at 11:51 AM..
This User Gave Thanks to abdulbadii For This Post:
Chubler_XL
# 4  
Old 03-04-2018
If you want to avoid output of the heading line add FNR<2{next} to the front of abdulbadii's program:

Code:
awk -F, 'FNR<2{next} NR==FNR { for (i=3;i<=NF;i++) arr[$1,i] = ","$i; next } { print $0 arr[$1,($2+2)] }' file1 file2

# 5  
Old 03-04-2018
Thank you both. It works, but I still don't understand where the $0 comes from in this bit
Code:
{ print $0 arr[$1, ($2+2)] }

I understand (just) how the transpose examples work in the various manual pages but as soon as there is a second file I get really really confused.

Thank you also for the suggestion about suppressing the headers - but I want the headers. You have all been really helpful. Thank you.
# 6  
Old 03-04-2018
$0 is the current row, in this case it is a row from any file except the first file, because the previous command texts NR == FNR and for every row of the first file the global row number (NR) will equal the File row number (FNR) and within this test next is called so the rest of the program is never reached.

To keep the headers in the output you need something like this:

Code:
awk -F, '
NR==FNR { for (i=3;i<=NF;i++) arr[$1,i] = ","$i; next } 
FNR==1 { print $0 ",Choice ; next }
{ print $0 arr[$1,($2+2)] }' file1 file2

explanation:
row 1: capture file #1 fields into arr[] and skip rest of program for file #1
row 2: if this is row number 1 of this file print row plus ",Choice"
row 3: otherwise print row plus arr[] value for current item and response
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

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 :). ... (4 Replies)
Discussion started by: cmccabe
4 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

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

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

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

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

8. Shell Programming and Scripting

Get values from different columns from file2 when match values of file1

Hi everyone, I have file1 and file2 comma separated both. file1 is: Header1,Header2,Header3,Header4,Header5,Header6,Header7,Header8,Header9,Header10 Code7,,,,,,,,, Code5,,,,,,,,, Code3,,,,,,,,, Code9,,,,,,,,, Code2,,,,,,,,,file2... (17 Replies)
Discussion started by: cgkmal
17 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: read values from file1; search for values in file2

I have read another post about this issue and am wondering how to adapt it to my own, much simpler, issue. I have a file of user IDs like so: 333333 321321 546465 ...etc I need to take each number and use it to print records wherein the 5th field matches the user ID pulled from the... (2 Replies)
Discussion started by: Bubnoff
2 Replies
Login or Register to Ask a Question