Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to sort values inside one column?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to sort values inside one column?
# 1  
Old 11-03-2009
How to sort values inside one column?
Hi,

Could someone please help me with this? I have a text file that has fields seperated by comma. The last column in it has multiple values seperated by "|". I need to sort values in the last column seperated by pipe..is there any way I can do this through script?

Sample text file -
Code:
TEST1,ABC,5|4|1|3|2
TEST2,DEF,10|7|25|11|52|19
TEST3,XYZ,5|11|52|1

The output should generate -
Code:
TEST1,ABC,1|2|3|4|5
TEST2,DEF,7|10|11|19|25|52
TEST3,XYZ,1|5|11|52


How can I achieve this? Smilie

---------- Post updated at 04:47 PM ---------- Previous update was at 03:46 PM ----------

Could anyone help please??
# 2  
Old 11-03-2009
Try this:

Code:
$ cat ./sort_fields.ksh
#!/bin/ksh

sort_array()
{
  set -A LOCALARRAY $1

  LOCALARRAY_COUNT=${#LOCALARRAY[*]}

  IDX=0
  TEMP=0
  while (( $IDX < $LOCALARRAY_COUNT ))
  do
    IDX2=`expr $IDX + 1`
    while (( $IDX2 < $LOCALARRAY_COUNT ))
      do
        if (( ${LOCALARRAY[$IDX]} > ${LOCALARRAY[$IDX2]} ))
        then
           TEMP=${LOCALARRAY[$IDX]}
           LOCALARRAY[$IDX]=${LOCALARRAY[$IDX2]}
           LOCALARRAY[$IDX2]=$TEMP
        fi
        IDX2=`expr $IDX2 + 1`
      done
    IDX=`expr $IDX + 1`
  done

 print "${LOCALARRAY[*]}"

}

while read LINE
do
 LINEPT1=${LINE%,*}
 set -A FIELDS $(echo $LINE | cut -d',' -f3 |cut -d'|'  --output-delimiter=' ' -f1-)
 LINEPT2=$(sort_array "${FIELDS[*]}"|cut -d' ' --output-delimiter='|' -f1-)
 print "${LINEPT1},${LINEPT2}"

done < test.dat

exit 0

$ cat ./test.dat
TEST1,ABC,5|4|1|3|2
TEST2,DEF,10|7|25|11|52|19
TEST3,XYZ,5|11|52|1

$ ./sort_fields.ksh
TEST1,ABC,1|2|3|4|5
TEST2,DEF,7|10|11|19|25|52
TEST3,XYZ,1|5|11|52

# 3  
Old 11-03-2009
I still consider myself to definitely be a beginner with awk but your question intrigued me so I worked at it a bit. The following doesn't meet 100% of your requirement but I just can't figure it out from this point.

Code:
$ awk -F, '{split($3,a,"|"); n=asort(a,b); for (i=1; i<=n; i++) sort=sort "|" b[i]; print $1 "," $2 "," sort}' file

input
Code:
TEST1,ABC,5|4|1|3|2
TEST2,DEF,10|7|25|11|52|19
TEST3,XYZ,5|11|52|1

output
Code:
TEST1,ABC,|1|2|3|4|5
TEST2,DEF,|1|2|3|4|5|7|10|11|19|25|52
TEST3,XYZ,|1|2|3|4|5|7|10|11|19|25|52|1|5|11|52

As you can see the problem I'm having is getting the for loop to only include the parts of the array relevant to the current line.

I'm scratching my head for now, but I know I'll figure it out soon Smilie
# 4  
Old 11-03-2009
Should be a simple solution, but stop to merge the lines. Anyone can help to continue it?

Code:
$ awk -F[\,\|] '{for(i=3;i<=NF;i++) {print $1,$2,$i}}' urfile |sort -k1,1n -k3,3n
TEST1 ABC 1
TEST1 ABC 2
TEST1 ABC 3
TEST1 ABC 4
TEST1 ABC 5
TEST2 DEF 7
TEST2 DEF 10
TEST2 DEF 11
TEST2 DEF 19
TEST2 DEF 25
TEST2 DEF 52
TEST3 XYZ 1
TEST3 XYZ 5
TEST3 XYZ 11
TEST3 XYZ 52

then I need merge the lines which $1 is same.
# 5  
Old 11-03-2009
Code:
sortbars() {
  echo "$1"|xargs -d'|' -n1|sort -n|xargs|tr ' ' '|'
}
while IFS=, read cola colb colc; do
  echo "$cola,$colb,$(sortbars $colc)"
done < infile

Code:
TEST1,ABC,1|2|3|4|5
TEST2,DEF,7|10|11|19|25|52
TEST3,XYZ,1|5|11|52

Alternatively:
Code:
sortbars() {
  IFS='|'; for i in $1; do
    echo $i
  done|sort -n|xargs|tr ' ' '|'
}

# 6  
Old 11-04-2009
perl:

Code:
while(<DATA>){
	chomp;
	my @tmp=split(",",$_);
	my @t1=split("[|]",$tmp[2]);
	$tmp[2]=join "|", sort {$a<=>$b} @t1;
	print join ",",@tmp;
	print "\n";
}
__DATA__
TEST1,ABC,5|4|1|3|2
TEST2,DEF,10|7|25|11|52|19
TEST3,XYZ,5|11|52|1

# 7  
Old 11-04-2009
Thank you all for awesome suggestions!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Use sort to sort numerical column

How to sort the following output based on lowest to highest BE? The following sort does not work. $ sort -t. -k1,1n -k2,2n bfd.txt BE31.116 0s 0s DOWN DAMP BE31.116 0s 0s DOWN DAMP BE31.117 0s 0s ... (7 Replies)
Discussion started by: sand1234
7 Replies

2. UNIX for Advanced & Expert Users

Sort by second column numeric values

From googling and reading man pages I figured out this sorts the first column by numeric values. sort -g -k 1,1 Why does the -n option not work? The man pages were a bit confusing. And what if I want to sort the second column numerically? I haven't been able to figure that out. The file... (7 Replies)
Discussion started by: cokedude
7 Replies

3. UNIX for Dummies Questions & Answers

Sort command in one column and not effect to another column

If my data is numerical : 1 = 101 2 = 102 3 = 104 4 = 104 7 = 103 8 = 103 9 = 105 I need the result like below: 1 = 101 2 = 102 3 = 103 4 = 103 7 = 104 8 = 104 9 = 105 (4 Replies)
Discussion started by: GeodusT
4 Replies

4. Shell Programming and Scripting

Converting odd values to even values(or vice-versa) located in a column

Hello All, I have a below data in a .csv file where all rows where col1 is A, col2 is odd numbers, similarly even numbers for all rows where col1 is B. Note that my data has some other columns(not shown here) too (around 100) after col2. Tool,Data A,1 A,3 A,5 .... so on B,2 B,4 .... ... (4 Replies)
Discussion started by: ks_reddy
4 Replies

5. Shell Programming and Scripting

how to sort inside awk

Hi guys I have a problem trying to sort output produced with the help of 'Awk'. After "grepping" the pattern out of the file and sorting it, sort command acts a little strange: $ grep -w -n -i "p*cmo" /bb/data/rmt4db.lrl | sort -r 32:P1096CMO 63836 344 passthru 31:P1084CMO 121335 329 passthru... (3 Replies)
Discussion started by: aoussenko
3 Replies

6. UNIX for Dummies Questions & Answers

shift values in one column as header for values in another column

Hi Gurus, I have a tab separated text file with two columns. I would like to make the first column values as headings for the second column values. Ex. >value1 subjects >value2 priorities >value3 requirements ...etc and I want to have a file >value1 subjects >value2 priorities... (4 Replies)
Discussion started by: Unilearn
4 Replies

7. Shell Programming and Scripting

Cat Values from Several files if it meets criteria for column values

I have results from some statistical analyses. The format of the results are as given below: I want to select lines that have a p-value (last column) less than 0.05 from all the results files (*.results) and cat to a new results file. It would be very nice if a new column is added that tells... (2 Replies)
Discussion started by: genehunter
2 Replies

8. Shell Programming and Scripting

print unique values of a column and sum up the corresponding values in next column

Hi All, I have a file which is having 3 columns as (string string integer) a b 1 x y 2 p k 5 y y 4 ..... ..... Question: I want get the unique value of column 2 in a sorted way(on column 2) and the sum of the 3rd column of the corresponding rows. e.g the above file should return the... (6 Replies)
Discussion started by: amigarus
6 Replies

9. Shell Programming and Scripting

Question about sort specific column and print other column at the same time !

Hi, This is my input file: ali 5 usa abc abu 4 uk bca alan 6 brazil bac pinky 10 utah sdc My desired output: pinky 10 utah sdc alan 6 brazil bac ali 5 usa abc abu 4 uk bca Based on the column two, I want to do the descending order and print out other related column at the... (3 Replies)
Discussion started by: patrick87
3 Replies

10. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies
Login or Register to Ask a Question