R: Grouping two rows into a new row based on factor identity

Question

Within a large dataframe, I am trying to create a new row that groups specific data from other rows, based on the identity of another factor. Here is some example data:

> Species    Status    Value
> A         Introduced   10
> A          Native      3
> B          Crypt       6
> C         Introduced   19
> C          Native      4

For each Species, I would like to create a new row, that only takes the data for the Status "Introduced" or "Crypt", and ignores the data in "Native" Status. Each species either has data for only "Introduced" and "Native" or only "Crypt".

Thus, my desired output will look like this:

> Species    Status    Value
> A         Introduced   10
> A          Native      3
> A         IC.Total     10
> B          Crypt       6
> B         IC.Total     6
> C         Introduced   19
> C          Native      4
> C         IC.Total     19

Is a for loop the best way to go about this, or is there a more elegant way? Any suggestions would be great-thanks for your help!

Answer 1

The following uses the data.table package.
Assuming your original data.frame is called myDat:

library(data.table)
myDT <- data.table(myDat, key="Species")

# Creates a new DT, of only the Speices column
myDT2 <- setkey(unique(myDT[, list(Species)]), "Species")

# Add IC.Total values
myDT2[myDT[Status=="Introduced"], c("Status", "ValueC") := list("IC.Total", Value)]

# Add Crypt values
myDT2[myDT[Status=="Crypt"], c("Status", "ValueC") := list("Crypt", Value)]

# fix the column name
setnames(myDT2, "ValueC", "Value")

# combine and sort by speicies
myDT <- setkey(rbind(myDT, myDT2), "Species")

myDT
#    Species     Status Value
# 1:       A Introduced    10
# 2:       A     Native     3
# 3:       A   IC.Total    10
# 4:       B      Crypt     6
# 5:       B      Crypt     6
# 6:       C Introduced    19
# 7:       C     Native     4
# 8:       C   IC.Total    19

---

Note, if you dont want to duplicate the crypt count, simply take out that line above.