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

4
votes

Problem: I want to add_row using dplyr/tibble. I want to group the data by A in my example, and then add_row that contains the group name A and then a value for B.

The problem I am facing is trying to add the Group_by variable A in the column under A. No matter what I try it always comes back with an error or NA as the value in that column.

  1. Reproducible example: 

    example <- data.frame(A = sample(letters[1:3],10,replace = TRUE), 
              B = sample(letters[24:26],10,replace = TRUE),
              C = sample(1:3,10,replace = TRUE))

  2. Output of example data:

          A B C
   1  c y 2
   2  b x 3
   3  c y 1
   4  b y 1
   5  c z 1
   6  a x 1
   7  b x 1
   8  c z 2
   9  a y 3
   10 c y 1

  3. Code I want to run.

    answer <- example %>%
  mutate(A = as.character(A),
         B = as.character(B)) %>%
  group_by(A) %>%
  do(add_row(., 
             B = "ADDED",
             C = "ADDED"))

  4. Output of the data:

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

  5. So in the output of the data, where there is NA I would like it to say the group name (a,b, or c)

I have tried putting just the group variable name there, it does not work throws an error.

Thanks!

3
rdplyrtibble

3 Answers

5
votes

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
0
votes
library(zoo)

df=read.table(text='A     B     C
              1     a     x     1
              2     a     y     3
              3  NA ADDED ADDED
              4     b     x     3
              5     b     y     1
              6     b     x     1
              7  NA ADDED ADDED
              8     c     y     2
              9     c     y     1
              10     c     z     1
              11     c     z     2
              12     c     y     1
              13  NA ADDED ADDED',header=TRUE,stringsAsFactors=FALSE)

df$A=na.locf(df$A)

> df
   A     B     C
1  a     x     1
2  a     y     3
3  a ADDED ADDED
4  b     x     3
5  b     y     1
6  b     x     1
7  b ADDED ADDED
8  c     y     2
9  c     y     1
10 c     z     1
11 c     z     2
12 c     y     1
13 c ADDED ADDED
0
votes
library(tidyverse)

example <- tibble(A = sample(letters[1:3], 10, replace = TRUE),
                  B = sample(letters[24:26], 10, replace = TRUE), 
                  C = sample(1:3, 10, replace = TRUE)) %>% 
  mutate(C = as.character(C)) %>% 
  arrange(A)

to_be_added <- example %>% distinct(A) %>% cbind(B = "ADDED", C = "ADDED")

bind_rows(example, to_be_added) %>% arrange(A)
#> # A tibble: 13 x 3
#>        A     B     C
#>    <chr> <chr> <chr>
#>  1     a     z     2
#>  2     a     y     1
#>  3     a ADDED ADDED
#>  4     b     x     1
#>  5     b     z     1
#>  6     b     z     1
#>  7     b     y     3
#>  8     b     y     1
#>  9     b     y     1
#> 10     b ADDED ADDED
#> 11     c     y     1
#> 12     c     z     1
#> 13     c ADDED ADDED