Substitute values (for specific dates) from a second data frame to the first data frame

Question

I have two time series data frames:

The first one:

head(df1) :

            MSCI ACWI    DJGlbl Russell 1000 Russell Dev S&P GSCI Industrial  S&P GSCI Precious
1999-03-01 -0.7000000 0.2000000   -0.1000000  -1.5000000           -1.0000000        -0.4000000
1999-03-02 -0.5035247 0.0998004   -0.7007007  -0.2030457            0.4040404        -0.3012048
1999-03-03 -0.2024291 0.2991027    0.0000000  -0.6103764            0.1006036        -0.1007049
1999-03-04  0.7099391 0.2982107    1.5120968  -0.1023541            0.5025126         0.4032258
1999-03-05  2.4169184 0.8919722    2.1847071   2.7663934           -1.2000000         0.0000000
1999-03-08  0.3933137 0.3929273    0.5830904  -0.0997009           -0.2024291         1.1044177


tail(df1) : 

             MSCI ACWI      DJGlbl Russell 1000 Russell Dev S&P GSCI Industrial  S&P GSCI Precious
2011-12-23  0.68241470  0.84790673    0.9441385   0.6116208            0.5822862        -0.2345300
2011-12-26 -0.05213764  0.00000000    0.0000000   0.0000000            0.0000000         0.0000000
2011-12-27  0.20865936  0.05254861    0.3117693   0.2431611            0.0000000        -0.7233273
2011-12-28 -0.62467465 -1.20798319   -1.1655012  -0.9702850           -2.0414381        -2.4043716
2011-12-29  0.52383447  0.47846890    0.8647799   0.5511329           -0.0933126        -1.2504666
2011-12-30  0.26055237  1.03174603   -0.4676539   1.2180268            1.9613948         1.7388017

The second one:

    head(df2) :
                 MSCI.ACWI     DJGlbl Russell.1000 Russell.Dev S.P.GSCI.Industrial S.P.GSCI.Precious
     1999-06-01  0.00000000 0.24438520    0.0000000           0         -0.88465521       0.008522842
     1999-07-01  0.12630441 0.06755621    0.0000000           0          0.29394697       0.000000000
     1999-08-02  0.07441812 0.18922829    0.0000000           0          0.02697299      -0.107155063
     1999-09-01 -0.36952701 0.08684107    0.1117509           0          0.24520976       0.000000000
     1999-10-01  0.00000000 0.00000000    0.0000000           0          0.00000000       1.941266205
     1999-11-01  0.41879925 0.00000000    0.0000000           0          0.00000000      -0.197897901


    tail(df2) :
                 MSCI.ACWI    DJGlbl  Russell.1000 Russell.Dev S.P.GSCI.Industrial S.P.GSCI.Precious
     2011-07-01  0.00000000 0.0000000    0.0000000   0.0000000          0.00000000        -0.1141162
     2011-08-01  0.00000000 0.0000000    0.0000000   0.0000000          0.02627347         0.0000000
     2011-09-01 -0.02470873 0.2977585   -0.0911891   0.6367605          0.00000000         0.2830977
     2011-10-03  0.42495188 0.0000000    0.4200743  -0.4420027         -0.41012646         0.0000000
     2011-11-01  0.00000000 0.0000000    0.0000000  -0.6597739          0.00000000         0.0000000
     2011-12-01  0.50273034 0.0000000    0.0000000   0.6476393          0.00000000         0.0000000

The first df cointains daily observations. The second df contains only the "first day of each month" forecasted values. I would like to substitute the values from the second df into the first one. In other words, the "first day of each month" values in the first df will be substituted for the "first day of each month" values from the second df. I tried to write an lapply loop that substitutes the values and was only trying to use match function. But I failed. I could not find the similar question at StackOverflow either...

Greatful for any suggestions!

P.S.

The thing is that the above df's are time series. Namely I convert the df's to time series :

library(fPortfolio)
charvec=dfnottimeseries[,1]
df1=as.timeSeries(dfnottimeseries,charvec)

and the same for the second df (df2). For example:

