5

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?

2
  • Cross posted here: stackoverflow.com/questions/32806513/…
    – user000001
    Sep 27 '15 at 13:46
  • Both of your attempts read the whole file once (or twice!) for each record, so they're O(n^2). If you'd needed to do something more complicated than join or paste could handle, you could read all the lines of one file into an associative array in awk, or a list or hash in perl, then process the other file and grab appropriate entries from the first file. If they're both sorted, perl is a good choice for reading from two files at once. I think awk makes that fairly easy, too, but you'd have to process it with split manually, not awk field splitting. Sep 27 '15 at 23:55
8

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
2
  • this is still inefficient if there is a need to do it on 6M records Sep 27 '15 at 9:43
  • Then, use paste to begin with. The tail and head examples were there to answer the question in the topic. Sep 27 '15 at 9:46
4

As you use key-field better use join

join -t ',' sorted_UID_data1.txt sorted_UID_data2.txt
4
  • 2
    assuming the files are sorted (an the common field used as a key) beforehand. Sep 27 '15 at 13:41
  • @MichelBillaud Did you see sorted_UID_data1.txt?
    – Costas
    Sep 27 '15 at 13:49
  • Yes, of course. Well, the first message says : << Data in those 2 files can be correlated by UID (if ordering the file, should be at same row in both files). >>. The "if ordering" somehow weakens the belief that they are actually ordered. So, better safe than sorry, insisting on this point may not hurt. Sep 27 '15 at 13:59
  • 2
    There are several other points in the message that are not clear: 1) why the title "removing 1st line from a text file" 2) concatenation of the files, where the deal is about pasting the lines. So this make me feel the terminology used in the question has to be swallowed with caution and a grain of salt. Sep 27 '15 at 14:03

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.