Ytukyg

I have monthly data like this, where my first column is date and next column is me:

I need to do simple thing:
I need to create another variable such that for entire 1994, it takes value of December 1993 of me; similarly, for 1995 it takes value of December of 1994 and so on. If it is not available,then give NA.

structure(list(date = structure(c(8673, 8702, 8734, 8765, 8796, 

8824, 8855, 8884, 8916, 8946, 8975, 9008, 9038, 9069, 9099, 9129, 

9161, 9189, 9220, 9248, 9281, 9311, 9342, 9373, 9402, 9434, 9464, 

9493, 9526, 9555, 9584, 9616, 9647, 9675, 9708, 9738, 9769, 9800, 

9829, 9861, 9892, 9920, 9951, 9981, 10011, 10042, 10073, 10102, 

10134, 10165, 10193, 10226, 10256, 10284, 10316, 10346, 10375, 

10407, 10438, 10469, 10499, 10529, 10560, 10591, 10620, 10648, 

10681, 10711, 10739, 10772, 10802, 10834, 10864, 10893, 10925, 

10956, 10987, 11016, 11047, 11075, 11108, 11138, 11169, 11200, 

11229, 11261, 11291, 11320), class = "Date"), me = c(41535, 39458.25, 

38766, 43611.75, 54687.75, 65763.75, 66456, 92069.25, 89300.25, 

82452.125, 81066.375, 76909.125, 70698.75, 79709.375, 77630, 

71391.875, 69312.5, 69312.5, 70542.8125, 52621.125, 46520.125, 

43469.625, 45757.5, 43850.9375, 40492, 32088, 38964, 35149.75, 

32857.375, 35149.75, 29074.75, 26779.375, 27544.5, 32140.5, 32905.75, 

32905.75, 34436.25, 31375.25, 32140.5, 29878.875, 39838.5, 42519.9375, 

42707.25, 40014, 43861.5, 51615.125, 46992.875, 46992.875, 53996.25, 

47053.875, 47053.875, 46706, 50180, 56356, 65641.25, 69116.375, 

65255.125, 60469.5, 62020, 41863.5, 48919.5, 55908, 57461, 57970.3125, 

59137.5, 53301.5625, 68475, 72365.625, 65751.5625, 71587.5, 85982.8125, 

73921.875, 84496.5, 82149.375, 79019.875, 89973.125, 99752.8125, 

106794.1875, 103425.5625, 123669, 143544.375, 143325, 139668.75, 

143325, 139536, 122820.75, 125001, 101933.0625)), .Names = c("date", 

"me"), class = "data.frame", row.names = c(81L, 80L, 79L, 82L, 

87L, 91L, 92L, 88L, 83L, 90L, 94L, 86L, 84L, 93L, 89L, 85L, 102L, 

101L, 95L, 105L, 96L, 106L, 99L, 100L, 104L, 98L, 97L, 103L, 

108L, 107L, 112L, 111L, 109L, 110L, 114L, 117L, 115L, 116L, 118L, 

113L, 123L, 125L, 130L, 128L, 119L, 122L, 127L, 120L, 126L, 129L, 

121L, 124L, 140L, 136L, 139L, 137L, 134L, 132L, 131L, 141L, 133L, 

135L, 138L, 142L, 146L, 153L, 154L, 150L, 148L, 144L, 149L, 152L, 

143L, 145L, 151L, 147L, 165L, 157L, 156L, 163L, 164L, 160L, 161L, 

158L, 155L, 166L, 162L, 159L))

The prior December's me value is the value found m rows back where m is 1 for Jan, 2 for Feb and so on or else if there is no row m rows back it is NA. Computing m and the sequential row numbers ix we have the following. No packages are used.

m <- as.numeric(format(DF$date, "%m"))

ix <- seq_len(nrow(DF))

transform(DF, me_dec = me[ifelse(ix - m < 1, NA, ix - m)])

giving:

          date        me   me_dec

81  1993-09-30  41535.00       NA

80  1993-10-29  39458.25       NA

79  1993-11-30  38766.00       NA

82  1993-12-31  43611.75       NA

87  1994-01-31  54687.75 43611.75

91  1994-02-28  65763.75 43611.75

92  1994-03-31  66456.00 43611.75

88  1994-04-29  92069.25 43611.75

83  1994-05-31  89300.25 43611.75

90  1994-06-30  82452.12 43611.75

94  1994-07-29  81066.38 43611.75

86  1994-08-31  76909.12 43611.75

84  1994-09-30  70698.75 43611.75

93  1994-10-31  79709.38 43611.75

89  1994-11-30  77630.00 43611.75

85  1994-12-30  71391.88 43611.75

102 1995-01-31  69312.50 71391.88

101 1995-02-28  69312.50 71391.88

95  1995-03-31  70542.81 71391.88

105 1995-04-28  52621.12 71391.88

96  1995-05-31  46520.12 71391.88

106 1995-06-30  43469.62 71391.88

99  1995-07-31  45757.50 71391.88

100 1995-08-31  43850.94 71391.88

104 1995-09-29  40492.00 71391.88

98  1995-10-31  32088.00 71391.88