dput(head(df1)) :
new("timeSeries"
    , .Data = structure(c(-0.700000000000003, -0.503524672708963, -0.202429149797574, 
0.709939148073025, 2.41691842900303, 0.393313667649942, 0.200000000000003, 
0.0998003992015911, 0.299102691924224, 0.298210735586492, 0.891972249752221, 
0.392927308447943, -0.0999999999999943, -0.700700700700703, 0, 
1.51209677419355, 2.18470705064548, 0.583090379008741, -1.5, 
-0.203045685279191, -0.610376398779241, -0.102354145342895, 2.76639344262295, 
-0.0997008973080701, -1, 0.40404040404041, 0.100603621730377, 
0.50251256281407, -1.2, -0.202429149797574, -0.400000000000006, 
-0.301204819277106, -0.100704934541787, 0.403225806451604, 0, 
1.10441767068274, 0.0999999999999943, 1.59840159840161, 2.94985250737463, 
3.15186246418338, 0.55555555555555, 2.30202578268877, -0.0999999999999943, 
0.800800800800798, 0.496524329692155, 0.691699604743086, 0.196270853778203, 
0.685602350636634, -0.0999999999999943, 0, -0.100100100100109, 
-0.100200400801598, 0.100300902708119, 0.300601202404807, -0.799999999999997, 
0, -0.302419354838707, 0, 0.707785642062678, -0.200803212851394, 
-0.200000000000003, -0.200400801603209, -0.100401606425697, -0.201005025125631, 
0.201409869083588, 0.100502512562808, -0.200000000000003, -0.200400801603209, 
-0.100401606425697, -0.201005025125631, 0.201409869083588, 0.100502512562808, 
0, -0.299999999999997, -1.00300902708124, -0.303951367781152, 
0.711382113821127, 0.40363269424824, -0.00938085999999316, 0.00938174008789637, 
-0.196998120000003, -0.0751950428206875, 0.0470322689501711, 
0.047010148983195, -0.00938085999999316, 0.00938174008789637, 
-0.196998120000003, -0.0751950428206875, 0.0470322689501711, 
0.047010148983195), .Dim = c(6L, 15L), .Dimnames = list(c("1999-03-01", 
"1999-03-02", "1999-03-03", "1999-03-04", "1999-03-05", "1999-03-08"
), c("MSCI ACWI", "DJGlbl", "Russell 1000", "Russell Dev", "S&P GSCI Industrial ", 
"S&P GSCI Precious", "S&P GSCI Energy", "UBS Bloomberg", "iBoxx $ Liquid", 
"IBOXX ??? LQD", "iBoxx $ Liquid High Yield", "Markit IBOXX", 
"DBIQ Emerging", "Barclays US", "iBoxx Sov Euro IL")))
    , units = c("MSCI ACWI", "DJGlbl", "Russell 1000", "Russell Dev", "S&P GSCI Industrial ", 
"S&P GSCI Precious", "S&P GSCI Energy", "UBS Bloomberg", "iBoxx $ Liquid", 
"IBOXX ??? LQD", "iBoxx $ Liquid High Yield", "Markit IBOXX", 
"DBIQ Emerging", "Barclays US", "iBoxx Sov Euro IL")
    , positions = c(920246400, 920332800, 920419200, 920505600, 920592000, 920851200
)
    , format = "%Y-%m-%d"
    , FinCenter = "GMT"
    , recordIDs = structure(list(), .Names = character(0), row.names = integer(0), class = "data.frame")
    , title = "Time Series Object"
    , documentation = "Sun Oct 21 16:13:25 2012"
)

and

dput(head(df2))
new("timeSeries"
    , .Data = structure(c(0, 0.126304409, 0.074418121, -0.369527006, 0, 0.41879925, 
0.244385197, 0.067556211, 0.189228292, 0.086841066, 0, 0, 0, 
0, 0, 0.111750885, 0, 0, 0, 0, 0, 0, 0, 0, -0.88465521, 0.293946971, 
0.026972992, 0.245209764, 0, 0, 0.008522842, 0, -0.107155063, 
0, 1.941266205, -0.197897901, -0.013096887, 0, 0, 0.388013092, 
0.372488895, 0, -0.19041112, 0.136631233, 0, 0.183846748, 0.197612269, 
0, -0.002248848, 0.274449827, -0.143453619, -0.128428882, 0, 
0.144213551, -0.044105244, 0, -0.137138837, -0.074970085, 0, 
0, -0.128433675, 0.141883192, -0.052900591, -0.062071483, 0.062037765, 
0, -0.128433675, 0.141883192, -0.052900591, -0.062071483, 0.062037765, 
0, -0.133904169, 0.360826639, -0.040831396, -0.060762388, 0, 
0.129691732, 0.050392981, 0.020081642, 0.01978185, 0.030688482, 
0, 0.012962734, 0.050392981, 0.020081642, 0.01978185, 0.030688482, 
0, 0.012962734), .Dim = c(6L, 15L), .Dimnames = list(NULL, c("MSCI ACWI", 
"DJGlbl", "Russell 1000", "Russell Dev", "S&P GSCI Industrial ", 
"S&P GSCI Precious", "S&P GSCI Energy", "UBS Bloomberg", "iBoxx $ Liquid", 
"IBOXX ??? LQD", "iBoxx $ Liquid High Yield", "Markit IBOXX", 
"DBIQ Emerging", "Barclays US", "iBoxx Sov Euro IL")))
    , units = c("MSCI ACWI", "DJGlbl", "Russell 1000", "Russell Dev", "S&P GSCI Industrial ", 
"S&P GSCI Precious", "S&P GSCI Energy", "UBS Bloomberg", "iBoxx $ Liquid", 
"IBOXX ??? LQD", "iBoxx $ Liquid High Yield", "Markit IBOXX", 
"DBIQ Emerging", "Barclays US", "iBoxx Sov Euro IL")
    , positions = c(928195200, 930787200, 933552000, 936144000, 938736000, 941414400
)
    , format = "%Y-%m-%d"
    , FinCenter = "GMT"
    , recordIDs = structure(list(), .Names = character(0), row.names = integer(0), class = "data.frame")
    , title = "Time Series Object"
    , documentation = "Sun Oct 21 19:10:08 2012"
)

Is there any way you could provide a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example)? — flodel, Oct 21 '12 at 17:27
From the beginning the df'2 are not time series. I then convert them to time series as i edited above. I then try to replace first day of each month values from df2 into df1. Thanks! Could you please repost your answer? It might definitely work if I dont use ts.. — user1665355, Oct 21 '12 at 17:35
Could you post the result of `dput(head(df1))` and `dput(tail(df2))`? That would make your example reproducible. — David Robinson, Oct 21 '12 at 17:40

score 1 · Accepted Answer · answered Oct 21 '12 at 17:55

I think this should work with your timeSeries:

df1[rownames(df2), ] <- df2

This assumes that the df2 timestamps are a subset of those in df1. If it is not the case, use the intersection:

replace.months <- intersect(rownames(df1), rownames(df2))
df1[replace.months, ] <- df2[replace.months, ]

Substitute values (for specific dates) from a second data frame to the first data frame

1 Answers1