-1

I have a dataframe with a datetime column in POSIXct format. I am aware that I can use the week() function in lubridate to obtain the specific week of the year that each datetime is occurring. For example, 2018-01-09 is in week 2 of the year. However, what I am attempting to do is create a separate week column based on the timing of the first event. Thus, the first recorded timestamp in a given year is day one of week one, the start of the next week is seven days later, and that continues until the last recorded timestamp.

Here is an example dataframe of timestamps.

structure(list(DateTime = structure(c(1530966465, 1530476296, 
1532068672, 1530184126, 1531553917, 1533584592, 1533598716, 1534290590, 
1530660196, 1531019505, 1531929952, 1531220568, 1531464927, 1531253187, 
1533907581, 1531382467, 1531049364, 1534275102, 1533242173, 1530437391, 
1531821904, 1531511295, 1530394204, 1530622501, 1532076187, 1530628179, 
1532316349, 1531100712, 1534414604, 1533893637), class = c("POSIXct", 
"POSIXt"), tzone = "EST")), row.names = c(527906L, 577471L, 537361L, 
501302L, 438775L, 208959L, 623171L, 264669L, 483416L, 416530L, 
506487L, 565627L, 603002L, 557837L, 257743L, 452464L, 498886L, 
59601L, 52207L, 551750L, 541328L, 416508L, 564241L, 599176L, 
446559L, 564590L, 538353L, 402453L, 606952L, 12402L), class = "data.frame")
ljh2001
  • 391
  • 3
  • 17

2 Answers2

1

You could arrange the data based on DateTime, get week number of it and subtract the week value from the first value. 

library(dplyr)
library(lubridate)

df %>%
  arrange(DateTime) %>%
  mutate(week = week(DateTime), week = week - first(week) + 1)

Without arranging the data we can subtract from the min week value. 

df %>% mutate(week = week(DateTime), week = week - min(week) + 1)

Using base R, this can be done as : 

df$week <- as.integer(format(df$DateTime, "%V"))
transform(df, week = week - min(week) + 1)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
0

We could do this with data.table

library(data.table)
library(lubridate)
setDT(df)[order(DateTime), week := week(DateTime) - first(week(DateTime)) + 1]
akrun
  • 874,273
  • 37
  • 540
  • 662