97  1995-11-30  38964.00 71391.88

103 1995-12-29  35149.75 71391.88

108 1996-01-31  32857.38 35149.75

107 1996-02-29  35149.75 35149.75

112 1996-03-29  29074.75 35149.75

111 1996-04-30  26779.38 35149.75

109 1996-05-31  27544.50 35149.75

110 1996-06-28  32140.50 35149.75

114 1996-07-31  32905.75 35149.75

117 1996-08-30  32905.75 35149.75

115 1996-09-30  34436.25 35149.75

116 1996-10-31  31375.25 35149.75

118 1996-11-29  32140.50 35149.75

113 1996-12-31  29878.88 35149.75

123 1997-01-31  39838.50 29878.88

125 1997-02-28  42519.94 29878.88

130 1997-03-31  42707.25 29878.88

128 1997-04-30  40014.00 29878.88

119 1997-05-30  43861.50 29878.88

122 1997-06-30  51615.12 29878.88

127 1997-07-31  46992.88 29878.88

120 1997-08-29  46992.88 29878.88

126 1997-09-30  53996.25 29878.88

129 1997-10-31  47053.88 29878.88

121 1997-11-28  47053.88 29878.88

124 1997-12-31  46706.00 29878.88

140 1998-01-30  50180.00 46706.00

136 1998-02-27  56356.00 46706.00

139 1998-03-31  65641.25 46706.00

137 1998-04-30  69116.38 46706.00

134 1998-05-29  65255.12 46706.00

132 1998-06-30  60469.50 46706.00

131 1998-07-31  62020.00 46706.00

141 1998-08-31  41863.50 46706.00

133 1998-09-30  48919.50 46706.00

135 1998-10-30  55908.00 46706.00

138 1998-11-30  57461.00 46706.00

142 1998-12-31  57970.31 46706.00

146 1999-01-29  59137.50 57970.31

153 1999-02-26  53301.56 57970.31

154 1999-03-31  68475.00 57970.31

150 1999-04-30  72365.62 57970.31

148 1999-05-28  65751.56 57970.31

144 1999-06-30  71587.50 57970.31

149 1999-07-30  85982.81 57970.31

152 1999-08-31  73921.88 57970.31

143 1999-09-30  84496.50 57970.31

145 1999-10-29  82149.38 57970.31

151 1999-11-30  79019.88 57970.31

147 1999-12-31  89973.12 57970.31

165 2000-01-31  99752.81 89973.12

157 2000-02-29 106794.19 89973.12

156 2000-03-31 103425.56 89973.12

163 2000-04-28 123669.00 89973.12

164 2000-05-31 143544.38 89973.12

160 2000-06-30 143325.00 89973.12

161 2000-07-31 139668.75 89973.12

158 2000-08-31 143325.00 89973.12

155 2000-09-29 139536.00 89973.12

166 2000-10-31 122820.75 89973.12

162 2000-11-30 125001.00 89973.12

159 2000-12-29 101933.06 89973.12

Here's a possible solution:

library(zoo)

library(lubridate)

We first create some simple useful variables:

d <- d %>% 

  mutate(date = ymd(date),

         month = month(date),

         year = year(date)) %>% 

  groupby(year) %>% # for each year we fill just the december value in new_var

  mutate(new_var = ifelse(month==12, me, NA)) %>% ungroup()

Now we can fill new_var using na.locf from zoo. Moving forward each NA will be filled with the last non empty value (the december value).

d <- d %>% 

  mutate(new_var = na.locf(new_var, na.rm=F)) 



head(d, 10)

# # A tibble: 10 x 5

# date           me month  year new_var

# <date>      <dbl> <dbl> <dbl>   <dbl>

# 1 1993-09-30 41535      9  1993     NA 

# 2 1993-10-29 39458.    10  1993     NA 

# 3 1993-11-30 38766     11  1993     NA 

# 4 1993-12-31 43612.    12  1993  43612.

# 5 1994-01-31 54688.     1  1994  43612.

# 6 1994-02-28 65764.     2  1994  43612.

# 7 1994-03-31 66456      3  1994  43612.

# 8 1994-04-29 92069.     4  1994  43612.

# 9 1994-05-31 89300.     5  1994  43612.

# 10 1994-06-30 82452.     6  1994  43612.

You just need to define how those NA would be filled, because there's no previous year.

here's another solution using only dplyr with df as your data.

you create 2 data.frames :

• one with date, me, year and month = '12'


• one with new_var = me, year = {year + 1}, month

You then merge the 2 data.frames (I use (data.table::)merge but you can use dplyr::left_join, both are fine)

and then remove year, month.

df %>% 

  {merge(x = transmute(., date, me, year = as.numeric(substr(date, 1, 4)), month = '12'),

         y = transmute(., new_var = me, year = as.numeric(substr(date, 1, 4)) + 1, month = substr(date, 6, 7)), 

         by = c('year', 'month'), 

         all.x = TRUE)} %>%

  select(-year, -month)

library(lubridate)

new$date <- ymd(new$date)



new$flag <- ifelse(month(new$date) == month(12),1,"NA")

This is a simple solution using Base R ad lubridate