Division of columns from two files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Division of columns from two files
# 1  
Old 07-07-2013
Division of columns from two files

I have two text files T1 and T2

T1 looks as follows

Code:
Name         All                        Maths               Science             English                 Social   
Anie         48/18  =   2.7        44/17  =   2.6         20/6   =   3.3        24/11  =   2.2            NA  
Edwin        134/41  =   3.3        96/30  =   3.2        34/10  =   3.4        50/18  =   2.8        12/2   =   6.0
Andrew       14/9   =   1.6        14/9   =   1.6         6/3   =   2.0         8/6   =    1.3             NA

T2 looks as follows

Code:
Name         All                        Maths               Science             English                 Social 
Anie         28/19  =   1.5        26/18  =   1.4         5/4   =   1.2         21/14  =   1.5             NA  
Edwin         64/16  =   4.0        43/12  =   3.6        28/6   =   4.7        15/6   =   2.5            18/3 =  6.0
Andrew        118/41  =   2.9       110/39  =   2.8        61/17  =   3.6        49/22  =  2.2            NA

I would like to divide the answers of each column as shown below.


output

Code:
Name         All                        Maths               Science             English                 Social 
Anie         2.7/1.5 = 1.8            2.6/1.4 = 1.86      3.3/1.2 = 2.75      2.2/1.5 = 1.45             NA
Edwin        3.3/4.0 = 0.83           3.2/3.6 = 0.89      3.4/4.7 = 0.72      2.8/2.5 = 1.12            6.0/6.0 = 1.0
Andrew       1.6/2.9 = 0.55           1.6/2.8 = 0.57      2.0/3.6 = 0.56      1.3/2.2 = 0.59             NA


Last edited by Scrutinizer; 07-07-2013 at 01:15 PM.. Reason: code tags
# 2  
Old 07-07-2013
Hi, what have you tried so far? What OS and Version are you using? What utility or shell were you planning on using? What field separator is used in the input file?
# 3  
Old 07-07-2013
Thank you for your comment. My OS is ubuntu12.04. The input file field separator is space.
# 4  
Old 07-07-2013
If the files T1 and T2 are in the same order,
awk in its normal loop for each line can get the corresponding line from the other file.
Code:
awk '
{
# got line from the input file
# store the needed values in s[]
for (i=4;i<=NF;i+=3) s[i]=$i
}
{
# get line from the 2nd input file
getline <f2
}
NR==1 {
# header line
print; next
}
{
# print name
print $1
# print the needed values, from s[] and current line
 for (j=4;j<=NF;j+=3) print s[j],$j
 print ""
}' f2=T2 T1

Still to be done: formatting the output.

Last edited by MadeInGermany; 07-07-2013 at 03:06 PM.. Reason: missing $
This User Gave Thanks to MadeInGermany For This Post:
# 5  
Old 07-07-2013
The following seems to work even if the names in T1 and T2 are not in the same order, and if NA can be an entry in fields other than the last field. If will also print NA if a value in T2 is 0 (instead of trying to divide by 0 in this case). However, it differs from the requested output in the following ways:
  1. Output in each field is aligned with the field's heading.
  2. Each numeric result is printed with two decimal places (even if the last digit is "0").
  3. The result of dividing 3.3 by 4.0 is rounded up from 0.825 to 0.83 instead of down to 0.82.
Code:
awk '
BEGIN { ffmt = "%s/%s = %.2f"                           # field format string
        mfmt = "%-12s %-26s %-19s %-17s %-25s %-s\n"    # main format string
}
function calc(numerator, denominator) {
        if(numerator == "NA" || denominator == "NA" || denominator == 0)
                return "NA"
        # You could check if the last character in the string produced
        # below is a "0" and remove it before returning the result if
        # not having a trailing zero is important.
        return sprintf(ffmt, numerator, denominator, numerator / denominator)
} 
FNR == 1 {
        # Print the header from one of the input files.
        if(NR == 1) print
        next
}
FNR == NR {
        # Get denominators for each field for a given name.  A data
        # field is in one of the following two formats:
        #               NA
        # or    num/den = val
        # so awk will see one field if the field contains NA and will
        # see three fields otherwise.
        f = 1
        for(i = 2; i <= NF; f++)
                if($i == "NA") {
                        d[$1, f] = $i
                        i++
                } else {d[$1, f] = $(i + 2)
                        i += 3
                }
        next 
}
{       # Get numerators for this line.
        f = 1
        for(i = 2; i <= NF; f++)
                if($i == "NA") {
                        n[f] = $i
                        i++
                } else {n[f] = $(i + 2)
                        i += 3
                }
        # Calculate and print results for the name on this line.
        printf(mfmt, $1, calc(n[1], d[$1, 1]), calc(n[2], d[$1, 2]),
                calc(n[3], d[$1, 3]), calc(n[4], d[$1, 4]),
                calc(n[5], d[$1, 5]))
}' T2 T1 > output

