Visit Our UNIX and Linux User Community


How to count occurrences in a specific column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to count occurrences in a specific column
# 1  
Old 10-01-2012
How to count occurrences in a specific column

Hi,

I need help to count the number of occurrences in $3 of file1.txt. I only know how to count by checking one by one and the code is like this:
Code:
awk '$3 ~ /aku hanya poyo/ {++c} END {print c}' FS="\t" file1.txt

But this is not wise to do as i have hundreds of different occurrences in that column that i need to count. my sample input file as follow:

file1.txt
Code:
123   ghtd   tidak mahu   
645   pled   aku hanya poyo
944   pom    ngeh3
3351  bhg    tidak mahu
5545  polo   ngeh3
4474  klsa   tidak mahu

output
Code:
tidak mahu       3
aku hanya poyo   1
ngeh3            2

Thanks in advance.
# 2  
Old 10-01-2012
Is TAB the delimiter in your file?
# 3  
Old 10-01-2012
Code:
 awk '{sub(" *$","");s=substr($0, index($0,$3));o[s]++;}END { for (i in o) printf("%-20s %d\n", i, o[i]);}' file1.txt

# 4  
Old 10-01-2012
Hi bartus11,

yes, it is tab delimited. but for each column, i have data in strings.

hi rdrtx1,
i tried your code but it didnt work Smilie

thanks
# 5  
Old 10-01-2012
Try:
Code:
awk -F"\t" '{a[$3]++}END{for (i in a) print i"\t"a[i]}' file

This User Gave Thanks to bartus11 For This Post:
# 6  
Old 10-01-2012
Hi bartus11,

It works great. Thanks..But i have another issue, after i run your code, i just realized that in $3, there could be more than 1 value that are separated by comma and it is not being counted. The sample is like this:-

Code:
123   ghtd   tidak mahu    
645   pled   aku hanya poyo 
944   pom    ngeh3
3351  bhg    tidak mahu 
545   polo   ngeh3 
4474  klsa   tidak mahu
1141  meh    tidak mahu, ngeh3, dtg sini
457   nah    aku hanya poyo, tidak mahu

where the output should be
Code:
tidak mahu       5 
aku hanya poyo   2 
ngeh3            3
dtg sini        1

appreciate your kind help again. Thanks
# 7  
Old 10-01-2012
Code:
awk '{sub("[\t ]*$","");gsub(" *, *",",");s=substr($0,index($0,$3));c=split(s,sa,",");for (i in sa)o[sa[i]]++;}END{for(i in o)printf("%-20s %d\n",i,o[i]);}' file1.txt

This User Gave Thanks to rdrtx1 For This Post:

Previous Thread | Next Thread
Test Your Knowledge in Computers #426
Difficulty: Medium
JavaScript uses prototypes where many other object-oriented languages use classes for inheritance.
True or False?

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Count occurrences in first column

input amex-11 10 abc amex-11 20 bcn amed-12 1 abc I tried something like this. awk '{h++}; END { for(k in h) print k, h }' rm1 output amex-11 1 10 abc amex-11 1 20 bcn amed-12 2 1 abc Note: The second column represents the occurrences. amex-11 is first one and amed-12 is the... (5 Replies)
Discussion started by: quincyjones
5 Replies

2. Shell Programming and Scripting

Count frequency of unique values in specific column

Hi, I have tab-deliminated data similar to the following: dot is-big 2 dot is-round 3 dot is-gray 4 cat is-big 3 hot in-summer 5 I want to count the frequency of each individual "unique" value in the 1st column. Thus, the desired output would be as follows: dot 3 cat 1 hot 1 is... (5 Replies)
Discussion started by: owwow14
5 Replies

3. Shell Programming and Scripting

Count specific column values

Hi all: quick question! I have the following data that resembles some thing like this: i am tired tired am i what is up hello people cool I want to count (or at least isolate) all of the unique elements in the 2nd column. I have tried this: cut -f 2 | uniq 'input' which does... (3 Replies)
Discussion started by: owwow14
3 Replies

4. Shell Programming and Scripting

awk Group By and count string occurrences

Hi Gurus, I'm scratching my head over and over and couldn't find the the right way to compose this AWK properly - PLEASE HELP :confused: Input: c,d,e,CLICK a,b,c,CLICK a,b,c,CONV c,d,e,CLICK a,b,c,CLICK a,b,c,CLICK a,b,c,CONV b,c,d,CLICK c,d,e,CLICK c,d,e,CLICK b,c,d,CONV... (6 Replies)
Discussion started by: Royi
6 Replies

5. Shell Programming and Scripting

Count specific characters at specific column positions

Hi all, I need help. I have an input text file (input.txt) like this: 21 GTGCAACACCGTCTTGAGAGG 50 21 GACCGAGACAGAATGAAAATC 73 21 CGGGTCTGTAGTAGCAAACGC 108 21 CGAAAAATGAACCCCTTTATC 220 21 CGTGATCCTGTTGAAGGGTCG 259 Now I need to count A/T/G/C numbers at each character location in column... (2 Replies)
Discussion started by: thienxho
2 Replies

6. Shell Programming and Scripting

Script to count word occurrences, but exclude some?

I am trying to count the occurrences of ALL words in a file. However, I want to exclude certain words: short words (i.e. <3 chars), and words contained in an blacklist file. There is also a desire to count words that are capitalized (e.g. proper names). I am not 100% sure where the line on... (5 Replies)
Discussion started by: Cronk
5 Replies

7. Shell Programming and Scripting

Count occurrences in awk

Hello, I have an output from GDB with many entries that looks like this 0x00007ffff7dece94 39 in dl-fini.c 0x00007ffff7dece97 39 in dl-fini.c 0x00007ffff7ab356c 50 in exit.c 0x00007ffff7aed9db in _IO_cleanup () at genops.c:1022 115 in dl-fini.c 0x00007ffff7decf7b in _dl_sort_fini (l=0x0,... (6 Replies)
Discussion started by: ikke008
6 Replies

8. Shell Programming and Scripting

Count the number of occurrences of the word

I am a newbie in UNIX shell script and seeking help on this UNIX function. Please give me a hand. Thanks. I have a large file. Named as 'MyFile'. It was tab-delmited. I am told to write a shell function that counts the number of occurrences of the ord “mysring” in the file 'MyFile'. (1 Reply)
Discussion started by: duke0001
1 Replies

9. UNIX for Dummies Questions & Answers

count occurrences and substitute with counter

Hi Unix-Experts, I have a textfile with several occurrences of some string XXX. I'd like to count all the occurrences and number them in reverse order. E.g. input: XXX bla XXX foo XXX output: 3 bla 2 foo 1 I tried to achieve this with sed, but failed. Any suggestions? Thanks in... (4 Replies)
Discussion started by: ptob
4 Replies

Featured Tech Videos