CODEWITHSUNDEEP
rrowaddition
tjr
tjr

Reputation: 691

add new rows from combination of multiple rows

I am trying to add various columns together to add new rows to the end of a data frame, df, if conditions are met. 

SurveyYear State Stratum Plot species pairs males_24 tib
1       2015    CT      12    4    MALL     0        0   1
2       2015    CT      12    4    ABDU     1        2   4
3       2015    CT      12    4    AGWT     1        0   0
4       2015    CT      11    2    ABDU     2        1   2
5       2015    CT      11    2    MALL     0        1   0
6       2015    CT      11    2    ABDU     4        4   7

For each grouped State, Stratum, and Plot I want to add a row to df with the sum of pairs, males_24, and tib. This needs to be done by groups of species to make a new species "TODU". In this case add up all species = ABDU and AGWT (actual data set has about 8 species to add up and 4 to not include). So there would be 2 new rows (keeping all others still) added to df that are:

2015 CT 12 4 TODU 2 2 4
2015 CT 11 2 TODU 6 5 9

I can append rows easy enough manually, or adding a single column using

df[nrow(df) + 1, ] <- c(,)

But I'm having a hard time figuring out how to group and sum while keeping the remainder of the data set intact and doing this for many variations. In SAS I would do this using proc sort, but I don't think I should need to sort first using R. Any help would be greatly appreciated. Thanks.

Upvotes: 0

Views: 111

Answers (1)

Rorschach
Rorschach

Reputation: 32466

With dplyr you could do (data is dat)

library(dplyr)

new_rows <- dat %>% group_by(State, Stratum, Plot) %>%
  summarise(SurveyYear = 2015,
            species = "TODU",
            pairs = sum(pairs),
            males_24 = sum(males_24),
            tib = sum(tib))
new_rows
#   State Stratum Plot SurveyYear species pairs males_24 tib
# 1    CT      11    2       2015    TODU     6        6   9
# 2    CT      12    4       2015    TODU     2        2   5

rbind(dat, new_rows)

Edit: to sum over some species, summarise first, then add species column.

specs <- c("AGWT", "ABDU")
new_rows <- dat %>% group_by(State, Stratum, Plot) %>%
  summarise(SurveyYear = 2015,
            pairs = sum(pairs[species %in% specs]),
            males_24 = sum(males_24[species %in% specs]),
            tib = sum(tib[species %in% specs])) %>%
  mutate(species = "TODU")
new_rows

#   State Stratum Plot SurveyYear pairs males_24 tib species
# 1    CT      11    2       2015     6        5   9    TODU
# 2    CT      12    4       2015     2        2   4    TODU

Upvotes: 1

Related Questions