-1

For the following data set (a small portion of a large data set), I want to create a variable called "DATE", which will use the information from the variables "fyr" and "fyearq". Actually, the variable "fyr" indicates the month - e.g., 5 means May and 10 means October. The variable "fyearq" indicates the year corresponding to that month. Now the new variable "DATE" will be like (for the first observation) 1968-5-31, meaning that in addition to using the data from the two variables "fyr" and "fyearq", it will add the LAST DAY of the corresponding month. Basically, I want to create the variable because eventually using the "DATE" variable and "rdq" variable, I will create another variable called "DIFF", which is actually the number of days between these two dates variables (variable "DATE" and variable "rdq"). I know the package lubridate is excellent for dealing with the date problems, but am not sure how to use it. 

  fyr  fyearq  tic        rdq
1    5   1968  AIR       <NA>
2    5   1969  AIR       <NA>
3    5   1970  AIR       <NA>
4    5   1971  AIR       <NA>
5    5   1972  AIR 1973-07-23
6    5   1973  AIR 1974-07-06
7    5   1974  AIR 1975-07-18
8    5   1975  AIR 1976-07-15
9    5   1976  AIR 1977-07-20
10   5   1977  AIR 1978-06-29
11   5   1978  AIR 1979-07-16
12   5   1979  AIR 1980-07-14
13   5   1980  AIR 1981-07-20
14   5   1981  AIR 1982-07-22
15   5   1982  AIR 1983-07-28
16   5   1983  AIR 1984-07-26
17   5   1984  AIR 1985-07-24
18   5   1985  AIR 1986-07-08
19   5   1986  AIR 1987-07-14
20   5   1987  AIR 1988-07-20
21   5   1988  AIR 1989-07-18
22   5   1989  AIR 1990-06-20
23   5   1990  AIR 1991-06-20
24   5   1991  AIR 1992-06-19
25   5   1992  AIR 1993-07-14
26   5   1993  AIR 1994-07-06
27   5   1994  AIR 1995-07-06
28   5   1995  AIR 1996-07-01
29   5   1996  AIR 1997-06-25
30   5   1997  AIR 1998-06-25
31   5   1998  AIR 1999-06-24
32   5   1999  AIR 2000-06-28
33   5   2000  AIR 2001-06-28
34   5   2001  AIR 2002-08-26
35   5   2002  AIR 2003-07-03
36   5   2003  AIR 2004-06-29
37   5   2004  AIR 2005-07-13
38   5   2005  AIR 2006-07-12
39   5   2006  AIR 2007-07-11
40   5   2007  AIR 2008-07-09
41   5   2008  AIR 2009-07-14
42   5   2009  AIR 2010-07-13
43   5   2010  AIR 2011-07-06
44   5   2011  AIR 2012-07-17
45   5   2012  AIR 2013-07-25
46   5   2013  AIR 2014-07-15
47   5   2016  AIR 2017-07-11
48   5   2014  AIR 2015-07-13
49   5   2015  AIR 2016-07-12
50   5   2017  AIR 2018-07-10
51  10   1982 ABSI       <NA>
52  10   1983 ABSI       <NA>
53  10   1984 ABSI 1984-12-20
54  10   1985 ABSI       <NA>
55  10   1986 ABSI 1986-11-18
56  10   1987 ABSI       <NA>
57  10   1988 ABSI 1988-11-22
58  10   1989 ABSI 1989-11-22
59  10   1990 ABSI 1990-11-29
60  10   1991 ABSI 1991-11-21
61  10   1992 ABSI 1992-11-30
62  10   1993 ABSI 1993-11-26
63  10   1994 ABSI 1994-11-29
Sharif
  • 163
  • 1
  • 9

2 Answers2

1

Base R should be sufficient in this case. We can increment fyr and then wrap it in as.Date to get first date of next month and subtract - 1 to get last date of current month. 

as.Date(paste(df$fyearq, df$fyr + 1, "01"), "%Y %m %d") - 1
#[1] "1968-05-31" "1969-05-31" "1970-05-31" "1971-05-31" "1972-05-31"....

If you have don't want to specify format explicitly you can use lubridate with same logic

lubridate::ymd(paste(df$fyearq, df$fyr  + 1, "01")) - 1

Logic of answer inspired from @thelatemail's comment.

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
0

We can use base R methods with sprintf

as.Date(do.call(sprintf, c(fmt = "%d-%d-01",
            df[c('fyearq', 'fyr')] + list(0, 1)))) -1
#[1] "1968-05-31" "1969-05-31" "1970-05-31" "1971-05-31" "1972-05-31"  ...

data

df <- structure(list(fyr = c(5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L), fyearq = c(1968L, 1969L, 
1970L, 1971L, 1972L, 1973L, 1974L, 1975L, 1976L, 1977L, 1978L, 
1979L, 1980L, 1981L, 1982L, 1983L, 1984L, 1985L, 1986L, 1987L, 
1988L, 1989L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 
1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2016L, 
2014L, 2015L, 2017L, 1982L, 1983L, 1984L, 1985L, 1986L, 1987L, 
1988L, 1989L, 1990L, 1991L, 1992L, 1993L, 1994L), tic = c("AIR", 
"AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", 
"AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", 
"AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", 
"AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", 
"AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", "AIR", 
"AIR", "AIR", "AIR", "AIR", "ABSI", "ABSI", "ABSI", "ABSI", "ABSI", 
"ABSI", "ABSI", "ABSI", "ABSI", "ABSI", "ABSI", "ABSI", "ABSI"
), rdq = c(NA, NA, NA, NA, "1973-07-23", "1974-07-06", "1975-07-18", 
"1976-07-15", "1977-07-20", "1978-06-29", "1979-07-16", "1980-07-14", 
"1981-07-20", "1982-07-22", "1983-07-28", "1984-07-26", "1985-07-24", 
"1986-07-08", "1987-07-14", "1988-07-20", "1989-07-18", "1990-06-20", 
"1991-06-20", "1992-06-19", "1993-07-14", "1994-07-06", "1995-07-06", 
"1996-07-01", "1997-06-25", "1998-06-25", "1999-06-24", "2000-06-28", 
"2001-06-28", "2002-08-26", "2003-07-03", "2004-06-29", "2005-07-13", 
"2006-07-12", "2007-07-11", "2008-07-09", "2009-07-14", "2010-07-13", 
"2011-07-06", "2012-07-17", "2013-07-25", "2014-07-15", "2017-07-11", 
"2015-07-13", "2016-07-12", "2018-07-10", NA, NA, "1984-12-20", 
"<NA>", "1986-11-18", NA, "1988-11-22", "1989-11-22", "1990-11-29", 
"1991-11-21", "1992-11-30", "1993-11-26", "1994-11-29")), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", 
"14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", 
"25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", 
"36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", 
"47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", 
"58", "59", "60", "61", "62", "63"))
Community
  • 1
  • 1
akrun
  • 874,273
  • 37
  • 540
  • 662