Add Row in Each Group Using Dplyr and Add_Row()

Add row in each group using dplyr and add_row()

If you want to use a grouped operation, you need do like JasonWang described in his comment, as other functions like mutate or summarise expect a result with the same number of rows as the grouped data frame (in your case, 50) or with one row (e.g. when summarising).

As you probably know, in general do can be slow and should be a last resort if you cannot achieve your result in another way. Your task is quite simple because it only involves adding extra rows in your data frame, which can be done by simple indexing, e.g. look at the output of iris[NA, ].

What you want is essentially to create a vector

indices <- c(NA, 1:50, NA, 51:100, NA, 101:150)

(since the first group is in rows 1 to 50, the second one in 51 to 100 and the third one in 101 to 150).

The result is then iris[indices, ].

A more general way of building this vector uses group_indices.

indices <- seq(nrow(iris)) %>% 
    split(group_indices(iris, Species)) %>% 
    map(~c(NA, .x)) %>%
    unlist

(map comes from purrr which I assume you have loaded as you have tagged this with tidyverse).

How to add a row to each group and assign values

According to the documentation of the function group_modify, if you use a formula, you must use ". or .x to refer to the subset of rows of .tbl for the given group;" that's why you used .x inside the add_row function. To be entirely consistent, you have to do it also within the first function.

df %>% 
  group_by(id) %>% 
  group_modify(~ add_row(A=4, B=first(.x$B), .x))

# A tibble: 6 x 3
# Groups:   id [3]
  id        A     B
  <chr> <dbl> <dbl>
1 one       1     4
2 one       4     4
3 three     3     6
4 three     4     6
5 two       2     5
6 two       4     5

Using first(.$B) or first(df$B) will provide the same results.

Add row for each group

You can try

library(tidyverse)
d <- data.frame(Serial_number=gl(5, 2), Amplification=1:10, Voltage=20:11)

d %>% 
  split(.$Serial_number) %>% 
  map(~add_row(., Serial_number=unique(.$Serial_number), Amplification=NA, Voltage=NA)) %>% 
  bind_rows()
   Serial_number Amplification Voltage
1              1             1      20
2              1             2      19
3              1            NA      NA
4              2             3      18
5              2             4      17
6              2            NA      NA
7              3             5      16
8              3             6      15
9              3            NA      NA
10             4             7      14
11             4             8      13
12             4            NA      NA
13             5             9      12
14             5            10      11
15             5            NA      NA

Using base R you can try

do.call("rbind", lapply(split(d, d$Serial_number), function(x) rbind(x, c(1, NA, NA))))

Of course you can rbind a new dataframe as mentioned in the comments by Shique

rbind(d,
      data.frame(Serial_number=unique(d$Serial_number), Amplification=NA, Voltage=NA))

Then order the dataframe using d[order(d$Serial_number),]

R add rows to grouped df using dplyr

This should do the trick:

 library(plyr)

 df %>%
   join(subset(df, item_code %in% additional_rows$item_code, select = c(id, item_code)) %>%
        join(additional_rows) %>% 
        subset(!duplicated(.)), type = "full") %>%
   arrange(id, item_code, -score)

Not sure if its the best way, but it works

Edit: to get the score in the same order added the other arrange terms

Edit 2: alright, there should now be no duplicated rows added from the additional rows as per your comment

Can't add rows to grouped data frames

I actually recently made a little helper function for exactly this. The idea
is to use group_modify() to take the group data, and
bind_rows() the summary statistics calculated with summarise().

This is what it looks like in code:

add_summary_rows <- function(.data, ...) {
  group_modify(.data, function(x, y) bind_rows(x, summarise(x, ...)))
}

And here’s how that would work with your data:

library(dplyr, warn.conflicts = FALSE)

df <- data.frame(
  test_id = c(1, 1, 1, 1, 1, 1, 1, 1),
  test_nr = c(1, 1, 1, 1, 2, 2, 2, 2),
  region = c("A", "B", "C", "D", "A", "B", "C", "D"),
  test_value = c(3, 1, 1, 2, 4, 2, 4, 1)
)

df %>% 
  group_by(test_id, test_nr) %>% 
  add_summary_rows(
    region = "MEAN",
    test_value = mean(test_value)
  )
#> # A tibble: 10 x 4
#> # Groups:   test_id, test_nr [2]
#>    test_id test_nr region test_value
#>      <dbl>   <dbl> <chr>       <dbl>
#>  1       1       1 A            3   
#>  2       1       1 B            1   
#>  3       1       1 C            1   
#>  4       1       1 D            2   
#>  5       1       1 MEAN         1.75
#>  6       1       2 A            4   
#>  7       1       2 B            2   
#>  8       1       2 C            4   
#>  9       1       2 D            1   
#> 10       1       2 MEAN         2.75

