Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Filter rows from table

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Filter rows from table
# 1  
Old 01-03-2013
Filter rows from table
Hi ,

I need to filter input file according to following

All rows with the following conditions should be removed
1) If in a row, the number of 'N's starting col 2 exceeds 2 (3 or more)

OR

2) If a row is duplicated with the same value, starting col 2,
A value 'N' is considered missing value and shouldnt be considered
in this step. If a row has 4 'C's and and 2 'N's , it is considered a duplicate
row.

Inp

Code:
S10_14112872		C	C	C	N	C	C
S10_14113021		G	G	N	N	G	G
S10_14113022		T	T	T	T	T	T
S10_14113023		A	A	A	A	A	A
S10_14113025		T	T	T	A	T	T
S10_14113070		N	N	N	A	A	T
S10_14113072		A	C	A	A	A	A
S10_14113072		G	C	G	G	N	N
S10_14113073		C	C	C	C	C	C
S10_14113075		N	N	A	N	N	A

Desired op

Code:
S10_14113025		T	T	T	A	T	T
S10_14113072		A	C	A	A	A	A
S10_14113072		G	C	G	G	N	N

# 2  
Old 01-03-2013
This should work:
Code:
$ awk '{for(i=2;i<=NF;i++)a[$i]++;for(i in a){if(i=="N"){if(a[i]>2)p=1}else x++;delete a[i]};if(!p&&x>1)print;x=p=0}' filename
S10_14113025            T       T       T       A       T       T
S10_14113072            A       C       A       A       A       A
S10_14113072            G       C       G       G       N       N

This User Gave Thanks to Subbeh For This Post:
newbie83
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert rows into columns and create table with awk

Hello I've four fields . They are First Name, Last Name, Age, Country. So when I run a Unix command, I get below output with these fields comes every time in different order as you can see. Some times first name is the first row and other time last name is first row in the output and etc etc..... (9 Replies)
Discussion started by: rprpr
9 Replies

2. UNIX for Advanced & Expert Users

Filter table of different length

Dear Forum, I have to filter (e.g. PF=0.8) a text file according to some measured and recorded values for different fields (sensor). The files can be large and the recorded data points (ID) could differ in length. I have worked out a solution but it is very messy and not flexible. Does anybody... (4 Replies)
Discussion started by: GDC
4 Replies

3. UNIX for Beginners Questions & Answers

Merge cells in all rows of a HTML table dynamically.

Hello All, I have visited many pages in Unix.com and could find out one solution for merging the HTML cells in the 1st row. (Unable to post the complete URL as I should not as per website rules). But, however I try, I couldn't achieve this merging to happen for all other rows of HTML... (17 Replies)
Discussion started by: Mounika
17 Replies

4. Shell Programming and Scripting

UNIX command -Filter rows in fixed width file based on column values

Hi All, I am trying to select the rows in a fixed width file based on values in the columns. I want to select only the rows if column position 3-4 has the value AB I am using cut command to get the column values. Is it possible to check if cut -c3-4 = AB is true then select only that... (2 Replies)
Discussion started by: ashok.k
2 Replies

5. Shell Programming and Scripting

Fill in missing rows with zero to have uniform table

Hello, I have two files of same structure except some rows are missing randomly in each file. How do I fill the missing rows to have the exact ID column (S01 ~ S96) and rest columns filled with "0" with awk? The purpose of this step is to join the two files side by side. The closest thread is... (17 Replies)
Discussion started by: yifangt
17 Replies

6. Programming

Getting Rows from a MySQL Table with max values?

I feel stupid for asking this because it seems that MYSQL code isn't working the way that I think it should work. Basically I wrote code like this: select * from `Test_DC_Trailer` HAVING max(DR_RefKey); Where the DR_RefKey is a unique numeric field that is auto iterated (like a primary key)... (7 Replies)
Discussion started by: Astrocloud
7 Replies

7. Shell Programming and Scripting

How to select the rows from oracle table using perl?

Hi, I am connecting to oracle database using perl. I am able to connect but i am not able to get all the rows from the table. Here is the code. #!/usr/bin/perl use DBI; my $dbh=DBI->connect("DBI:Oracle:student","class","welcome") or die "Couldnot connect oracle Database";... (1 Reply)
Discussion started by: vanitham
1 Replies

8. Shell Programming and Scripting

How to filter a table by two columns

Dear Forum, I would like to know how could I found every result from one column at a table based at two table. Exemplo: Table: Red 4 Red 5 Red 10 Black 33 Black 44 Black 5 Green 2 Green 55 Green 78 I would like to have every color with the lower result... (12 Replies)
Discussion started by: lColli
12 Replies

9. Shell Programming and Scripting

Shell script to extract rows from table

I have an Employee with EID, ENAME and ESTATUS as columns in SQL. I want to extract the status of an employee and update the details if the status is 'A'. Can anyone help in writing the shell script. (1 Reply)
Discussion started by: vkca
1 Replies

10. Programming

Creating a table like format with rows and columns

I have few files which have two columns in each. like e2 1 1 2694 2 4 2485 3 2 2098 5 1 2079 6 5 2022 9 4 1734 11 5 1585 13 2 1461 18 1 1092 21 2 1019 24 1 915 25 3 907 27 1 891 28 3 890 34 1 748 39 1 700 (1 Reply)
Discussion started by: kamuju
1 Replies
Login or Register to Ask a Question