How to change a date column with a for loop?

General
, ,
1

Hi all,

I am trying to transform a column from a stagnant date (just the year 2019, there are no other years present in the data) to YYYY-MM-DD date type iterating through each month of the year for each group (Town). Any ideas how to do this? I think a for loop would be the move. Something like

for i in town
mutate(Date = ....)

My data was originally like:

Town Date
Chicago 2019
Portland 2019

I created 12 of each like this:

df <-
 df %>%
  slice(rep(1:n(), each = 12))

Now my data is like:

Town Date
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Chicago 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019
Portland 2019

I want:

Town Date
Chicago 2019 -01-01
Chicago 2019 -02-01
Chicago 2019-03-01
Chicago 2019-04-01
Chicago 2019-05-01
Chicago 2019-06-01
Chicago 2019-07-01
Chicago 2019-08-01
Chicago 2019-09-01
Chicago 2019-10-01
Chicago 2019-11-01
Chicago 2019-12-01
Portland 2019-01-01
Portland 2019 -02-01
Portland 2019 -03-01
Portland 2019 -04-01
Portland 2019 -05-01
Portland 2019 -06-01
Portland 2019 -07-01
Portland 2019 -08-01
Portland 2019 -09-01
Portland 2019 -10-01
Portland 2019 -11-01
Portland 2019 -12-01

Does anyone have any ideas on how to manipulate the date data type in a for loop to do this? Other ideas welcome as well.

2 
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

DF <- data.frame(
  Town =
    c("Chicago", "Chicago", "Chicago", "Portland", "Portland", "Portland"),
  Date =
    c(2019, 2019, 2019, 2019, 2019, 2019))

DF
#>       Town Date
#> 1  Chicago 2019
#> 2  Chicago 2019
#> 3  Chicago 2019
#> 4 Portland 2019
#> 5 Portland 2019
#> 6 Portland 2019

DF$Date <- make_date(DF$Date)

DF
#>       Town       Date
#> 1  Chicago 2019-01-01
#> 2  Chicago 2019-01-01
#> 3  Chicago 2019-01-01
#> 4 Portland 2019-01-01
#> 5 Portland 2019-01-01
#> 6 Portland 2019-01-01
3

Thank you, but I am trying to also iterate through the months as well.

4

Treating your data as data.frame dat1

library(lubridate)
dd <- seq(ymd("2019-01-01"), ymd("2019-12-01"), rep = 2, by = "months" )
dat1$month <- dd

5 
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

DF <- data.frame(
  Town =
    c("Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Chicago", "Portland", "Portland", "Portland", "Portland", "Portland", "Portland", "Portland", "Portland", "Portland", "Portland", "Portland", "Portland"),
  Date =
    c(2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019))

DF$Date <- make_date(DF$Date)

towns <- length(unique(DF$Town))

mons <- rep(0:11,towns)

DF$Date = DF$Date + months(mons)

DF
#>        Town       Date
#> 1   Chicago 2019-01-01
#> 2   Chicago 2019-02-01
#> 3   Chicago 2019-03-01
#> 4   Chicago 2019-04-01
#> 5   Chicago 2019-05-01
#> 6   Chicago 2019-06-01
#> 7   Chicago 2019-07-01
#> 8   Chicago 2019-08-01
#> 9   Chicago 2019-09-01
#> 10  Chicago 2019-10-01
#> 11  Chicago 2019-11-01
#> 12  Chicago 2019-12-01
#> 13 Portland 2019-01-01
#> 14 Portland 2019-02-01
#> 15 Portland 2019-03-01
#> 16 Portland 2019-04-01
#> 17 Portland 2019-05-01
#> 18 Portland 2019-06-01
#> 19 Portland 2019-07-01
#> 20 Portland 2019-08-01
#> 21 Portland 2019-09-01
#> 22 Portland 2019-10-01
#> 23 Portland 2019-11-01
#> 24 Portland 2019-12-01
6

Stick with your original data and use it to join a date lookup table.

library(tidyverse)
DF <- data.frame(
  Town =  c("Chicago", "Portland"),
   Date= c(2019, 2019))


(lkup <- data.frame( Date=rep(2019,12),
                    mdate = seq.Date(
                      from=as.Date("2019-01-01"), as.Date("2019-12-01"), by = "months" )))


left_join(DF,lkup)
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.