Join and awk max column

Hi Friends,

I have a file1 with 3400 records that are tab separated and I have a file2 with 6220 records. I want to merge both these files. I tried using join file1 and file2 after sorting. But, the records should be (3400*6220 = 21148000). Instead, I get only around 11133567. Is there anything that I am doing wrong. Also, is there a way to join each record in file1 to each record in file2 using awk?

File1
a b c d
e f g h

File2
1 2 3 4
5 6 7 8

Output
a b c d 1 2 3 4
a b c d 5 6 7 8
e f g h 1 2 3 4
e f g h 5 6 7 8

Also, can some tell me how to find the max of column in awk. I tried the following one...

awk 'max=="" || $2 > max {max=$2} END{ print max}' FS=" " input.txt

But, that gives only for numerical values. And, I have a mix of numbers and characters in column6. Ex: xyz_123 to xyz_4453. But, the max on $6 is showing to be xyz_999.

Thanks in advance.

All helps appreciated.

Can you please paste the join command you are using for this?

An awk solution...

awk 'NR==FNR{a[$0];next} { for(i in a){print i, $0} }' file1 file2 | sort

To count the max column

awk '{max=NF>max?NF:max}END{print max}' infile

--ahamed

Try this for join command:

while read LINE
do
sed -e "s/^/${LINE}/g" File2
done < File1

awk 'NR==FNR{a[FNR]=$0;max2=FNR;next} {for (i=1;i<=max2;i++) print $0,a}' file2 file1

Join file1 file2 > file3

Really? I don't think that will work. At least it is not working for me, not with the data samples provided unless you have tried it with actual data where there is some common factor.

man join

BTW, does the awk solution works for you?

--ahamed

oops...I am sorry. Thanks for reminding me.

my files are this way.

File1
x y z
x d e
a b c
a g h

File2
x 1 2 3 4
a 5 6 7 8
y 3 4 5 6

Output
x y z 1 2 3 4
x d e 1 2 3 4
a b c 5 6 7 8.
a g h 5 6 7 8

I apologize for my insanity.

All the codes work for each record by reach record.

But, is there a way to match it to the first column of file1 to the first column of file 2 and then only print the output?

Thanks in advance.

awk 'NR==FNR{a[$0]=$1;next}{for(i in a){if(a==$1){print i,substr($0,length($1)*2)}}}' file1 file2

--ahamed

What does the updated one do?

Strip the first value from the second file... i.e. the common field

--ahamed

Ok. You mean it wouldn't print the first value from second file in the final output. Did I get it right?

Also, the max column code you have given me, prints the maximum number of columns but not the maximum value in a column.

All your time is appreciated ahamed!

I owe you a long time for saving a lot of my time.

Thanks in advance.

Yeah thats right...

Anf for the max, try this... I thought you wanted the max column number, my bad!

awk '{for(i=1;i<=NF;i++){max=$i+0>max?$i+0:max}print max; max=0}' infile

Did I get you wrong again? You want the max value in a column or row?

--ahamed

OK. Let's think cool.

I have a file like this

x y z 1 2 3
a b c 4 9 22
g h k 2 1 -9

Now, I want to see maximum value in a column. Say, fourth. The answer would be 4. Say fifth, the answer would be 9 and for sixth it would be 22.

I would appreciate, if in addition to the maximum value in a column, is there a way to print the range too?

Thanks ahamed. I doubt if you are the author of awk.

awk '{for(i=1;i<=NF;i++){a=$i+0>a?$i+0:a}}END{for(i=1;i<=length(a);i++){printf (a?a:0)OFS}printf "\n"}1' infile

For the range

awk '{for(i=1;i<=NF;i++){b=b?b:$i+0;a=$i+0>a?$i+0:a; b=$i+0<b?$i+0:b;}}
END{for(i=1;i<=length(a);i++){printf (a?a:0)"/"(b?b:0)OFS}printf "\n"}1' infile

--ahamed