re-arrange mixed input dataframe

Question

I have an input dataframe that is not consistent. Here it is.

df <- structure(list(Gene = c("k141_1305_1", "k141_1406_2", "k141_1406_3", 
"k141_6669_1", "k141_9215_1", "k141_13242_1", "k141_13333_5", 
"k141_17708_1", "k141_19670_1", "k141_19670_6"), Phylum = c("p__Actinobacteria", 
"p__Firmicutes", "p__Firmicutes", "p__Cyanobacteria", "p__Actinobacteria", 
"p__Actinobacteria", "p__Firmicutes", "p__Firmicutes", "p__Actinobacteria", 
"p__Proteobacteria"), Class = c("c__Actinobacteria", "c__Clostridia", 
"c__Clostridia", "o__Nostocales", "c__Actinobacteria", "c__Actinobacteria", 
"c__Clostridia", "c__Bacilli", "c__Actinobacteria", "c__Gammaproteobacteria"
), Order = c("o__Pseudonocardiales", "o__Clostridiales", "o__Clostridiales", 
"f__Hapalosiphonaceae", "o__Pseudonocardiales", "o__Pseudonocardiales", 
"o__Clostridiales", "o__Bacillales", "o__Pseudonocardiales", 
"o__Pseudomonadales"), Family = c("f__Pseudonocardiaceae", "f__Lachnospiraceae", 
"f__Lachnospiraceae", "g__Fischerella", "f__Pseudonocardiaceae", 
"f__Pseudonocardiaceae", "f__Clostridiales Family XIII. Incertae Sedis", 
"g__Exiguobacterium", "f__Pseudonocardiaceae", "f__Pseudomonadaceae"
), Genus = c("g__Pseudonocardia", "s__Lachnospiraceae bacterium 10-1", 
"s__Lachnospiraceae bacterium 10-1", "s__Fischerella muscicola", 
"g__Pseudonocardia", "g__Pseudonocardia", "s__[Eubacterium] infirmum", 
"s__Exiguobacterium enclense", "g__Pseudonocardia", "g__Pseudomonas"
), Species = c("s__Pseudonocardia sp. Ae331_Ps2", "unknown", 
"unknown", "unknown", "s__Pseudonocardia sp. Ae331_Ps2", "s__Pseudonocardia sp. Ae331_Ps2", 
"unknown", "unknown", "s__Pseudonocardia ammonioxydans", "s__Pseudomonas aeruginosa group"
)), .Names = c("Gene", "Phylum", "Class", "Order", "Family", 
"Genus", "Species"), row.names = c(3212L, 3853L, 3854L, 17967L, 
24006L, 34126L, 34325L, 43722L, 49328L, 49332L), class = "data.frame")

The dataframe looks like this

 Gene            Phylum             Class                Order                Family
3212   k141_1305_1 p__Actinobacteria c__Actinobacteria o__Pseudonocardiales f__Pseudonocardiaceae
3853   k141_1406_2     p__Firmicutes     c__Clostridia     o__Clostridiales    f__Lachnospiraceae
3854   k141_1406_3     p__Firmicutes     c__Clostridia     o__Clostridiales    f__Lachnospiraceae
17967  k141_6669_1  p__Cyanobacteria     o__Nostocales f__Hapalosiphonaceae        g__Fischerella
24006  k141_9215_1 p__Actinobacteria c__Actinobacteria o__Pseudonocardiales f__Pseudonocardiaceae
34126 k141_13242_1 p__Actinobacteria c__Actinobacteria o__Pseudonocardiales f__Pseudonocardiaceae
                                  Genus                         Species
3212                  g__Pseudonocardia s__Pseudonocardia sp. Ae331_Ps2
3853  s__Lachnospiraceae bacterium 10-1                         unknown
3854  s__Lachnospiraceae bacterium 10-1                         unknown
17967          s__Fischerella muscicola                         unknown
24006                 g__Pseudonocardia s__Pseudonocardia sp. Ae331_Ps2
34126                 g__Pseudonocardia s__Pseudonocardia sp. Ae331_Ps2

As you can see the dataframe is not structured as it should be. The dataframe was generated this way so i don´t have control over it.

The problem is that microorganisms should be annotated using different ranks (from Pylum to species, one per column). As you can see in some cases there are missing ranks , for example Gene 17967 (4th row) does not have a class rank (no "c__" annotation). What happens is that in the column class this particular taxa has the order ("o__Nostocales") instead of an empty "c__" annotation. Same happens for others, for example 2nd row does not have genus "g__" annotation so the species is put in the genus column.

The first and two last rows are an example of how it should be.

