CODEWITHSUNDEEP
rdplyr
Sebastian
Sebastian

Reputation: 2570

Add row to data frame with dplyr

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.

Upvotes: 35

Views: 82591

Answers (5)

tmfmnk
tmfmnk

Reputation: 40171

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))

Upvotes: 6

Maksym Tulyuk
Maksym Tulyuk

Reputation: 91

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

Upvotes: 0

Salim B
Salim B

Reputation: 2719

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

Upvotes: 15

Rickard
Rickard

Reputation: 3690

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))

Upvotes: 52

LyzandeR
LyzandeR

Reputation: 37889

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

Upvotes: 22

Related Questions