Can you specify what space to separate columns by?

user_19064289 0
Viewed 33

I am working with a data set called sleep with the following columns:

  head(sleep)

  Id              SleepDay TotalSleepRecords TotalMinutesAsleep TotalTimeInBed
1 1503960366 4/12/2016 12:00:00 AM                 1                327            346
2 1503960366 4/13/2016 12:00:00 AM                 2                384            407
3 1503960366 4/15/2016 12:00:00 AM                 1                412            442
4 1503960366 4/16/2016 12:00:00 AM                 2                340            367

I am trying to separate the sleepDay column into two columns named "Date" and "Sleep"

I used the separate function and was able to create the two columns below:

separate(weight_log, Date, into = c("Date", "Time"), sep = ' ')
           Id      Date     Time WeightKg WeightPounds Fat   BMI IsManualReport        LogId
1  1503960366  5/2/2016 11:59:59     52.6     115.9631  22 22.65           True 1.462234e+12
2  1503960366  5/3/2016 11:59:59     52.6     115.9631  NA 22.65           True 1.462320e+12
3  1927972279 4/13/2016  1:08:52    133.5     294.3171  NA 47.54          False 1.460510e+12

I want to be able to keep the AM and PM next to the times, but with the function I used, they seem to disappear I assume because I am separating based on a space. Is there anyway to be able to specify that I am only trying to separate the column into two based on the first space?

Edit: The data set Sleep shown at the top is different then the dataset I used the separator function on which is weight_log, but the issue is the same

r
1 Answers
data.frame(SleepDay = "4/12/2016 12:00:00 AM") %>%
  separate(SleepDay, into = c("Date", "Time"), sep = " ", extra = "merge")
#       Date        Time
#1 4/12/2016 12:00:00 AM

If you are doing further analysis or visualization, I recommend converting the text into a datetime.

library(lubridate)
data.frame(SleepDay = "4/12/2016 12:05:00 AM") %>%
  mutate(SleepDay = mdy_hms(SleepDay),
         SleepDay_base = as.POSIXct(SleepDay),
         date = as_date(SleepDay),
         time_12 = format(SleepDay, "%I:%M %p"),
         time_24 = format(SleepDay, "%H:%M"))



#          SleepDay       SleepDay_base       date  time_12 time_24
#1 2016-04-12 00:05:00 2016-04-12 00:05:00 2016-04-12 12:05 AM   00:05
user_68518250
Related