CODEWITHSUNDEEP
rdataframe
Paul
Paul

Reputation: 696

R add column values to dataframe based on rownames values

I need to add a column to a dataframe that reflects the information encoded in the rownames. The following is a minimal example illustrating the problem.

Example:

df <- data.frame(c(1,2,3,4),c(0,1,0,1))
colnames(df) <- c("Value","Ident")
rownames(df) <- c("fish1_101","fish1_102","fish2_103","fish2_104")
df

Encoded in the rownames is information about each sample. In this example the "fish1" prefix indicates salmon while the "fish2" prefix indicates sunfish.

I need to add a new column "fish_species" specifying the correct fish species.

Attempt:

key_df <- data.frame(c("fish1","fish2","fish3"),c("salmon","sunfish","halibut"))
colnames(key_df) <- c("key","species")

df["species"] <- apply(df, 1, function(x){
NameFound <- x[3]
NameFound_split <- unlist(strsplit(NameFound, "_"))
if (NameFound_split[1] == "fish1"){
out <- "salmon"
} else if (NameFound_split[1] == "fish2") {
out <- "sunfish"
} else if (NameFound_split[1] == "fish3") {
out <- "halibut"
}
return(out)
})

df <- df[,c(1,2,4)]
df # This is the desired result.

I am looking for a cleaner and more high throughput way to do this where an if statement isn't needed for each identity.

Upvotes: 1

Views: 55

Answers (3)

ThomasIsCoding
ThomasIsCoding

Reputation: 102700

Try the following base R option using match

df$species <- with(
  key_df,
  species[match(gsub("_.*", "", rownames(df)), key)]
)

and you will obtain

> df
          Value Ident species
fish1_101     1     0  salmon
fish1_102     2     1  salmon
fish2_103     3     0 sunfish
fish2_104     4     1 sunfish

Upvotes: 1

Onyambu
Onyambu

Reputation: 79338

df %>%
  rownames_to_column('key')%>%
  mutate(key = str_remove(key, '_.*'))%>%
  left_join(key_df, by = 'key')

    key Value Ident species
1 fish1     1     0  salmon
2 fish1     2     1  salmon
3 fish2     3     0 sunfish
4 fish2     4     1 sunfish

Upvotes: 2

PaulS
PaulS

Reputation: 25528

A possible solution:

library(tidyverse)

df <- data.frame(c(1,2,3,4),c(0,1,0,1))
colnames(df) <- c("Value","Ident")
rownames(df) <- c("fish1_101","fish1_102","fish2_103","fish2_104")

df %>% 
  rownames_to_column("fish_species") %>% 
  mutate(fish_species = if_else(str_detect(fish_species,"fish1"), "salmon", "sunfish"))

#>   fish_species Value Ident
#> 1       salmon     1     0
#> 2       salmon     2     1
#> 3      sunfish     3     0
#> 4      sunfish     4     1

Upvotes: 1

Related Questions