Add row to data frame with dplyr

Question

I have this sample data:

cvar <- c("2015-11-01","2015-11-02","All")
nvar1 <- c(12,10,5)
nvar2 <- c(7,5,6)
data <- cbind.data.frame(cvar,nvar1,nvar2)

And I just want to add a new row to the data.frame containing the sums of nvar1 & nvar2 and a character, so with base R I would just use

data[nrow(data)+1,] <- c("add",sum(data[,2]),sum(data[,3]))

or something more clever with lapply, but just to show you what I'm looking for.

I would like this simple command within the pipe environment, so data %>% ... gives me the above outcome.

Appreciate any help, thank you.

Answer 1

Using only dplyr you could do the following

data %<>%
  summarise(cvar = "add",
            nvar1 = sum(nvar1),
            nvar2 = sum(nvar2)) %>%
  bind_rows(data)

which results in

        cvar nvar1 nvar2
1        add    27    18
2 2015-11-01    12     7
3 2015-11-02    10     5
4        All     5     6

Note that this way the new row is added at the beginning rather than the end of the original dataframe.

If you want to add the new row at the end instead, use the following code (thanks to krlmlr for pointing this out)

data %<>%
  summarise(cvar = "add",
            nvar1 = sum(nvar1),
            nvar2 = sum(nvar2)) %>%
  bind_rows(data, .)

which results in

        cvar nvar1 nvar2
1 2015-11-01    12     7
2 2015-11-02    10     5
3        All     5     6
4        add    27    18

Answer 2

Something like this then maybe:

data %>% 
     rbind(c("add",sum(nvar1),sum(nvar2)))
#        cvar nvar1 nvar2
#1 2015-11-01    12     7
#2 2015-11-02    10     5
#3        All     5     6
#4        add    27    18

Edit:

According to your comment, this will work:

data %>% 
  mutate(nvar3 = nvar1) %>% 
  rbind(c("add",sum(nvar1),sum(nvar2),sum(.$nvar3))) 

Using the . will allow rbind to find nvar3

Edit2:

Provide the new row as a list and it will maintain the column classes:

> str(
+ data %>% 
+   mutate(nvar3 = nvar1) %>% 
+   rbind(list("add",sum(nvar1),sum(nvar2),sum(.$nvar3))) 
+ )
'data.frame':   4 obs. of  4 variables:
 $ cvar : chr  "2015-11-01" "2015-11-02" "All" "add"
 $ nvar1: num  12 10 5 27
 $ nvar2: num  7 5 6 18
 $ nvar3: num  12 10 5 27

Answer 3

With tibble version 1.2 you can use add_row()

https://blog.rstudio.org/2016/08/29/tibble-1-2-0/

data %>% 
 add_row(cvar = "add", nvar1 = sum(nvar1), nvar2 = sum(nvar2))

Answer 4

One option utilizing summarise_all() and bind_rows() could be:

data %>% 
 bind_rows(summarise_all(., ~ if (is.numeric(.)) sum(.) else "add"))

        cvar nvar1 nvar2
1 2015-11-01    12     7
2 2015-11-02    10     5
3        All     5     6
4        add    27    18

Or adding the row and then calculating the sum only for that last row using if_else():

data %>%
 add_row(cvar = "add") %>%
 mutate_at(-1, ~ if_else(row_number() == max(row_number()), sum(., na.rm = TRUE), .))

Or an alternative to @Rickard's answer when the variables are not in global environment:

data %>% 
 add_row(cvar = "add", nvar1 = sum(data$nvar1), nvar2 = sum(data$nvar2))

Answer 5

If someone is still looking for an universal solution I would be use:

cvar <- c("2015-11-01","2015-11-02","All")
nvar1 <- c(12,10,5)
nvar2 <- c(7,5,6)
data <- tibble::tibble(cvar,nvar1,nvar2)

purrr::map_df(data, ~c(.x, ifelse(is.numeric(.x), sum(.x, na.rm=TRUE), NA)))

P.S. I use tibble to keep character because a data frame converts them to factor and base::c "destroy" them