How to create a date (column) from a date-time (column) in R-CodePudding

I have imported a CSV containing dates in the column "Activity_Date_Minute". The date value for example is "04/12/2016 01:12:00". Now when I read the .csv into a dataframe and extract only the date this gives me date in the column as 4-12-20. Can someone help how to get the date in mm-dd-yyyy in a separate column?

Tried the below code. Was expecting to see a column with dates e.g 04/12/2016 (mm/dd/yyyy).

#Installing packages

install.packages("tidyverse")
library(tidyverse)

install.packages('ggplot2')
library(ggplot2)

install.packages("dplyr")
library(dplyr)

install.packages("lubridate")
library(lubridate)
##Installing packages

install.packages("tidyverse")
library(tidyverse)

install.packages('ggplot2')
library(ggplot2)

install.packages("dplyr")
library(dplyr)

install.packages("lubridate")
library(lubridate)

##Reading minute-wise METs into "minutewiseMET_Records" and summarizing MET per day for all the IDs

minutewiseMET_Records <- read.csv("minuteMETsNarrow_merged.csv")
str(minutewiseMET_Records)
## converting column ID to character,Activity_Date_Minute to date 
minutewiseMET_Records$Id <- as.character(minutewiseMET_Records$Id)
minutewiseMET_Records$Date <- as.Date(minutewiseMET_Records$Activity_Date_Minute)

str(minutewiseMET_Records)

The Console is as follows:

minutewiseMET_Records <- read.csv("minuteMETsNarrow_merged.csv") str(minutewiseMET_Records) 'data.frame': 1048575 obs. of 3 variables: $ Id : num 1.5e 09 1.5e 09 1.5e 09 1.5e 09 1.5e 09 ... $ Activity_Date_Minute: chr "04/12/2016 00:00" "04/12/2016 00:01" "04/12/2016 00:02" "04/12/2016 00:03" ... $ METs : int 10 10 10 10 10 12 12 12 12 12 ...

converting column ID to character,Activity_Date_Minute to date

minutewiseMET_Records$Id <- as.character(minutewiseMET_Records$Id) minutewiseMET_Records$Date <- as.Date(minutewiseMET_Records$Activity_Date_Minute)

converting column ID to character,Activity_Date_Minute to date

minutewiseMET_Records$Id <- as.character(minutewiseMET_Records$Id) minutewiseMET_Records$Date <- as.Date(minutewiseMET_Records$Activity_Date_Minute) str(minutewiseMET_Records) 'data.frame': 1048575 obs. of 4 variables: $ Id : chr "1503960366" "1503960366" "1503960366" "1503960366" ... $ Activity_Date_Minute: chr "04/12/2016 00:00" "04/12/2016 00:01" "04/12/2016 00:02" "04/12/2016 00:03" ... $ METs : int 10 10 10 10 10 12 12 12 12 12 ... $ Date : Date, format: "4-12-20" "4-12-20" ...

CodePudding user response：

I think this will work for you

minutewiseMET_Records$Date <- format(as.Date(minutewiseMET_Records$Activity_Date_Minute, format = "%d/%m/%Y"),"%m/%d/%Y")

Fist of all you have to tell R the format of your initial data. Then, you ask it which is the format you want for the output.

CodePudding user response：

Activity_Date_Minute isn’t a datetime in your initial data, it’s a character. So you’ll have to first convert it to a datetime (e.g., using lubridate::mdy_hm()), then use as.Date().

library(dplyr)
library(lubridate)

minutewiseMET_Records %>%
  mutate(
    Activity_Date_Minute = mdy_hm(Activity_Date_Minute), 
    Activity_Date = as.Date(Activity_Date_Minute)
  )

# A tibble: 4 × 2
  Activity_Date_Minute Activity_Date
  <dttm>               <date>       
1 2016-04-12 00:00:00  2016-04-12   
2 2016-04-12 00:01:00  2016-04-12   
3 2016-04-12 00:02:00  2016-04-12   
4 2016-04-12 00:03:00  2016-04-12