Remove not increasing rows based on other columns values

user_18748625 0
Viewed 42

I have a data frame on R and I want to remove all rows that are not increasing in my column 3. Each row have to be higher or equal than the previous one. But my main difficulty is that the rows have to increase according other columns 1 and 2. In my example, Column3 have to increase according Column1 [A-B] and 2 [1:4]. Here, Column1 [B] have to be removed because 199>197.

PS : It is CO2 measurements corresponding to many plots and date. When the CO2 measurments is not monotonous in the time, the measurement is wrong.

Column1 Column2 Column3
A 1 200
A 2 202
A 3 204
A 4 207
B 1 199
B 2 197
B 3 200
B 4 202
dataframe r loops
2 Answers

You can use diff() to determine if a group is increasing.

subset(df, ave(Column3, Column1, FUN = \(x) all(diff(x) >= 0)) == 1)

#   Column1 Column2 Column3
# 1       A       1     200
# 2       A       2     202
# 3       A       3     204
# 4       A       4     207

Its dplyr equivalent:

library(dplyr)

df %>%
  group_by(Column1) %>%
  filter(all(diff(Column3) >= 0)) %>%
  ungroup()
user_100689850

There may be an easier way to go about it, but here is an approach:

If you want just the observation that violates the condition removed (here, the observation with value 197 in it), try this:

df %>% group_by(Column1) %>% 
  mutate(del = (lag(Column3) > Column3)) %>% 
  filter(!del|is.na(del)) %>% 
  select(-del)

Output:

# Column1 Column2 Column3
# <chr>     <int>   <int>
# 1 A             1     200
# 2 A             2     202
# 3 A             3     204
# 4 A             4     207
# 5 B             1     199
# 6 B             3     200
# 7 B             4     202

If you want to remove all the observations from a given group where the condition is not met (here, group b)

df %>% group_by(Column1) %>% 
  mutate(del = any((lag(Column3) > Column3), na.rm = TRUE)) %>% 
  filter(!del) %>% 
  select(-del)

Output:

# Column1 Column2 Column3
# <chr>     <int>   <int>
# 1 A             1     200
# 2 A             2     202
# 3 A             3     204
# 4 A             4     207

Data used in this example:

df <- read.table(text = "Column1    Column2 Column3
A   1   200
A   2   202
A   3   204
A   4   207
B   1   199
B   2   197
B   3   200
B   4   202", header = TRUE)
user_121097880
Related