Home > other >  Insert specific N blank rows depending on column value
Insert specific N blank rows depending on column value

Time:12-03

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

CodePudding user response:

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  

CodePudding user response:

Using R base

do.call(rbind, c(make.row.names=FALSE, lapply(split(df, df$y), function(z){
  x <- y <- rep(NA, z$x)
  rbind(cbind(x, y), z)
}) ))
    x        y
1   0  chicago
2  NA     <NA>
3  NA     <NA>
4  NA     <NA>
5   3    Colon
6  NA     <NA>
7   1   havana
8  NA     <NA>
9   1   la paz
10 NA     <NA>
11  1     lima
12  0    Miami
13 NA     <NA>
14 NA     <NA>
15  2 new york
  • Related