Awk - Count instances of a number in col1 and put results in a col2 (new) of diff file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk - Count instances of a number in col1 and put results in a col2 (new) of diff file
# 1  
Old 06-27-2010
Awk - Count instances of a number in col1 and put results in a col2 (new) of diff file

I have 2 files as follows:

filename1: [FileID FileName FileSize]:
Code:
6742 /welcome/mundial98_ahf1_404.htm 1020
6743 /welcome/mundial98_ahf1_404.htm 2224
6744 /welcome/mundial_ef1_404.htm 21678
6745 /welcome/mundial_if_404.htm 4236
6746 /welcome/mundial_lf1_404.htm 21678

filename2: [FileID timestamp operation FileName FileSize]
Code:
6746 894694763 1 /welcome/mundial_lf1_404.htm 21678
6742 894694763 1 /welcome/mundial98_ahf1_404.htm 1020
6742 894694763 1 /welcome/mundial98_ahf1_404.htm 1020
6745 894694763 1 /welcome/mundial_if_404.htm 4236
6746 894694763 1 /welcome/mundial_lf1_404.htm 21678
6746 894694763 1 /welcome/mundial_lf1_404.htm 21678


I need to count the occurrences of each number in col1 of filename2 and place the them in a new col (between col1 and col2) of filename1.They should be in the row corresponding to the 1st column of each file (FileID).

So for the 2 files above, the expected output should be a new filename1 with a new column (NumRequests):
filename1 (new): [FileID NumRequests FileName FileSize]:
Code:
6742 2 /welcome/mundial98_ahf1_404.htm 1020
6743 0 /welcome/mundial98_ahf1_404.htm 2224
6744 0 /welcome/mundial_ef1_404.htm 21678
6745 1 /welcome/mundial_if_404.htm  4236
6746 3 /welcome/mundial_lf1_404.htm 21678

Thank you so much for your help!

Last edited by Franklin52; 06-27-2010 at 02:19 PM.. Reason: Please use code tags
# 2  
Old 06-27-2010
Try:
Code:
awk 'NR==FNR{a[$1]++;next}
{$1=a[$1]?$1 FS a[$1]:$1 FS "0"}
1' filename2 filename1  > newfile

This User Gave Thanks to Franklin52 For This Post:
# 3  
Old 06-27-2010
This worked perfectly! Thank you!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to count number of results found?

Hi guys, I'm struggling with this one, any help is appreciated. I have File1 with hundreds of unique words, like this: word1 word2 word3 I want to count each word from file1 in file2 and return how many times each word is found. I tried something like this: for i in $(cat file1); do... (13 Replies)
Discussion started by: demmel
13 Replies

2. Shell Programming and Scripting

Compare two CSV files and put the difference in third file with line no,field no and diff value.

I am having two csv files i need to compare these files and the output file should have the information of the differences at the field level. For Example, File 1: A,B,C,D,E,F 1,2,3,4,5,6 File 2: A,C,B,D,E,F 1,2,4,5,5,6 out put file: (12 Replies)
Discussion started by: karingulanagara
12 Replies

3. UNIX for Dummies Questions & Answers

If col1 and col2 of any line in both of two files in two files match, col1 and col2

I have two files, and I'm interested in the first two columns of each. File1 compares set1 to set2 (column1 = set1 name, column2 = set2 name). File2 compares set2 to set1 (column1 = set2 name, column2 =set1 name). I want to print the set names (column values) that appear as pairs in both... (1 Reply)
Discussion started by: pathunkathunk
1 Replies

4. Shell Programming and Scripting

count the number of instances in 2 columns using awk

Input A.1 Q.1 A.1 Q.2 A.1 Q.3 A.2 Q.4 Explanation: Final Output A.1 Q.1 s1 t1 A.1 Q.2 s1 t2 A.1 Q.3 s1 t3 A.2 Q.4 s5 t5 ---------- Post updated 09-28-12 at 03:38 AM ---------- Previous update was 09-27-12 at 09:10 AM ---------- Hi Guys, I was able to do until... (11 Replies)
Discussion started by: quincyjones
11 Replies

5. Shell Programming and Scripting

List unique values and count instances in .csv file

I need to take the second column of a .csv file and count the number of instances of each unique value in that same second column. I'd like the output to be value,count sorted by most instances. Thanks for any guidance! Data example: 317476,317756,0 816063,318861,0 313123,319091,0... (4 Replies)
Discussion started by: batcho
4 Replies

6. Shell Programming and Scripting

count and number instances of a character in sed or awk

I currently use LaTeX together with a sed script to set cloze test papers for my students. I currently pepend and equals sign to the front of the words I want to leave out in the finished test, =perpendicular, for example. I am able to number the blanks using a variable in LaTeX. I would like to... (8 Replies)
Discussion started by: maouinin
8 Replies

7. Shell Programming and Scripting

.procmailrc and uudeview (put attachments from diff senders to diff folders)

Moderator, please, delete this topic (1 Reply)
Discussion started by: optik77
1 Replies

8. Shell Programming and Scripting

How to put count for first element in a file at the end

Hi, I have a file where I need to count the total for the first element and put it back at the end of file... here is the example... input.. FHDR|ABC|20100607| |ABC|8453|CDE|E166|||| 123|ABC|8453|CDE|E166|||| 123|ABC|8453|CDE|E166|||| 111|ABC|8453|CDE|E166||||... (8 Replies)
Discussion started by: donadarsh
8 Replies

9. UNIX for Dummies Questions & Answers

minus col1 and col2

my file looks like this: 101928 101943 101928 101944 101929 101943 101929 101943 101929 102044 i want to insert bc to get answer like this: 101928 101943 000015 101928 101944 000016 101929 101943 000013 101929 101943 000014 101929 102044 000115 total 000173 my... (3 Replies)
Discussion started by: tjmannonline
3 Replies

10. UNIX for Dummies Questions & Answers

awk | stop after specified number of results

I am searching some rather large text files using grep and or awk. What I would like to know is if there is a way (either with grep, awk, or realy any other unix tool) to stop the search when a predifined number of results are returned. I would like to do this for speed purpuses. When i get... (6 Replies)
Discussion started by: evan108
6 Replies
Login or Register to Ask a Question