Changing week number from week of year to a sequence

[ConcernedPotatoe]

I am trying to create a column in a dataset that will indicate what week the data in a row was collected. I have come partway to a solution using the following code:

df$Week <- as.integer(format(df$Date_time, "%V"))
df<- transform(df, Week = Week - min(Week) + 1)

This produces the column I want, however, it numbers the weeks by the week of the year, starting at 49, rather than just a cumulative number of weeks starting at 1. I'm attempting to just subtract the week by the minimum value in the "week" column and adding 1, but it doesn't seem to work.

    Target_length_across_beams Target_range_extent Ping_number         Target_class Target_orientation ...45        Date_time      Week
                       -9.9e+37            -9.9e+37           0 Unclassified targets           -9.9e+37    NA 2020-12-01 18:14:54   49
                       -9.9e+37            -9.9e+37           0 Unclassified targets           -9.9e+37    NA 2020-12-01 18:14:54   49
                       -9.9e+37            -9.9e+37           0 Unclassified targets           -9.9e+37    NA 2020-12-01 18:14:54   49
                       -9.9e+37            -9.9e+37           1 Unclassified targets           -9.9e+37    NA 2020-12-01 18:14:55   49
                       -9.9e+37            -9.9e+37           1 Unclassified targets           -9.9e+37    NA 2020-12-01 18:14:55   49
                       -9.9e+37            -9.9e+37           1 Unclassified targets           -9.9e+37    NA 2020-12-01 18:14:55   49

What I want is for the "49" in the week column to be changed to "1" and for that value to increase by 1 for each consecutive week.

[StatSteph]

It works for me. I've also provided a tidyverse solution.

blob <- "Date_time
2020-12-01 18:14:54
2020-12-01 18:14:54
2020-12-01 18:14:54
2020-12-01 18:14:55
2020-12-01 18:14:55
2020-12-01 18:14:55"

library(tidyverse)

mydf <- read_csv(blob)

mydf %>%
   mutate(Week=as.integer(format(Date_time, "%V")),
          Week=Week-min(Week)+1)
#> # A tibble: 6 x 2
#>   Date_time            Week
#>   <dttm>              <dbl>
#> 1 2020-12-01 18:14:54     1
#> 2 2020-12-01 18:14:54     1
#> 3 2020-12-01 18:14:54     1
#> 4 2020-12-01 18:14:55     1
#> 5 2020-12-01 18:14:55     1
#> 6 2020-12-01 18:14:55     1

df <- as.data.frame(mydf)
df$Week <- as.integer(format(df$Date_time, "%V"))
df
#>             Date_time Week
#> 1 2020-12-01 18:14:54   49
#> 2 2020-12-01 18:14:54   49
#> 3 2020-12-01 18:14:54   49
#> 4 2020-12-01 18:14:55   49
#> 5 2020-12-01 18:14:55   49
#> 6 2020-12-01 18:14:55   49
df <-transform(df, Week=Week-min(Week)+1)
df
#>             Date_time Week
#> 1 2020-12-01 18:14:54    1
#> 2 2020-12-01 18:14:54    1
#> 3 2020-12-01 18:14:54    1
#> 4 2020-12-01 18:14:55    1
#> 5 2020-12-01 18:14:55    1
#> 6 2020-12-01 18:14:55    1

^{Created on 2021-08-11 by the reprex package (v2.0.0)}

[ConcernedPotatoe]

I've tried the tidyverse solution before but it seem to create 3 new columns filled with meaningless data.

I should mention that the "Week" column doesn't exist in the dataset until I create it using this :
df$Week <- as.integer(format(df$Date_time, "%V"))

So that could be messing with the tidyverse package code. I don't know why the "transform" line isn't working for me....

[StatSteph]

Share a reproducible example of what you are doing. Use the function reprex::reprex and share your output.

[ConcernedPotatoe]

function (x = NULL, input = NULL, wd = NULL, venue = c("gh", 
    "r", "rtf", "html", "slack", "so", 
    "ds"), render = TRUE, advertise = NULL, session_info = opt(FALSE), 
    style = opt(FALSE), comment = opt("#>"), tidyverse_quiet = opt(TRUE), 
    std_out_err = opt(FALSE), html_preview = opt(TRUE), outfile = "DEPRECATED", 
    show = "DEPRECATED", si = "DEPRECATED") 
{
    if (!missing(show)) {
        html_preview <- show
        reprex_warning("{.code show} is deprecated, please use {.code html_preview} instead")
    }
    if (!missing(si)) {
        session_info <- si
    }
    reprex_impl(x_expr = substitute(x), input = input, wd = wd, 
        venue = venue, render = render, new_session = TRUE, advertise = advertise, 
        session_info = session_info, style = style, html_preview = html_preview, 
        comment = comment, tidyverse_quiet = tidyverse_quiet, 
        std_out_err = std_out_err, outfile = outfile)
}
<bytecode: 0x0000024dd8a6ceb8>
<environment: namespace:reprex>

Here is the output.

[StatSteph]

Maybe I should have been more specific. You need to create a reproducible example. See here: FAQ: How to do a minimal reproducible example ( reprex ) for beginners

[ConcernedPotatoe]

Ping_date  Ping_time
   <date>     <time>   
 1 2020-12-01 18:14:54 
 2 2020-12-01 18:14:54 
 3 2020-12-01 18:14:54 
 4 2020-12-01 18:14:55 
 5 2020-12-01 18:14:55 
 6 2020-12-01 18:14:55 
 7 2020-12-01 18:14:56 
 8 2020-12-01 18:14:56 
 9 2020-12-01 18:14:56 
10 2020-12-01 18:14:56 

#Creates the Date_time column 
target_detections_all_15E$Date_time <- as.POSIXct(paste(as.Date(as.character(target_detections_all_15E$Ping_date),"%Y-%m-%d"), target_detections_all_15E$Ping_time, sep=" "),format = "%Y-%m-%d %H:%M:%S", tz="Asia/Bangkok")
#eliminates zeroes in the data
target_detections_all_15E<- target_detections_all_15E[target_detections_all_15E$TS_comp !=-9.9e+37,]
#Formats  the time to create the Week column and is supposed to change the week numbers to a sequence that starts at 1.
target_detections_all_15E$Week <- as.integer(format(target_detections_all_15E$Date_time, "%V"))
target_detections_all_15E<- transform(target_detections_all_15E, Week=Week-min(Week)+1)

The only 2 columns needed that are in the data frame to start is ping_date and ping_time. I hope this suffices, I'm sorry I'm very inexperienced with this forum, I usually don't have to post questions.