awk to update file with sum of matching fields in another file Post: 303013397

Sponsored Content

Top Forums Shell Programming and Scripting awk to update file with sum of matching fields in another file Post 303013397 by Chubler_XL on Tuesday 20th of February 2018 09:01:16 PM

02-20-2018

Moderator

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

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:

Chubler_XL

View Public Profile for Chubler_XL

Find all posts by Chubler_XL

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

7. Shell Programming and Scripting

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

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

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

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

LEARN ABOUT REDHAT

igawk

IGAWK(1)							 Utility Commands							  IGAWK(1)

NAME

       igawk - gawk with include files

SYNOPSIS

       igawk [ all gawk options ] -f program-file [ -- ] file ...
       igawk [ all gawk options ] [ -- ] program-text file ...

DESCRIPTION

       Igawk is a simple shell script that adds the ability to have ``include files'' to gawk(1).

       AWK programs for igawk are the same as for gawk, except that, in addition, you may have lines like

	      @include getopt.awk

       in your program to include the file getopt.awk from either the current directory or one of the other directories in the search path.

OPTIONS

       See gawk(1) for a full description of the AWK language and the options that gawk supports.

EXAMPLES

       cat << EOF > test.awk
       @include getopt.awk

       BEGIN {
	    while (getopt(ARGC, ARGV, "am:q") != -1)
		 ...
       }
       EOF

       igawk -f test.awk

SEE ALSO

       gawk(1)

       Effective AWK Programming, Edition 1.0, published by the Free Software Foundation, 1995.

AUTHOR

       Arnold Robbins (arnold@skeeve.com).

Free Software Foundation					    Nov 3 1999								  IGAWK(1)