Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to convert a single column into several columns?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to convert a single column into several columns?
# 22  
Old 06-06-2011
Code:
#!/usr/bin/perl -w

my $COLS = 3; # or shift 

my $maxlength = 0;
my @words = ();
while (<>) {
    s/\s+$//;
    $maxlength = length if length > $maxlength;
    push @words, $_;
}

$maxlength++;

my $rows = int @words/$COLS;
my @lasts = splice @words, ($COLS*$rows);

for (my $i = 0; $i < $rows; $i++) {
    for (my $j = 0; $j < $COLS; $j++) {
        my $word = $words[$i + $j*$rows];
        printf "%-${maxlength}s", $word;
    }
    print "\n";
}
if (@lasts) {
    printf "%-${maxlength}s", $_ for @lasts;
    print "\n";
}

Not very perlish although.
# 23  
Old 06-06-2011
Quote:
Originally Posted by pinpe
Hi kumaran_5555,

My OS is Sun Solaris Unix based. Can you convert your code to Unix please... It is giving me an error...

Code:
root@pinpe>nawk -v row=3 '{arr[NR%row]=arr[NR%row]" "$0} END{n=asort(arr);for(i=1;i<=n;i++){print arr[i]}} ' test.txt
nawk: calling undefined function asort
 input record number 81, file test.txt
 source line number 1
root@pinpe>gawk -v row=3 '{arr[NR%row]=arr[NR%row]" "$0} END{n=asort(arr);for(i=1;i<=n;i++){print arr[i]}} ' test.txt

CORRECT>awk -v row=3 '{arr[NR%row]=arr[NR%row]" "$0} END{n=asort(arr);for(i=1;i<=n;i++){print arr[i]}} ' test.txt (y|n|e|a)?

Thanks in advance.


Br,
Pete

I couldn't make it using awk in solaris,

Here is bash script which will work any where, change the rows variable as you need.
Code:
#!/bin/bash

rows=3
j=0
i=0

while read line
do
        key=`expr $i % $rows`
        if [ $j -lt $key ]
        then
                j=$key
        fi
        arr[$key]=${arr[$key]}" "$line
        i=`expr $i + 1`
done  < test.txt

i=0
while [ $i -le $j ]
do    
    echo ${arr[$i]}
    i=`expr $i + 1`
done

# 24  
Old 06-06-2011
an awk-one; I hope some one from this forum will give more elegant "awk" script.

Code:
 
awk '1' ORS=" " input_file > modifiled_file
 
echo "\n" >> modifiled_file
 
awk '{ for(i=1;i<=NF;i++) { for(j=i;j<=NF;j=j+5) {printf "%s ", $j ;$j=""};printf "\n";}}' modifiled_file | sed '/^[ ]*$/d'

# 25  
Old 06-06-2011
Hi getmmg,

YES! now its working. But please can you try one more time the input file below. This is my "actual" file. Hope you can spare more time on trying to run this again to give 3 columns in order. Thank you so much mate!!

input file:
Code:
382
527
10625
87868
18
5
1807367
8614
730
7025
375
837
3558
15530
5363
7807
368
681
12372
18789
13
254508
366
491
697
20194
18
5
1341408
4419
666
5618
362
837
2645
1272
5046
7690
332
139
11201
18717
13
9760
0
0
10
10
0
0
42629
96
1
247
0
0
3
8
58
0
0
3
24
23
0
0


output file:
Code:
382         366         0    
527         491         0    
10625       697         10   
87868       20194       10   
18          18          0    
5           5           0    
1807367     1341408     42629
8614        4419        96   
730         666         1    
7025        5618        247  
375         362         0    
837         837         0    
3558        2645        3    
15530       1272        8    
5363        5046        58   
7807        7690        0    
368         332         0    
681         139         3    
12372       11201       24   
18789       18717       23   
13          13          0    
254508      9760        0


Br,
Pete
# 26  
Old 06-06-2011
This worked for me:

Code:
 
 
 
awk '1' ORS=" " input_file  | awk 'BEGIN {$0=$0"\n"}1'|awk '{ for(i=1;i<=NF;i++) { for(j=i;j<=NF;j=j+NF/3) {printf "%s\t", $j ;$j=""};printf "\n";}}' |  | awk 'NF>1'

where 3-decides how many columns you want!!!
Last edited by panyam; 06-06-2011 at 01:58 PM.. Reason: did with only awk
# 27  
Old 06-06-2011
Problem happened when it was getting sorted. It sorts row 1,10...19,then 2,3 etc.,

I have added formatting for rows to fix that

Hope this code will your issue.

Code:
perl -0ane 'BEGIN{$cols=3;$k=0}END{foreach(@F){$j=sprintf("%03d",++$k);$hash{$j}.= "$_\t";$k=0 if($k == (($#F+1)/$cols))}; print "$hash{$_}\n" for sort keys %hash}' input

Cheers.
This User Gave Thanks to getmmg For This Post:
pinpe
# 28  
Old 06-07-2011
It was rather easy in awk, i was little confuesed,


If you have rows as input,

