awk split one column into multiple columns

Question

How can split one column data into multiple columns based on column values using awk? Example file and desired output is below. My bash version is 3.2.52(1).

$ cat examplefile
A
1
B
2
B
3
C
10
C
11
C
13
A
4
B
5
B
6
B
7
C
14

Desired output:

$ cat outputfile
A        B      C
1        2      10
null     B      C
null     3      11
null    null    C
null    null    13
A       B       C
4       5       14
null    B       null
null    6       null
null    B       null
null    7       null

Or forget about null values How can I obtain two columns as in the outputfile2?

cat examplefile2
A
1
B
2
B
3
cat outputfile2
A   B
1   2
    B
    3

The purpose is putting A,B,C and their following row into different columns — Mel Simpson, Oct 21 '14 at 08:51
Instead of commenting little things, try and edit your original question with a full good explanation, some examples and what you have tried. Otherwise, it is quite unlikely that people will spend the time to understand the problem and try to figure out a way to solve it. — fedorqui, Oct 21 '14 at 09:24
I am new to awk.Is there a way to split the data in two columns as in outputfile2? — Mel Simpson, Oct 23 '14 at 09:05

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

You can get it:

awk 'BEGIN{l=1;ll="";} {if (l) {ll=$0;l=0;} else {if (length(a[ll])>0) {a[ll]=a[ll]","ll","$0;} else {a[ll]=ll","$0;}l=1;}} END{for (k in a){print a[k];}}' examplefile

It works for any number of classes (A,B,C...).

The output is:

A,1,A,4
B,2,B,3,B,5,B,6,B,7
C,10,C,11,C,13,C,14

If you want it as columns, just have a quick look to the following post: An efficient way to transpose a file in Bash

edited May 23 '17 at 12:27

Community

1
1

answered Oct 23 '14 at 11:44

arutaku

5,937
1
24
38

Thanks for your efforts even though it is not exactly that I want.With your command and transpose code it gives the following output – Mel Simpson Oct 23 '14 at 14:21
A,1,A,4 B,2,B,3,B,5,B,6,B,7 C,10,C,11,C,13,C,14 What I want is the following – Mel Simpson Oct 23 '14 at 14:21
cat outputfile A B C 1 2 10 B C 3 11 C 13 A B C 4 5 14 B 6 B 7 – Mel Simpson Oct 23 '14 at 14:22
split by comma and use an inner for to transpose the input :-) – arutaku Oct 23 '14 at 14:55

awk split one column into multiple columns

1 Answers1