Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Sort tab delimited file according to which rows have missing values

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Sort tab delimited file according to which rows have missing values
# 1  
Old 03-27-2015
Sort tab delimited file according to which rows have missing values
Hello!
I have a tab delimited file with values in three columns. Some values occur in all three columns, other values are present in only one or two columns. I would like to sort the file so that rows with no missing values come first, rows with one missing values come next, and rows with two missing values come last. Thanks in advance for any advice!

I have:

Code:
A B C
1 1 X
2 X 2
4 4 4
5 X 5
6 6 X
3 3 3
7 X X
X 8 X
9 9 9

I would like:
Code:
A B C
3 3 3
4 4 4
9 9 9
1 1 X
6 6 X
2 X 2
5 X 5
7 X X
X 8 X

# 2  
Old 03-27-2015
Any attempts from your side?
# 3  
Old 03-27-2015
A crude way, I`m sure there is better solution

Code:
awk -F"X" 'NR==1{$0= $0" D"} NR>1{ $0=$0" "NF}1' infile| awk 'NR<2{print $0;next}{print $0| "sort -nk4,4"}' | awk '{print $1,$2,$3}'

This User Gave Thanks to senhia83 For This Post:
MBarrett1213
# 4  
Old 03-27-2015
Thank you so much. This worked perfectly. I appreciate your help!
# 5  
Old 03-27-2015
Yes, the idea FS="x" simplifies the task a lot.
Here is a smarter implementation:
Code:
awk -F"X" '{a[NF]=a[NF] ((a[NF]=="")?"":RS) $0} (NF>max) {max=NF} END {for (i=1; i<=max; i++) print a[i]}' infile

Though it does not do an alphabetic sort.
# 6  
Old 03-27-2015
Code:
 awk 'NR==1 {print "0", $0; next} {print 1+gsub (/X/,"&",$0), $0}' file | sort -k1,1 | awk '{sub ($1 " ", "")}1'
A B C
3 3 3
4 4 4
9 9 9
1 1 X
2 X 2
5 X 5
6 6 X
7 X X
X 8 X

These 2 Users Gave Thanks to RudiC For This Post:
MBarrett1213 vgersh99
# 7  
Old 03-27-2015
Quote:
Originally Posted by RudiC
Code:
 awk 'NR==1 {print "0", $0; next} {print 1+gsub (/X/,"&",$0), $0}' file | sort -k1,1 | awk '{sub ($1 " ", "")}1'
A B C
3 3 3
4 4 4
9 9 9
1 1 X
2 X 2
5 X 5
6 6 X
7 X X
X 8 X

I like 'cut' for this approach:
Code:
awk 'NR==1 {print "0", $0; next} {print 1+gsub (/X/,"&",$0), $0}' file | sort -k1,1 | cut -d ' ' -f2-

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace a column in tab delimited file with column in other tab delimited file,based on match

Hello Everyone.. I want to replace the retail col from FileI with cstp1 col from FileP if the strpno matches in both files FileP.txt ... (2 Replies)
Discussion started by: YogeshG
2 Replies

2. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

3. UNIX for Dummies Questions & Answers

How to sort the 6th field of tab delimited files?

Here's a sample of the data: NAME BIRTHDAY SEX LOCATION AGE ID Jim 05/11/1986 M Japan 27 86 Rei 08/25/1990 F Korea 24 33 Jane 02/24/1985 F India 29 78 I've been trying to sort files using the... (8 Replies)
Discussion started by: maihani
8 Replies

4. UNIX for Dummies Questions & Answers

awk - Extract 4 lines in Column to Rows Tab Delimited between tags

I have tried the following to no avail. xargs -n8 < test.txt awk '{if(NR%6!=0){p=""}else{p="\n"};printf $0" "p}' Mod_Alm_log.txt > test.txt I have tried different variations of the above, the problem is mixes lines together. And it includes the tags "%a and %A" I need them to be all tab... (16 Replies)
Discussion started by: mytouchsr
16 Replies

5. UNIX for Dummies Questions & Answers

Extracting rows from a space delimited text file based on the values of a column

I have a space delimited text file. I want to extract rows where the third column has 0 as a value and write those rows into a new space delimited text file. How do I go about doing that? Thanks! (2 Replies)
Discussion started by: evelibertine
2 Replies

6. Shell Programming and Scripting

Help with converting Pipe delimited file to Tab Delimited

I have a file which was pipe delimited, I need to make it tab delimited. I tried with sed but no use cat file | sed 's/|//t/g' The above command substituted "/t" not tab in the place of pipe. Sample file: abc|123|2012-01-30|2012-04-28|xyz have to convert to: abc 123... (6 Replies)
Discussion started by: karumudi7
6 Replies

7. UNIX for Advanced & Expert Users

merge two tab delimited file with exact same number of rows in unix/linux

Hi I have two tab delimited file with different number of columns but same number of rows. I need to combine these two files in such a way that row 1 in file 2 comes adjacent to row 1 in file 1. For example: The content of file1: field1 field2 field3 a1 a2 a3 b1 b2 b3... (2 Replies)
Discussion started by: mary271
2 Replies

8. UNIX for Dummies Questions & Answers

How to echo space or tab delimited values into rows?

Hi, I have the following code: LIST=`ls | grep '.sql$'` echo $LIST The above code will give me something like.. file1.sh file2.sh file3.sh file4.sh file5.sh I want to display the values into rows using echo like... file1.sh file2.sh (5 Replies)
Discussion started by: adshocker
5 Replies

9. Shell Programming and Scripting

Retrieving values from tab-delimited file in unix script

Hi I am trying to retrieve values from a tab-delimited file.I am using while read record value=`echo $record | cut -f12` done Where 12 is the column no i want retieve and record is one line of the file. But it is returning the full record. Plz help (4 Replies)
Discussion started by: akashtcs
4 Replies

10. Shell Programming and Scripting

So I converted columns to rows but I want it to be tab delimited and also I want.....

Hi, So my file looks like this: title number JR 2 JR 2 JR 4 JR 5 NM 5 NM 8 NM 2 NM 8 I used this line that I wrote to convert it to rows so it will look like this: awk -F"\t" '!/^$/{a=a" "$3} END {for ( i in a) {print i,a}}' occ_output.tab > test.txt JR 2 2 4 5 NM 5 8... (4 Replies)
Discussion started by: kylle345
4 Replies
Login or Register to Ask a Question