Join and awk max column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join and awk max column
# 1  
Old 01-26-2012
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.
# 2  
Old 01-26-2012
Can you please paste the join command you are using for this?

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

To count the max column
Code:
awk '{max=NF>max?NF:max}END{print max}' infile

--ahamed
This User Gave Thanks to ahamed101 For This Post:
# 3  
Old 01-26-2012
Try this for join command:

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

# 4  
Old 01-27-2012
Code:
awk 'NR==FNR{a[FNR]=$0;max2=FNR;next} {for (i=1;i<=max2;i++) print $0,a[i]}' file2 file1

This User Gave Thanks to rdcwayx For This Post:
# 5  
Old 01-27-2012
Quote:
Originally Posted by ahamed101
Can you please paste the join command you are using for this?

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

To count the max column
Code:
awk '{max=NF>max?NF:max}END{print max}' infile

--ahamed
Join file1 file2 > file3
# 6  
Old 01-27-2012
Quote:
Originally Posted by jacobs.smith
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
Quote:
join - join lines of two files on a common field
BTW, does the awk solution works for you?

--ahamed
# 7  
Old 01-27-2012
Quote:
Originally Posted by ahamed101
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.

Last edited by jacobs.smith; 01-27-2012 at 10:39 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. UNIX for Dummies Questions & Answers

Join with awk different column

hi guys, i need help I need to join file2 to file1 when column 3 in my file1 and column 1 in my file2 in the same string file1 AA|RR|ESKIM RE|DD|RED WE|WW|SUPSS file2 ESKIM|ES SUPSS|SS Output AA|RR|ESKIM|ES RE|DD|RED| WE|WW|SUPSS|SS (3 Replies)
Discussion started by: radius
3 Replies

3. Shell Programming and Scripting

Get the MAX value out of a column

I've the following data set. I would like to look at the column 3 and only use the rows which has the max value for column 3 Can we use the awk or sed to achieve it. 10 2 10 100 11 2 20 100 12 2 30 100 13 2 30 100 14 ... (7 Replies)
Discussion started by: rudoraj
7 Replies

4. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

5. Shell Programming and Scripting

AWK script to create max value of 3rd column, grouping by first column

Hi, I need an awk script (or whatever shell-construct) that would take data like below and get the max value of 3 column, when grouping by the 1st column. clientname,day-of-month,max-users ----------------------------------- client1,20120610,5 client2,20120610,2 client3,20120610,7... (3 Replies)
Discussion started by: ckmehta
3 Replies

6. Shell Programming and Scripting

extracting row with max column value using awk or unix

Hello, BC106081_abc_128240811_128241377 7.96301 BC106081_abc_128240811_128241377 39.322 BC106081_cde_128240811_128241377 1.98628 BC106081_def_128240811_128241377 -2.44492 BC106081_abc_128240811_128241377 69.5504 FLJ00075_xyz_14406_16765 -0.173417 ... (3 Replies)
Discussion started by: Diya123
3 Replies

7. Shell Programming and Scripting

loop in awk - column max for each column

Hello all, this should really be easy for you... I need AWK to print column maxima for each column of such input: Input: 1 2 3 1 2 1 1 3 2 1 1 2 Output should be: 2 2 3 3 This does the sum, but i need max instead: { for(i=1; i<=NF; i++) sum +=$i } END {for(i=1; i in sum;... (3 Replies)
Discussion started by: irrevocabile
3 Replies

8. Shell Programming and Scripting

Join multiple files by column with awk

Hi all, I searched through the forum but i can't manage to find a solution. I need to join a set of files placed in a directory (~1600) by column, and obtain an output with first and second column common to each file, but following columns are taken from the file in the list (precisely the fourth... (10 Replies)
Discussion started by: macsx82
10 Replies

9. Shell Programming and Scripting

sed/awk to retrieve max year in column

I am trying to retrieve that max 'year' in a text file that is delimited by tilde (~). It is the second column and the values may be in Char format (double quoted) and have duplicate values. Please help. (4 Replies)
Discussion started by: CKT_newbie88
4 Replies

10. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies
Login or Register to Ask a Question