Finding the most common entry in a column

Donkey25 · #1 (**permalink**) 11-21-2007

Hi,

I have a file with 3 columns in it that are comma separated and it has about 5000 lines. What I want to do is find the most common value in column 3 using awk or a shell script or whatever works! I'm totally stuck on how to do this.

e.g.

value1,value2,bob
value1,value2,bob
value1,value2,bob
value1,value2,dave
value1,value2,james

Clearly in the above example the most popular value in column3 is "bob", but how would I write a script to work this out?

Many thanks

vgersh99 · #2 (**permalink**) 11-21-2007

nawk -f don.awk myFile

don.awk:

Code:

BEGIN {
  FS=","
}
{a[$3]++; if (a[$3] > comV) { comN=$3; comV=a[$3]} }
END {
    printf("Most Common Name: [%s] = [%d]\n", comN, comV)
}

summer_cherry · #3 (**permalink**) 11-21-2007

Hi,
This one should also be ok for you. Actually, this case involved persormance issue, since your file has thousound and hunderds of lines. So difficult logic will have different result.

To be honest, i only know how to get the result, but i have no idea to give out a high-performance code. So you'd better ask some expert for help.

Here comes my code:

Code:

awk 'BEGIN{
FS=","
n=0
}
{
sum[$3]++
if (sum[$3]>n)
{
	n=sum[$3]
	m=$3
}
}
END{
print m
}' filename

drl · #4 (**permalink**) 11-21-2007

Hi.

So you're willing to accept a (more or less) random result of any of the winners if there is a tie among two or more names? ... cheers, drl

radoulov · #5 (**permalink**) 11-22-2007

It will be sufficient to turn comN/m into array.

Donkey25 · #6 (**permalink**) 11-22-2007

Thanks guys,

I got both of the above to work but my CPU usage hit 100% lol! Any ideas on either making this more efficient or limiting the amount of CPU that this awk script can hog?

Thanks again

Klashxx · #7 (**permalink**) 11-22-2007

Hi, can u check this?

Code:

awk -F\, '{print $NF}' file|sort -u|xargs -i ksh -c 'echo "{} \c";grep -wc ",{}$" file'|sort -r -k 2,2|head -1|awk '{print $1}'

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Finding the total of a column using awk	ironhead3fan	Shell Programming and Scripting	8	01-30-2009 06:59 AM
How to check Null values in a file column by column if columns are Not NULLs	Mandab	Shell Programming and Scripting	7	03-15-2008 09:57 AM
finding duplicate files by size and finding pattern matching and its count	jerome Sukumar	Shell Programming and Scripting	2	12-01-2006 04:20 AM
Replace 10th column with a new column--- Terriblly hurry	ahmedwaseem2000	Shell Programming and Scripting	2	09-06-2005 01:10 AM
Finding a column in a flatfile	peter.herlihy	UNIX for Dummies Questions & Answers	3	01-07-2002 08:08 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: