How to split one long columns into multiple columns shell script

Question

I have a sequence of value in one column that I would like to split in multiple columns. The number of rows is known only number of columns is changing.

Here my input :

,
0,
3.375,
1,
21,
BEAM
-7.1,
,
1,
-100,
0,
0,
,
0,
,
2,
3.7375,
1,
0,
,
0,
,
0,
9.375,
1,
29,
BEAM
-7.9,
,
1,
-100,
0,
0,
,
0,
,
2,
3.7375,
1,
0,
,
0,

Desired output where number of columns is 2:

,          ,
0,         0,
3.375,     9.375,  
1,         1,
21,        29,
BEAM       BEAM,
-7.1,      -7.9
,          ,
1,         1,
-100,      -100,
0,         0,
0,         0,
,          ,
0,         0,
,          ,
2,         2, 
3.7375,    3.7375,
1,         1,
0,         0,
,          ,
0,         0,

What I tried 1st :

NUMBER=$(cat HBS-A | cut -d"," -f1 | sed '/\--/d' | uniq | wc -l)
pr -ts" " --columns $NUMBER HBS-value | tr -s " " "," | sed 's/^,//' > HBS-table

First, I determine how many sequence are in the file to get the number of columns. THen I use pr command, it works beautifully but when the number of column is limited to 72. Most of the time I have more than 100+ columns.

Then I tried this :

awk -v row=21 '{A[(NR-1)%row]=A[(NR-1)%row]$0" ";next}END{for(i in A)print A[i]}' HBS-value

It will give the right format but all my data is completely scrambled and I don't understand why but when I try with a simple example such as:

A 
B
C
D

I would get :

A C
B D

Any suggestion please ?

EDIT-----

This is my output from the example above :

0,          0,
0,          0,
,           ,
0,          0,
0,          0,
,           ,
2,          2,
3.7375,     3.7375,
1,          1,
0,          0,
,           ,
,           ,
0,          0,
3.375,      9.375,
1,          1,
21,         29,
BEAM        BEAM
-7.1,      -7.9,
,           ,
1,          1,
-100,      -100,

BEAM is supposed to be row #6 but it goes to #17

1. Get the first half of the file. 2. Get the second half of the file. 3. Join using paste. — KamilCuk
– KamilCuk, Commented Mar 23, 2020 at 21:12
@KamilCuk in the case where I have 2184 lines, one sequence is 21 lines, I would need 104 columns. Should I use a loop to cut in half with PR until I reach the final size ? — MKF
– MKF, Commented Mar 23, 2020 at 21:18
Wait, wait. You presented a file that should generate two columns. So cound of columns must be predefined, given? — KamilCuk
– KamilCuk, Commented Mar 23, 2020 at 21:20
How is the count of columns calculated? one sequence is 21 lines - so the count of rows is given? And the count of columns is count of lines in file divided by 21 ? @edit .... because 2184/21 = 104. — KamilCuk
– KamilCuk, Commented Mar 23, 2020 at 21:23

KamilCuk · Accepted Answer · 2020-03-23 21:23:05Z

2

There is utility columns that is part of autogen package on my archlinux, that let's you just specify the count of columns with -c option:

columns --by-columns -w 1 -c $(( $(wc -l <file) / 21 )) -i file

answered Mar 23, 2020 at 21:23

KamilCuk

146k8 gold badges86 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MKF · Accepted Answer · 2020-03-24 13:39:22Z

0

I finally manager to get rid of the limit by adding -w LIMIT, I will put 5000 just to be on the safe side.

So in my case that would be :

NUMBER=$(cat HBS-A | cut -d"," -f1 | sed '/\--/d' | uniq | wc -l)
pr -ts" " --columns $NUMBER HBS-value -w 5000 | tr -s " " "," | sed 's/^,//' > HBS-table

answered Mar 24, 2020 at 13:39

MKF

456 bronze badges

Collectives™ on Stack Overflow

How to split one long columns into multiple columns shell script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related