How to split all columns into multiple columns?

Login or Register to Ask a Question and Join Our Community

How to split all columns into multiple columns?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to split all columns into multiple columns?

12-22-2014

Registered User

24, 0

Join Date: Mar 2014

Last Activity: 2 November 2016, 11:22 PM EDT

Posts: 24

Thanks Given: 12

Thanked 0 Times in 0 Posts

How to split all columns into multiple columns?

Hi, all.

How can I split all columns into multiple columns separated by tab?

Input:

Code:

qq	ee	TT	12	m1
aa	gg	GG	34	2u
zz	dd	hh	56	4h
ww	cc	JJ	78	5y
ss	ff	kk	90	j8
xx	pp	mm	13	p0

Output:

Code:

q	q	e	e	T	T	1	2	m	1
a	a	g	g	G	G	3	4	2	u
z	z	d	d	h	h	5	6	4	h
w	w	c	c	J	J	7	8	5	y
s	s	f	f	k	k	9	0	j	8
x	x	p	p	m	m	1	3	p	0

Thanks.

huiyee1

View Public Profile for huiyee1

Find all posts by huiyee1

12-22-2014

Registered User

2,205, 181

Join Date: Mar 2006

Last Activity: 8 May 2020, 5:01 AM EDT

Location: Bangalore,India

Posts: 2,205

Thanks Given: 31

Thanked 181 Times in 171 Posts

Code:

$ awk ' gsub("\t",X) && gsub(".","&\t") ' file
q       q       e       e       T       T       1       2       m       1
a       a       g       g       G       G       3       4       2       u
z       z       d       d       h       h       5       6       4       h
w       w       c       c       J       J       7       8       5       y
s       s       f       f       k       k       9       0       j       8
x       x       p       p       m       m       1       3       p       0

Code:

$ awk ' gsub(".","&\t") && gsub("\t\t\t","\t") ' file
q       q       e       e       T       T       1       2       m       1
a       a       g       g       G       G       3       4       2       u
z       z       d       d       h       h       5       6       4       h
w       w       c       c       J       J       7       8       5       y
s       s       f       f       k       k       9       0       j       8
x       x       p       p       m       m       1       3       p       0

anbu23

View Public Profile for anbu23

Find all posts by anbu23

12-22-2014

Moderator

3,105, 1,603

Join Date: May 2013

Last Activity: 31 August 2020, 1:46 AM EDT

Location: Chennai

Posts: 3,105

Thanks Given: 1,269

Thanked 1,603 Times in 1,369 Posts

Hello huiyee1,

Following may help you in same.

Code:

awk '{gsub(/[[:space:]]/,X,$0);gsub(/./,"&\t",$0)} 1'  Input_file

Ouptut will be as follows.

Code:

q       q       e       e       T       T       1       2       m       1
a       a       g       g       G       G       3       4       2       u
z       z       d       d       h       h       5       6       4       h
w       w       c       c       J       J       7       8       5       y
s       s       f       f       k       k       9       0       j       8
x       x       p       p       m       m       1       3       p       0

Thanks,
R. Singh

RavinderSingh13

View Public Profile for RavinderSingh13

Find all posts by RavinderSingh13

12-22-2014

Registered User

181, 51

Join Date: Mar 2010

Last Activity: 4 December 2018, 6:47 AM EST

Location: Albany, NY

Posts: 181

Thanks Given: 8

Thanked 51 Times in 44 Posts

sed anyone?

Code:

sed -e 's/[^\t]/&\t/g' -e 's/\t\t/\t/g'

derekludwig

View Public Profile for derekludwig

Find all posts by derekludwig

12-22-2014

Moderator

12,296, 3,792

Join Date: Nov 2008

Last Activity: 1 January 2021, 1:47 AM EST

Location: Amsterdam

Posts: 12,296

Thanks Given: 679

Thanked 3,792 Times in 3,282 Posts

All suggestions have a trailing TAB character, which introduces an extra field.

Like this there would be no trailing TAB:

