How to convert a single column into several columns?

Shell Programming and Scripting
1

Hi

I have a ksh script which gives me the output as a single column with several rows like:

AAA
BBB
CCC
DDD
EEE
FFF
GGG
HHH
III

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 3 columns, the output should look like

file.txt:

AAA DDD GGG
BBB EEE HHH
CCC FFF III

Is it possible to do this in Unix using sed command and in comma separated?

I tried

cat file.txt | sed 'N;N;s/\n/,/g'

but its giving me wrong output.

Thank You,
Pinpe

2

Try this.

cgi@tioman> (/home/cgi) $ awk -v col=3 '{if(NR%col){printf "%s ",$0 }else {printf "%s\n",$0}} ' test.txt
AA BBB CCC
DDD EEE FFF
GGG HHH III
cgi@tioman> (/home/cgi) $

But i think you can set any one rows / columns and the other goes depends on your file size.

3 
perl -0ane 'BEGIN{$cols=3}END{for($i=0;$i<=$#F;$i++){print "$F[$i]\t"; print "\n" if((($i+1) % $cols) == 0)}}' inp

Her I have given the cols a 3. You can change it as per ur req

4

Hi getmmg/kumaran_5555,

Thanks for the prompt response but..

My desired output should be like this...

AAA DDD GGG
BBB EEE HHH
CCC FFF III

Your script output is this...

AAA BBB CCC
DDD EEE FFF
GGG HHH III

Br,
Pete

5

Using Awk

awk '{A[(NR-1)%3]=A[(NR-1)%3]$0" ";next}END{for(i in A)print A}' your_infile
6

We all missed that. :slight_smile:
Try this one, again you have freedom to choose only the rows. This works on linux.

cgi@tioman> (/home/cgi) $ awk -v row=3 '{arr[NR%row]=arr[NR%row]" "$0} END{n=asort(arr);for(i=1;i<=n;i++){print arr}} ' test.txt
 AA DDD GGG
 BBB EEE HHH
 CCC FFF III
7

This should work

perl -lne 'BEGIN{$cols=3;$i=0}; $hash{$i}.= "$_ "; $i++; $i=0 if($i == $cols); END{print $_ for values %hash}' inp
8

how does awk ensures the array's index order. Usually it has arbitrary order, an you may end up with different line order in the output.

9

Hi getmmg,

Still does not work for my desired output. Let me clear my input and outful files as shown below...

inputfile.txt:


AAA
BBB
CCC
DDD
EEE
FFF
GGG
HHH
III
JJJ
KKK
LLL
MMM
NNN
OOO

output.txt file is like this...


AAA     FFF     KKK
BBB     GGG     LLL
CCC     HHH     MMM
DDD     III     NNN
EEE     JJJ     OOO

Thanks in advance.

Br,
Pinpe

10

Have you tried my command,

it worked fine.

cgi@tioman> (/home/cgi) $ awk -v row=5 '{arr[NR%row]=arr[NR%row]" "$0} END{n=asort(arr);for(i=1;i<=n;i++){print arr}} ' test.txt
 AAA FFF KKK
 BBB GGG LLL
 CCC HHH MMM
 DDD III NNN
 EEE JJJ OOO
11

Hi kumaran_5555,

I'm getting error...

root@pinpe>awk -v row=5 '{arr[NR%row]=arr[NR%row]" "$0} END{n=asort(arr);for(i=1;i<=n;i++){print arr}}' inputfile.txt
awk: syntax error near line 1
awk: bailing out near line 1

Thanks.

Br,
Pinpe

12

Hi Pinpe,

if you are using solaris...please use nawk instead of awk as Kumaran has mentioned he tried in Linux.

Thanks
Sha

13

Hi getmmg,

This one works for me but it somehow re-arranged the position of the values. My inputfile is actually a list of numbers and not letters. Can you please modify a little this code of yours? Thanks.

389    
57     
4      
29     
51     
382    
527    
10625  
87868  
18     
5      
1807367
8614   
730    
7025

The output should be like this...

389     382       5      
57      527       1807367
4       10625     8614   
29      87868     730    
51      18        7025

Thanks in advance.

Br,
Pinpe

14

Pinpe,

This should work

 
 
perl -0ane 'BEGIN{$cols=5;$k=0}END{$total=($#F+1)/$cols;foreach(@F){$hash{$k}.= "$_\t";$k++; $k=0 if($k == $total)}; print "$_\n" for values %hash}' inp

I've got following output when the cols was given as 5 and then as 3.
Let me know if this is what you require.

Output I got

 
 
5 cols
 
AAA DDD GGG JJJ MMM
BBB EEE HHH KKK NNN
CCC FFF III LLL OOO
 
3 cols
 
AAA FFF KKK
BBB GGG LLL
CCC HHH MMM
DDD III NNN
EEE JJJ OOO
15

Hi getmmg,

Can you please use the input file below. So instead of letter you will use numbers.

389    
57     
4      
29     
51     
382    
527    
10625  
87868  
18     
5      
1807367
8614   
730    
7025

The output should be like this...

389     382       5      
57      527       1807367
4       10625     8614   
29      87868     730    
51      18        7025

Thanks in advance.

Br,
Pinpe

16

This is what I've got

 
389     382     5
57      527     1807367
4       10625   8614
29      87868   730
51      18      7025
17

Hi getmmg,

What do you think its giving me a wrong output...?

root@pinpe>perl -0ane 'BEGIN{$cols=3;$k=0}END{$total=($#F+1)/$cols;foreach(@F){$hash{$k}.= "$_\t";$k++; $k=0 if($k == $total)}; print "$_\n" for values %hash}' test.txt 
51      18      7025    
57      527     1807367 
29      87868   730     
389     382     5       
4       10625   8614

Thanks,
Pete

18 
gawk -v row=3 '{arr[NR%row]=arr[NR%row]" "$0} END{n=asort(arr);for(i=1;i<=n;i++){print arr}} ' test.txt

Try this one with gawk/nawk, this should work

19

Hello Pinpe,

I guess it's giving the output properly ( order is missing ) . Check it out how to get the output in the order you read!!!

Regards
Ravi

20