How to separate data into morning and afternoon

Question

I have a column "Start time" that has both date and time in it, I would like to separate (and save into different .csv files) all the data that is before noon and after noon.

> dput(head(DQ))
structure(list(ID = 1:6, Date = c("2022-02-15", "2022-02-25", 
"2022-02-25", "2022-02-28", "2022-03-01", "2022-03-01"), Start.time = c("2-15-22 11:15:43", 
"2-25-22 8:52:33", "2-25-22 8:55:08", "2-28-22 23:19:36", "3-1-22 8:58:31", 
"3-1-22 21:04:49"), Completion.time = c("2-15-22 11:16:59", "2-25-22 8:55:02", 
"2-25-22 16:16:37", "2-28-22 23:21:52", "3-1-22 9:00:02", "3-1-22 21:06:31"
), Email = c("anonymous", "anonymous", "anonymous", "anonymous", 
"anonymous", "anonymous"), Name = c(NA, NA, NA, NA, NA, NA), 
    Total.points = c(NA, NA, NA, NA, NA, NA), Quiz.feedback = c(NA, 
    NA, NA, NA, NA, NA), Participant.Code = c("AE1_04", "AE1_02", 
    "AE1_02", "AE1_02", "AE1_02", "AE1_02"), Points...Participant.Code...Code.du.participant...Code.de.la.participante = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Participant.Code...Code.du.participant...Code.de.la.participante = c(NA, 
    NA, NA, NA, NA, NA), Stiffness...Raideur = c(10L, 3L, 2L, 
    6L, 7L, 6L), Points...Stiffness...Raideur = c(NA, NA, NA, 
    NA, NA, NA), Feedback...Stiffness...Raideur = c(NA, NA, NA, 
    NA, NA, NA), Fatigue...Fatigue = c(10L, 6L, 4L, 6L, 7L, 5L
    ), Points...Fatigue...Fatigue = c(NA, NA, NA, NA, NA, NA), 
    Feedback...Fatigue...Fatigue = c(NA, NA, NA, NA, NA, NA), 
    Discomfort...Inconfort = c(7L, 7L, 5L, 6L, 7L, 7L), Points...Discomfort...Inconfort = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Discomfort...Inconfort = c(NA, 
    NA, NA, NA, NA, NA), Happiness...Joie = c(1L, 8L, 7L, 8L, 
    7L, 8L), Points...Happiness...Joie = c(NA, NA, NA, NA, NA, 
    NA), Feedback...Happiness...Joie = c(NA, NA, NA, NA, NA, 
    NA), Productivity...Productivité = c(10L, 6L, 7L, 8L, 5L, 
    8L), Points...Productivity...Productivité = c(NA, NA, NA, 
    NA, NA, NA), Feedback...Productivity...Productivité = c(NA, 
    NA, NA, NA, NA, NA), Ability.to.concentrate...Capacité.de.se.concentrer = c(7L, 
    8L, 6L, 8L, 6L, 8L), Points...Ability.to.concentrate...Capacité.de.se.concentrer = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Ability.to.concentrate...Capacité.de.se.concentrer = c(NA, 
    NA, NA, NA, NA, NA), Alertness...Vigilance = c(7L, 5L, 4L, 
    8L, 6L, 7L), Points...Alertness...Vigilance = c(NA, NA, NA, 
    NA, NA, NA), Feedback...Alertness...Vigilance = c(NA, NA, 
    NA, NA, NA, NA), Stress...Stress = c(10L, 9L, 7L, 8L, 7L, 
    7L), Points...Stress...Stress = c(NA, NA, NA, NA, NA, NA), 
    Feedback...Stress...Stress = c(NA, NA, NA, NA, NA, NA), Back.Pain...Mal.de.dos = c(9L, 
    6L, 6L, 7L, 7L, 7L), Points...Back.Pain...Mal.de.dos = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Back.Pain...Mal.de.dos = c(NA, 
    NA, NA, NA, NA, NA), Neck.Pain...Douleur.au.cou = c(9L, 4L, 
    3L, 6L, 4L, 5L), Points...Neck.Pain...Douleur.au.cou = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Neck.Pain...Douleur.au.cou = c(NA, 
    NA, NA, NA, NA, NA), Head.Pain...Mal.de.tête = c(7L, 1L, 
    1L, 2L, 1L, 1L), Points...Head.Pain...Mal.de.tête = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Head.Pain...Mal.de.tête = c(NA, 
    NA, NA, NA, NA, NA), Eye.Pain...Douleur.oculaire = c(10L, 
    8L, 1L, 1L, 1L, 1L), Points...Eye.Pain...Douleur.oculaire = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Eye.Pain...Douleur.oculaire = c(NA, 
    NA, NA, NA, NA, NA), In.the.past.24.hours..have.you.done.any.light.physical.activity...Au.cours.des.dernières.24.heures..avez.vous.fait.une.activité.physique.légère. = c("No / Non", 
    "No / Non", "Yes / Oui", "No / Non", "Yes / Oui", "Yes / Oui"
    ), Points...In.the.past.24.hours..have.you.done.any.light.physical.activity...Au.cours.des.dernières.24.heures..avez.vous.fait.une.activité.physique.légère. = c(NA, 
    NA, NA, NA, NA, NA), Feedback...In.the.past.24.hours..have.you.done.any.light.physical.activity...Au.cours.des.dernières.24.heures..avez.vous.fait.une.activité.physique.légère. = c(NA, 
    NA, NA, NA, NA, NA), If.yes..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps. = c("", 
    "", "brisk 10 min walk", "", "stretching", "Stretching"), 
    Points...If.yes..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps. = c(NA, 
    NA, NA, NA, NA, NA), Feedback...If.yes..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps. = c(NA, 
    NA, NA, NA, NA, NA), In.the.past.24.hours..have.you.done.any.moderate.physical.activity...Au.cours.des.dernières.24.heures..avez.vous.fait.une.activité.physique.modérée. = c("No / Non", 
    "Yes / Oui", "No / Non", "Yes / Oui", "No / Non", "No / Non"
    ), Points...In.the.past.24.hours..have.you.done.any.moderate.physical.activity...Au.cours.des.dernières.24.heures..avez.vous.fait.une.activité.physique.modérée. = c(NA, 
    NA, NA, NA, NA, NA), Feedback...In.the.past.24.hours..have.you.done.any.moderate.physical.activity...Au.cours.des.dernières.24.heures..avez.vous.fait.une.activité.physique.modérée. = c(NA, 
    NA, NA, NA, NA, NA), If.yes..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps.2 = c("", 
    "30 min walk at lunch", "", "Magasiner et un petit strength training et cardio", 
    "", ""), Points...If.yes..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps.2 = c(NA, 
    NA, NA, NA, NA, NA), Feedback...If.yes..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps.2 = c(NA, 
    NA, NA, NA, NA, NA), In.the.past.24.hours..have.you.done.any.vigorous.physical.activity.....Au.cours.des.dernières.24.heures..avez.vous.pratiqué.une.activité.physique.intense. = c("No / Non", 
    "No / Non", "No / Non", "No / Non", "No / Non", "No / Non"
    ), Points...In.the.past.24.hours..have.you.done.any.vigorous.physical.activity.....Au.cours.des.dernières.24.heures..avez.vous.pratiqué.une.activité.physique.intense. = c(NA, 
    NA, NA, NA, NA, NA), Feedback...In.the.past.24.hours..have.you.done.any.vigorous.physical.activity.....Au.cours.des.dernières.24.heures..avez.vous.pratiqué.une.activité.physique.intense. = c(NA, 
    NA, NA, NA, NA, NA), If.so..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps. = c("", 
    "", "", "", "", ""), Points...If.so..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps. = c(NA, 
    NA, NA, NA, NA, NA), Feedback...If.so..what.did.you.do.and.for.how.long....Si.oui..qu.avez.vous.fait.et.pendant.combien.de.temps. = c(NA, 
    NA, NA, NA, NA, NA), Please.add.any.additional.comments.you.have...Veuillez.ajouter.tout.commentaire.supplémentaire.que.vous.avez. = c(NA, 
    NA, NA, NA, NA, NA), Points...Please.add.any.additional.comments.you.have...Veuillez.ajouter.tout.commentaire.supplémentaire.que.vous.avez. = c(NA, 
    NA, NA, NA, NA, NA), Feedback...Please.add.any.additional.comments.you.have...Veuillez.ajouter.tout.commentaire.supplémentaire.que.vous.avez. = c(NA, 
    NA, NA, NA, NA, NA)), row.names = c(NA, 6L), class = "data.frame")

