Insert specific N blank rows depending on column value

December 2, 2022

I have a data frame looking like this :

> df <- data.frame(x = c(1,0,2,0,1,3,1),
+                  y = c("lima","chicago","new york","Miami","havana","Colon","la paz"))
> df
  x        y
1 1     lima
2 0  chicago
3 2 new york
4 0    Miami
5 1   havana
6 3    Colon
7 1   la paz

I would like to find a way to insert blank N rows depending on the value of column x
so if x is 1, 1 blank row would be inserted above, if x is 3, 3 blank rows would be inserted above. The desired output for the data frame above should be this:

> df
    x        y
1  NA     <NA>
2   1     lima
3   0  chicago
4  NA     <NA>
5  NA     <NA>
6   2 new york
7   0    Miami
8  NA     <NA>
9   1   havana
10 NA     <NA>
11 NA     <NA>
12 NA     <NA>
13  3    Colon
14 NA     <NA>
15  1   la paz

>Solution :

We could do it this way:
We group by row number and add to each row number x + 1 rows.
Using a trick we can show the NA’s first:

library(dplyr)

df %>% 
  group_by(ID = row_number()) %>% 
  summarise(cur_data()[seq(x+1),]) %>% 
  arrange(!is.na(x), x, .by_group = TRUE) %>% 
  ungroup() %>% 
  select(-ID)

       x y       
   <dbl> <chr>   
 1    NA NA      
 2     1 lima    
 3     0 chicago 
 4    NA NA      
 5    NA NA      
 6     2 new york
 7     0 Miami   
 8    NA NA      
 9     1 havana  
10    NA NA      
11    NA NA      
12    NA NA      
13     3 Colon   
14    NA NA      
15     1 la paz