Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

deleting columns with NAs

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting deleting columns with NAs
# 1  
Old 06-06-2012
deleting columns with NAs
I want to be able to delete columns whose data have more than 10 percent of NAs.
Code:
x1 x2 x3 x4
1 1 1 1
2 NA 2 2
1 2 1 NA
1 2 1 NA
NA 2 1 NA
1 2 1 NA
1 2 1 NA
1 2 1 NA
1 2 1 NA
1 2 1 NA
1 2 1 NA
1 2 1 NA
1 2 1 NA

so in this case i will delete x4.
lets say there are 100 tables with 500 columns each.
So I want to write 2 loops to extract and delete the columns with NAs.
Can you give me a hand?

Thanks Smilie
Last edited by Franklin52; 06-07-2012 at 03:58 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 06-06-2012
If your data can tolerate multiple spaces between columns:

Code:
SKIPCOLS=$(awk '{ for(N=1; N<=NF; N++) if($N == "NA") A[N]++ } END { for(X in A) if(A[X] > (NR/10)) S=S","X; print substr(S,2) }' data)

awk -v SKIP="4" 'BEGIN { split(SKIP, S, ",");} { for(X in S) $(S[X])="" } 1' data

This User Gave Thanks to Corona688 For This Post:
johnkim0806
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

2. Shell Programming and Scripting

Deleting specific columns

Hi group, Can you please tell how to delete specific columns from a file. I know something like awk -F, '{ print $1" "$2" "15 }' input.txt > output.txt will delete all other columns. But this is in a way to copy some particular columns. But is there any other way to select just some... (11 Replies)
Discussion started by: smitra
11 Replies

3. Shell Programming and Scripting

How to use awk for deleting columns?

Hi folks, How awk 'll help to do this file contains bunch of insert statement like below remove fourth column from all statement put it in new file INSERT INTO `tbl_medicalquestions` VALUES (1,'Is anyone waiting for an operation, post operative check up, any other hospital treatment or... (12 Replies)
Discussion started by: ashutoshverma
12 Replies

4. Shell Programming and Scripting

Deleting columns passed as arguments to the script

Hi all, I am trying to delete columns in a file using a script. The columns that need to be deleted are passed as arguments to the script. The script should look like this > delete_columns.sh <file_name.txt> <column_numbers_to_be_deleted> The contents of the file_name.txt will be like ... (5 Replies)
Discussion started by: VNR
5 Replies

5. UNIX for Advanced & Expert Users

Help in Deleting columns and Renaming Mutliple columns in a .Csv File

Hi All, i have a .Csv file in the below format startTime, endTime, delta, gName, rName, rNumber, m2239max, m2239min, m2239avg, m100016509avg, m100019240max, metric3min, m100019240avg, propValues 11-Mar-2012 00:00:00, 11-Mar-2012 00:05:00, 300.0, vma3550a, a-1_CPU Index<1>, 200237463, 0.0,... (9 Replies)
Discussion started by: mahi_mayu069
9 Replies

6. UNIX for Dummies Questions & Answers

Deleting all rows with empty columns

I have a text file that looks like this: 1 rs523634 8.22486 1 1 rs585160 8.22488 1 rs497228 8.2249 1 1 rs600933 8.225 1 rs480106 8.22531 1 rs600199 8.22533 1 rs529015 8.22534 1 rs598894 8.22534 I want to delete the rows with empty... (2 Replies)
Discussion started by: evelibertine
2 Replies

7. Shell Programming and Scripting

Deleting columns by list or file

Dear specialists out there, please help a poor awk newbie: I have a very huge file to process consisting of 300000 columns and 1500 rows. About 20000 columns shall be deleted from that file. So it is clear, that I can't do this by writing down all the columns in an awk command like $1, $x etc.... (5 Replies)
Discussion started by: flxms
5 Replies

8. Shell Programming and Scripting

deleting rows that dont have a certain # of columns

Hi, I want to delete rows that dont have a certain # of columns. In my case, rows that are less than 8 should be removed (those greater than 8 are ok). For instance: 1 2 3 4 5 6 7 8 2 3 2 4 3 2 1 5 1 2 3 4 5 6 8 2 2 4 3 1 1 1 1 1 1 1 1 1 after: 1... (8 Replies)
Discussion started by: gisele_l
8 Replies

9. Shell Programming and Scripting

Deleting columns from CSV file

Hi All, Am working on perl script which should delete columns in existing CSV file. If my file is : AA,BB,CC,DD 00,11,22,33 00,55,66,77 00,99,88,21 AA,BB... are all my headers can come in any order (e.g AA,CC,BB...) and rest are values. I want to delete column CC... Can anybody help... (2 Replies)
Discussion started by: darshakraut
2 Replies

10. Shell Programming and Scripting

Deleting specific columns from a file

Hi Friends, I want to delete specific columns from a file. Say my file content is as follows: "1","a","ww1",1234" "2","b","wwr3","2222" "3","c","erre","3333" Now i want to delete the column 2 and 4 from this file. That is I want the file content to be: "1","ww1" "2","wwr3"... (11 Replies)
Discussion started by: premar
11 Replies
Login or Register to Ask a Question