awk to update file with sum of matching fields in another file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to update file with sum of matching fields in another file
# 1  
Old 02-20-2018
awk to update file with sum of matching fields in another file

In the awk below I am trying to add a penalty to a score to each matching $1 in file2 based on the sum of $3+$4 (variable TL) from file1. Then the $4 value in file1 is divided by TL and multiplied by 100 (this valvue is variable S). Finally, $2 in file2 - S gives the updated $2 result in file2. Since math is not my strong suit there probaly is a better way of doing this, but this is what I could think off, however it returnsa syntax error (FILENAME=file2 FNR=1) fatal: attempt to use array TL' in a scalar context Thank you Smilie.

file1 space delimited
Code:
ACP5 4 1058 0
ACTB5 10 1708 79
ORAI1 2 952 0
TBX1 9 1932 300

file2 tab delimited
Code:
ACP5     100.00
ACTB     100.00
ORAI1    94.01
TBX1     77.23

desired output tab-delimited the --- is an example calculation and not part of the output
Code:
ACP5    100.00
ACTB    89.59   ---- $3+$4=1787 this is TL (comes from file1), $4/TL*100 is 4.42, $2 in file2 is 100 - 4.42 = 95.58 ----
ORAI1   94.01
TBX1    63.79

awk
Code:
awk '
FNR==NR{  # process each line
  TL[$1]=($3+$4);next} ($1 in TL)  # from file1 store sum of $3 and $4 in TL
    {S=(P[$4]/TL)*100;printf("%s\t %.2f\n",$1,  $2-S)  # store $4/TL from file1 in S and subtract S from $2 in file2, output two decimal places
      }1' OFS="\t" file1 FS="\t" file2  # update and define input


Last edited by cmccabe; 02-20-2018 at 08:56 PM.. Reason: fixed format and typo
# 2  
Old 02-20-2018
Few issues with your code:

1. P[] is not assigned.
2. Poorly formatted line with comment between condition and { action }
3. T[] referenced without an index
4. P[] referenced with wrong field for index
5. next required to avoid output twice for 1 record


try:

Code:
awk '
FNR==NR{  # process each line
  P[$1]=$4;TL[$1]=($3+$4);next} 
# from file1 store sum of $3 and $4 in TL
($1 in TL) {
S=(P[$1]/TL[$1])*100;printf("%s\t%.2f\n",$1,  $2-S);next  # store $4/TL from file1 in S and subtract S from $2 in file2, output two decimal places
      }1' OFS="\t" file1 FS="\t" file2  # update and define input

or
Code:
awk '
# from file1 store sum of $3 and $4 in A and B
FNR==NR{
  A[$1]=$3; B[$1]=$4; next
}
($1 in A) {
  # Recalculate field #2 to two decimal places from the file1 fields stored above
  $2=sprintf("%.2f", $2 - B[$1] / (A[$1] + B[$1]) * 100)
}1' OFS='\t' file1 FS='\t' file2


Last edited by Chubler_XL; 02-20-2018 at 10:14 PM..
This User Gave Thanks to Chubler_XL For This Post:
# 3  
Old 02-22-2018
Thank you very much Smilie.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk to update file with partial matching line in another file and append text

In the awk below I am trying to cp and paste each matching line in f2 to $3 in f1 if $2 of f1 is in the line in f2 somewhere. There will always be a match (usually more then 1) and my actual data is much larger (several hundreds of lines) in both f1 and f2. When the line in f2 is pasted to $3 in... (4 Replies)
Discussion started by: cmccabe
4 Replies

2. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies

3. Shell Programming and Scripting

awk to update file based on match in 3 fields

Trying to use awk to store the value of $5 in file1 in array x. That array x is then used to search $4 of file1 to find aa match (I use x to skip the header in file1). Since $4 can have multiple strings in it seperated by a , (comma), I split them and iterate througn each split looking for a match.... (2 Replies)
Discussion started by: cmccabe
2 Replies

4. Shell Programming and Scripting

Speed : awk command to count the occurrences of fields from one file present in the other file

Hi, file1.txt AAA BBB CCC DDD file2.txt abc|AAA|AAAabcbcs|fnwufnq bca|nwruqf|AAA|fwfwwefwef fmimwe|BBB|fnqwufw|wufbqw wcdbi|CCC|wefnwin|wfwwf DDD|wabvfav|wqef|fwbwqfwfe i need the count of rows of file1.txt present in the file2.txt required output: AAA 2 (10 Replies)
Discussion started by: mdkm
10 Replies

5. Shell Programming and Scripting

Awk: adding fields after matching $1

Dear AWK-experts! I did get stuck in the task of combining files after matching fields, so I'm still awkward with learning AWK. There are 2 files: one containing 3 columns with ID, coding status, and score for long noncoding RNAs: file1 (1.txt) (>5000 lines) ... (12 Replies)
Discussion started by: kben
12 Replies

6. Shell Programming and Scripting

How to find sum of any 'n' number of values from file matching target value?

I have a simple text file having payment amount value on each line. At the end of day 'n' number of payments created difference in amount that I need to match from this file. I have information about how many payments created difference and difference amount. Please help me to build shell... (3 Replies)
Discussion started by: swats007
3 Replies

7. Shell Programming and Scripting

Sum fields of different files using awk

I'm trying to sum each field of the second column over many different files. For example: file1: file2: 1 5 1 5 2 6 2 4 3 5 3 3 To get: file3 1 10 2 10 3 8 I found answer when there are only 2 files as... (10 Replies)
Discussion started by: rogeriog.em
10 Replies

8. Shell Programming and Scripting

awk - writing matching pattern to a new file and deleting it from the current file

Hello , I have comma delimited file with over 20 fileds that i need to do some validations on. I have to check if certain fields are null and then write the line containing the null field into a new file and then delete the line from the current file. Can someone tell me how i could go... (2 Replies)
Discussion started by: goddevil
2 Replies

9. UNIX for Dummies Questions & Answers

sum of all matching rows using awk

I have file "1","x1897"," 89.10" "1","x2232"," -12.12" "1","x1897"," 389.10" "1","x2232"," 212.12" "1","x1897"," 19.10" "1","x2232"," 2.12" i want to add all 3 rd column rows (they have spaces also)for x1 and sum of 3rd column rows for x2 separately. I am very... (8 Replies)
Discussion started by: i.scientist
8 Replies

10. Shell Programming and Scripting

calculating sum of fields in a file

Hey, I have a file and it has only one field. I need to calculate the sum of each filed as total. For e.g my file is 1 2 3 4 5 I need to calculate the total sum as 15. Please let me know how i can do it? (4 Replies)
Discussion started by: dsravan
4 Replies
Login or Register to Ask a Question