How can I combine rows of data when their character values are equal? (R)

user_16235608 0
Viewed 42

I have a dataset that was recorded by observation(each observation has its own row of data). I am looking to combine/condense these rows by the plant they were found on - currently a character variable. All other columns are numerical vales.

EX:

This is the raw data |Sci_Name|Honeybee_count|Other_bee_Obsevrved|Stem_count| |---|---|---|---| |Zizia aurea|1|5|10| |Asclepias viridiflora|15|1|3| |Viola unknown|0|0|4| |Zizia aurea|0|2|6| |Zizia aurea|3|6|3| |Asclepias viridiflora|8|2|17|

and I want:

Sci_Name Honeybee_count Other_bee_Obsevrved Stem_count
Zizia aurea 4 13 19
Asclepias viridiflora 23 3 20
Viola unknown 0 0 4

I am currently pulling this data from a CSV already in table form. I have been attempting to create a new table/data frame with one entry of each plant species, and blanks/0s for each other variable, which I can then use to c-binding the two together. This, however, has been clunky at best and I am having trouble figuring out how to have each row check itself. I am open to any approach, let me know what you think!

Thanks :D

r merge
1 Answers

We can use the formula method in aggregate from base R. On the rhs of the ~, specify the grouping variable and on the lhs, use . for denoting the rest of the variables. Specify the FUN as sum and it will do the column wise sum by group

aggregate(. ~ Sci_Name, df1, sum)

-output

            Sci_Name Honeybee_count Other_bee_Obsevrved Stem_count
1 Asclepias viridiflora             23                   3         20
2         Viola unknown              0                   0          4
3           Zizia aurea              4                  13         19

data

df1 <- structure(list(Sci_Name = c("Zizia aurea", "Asclepias viridiflora", 
"Viola unknown", "Zizia aurea", "Zizia aurea", "Asclepias viridiflora"
), Honeybee_count = c(1L, 15L, 0L, 0L, 3L, 8L), Other_bee_Obsevrved = c(5L, 
1L, 0L, 2L, 6L, 2L), Stem_count = c(10L, 3L, 4L, 6L, 3L, 17L)), 
class = "data.frame", row.names = c(NA, 
-6L))
user_37322710
Related