CODEWITHSUNDEEP
rdataframedplyrdata.tabletidyverse
Jordan Wrong
Jordan Wrong

Reputation: 1245

Insert a Sequence of Dates For Every Date In a dataframe

I have a data frame that has two columns, Ticker and Date. For every Date observation, I would like to create a sequence of dates that goes back 3 days from the original date to the original date (like seq(OriginalDate, OriginalDate-3, by=1))

For example:

df = data.frame(Ticker = c("AAPL", "MSFT"), Date = c("2019-01-05", "2019-02-10"))

print(df)

Ticker Date
AAPL   2019-01-05
MSFT   2019-02-10

I would like the new data frame to look like this:

print(df)

Ticker Date        Date_Sequence
AAPL   2019-01-05  2019-01-05 #original Date
AAPL   2019-01-05  2019-01-04 #original Date -1
AAPL   2019-01-05  2019-01-03 #original Date -2
MSFT   2019-02-10  2019-02-10 
MSFT   2019-02-10  2019-02-09
MSFT   2019-02-10  2019-02-08

Upvotes: 2

Views: 624

Answers (3)

M--
M--

Reputation: 28850

Using data.table:

library(data.table)

setDT(df)[ , .(Date_Sequence = as.Date(Date) - 0:2), .(Ticker, Date)]

#    Ticker       Date Date_Sequence
# 1:   AAPL 2019-01-05    2019-01-05
# 2:   AAPL 2019-01-05    2019-01-04
# 3:   AAPL 2019-01-05    2019-01-03
# 4:   MSFT 2019-02-10    2019-02-10
# 5:   MSFT 2019-02-10    2019-02-09
# 6:   MSFT 2019-02-10    2019-02-08

Upvotes: 5

IceCreamToucan
IceCreamToucan

Reputation: 28685

df$Date <- as.Date(df$Date)
df <- df[rep(seq(nrow(df)), each = 3),]
df$Date_Sequence <- df$Date - 0:2

df
#     Ticker       Date Date_Sequence
# 1     AAPL 2019-01-05    2019-01-05
# 1.1   AAPL 2019-01-05    2019-01-04
# 1.2   AAPL 2019-01-05    2019-01-03
# 2     MSFT 2019-02-10    2019-02-10
# 2.1   MSFT 2019-02-10    2019-02-09
# 2.2   MSFT 2019-02-10    2019-02-08

Upvotes: 2

akrun
akrun

Reputation: 887128

One option is to first convert the 'Date' to Date class, uncount to expand 'n' times each row, grouped by 'Ticker', create a sequence of reverse dates from the first of 'Date'

library(dplyr)
library(tidyr)
library(purrr)
df %>% 
   mutate(Date = as.Date(Date)) %>%
   uncount(3) %>% 
   group_by(Ticker) %>% 
   mutate(Date_Sequence = seq(first(Date), length = n(), by = '-1 day'))

Or another option is to create a list column with mutate and unnest

df %>%
  mutate(Date = as.Date(Date),
         Date_Sequence = map(Date, seq, length = 3, by = '-1 day')) %>%
  unnest_legacy(Date_Sequence)
#  Ticker       Date Date_Sequence
#1   AAPL 2019-01-05    2019-01-05
#2   AAPL 2019-01-05    2019-01-04
#3   AAPL 2019-01-05    2019-01-03
#4   MSFT 2019-02-10    2019-02-10
#5   MSFT 2019-02-10    2019-02-09
#6   MSFT 2019-02-10    2019-02-08

Upvotes: 4

Related Questions