Combining records from two sorted files

Question

I have two big files with more than six million records. Data in those two files can be correlated by UID (if ordering the file, should be at same row in both files). Eventually I need to get data from the first file concatenated with data in the second file.

The issue is that executing the script is taking 10 hours for about 650,000 records!!

I'd like to improve it.

UIDS=`cut -f1 -d',' sorted_UID_data1.txt`

for record in $UIDS
do
    echo `grep $record sorted_UID_data1.txt| awk -F ',' '{print $2}'`,`grep $record sorted_UID_data2.txt` >> data.txt
done

In order to optimize it, I thought of

TOTAL_RECORDS=`wc -l < sorted_UID_data1.txt`

recordId=1
while [ $recordId -le $TOTAL_RECORDS ]
do
    echo `sed -n "${recordId}{p;q;}" sorted_UID_data1.txt| awk -F ',' '{print $2}'`,`sed -n "${recordId}{p;q;}" sorted_UID_data2.txt` >> data.txt
    recordId=$(( $recordId + 1 ))
done

And this is also taking too much time.

But then, I'm thinking: What if I always can grab the first line of the file? I've seen that this could be done by sed, tail, or AWK, but this seems to be inefficient.

How can I fix this problem?

Answer 1

To remove the first line use tail :

# seq 5 | tail -n +2
2
3
4
5

And to only "grab the first line" use head :

# seq 5 | head -n 1
1

But to join two files line by line use paste :

# seq 5 > nums
# echo -e 'a\nb\nc\nd\ne' > chars
# paste nums chars     
1       a
2       b
3       c
4       d
5       e

And to join two files with matching common fileds use join:

# paste -d , <( seq 5 ) <( seq 11 15 ) > teens
# paste -d , <( seq 5 ) <( seq 21 25 ) > twenties
# join -t , teens twenties
1,11,21
2,12,22
3,13,23
4,14,24
5,15,25

Answer 2

As you use key-field better use join

join -t ',' sorted_UID_data1.txt sorted_UID_data2.txt