0

I am trying to create an "Experiment Night" column for this dataset, which would convert, say, Sept 1 - Oct 15 to Experiment Night 1-45. The issue is, I want to count a "day" as 12:00 PM one day to 12:00 PM the next, so the evening of September 1 would be the same Experiment Night as the early morning of Sept 2. I have a DateTime column in format y-m-d h:m:s as well as separate columns with Month (1-12), Day (1-31), Time (0:00-24:00) values. How would you suggest I approach this? I have not been able to find a similar problem elsewhere.

I do not know how to approach this question, so have been unable to try anything. Searches have not yielded anything similar enough to adapt any code, I have only been able to find solutions for calculating differences between dates, which does not help me. I hope I have been descriptive enough.

Cole
  • 1
  • 1
  • It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. – MrFlick Apr 06 '23 at 17:03

1 Answers1

0

If this is your data

df <- data.frame(
        Date = seq(as.POSIXct("2022-09-01 00:00"), 
                   as.POSIXct("2022-09-02 19:00"), "2 hours"), dat = 1:22)

df
                  Date dat
1  2022-09-01 00:00:00   1
2  2022-09-01 02:00:00   2
3  2022-09-01 04:00:00   3
...

Get the ids by subtracting 12 hours from the original date-time.

library(dplyr)

df %>% 
  mutate('Experiment Night' = consecutive_id(format(Date - 3600*12, "%d")))
                  Date dat Experiment Night
1  2022-09-01 00:00:00   1                1
2  2022-09-01 02:00:00   2                1
3  2022-09-01 04:00:00   3                1
4  2022-09-01 06:00:00   4                1
5  2022-09-01 08:00:00   5                1
6  2022-09-01 10:00:00   6                1
7  2022-09-01 12:00:00   7                2
8  2022-09-01 14:00:00   8                2
9  2022-09-01 16:00:00   9                2
10 2022-09-01 18:00:00  10                2
11 2022-09-01 20:00:00  11                2
12 2022-09-01 22:00:00  12                2
13 2022-09-02 00:00:00  13                2
14 2022-09-02 02:00:00  14                2
15 2022-09-02 04:00:00  15                2
16 2022-09-02 06:00:00  16                2
17 2022-09-02 08:00:00  17                2
18 2022-09-02 10:00:00  18                2
19 2022-09-02 12:00:00  19                3
20 2022-09-02 14:00:00  20                3
21 2022-09-02 16:00:00  21                3
22 2022-09-02 18:00:00  22                3
Andre Wildberg
  • 12,344
  • 3
  • 12
  • 29
  • And if the dates are non-consecutive? e.g. there is data for the 24th and 26th but not the 25th, how can I have Experiment Night skip from 1 to 3? – Cole Apr 06 '23 at 19:02
  • Please add a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) (including expected output) that fits your problem, that's the best way to avoid incremental back and forth. – Andre Wildberg Apr 06 '23 at 20:56