Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sort data from column to row

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sort data from column to row
# 1  
Old 02-10-2012
Sort data from column to row
Hi, I need somebody's help with sorting data with awk.
I've got a file:
Code:
        10         aaa            4584
        12         bbb            6138
        20         ccc            4417
        21         ddd            7796
        10         eee            7484
        12         fff            9503
        20         ggg            7034
        21         hhh            9652
        10         iii            4894
        12         jjj            6011
        20         kkk            8504
        21         lll            4691
        10         mmm            2487
        12         nnn            3078
        20         ooo            7903
        21         ppp            6722

I need to to sort this data to get output like this:
Code:
        10         aaa         eee         iii         mmm
        12         bbb         fff         jjj         nnn 
        20         ccc         ggg         kkk         ooo 
        21         ddd         hhh         lll         ppp

# 2  
Old 02-10-2012
With Perl:

Code:
perl -lane'
  push @{$_{$F[0]}}, $F[1];
  END {
    print join "\t", $_, sort, @{$_{$_}} 
      for sort { $a <=> $b } keys %_
    }' infile

Which version of awk do you have?

Update:

It seams that the second column is already ordered, if this is true, that task is easier.
With GNU awk < 4 it would be:

Code:
WHINY_USERS= gawk 'END {
  for (k in _)
    printf "%d%s%s\n", k, OFS, _[k]
  }
{
  k = sprintf("%15s", $1)
  _[k] = k in _ ? _[k] OFS $2 : $2
  }' OFS='\t' infile

With GNU awk >= 4:

Code:
awk 'END {
  PROCINFO["sorted_in"] = "@ind_str_asc"
  for (k in _)
    print k, _[k]
  }
{
  _[$1] = $1 in _ ? _[$1] OFS $2 : $2
  }' OFS='\t' infile

For other awk implementations:

Code:
sort -k1,1n -k2,2 infile |
  awk 'END {
    for (i = 0; ++i <= c;)
      print o[i], _[o[i]]
    }
  {
   t[$1]++ || o[++c] = $1
   _[$1] = $1 in _ ? _[$1] OFS $2 : $2
    }' OFS='\t'

Last edited by radoulov; 02-10-2012 at 05:23 PM..
# 3  
Old 02-10-2012
Actually I don't know how to check the version. Is that
mawk 1.3.3-15 that you asking about?

In regards to the topic I don't need to change order of column expressions. So sorting is not necessary. Thank you I will try it.
# 4  
Old 02-10-2012
With mawk you should use the last one.

Update:

Actually, if the second column is already ordered, this should be sufficient:

Code:
awk 'END {
    for (k in _)
      print k, _[k]
    }
  {
   _[$1] = $1 in _ ? _[$1] OFS $2 : $2
    }' OFS='\t' infile |
      sort

# 5  
Old 02-10-2012
Thanks man! It works perfectly.
# 6  
Old 02-10-2012
You're welcome!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Column to Row Data.

HI Guys, I have below Input :- X L1 5 Y L1 10 Z L1 15 X L2 20 Y L2 12 Z L2 15 X L3 100 Y L3 Z L3 300 Output:- ID L1 L2 L3 X 5 10 15 Y 20 12 15 Z 100 Null 300 (11 Replies)
Discussion started by: pareshkp
11 Replies

2. Shell Programming and Scripting

Replace First Column and First Row Data

HI Guys, I just want to replace data for First Column and Row Cell(1,1) Input :- Hello A B C X 1 2 3 Y 4 5 6 Z 7 8 9 Output:- Byee A B C X 1 2 3 Y 4 5 6 Z 7 8 9 From Hello to Byee .....And The Each file have Different String. (3 Replies)
Discussion started by: pareshkp
3 Replies

3. Shell Programming and Scripting

Convert Data from Column to Row

Hi FileA.txt E_TIM 16, ETE 15, EOND 26, EEC 81, E_1 un, E_2 un, E_3 un, E_4 284, E_TIM 17, ETE 15, EOND 29, EEC 82, E_1 un, E_2 un, E_3 un, E_4 249, (6 Replies)
Discussion started by: asavaliya
6 Replies

4. Shell Programming and Scripting

awk transpose column to row and sort

I am trying to awk the output from below output for each port: i need separate line with comma source file Output required (3 Replies)
Discussion started by: ranjancom2000
3 Replies

5. UNIX for Advanced & Expert Users

Convert column data to row data using shell script

Hi, I want to convert a 3-column data to 3-row data using shell script. Any suggestion in this regard is highly appreciated. Thanks. (4 Replies)
Discussion started by: sktkpl
4 Replies

6. Shell Programming and Scripting

Sort a the file & refine data column & row format

cat file1.txt field1 "user1": field2:"data-cde" field3:"data-pqr" field4:"data-mno" field1 "user1": field2:"data-dcb" field3:"data-mxz" field4:"data-zul" field1 "user2": field2:"data-cqz" field3:"data-xoq" field4:"data-pos" Now i need to have the date like below. i have just... (7 Replies)
Discussion started by: ckaramsetty
7 Replies

7. Shell Programming and Scripting

Moving data from a specified column/row to another column/row

Hello, I have an input file like the following: 11_3_4 2_1_35 3_15__ _16989 Where '_' is a space. The data is in a table. Is there a way for the program to prompt the user for x1,y1 and x2,y2, where x1,y1 is the desired number (for example x=6 y=4 is a value of 4) and move to a desired spot... (2 Replies)
Discussion started by: jl487
2 Replies

8. Shell Programming and Scripting

row to column and position data in to fixed column width

Dear friends, Below is my program and current output. I wish to have 3 or 4 column output in order to accomodate in single page. i do have subsequent command to process after user enter the number. Program COUNT=1 for MYDIR in `ls /` do VOBS=${MYDIR} echo "${COUNT}. ${MYDIR}" ... (4 Replies)
Discussion started by: baluchen
4 Replies

9. Shell Programming and Scripting

Convert row data to column data

Hi Guys, I have a file as follows: a 1 b 786 c 90709 d 99 a 9875 b 989 c 887 d 111 I want: a 1 9875 b 786 989 (3 Replies)
Discussion started by: npatwardhan
3 Replies

10. Shell Programming and Scripting

Move data from Column to Row

Hi all, I have a file with several columns of data, eg: A B C D 1 2 5 1 2 2 2 2 8 4 4 4 4 2 3 4 10 9 4 4 9 7 1 2 I need to get the values from, say, column B and place them into a string separated by a semicolon, eg: 2;2;4;2;9;7 Does... (4 Replies)
Discussion started by: danhodges99
4 Replies
Login or Register to Ask a Question