Filtering , classifying and creating a new variable based on a reference datetime

user_15587184 0
Viewed 40

I am currently working with a dataset containing the records of some water-supply tanks that shows the DATE of technical inspections of 5 different tanks (IDENT) and also the TYPE of inspection recorded which can only have two values "READ" when the tank is working properly and "ERROR" when otherwise performing poorly.

IDENT DATE TYPE
X3 30/04/2021 ERROR
X1 1/05/2021 READ
X1 2/05/2021 ERROR
X4 3/05/2021 READ
X9 4/05/2021 ERROR
X6 5/05/2021 READ
X1 6/05/2021 READ
X3 7/05/2021 ERROR
X3 8/05/2021 READ

I have to create a dataframe that can filter and select every TYPE="ERROR" DATE for each water tank (is there is no error recorded on the dateset for a specify tank is not necessary to show it) and show the latest TYPE="READ" DATE prior to the each tank's ERROR and also the latest DATE After each tank's error date, to illustrate I am to achieve this table:

| IDENT | READ_PRIOR |    ERROR   | POST_READ |
|:-----:|:----------:|:----------:|:---------:|
|   X3  |     NA     | 30/04/2021 | 8/05/2021 |
|   X3  |     NA     |  7/05/2021 | 8/05/2021 |
|   X1  |  1/05/2021 |  2/05/2021 | 6/05/2021 |
|   X9  |     NA     |  4/05/2021 |     NA    |

What Have I tried?

I have started working on this problem by arranging the data set in chronological order by DATE and also grouping by IDENT using the tidyverse package, also I can select the latest DATE for a group using the top_n function but my issue is that I cant seem to find a way to successfully filter or select the latest dates for a tank before and after the reference TYPE="ERROR" so that's where I am bumping my head. Thank you so much for helping me put guys I truly appreciate it.

Code:

df<- tibble::tribble(
~IDENT  ~DATE   ~TYPE,
        
"X3",   "30/04/2021",   "ERROR",
        
"X1",   "1/05/2021",    "READ",
        
"X1",   "2/05/2021",    "ERROR",
        
"X4",   "3/05/2021",    "READ",
        
"X9",   "4/05/2021",    "ERROR",
        
"X6",   "5/05/2021",    "READ",
        
"X1",   "6/05/2021",    "READ",
        
"X3",   "7/05/2021",    "ERROR",
        
"X3",   "8/05/2021",    "READ")

Thank you so much guys!

data-wrangling tidyverse filtering r date
1 Answers

We convert the 'DATE' column to Date class (dmy - from lubridate), arrange the rows by 'IDENT' and 'DATE', grouped by 'IDENT', recode the 'READ' values to 'READ_PRIOR' and 'POST_READ' with case_when based on the presence of 'ERROR', remove the 'IDENT' groups that have no 'ERROR' value in 'TYPE', reshape to 'wide' format with pivot_wider and fill the values in 'POST_READ' with non-NA adjacent values for each 'IDENT'

library(dplyr)
library(tidyr)
library(data.table)
library(lubridate)
df %>% 
  mutate(DATE = dmy(DATE)) %>%
  arrange(IDENT, DATE) %>% 
  group_by(IDENT) %>% 
  mutate(TYPE = case_when(TYPE == 'READ' & lead(TYPE) == 'ERROR' 
     ~ 'READ_PRIOR', TYPE == 'READ' ~ 'POST_READ', TRUE ~ TYPE)) %>%       
  filter('ERROR' %in% TYPE) %>%     
  mutate(rn = rowid(IDENT, TYPE)) %>% 
  pivot_wider(names_from = TYPE, values_from = DATE) %>% 
  fill(POST_READ) %>%
  ungroup %>%
  select(-rn)

-output

# A tibble: 4 x 4
#  IDENT READ_PRIOR ERROR      POST_READ 
#  <chr> <date>     <date>     <date>    
#1 X1    2021-05-01 2021-05-02 2021-05-06
#2 X3    NA         2021-04-30 2021-05-08
#3 X3    NA         2021-05-07 2021-05-08
#4 X9    NA         2021-05-04 NA
user_37322710
Related