Remove rows between two specific rows

Question

I have a small issue regarding removing specific rows. In this example, I would like to remove the rows from the word "5055" in the column "power" until the word "Exer" in the column "fr". Importantly, I would like to apply this function in both id (Here, LM01-PRD-S1 and LB02-PRD-S1).

                   time   power        hr     fr          id

 1                  <NA>  5055       Zoti      E LM01-PRD-S1
 2              747 mmHg  <NA>       09/0   2016 LM01-PRD-S1
 3 9.7222222222222224E-3     0         76     20 LM01-PRD-S1
 4  2.013888888888889E-2     0         77     16 LM01-PRD-S1
 5 2.9861111111111113E-2     0         77     17 LM01-PRD-S1
 6                  <NA>  <NA>       <NA>   Exer LM01-PRD-S1
 7 1.0416666666666666E-2    25         90     24 LM01-PRD-S1
 8 1.9444444444444445E-2    25         92     23 LM01-PRD-S1
 9 3.0555555555555555E-2    25         93     22 LM01-PRD-S1
10                  <NA>  5055       Zoti      E LB02-PRD-S1
11              750 mmHg  <NA>       11/0   2016 LB02-PRD-S1
12 8.3333333333333332E-3     0         81     14 LB02-PRD-S1
13 1.6666666666666666E-2     0         96     15 LB02-PRD-S1
14 2.8472222222222222E-2     0         71     14 LB02-PRD-S1
15                  <NA>  <NA>       <NA>   Exer LB02-PRD-S1
16 1.0416666666666666E-2    35        102     16 LB02-PRD-S1
17 1.9444444444444445E-2    35        101     17 LB02-PRD-S1
18 3.0555555555555555E-2    35        105     15 LB02-PRD-S1

I tried this function, but I removed rows 1 to 15, while I would like to remove only rows 1 to 6 and 10 to 15.

df[-c(min(grep("5055",df[,power])):max(grep("Exer",df[,fr]))),]

Here is the final result I would like to obtain.

                   time power    hr    fr          id
1 1.0416666666666666E-2    25    90    24 LM01-PRD-S1
2 1.9444444444444445E-2    25    92    23 LM01-PRD-S1
3 3.0555555555555555E-2    25    93    22 LM01-PRD-S1
4 1.0416666666666666E-2    35   102    16 LB02-PRD-S1
5 1.9444444444444445E-2    35   101    17 LB02-PRD-S1
6 3.0555555555555555E-2    35   105    15 LB02-PRD-S1

I hope I explained well. Thank you for your help!

Are these two words unique, i.e. does each only occur once? Can you add sample data to your question? — Tim Biegeleisen, Nov 13 '19 at 07:08
Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. — Ronak Shah, Nov 13 '19 at 08:14
I updated my question based on the reproductible example. I hope it is better now, and easier to understand. Thank you. — MaxB, Nov 14 '19 at 07:58

dc37 · Answer 1 · 2019-11-14T15:50:23.417

0

Here a solution. It's definitely not the most elegant but it is doing the job.

1) Defining positions of each target 5055 and Exer

vec5055 = grep("5055",df[,"power"])
vecExer = grep("Exer",df[,"fr"])

Then, we are creating a new vector VEC that will contain all rows we wanted to deleted and we will apply to the dataframe df:

if(length(vec5055) == length(vecExer)){
  VEC = NULL
  for(i in 1:length(vec5055))
  {
    VEC = c(VEC,vec5055[i]:vecExer[i])
  }
  df = df[-VEC,]
}

And you should get the dataset you are expecting. I'm sure it exists other solutions more straightforward but for now it's the only one that cross my mind.

Let me know if it is ok for you

edited Nov 14 '19 at 15:50

answered Nov 13 '19 at 07:53

dc37

15,840
4
15
32

Thank you for your help. I would like to specify the words (here "address" and "Exercice") for making the reference. – MaxB Nov 13 '19 at 08:03
I was thinking using the grep function, but I am not able to use it. the words "address" and "Exercice" are repeated several times in the same columns – MaxB Nov 13 '19 at 08:12
@MaxB, I'm not sure that this will work as words `address` and `exercise` are present twice in your dataframe, so how R will know which lines you want to delete. Moreover, does your images is what you obtain when you open the dataframe in R (`View(name_of_your_dataframe`) ? It look like an excel sheet and I don't think your dataframe will have the same structure once open in R. I think you should really try to post a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – dc37 Nov 13 '19 at 08:16
Yes I want R to delete every rows between these two words ("address" as first and "Exercise" as second), even if it is present twice or more. I just used a printscreen of an excel sheet, I am am really new using R. Thank you. – MaxB Nov 13 '19 at 08:30
I edited my code to propose you a solution but you should really provide the output of your dataset in R (not in Excel) (at least a printscreen of your dataset in Rstudio, if you don't know how to print it in the console). – dc37 Nov 13 '19 at 08:47
I updated my question based on the reproductible example. I hope it is better now, and easier to understand. Thank you. – MaxB Nov 14 '19 at 07:57
I edited my answer to reflect a new solution to your updated question. Let me if it is working – dc37 Nov 14 '19 at 15:50

Remove rows between two specific rows

1 Answers1