0

I'm trying to figure out the best way to get a data value out of a data frame, Inflation.

Within the code below, I want to multiply a value in my data frame iData by an inflation value. This depends on the data year for that value, and the year of the coefficient I'm using, but I can't figure out the best way to get the subset code to work.

    Inflation <- structure(list(ID = 2:211, Code = structure(c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "GBP", class = "factor"), 
    CoefficientYear = c(1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 
    2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 
    2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 1998L, 1999L, 2000L, 
    2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 
    2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 
    1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 
    2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 
    2016L, 2017L, 2018L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 
    2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 
    2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 1998L, 1999L, 2000L, 
    2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 
    2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 
    1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 
    2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 
    2016L, 2017L, 2018L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 
    2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 
    2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 1998L, 1999L, 2000L, 
    2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 
    2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 
    1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 
    2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 
    2016L, 2017L, 2018L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 
    2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 
    2013L, 2014L, 2015L, 2016L, 2017L, 2018L), DataYear = c(2009L, 
    2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 
    2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 2009L, 
    2009L, 2009L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
    2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
    2010L, 2010L, 2010L, 2010L, 2010L, 2011L, 2011L, 2011L, 2011L, 
    2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 
    2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2012L, 
    2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 
    2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 
    2012L, 2012L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 
    2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 
    2013L, 2013L, 2013L, 2013L, 2013L, 2014L, 2014L, 2014L, 2014L, 
    2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 
    2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2015L, 
    2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 
    2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 
    2015L, 2015L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 
    2016L, 2016L, 2016L, 2016L, 2016L, 2017L, 2017L, 2017L, 2017L, 
    2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 
    2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2018L, 
    2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 
    2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 
    2018L, 2018L), Percent = c(19.80424276, 18.46883619, 17.68356676, 
    16.44767208, 15.19147958, 13.82855804, 12.48396228, 10.43429397, 
    8.100766173, 5.779730258, 2.166231372, 0, -3.285714286, -7.769953931, 
    -10.59166368, -13.14621037, -14.60640197, -14.65642282, -15.29803602, 
    -17.98803602, -20.69803602, 23.08995704, 21.75455048, 20.96928105, 
    19.73338637, 18.47719387, 17.11427232, 15.76967656, 13.72000825, 
    11.38648046, 9.065444544, 5.451945658, 3.285714286, 0, -4.484239645, 
    -7.305949392, -9.860496079, -11.32068769, -11.37070853, -12.01232173, 
    -14.70232173, -17.41232173, 27.57419669, 26.23879012, 25.45352069, 
    24.21762601, 22.96143351, 21.59851197, 20.25391621, 18.2042479, 
    15.8707201, 13.54968419, 9.936185303, 7.769953931, 4.484239645, 
    0, -2.821709747, -5.376256434, -6.836448043, -6.886468885, 
    -7.528082084, -10.21808208, -12.92808208, 30.39590643, 29.06049987, 
    28.27523044, 27.03933576, 25.78314326, 24.42022172, 23.07562596, 
    21.02595765, 18.69242985, 16.37139394, 12.75789505, 10.59166368, 
    7.305949392, 2.821709747, 0, -2.554546687, -4.014738296, 
    -4.064759138, -4.706372337, -7.396372337, -10.10637234, 32.95045312, 
    31.61504655, 30.82977713, 29.59388245, 28.33768995, 26.9747684, 
    25.63017264, 23.58050433, 21.24697654, 18.92594062, 15.31244174, 
    13.14621037, 9.860496079, 5.376256434, 2.554546687, 0, -1.460191609, 
    -1.510212451, -2.15182565, -4.84182565, -7.55182565, 34.41064473, 
    33.07523816, 32.28996873, 31.05407405, 29.79788156, 28.43496001, 
    27.09036425, 25.04069594, 22.70716815, 20.38613223, 16.77263335, 
    14.60640197, 11.32068769, 6.836448043, 4.014738296, 1.460191609, 
    0, -0.050020842, -0.691634041, -3.381634041, -6.091634041, 
    34.46066557, 33.12525901, 32.33998958, 31.1040949, 29.8479024, 
    28.48498085, 27.14038509, 25.09071678, 22.75718899, 20.43615307, 
    16.82265419, 14.65642282, 11.37070853, 6.886468885, 4.064759138, 
    1.510212451, 0.050020842, 0, -0.641613199, -3.331613199, 
    -6.041613199, 35.10227877, 33.7668722, 32.98160278, 31.7457081, 
    30.4895156, 29.12659405, 27.78199829, 25.73232998, 23.39880219, 
    21.07776627, 17.46426739, 15.29803602, 12.01232173, 7.528082084, 
    4.706372337, 2.15182565, 0.691634041, 0.641613199, 0, -2.69, 
    -5.4, 37.79227877, 36.4568722, 35.67160278, 34.4357081, 33.1795156, 
    31.81659405, 30.47199829, 28.42232998, 26.08880219, 23.76776627, 
    20.15426739, 17.98803602, 14.70232173, 10.21808208, 7.396372337, 
    4.84182565, 3.381634041, 3.331613199, 2.69, 0, -2.71, 40.50227877, 
    39.1668722, 38.38160278, 37.1457081, 35.8895156, 34.52659405, 
    33.18199829, 31.13232998, 28.79880219, 26.47776627, 22.86426739, 
    20.69803602, 17.41232173, 12.92808208, 10.10637234, 7.55182565, 
    6.091634041, 6.041613199, 5.4, 2.71, 0), Value = c(0.198042428, 
    0.184688362, 0.176835668, 0.164476721, 0.151914796, 0.13828558, 
    0.124839623, 0.10434294, 0.081007662, 0.057797303, 0.021662314, 
    0, -0.032857143, -0.077699539, -0.105916637, -0.131462104, 
    -0.14606402, -0.146564228, -0.15298036, -0.17988036, -0.20698036, 
    0.23089957, 0.217545505, 0.20969281, 0.197333864, 0.184771939, 
    0.171142723, 0.157696766, 0.137200083, 0.113864805, 0.090654445, 
    0.054519457, 0.032857143, 0, -0.044842396, -0.073059494, 
    -0.098604961, -0.113206877, -0.113707085, -0.120123217, -0.147023217, 
    -0.174123217, 0.275741967, 0.262387901, 0.254535207, 0.24217626, 
    0.229614335, 0.21598512, 0.202539162, 0.182042479, 0.158707201, 
    0.135496842, 0.099361853, 0.077699539, 0.044842396, 0, -0.028217097, 
    -0.053762564, -0.06836448, -0.068864689, -0.075280821, -0.102180821, 
    -0.129280821, 0.303959064, 0.290604999, 0.282752304, 0.270393358, 
    0.257831433, 0.244202217, 0.23075626, 0.210259576, 0.186924299, 
    0.163713939, 0.127578951, 0.105916637, 0.073059494, 0.028217097, 
    0, -0.025545467, -0.040147383, -0.040647591, -0.047063723, 
    -0.073963723, -0.101063723, 0.329504531, 0.316150466, 0.308297771, 
    0.295938824, 0.283376899, 0.269747684, 0.256301726, 0.235805043, 
    0.212469765, 0.189259406, 0.153124417, 0.131462104, 0.098604961, 
    0.053762564, 0.025545467, 0, -0.014601916, -0.015102125, 
    -0.021518257, -0.048418257, -0.075518257, 0.344106447, 0.330752382, 
    0.322899687, 0.310540741, 0.297978816, 0.2843496, 0.270903643, 
    0.250406959, 0.227071681, 0.203861322, 0.167726333, 0.14606402, 
    0.113206877, 0.06836448, 0.040147383, 0.014601916, 0, -0.000500208, 
    -0.00691634, -0.03381634, -0.06091634, 0.344606656, 0.33125259, 
    0.323399896, 0.311040949, 0.298479024, 0.284849809, 0.271403851, 
    0.250907168, 0.22757189, 0.204361531, 0.168226542, 0.146564228, 
    0.113707085, 0.068864689, 0.040647591, 0.015102125, 0.000500208, 
    0, -0.006416132, -0.033316132, -0.060416132, 0.351022788, 
    0.337668722, 0.329816028, 0.317457081, 0.304895156, 0.291265941, 
    0.277819983, 0.2573233, 0.233988022, 0.210777663, 0.174642674, 
    0.15298036, 0.120123217, 0.075280821, 0.047063723, 0.021518257, 
    0.00691634, 0.006416132, 0, -0.0269, -0.054, 0.377922788, 
    0.364568722, 0.356716028, 0.344357081, 0.331795156, 0.318165941, 
    0.304719983, 0.2842233, 0.260888022, 0.237677663, 0.201542674, 
    0.17988036, 0.147023217, 0.102180821, 0.073963723, 0.048418257, 
    0.03381634, 0.033316132, 0.0269, 0, -0.0271, 0.405022788, 
    0.391668722, 0.383816028, 0.371457081, 0.358895156, 0.345265941, 
    0.331819983, 0.3113233, 0.287988022, 0.264777663, 0.228642674, 
    0.20698036, 0.174123217, 0.129280821, 0.101063723, 0.075518257, 
    0.06091634, 0.060416132, 0.054, 0.0271, 0), Method = structure(c(2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "CPI"), class = "factor")), row.names = c(NA, -210L), class = "data.frame")
    Inflation



      iData <- structure(list(ID = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
    Indicator = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L), .Label = "Water consumption", class = "factor"), IndicatorID = c(10L, 
    10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L), InputA = c(0.8, 
    0.864291561, 0.756825327, 0.824653656, 0.852538526, 0.764414391, 
    0.751809875, 0.747453768, 0.844790034, 0.720459608), Input.A.Name = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "Water consumed", class = "factor"), 
    InputB = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L), .Label = " NA ", class = "factor"), Input.B.Name = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = " NA ", class = "factor"), 
    UnitCode = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L), .Label = "GBP", class = "factor"), DataYear = c(2016L, 
    2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L
    ), Country = structure(c(4L, 1L, 2L, 3L, 5L, 6L, 7L, 8L, 
    9L, 10L), .Label = c("Afghanistan", "Albania", "Algeria", 
    "ALL", "American Samoa", "Andorra", "Angola", "Antigua and Barbuda", 
    "Argentina", "Armenia"), class = "factor"), ISO = structure(c(4L, 
    1L, 3L, 10L, 8L, 5L, 2L, 9L, 6L, 7L), .Label = c("AFG", "AGO", 
    "ALB", "ALL", "AND", "ARG", "ARM", "ASM", "ATG", "DZA"), class = "factor"), 
    Division = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L), .Label = "One", class = "factor"), Further.Details.1 = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "fd1a", class = "factor"), 
    Further.details.2 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L), .Label = "fd2a", class = "factor")), row.names = c(NA, 
10L), class = "data.frame")

  iData




