Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Column 2 string count in Column 3

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Column 2 string count in Column 3
# 1  
Old 06-15-2016
Column 2 string count in Column 3
My I/p is

Code:


Code:



Col1|Col2|Col3
2116209997932|POSIX INC|POSIX 
2116209997933|POSIX INC|POSIX 
2116210089479|POSIX INC|POSIX 
2116210180502|POSIX INC|POSIX 
2116210512279|POSIX INC|Aero	
2116210516838|POSIX INC|POSIX 
2116210534342|POSIX INC|postal
2116210534345|POSIX INC|postal 
2116210545347|POSIX INC|Aero 
2116210556200|POSIX INC|POSIX 
2116210560525|POSIX INC|Aero 
212230510298|POSIX INC|postal
212230582802|POSIX INC|POSIX 
212230582803|POSIX INC|POSIX

Required o/p, I want the number of counts the 3rd column is come as shown below

Code:


Code:



Col1|Col2|Col3
POSIX INC|POSIX|8
POSIX INC|Aero|3
POSIX INC|postal|3

If 3rd Column i s blank, It should consider even that and give the count as shown
Last edited by nikhil jain; 06-15-2016 at 06:02 AM..
# 2  
Old 06-15-2016
Your header doesn't match the columns any more. Wouldn't it be more like Col2|Col3|CNT?

And, is Col2 always constant or does it need to be considered when counting?

Plus, looking at your motto: "Pave your own path!!!" and the number of posts you have, wouldn't it make sense to present your own attempts/ideas/thoughts first before asking for services?
This User Gave Thanks to RudiC For This Post:
RavinderSingh13
# 3  
Old 06-15-2016
Thanks for that Rudi, I have corrected it ,there is only one header
# 4  
Old 06-15-2016
I was talking of the header in the output... Col1 is gone; should it be represented in the output header?
Last edited by RudiC; 06-15-2016 at 06:20 AM.. Reason: typo
# 5  
Old 06-15-2016
col 1 is unique, so it doesn't make a difference.. if it doesnt hamper col 2 and rest then i dont mind if it comes also.
# 6  
Old 06-15-2016
You didn't answer my question, so I go on with my idea. Try

Code:
awk 'NR==1 {print $2, $3, "CNT"; next} {SUM[$2 FS $3]++} END {for (s in SUM) print s, SUM[s]}' FS="|" OFS="|" file
Col2|Col3|CNT
POSIX INC|POSIX |7
POSIX INC|Aero |2
POSIX INC|postal |1
POSIX INC|postal|2
POSIX INC|Aero	|1
POSIX INC|POSIX|1

Last edited by RudiC; 06-15-2016 at 06:30 AM..
This User Gave Thanks to RudiC For This Post:
nikhil jain
# 7  
Old 06-15-2016
Quote:
Originally Posted by nikhil jain
col 1 is unique, so it doesn't make a difference.. if it doesnt hamper col 2 and rest then i dont mind if it comes also.
Hello Nikhil,

There seems to be space at last of your lines, so could you please try following and let us know if this helps.
Code:
awk -F"|" 'NR==1{print $2 FS $3 FS "CNT"; next}{gsub(/[[:space:]]+$/,X,$3);B[$2 FS $3]++} END{for(i in B){print i FS B[i]}}'  Input_file

Output will be as follows.
Code:
Col2|Col3|CNT
POSIX INC|POSIX|8
POSIX INC|Aero|3
POSIX INC|postal|3

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
nikhil jain
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Count occurrence of column one unique value having unique second column value

Hello Team, I need your help on the following: My input file a.txt is as below: 3330690|373846|108471 3330690|373846|108471 0640829|459725|100001 0640829|459725|100001 3330690|373847|108471 Here row 1 and row 2 of column 1 are identical but corresponding column 2 value are... (4 Replies)
Discussion started by: angshuman
4 Replies

2. Shell Programming and Scripting

Read first column and count lines in second column using awk