I'm open to different ways of doing this, either sorting/filtering all of the morning and afternoons OR adding a column that states if it is morning and afternoon OR having it sorted and filtered in R and then having it saved to separate .csv files

please let me know if you need more details

Have you made any progress yourself? What did you attempt and where did you get stuck? — Steve, Jul 29 '22 at 12:37
@Steve I've tried something with excel with no luck, trying to use R in hopes of something I can re use later on when I have a larger data set — Richard Michaud Langis, Jul 29 '22 at 12:39
@harre should not be an issue, but no matter, the start time is the main concern — Richard Michaud Langis, Jul 29 '22 at 12:40
I'd start by trying to classify a single date-time by morning or afternoon - see [Filter R data frame by hour of the day](https://stackoverflow.com/q/57228827/5358968) - then afterwards look at outputting as two csvs. — Steve, Jul 29 '22 at 12:45
@Steve not too sure how to apply this, would you mind showing me how to do this? — Richard Michaud Langis, Jul 29 '22 at 12:49

harre · Accepted Answer · 2022-07-29T13:08:46.713

2

First split:

library(dplyr)

DQ <- 
  DQ |>
  mutate(morning = format(as.POSIXct(Start.time, format = "%m-%d-%y %H:%M:%S"), "%H") < 12) |>
  group_by(morning) |>
  group_split()

Output:

[[1]]
# A tibble: 2 × 6
     ID Date       Start.time       Completion.time  Email     morning
  <int> <chr>      <chr>            <chr>            <chr>     <lgl>  
