CODEWITHSUNDEEP
rdata.table
riskiem
riskiem

Reputation: 307

Fetching data from a data table in R

I have two data tables: MP and MPSubSample. MP has monthly data from 1965 to 2018 and MPSubSample has a few data points from MP. I want to expand MPSubSample such that if there is data from 196801(January 1968), then I want to get data from three months before and three months after from J 1968 from MP data table and add it to MPSubSample data table. Example is as follows:

MPSubSample:

Month     ER      SENT    SENT+   TS   DS    D12     E12    Inf
196608  -7.905    -1.12   -1.22  0.26  0.52 2.870   5.493  32.650

MP:

Month     ER      SENT   SENT+   TS        DS    D12     E12     Inf
196604  2.1373  -1.66   -1.62   0.13    0.45    2.7967  5.38    32.28
196605  2.445   -1.56   -1.55   0.14    0.5     2.8133  5.42    32.35
196606  -1.443  -1.41   -1.49   0.31    0.51    2.83    5.46    32.38
196607  -1.622  -1.31   -1.39   0.22    0.52    2.85    5.4767  32.45
196608  -7.905  -1.12   -1.22   0.26    0.52    2.87    5.4933  32.65
196609  -1.066  -1.36   -1.33   -0.19   0.6     2.89    5.51    32.75
196610  3.8619  -1.31   -1.33   -0.34   0.69    2.8833  5.5233  32.85
196611  1.3946  -1.28   -1.29   -0.16   0.78    2.8767  5.5367  32.88
196612  0.1325  -1.23   -1.18   -0.12   0.79    2.87    5.55    32.92
196701  8.1534  -1.06   -1.08   -0.14   0.77    2.88    5.5167  32.9

I want the final data set to be:

Month     ER      SENT   SENT+   TS        DS    D12     E12     Inf
196605  2.445   -1.56   -1.55   0.14    0.5     2.8133  5.42    32.35
196606  -1.44   -1.41   -1.49   0.31    0.51    2.83    5.46    32.38
196607  -1.622  -1.31   -1.39   0.22    0.52    2.85    5.4767  32.45
196608  -7.905  -1.12   -1.22   0.26    0.52    2.87    5.4933  32.65
196609  -1.066  -1.36   -1.33   -0.19   0.6     2.89    5.51    32.75
196610  3.8619  -1.31   -1.33   -0.34   0.69    2.8833  5.5233  32.85
196611  1.3946  -1.28   -1.29   -0.16   0.78    2.8767  5.5367  32.88

Upvotes: 0

Views: 46

Answers (1)

r2evans
r2evans

Reputation: 160407

Try this,

library(data.table)
setDT(MP); setDT(MPSubSample)

YM_plus <- function(a, b) {
  month <- a %% 100
  newmonth <- month + b
  newyear <- (a %/% 100) + (newmonth - 1) %/% 12
  newmonth <- (newmonth - 1) %% 12 + 1
  100 * newyear + newmonth
}

MP[, c("fromdate", "todate") := .(YM_plus(Month, -3), YM_plus(Month, +3)) ]
MP[MPSubSample, on = .(fromdate <= Month, todate >= Month)][, .SD, .SDcols = names(MPSubSample)]
#     Month      ER  SENT SENT.    TS   DS    D12    E12  Inf.
# 1: 196605  2.4450 -1.56 -1.55  0.14 0.50 2.8133 5.4200 32.35
# 2: 196606 -1.4430 -1.41 -1.49  0.31 0.51 2.8300 5.4600 32.38
# 3: 196607 -1.6220 -1.31 -1.39  0.22 0.52 2.8500 5.4767 32.45
# 4: 196608 -7.9050 -1.12 -1.22  0.26 0.52 2.8700 5.4933 32.65
# 5: 196609 -1.0660 -1.36 -1.33 -0.19 0.60 2.8900 5.5100 32.75
# 6: 196610  3.8619 -1.31 -1.33 -0.34 0.69 2.8833 5.5233 32.85
# 7: 196611  1.3946 -1.28 -1.29 -0.16 0.78 2.8767 5.5367 32.88

DataL

MPSubSample <- structure(list(Month = 196608L, ER = -7.905, SENT = -1.12, SENT. = -1.22, TS = 0.26, DS = 0.52, D12 = 2.87, E12 = 5.493, Inf. = 32.65), class = "data.frame", row.names = c(NA, -1L))
MP <- structure(list(Month = c(196604L, 196605L, 196606L, 196607L, 196608L, 196609L, 196610L, 196611L, 196612L, 196701L), ER = c(2.1373, 2.445, -1.443, -1.622, -7.905, -1.066, 3.8619, 1.3946, 0.1325, 8.1534), SENT = c(-1.66, -1.56, -1.41, -1.31, -1.12, -1.36, -1.31, -1.28, -1.23, -1.06), SENT. = c(-1.62, -1.55, -1.49, -1.39, -1.22, -1.33, -1.33, -1.29, -1.18, -1.08), TS = c(0.13, 0.14, 0.31, 0.22, 0.26, -0.19, -0.34, -0.16, -0.12, -0.14), DS = c(0.45, 0.5, 0.51, 0.52, 0.52, 0.6, 0.69, 0.78, 0.79, 0.77), D12 = c(2.7967, 2.8133, 2.83, 2.85, 2.87, 2.89, 2.8833, 2.8767, 2.87, 2.88), E12 = c(5.38, 5.42, 5.46, 5.4767, 5.4933, 5.51, 5.5233, 5.5367, 5.55, 5.5167), Inf. = c(32.28, 32.35, 32.38, 32.45, 32.65, 32.75, 32.85, 32.88, 32.92, 32.9)), class = "data.frame", row.names = c(NA, -10L))

Upvotes: 1

Related Questions