Code:

awk '{for(i=1; i<=NF; i++) sub(/./,"&\t", $i)}1' FS='\t' OFS='\t' file

or:

Code:

perl -lpe 's/[^\t](?=[^\t])/$&\t/g' file

--

Quote:

Originally Posted by derekludwig

sed anyone?

Code:

sed -e 's/[^\t]/&\t/g' -e 's/\t\t/\t/g'

Except for the trailing tab this works fine, but only GNU sed understands \t...

Last edited by Scrutinizer; 12-22-2014 at 06:30 AM..

This User Gave Thanks to Scrutinizer For This Post:

Scrutinizer

View Public Profile for Scrutinizer

Find all posts by Scrutinizer

12-22-2014

Registered User

25, 2

Join Date: Jul 2014

Last Activity: 7 July 2015, 6:35 AM EDT

Posts: 25

Thanks Given: 0

Thanked 2 Times in 2 Posts

You Can try This too

Code:

awk -F"\t" '{ for(i=1;i<=NF;i++)
                {
                $i=substr($i,1,1)"\t"substr($i,2,1)
                }
                print $0
        }' OFS="\t" exp.txt

Last edited by bharat1211; 12-22-2014 at 06:27 AM.. Reason: Output not pasted as expected

bharat1211

View Public Profile for bharat1211

Find all posts by bharat1211

12-22-2014

Moderator

3,105, 1,603

Join Date: May 2013

Last Activity: 31 August 2020, 1:46 AM EDT

Location: Chennai

Posts: 3,105

Thanks Given: 1,269

Thanked 1,603 Times in 1,369 Posts

Quote:

Originally Posted by bharat1211

You Can try This too

Code:

awk -F"\t" '{ for(i=1;i<=NF;i++)
                {
                $i=substr($i,1,1)"\t"substr($i,2,1)
                }
                print $0
        }' OFS="\t" exp.txt

Hello bharat1211,

Above code will work only if each field has only 2 characters, if any field has more than 2 characters then it will not help. Following may help in same just editing some points to your code.

Let's say we have input file as follows.

Code:

qqq     eee     TTT     122     m12
aaa     ggg     GGG     343     2uu
zzz     ddd     hhh     566     4hh
www     ccc     JJJ     788     5yy
sss     fff     kkk     900     j89
xxx     ppp     mmm     133     p01

Code:

awk -F"\t" '{for(i=1;i<=NF;i++){for(j=1;j<=length($i);j++){A=A?A OFS substr($i,j,1):substr($i,j,1)}};print A;A=""}' OFS="\t"  Input_file

Output will be as follows.

Code:

q       q       q       e       e       e       T       T       T       1       2       2       m       1       2
a       a       a       g       g       g       G       G       G       3       4       3       2       u       u
z       z       z       d       d       d       h       h       h       5       6       6       4       h       h
w       w       w       c       c       c       J       J       J       7       8       8       5       y       y
s       s       s       f       f       f       k       k       k       9       0       0       j       8       9
x       x       x       p       p       p       m       m       m       1       3       3       p       0       1

Thanks,
R. Singh

---------- Post updated at 07:58 AM ---------- Previous update was at 07:38 AM ----------

Quote:

Originally Posted by Scrutinizer

All suggestions have a trailing TAB character, which introduces an extra field.

Like this there would be no trailing TAB:

Code:

awk '{for(i=1; i<=NF; i++) sub(/./,"&\t", $i)}1' FS='\t' OFS='\t' file

or:

Code:

perl -lpe 's/[^\t](?=[^\t])/$&\t/g' file

--

Except for the trailing tab this works fine, but only GNU sed understands \t...

Hello Scrutinizer,

Thank you for nice coode

, if we have more than 2 characters in fields, then following may help.

Code:

awk '{for(i=1; i<=NF; i++) gsub(/./,"&\t", $i)}1' FS='\t' Input_file

Output will be as follows.

Code:

q       q       q        e      e       e        T      T       T        1      2       2        m      1       2
a       a       a        g      g       g        G      G       G        3      4       3        2      u       u
z       z       z        d      d       d        h      h       h        5      6       6        4      h       h
w       w       w        c      c       c        J      J       J        7      8       8        5      y       y
s       s       s        f      f       f        k      k       k        9      0       0        j      8       9
x       x       x        p      p       p        m      m       m        1      3       3        p      0       1

Thanks,
R. Singh

RavinderSingh13

View Public Profile for RavinderSingh13

Find all posts by RavinderSingh13

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Split into multiple files by using Unique columns in a UNIX file

I have requirement to split below file (sample.csv) into multiple files by using the unique columns (first 3 are unique columns) sample.csv 123|22|56789|ABCDEF|12AB34|2019-07-10|2019-07-10|443.3400|1|1 123|12|5679|BCDEFG|34CD56|2019-07-10|2019-07-10|896.7200|1|2...

2. Shell Programming and Scripting

Split multi columns line to 2 columns

I have data like this 1 a,b,c 2 a,c 3 b,d 4 e,f would like convert like this 1 a 1 b 1 c 2 a 2 c 3 b 3 d 4 e 4 f Please help me out

3. Shell Programming and Scripting

Split 5 columns into 3 columns

I have a big data set as follows 8.519 8.601 8.833 9.183 9.602 1.003 1.041 1.070 1.085 1.084 1.06 1.034 9.879 9.307 8.66 I simply want this to arrange like this 8.519 8.601 8.833 9.183 9.602 1.003 1.041 1.070 1.085 1.084 1.06 1.034 9.879 9.307 8.66 ...

4. Shell Programming and Scripting

Split text separated by ; in a column into multiple columns

Hi, I need help to split a long text in a column which is separated by ; and i need to print them out in multiple columns. My input file is tab-delimited and has 11 columns as below:- aRg02004 21452 asdfwf 21452 21452 4.6e-29 5e-29 -1 3 50 ffg|GGD|9009 14101.10 High class -node. ; ffg|GGD|969...

5. UNIX for Dummies Questions & Answers

Split a column into multiple columns at certain character count

Hey everyone, I have an issue with a client that is passing me a list of values in one column, and occasionally the combination of all the values results in more than an 255 character string. My DB has a 255 character limit, so I am looking to take the column (comma delimited file), and if it...

6. Shell Programming and Scripting

awk script to split file into multiple files based on many columns

So I have a space delimited file that I'd like to split into multiple files based on multiple column values. This is what my data looks like 1bc9A02 1 10 1000 FTDLNLVQALRQFLWSFRLPGEAQKIDRMMEAFAQRYCQCNNGVFQSTDTCYVLSFAIIMLNTSLHNPNVKDKPTVERFIAMNRGINDGGDLPEELLRNLYESIKNEPFKIPELEHHHHHH 1ku1A02 1 10...

7. Shell Programming and Scripting

Split columns

Greetings, I have an input file with two columns. The first column has different codes. Each time I have a new code I need to split the columns into new ones. The input file looks like this CR124 1320 CR124 1138 CR124 682 CR124 629 CR124 592 CR124 580 CR124 537 CR161 3967 CR161 3656...

8. UNIX for Dummies Questions & Answers

Split columns

Greetings, I have an input file with two columns. The first column has different codes. Each time I have a new code I need to split the columns into new ones. The input file looks like this CR124 1320 CR124 1138 CR124 682 CR124 629 CR124 592 CR124 580 CR124 537 CR161 3967...

9. Shell Programming and Scripting

split one column into multiple columns

hey, i have the following data: 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000

10. UNIX for Dummies Questions & Answers

split one column into multiple columns

hey guys... Im looking to do the following: 1 2 3 4 5 6 7 8 9 Change to: 1 4 7 2 5 8 3 6 9 Did use | perl -lpe'$\=$.%3?$":"\n"' , but it doesnt give me the matrix i want.

Login or Register to Ask a Question