Coeffb <- 1.98

CoeffbYear <- 2018

CoeffbCurrency <- 'GBP'

####### get values
Step1 <- iData %>% 

  ### filter for required indicator ID

  filter(IndicatorID == 10) %>% 

  ##### Add column with step

  mutate(Step = 1) %>%

  ###### calculation

  mutate(iresult_step1 = InputA * Coeffb) %>%

  ##### Inflation TDB #######

  mutate(iresult_pos = iresult_step1 * ( 1 + subset(Inflation$Value,     Inflation$CoefficientYear==CoeffbYear & Inflation$DataYear== DataYear & Inflation$Code==CoeffbCurrency)))

Ideally, if the DataYear for my InputA value in iData is 2018, and the coefficient year is 2018, then the subset function should return 0 and the result_step1 value will be multiplied by 1.

Any help would be greatly appreciated! Hopefully what I've written makes sense!

Improve this question
1
  • Can you show the expected output. You don't need to use subset with tidyverse Commented Oct 2, 2019 at 16:13

1 Answer 1

Reset to default
1

We can change the last step to

... %>%
mutate(iresult_pos = iresult_step1 * (1 +  with(Inflation, 
       Value[CoeffYear== CoeffbYear & DataYear== DataYear & Code==CoeffbCurrency])))
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi, thanks for sending that! It looks like a step in the right direction, but I get this error message 'Warning message: In iresult_step1 * (1 + with(Inflation, Value[CoefficientYear == : longer object length is not a multiple of shorter object length' and I'm not sure what the issue is, do you have any thoughts? Thanks again!
@MeganCritchley It could be an issue when you have different lengths. WIth your dataset showed, I didn't get the error. I think a better option is merge
@MeganCritchley. But to do that, you may have to update with a reproducible example that shows the issue
Hi, okay, my actual dataset has about 5000 rows and a few more columns, so i put up a very simplified version. I haven't used merge much before and not sure what the best inputs would be, would that be in place of the with function?
@MeganCritchley. I would use a data.table join. If out is the ouput before the last step, out[Inflation, iresult_pos := iresult_step1 * (1 + Value), on = .(CoeffYear, DataYear, Code), by = .EACHI] (not tested though)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.