R- add row to data frame after a specific sting/ pattern

Question

I am trying to fill in some missing data in my data frame, which looks like:

ID  Value
1234    0001A
Text    Text 2
1235    0001A
1236    0001A
Text    Text 2
1237    0001A
1238    0001A
1239    0001A
Text    Text 2
1240    0001A

What I want is after every numeric value in ID I want to insert a Text row, so the final result would be:

ID  Value
1234    0001A
Text    Text 2
1235    0001A
Text    
1236    0001A
Text    Text 2
1237    0001A
Text    
1238    0001A
Text    
1239    0001A
Text    Text 2
1240    0001A
Text

I had found this answer and Ive tried adapting it to my requirements but no joy. Answer adding row based on presence of NA values

Example

df <- structure(list(ID = c("1234", "Text", "1235", "1236", "Text", "1237", "1238", "1239", "Text", "1240"), Value = structure(c(1L, 
2L, 1L, 1L, 2L, 1L, 1L, 1L, 2L, 1L), .Label = c("0001A", "Text 2"), class = "factor")), row.names = c(NA, -10L), class = "data.frame")

Note: ID column exists as a character element.

score 1 · Accepted Answer · answered May 25 '20 at 08:10

We can add a row number as a reference to arrange the data later.

library(dplyr)
df <- df %>% mutate(row = row_number())

Filter the rows which has a number in ID but is not followed by a 'Text', change the ID value to 'Text' and Value to empty string and bind to the original dataframe and arrange.

df %>%
  filter(grepl('\\d+', ID) & lead(ID, default = last(ID)) != 'Text') %>%
  mutate(ID = 'Text', Value = '') %>%
  bind_rows(df) %>%
  arrange(row, desc(Value)) %>%
  select(-row)


#     ID  Value
#1  1234  0001A
#2  Text Text 2
#3  1235  0001A
#4  Text       
#5  1236  0001A
#6  Text Text 2
#7  1237  0001A
#8  Text       
#9  1238  0001A
#10 Text       
#11 1239  0001A
#12 Text Text 2
#13 1240  0001A
#14 Text

score 1 · Answer 2 · answered May 25 '20 at 08:11

You can use grepl to find where you have numbers and diff to find places where to insert a new row. With lapplyyou go to each row an insert if needed with rbind an new row.

i <- grepl("^[0-9]+$", df$ID)
j <- c(diff(i) == 0, i[length(i)])
do.call(rbind, lapply(1:nrow(df), function(x) rbind(df[x,]
 , data.frame(ID="Text", Value="")[j[x]])))
#     ID  Value
#1  1234  0001A
#2  Text Text 2
#3  1235  0001A
#11 Text       
#4  1236  0001A
#5  Text Text 2
#6  1237  0001A
#12 Text       
#7  1238  0001A
#13 Text       
#8  1239  0001A
#9  Text Text 2
#10 1240  0001A
#14 Text

R- add row to data frame after a specific sting/ pattern

2 Answers2