Changing a character into a date format after checking the entry of another column/row

Question

I have a data set with three fields. I would like to replace any row that has 'Annual' in it to an appropriate date.

data:

df <- structure(list(`Grants Period Month` = c("Annual", "01-2014-12", 
"Annual", "Annual", "01-2013-06", "Annual"), `Due Date` = structure(c(16525, 
16437, 16160, 17256, 15888, 16160), class = "Date"), `Late/Timely Flag` = c("On-Time", 
"Late", "Late", "Late", "On-Time", "Late")), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

#here's a look at my data
# A tibble: 6 x 2
 `Grants Period Month` `Due Date`    `Late/Timely Flag`
  <chr>                <date>            <chr>         
1 Annual               2015-03-31       On-Time    
2 01-2014-12           2015-01-02        Late          
3 Annual               2014-03-31        Late             
4 Annual               2017-03-31        Late             
5 01-2013-06           2013-07-02       On-Time   
6 Annual               2014-03-31        Late

I would like to change any row with the entry 'Annual' in it to the due date entry of that particular row.

#Expected result
# A tibble: 6 x 2
 `Grants Period Month` `Due Date`    `Late/Timely Flag`
  <chr>                <date>            <chr>         
1 2015-03-31           2015-03-31       On-Time    
2 01-2014-12           2015-01-02        Late          
3 2014-03-31           2014-03-31        Late             
4 2017-03-31           2017-03-31        Late             
5 01-2013-06           2013-07-02       On-Time   
6 2014-03-31           2014-03-31        Late

`mutate(df, Grants Period Month = ifelse(Grants Period Month == "Annual", Due Date, Grants Period Month))` Play with the column names until they match...or better use `df %>% janitor::clean_names()` to obtain names first — Matias Andina, May 23 '19 at 21:43
Please post your data in the form that I added in my edit. You can get the structure by using `dput(df)` or `dput(head(df,6))` if your dataset is large. — M--, May 23 '19 at 22:14

score 1 · Answer 1 · edited May 23 '19 at 22:21

1

You could do something like

# df is your data frame
is_annual <- df[["Grants Period Month"]] == "Annual"
df[["Grants Period Month"]][is_annual] <- as.character(df[["Due Date"]][is_annual])

edited May 23 '19 at 22:21

M--

25,431
8
61
93

answered May 23 '19 at 21:44

niko

5,253
1
12
32

You should also test your solution before posting. It doesn't work like this. You need `as.character()` – M-- May 23 '19 at 22:15

Matias Andina · Answer 2 · 2019-05-23T23:55:15.363

library(dplyr)
mutate(df, `Grants Period Month` = ifelse(`Grants Period Month` == "Annual",
                                                    as.character(`Due Date`), 
                                                      `Grants Period Month`))

First use df %>% janitor::clean_names(). It will give you clean names and the code above, and in general your data, becomes more legible with those new names.

UPDATE

I went for the quick and dirty, no back ticks, doing from cellphone...Here's my take from a proper keyboard and R session. Be aware that all dates that fail to parse will be converted to due date. I also added the new data in new columns instead of overwriting.

df <- structure(list(`Grants Period Month` = c("Annual", "01-2014-12", "Annual", "Annual", "01-2013-06", "Annual"), `Due Date` = structure(c(16525, 16437, 16160, 17256, 15888, 16160), class = "Date"), `Late/Timely Flag` = c("On-Time", "Late", "Late", "Late", "On-Time", "Late")), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))


    library(dplyr)
    df %>%
      mutate(grants_period = lubridate::myd(`Grants Period Month`),
             due_date = lubridate::ymd(`Due Date`)) %>%
      mutate(new_col=ifelse(is.na(grants_period),
                                  due_date,
                                  grants_period)) %>%
      mutate(new_col = lubridate::as_date(new_col))

which produces

# A tibble: 6 x 6
  `Grants Period Month` `Due Date` `Late/Timely Flag` grants_period due_date   new_col   
  <chr>                 <date>     <chr>              <date>        <date>     <date>    
1 Annual                2015-03-31 On-Time            NA            2015-03-31 2015-03-31
2 01-2014-12            2015-01-02 Late               2014-01-12    2015-01-02 2014-01-12
3 Annual                2014-03-31 Late               NA            2014-03-31 2014-03-31
4 Annual                2017-03-31 Late               NA            2017-03-31 2017-03-31
5 01-2013-06            2013-07-02 On-Time            2013-01-06    2013-07-02 2013-01-06
6 Annual                2014-03-31 Late               NA            2014-03-31 2014-03-31

```df %>% mutate(`Grants Period Month` = ifelse(`Grants Period Month` == "Annual", as.character(`Due Date`), `Grants Period Month`))``` — M--, May 23 '19 at 22:10
and if you want to test it, here is ```df <- structure(list(`Grants Period Month` = c("Annual", "01-2014-12", "Annual", "Annual", "01-2013-06", "Annual"), `Due Date` = structure(c(16525, 16437, 16160, 17256, 15888, 16160), class = "Date"), `Late/Timely Flag` = c("On-Time", "Late", "Late", "Late", "On-Time", "Late")), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))``` — M--, May 23 '19 at 22:12
*Because your column names have spaces this code will not work. Play with the column names until they match...* This is wrong. The code above doesn't work because you need to use ```(variable = `column name`)``` if you have space. And also, it doesn't work because you need to consider type of input and output column when mutating. — M--, May 23 '19 at 22:16
@M-M thanks for the comments, I edited the answer to something that actually works. Also "play with the column names" was my friendly way of saying "use backticks or fix your column names to something with no spaces" — Matias Andina, May 23 '19 at 22:30

Changing a character into a date format after checking the entry of another column/row

2 Answers2

UPDATE