I have a table that looks like this:

DAPPUDRAFT_194440   Phosphorous     
DAPPUDRAFT_194440   Temperature     
DAPPUDRAFT_194472   Phosphorous Fishkairomones  
DAPPUDRAFT_194472   Temperature     
DAPPUDRAFT_194512   Fishkairomones      
DAPPUDRAFT_194512   Cadmium Zinc    Quantumdots
DAPPUDRAFT_195644   Salinity        
DAPPUDRAFT_195644   Phosphorous     
DAPPUDRAFT_196131   Salinity        
DAPPUDRAFT_196131   Phosphorous     
DAPPUDRAFT_196131   hypoxia     
DAPPUDRAFT_196694   Salinity        

As you can see, it can have data in variable number of columns (separated by a tab).

The duplicate entries in the first column starting "DAPPUDRAFT_" should be removed and all the other values that occur in multiple rows should occur in a single row.

For Example in my input table, "DAPPUDRAFT_194440" occurs 2 times in the table and it has two values "temperature" in one row, "phosphorus" in second row as seen in the subset of data here :

 DAPPUDRAFT_194440   Phosphorous     
 DAPPUDRAFT_194440   Temperature  

What I would like to see is: "DAPPUDRAFT_" should occur only once, and the two entries "temperature" and "phosphorus" should occur in the same row separated by a tab as seen here:

 DAPPUDRAFT_194440   Phosphorous   Temperature  

Expected output:

DAPPUDRAFT_194440   Phosphorous Temperature     
DAPPUDRAFT_194472   Phosphorous Fishkairomones  Temperature 
DAPPUDRAFT_194512   Fishkairomones  Cadmium Zinc    Quantumdots
DAPPUDRAFT_195644   Salinity    Phosphorous     
DAPPUDRAFT_196694   Salinity            
DAPPUDRAFT_196131   Salinity    Phosphorous hypoxia 

I tried with the "reshape2" package in R with the dcast function. But it does something totally different than what i wanted. Is there a way on the command line or R or perl that can help solving this?


Simply with awk:

awk '{ r=$0; sub($1,"",r); a[$1]=(a[$1])? a[$1]"\t"r : r }
     END{ for(i in a) { gsub(/[[:space:]]{2,}/," ",a[i]); print i,a[i] } }' file

  • r=$0 - capturing copy of the record

  • sub($1,"",r) - removing the 1st field the copy to store remaining fields in r variable

  • a[$1]=(a[$1])? a[$1]"\t"r : r - accumulating values for same group(presented by the 1st field)

  • for(i in a) - iterating through all grouped items

  • gsub(/[[:space:]]{2,}/," ",a[i]) - removing excessive whitespaces between words

  • print i,a[i] - print the group name and its values

The output:

DAPPUDRAFT_194440  Phosphorous Temperature 
DAPPUDRAFT_196694  Salinity
DAPPUDRAFT_194512  Fishkairomones Cadmium Zinc Quantumdots
DAPPUDRAFT_194472  Phosphorous Fishkairomones Temperature 
DAPPUDRAFT_196131  Salinity Phosphorous hypoxia 
DAPPUDRAFT_195644  Salinity Phosphorous 
  • That's awesome! Could you please explain the command? – biobudhan Sep 15 '17 at 12:24
  • @biobudhan, sure, see my explanation – RomanPerekhrest Sep 15 '17 at 12:30


$ perl -e 'while(<ARGV>){chomp;($x,$y)=split(/\s+/,$_,2);$hash{$x}.=$y;}for(keys %hash){print "$_ $hash{$_}\n";}' test1
DAPPUDRAFT_196694 Salinity
DAPPUDRAFT_194440 Phosphorous     Temperature
DAPPUDRAFT_195644 Salinity        Phosphorous
DAPPUDRAFT_194472 Phosphorous Fishkairomones  Temperature
DAPPUDRAFT_194512 Fishkairomones      Cadmium Zinc    Quantumdots
DAPPUDRAFT_196131 Salinity        Phosphorous     hypoxia

If you don't care how lines and elements are ordered:

sed 'G;s/^\(.*\)\(\t.*\)\n\(.*\)\1/\3\1\2/;h;$!d;s/\n$//' file

For non-GNU sed replace the \t by a litaral TAB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.