1     4 2022-02-28 2-28-22 23:19:36 2-28-22 23:21:52 anonymous FALSE  
2     6 2022-03-01 3-1-22 21:04:49  3-1-22 21:06:31  anonymous FALSE  

[[2]]
# A tibble: 4 × 6
     ID Date       Start.time       Completion.time  Email     morning
  <int> <chr>      <chr>            <chr>            <chr>     <lgl>  
1     1 2022-02-15 2-15-22 11:15:43 2-15-22 11:16:59 anonymous TRUE   
2     2 2022-02-25 2-25-22 8:52:33  2-25-22 8:55:02  anonymous TRUE   
3     3 2022-02-25 2-25-22 8:55:08  2-25-22 16:16:37 anonymous TRUE   
4     5 2022-03-01 3-1-22 8:58:31   3-1-22 9:00:02   anonymous TRUE

Then save:

library(readr)

write_csv(DQ[[1]], "afternoon.csv")
write_csv(DQ[[2]], "morning.csv")

edited Jul 29 '22 at 13:08

answered Jul 29 '22 at 13:02

harre

7,081
2
16
28

is there a way to not have a period added between every word in my headers? its affecting some other code – Richard Michaud Langis Jul 29 '22 at 13:30
That's how your data is imported into `R`, so you'll need to check the settings there. – harre Jul 29 '22 at 13:32
is there a way to remove that? only for the headers – Richard Michaud Langis Jul 29 '22 at 13:32
1

See e.g. https://stackoverflow.com/questions/17152483/avoid-that-space-in-column-name-is-replaced-with-period-when-using-read-cs – harre Jul 29 '22 at 13:33
I have this error now ```Error: unexpected symbol in: " DQ |> mutate(morning = format(as.POSIXct(Start time"``` – Richard Michaud Langis Jul 29 '22 at 13:39
Use these: `` -- see https://stackoverflow.com/questions/34642116/how-to-use-in-r-to-select-a-column-with-space-in-its-colname – harre Jul 29 '22 at 13:58

Mohan Govindasamy · Answer 2 · 2022-07-29T12:59:14.837

1

You can use hms from the lubridate package for this and filter it or create a new column based on your need.

library(tidyverse)
library(lubridate)

df <- structure(list(ID = 1:6, 
                     Date = c("2022-02-15", "2022-02-25", 
                                  "2022-02-25", "2022-02-28", "2022-03-01", "2022-03-01"), 
                     Start.time = c("2-15-22 11:15:43", "2-25-22 8:52:33", "2-25-22 8:55:08", 
                                    "2-28-22 23:19:36", "3-1-22 8:58:31","3-1-22 21:04:49")), 
                row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

df %>% 
  mutate(time = Start.time %>% str_extract(" .*") %>% str_trim() %>% hms()) %>% 
  mutate(day = ifelse(time >= hms("00:00:00") & time < hms("12:00:00"), "fn", "an"))

  
# A tibble: 6 × 5
ID Date       Start.time       time        day  
<int> <chr>      <chr>            <Period>    <chr>
  1     1 2022-02-15 2-15-22 11:15:43 11H 15M 43S fn   
2     2 2022-02-25 2-25-22 8:52:33  8H 52M 33S  fn   
3     3 2022-02-25 2-25-22 8:55:08  8H 55M 8S   fn   
4     4 2022-02-28 2-28-22 23:19:36 23H 19M 36S an   
5     5 2022-03-01 3-1-22 8:58:31   8H 58M 31S  fn   
6     6 2022-03-01 3-1-22 21:04:49  21H 4M 49S  an

edited Jul 29 '22 at 12:59

answered Jul 29 '22 at 12:55

Mohan Govindasamy

856
3
11

so to do the afternoon count, would I simply change the mutate times from 12:00:01 to 23:59:59 ? – Richard Michaud Langis Jul 29 '22 at 12:56
I have already done the afternoon count as `an` here – Mohan Govindasamy Jul 29 '22 at 12:58
Looking at my file, it does not seem to be separated – Richard Michaud Langis Jul 29 '22 at 13:03
Do you mean your files are not separated, I haven't separated the files here. I created a column with `an` and `fn` data. You can go ahead and subset the data frame into two based on the `day` column. – Mohan Govindasamy Jul 29 '22 at 13:07
oh ok I see, let me try that – Richard Michaud Langis Jul 29 '22 at 13:07

score 1 · Answer 3 · answered Jul 29 '22 at 13:00

1

This should do the job:

dt2 <- dt %>%
  mutate(start_hour = hour(as.POSIXct(Start.time, format = "%m-%d-%y %H:%M:%S")),
         completion_hour = hour(as.POSIXct(Completion.time, format = "%m-%d-%y %H:%M:%S"))) %>%
  mutate(part_of_day = ifelse(start_hour < 12, "morning", "afternoon"))

Note that this classifies a start time of exactly noon as afternoon and exactly midnight as morning.

answered Jul 29 '22 at 13:00

broti

1,338
8
29

I'm viewing both dt2 and dt, neither exclusively have mornings or afternoons, where am I suppose to have these data frames that are separated – Richard Michaud Langis Jul 29 '22 at 13:03

How to separate data into morning and afternoon

3 Answers3