3

I have a file (file1.txt) with the contents as below.

8145216 3377090 1.5
1405541 53595498 1.53637

I need to generate all the possible combinations for the first 2 columns in the above file. I use a php program as discussed in this answer to generate all the combinations.

After finding out the combinations, I have my file as below.

3377090 8145216
1405541 8145216
1405541 3377090
53595498 8145216
53595498 3377090
53595498 1405541

In the above file, I need to append the 3rd column values from file1.txt and if the value is not present in the file1.txt I need to append 0 as the 3rd column. The final output that am trying to get is,

3377090 8145216 1.5
1405541 8145216 0
1405541 3377090 0
53595498 8145216 0
53595498 3377090 0
53595498 1405541 1.53637
2
awk 'NR==FNR{a[$1>=$2?$1SUBSEP$2:$2SUBSEP$1]=$3;next};
{k=$1>=$2?$1SUBSEP$2:$2SUBSEP$1; print $0, k in a?a[k]:0}' file1.txt file2.txt
3377090 8145216 1.5
1405541 8145216 0
1405541 3377090 0
53595498 8145216 0
53595498 3377090 0
53595498 1405541 1.53637
  • Thanks for providing a solution. I will check this answer also as soon as I get into the server. :) – Ramesh Apr 11 '14 at 1:13
2

Personally, I would just do the whole thing in Awk from the original file rather than half in PHP and half in Awk or Perl. Given file1.txt above, the following will produce the desired output:

{
    Vals[$1]++;
    Vals[$2]++;
    Third_col[$1, $2] = Third_col[$2, $1] = $3;
}
END{
    for (i in Vals) {
        for (j in Vals) { 
            if (i == j || (i SUBSEP j in printed)) { continue } 
            Third_col_val = (i SUBSEP j in Third_col) ? Third_col[i, j] : 0; 
            print i, j, Third_col_val; 
            printed[j, i]++; 
            printed[i, j]++ 
        } 
    } 
}

Note that this takes O(n2) time, so for very large lists (millions of rows), it will become slow and memory intensive.

1

Another awk solution:

$ awk 'FNR==NR{a[$1,$2]=$3;next}
    {print $0,a[$1,$2]?a[$1,$2]:a[$2,$1]?a[$2,$1]:0}
' file1 file2
3377090 8145216 1.5
1405541 8145216 0
1405541 3377090 0
53595498 8145216 0
53595498 3377090 0
53595498 1405541 1.53637

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.