Is there a chance to correct those rows in a quick way so that each colum has it corresponding taxonomic rank. ???

For example if i take the second row the correct output should be:

Gene            Phylum             Class                Order                Family 
     3853   k141_1406_2     p__Firmicutes     c__Clostridia     o__Clostridiales    f__Lachnospiraceae
          Genus    Species
    3853  g__ s__Lachnospiraceae bacterium 10-1 

Alternatevely it could be an unknown g__unknown tag.

3853   k141_1406_2     p__Firmicutes     c__Clostridia     o__Clostridiales    f__Lachnospiraceae
 3853  g__unknown s__Lachnospiraceae bacterium 10-1 

Answer 1

Try using this code:

 adds=function(x){
   nam=c("k","p","c","o","f","g","s")
   l=which(is.na(match(nam,substr(x,1,1))));
   if(length(l)>0)`names<-`(head(unlist(append(x,paste0(nam[l],"__"),l-1)),-1),names(x))
   else x
 }

 data.frame(t(apply(df,1,adds)))

This should be able to append the desired name to the row. hence giving the expected result. If this helps, please let us know. Thank you.

Answer 2

First we need to recognize that the current columns are meaningless (apart from their names), and that the prefixes carry a meaning, that we'll map to the long column names.

so we build a lookup table and then use tidyr and dplyr

library(dplyr)
library(tidyr)
lkp <- data.frame(key1  = c("Phylum","Class","Order","Family","Genus","Species"),
                  key2 = c("p","c","o","f","g","s"),
                  stringsAsFactors = F)

df %>% gather(key,val,-Gene)          %>%  # put everything in a single column
  filter(val != "unknown")            %>%  # get rid of the unknowns, they don't contain info and have irregular format (no underscore)
  separate(val,c("key2","val2"),sep="__",remove = F) %>% # separate the values, keeping the original
  left_join(lkp)                      %>%  # add info from lookup table
  select(Gene,val,key1)               %>%  # keep only relevant columns
  spread (key1,val, fill = "unknown") %>%  # set back in wide format
  as.data.frame                            # convert from tibble to data.frame

# Gene                  Class                                       Family              Genus                Order            Phylum                           Species
# 1   k141_1305_1      c__Actinobacteria                        f__Pseudonocardiaceae  g__Pseudonocardia o__Pseudonocardiales p__Actinobacteria   s__Pseudonocardia sp. Ae331_Ps2
# 2  k141_13242_1      c__Actinobacteria                        f__Pseudonocardiaceae  g__Pseudonocardia o__Pseudonocardiales p__Actinobacteria   s__Pseudonocardia sp. Ae331_Ps2
# 3  k141_13333_5          c__Clostridia f__Clostridiales Family XIII. Incertae Sedis            unknown     o__Clostridiales     p__Firmicutes         s__[Eubacterium] infirmum
# 4   k141_1406_2          c__Clostridia                           f__Lachnospiraceae            unknown     o__Clostridiales     p__Firmicutes s__Lachnospiraceae bacterium 10-1
# 5   k141_1406_3          c__Clostridia                           f__Lachnospiraceae            unknown     o__Clostridiales     p__Firmicutes s__Lachnospiraceae bacterium 10-1
# 6  k141_17708_1             c__Bacilli                                      unknown g__Exiguobacterium        o__Bacillales     p__Firmicutes       s__Exiguobacterium enclense
# 7  k141_19670_1      c__Actinobacteria                        f__Pseudonocardiaceae  g__Pseudonocardia o__Pseudonocardiales p__Actinobacteria   s__Pseudonocardia ammonioxydans
# 8  k141_19670_6 c__Gammaproteobacteria                          f__Pseudomonadaceae     g__Pseudomonas   o__Pseudomonadales p__Proteobacteria   s__Pseudomonas aeruginosa group
# 9   k141_6669_1                unknown                         f__Hapalosiphonaceae     g__Fischerella        o__Nostocales  p__Cyanobacteria          s__Fischerella muscicola
# 10  k141_9215_1      c__Actinobacteria                        f__Pseudonocardiaceae  g__Pseudonocardia o__Pseudonocardiales p__Actinobacteria   s__Pseudonocardia sp. Ae331_Ps2

Replace val by val2 in the 2 last instances if you want to get rid of the prefixes by the same token.

df %>% gather(key,val,-Gene) %>%
  filter(val != "unknown") %>%
  separate(val,c("key2","val2"),sep="__",remove = F) %>%
  left_join(lkp) %>%
  select(Gene,val2,key1) %>%
  spread (key1,val2,fill="unknown") %>%
  as.data.frame