CODEWITHSUNDEEP
rdate
Austin Overman
Austin Overman

Reputation: 163

parsing date elements for calculations and look up

My real goal here is to use the numeric value for a month in table 1 (i.e. January = 01, ... December =12; years are present as a separate column) and find a value in table 2 where the value returned is from one month earlier. The problem I do not know how to deal with is when the month from table 1 is January (i.e. 2014-01), how would I return the value from table 2 related to December 2013 (i.e. 2013-12)?

I'm thinking that there is a package that has a process to decrement the date/month accounting for the beginning of the year condition I describe above. I do not have an issue converting the month and year columns into actual dates to accomplish this task.

year1 <- c(2013, 2013, 2014)
year2 <- c(2013, 2013, 2014)
month1 <- c(04, 08, 01)
month2 <- c(03, 12, 08)
value1 <- c(4,6,10)
value2 <- c(6,3,8)

df1 <- data.frame(year1, month1, value1)
df2 <- data.frame(year2, month2, value2)

Given the date combination of 2014-01 from df1, the expected output from df2 would be value2 = 3 from date combination 2013-12.

Thanks in advance

Upvotes: 0

Views: 66

Answers (3)

Uwe
Uwe

Reputation: 42544

I find it more convenient to work with Date objects because it's easier to add/subtract days or months (thanks to the lubridate package). So, the idea is to use the first day of a month as date field instead of separate fields for year and month.

In addition, I prefer data.table for data manipulation. 

# initial data
df1 <- data.frame(year1=c(2013, 2013, 2014), month1=c(04, 08, 01), value1=c(4,6,10))
df2 <- data.frame(year2=c(2013, 2013, 2014), month2=c(03, 12, 08), value2=c(6,3,8))

library(data.table)   # CRAN version 1.10.4 used
library(lubridate)    # CRAN version 1.6.0 used

# coerce 1st data.frame to data.table,
# create date from year and month, skip year and month columns,
# create join date which is one month earlier
DT1 <- setDT(df1)[, .(date1 = as.Date(sprintf("%4i-%02i-01", year1, month1)),
                      value1)][, join.date := date1 - months(1L),]

# coerce 2nd data.frame to data.table,
# create date from year and month, skip year and month columns,
DT2 <- setDT(df2)[, .(date2 = as.Date(sprintf("%4i-%02i-01", year2, month2)),
                      value2)]

# right join: take all rows of DT1
DT2[DT1, on = c(date2 = "join.date")]
#        date2 value2      date1 value1
#1: 2013-03-01      6 2013-04-01      4
#2: 2013-07-01     NA 2013-08-01      6
#3: 2013-12-01      3 2014-01-01     10

Upvotes: 1

jogo
jogo

Reputation: 12559

You can merge the dataframes (after some manipulation):

df1 <- data.frame(year1=c(2013, 2013, 2014), month1=c(04, 08, 01), value1=c(4,6,10))
df2 <- data.frame(year2=c(2013, 2013, 2014), month2=c(03, 12, 08), value2=c(6,3,8))

df1$month2 <- ifelse(df1$month1==1, 12, df1$month - 1)
df1$year2 <- ifelse(df1$month2==12, df1$year1-1, df1$year1)
merge(df1, df2, all.x=TRUE)
#   month2 year2 year1 month1 value1 value2
# 1      3  2013  2013      4      4      6
# 2      7  2013  2013      8      6     NA
# 3     12  2013  2014      1     10      3

Upvotes: 0

onnhoJ
onnhoJ

Reputation: 56

It's a bit of a workaround, but here is an idea that might help: instead of only subtracting 1, subtract 2, use the modulo operator and then add 1 back. 

i = 1:12
((i - 2) %% 12) + 1
[1] 12  1  2  3  4  5  6  7  8  9 10 11

Upvotes: 0

Related Questions