Hello,
I am having a little problem and was wondering if anyone had any ideas on how to best solve it.
Here is the problem:
- I have a large file 6000 rows by 2500 columns.
- First I sort the file by columns 1 and 2
- then I find that various rows in these two columns (1 and 2) have duplicate values, sometimes only twice, but sometimes three or four, or five or up to 9 times.
- this duplication occurs in only the first two columns, but we don't know in which rows and we don't know how much duplication there is. The remaining columns, i.e. column 3 to column 2500, for the corresponding rows contain data.
- Programatically, I would like to find the duplicated rows by searching columns 1 and 2 and when I find them, average the respective data for these rows in columns 3 to 2500.
- So, once this is done I want to save the averaged data to file. In each row this file should have the name of colunm 1 and 2 and the averaged row values for columns 3 to 2500. So the file will have n rows by 2500 columns, where n will depend on how many duplicated rows there are in the original file.
I hope that this makes sense. I have outlined the problem in a simple example below:
In the example below we have two duplicates in rows 1 and 2 and four duplicates in rows 5 to 8.
Example input file:
Col1 Col2 Col3 ... Col2500
3 4 0.2 ... 0.5
3 4 0.4 ... 0.8
8 5 0.1 ... 0.4
7 9 0.7 ... 0.9
2 8 0.1 ... 0.5
2 8 0.5 ... 0.8
2 8 0.3 ... 0.2
2 8 0.6 ... 0.7
6 9 0.9 ... 0.1
...
So, based on the above example, the first two rows need averaging (two duplicates) as do rows 5 to 8 (four duplicates). The output file should look like this:
Col1 Col2 Col3 ... Col2500
3 4 0.3 ... 0.65
8 5 0.1 ... 0.4
7 9 0.7 ... 0.9
2 8 0.375 ... 0.55
6 9 0.9 ... 0.1
...