Note that the order of input files was chosen such that the order of output lines would match the order of input lines in T1 in case T1 and T2 lines are not in the same order.

If you are running this on a Solaris system, use /usr/xpg4/bin/awk or /usr/xpg6/bin/awk instead of using /bin/awk or /usr/bin/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

How to use "awk" to print columns from different files in separate columns?

Hi, I'm trying to copy and paste the sixth column from a bunch of files into a single file having each column pasted in separate columns (and not one after each other in just one column.) I tried this code but works only partially because it copied and pasted 50 rows of each column... (6 Replies)
Discussion started by: Frastra
6 Replies

2. Shell Programming and Scripting

Adding columns from 2 files with variable number of columns

I have two files, file1 and file2 who have identical number of rows and columns. However, the script is supposed to be used for for different files and I cannot know the format in advance. Also, the number of columns changes within the file, some rows have more and some less columns (they are... (13 Replies)
Discussion started by: maya3
13 Replies

3. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

4. UNIX for Dummies Questions & Answers

Division of wc output

I have a function that outputs 3 lines for each result and I want to know how many results there are. so for example function | wc -l 24 but I want to see the result 8. so is there a easy way to divide the result? (5 Replies)
Discussion started by: yatici
5 Replies

5. Shell Programming and Scripting

Combine columns from many files but keep them aligned in columns-shorter left column issue

Hello everyone, I searched the forum looking for answers to this but I could not pinpoint exactly what I need as I keep having trouble. I have many files each having two columns and hundreds of rows. first column is a string (can have many words) and the second column is a number.The files are... (5 Replies)
Discussion started by: isildur1234
5 Replies

6. UNIX for Dummies Questions & Answers

Help in division

hi, The below commands result only the whole number(not giving the decimal values). pandeeswaran@ubuntu:~$ echo 1,2,3,4|sed 's/,/\//g'|bc 0 pandeeswaran@ubuntu:~$ echo 1000,2,3|sed 's/,/\//g'|bc 166 How to make it to return the decimal values? Thanks (5 Replies)
Discussion started by: pandeesh
5 Replies

7. Shell Programming and Scripting

division by zero

Hello, I am searching for a way to calculate for example 10/100 within a shellscript and the result should be 0.1 and not just 0. Every alternative i tried just results 0 Thank you in advance 2retti (6 Replies)
Discussion started by: 2retti
6 Replies

8. Shell Programming and Scripting

How to compare 2 files & get only few columns based on a condition related to both files?

Hiiiii friends I have 2 files which contains huge data & few lines of it are as shown below File1: b.dat(which has 21 columns) SSR 1976 8 12 13 10 44.00 39.0700 70.7800 7.0 0 0.00 0 2.78 0.00 0.00 0 0.00 2.78 0 NULL ISC 1976 8 12 22 32 37.39 36.2942 70.7338... (6 Replies)
Discussion started by: reva
6 Replies

9. Shell Programming and Scripting

division by 0 error

Hi, I am writing a script that among other things will be checking for various files on mount points. One of the conditions is that unless the server has failed over the df command will show root ( / ). If when checking the files the script comes across /, I want it to skip it, otherwise to... (2 Replies)
Discussion started by: cat55
2 Replies

10. Shell Programming and Scripting

division problem

how can i show the value when i divide a number where the dividend is greater then the divisor. for example... 3 divided by 15 ---> let x=3/15 when i do this in the shell environment it gives me an output of 0. please help me. thanks. (3 Replies)
Discussion started by: inquirer
3 Replies
Login or Register to Ask a Question

Featured Tech Videos