I need to multiply the the value in row1 & column 3 of File2 to the values in Column6 and onwards ( MI1_A .... MI45_A ) of File1 all the way down till the last row, ignoring every alternate column( MI1_H .. MI45_H ).
In the output file I need
1) Column "ID2" from file1 : the unique record id
2)Columns ending with "_A": The values of these columns need to be multiplied with the 3rd column of file 2.
3) Column id of file 1 (starting from column 6th) matches with the row id (except for "_A") of file2.
4) From 6th column onward in file1, every alternate column is to be used for the multiplication (6th, 8th, 10th and so on)
Hope that helps to better understand the problem!
In File1 we are using data from columns with headings:
ID2 M11_A M12_A
and multiplying values in those columns with data from rows with the data in the 1st two columns containing the data:
M11 A
M12 B
and producing output with the headings:
ID2 M1_A M2_A
Why are all three of these sets of names different? I can understand if you're saying we should completely ignore the 1st two columns in File2 , but I'm not sure that I understand the transformation that you want to happen when moving headings from File1 to your output file. Is it just that 2nd character of each of the headings (except ID2 ) copied from File1 are to be deleted when printing the headings in the output file? Why are the headings for the fields used in File1 copied to the output file unchanged (like RudiC did in his suggestion)?
@Don Cragun
In #Post5 i've tried to explain the files and what output i'm looking for. It is a bit confusing though, ill try to explain here #File1:Headings from column6 onwards contain either "_A" or "_H" in the end. I only want to use the columns with "_A" for the desired multiplication. ID2 is the records id here and all other columns before column6 are not important. #File2: Record id is the same as column id from column 6 onwards in File1 except for both "_A" and "_H".
Hope that helps to better understand the problem!
---------- Post updated at 09:06 AM ---------- Previous update was at 08:55 AM ----------
@RudiC: There is no output at all. Could you please explain your code a bit?
---------- Post updated at 09:08 AM ---------- Previous update was at 09:06 AM ----------
@RudiC: There is no output at all. Could you please explain your code a bit?
There is, as you can see, in the three lines under the code.
The FNR == NR part collects the factors from file2 into an array T, indexed by the field1 value suffixed by the parameter variable SFX.
Then, for every line in file1, the desired column is printed, identified by parameter variable COL.
For file1's first line, the to be selected columns are collected in array C, and the factors fromfile2 in array F, both indexed by a counter CNT. Also, the header fields are printed.
Then, for every input line, the desired fields, eventually multiplied by the respective factors, are printed.
Both code snippets provided work on the two files presented as samples in post#1 if run as shown. I don't think there are any version specific features used, so I can't imagine the scripts to fail unless
you didn't run them as indicated (using the correct actual filenames!)
If you have a file named code.awk containing a shell script that invokes an awk command, then you need to use a shell to run that script; not awk . And, the file you give to the shell to be run needs to be the file that contains the script you want to run.
You haven't told us what operating system or shell you're using... So making some wild guesses. If you really saved the shell script RudiC suggested (as provided in post #8 in this thread and duplicated here:
awk '
FNR == NR {T[$1 SFX] = $3
next
}
{printf "%s", $COL
}
FNR == 1 {for (i=COL+1; i<=NF; i++) {if ($i in T) {C[++CNT] = i
F[CNT] = T[$i]
printf " %s", $i
}
}
printf RS
next
}
{for (i=1; i<=CNT; i++) printf "%s%s", FS, $C * F
print ""
}
' COL=2 SFX="_A" file2 file1
in a file named code.awk , you would execute that script with something like:
sh code.awk
If you are trying this on a Solaris/SunOS system, you will need to change awk on the first line in code.awk to /usr/xpg4/bin/awk or nawk before you execute the above command.