Code:
 nawk -v rows=22 '{arr[(NR-1)%rows]=arr[(NR-1)%rows]"\t"$0} END{for(i=0;i<rows;i++){print arr[i]}}' test.txt

if you have columns as input, 3 represents your number of columns

Code:
nawk -v rows=$(echo `wc -l test.txt|cut -d' ' -f1` / 3 |bc) '{arr[(NR-1)%rows]=arr[(NR-1)%rows]"\t"$0} END{for(i=0;i<rows;i++){print arr[i]}}' test.txt

This one works on solaris with standard nawk

Output
Code:
        382     366     0
        527     491     0
        10625   697     10
        87868   20194   10
        18      18      0
        5       5       0
        1807367 1341408 42629
        8614    4419    96
        730     666     1
        7025    5618    247
        375     362     0
        837     837     0
        3558    2645    3
        15530   1272    8
        5363    5046    58
        7807    7690    0
        368     332     0
        681     139     3
        12372   11201   24
        18789   18717   23
        13      13      0
        254508  9760    0
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert row to columns start from nth column

Dear All, We have input like this: 161 57 1378 176 1392 262 1444 441 1548 538 1611 670 1684 241 57 1378 208 1393 269 1447 444 1549 538 1610 677 1700 321 ... (4 Replies)
Discussion started by: attila
4 Replies

2. Shell Programming and Scripting

Convert single column into multiple rows

Convert Single column to multiple rows file a.txt contains data like below Server=abc Run=1 Tables=10 Sessions=16 Time=380 Jobs=5 Server=abc Run=2 Tables=15 Sessions=16 Time=400 Jobs=5 Server=abc Run=3 Tables=20 Sessions=16 Time=450 (5 Replies)
Discussion started by: sol_nov
5 Replies

3. Shell Programming and Scripting

Multiple columns to a single column

I have this input: 10 22 1 100 11 22 10 1 50 14 3 1 100 23 3 1 100 24 15 1 100 10 22 5 3 1 33.333 11 22 1 100 It has an inconsistent number of fields but the last field is determined by 100/(NF-2) using awk. I want to take this multiple columned input file and transform so that... (2 Replies)
Discussion started by: mdlloyd7
2 Replies

4. Shell Programming and Scripting

convert columns into rows with respect to first column

Hello All, Please help me with this file. My input file (Tab separated) is like: Abc-01 pc1 -0.69 Abc-01 E2cR 0.459666666666667 Abc-01 5ez.2 1.2265625 Xyz-01 pc1 -0.153 Xyz-01 E2cR 1.7358 Xyz-01 5ez.2 2.0254 Ced-02 pc1 -0.5714 Ced-02 ... (7 Replies)
Discussion started by: mira
7 Replies

5. Shell Programming and Scripting

Convert columns to single row

Hello all I have data like 1 2 3 4 5 I wish my output would be like 1,2,3,4,5 For this i have executed 'BEGIN {FS="\n"; ORS=","} {print $0}' test and got the output as 1,2,3,4,5, I do not want to have , at the end of 5. output should be like (5 Replies)
Discussion started by: vasuarjula
5 Replies

6. Shell Programming and Scripting

how to introduce a space in a single column without distrubing the other columns

Hello Experts, I am new to this forum, I would like to do the following changes in one of the column of a txt file, which is having around 9 column. For example, column 3 is having letters like this AB11 AB12 C CA CB AC1 AC2 I would like to convert the same column as follows ... (5 Replies)
Discussion started by: Fredrick
5 Replies

7. Shell Programming and Scripting

Convert two column data into 8 columns

Apologies if this has been covered - I did search but couldn't find what I was looking for. I have a simple X-Y input file. I want to convert it from two columns into 8 columns - 4 pairs of X-Y data. So my input file looks like X1 Y1 X2 Y2 X3 Y3 X4 Y4 X5 Y5 etc And I want it to look... (8 Replies)
Discussion started by: NickC
8 Replies

8. UNIX for Dummies Questions & Answers

How to convert a single column into several rows and columns?

I have a program which gives me the output as a single column with hundreds of rows like: 213 314 324 324 123 I want to be able to create a new file from this file which allows me to set the number of rows and columns in the new file, i.e. for this example, if I specify 3 rows and 2... (5 Replies)
Discussion started by: ashton_smith
5 Replies

9. Shell Programming and Scripting

Single column to multiple columns in awk

Hi - I'm new to the awk programming language. I'm trying to print a single column of data to several columns, and I found an article on iTWorld.com (ITworld.com - Printing in columns). It looks like the mkCols2 script is very close to what I need to do, but it looks like the end of the code... (2 Replies)
Discussion started by: astroDave
2 Replies

10. UNIX for Dummies Questions & Answers

single column to multiple columns

Hello, I have a single column of data that I would like to cut/print (with awk or ...) into multiple columns at every empty row (or common character). Input: 5.99123 5.94693 7.21383 5.95202 0.907935 5.99149 6.08427 0.975774 6.077 Output: 5.99123 5.95202 6.08427 5.94693... (7 Replies)
Discussion started by: agibbs
7 Replies
Login or Register to Ask a Question