CODEWITHSUNDEEP
rmultiple-columnslubridate
luis vergara
luis vergara

Reputation: 141

How to add weeks to a date column with a column that is just a number

What I am trying to do is calculate the end Date of a huge data-frame based on a number of weeks that will be required for the job.

desired outcome:

such is PO.Due.Date = 2019-01-01 Weeks duration = 4 (New Column) End Date = 2019-01-29

 $ Quote Number    : chr  "Q000297" "Q000300" "Q000401" "Q000405" ...
 $ Confidence.Level: num  0.1 0.1 0.1 0.1 0.1 0.6 0.2 0.2 0.6 0.1 ...
 $ PO.Due.Date     : Date, format: "2019-01-03" "2019-01-03" "2019-01-03" ...
 $ Duration.Weeks  : num  2 2 4 4 2 1 4 4 4 4 ...

mydf

   Quote Number Confidence.Level PO.Due.Date Duration.Weeks
1       Q000297             0.10  2019-01-03              2
2       Q000300             0.10  2019-01-03              2
3       Q000401             0.10  2019-01-03              4
4       Q000405             0.10  2019-01-03              4
5       Q000464             0.10  2019-01-03              2
6       Q000465             0.60  0028-02-20              1
7     Q000479/1             0.20  2019-03-01              4
8       Q000480             0.20  2019-03-01              4
9       Q000481             0.60  2019-02-28              4
10      Q000494             0.10  2019-01-03              4

I though I could get it into weeks and year and then add them. However that will mess up the end product that we are looking for that is day detailed.

mydf$week <- format(mydf$PO.Due.Date, format="%Y-%U")

or even with a function or something like but I couldn't get it 

mydf %>%
    mutate(PO.End.Date = colSums(PO.Due.Date + weeks(mydf$Duration.Weeks)))

Upvotes: 0

Views: 1255

Answers (2)

Roman
Roman

Reputation: 4989

One-liner in base R:

d$EndDate <- d$StartDate + 7 * d$DurationWeeks

> d
    StartDate DurationWeeks    EndDate
1  2019-01-03             2 2019-01-17
2  2019-01-03             2 2019-01-17
3  2019-01-03             4 2019-01-31
4  2019-01-03             4 2019-01-31
5  2019-01-03             2 2019-01-17
6  2019-02-20             1 2019-02-27
7  2019-03-01             4 2019-03-29
8  2019-03-01             4 2019-03-29
9  2019-02-28             4 2019-03-28
10 2019-01-03             4 2019-01-31

  • Your StartDate needs to be formatted as a proper date,
    e.g., as.Date(d$StartDate, "%Y-%m-%d").

  • The data should be clean. 0028-02-20 (line 6) is not a proper date.

Data

d <- structure(list(StartDate = structure(c(17899, 17899, 17899, 17899,  17899, 17947, 17956, 17956, 17955, 17899), class = "Date"), DurationWeeks = c(2L,  2L, 4L, 4L, 2L, 1L, 4L, 4L, 4L, 4L)), class = "data.frame", row.names = c(NA, -10L))

Upvotes: 1

TJ83
TJ83

Reputation: 71

It seems your example has a typo? 4 weeks following 1 Jan 2019 is not 1 Feb 2019...?

If you add n to a date-object you get a new date-object n days later. So I would suggest:

mydf$PO.End.Date<-mydf$PO.Due.Date+mydf$Duration.Weeks*7

Upvotes: 1

Related Questions