Multiplication of two files

February 21, 2016 at 18:11:49
Specs: Windows 7
Multiplication of two files
Hi there! I have two files like below
File1 (with a header, ~1000 rows, ~50 columns)

ID1 ID2 ID3 ID4 ID5 MI1_A MI1_H MI2_A MI2_H
0 1 0 0 0 1 0 2 1
0 2 0 0 0 2 1 0 1

File2 (without a header, ~50 rows)

MI1 A 0.4 3.1
MI2 B -0.2 0.1

Output

ID2 M1_A M2_A
1 1*0.4 2*-0.2
2 2*0.4 0*-0.2

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 ). I am looking for awk or linux solution

message edited by adi27


See More: Multiplication of two files

Report •

#1
February 22, 2016 at 21:57:52
Sorry, but I just don't understand what you want. Maybe I am just getting old. Maybe you can try explaining it again.

Report •

#2
February 22, 2016 at 22:00:39
@nails
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)

#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".

message edited by adi27


Report •

#3
February 25, 2016 at 08:01:04
Sorry, but I still don't totally get it. File1 has 1000 rows and the other File2 has 50 rows. It's still not clear if you expect only 50 rows for output? Does File2 have 50 columns?

Also, to solve this problem, File2 has to be read into memory and File1 will then be processed a row at a time. I am not certain that awk will work with that many columns. perl would probably be a better choice.


Report •

Related Solutions

#4
February 25, 2016 at 15:14:33
Column id of file 1 starting from col6( every alternate id, col 6, col 8, col 10....) in file 1 matches the row id of file 2. So if file 2 has 50 rows , file1 has 2(50)+ 6 extra columns= 106 columns.

Report •

Ask Question