Calculate percent using values in 2 files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Calculate percent using values in 2 files
# 1  
Old 06-09-2016
Calculate percent using values in 2 files

Trying to use file1 which is the actual counts in $2 associated with each $1 entry. The total of each $1 is in file2 with the total in $3. So when there is a match between $1 in file1 with $1 in file2, then the % is calculated using the $2 value of file1 and $3 value of file2. Thank you Smilie.

file1
Code:
AAA 1000
BBB 0
CCC 400
DDD 0
EEE 50

file2
Code:
AAA 2 1000
BBB 5 200
CCC 1 400
DDD 2 800
EEE 3 500

desired output
Code:
AAA 100.0%
BBB 0.0%
CCC 100.0%
DDD 0.0%
EEE 10.0%

awk with current output
Code:
awk 'FNR==NR{A[$1]=$2;next} ($1 in A){X=(A[$1]/$3)*100;printf("%s %.2f\n",$1,  100-X)}' file1 file2
AAA 0.00
BBB 100.00
CCC 0.00
DDD 100.00
EEE 90.00

# 2  
Old 06-09-2016
change 100-X to X and you're done.

Or use this(if both files are sorted by the key field):

Code:
join file1 file2 | awk '{ printf "%3s %6.2f %\n",$1,$2/$4*100 }'

if the files are unsorted, you have to sort them first:

Code:
sort file1 -o file1
sort file2 | join file1 - | awk '{ printf "%3s %6.2f %\n",$1,$2/$4*100 }'


Last edited by stomp; 06-09-2016 at 11:54 PM..
This User Gave Thanks to stomp For This Post:
# 3  
Old 06-10-2016
A little modification to yours will do:
Code:
awk 'FNR==NR{A[$1]=$2;next} ($1 in A){X=(A[$1]/$3)*100;printf("%s %.1f%\n",$1, X)}' file1 file2

Or even this:
Code:
awk 'FNR==NR{A[$1]=$2;next} $1 in A{printf "%s %.1f%\n",$1, A[$1]/$3*100}' file1 file2

This User Gave Thanks to Aia For This Post:
# 4  
Old 06-11-2016
Thank you very much Smilie.
# 5  
Old 06-11-2016
The standard way to print a percent sign character in a printf format string (in C, in the printf utility, and in awk) is with %%. Some versions of awk will let you get by with:
Code:
awk 'FNR==NR{A[$1]=$2;next} $1 in A{printf "%s %.1f%\n",$1, A[$1]/$3*100}' file1 file2

Others will give you a diagnostic similar to:
Code:
awk: weird printf conversion %

 input record number 1, file2 
 source line number 1
awk: not enough args in printf(%.1f%
)
 input record number 1, file2 
 source line number 1

Code:
awk 'FNR==NR{A[$1]=$2;next} $1 in A{printf "%s %.1f%%\n",$1, A[$1]/$3*100}' file1 file2

should work with any awk.
This User Gave Thanks to Don Cragun For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Calculate average from a given set of keys and values

Hello, I am writing a script which expects as its input a hash with student names as the keys and marks as the values. The script then returns array of average marks for student scored 60-70, 70-80, and over 90. Output expected 50-70 1 70-90 3 over 90 0 The test script so far... (4 Replies)
Discussion started by: nans
4 Replies

2. Shell Programming and Scripting

awk to calculate total and percent off field in file

Trying to use awk to print the lines in file that have either REF or SNV in $3, add a header line, sort by $4 in numerical order. The below code does that already, but where I am stuck is on the last part where the total lines are counted and printed under Total_Targets, under Targets_less_than is... (4 Replies)
Discussion started by: cmccabe
4 Replies

3. Shell Programming and Scripting

How to calculate avg values of csv file using shell scripting .?

hi all i have a reporting work and i want it to be automated using shell scripting kindly let me know how can i make that possibe . eg data are :... (2 Replies)
Discussion started by: Avinash shaw
2 Replies

4. Shell Programming and Scripting

Match first two columns and calculate percent of average in third column

I have the need to match the first two columns and when they match, calculate the percent of average for the third columns. The following awk script does not give me the expected results. awk 'NR==FNR {T=$3; next} $1,$2 in T {P=T/$3*100; printf "%s %s %.0f\n", $1, $2, (P>=0)?P:-P}' diff.file... (1 Reply)
Discussion started by: ncwxpanther
1 Replies

5. Shell Programming and Scripting

Calculate average of top n% of values - UNIX

Hey guys, I have several huge tab delimited files which look like this: a 1 20 a 3 15 a 5 10 b 2 15 b 6 10 c 3 23 what I am interested is to calculate the average of top n% of data in third column. So for example for this file the top 50% values are: 23 20 (Please note that it... (11 Replies)
Discussion started by: @man
11 Replies

6. UNIX for Dummies Questions & Answers

How to calculate umask values?

Hi, I was trying to understand how to calculate umask value but couldnt get the right way to calculate it. can some one please give me a small formula or easy method to do it? Thanks, Waseem (4 Replies)
Discussion started by: ahmedwaseem2000
4 Replies

7. Shell Programming and Scripting

Calculate difference between two successive values

Hi, I have a file containing timestamps (at micro-seconds granularity). It looks like the following: 06:49:42.383818 06:49:42.390190 06:49:42.392308 06:49:42.392712 06:49:42.393437 06:49:42.393960 06:49:42.402115 Now I need a sed/awk script to take the difference of two successive... (2 Replies)
Discussion started by: sajal.bhatia
2 Replies

8. UNIX for Advanced & Expert Users

calculate logical layout values for hdd

Please advice me how to calculate logical layout values in hdd from below table. Physical Layout ----------------- Bytes per Sector : 512 Sectors per Track : 480-1272 Number of Heads : 4 Number of Disks : 2 Logical Layout --------------- Number of Heads : 16 Number of Sectors /... (9 Replies)
Discussion started by: presul
9 Replies

9. Shell Programming and Scripting

How to calculate the percentage for the values in column

Hi, I am having the file which contains the following two columns. 518 _factorial 256 _main 73 _atol 52 ___do_global_ctors 170 ___main 52 ___do_g How can calculate the percentage of each value in the first column ? first need to get the sum of the first column and... (3 Replies)
Discussion started by: saleru_raja
3 Replies

10. Shell Programming and Scripting

read from a file and calculate values for a specified field

hi guys im running into a problem here im trying to calculate a sum a values from a field for example a hava a file caled <filename> and it has $3 fields, and i want toextract a group of values from field &1 and compute the sum of values from $3 accordingly this is my code so far... awk... (3 Replies)
Discussion started by: lucho_1
3 Replies
Login or Register to Ask a Question