CODEWITHSUNDEEP
r
deslaur
deslaur

Reputation: 43

Replace blank date field with another date field

I'm trying to replace a date field that has blanks, with another date field (which may also contain blanks). I'm trying to use an ifelse function which is: 

companies$Date.Closed <- ifelse(companies$Date.Closed== "", companies$Date.Hidden,companies$Date.Closed)

This isn't working. For reference of what my data looks like

Company    Date.Closed    Date.Hidden
CompanyA   2018-01-01     2018-01-01
CompanyB                  2018-02-01
CompanyC
CompanyD   2018-02-01

Note: the blanks show up as grey "NA"'s in r Basically, if Date.Closed is blank, fill it with Date.Hidden. When I try my code above, I'm getting the 

Error in charToDate(x):character string is not in a standard unambiguous format

But I know both my dates are in the proper format because I can use them in other lines of code, such as a subset based on date.

Thanks

dput(companies$Date.Closed) returns a bunch of NA's and a number where a date presumably existed, like: NA, NA, 17371

Upvotes: 2

Views: 814

Answers (1)

Maurits Evers
Maurits Evers

Reputation: 50718

For future posts, it's always better to include sample data as the ouput of dput. That way we not only know the data but also the data types.

Solution 1: Assuming Date.Closed and Date.Hidden are factors

In base R you can do

df$Date.Closed = ifelse(
    df$Date.Closed == "", 
    as.character(df$Date.Hidden), 
    as.character(df$Date.Closed))
df
#   Company Date.Closed Date.Hidden
#1 CompanyA  2018-01-01  2018-01-01
#2 CompanyB  2018-02-01  2018-02-01
#3 CompanyC
#4 CompanyD  2018-02-01

or alternatively

df <- transform(df, Date.Closed = ifelse(
    Date.Closed == "", 
    as.character(Date.Hidden), 
    as.character(Date.Closed)))

or the tidyverse way

library(tidyverse)
df %>%
    mutate(Date.Closed = if_else(Date.Closed == "", Date.Hidden, Date.Closed))
#   Company Date.Closed Date.Hidden
#1 CompanyA  2018-01-01  2018-01-01
#2 CompanyB  2018-02-01  2018-02-01
#3 CompanyC
#4 CompanyD  2018-02-01

Solution 2: Assuming Date.Closed and Date.Hidden are Date objects

df %>%
    mutate(Date.Closed = if_else(is.na(Date.Closed), Date.Hidden, Date.Closed))
#   Company Date.Closed Date.Hidden
#1 CompanyA  2018-01-01  2018-01-01
#2 CompanyB  2018-02-01  2018-02-01
#3 CompanyC        <NA>        <NA>
#4 CompanyD  2018-02-01        <NA>

Sample data

df <- read.table(text =
    "Company    Date.Closed    Date.Hidden
CompanyA   2018-01-01     2018-01-01
CompanyB   ''               2018-02-01
CompanyC   ''   ''
CompanyD   2018-02-01   ''", header = T)

Upvotes: 1

Related Questions