Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Select 2 columns and transpose row by row

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Select 2 columns and transpose row by row
# 1  
Old 10-26-2012
Select 2 columns and transpose row by row
Hi,

I have a tab-delimited file as follows:

Code:
1  1  2  2  3  3  4  4
a  a  b  b  c  c  d  d
5  5  6  6  7  7  8  8
e  e  f  f  g  g  h  h
9  9  10 10 11 11 12 12
i  i  j  j  k  k  l  l
13 13 14 14 15 15 16 16
m  m  n  n  o  o  p  p

The output I need is:
Code:
1 1 a a 5 5 e e 9  9  i i 13 13 m m
2 2 b b 6 6 f f 10 10 j j 14 14 n n
3 3 c c 7 7 g g 11 11 k k 15 15 o o
4 4 d d 8 8 h h 12 12 l l 16 16 p p

I have tried the following, but it transposes single columns to rows:
Code:
BEGIN{FS=OFS=" "}
{
for(i=1;i<=NF;i++)
{
 a[NR,i]=$i
 if(big <= NF)  big=NF
 }
}
END{for(i=1;i<=big;i++) {for(j=1;j<=NR;j++) printf("%s%s",a[j,i], (j==NR ? "" : OFS))
    printf "\n"
   }
}

The output of above code is:
Code:
1 a 5 e 9  i 13 m
1 a 5 e 9  i 13 m
2 b 6 f 10 j 14 n
2 b 6 f 10 j 14 n 
etc.

which is not what I want.

Please help!
Last edited by mvaishnav; 10-26-2012 at 03:35 PM.. Reason: Code formatting was incorrect.
# 2  
Old 10-26-2012
Code:
awk 'END {
  for (i = 1; i <= NF; i += 2)
    for (j = 0; ++j <= NR;)
      printf "%s", (d[j, i] OFS d[j, i + 1] (j < NR ? OFS : ORS))
  }
{
  for (i = 0; ++i <= NF;)
    d[NR, i] = $i
  }' OFS='\t' infile

Note that in some awk implementations NR and NF may not be available in the END block.
If you're using one of those, the script should be:

Code:
awk 'END {
  for (i = 1; i <= nf; i += 2)
    for (j = 0; ++j <= nr;)
      printf "%s", (d[j, i] OFS d[j, i + 1] (j < NR ? OFS : ORS))
  }
{
  for (i = 0; ++i <= NF;)
    d[NR, i] = $i
  nr = NR; nf = NF    
  }' OFS='\t' infile

# 3  
Old 10-26-2012
Hi radoulov,

Thanks for such a quick reply!

I tried your suggestions, but in both cases I am getting the output as:

Code:
1  1  2  2  3  3  4  4
a  a  b  b  c  c  d  d
5  5  6  6  7  7  8  8
e  e  f  f  g  g  h  h
9  9  10 10 11 11 12 12
i  i  j  j  k  k  l  l
13 13 14 14 15 15 16 16
m  m  n  n  o  o  p  p
1	1	a	a	5	5	e	e	9	9	i	i	13	13	m	m
2	2	b	b	6	6	f	f	10	10	j	j	14	14	n	n
3	3	c	c	7	7	g	g	11	11	k	k	15	15	o	o
4	4	d	d	8	8	h	h	12	12	l	l	16	16	p	p

The original file contents are also being displayed in the beginning. How to avoid that?
# 4  
Old 10-26-2012
Could you please double check your data and the script you're using?
This User Gave Thanks to radoulov For This Post:
mvaishnav
# 5  
Old 10-26-2012
Hi radoulov,

I just checked my script: it works!

Previously my script was not working because I was doing the following:

Code:
#!/usr/bin/awk

END {
  for (i = 1; i <= nf; i += 2)
    for (j = 0; ++j <= nr;)
      printf "%s", (d[j, i] OFS d[j, i + 1] (j < NR ? OFS : ORS))
  }
{
  for (i = 0; ++i <= NF;)
    d[NR, i] = $i
  nr = NR; nf = NF    
  } OFS="\t"



I saved the script as transpose.awk and then I ran it using:
Code:
awk -f transpose.awk infile

That was giving me the original file contents also. But when I typed in your script on the command-line directly, I got the result I expected.

One request:
Would you please explain your code to help me understand?

Cheers.
# 6  
Old 10-26-2012
Quote:
Originally Posted by mvaishnav
Hi radoulov,

I just checked my script: it works!

Previously my script was not working because I was doing the following:

Code:
#!/usr/bin/awk
 
END {
 for (i = 1; i <= nf; i += 2)
   for (j = 0; ++j <= nr;)
     printf "%s", (d[j, i] OFS d[j, i + 1] (j < NR ? OFS : ORS))
 }
{
 for (i = 0; ++i <= NF;)
   d[NR, i] = $i
 nr = NR; nf = NF    
 } OFS="\t"

You just need to take the OFS='\t' out of the script and execute it like this:

Code:
awk -f scriptname OFS='\t' infile

Quote:
I saved the script as transpose.awk and then I ran it using:
Code:
awk -f transpose.awk infile

That was giving me the original file contents also. But when I typed in your script on the command-line directly, I got the result I expected.

One request:
Would you please explain your code to help me understand?

Cheers.
Sure, version with comments:

Code:
 
awk 'END {    
# after the input has been read
# outer: for every two fields - 1, 3, 5 ... loop:
  for (i = 1; i <= NF; i += 2)
# inner: for every record, loop 
    for (j = 0; ++j <= NR;)
# print the content of the array d (composite keyed by the 
# number of the record and the number of the field
      printf "%s", (d[j, i] OFS d[j, i + 1] (j < NR ? OFS : ORS))
  }
{
# build an array named d (mnemonic for data) keyed by
# the number of the record NR and the number of the field 
  for (i = 0; ++i <= NF;)
    d[NR, i] = $i
  }' OFS='\t' infile

This User Gave Thanks to radoulov For This Post:
mvaishnav
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How transpose column in a row?

Hello guys, First of all happy holidays and happy new year. I'm new in bioinformatic and also it is my first time that I write in this forum. Therefore, sorry if I make some mistakes. I'm writing to ask your help to fix a problem: I have a file like this: gene1 GO:0016491|GO:0055114... (8 Replies)
Discussion started by: Salvatore_espos
8 Replies

2. UNIX for Beginners Questions & Answers

Transpose a sequence of symbols from row to one columns (no separators))

Hi all, I have a data file. It contains one header line followed by a new line which is one row of different characters without separators (charahters can be dots, dash, capital and small letters). What I need is ignoring header line to transpose these characters so they form a column. So, from... (4 Replies)
Discussion started by: kush
4 Replies

3. Shell Programming and Scripting

Transpose columns to row

Gents Using the attached file and using this code. awk '{print substr($0,4,2)}' input.txt | sort -k1n | awk '{a++}END{for(i in a) print i,a}' | sort -k1 > output i got the this output. 00 739 01 807 02 840 03 735 04 782 05 850 06 754 07 295 08 388 09 670 10 669 11 762 (8 Replies)
Discussion started by: jiam912
8 Replies

4. Shell Programming and Scripting

Transpose row to column

I'm using the testawk.awk from the following thread https://www.unix.com/shell-programming-and-scripting/18897-row-column-transpose.htmlI'm getting the following output fieldname1 data1 fieldname2 data2 fieldname3 data3 How can I get like this instead 1 fieldname1 data1 2 fieldname2 data2... (1 Reply)
Discussion started by: makkan
1 Replies

5. Shell Programming and Scripting

To transpose row into column

Hi All, In shell, I have below data coming from some some text file as below: . 351706 5861.8 0.026 0.012 12.584 0.026 0.012 12.582 0.000 0.000 0.000 Now i need the above data to be transposed as below 351706... (16 Replies)
Discussion started by: Anamica
16 Replies

6. Shell Programming and Scripting

Column to row Transpose

Hi Folks, Iam a kinda newbie to unix shell scripting, the scenario is i have a text file containing the following info Charlie chicago 15 Charlie newyork 26 jonny chicago 14 jonny newyork 15 joe chicago 15 joe newyork 18output should be Name chicago ... (3 Replies)
Discussion started by: tech_frk
3 Replies

7. UNIX for Dummies Questions & Answers

Row to column transpose

Hi there, Below is sample three rows which i need transpose into multiple rows. By keeping first 2 fields static and split them into multiple rows depend following date field. Each into seperate rows. Sample code: ... (6 Replies)
Discussion started by: ganeshd
6 Replies

8. Shell Programming and Scripting

Transpose column to row

Hi i have a file which has values seperated by "," as shown below and I want to transpose for every doc_id in one row. Input: DOC_ID,KEYWORD 105,REGISTROS 105,GEOLOGIA 105,NUCLEOS 105,EXPEDIENTE 105,PROGRAMAS 10025,EXPEDIENTE 10025,LOCALIZACIONES 10025,OFICIOS 10025,PROGRAMAS... (4 Replies)
Discussion started by: juelillo
4 Replies

9. Shell Programming and Scripting

Transpose - Multiple row to Multiple columns

I have seen many posts to transpose rows into columns, but not for multiple rows to columns..so please help me how to do this.. I have around 1000 rows and 1000 columns, i want to transpose it.. A1 A2 A3 B1 B2 B3 C1 C2 C3 D1 D2 D3 I want output to be like A1 B1 C1 D1 A2 B2 C2 D2... (5 Replies)
Discussion started by: ganeshss
5 Replies

10. Shell Programming and Scripting

Row to column transpose

Can we transpose rows to columns? Fields within row are separated by a comma. (10 Replies)
Discussion started by: videsh77
10 Replies
Login or Register to Ask a Question