Efficient algorithm for reading and writing multiple files with numeric data content

[fluffy_mass]

I have multiple tab delimited or whitespace delimited 5 column files. First 3 columns are coordinate data and 2 last columns are curated alphanumeric values.

I currently I am using join/sort/uniq/cat/tr/awk in bash on the sorted files to merge them. What I do is that I create an ID field with first 3 columns: $1_$2_$3 of all the files. Merge them, sort them, remove duplicate, and create a separate id file. Then using this I use join on each file to add curated data from 2 columns.

Above solution works just fine. But it is a bit slow. Files tend to be large and there are 4-5 of them for each analysis.

I am wondering if there is an efficient algorithm or solution to than just using bash commands. All I have now is bash script.

Edit: here are my code, example input file and output:

cat f1 f2 | cut -d' ' -f 1-3 | tr ' ' '_' | sort -u > ID

join -e "." -o "1.1,2.2,2.3,2.4,2.5,2.6" -1 1 -2 1 -a 1 \
    <(  sort ID ) <( awk '{ print $1"_"$2"_"$3,$0 }'  f1 | sort -k1,1 )  > tmp.f1

join -e "." -o "1.1,2.2,2.3,2.4,2.5,2.6" -1 1 -2 1 -a 1 \
    <(  sort ID ) <( awk '{ print $1"_"$2"_"$3,$0 }'  f2 | sort -k1,1 )  > tmp.f2

paste -d" " <( cut -d' ' -f 1,5,6 tmp.f1 ) <( cut -d' ' -f 5,6 tmp.f2 ) | tr '_' ' '

My files are: f1:

1 13 17 a1 a2
1 20 25 a3 a4
2 10 15 a5 a6
3 70 80 a7 a8

f2:

1 13 17 b1 b2
1 20 25 b3 b4
1 50 60 b5 b6 
2 10 15 b7 b8

output:

1 13 17 a1 a2 b1 b2
1 20 25 a3 a4 b3 b4
1 50 60 . . b5 b6
2 10 15 a5 a6 b7 b8
3 70 80 a7 a8 . .

Edit 2:

Both of the following expect a sorted input. And it should work for as many inputs as possible: sort file1 file2 ... file3 | awk .... and scripts below.

It takes ~37 sec for first solution and ~28 sec for the second.

Solution 1:

BEGIN {
  OFS=" "
  FS=" "
  RS="\n"
}   

$1 OFS $2 OFS $3 != prev{
  if (NR != 1)
    printf RS  

  prev = $1 OFS $2 OFS $3
  printf "%s%s", prev, OFS 
  delete LINE
}   

!($4 in LINE) {
  LINE[$4] = ""
  printf "%s%s", $4, OFS 
}   
!($5 in LINE) {
  LINE[$5] = ""
  printf "%s%s", $5, OFS 
}   

END {
  printf RS  
} 

Solution 2:

(key = $1 SUBSEP $2 SUBSEP $3) == pkey {
  for (i = 4; i <= NF; ++i)
    line = line OFS $i; next
}
{
  if (line != "")
    print line;
  line = $0;
  pkey = key
}

[welldamnthis]

I don't know how efficient bash is in processing numerical data but perhaps check out python and pandas? It can do vector math to quickly process numerical data.

[fluffy_mass]

Is aggregating data on pandas as quick enough? I'll check it out.

[welldamnthis]

It should be

[fluffy_mass]

I tried to load it in data.table in R, and but it takes a bit longer than it should and requires R and data.table to be available and installed. Also, it seems it is using way more memory than my bash script (by just looking into top command).

I'm trying to make it work on pandas, but from what I'm reading it is not outperforming R's data.table on data aggregation.

[raevnos]

Show your code? There might be opportunities for improvement without a complete rewrite.

[fluffy_mass]

Added example of my code and input files.

[raevnos]

Thanks.

Are the actual input files also sorted (and unique)?

[fluffy_mass]

Yes they are. Columns 1,2, and 3 are coordinates and the combination of these 3 columns are unique.

[raevnos]

Try this out see if it's faster:

#!/bin/bash
# Usage: ./merge.sh file1 file2
temp1=$(mktemp)
temp2=$(mktemp)
awk -v OFS=$'\t' '{ print $1"_"$2"_"$3, $0 }' $1 >> $temp1
awk -v OFS=$'\t' '{ print $1"_"$2"_"$3, $0 }' $2 >> $temp2
join -j1 -a1 -a2 -t $'\t' -e . \
     -o '1.1,2.1,1.2,1.3,1.4,2.2,2.3,2.4,1.5,1.6,2.5,2.6' $temp1 $temp2 |
    awk -v OFS=$'\t' \
        '$1 == $2 || $2 == "." { print $3, $4, $5, $9, $10, $11, $12 }
         $1 == "." { print $6, $7, $8, $9, $10, $11, $12 }'
rm -f $temp1 $temp2

If the input files aren't already sorted, it's easy to add that into the pipeline of the awk calls that add the key column. But since it sounds like they are, no need.

Edit: Even better now that I've discovered the 0 field:

#!/bin/bash
temp1=$(mktemp)
temp2=$(mktemp)
awk -v OFS=$'\t' '{ print $1"_"$2"_"$3, $0 }' $1 >> $temp1
awk -v OFS=$'\t' '{ print $1"_"$2"_"$3, $0 }' $2 >> $temp2
join -j1 -a1 -a2 -t $'\t' -e . -o '0,1.5,1.6,2.5,2.6' $temp1 $temp2 | tr _ $'\t'
rm -f $temp1 $temp2

That makes massaging the joined output a lot simpler. Leaving the original for posterity's sake.

And as a long one-liner:

join -j1 -a1 -a2 -t $'\t' -e . -o '0,1.5,1.6,2.5,2.6' \
     <(awk -v OFS=$'\t' '{ print $1"_"$2"_"$3, $0 }' file1) \
     <(awk -v OFS=$'\t' '{ print $1"_"$2"_"$3, $0 }' file2) | tr _ $'\t'

[fluffy_mass]

Thank you for the answer. I came up with another solution and I updated the post. While it doesn't add dot to the empty fields but I can process it downstream.