Problem selecting data from a column in R

Question

Could you help me resolve the issue:

Do You know why data_OC is returning 0?. I would like to cell data that have empty DTPE from data_grouped set.

Thank you so much!

data <- structure(
  list(Id=c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1),
       date1 = c("2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20",
                 "2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20",
                 "2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20","2021-06-20",
                 "2021-06-20","2021-06-20","2021-06-20","2021-06-20"),
       date2 = c("2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-04-02",
                 "2021-04-02","2021-04-02","2021-04-02","2021-04-02","2021-04-02","2021-04-03",
                 "2021-04-03","2021-04-03","2021-04-03","2021-04-03","2021-04-08","2021-04-08",
                 "2021-04-09","2021-04-09","2021-04-10","2021-04-10"),
       Week= c("Thursday","Thursday","Thursday","Thursday","Friday","Friday","Friday","Friday",
               "Friday","Friday","Saturday","Saturday","Saturday","Saturday","Saturday","Thursday",
               "Thursday","Friday","Friday","Saturday","Saturday"),
       DTPE = c("Ho","Ho","Ho","Ho","","","","","","","","","","","","","","","","Ho","Ho"),
       D1 = c(8,1,9, 3,5,4,7,6,3,8,2,3,4,6,7,8,4,2,6,2,3), DR01 = c(4,1,4,3,3,4,3,6,3,7,2,3,4,6,7,8,4,2,6,7,3),
       DR02 = c(2,1,4,3,3,4,1,6,3,7,2,3,4,6,7,8,4,2,6,2,3), DR03 = c(7,5,4,3,3,4,1,5,3,3,2,3,4,6,7,8,4,2,6,4,3),
       DR04= c(4,5,6,7,3,2,7,4,2,1,2,3,4,6,7,8,4,2,6,4,3),DR05 = c(9,5,4,3,3,2,1,5,3,7,2,3,4,7,7,8,4,2,6,4,3)),
  class = "data.frame", row.names = c(NA, -21L))
                                      

dim_data<-dim(data)

day<-c(seq.Date(from = as.Date(data$date2[1]), by = "days",
                length = dim_data[1]
)) 

data_grouped <- data %>%
  mutate(across(starts_with("date"), as.Date)) %>%
  group_by(date2) %>%
  summarise(Id = first(Id),
            date1 = first(date1),
            Week = first(Week),
            DTPE = first(DTPE),
            D1 = sum(D1)) %>%
  select(Id,date1,date2,Week,DTPE,D1)

data_grouped <- data_grouped %>% mutate(date1=format(date1,"%d/%m/%Y"),
                                    date2=format(date2,"%d/%m/%Y"))
    data_grouped<-data.frame(data_grouped)
  Id      date1      date2     Week DTPE D1
1  1 20/06/2021 02/04/2021   Friday      33
2  1 20/06/2021 03/04/2021 Saturday      22
3  1 20/06/2021 08/04/2021 Thursday      12
4  1 20/06/2021 09/04/2021   Friday       8
5  1 20/06/2021 10/04/2021 Saturday   Ho  5
6  1 20/06/2021 01/07/2021 Thursday   Ho 21

data_OC<-subset(data_grouped,is.na(DTPE))
data_OC
[1] Id    date1 date2 Week  DTPE  D1   
<0 linhas> (ou row.names de comprimento 0)

from = as.Date(df$date2[1])... Did you mean as.Date(data$date2[1])? — Martin Gal
– Martin Gal, Commented Aug 28, 2021 at 16:11

Shibaprasad · Accepted Answer · 2021-08-28 16:20:15Z

3

Apart from the answer provided, you can do one more thing, if you want your existing code to work.

Just add this line, so that the empty cells will be turned into NA

data_grouped$DTPE[data_grouped$DTPE==''] <- NA

Now, if you run the last two lines of your code, you'll have the desired output

data_OC<-subset(data_grouped,is.na(DTPE))
data_OC

Id date1      date2      Week     DTPE     D1
  <dbl> <chr>      <chr>      <chr>    <chr> <dbl>
1     1 20/06/2021 02/04/2021 Friday   NA       33
2     1 20/06/2021 03/04/2021 Saturday NA       22
3     1 20/06/2021 08/04/2021 Thursday NA       12
4     1 20/06/2021 09/04/2021 Friday   NA        8

answered Aug 28, 2021 at 16:20

Shibaprasad

1,3321 gold badge9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Gal · Accepted Answer · 2021-08-28 16:15:30Z

2

Your code looks for row that are NA in column DTPE. But, as shown in your data, the "empty" cells of DTPE aren't acutally empty but contain an empty string "". So either you change "" into NA

library(dplyr)
data_grouped %>% 
  mutate(DTPE = na_if(DTPE, ""))

followed by your subset-statement or you subset by

subset(data_grouped, DTPE == "")

Since you are using tidyverse, you could and should use filter() and stay in the pipeline.

answered Aug 28, 2021 at 16:15

Martin Gal

17k5 gold badges24 silver badges42 bronze badges

1 Comment

Phil Over a year ago

subset(data_grouped, DTPE == "") is really the simplest thing to do. Could also do subset(data_grouped, stringr::str_length(DTPE) == 0)

Collectives™ on Stack Overflow

Problem selecting data from a column in R

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related