Dates showing N/A

Posit Professional & Hosted Products Posit Cloud
1

I am new to R. Please give me the idiot explanation to my question please.
I added my data which is a .CSV file from excel into R. Everything is going smoothly except when i try to change the date column from character to date, it will be showing N/A all through in the date column.

daily_activity <- daily_activity %>%
mutate(date = as_date(date, format = "%d-%m-%y"))

daily_activity_sleep_day <- merge(daily_activity, sleep_day, by=c("id", "date"))
glimpse(daily_activity_sleep_day)

daily_activity <- daily_activity %>%
mutate(date = as_date(date, format = "%d-%m-%y"))

id date totalsteps totaldistance trackerdistance loggedactivitiesdistance

1 1503960366 NA 13162 8.5 8.5 0
2 1503960366 NA 10735 6.97 6.97 0
3 1503960366 NA 10460 6.74 6.74 0
4 1503960366 NA 9762 6.28 6.28 0
5 1503960366 NA 12669 8.16 8.16 0
6 1503960366 NA 9705 6.48 6.48 0

:information_source: 9 more variables: veryactivedistance , moderatelyactivedistance ,

lightactivedistance , sedentaryactivedistance , veryactiveminutes ,

fairlyactiveminutes , lightlyactiveminutes , sedentaryminutes ,

calories

2

Since I don't know what your original data could be, I guess this mostly caused by the format argument that passed to as_date(). Here're some examples:

> as_date("07-08-2023", format = "%D-%M-%Y")
[1] NA
Warning message:
 1 failed to parse. 

> as_date("07-08-2023", format = "%d-%m-%y")
[1] NA
Warning message:
 1 failed to parse. 

> as_date("07-08-2023", format = "%d-%m-%Y")
[1] "2023-08-07"

Cuz %M refers to the minute, and %m refers to month in 2-digit form; %D refers to the short form of date, %d refers to the day of month in 2-digit form; %Y refers to the complete 4 numbers of the year and %y refers to the last 2 numbers of the year.

Standard def. of each datetime format param can be found at https://www.ibm.com/docs/en/workload-automation/9.5.0?topic=troubleshooting-date-time-format-reference-strftime

3

Hi @magandmore , welcome!

If you want get a good solution for this, try to put a reproducible example of your data.

See examples:

########

Other way is copy and paste the result of:

head(dput(daily_activity ),30)
4

Thank you M_AcostaCH. Here is the reproducible example of my data.

data.frame(
  stringsAsFactors = FALSE,
                Id = c(1503960366,1503960366,
                       1503960366,1503960366,1503960366,1503960366,1503960366,
                       1503960366,1503960366,1503960366,1503960366,1503960366,
                       1503960366,1503960366,1503960366,1503960366,1503960366,
                       1503960366,1503960366,1503960366),
      ActivityDate = c("12-Apr-2016","13-Apr-2016",
                       "14-Apr-2016","15-Apr-2016","16-Apr-2016","17-Apr-2016",
                       "18-Apr-2016","19-Apr-2016","20-Apr-2016",
                       "21-Apr-2016","22-Apr-2016","23-Apr-2016","24-Apr-2016",
                       "25-Apr-2016","26-Apr-2016","27-Apr-2016","28-Apr-2016",
                       "29-Apr-2016","30-Apr-2016","1-May-2016"),
        TotalSteps = c(13162,10735,10460,9762,
                       12669,9705,13019,15506,10544,9819,12764,14371,10039,
                       15355,13755,18134,13154,11181,14673,10602),
     TotalDistance = c(8.5,6.96999979,6.739999771,
                       6.28000021,8.159999847,6.480000019,8.590000153,
                       9.880000114,6.679999828,6.340000153,8.130000114,9.039999962,
                       6.409999847,9.800000191,8.789999962,12.21000004,
                       8.529999733,7.150000095,9.25,6.809999943)
)

daily_activity <- daily_activity %>%
  mutate(ActivityDate = as_date(date, format = "%d-%m-%y"))

I get the below message when i run the mutate function above but i have installed and loaded the dplyr package. When it worked in a previous work session, my date column was N/A all through. Please help :cry:

:information_source: In argument: ActivityDate = as_date(date, format = "%d-%m-%y").
Caused by error in as.Date.default():
! do not know how to convert 'x' to class “Date”

5

Change this to: format = "%d-%b-%y"

6

Try with this: as.Date()

daily_activity <- daily_activity %>%
  mutate(ActivityDate_2 = as.Date(ActivityDate , format = "%d-%b-%Y"))
7

Thank you so much. It worked. I'm super grateful.

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.