Hello all, I would like to ask your help here: I've a huge file that has 2 columns. A part of it is: sorted.txt: kss23 rml.67lkj kss23 zhh.6gf kss23 nhd.09.fdd kss23 hp.767.88.89 fl67 nmdsfs.56.df.67 fl67 kk.fgf.98.56.n fl67 bgdgdfg.hjj.879.d fl66 kl..hfh.76.ghg fl66... (5 Replies)
Discussion started by: Padavan
5 Replies

3. UNIX for Dummies Questions & Answers

Count occurrence of string (based on type) in a column using awk

Hello, I have a table that looks like what is shown below: AA BB CC XY PQ RS AA BB CC XY RS I would like the total counts depending on the set they belong to: if search pattern is in {AA, BB, CC} --> count them as Type1 | wc -l (3 Replies)
Discussion started by: Gussifinknottle
3 Replies

4. Shell Programming and Scripting

Count occurrence of string in a column using awk

Hi, I want to count the occurrences of strings in a column and display as in example below: Input: get1 345 789 098 get2 567 982 090 fet4 777 610 632 get1 800 544 230 get1 600 788 451 get2 892 321 243 get1 673 111 235 fet3 789 220 278 fet4 768 222 341 output: 4 get1 345 789... (7 Replies)
Discussion started by: aydj
7 Replies

5. Shell Programming and Scripting

Difference of the same column when two other column matches and one column differs less than 1 hour

This is my input file : # cat list 20130430121600, cucm, location,76,2 20130430121600,cucm1,location1,76,4 20130430122000,cucm,location,80,8 20130430122000,cucm1,location1,90,8 20130430140000,cucm1,location1,87,11 20130430140000, cucm,location,67,9 This is the required output ... (1 Reply)
Discussion started by: Lakshmikumari
1 Replies

6. Shell Programming and Scripting

Use a string in one column to get the largest or the smallest of another column

I have data that looks like this: chr1 mm9_knownGene exon 155747075 155747189 0.000000 + . gene_id "Glul"; transcript_id "uc007daq.1"; chr1 mm9_knownGene exon 155750064 155750076 0.000000 + . gene_id "Glul";... (3 Replies)
Discussion started by: pbluescript
3 Replies

7. Shell Programming and Scripting

Need Count of Column

Need help on count AL06 AL06_71 A=1 it=1 90 AL06 AL06_72 A=1 it=1 60 AL06 AL06_73 A=1 it=1 80 AL06 AL06_7 A=2 it=1 0 AL06 AL06_7 A=2 it=1 0 AL06 AL06_7 A=2 it=1 0 AL07 AL07_71 A=1 it=1 40 AL07 AL07_72 A=1 it=1 40 AL07 AL07_73 A=1 it=1 70 AL08 AL08_7 A=2 it=1 0 1 AL08 AL08_7 A=2 it=1... (2 Replies)
Discussion started by: asavaliya
2 Replies

8. Shell Programming and Scripting

Copy column string and put in different column

Hello Here is my input: SU3902 SU3902A NS29C (10) (00) Q1J1 0 SU3902 SU3902B VLR05 (20) (02) Q2H1 4 SU3902 SU3902C NBR22 (30) (06) Q3R5 8 SU3904 SU39047 NSV19 (11) (09) Q4k6 2 SU3904 SU39048 LB231 (12) (05) Q5k1 6 SU3904 SU39049 11VLT (13) (08) Q10C1 10 SU3904 SU3904A 25R05 (15) (06)... (3 Replies)
Discussion started by: pareshkp
3 Replies

9. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

10. Shell Programming and Scripting

Changing one column of delimited file column to fixed width column

Hi, Iam new to unix. I have one input file . Input file : ID1~Name1~Place1 ID2~Name2~Place2 ID3~Name3~Place3 I need output such that only first column should change to fixed width column of 15 characters of length. Output File: ID1<<12 spaces>>Name1~Place1 ID2<<12... (5 Replies)
Discussion started by: manneni prakash
5 Replies
Login or Register to Ask a Question