Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Count the lines with the same values in a column and write the output to a file

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Count the lines with the same values in a column and write the output to a file
# 1  
Old 07-03-2012
Count the lines with the same values in a column and write the output to a file
Hey everyone!

I have a tab delimited data set which I want to create an output contained the calculation of number of those lines with a certain value in 2nd and 3rd column.

my input file is like this:
Code:
ID1   1   10M   AAATTTCCGG
ID2   5    4M    ACGT
ID3   5    8M    ACCTTGGA
ID4   5    8M    ACCTTGGA
ID5   5    8M    ACCTTGGA
ID6   20   3M   TCG
ID7   20   3M   TCG
ID8   20   12M   AACCTTGGCCTT
ID9   20   12M   AACCTTGGCCTT
ID10   20   12M   AACCTTGGCCTT

I want my output to be like this:
Code:
1    10M    1    AAATTTCCGG
5    4M     1    ACGT
5    8M     3    ACCTTGGA
20   3M    2    TCG
20   12M   3   AACCTTGGCCTT

Thanks in advance! Smilie
# 2  
Old 07-03-2012
Try the below,

Code:
cut -f2- FileName | sort | uniq -c

The first column will give you the count of the occurrences.
# 3  
Old 07-03-2012
Let us know if the order matters (or just add | sort -nk1 -k2after the script).
Code:
awk 'END {
  for (k in c) 
    print k, c[k], d[k] 
  }
{
  k = $2 OFS $3
  c[k]++; d[k] = $NF
  }' infile

This User Gave Thanks to radoulov For This Post:
@man
# 4  
Old 07-03-2012
Thanks Athix. I tried this code. But it didn't work. It just gives value 1 for all lines in the first column which is not true. And it keeps repeating the lines with the same values in 2nd and 3rd and 4th columns which I don't want.

---------- Post updated at 03:49 PM ---------- Previous update was at 03:46 PM ----------

Thnaks radoulov. I'm really newbie! Smilie I need more explanation. I tried to copy and paste what you said into terminal but I couldn't manage to give the path for the input file. Let me why although I know it is stupid! Smilie BTW, I already sorted my file using this script:
Code:
sort -n -k1 -k2 <filename>

# 5  
Old 07-03-2012
Code:
awk 'END {  
   for (k in c)      
    print k, c[k], d[k]    
    } 
{   
  k = $2 OFS $3  
  c[k]++; d[k] = $NF   
    }' <filename>

This User Gave Thanks to radoulov For This Post:
@man
# 6  
Old 07-03-2012
Code:
$ awk '{print $3,$2,$4}' input.txt | sort | uniq -c | awk '{print $3,$2,$1,$4}' | sort -n
1 10M 1 AAATTTCCGG
5 4M 1 ACGT
5 8M 3 ACCTTGGA
20 12M 3 AACCTTGGCCTT
20 3M 2 TCG

This User Gave Thanks to itkamaraj For This Post:
@man
# 7  
Old 07-03-2012
Thanks radoulov! It works perfectly! Smilie It just prints the last column of my dataset as the last column of output. In my real data I want the 10th column for the last column in the output! Thanks alot! Smilie
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 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. 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

4. 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

5. UNIX for Dummies Questions & Answers

count number of distinct values in each column with awk

Hi ! input: A|B|C|D A|F|C|E A|B|I|C A|T|I|B As the title of the thread says, I would need to get: 1|3|2|4 I tried different variants of this command, but I don't manage to obtain what I need: gawk 'BEGIN{FS=OFS="|"}{for(i=1; i<=NF; i++) a++} END {for (b in a) print b}' input ... (2 Replies)
Discussion started by: beca123456
2 Replies

6. Shell Programming and Scripting

Word count of values in a column

Hi friends, I have an input file of the following format a b c 1.11112 d e f 4.5767 g h i 19.098 k i l 87.9999 I am looking for an awk one liners that would help me in giving the following output output.txt Range of the column: 1.11112 to 87.9999 Total records between 1 and 10 - 2... (5 Replies)
Discussion started by: jacobs.smith
5 Replies

7. Shell Programming and Scripting

How to compare the values of a column in awk in a same file and consecutive lines..

I would like to compare the values of 2nd column of consecutive lines of same file in such a way so that if the difference between first value and second value is more than 100 it should print complete line else ignore line. Input File ========== PDB 2500 RTDB 123 RTDB-EAGLE 122 VSCCP 2565... (4 Replies)
Discussion started by: manuswami
4 Replies

8. UNIX for Dummies Questions & Answers

Count Number Of lines in text files and append values to beginning of file

Hello, I have 50 text files in a directory called "AllFiles" I want to make a program that will go inside of the "AllFiles" Directory and count the number of lines in each individual text file. Then, the program will calculate how many more lines there are over 400 in each text file and... (7 Replies)
Discussion started by: motoxeryz125
7 Replies

9. Shell Programming and Scripting

Help with script to read lines from file and count values

Hi, I need some help with a script I'm trying to write. I have a log file containing references to a number of different webservices. I wish to write a script that will list the webservices with a count as to how many times they appear in the log. An example of the log file content: ... (2 Replies)
Discussion started by: gman2010
2 Replies

10. UNIX for Dummies Questions & Answers

count number of rows based on other column values

Could anybody help with this? I have input below ..... david,39 david,39 emelie,40 clarissa,22 bob,42 bob,42 tim,32 bob,39 david,38 emelie,47 what i want to do is count how many names there are with different ages, so output would be like this .... david,2 emelie,2 clarissa,1... (3 Replies)
Discussion started by: itsme999
3 Replies
Login or Register to Ask a Question