Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Convert a column to a line

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert a column to a line
# 1  
Old 06-16-2014
Convert a column to a line
Hi,

I need to convert my text file into a sort of matrix form. my input file has 5 columns. $2 and $4 will be ignored and omitted from output, while $3 need to be re-arranged horizontally on top:-

Code:
Akd1     aa233     Akd1     545    524.2
jlk01    aa233     Akd1     90     447
mht5     aa233     Akd1     55     324.7
wtr5     aa233     Akd1     4      100.2
wtr5     uit145    wtr5     300    598
ztw2     uit145    wtr5     245    544.9
Akd1     uit145    wtr5     151    300.5
mht5     plrr012   jlk01    825    600
wtr5     plrr012   jlk01    632    522
jlk01    plrr012   jlk01    356    154
ztw2     plrr012   jlk01    320    65
Akd1     rt2       mht5     800    1000.9
mht5     rt2       mht5     658    952
mht5     wre51     ztw2     935    900
jlk01    wre51     ztw2     910    842
ztw2     wre51     ztw2     601    451.3

I need to have an output where like below. :-

Code:
       Akd1     jlk01     mht5     wtr5     ztw2
Akd1   524.2      -      1000.9    300.5     -
jlk01  447       154        -      -        842       
mht5   324.7     600       952     -        900
wtr5   100.2     522        -      598       -
ztw2    -         65        -      544.9    451.3

Anyone please help me. I have thousands of lines like this that i need to work on. Just don't know how should i go about it. Is there any chance that i could do it in awk? Thanks.
# 2  
Old 06-16-2014
Try
Code:
awk     '       {LN[$1]; HD[$3]; MX[$1,$3]=$5}
         END    {               printf "%8s", ""; for (i in HD) printf "%8s", i; print "";  
                 for (j in LN) {printf "%8s",j;   for (i in HD) printf "%8s", MX[j,i]; print ""}  
                }
        ' file
            mht5   jlk01    Akd1    ztw2    wtr5
    mht5     952     600   324.7     900        
   jlk01             154     447     842        
    Akd1  1000.9           524.2           300.5
    ztw2              65           451.3   544.9
    wtr5             522   100.2             598

This User Gave Thanks to RudiC For This Post:
redse171
# 3  
Old 06-16-2014
Quote:
Originally Posted by RudiC
Try
Code:
awk     '       {LN[$1]; HD[$3]; MX[$1,$3]=$5}
         END    {               printf "%8s", ""; for (i in HD) printf "%8s", i; print "";  
                 for (j in LN) {printf "%8s",j;   for (i in HD) printf "%8s", MX[j,i]; print ""}  
                }
        ' file
            mht5   jlk01    Akd1    ztw2    wtr5
    mht5     952     600   324.7     900        
   jlk01             154     447     842        
    Akd1  1000.9           524.2           300.5
    ztw2              65           451.3   544.9
    wtr5             522   100.2             598


Hi RudiC,

Thanks so much for your prompt response. I tried your code and it worked for the sample data that i gave. However, i need the $1 and row 1 in alphabetical order and the specific position that has no values to be indicated with "-" like what i gave in the sample output above. The reason is i have more than 50 columns and thousands of rows to work on and if i dont put "-", the results is a little bit messy for me to check. It is hard for me to check one by one those with no values. I tried to understand and play around with your codes but still i did not get what i want. need further help pls. thanks

---------- Post updated at 05:18 PM ---------- Previous update was at 03:11 PM ----------

Finally, I got the solution...Below is the code that being modified to suit what i need. but i didn't add "-" though. I did tab delimited instead and add newline to arrange the output properly.

Code:
awk -F"\t"    '       {LN[$1]; HD[$3]; MX[$1,$3]=$5}
         END    {   for (i in HD) printf "%s\t", i; printf "\n";
                 for (j in LN) {printf "%s\t",j;   for (i in HD) printf "%s\t", MX[j,i]; printf "\n"; }  
                }
        '  file

thanks for your help. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert Rows into Column

Hi Experts, I have a requirement to convert rows into columns. For e.g. Input File: Output File should be like Appreciate if you could suggest code snippet(may be awk) for above requirement... Thanks in Advance for your help... (3 Replies)
Discussion started by: sai_2507
3 Replies

2. Shell Programming and Scripting

Merge two files line by line and column by column

Hi All, I have two files having oracle query result. I want to merge to files line by line and also with column File1 23577|SYNC TYPE 23578|Order Number|ConnectionState 23585|Service State|Service NameFile2 23577|AR Alarm Sync 23578|A5499|9 23585|7|test_nov7Result... (18 Replies)
Discussion started by: Harshal22
18 Replies

3. Shell Programming and Scripting

convert row to column with respect of first column.

Input file A.txt :- C2062 -117.6 -118.5 -117.5 C5145 0 0 0 C5696 0 0 0 Output file B.txt C2062 X -117.6 C2062 Y -118.5 C2062 Z -117.5... (4 Replies)
Discussion started by: asavaliya
4 Replies

4. Shell Programming and Scripting

convert single line output to multiple line

Hi all, I have a single line output like below echo $ips 10.26.208.28 10.26.208.26 10.26.208.27 want to convert above single line output as below format. Pls advice how to do ? 10.26.208.28 10.26.208.26 10.26.208.27 Regards Kannan (6 Replies)
Discussion started by: kamauv234
6 Replies

5. Shell Programming and Scripting

1st column,2nd column on first line 3rd,4th on second line ect...

I need to take one column of data and put it into the following format: 1st line,2nd line 3rd line,4th line 5th line,6th line ... Thanks! (6 Replies)
Discussion started by: batcho
6 Replies

6. Shell Programming and Scripting

Convert string in a column

Hi I got this: aix.acct,aix.system.config.cron,aix.system.config.src,aix.system.install,string2 and I want this: aix.acct system.config.cron aix.system.config.src aix.system.install string2 I tried using sed by changing ',' into an 'ENTER' but I couldn't. :-( thxs (2 Replies)
Discussion started by: iga3725
2 Replies

7. Shell Programming and Scripting

Convert Column to rows

Hi, I have a file with below contents. Heading1 Heading2 Heading3 Heading4 Value1 Value2 Value3 Value4 The file has only 2 rows and is tab separated The desired output is : Heading1 Value1 Heading2 Value2 Heading3 Value3 Heading4 Value4 CAn you please help? (5 Replies)
Discussion started by: kaponeh
5 Replies

8. UNIX for Dummies Questions & Answers

Convert first line into column

I have a file: 2009_11 3455 500 5355 600 7777 700 The first line is the period. I want to concatenate the Period into every line using a one line command..I guess maybe awk or something. So the output would look like this: 3455 500 2009_11 5355 600 2009_11 7777 700 2009_11 ... (4 Replies)
Discussion started by: alfredo123
4 Replies

9. Shell Programming and Scripting

awk convert from line to column

i have an output like this : 012008 25760883 022008 12273095 032007 10103 032008 10115642 042007 20952798 but i would like to have it like this 012008,25760883 022008,12273095 032007,10103 032008,10115642 042007,20952798 (4 Replies)
Discussion started by: jarmouda
4 Replies

10. UNIX for Dummies Questions & Answers

read a line from a csv file and convert a column to all caps

Hello experts, I am trying to read a line from a csv file that contains '.doc' and print the second column in all caps. e.g. My csv file contains: Test.doc|This is a Test|test1|tes,t2|test-3 Test2.pdf|This is a Second Test| test1|tes,t2|t-est3 while read line do echo "$line" |... (3 Replies)
Discussion started by: orahi001
3 Replies
Login or Register to Ask a Question