topic scripting question: how to merge two file based on common strings? in Operating System - HP-UX

scripting question: how to merge two file based on common strings?

rveri — Tue, 17 Mar 2009 16:22:40 GMT

Hello scripting masters,
I have two file , and want to make it one , after comparing a common field:

file1
######
mm00 9900
nn00 8811
oo00 7722
pp00 663x
qq00 5544
rr00 4433

file2
##########
mm00 9:0:1
nn00 8:0:2
oo00 7:0:3
pp00 6:0:4
qq00 5:0:x
rr00 4:0:t

Output should be:
mm00 9900 9:0:1
nn00 8811 8:0:2
oo00 7722 7:0:3
pp00 663x 6:0:4
qq00 5544 5:0:x
rr00 4433 4:0:t
###################

Thanks in advance..

Re: scripting question: how to merge two file based on common strings?

James R. Ferguson — Tue, 17 Mar 2009 16:33:40 GMT

Hi:

# comm

...see the manpages.

Regards!

...JRF...

Re: scripting question: how to merge two file based on common strings?

Bob E Campbell — Tue, 17 Mar 2009 16:44:05 GMT

If you are really asking the scripting question of how to nest file reads in shell, use something like this:

cat f2 |&
while read A B
do
read -p C D
if [[ ${A} != ${C} ]]
then
echo "ERROR: Files out of sync ($A != $C)"
else
echo "$A $B $D"
fi
done < f1
mm00 9900 9:0:1
nn00 8811 8:0:2
oo00 7722 7:0:3
pp00 663x 6:0:4
qq00 5544 5:0:x
rr00 4433 4:0:t

The comm(1) command does not do partial line pattern matching and is not really what you are looking for.

The above script assumes sort order and a 1 to 1 match for the lines. This can obviously be made much smarter.

Re: scripting question: how to merge two file based on common strings?

Bob E Campbell — Tue, 17 Mar 2009 17:03:10 GMT

OK, if I take away the assumptions of sort order and 1 to 1 mapping I come up with:

while read A B
do
Matches="$(grep -e "^${A}" f2)"
if [[ -z "${Matches}" ]]
then
echo "$A $B MISSING-DATA"
else
echo "${Matches}" |\
while read C D
do
echo "$A $B $D"
done
fi
done < f1

while read C D
do
grep -sqe "^${C}" f1
if [[ $? -ne 0 ]]
then
echo "$C MISSING-DATA $D"
fi
done < f2

gives the result (with edited files)

mm00 9900 9:0:1
nn00 8811 8:0:2
oo00 7722 7:0:3
pp00 663x 6:0:4
pp00 663x 6:5:3
pp00 663x 5:0:2
qq00 5544 5:0:x
rr00 4433 4:0:t
zz00 1234 MISSING-DATA
xx00 MISSING-DATA 3:1:7

Really just filling time before lunch... I should be doing many other things....

Re: scripting question: how to merge two file based on common strings?

Hein van den Heuvel — Tue, 17 Mar 2009 18:18:09 GMT

I'm sure JRF meant 'join', not comm'.

That should work, IF the files are sorted ALPHA. Watch out for free-format numbers.

Hein.

Re: scripting question: how to merge two file based on common strings?

Suraj K Sankari — Wed, 18 Mar 2009 03:51:19 GMT

Hi,

here is the example
[root@rspc521 suraj]# cat 1
nn00 8811
oo00 7722
pp00 663x
qq00 5544
rr00 4433

[root@rspc521 suraj]# cat 2
mm00 9:0:1
nn00 8:0:2
oo00 7:0:3
pp00 6:0:4
qq00 5:0:x
rr00 4:0:t

[root@rspc521 suraj]# join 1 2
nn00 8811 8:0:2
oo00 7722 7:0:3
pp00 663x 6:0:4
qq00 5544 5:0:x
rr00 4433 4:0:t

Suraj