How use if else in mutate function in R

Question

I have a data.frame DT_new with 4 columns :

1. Graduated (Date format)
2. Work (Date format)
3. Married (Date format)
4. Jumlah (Double format)

Sample:

 Graduated         Work      Married   Jumlah
2015-05-01   2015-05-02   2015-05-03       20
        NA   2015-05-02   2015-05-03       20
        NA           NA   2015-05-03       20
        NA   2015-05-02           NA       20  

I need to aggregate Jumlah by date in Graduated or Work or Married

• when Graduated value is not NA, use date from Graduated
• when Graduated value is NA , use another value from Work or Married

format what I want is :

     Dates   Total 
2015-05-01      10
2015-05-02      40
2015-05-03      30

I have tried aggregate with group by in R but just group by 1 column (Graduated), such as:

DT_Totals = DT_Total %>%
  group_by(Graduated) %>%
  summarise(Total= sum(Jumlah)) %>%
  arrange(Graduated)

How can I handle my problem?

Answer 1

You need first create new column and then group over them.

I got function to return first not NA element in vectors defined as:

first_not_na <- function(...) {
    Reduce(list(...), f=function(x,y) {
        x[is.na(x)] <- y[is.na(x)]
        x
    })
}

And you can use it as follow

DT_new %>%
    group_by(Date = first_not_na(Graduated, Work, Married)) %>%
    summarise(Total = sum(Jumlah)) %>%
    arrange(Date)

or splitting to two steps:

DT_new %>%
    mutate(Date = first_not_na(Graduated, Work, Married)) %>%
    group_by(Date) %>%
    summarise(Total = sum(Jumlah)) %>%
    arrange(Date)

Answer 2

Just create a new date column using ifelse:

DT_new %>% 
  mutate(Date1 = ifelse(!is.na(Graduated), Graduated, ifelse(!is.na(Work), Work, Married))) %>% 
  group_by(Date1) %>%
  summarise(Total = sum(Jumlah)) %>%
  arrange(Date1)

Update

In case the dates are numeric (Date) type:

DT_new %>% 
  mutate(Date1 = ifelse(!is.na(Graduated), Graduated, ifelse(!is.na(Work), Work, Married))) %>% 
  mutate(Date1 = as.Date(Date1, origin = "1970-01-01")) %>% 
  group_by(Date1) %>%
  summarise(Total = sum(Jumlah)) %>%
  arrange(Date1)