CODEWITHSUNDEEP
rtidyverse
cephalopod
cephalopod

Reputation: 1906

how to enter several empty rows in a df based on a string within a column

I have an output df into which I'm trying to insert blank rows after every closing_balentry in the column named placement_status_type. The idea is that after I insert blank rows I save as an excel file to make it easy for the final user to read the numbers in excel.

I know the add_rowfunction but can't work out a way to use it within a condition.

sample data:
df <- data.frame(stringsAsFactors=FALSE,
         placement_status_type = c("opening_bal", "New", "Transfer", "Reinstated",
                                   "Suspended", "Exit", "closing_bal",
                                   "opening_bal", "New", "Transfer", "Reinstated",
                                   "Suspended", "Exit", "closing_bal", "opening_bal",
                                   "New", "Transfer", "Exit", "closing_bal",
                                   "opening_bal", "New", "Exit", "closing_bal",
                                   "Transfer", "Exit", "closing_bal", "Transfer",
                                   "Suspended", "Exit", "closing_bal"),
                        Aug_18 = c(173, 11, -6, 16, -21, -9, 164, 5, 4, 0, 3, 0, -2,
                                   10, 17, 6, -1, -4, 18, -1, 0, 0, -1, 0, 0,
                                   0, 0, 0, 0, 0)
      )

Upvotes: 3

Views: 432

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 389047

add_row can add only one row at a time. We can split the dataframe on every occurrence of "closing_bal" and then add_row for each group.

library(tidyverse)

df %>%
   group_split(c(0, 
     cumsum(placement_status_type == "closing_bal")[-nrow(df)]), keep = FALSE) %>%
   map_dfr(~add_row(., placement_status_type = "", Aug_18 = 0))


# A tibble: 36 x 2
#   placement_status_type Aug_18
#   <chr>                  <dbl>
# 1 opening_bal              173
# 2 New                       11
# 3 Transfer                  -6
# 4 Reinstated                16
# 5 Suspended                -21
# 6 Exit                      -9
# 7 closing_bal              164
# 8 ""                         0
# 9 opening_bal                5
#10 New                        4
# … with 26 more rows

Similarly, we can also use do if we want to avoid splitting and row binding the dataframe

df %>%
 group_by(group = c(0, 
         cumsum(placement_status_type == "closing_bal")[-nrow(df)])) %>%
 do(add_row(., placement_status_type = "", Aug_18 = 0)) %>%
 ungroup() %>%
 select(-group)

As a general solution if we have want to add a particular row multiple times we can create it as a separate tibble

add_df <- tibble(placement_status_type = "", Aug_18 = 0)

and repeat it accordingly

n <- 3

df %>%
  group_split(c(0, 
   cumsum(placement_status_type == "closing_bal")[-nrow(df)]), keep = FALSE) %>%
   map_dfr(~bind_rows(., add_df[rep(seq_len(nrow(add_df)), n), ]))

With do that would be

df %>%
  group_by(group = c(0, 
    cumsum(placement_status_type == "closing_bal")[-nrow(df)])) %>%
  do(bind_rows(., add_df[rep(seq_len(nrow(add_df)), n), ])) %>%
  ungroup() %>%
  select(-group)

All of these can be achieved in base R as well

do.call(rbind, lapply(split(df, 
  c(0, cumsum(df$placement_status_type == "closing_bal")[-nrow(df)])), function(x) 
   rbind(x, add_df[rep(seq_len(nrow(add_df)), n), ])))

Upvotes: 5

Chris Holbrook
Chris Holbrook

Reputation: 2636

Since add_row only adds one at a time, you can simply get the indices of the closing bal rows and then loop through them, simply accounting for number of rows previously added. 

#get closing bal row numbers
foo <- which(df$placement_status_type == "closing_bal")

#iteratively add new row using add_row
# while accounting for previous rows
for(i in 1:length(foo))
  df <- tibble::add_row(df, placement_status_type = NA, Aug_18 = NA, .after = foo[i] + (i - 1))

Upvotes: 2

Related Questions