Dear R-help,
I am working on what it seems to be a simple problem, but after several
hours trying to come up with a solution, unfortunately I have not been able
to.
I would like to go from "datain" to "dataout", that is,
create the NEWREF
variable according with some restrictions, and update the values for the
remaining variables in the original data set (which is way more bigger than
this example). The problem can be described as having products (coded as
REF) in stock. Here, the total nomber of units in stock are named TOENDREF
and those required for the customer are given by TIMEREF. The idea is to
use as many units of the previous REF as possible before using a new REF.
## input
datain <- structure(list(REF = c("999", "999",
"999", "1099", "731", "731",
"731", "731", "1442", "1442",
"1442", "1442"), TIMEREF = c(120,
240, 360, 30, 30, 60, 90, 120, 30, 60, 90, 120), TOENDREF = c(390,
270, 150, 480, 480, 450, 420, 390, 480, 450, 420, 390)), .Names =
c("REF",
"TIMEREF", "TOENDREF"), row.names = c(NA, 12L), class =
"data.frame")
datain
## output
dataout <- structure(list(REF = c(999L, 999L, 999L, 1099L, 731L, 731L,
731L,
731L, 1442L, 1442L, 1442L, 1442L), TIMEREF = c(120L, 240L, 360L,
30L, 30L, 60L, 90L, 120L, 30L, 60L, 90L, 120L), TOENDREF = c(390L,
270L, 150L, 120L, 90L, 30L, 420L, 300L, 270L, 210L, 120L, 0L),
NEWREF = c(999L, 999L, 999L, 999L, 999L, 999L, 731L, 731L,
731L, 731L, 731L, 731L)), .Names = c("REF", "TIMEREF",
"TOENDREF",
"NEWREF"), row.names = c(NA, 12L), class = "data.frame")
dataout
I what follows I will try to explain what I want to accomplish:
* Example 1
Take rows 3 and 4 of "datain"
#REF TIMEREF TOENDREF
#3 999 360 150
#4 1099 30 480
As 150 units of REF 999 are available, we could substitute the 30 units of
REF 1099 with them. Hence, the 4th row of the _updated_ "datain"
becomes
#REF TIMEREF TOENDREF NEWREF
#3 999 360 150 999
#4 1099 30 120 999
* Example 2
Now, let's take rows 3 to 8 of the _updated_ "datain":
#REF TIMEREF TOENDREF
#3 999 360 150
#4 999 30 120
#5 731 30 480
#6 731 60 450
#7 731 90 420
#8 731 120 390
In row 4, there 120 units available to be used. The number of units
required of REF 731 is 30, which can be easily covered by the remaining 120
units of REF 999. By doing so, the remaining units of REF 999 would then be
90. Hence, the newly _updated_ "datain" becomes
#REF TIMEREF TOENDREF
#3 999 360 150
#4 999 30 120
#5 999 30 90
#6 999 60 30
#7 731 90 420
#8 731 120 300
Finally, the updated "datain" file after processing the remaining REF
would
be
#REF TIMEREF TOENDREF
#9 731 30 270
#10 731 60 210
#11 731 90 120
#12 731 120 0
Hopefully I have explained well what I would like to end up with. If this
is not the case, I will be more than happy to provide more information.
Any help would be very much appreciated. Thanks in advance.
Best regards,
Jorge Velez.-
[[alternative HTML version deleted]]
david.kaethner at gmail.com
2015-Oct-22 15:00 UTC
[R] Update dataframe based on some conditions
Hi, what I find a little confusing about your example: If there are several positions with a REF of, say, 999, why do you not just add them up to a single position? Because if you have the same position in several rows, than REF is not a unique identifier for that position. You would need the row number as well. Without a unique ID per position, I don?t think you can solve your problem. Further: If I fill up a position from a previous row, won?t I just do the same thing again for the next row, thereby carrying items just down the list? Also, it would help if you used as for the example more useful variable names (such as ?product_ID? or ?number_in_stock?). And it would help if you try to make your example as simple as possible, meaning that you use the smallest amount of data possible without changing the problem.> Am 21.10.2015 um 22:07 schrieb Jorge I Velez <jorgeivanvelez at gmail.com>: > > Dear R-help, > > I am working on what it seems to be a simple problem, but after several > hours trying to come up with a solution, unfortunately I have not been able > to. > > I would like to go from "datain" to "dataout", that is, create the NEWREF > variable according with some restrictions, and update the values for the > remaining variables in the original data set (which is way more bigger than > this example). The problem can be described as having products (coded as > REF) in stock. Here, the total nomber of units in stock are named TOENDREF > and those required for the customer are given by TIMEREF. The idea is to > use as many units of the previous REF as possible before using a new REF. > > ## input > datain <- structure(list(REF = c("999", "999", "999", "1099", "731", "731", > "731", "731", "1442", "1442", "1442", "1442"), TIMEREF = c(120, > 240, 360, 30, 30, 60, 90, 120, 30, 60, 90, 120), TOENDREF = c(390, > 270, 150, 480, 480, 450, 420, 390, 480, 450, 420, 390)), .Names = c("REF", > "TIMEREF", "TOENDREF"), row.names = c(NA, 12L), class = "data.frame") > datain > > ## output > dataout <- structure(list(REF = c(999L, 999L, 999L, 1099L, 731L, 731L, > 731L, > 731L, 1442L, 1442L, 1442L, 1442L), TIMEREF = c(120L, 240L, 360L, > 30L, 30L, 60L, 90L, 120L, 30L, 60L, 90L, 120L), TOENDREF = c(390L, > 270L, 150L, 120L, 90L, 30L, 420L, 300L, 270L, 210L, 120L, 0L), > NEWREF = c(999L, 999L, 999L, 999L, 999L, 999L, 731L, 731L, > 731L, 731L, 731L, 731L)), .Names = c("REF", "TIMEREF", "TOENDREF", > "NEWREF"), row.names = c(NA, 12L), class = "data.frame") > dataout > > > I what follows I will try to explain what I want to accomplish: > > * Example 1 > Take rows 3 and 4 of "datain" > > #REF TIMEREF TOENDREF > #3 999 360 150 > #4 1099 30 480 > > As 150 units of REF 999 are available, we could substitute the 30 units of > REF 1099 with them. Hence, the 4th row of the _updated_ "datain" becomes > > #REF TIMEREF TOENDREF NEWREF > #3 999 360 150 999 > #4 1099 30 120 999 > > * Example 2 > Now, let's take rows 3 to 8 of the _updated_ "datain": > > #REF TIMEREF TOENDREF > #3 999 360 150 > #4 999 30 120 > #5 731 30 480 > #6 731 60 450 > #7 731 90 420 > #8 731 120 390 > > In row 4, there 120 units available to be used. The number of units > required of REF 731 is 30, which can be easily covered by the remaining 120 > units of REF 999. By doing so, the remaining units of REF 999 would then be > 90. Hence, the newly _updated_ "datain" becomes > > #REF TIMEREF TOENDREF > #3 999 360 150 > #4 999 30 120 > #5 999 30 90 > #6 999 60 30 > #7 731 90 420 > #8 731 120 300 > > Finally, the updated "datain" file after processing the remaining REF would > be > > #REF TIMEREF TOENDREF > #9 731 30 270 > #10 731 60 210 > #11 731 90 120 > #12 731 120 0 > > Hopefully I have explained well what I would like to end up with. If this > is not the case, I will be more than happy to provide more information. > > Any help would be very much appreciated. Thanks in advance. > > Best regards, > Jorge Velez.- > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.