Adding a row at the end of a group in R

With dplyr, you could use group_modify + add_row:

library(dplyr)

df %>%
  group_by(UserID) %>%
  group_modify(~ .x %>%
    summarise(PathID = max(PathID) + 1, Page = "end") %>%
    add_row(.x, .)
  ) %>%
  ungroup()

# # A tibble: 7 × 3
#   UserID PathID Page
#    <dbl>  <dbl> <chr>
# 1      1      1 home
# 2      1      2 about
# 3      1      3 services
# 4      1      4 end
# 5      5      1 home
# 6      5      2 pricing
# 7      5      3 end

add specific new row using dplyr based on some conditions in r

You can do this by combining do with tibble::add_row. I based this answer on the answer to this question: Add row in each group using dplyr and add_row(), specifically the comment by @JasonWang

df %>%
    dplyr::group_by(ID, semester_num) %>%
    do(tibble::add_row(.,
                       ID = .$ID[1],
                       semester_num = .$semester_num[1],
                       subject_result2 = .$Success[nrow(.)], #Get the last row of the group
                       Success = .$Success[nrow(.)]))

# A tibble: 14 x 4
# Groups:   ID, semester_num [4]
       ID semester_num subject_result2  Success  
    <int>        <dbl> <chr>            <chr>    
 1 100000            1 OTHERPassedTerm1 Grad_ENSC
 2 100000            1 OTHERPassedTerm1 Grad_ENSC
 3 100000            1 OTHERPassedTerm1 Grad_ENSC
 4 100000            1 Grad_ENSC        Grad_ENSC
 5 100000            2 MATH1PassedTerm1 Grad_ENSC
 6 100000            2 OTHERPassedTerm1 Grad_ENSC
 7 100000            2 OTHERPassedTerm1 Grad_ENSC
 8 100000            2 Grad_ENSC        Grad_ENSC
 9 200000            1 OTHERPassedTerm2 fail     
10 200000            1 MATH1PassedTerm2 fail     
11 200000            1 fail             fail     
12 200000            2 MATH1PassedTerm2 fail     
13 200000            2 OTHERPassedTerm2 fail     
14 200000            2 fail             fail  

Normally tibble::add_row won't work with a grouped data frame, but by using do, we can apply it to each group separately without leaving the pipe.

Problems with do add_row and group_by. Need the group_by variable name in add_row

You could add it directly within do.

example %>%
    mutate_if(is.factor, as.character) %>%
    group_by(A) %>%
    do(add_row(., 
               A = unique(.$A),
               B = "ADDED",
               C = "ADDED"))

Or use tidyr::fill at the end. Because it is filling the grouping variable you must ungroup first.

library(tidyr)

example %>%
    mutate_if(is.factor, as.character) %>%
    group_by(A) %>%
    do(add_row(.,
               B = "ADDED",
               C = "ADDED")) %>%
    ungroup() %>%
    fill(A)

# A tibble: 13 x 3
       A     B     C
   <chr> <chr> <chr>
 1     a     z     2
 2     a     x     1
 3     a     y     2
 4     a ADDED ADDED
 5     b     y     1
 6     b     z     1
 7     b ADDED ADDED
 8     c     z     2
 9     c     y     2
10     c     z     2
11     c     y     2
12     c     z     1
13     c ADDED ADDED

Add rows to grouped data with dplyr?

Without dplyr it can be done like this:

as.data.frame(xtabs(Demand ~ Week + Article, data))

giving:

       Week Article Freq
1  2013-W01   10004 1215
2  2013-W02   10004  900
3  2013-W03   10004  774
4  2013-W04   10004 1170
5  2013-W01   10006    0
6  2013-W02   10006    0
7  2013-W03   10006    0
8  2013-W04   10006    5
9  2013-W01   10007    2
10 2013-W02   10007    0
11 2013-W03   10007    0
12 2013-W04   10007    0

and this can be rewritten as a magrittr or dplyr pipeline like this:

data %>% xtabs(formula = Demand ~ Week + Article) %>% as.data.frame()

The as.data.frame() at the end could be omitted if a wide form solution were desired.

Insert dummy multiple X rows between groups with dplyr?

If you want to keep the group ID, I suggest group_modify. First, a helper function to add a blank row to a group

add_blank <- function(x, n=5) {
  tibble::add_row(x, X=rep(NA, n), Y=rep(-10, n))
}

And then you can do

df %>% 
  group_by(group) %>% 
  group_modify(~add_blank(., 5))

---

Related Topics