Merge column headers and transpose | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

Merge column headers and transpose

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 11-29-2012
giuliangiuseppe giuliangiuseppe is offline
Registered User
 
Join Date: Nov 2012
Last Activity: 10 April 2014, 10:34 AM EDT
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Merge column headers and transpose

Hello Everyone!
I am new on this forum and this is my first post. I wish to apologize for my, not canonical, English.
I would like to solve this problem but I have no clue of how do it!I will be grateful if someone could help me!
I have a table like this:

Code:
gene TF1 TF2 TF3 TF4
gene1 1 2 3 4
gene2 1 1 4 4
gene3 1 2 3 3
gene4 1 2 3 4
gene5 1 2 1 3
gene6 2 2 1 2
gene7 3 3 1 3
gene8 2 3 1 4

Each row is a gene and each column a transcription factor.The number in the intersection means if the TF there is, there is not, is upregulated, is downregulated.
Now what I would like to obtain is something like that:

Code:
gene TF
gene1TF1 1
gene1TF2 2
gene1TF3 3
gene1TF4 4
gene2TF1 1
gene2TF2 1
gene2TF3 4
gene2TF4 4
and so on

Adding at each gene the TF and the relative number!I get crazy!
I really hope that someone could help me!
Thanks a lot

Giuliano

Last edited by joeyg; 11-29-2012 at 01:29 PM.. Reason: corrected spelling
Sponsored Links
    #2  
Old 11-29-2012
vgersh99's Avatar
vgersh99 vgersh99 is online now Forum Advisor  
Forum Advisor
 
Join Date: Feb 2005
Last Activity: 20 April 2014, 2:33 PM EDT
Location: Foxborough, MA
Posts: 7,528
Thanks: 137
Thanked 534 Times in 502 Posts

Code:
awk '
NR==1 {
  n=split($0,h,FS)
  print $1, "TF"
  next
}
{
  for(i=2;i<=n;i++)
    print $1 h[i], $i
}' myFile

Sponsored Links
    #3  
Old 11-29-2012
giuliangiuseppe giuliangiuseppe is offline
Registered User
 
Join Date: Nov 2012
Last Activity: 10 April 2014, 10:34 AM EDT
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Thaks a lot but in the output the gene is not repeated for each trasnscription factor(TF).Its shown just the first TFfor each gene!!
Other suggestion?
    #4  
Old 11-29-2012
vgersh99's Avatar
vgersh99 vgersh99 is online now Forum Advisor  
Forum Advisor
 
Join Date: Feb 2005
Last Activity: 20 April 2014, 2:33 PM EDT
Location: Foxborough, MA
Posts: 7,528
Thanks: 137
Thanked 534 Times in 502 Posts

Code:
awk '
 NR==1 {   n=split($0,h,FS)   print $1, "TF"   next } 
{   for(i=2;i<=n;i++)     print $1 h[i], $i }' myFile

and with myFile:

Code:
gene TF1 TF2 TF3 TF4
gene1 1 2 3 4
gene2 1 1 4 4
gene3 1 2 3 3
gene4 1 2 3 4
gene5 1 2 1 3
gene6 2 2 1 2
gene7 3 3 1 3
gene8 2 3 1 4

produces the following:

Code:
gene TS
gene1TF1 1
gene1TF2 2
gene1TF3 3
gene1TF4 4
gene2TF1 1
gene2TF2 1
gene2TF3 4
gene2TF4 4
gene3TF1 1
gene3TF2 2
gene3TF3 3
gene3TF4 3
gene4TF1 1
gene4TF2 2
gene4TF3 3
gene4TF4 4
gene5TF1 1
gene5TF2 2
gene5TF3 1
gene5TF4 3
gene6TF1 2
gene6TF2 2
gene6TF3 1
gene6TF4 2
gene7TF1 3
gene7TF2 3
gene7TF3 1
gene7TF4 3
gene8TF1 2
gene8TF2 3
gene8TF3 1
gene8TF4 4

this matches exactly your desired output.
Sponsored Links
    #5  
Old 11-30-2012
pamu pamu is offline
Registered User
 
Join Date: Mar 2012
Last Activity: 14 April 2014, 6:10 AM EDT
Posts: 1,640
Thanks: 58
Thanked 476 Times in 472 Posts
Also Try..


Code:
awk 'NR==1{split($0,P);print $1,substr($2,1,2)}
    NR>1{for(i=2;i<=NF;i++){print $1""P[i],$i}}' file

Sponsored Links
    #6  
Old 11-30-2012
giuliangiuseppe giuliangiuseppe is offline
Registered User
 
Join Date: Nov 2012
Last Activity: 10 April 2014, 10:34 AM EDT
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Ok it works.I made I mistake!!
Thanks a lot!!
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Transpose field names from column headers to values in one column popesk Shell Programming and Scripting 6 07-22-2011 10:10 AM
Matching words based on column headers bha148 Shell Programming and Scripting 1 09-23-2010 02:58 AM
Sort by Column Headers cgk1983 UNIX for Dummies Questions & Answers 8 10-07-2009 10:17 AM
Excel Column Headers pk_eee Shell Programming and Scripting 1 08-09-2008 05:29 AM
Removing Headers and a Column DerangedNick Shell Programming and Scripting 30 02-01-2008 04:02 PM



All times are GMT -4. The time now is 03:00 PM.