Horizontal to vertical


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Horizontal to vertical
# 1  
Old 09-09-2011
Horizontal to vertical

Hi,
Silly question, if I have an excel file that looks something like this:

 
................. Subject 1 Subject 2 Subject 3 Subject 4
Fever..............13...........9.............23..........14
Headache.........2............12...........18..........23
Bleeding...........21...........45...........56.........88
Trauma............11...........23...........78.........22

But I need the information in columns rather than row:

 
................. Fever Headache Bleeding Trauma
Subject 1.......13.........2...........21........11
Subject 2........9.........12..........45........23
Subject 3.......23.........18.........56.........78
Subject 4.......14.........23.........88.........22


I know is a silly question but I need an AWK or perl script. I just do not know how to accomplish it.
Any help will be very much appreciated!
# 2  
Old 09-09-2011
Not a silly question, just a common one. The trouble is, rotating the data usually means storing it entire in memory first since seeking in a text file is difficult.

You don't have to space things with endless numbers of .'s, you can put things in code tags [ code ] stuff [ /code ] and it'll space naturally in a fixed-width font.

I rearranged your data a little, to look like this:
Code:
.               Subject1        Subject2        Subject3        Subject4
Fever           13              9               23              14
Headache        2               12              18              23
Bleeding        21              45              56              88
Trauma          11              23              78              22

The . is there just to let awk know there's anything at all there -- otherwise it'd ignore that blank spot as pure whitespace. And the spaces in the titles have to go if your data's space-separated, since it'd take the header line as twice as many fields as it should.

Code:
$ cat rotate.awk
#!/usr/bin/awk -f
{
        # Record the maximum number of fields for later
        if(MAX<NF)      MAX=NF;
        # Save each line in DATA.  Line (record) 1 goes into DATA[1], etc.
        DATA[NR]=$0;
}

END {
        # Loop through each field
        for(N=1; N<=MAX; N++)
        {
                # Loop through each line, getting record N from line M in turn.
                PREFIX=""       ;       LINE=""
                for(M=1; M<=NR; M++)
                {
                        split(DATA[M], A, FS);
                        # Put the data records into one new line.
                        LINE=LINE PREFIX A[N];
                        PREFIX="\t";
                }
                # Print the line.
                print LINE;
        }
}

$ ./rotate.awk < data
.       Fever   Headache        Bleeding        Trauma
Subject1        13      2       21      11
Subject2        9       12      45      23
Subject3        23      18      56      78
Subject4        14      23      88      22
$

# 3  
Old 09-09-2011
nawk -f transpose.awk OFS='\t' myFile.txt
transpose.awk:
Code:
{for(i=1;i<=NF;i++){a[i,NR]=$i};m=NF;n=NR}
END {
   for(j=1;j<=m;j++)
         for(k=1;k<=n;k++)
           printf("%s%s",a[j,k], (k==n) ? ORS : OFS)
}

This User Gave Thanks to vgersh99 For This Post:
# 4  
Old 09-09-2011
vgersh99

Sweet!
Really, nice!
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Transform vertical into horizontal list

Hi, I am creating a script that will pull data from database. The only thing missing now is that i have to transform the lines into horizontal list. EXAMPLE 2015-07-15 09:00:00.0 |TCSERVER01 |5354 2015-07-15 09:01:00.0 |TCSERVER01 |6899 ... (5 Replies)
Discussion started by: reignangel2003
5 Replies

2. UNIX for Dummies Questions & Answers

Print vertical to horizontal

Hi Masters, I need help to change my vertical data to horisontal input 2015-04-13|JS|741667 2015-04-13|JSJ|2272 2015-04-13|TMS|107099 2015-04-12|JMD|47945 2015-04-13|TM|760024 2015-04-13|JM|484508 2015-04-14|JMJ|318 2015-04-14|JSD|54436 2015-04-13|JM|15410 Output... (2 Replies)
Discussion started by: radius
2 Replies

3. UNIX for Dummies Questions & Answers

Change Vertical to Horizontal

I need to change data from vertical to horizontal but with condition input USA|80 AUS|40 BRA|33 VEGAS|40 KENTUCKY|50 NEWYORK|21 DARWIN|33 ADELAIDE|21 SAOPAOLO|44 RIO|89 GAPIZA|44 BENFLEX|32 AXIS|44 ACRE|56 HEIGHT|22 (5 Replies)
Discussion started by: radius
5 Replies

4. Shell Programming and Scripting

Help! output format from vertical to horizontal

Hi All, please help to achieve the desired output Example: I have a file which contains the below data empname robert empid 787 design consultant empname alex empid 898 design advocate Desired output should be empname empid design robert 787 consultant (19 Replies)
Discussion started by: rocky2013
19 Replies

5. Shell Programming and Scripting

How do i do the vertical to horizontal??

51009 8746 8912 17986 20315 24998 5368 38934 7805 8566 (4 Replies)
Discussion started by: nikhil jain
4 Replies

6. Shell Programming and Scripting

awk in horizontal and vertical math

Based on input ail,UTT,id1_0,COMBO,21,24,21,19,85 al,UTHAST,id1_0,COMBO,342,390,361,361,1454 and awk code as awk -F, '{ K=0; for(i=NF; i>=(NF-4); i--) { K=K+$i; J=J+$i;} { print K } } END { for ( l in J ) printf("%s ",J); }' I'm trying to add columns and lines in single line. line... (6 Replies)
Discussion started by: busyboy
6 Replies

7. Shell Programming and Scripting

Vertical And Horizontal Pivoting

Hi All, My Input data is: A=1 B=2 My desired Output should be: A|B 1|2 Thanks in advance... (3 Replies)
Discussion started by: kmsekhar
3 Replies

8. UNIX for Dummies Questions & Answers

vertical to horizontal

dear all, i'm new to unix and i try to figure out the best case for making list of vertical text to become horizontal and skip the line 1 and 2. example text : Data DATE XXXXX MAX 47 53 49 51 48 48 7 46 51 8 25 (6 Replies)
Discussion started by: andrisetia
6 Replies

9. Shell Programming and Scripting

combine files in horizontal way, not vertical.

Hi Everyone, I have three files. FileA: aaaa aaaa bb ccc FileB: 21 2 FileC: eeeeeee e eee ee Would like to combine three of them, not like cat, to cat three files, but the output should be like: (3 Replies)
Discussion started by: jimmy_y
3 Replies

10. Shell Programming and Scripting

Vertical an horizontal pivoing in unix

Please help me to do Vertical an horizontal pivoing in unix in single run. The input file is like this- MRKT|PROD|PRD|FACT1|FACT2|FACT3|FACT4 M1|P1|PR1|F11|F12|F13|F14 M1|P1|PR2|F21|F22|F23|F24 M1|P1|PR3|F31|F32|F33|F34 M2|P2|PR1|F41|F42|F43|F44 M2|P2|PR2|F51|F53|F54|F55... (4 Replies)
Discussion started by: marut_ashu
4 Replies
Login or Register to Ask a Question