Populating a table on R

Question

I have the following tab delimited file (read into my code under variable name "data"):

data <- read.csv(text="Species,Salt,Inhibited
C. violaceum,Cadmium nitrate,1
C. violaceum,Cadmium chloride,1
C. violaceum,Cobalt chloride,1
C. violaceum,Cobalt nitrate,1
C. violaceum,Iron (III) chloride,0
C. violaceum,Iron (III) sulfate,0
C. violaceum,Iron (II) sulfate,0
C. violaceum,Manganese chloride,0
C. violaceum,Manganese sulfate,0
C. violaceum,Nickel chloride,0
P. aeruginosa,Cadmium nitrate,1
P. aeruginosa,Cadmium chloride,1
P. aeruginosa,Cobalt chloride,1
P. aeruginosa,Cobalt nitrate,1
P. aeruginosa,Iron (III) chloride,0
P. aeruginosa,Iron (III) sulfate,0
P. aeruginosa,Iron (II) sulfate,0
P. aeruginosa,Manganese chloride,0
P. aeruginosa,Manganese sulfate,0
P. aeruginosa,Nickel chloride,1
S. marcescens,Cadmium nitrate,1
S. marcescens,Cadmium chloride,1
S. marcescens,Cobalt chloride,1
S. marcescens,Cobalt nitrate,1
S. marcescens,Iron (III) chloride,0
S. marcescens,Iron (III) sulfate,0
S. marcescens,Iron (II) sulfate,0
S. marcescens,Manganese chloride,0
S. marcescens,Manganese sulfate,0
S. marcescens,Nickel chloride,1")

I would like it to be put into a table in the format:

Salt    No.Inhibited    Species.Inhibited
Cadmium nitrate    3    C. violaceum, P. aeruginosa, S. marcescens
Iron (III) chloride     0    None
Nickel chloride    2    P. aeruginosa, S. marcescens

Etc. (I would like all the data included but only typed a short amount here) So far I have managed to make a table that shows The Salt in the first column and the No. Inhibited in the second column using the following code:

data1 <- aggregate(Inhibited~Salt, data=data, FUN = sum)

But I can't get the species that were inhibited to show up in a third column. I've tried using a for loop with an ifelse statement:

for(Species1 in data$Inhibited)
 ifelse (data$Inhibited == 1,yes=data$Species, no="None")
data3 <- cbind.data.frame(data1, Species1)

but this only creates a third column with the value "1" in each row. My professor suggested I use dcast (from the reshape2 package) to try to make this work, but I can't figure that out either. Can someone give me some direction on creating this third column?

See [this post](http://stackoverflow.com/questions/16596515/aggregating-by-unique-identifier-and-concatenating-related-values-into-a-string) for your second issue. If desired, you could use `cbind` to combine the results. — lmo, Apr 27 '17 at 15:42

MrFlick · Accepted Answer · 2017-04-27T15:57:36.153

You could use dplyr for this

library(dplyr)
data %>% group_by(Salt) %>% 
  mutate(keep=Inhibited==1) %>% 
  summarize(count=sum(keep), Inhibited=paste(Species[keep], collapse=", "))

which gives

                 Salt count                                  Inhibited
                <fctr> <int>                                      <chr>
1     Cadmium chloride     3 C. violaceum, P. aeruginosa, S. marcescens
2      Cadmium nitrate     3 C. violaceum, P. aeruginosa, S. marcescens
3      Cobalt chloride     3 C. violaceum, P. aeruginosa, S. marcescens
4       Cobalt nitrate     3 C. violaceum, P. aeruginosa, S. marcescens
5    Iron (II) sulfate     0                                           
6  Iron (III) chloride     0                                           
7   Iron (III) sulfate     0                                           
8   Manganese chloride     0                                           
9    Manganese sulfate     0                                           
10     Nickel chloride     2               P. aeruginosa, S. marcescens

Update: Ignore this question, I loaded it into a variable and the tags went away. Thank you again for your help!!! Thank you!!! This worked! Is there a way to remove the and tags? I am very new to R and the class I am taking didn't cover this package at all. If not, I can always manipulate it out in Word. — Starla Thornhill, Apr 27 '17 at 16:04
and can add `%>% as.data.frame` to the end to turn the tibble into a plain data.frame which does not have the variable type headers. — MrFlick, Apr 27 '17 at 16:05

Populating a table on R

1 Answers1