Conditional Cumulative Sum over the same row in R
I have a dataset like this
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
I'm trying to create a fourth column as shown below
dat1 <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4),
Col4 = c(1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4))
What I have tried till now,
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1 == "B"), lag(Col2)- Col3*lag(Col2), 0)))
d1
The Answer I'm getting is not what is in Col4, which is desired.
The condition for getting Col4 is :
if Col1 is B then get the value of Col2 as it is,
if Col1 is S & Previous Value of Col1 is B then 1-(0.2*1) which is equal to 0.8
if Col1 is S & Previous Value of Col1 is S as well then (1+0.8) -((1+0.8)*0.3) which is 1.26
Basically, it's like first performing difference and then performing cumulative sum including the difference and so on.
For now, I have taken a simple example to understand what I'm trying to achieve, the actual data-set has more than 1 million Obs. and Several Thousand Groups and what's worse is that the Combination of 'B' & 'S' alter. Like in some groups it's B,B,S,S and So on...
Any Help on this will be appreciated as I have tried several things other than if_else() and seen many conditional cumulative sum Ques as well but to no avail.
I think the same could be done easily in Excel with SUMIF() Function, but i need to do this with R
r if-statement excel-formula dataset
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
add a comment |
I have a dataset like this
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
I'm trying to create a fourth column as shown below
dat1 <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4),
Col4 = c(1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4))
What I have tried till now,
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1 == "B"), lag(Col2)- Col3*lag(Col2), 0)))
d1
The Answer I'm getting is not what is in Col4, which is desired.
The condition for getting Col4 is :
if Col1 is B then get the value of Col2 as it is,
if Col1 is S & Previous Value of Col1 is B then 1-(0.2*1) which is equal to 0.8
if Col1 is S & Previous Value of Col1 is S as well then (1+0.8) -((1+0.8)*0.3) which is 1.26
Basically, it's like first performing difference and then performing cumulative sum including the difference and so on.
For now, I have taken a simple example to understand what I'm trying to achieve, the actual data-set has more than 1 million Obs. and Several Thousand Groups and what's worse is that the Combination of 'B' & 'S' alter. Like in some groups it's B,B,S,S and So on...
Any Help on this will be appreciated as I have tried several things other than if_else() and seen many conditional cumulative sum Ques as well but to no avail.
I think the same could be done easily in Excel with SUMIF() Function, but i need to do this with R
r if-statement excel-formula dataset
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
Although stackoverflow.com/questions/14689424/… uses adata.table, maybe that helps as well
– CIAndrews
Nov 27 '18 at 10:21
1
Well, @CIAndrews it's not that simple. Other than this I have seen Ques form stackoverflow.com/questions/16741683/… & stackoverflow.com/questions/49356656/… & stackoverflow.com/questions/42707796/… but don't help solve the problem. This is done easily with excel. I tried but don't know how to apply the same in R
– Prashant Dey
Nov 27 '18 at 10:25
add a comment |
I have a dataset like this
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
I'm trying to create a fourth column as shown below
dat1 <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4),
Col4 = c(1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4))
What I have tried till now,
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1 == "B"), lag(Col2)- Col3*lag(Col2), 0)))
d1
The Answer I'm getting is not what is in Col4, which is desired.
The condition for getting Col4 is :
if Col1 is B then get the value of Col2 as it is,
if Col1 is S & Previous Value of Col1 is B then 1-(0.2*1) which is equal to 0.8
if Col1 is S & Previous Value of Col1 is S as well then (1+0.8) -((1+0.8)*0.3) which is 1.26
Basically, it's like first performing difference and then performing cumulative sum including the difference and so on.
For now, I have taken a simple example to understand what I'm trying to achieve, the actual data-set has more than 1 million Obs. and Several Thousand Groups and what's worse is that the Combination of 'B' & 'S' alter. Like in some groups it's B,B,S,S and So on...
Any Help on this will be appreciated as I have tried several things other than if_else() and seen many conditional cumulative sum Ques as well but to no avail.
I think the same could be done easily in Excel with SUMIF() Function, but i need to do this with R
r if-statement excel-formula dataset
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
I have a dataset like this
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
I'm trying to create a fourth column as shown below
dat1 <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4),
Col4 = c(1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4, 1, 0.8, 1.26, 4))
What I have tried till now,
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1 == "B"), lag(Col2)- Col3*lag(Col2), 0)))
d1
The Answer I'm getting is not what is in Col4, which is desired.
The condition for getting Col4 is :
if Col1 is B then get the value of Col2 as it is,
if Col1 is S & Previous Value of Col1 is B then 1-(0.2*1) which is equal to 0.8
if Col1 is S & Previous Value of Col1 is S as well then (1+0.8) -((1+0.8)*0.3) which is 1.26
Basically, it's like first performing difference and then performing cumulative sum including the difference and so on.
For now, I have taken a simple example to understand what I'm trying to achieve, the actual data-set has more than 1 million Obs. and Several Thousand Groups and what's worse is that the Combination of 'B' & 'S' alter. Like in some groups it's B,B,S,S and So on...
Any Help on this will be appreciated as I have tried several things other than if_else() and seen many conditional cumulative sum Ques as well but to no avail.
I think the same could be done easily in Excel with SUMIF() Function, but i need to do this with R
r if-statement excel-formula dataset
r if-statement excel-formula dataset
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
edited Nov 27 '18 at 10:21
Prashant Dey
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
asked Nov 27 '18 at 10:03
Prashant DeyPrashant Dey
1415
1415
Although stackoverflow.com/questions/14689424/… uses adata.table, maybe that helps as well
– CIAndrews
Nov 27 '18 at 10:21
1
Well, @CIAndrews it's not that simple. Other than this I have seen Ques form stackoverflow.com/questions/16741683/… & stackoverflow.com/questions/49356656/… & stackoverflow.com/questions/42707796/… but don't help solve the problem. This is done easily with excel. I tried but don't know how to apply the same in R
– Prashant Dey
Nov 27 '18 at 10:25
add a comment |
Although stackoverflow.com/questions/14689424/… uses adata.table, maybe that helps as well
– CIAndrews
Nov 27 '18 at 10:21
1
Well, @CIAndrews it's not that simple. Other than this I have seen Ques form stackoverflow.com/questions/16741683/… & stackoverflow.com/questions/49356656/… & stackoverflow.com/questions/42707796/… but don't help solve the problem. This is done easily with excel. I tried but don't know how to apply the same in R
– Prashant Dey
Nov 27 '18 at 10:25
Although stackoverflow.com/questions/14689424/… uses a
data.table , maybe that helps as well– CIAndrews
Nov 27 '18 at 10:21
Although stackoverflow.com/questions/14689424/… uses a
data.table , maybe that helps as well– CIAndrews
Nov 27 '18 at 10:21
1
1
Well, @CIAndrews it's not that simple. Other than this I have seen Ques form stackoverflow.com/questions/16741683/… & stackoverflow.com/questions/49356656/… & stackoverflow.com/questions/42707796/… but don't help solve the problem. This is done easily with excel. I tried but don't know how to apply the same in R
– Prashant Dey
Nov 27 '18 at 10:25
Well, @CIAndrews it's not that simple. Other than this I have seen Ques form stackoverflow.com/questions/16741683/… & stackoverflow.com/questions/49356656/… & stackoverflow.com/questions/42707796/… but don't help solve the problem. This is done easily with excel. I tried but don't know how to apply the same in R
– Prashant Dey
Nov 27 '18 at 10:25
add a comment |
1 Answer
1
active
oldest
votes
It feels like you didn't complete the if_else:
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1) == "B", 1-(0.2*1),
if_else(Col1 == 'S' & lag(Col1) == 'S',1.26,0))))
d1
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
Thanks, @CIAndrews, this triggered the idea.
– Prashant Dey
Dec 6 '18 at 9:59
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53497137%2fconditional-cumulative-sum-over-the-same-row-in-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It feels like you didn't complete the if_else:
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1) == "B", 1-(0.2*1),
if_else(Col1 == 'S' & lag(Col1) == 'S',1.26,0))))
d1
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
Thanks, @CIAndrews, this triggered the idea.
– Prashant Dey
Dec 6 '18 at 9:59
add a comment |
It feels like you didn't complete the if_else:
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1) == "B", 1-(0.2*1),
if_else(Col1 == 'S' & lag(Col1) == 'S',1.26,0))))
d1
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
Thanks, @CIAndrews, this triggered the idea.
– Prashant Dey
Dec 6 '18 at 9:59
add a comment |
It feels like you didn't complete the if_else:
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1) == "B", 1-(0.2*1),
if_else(Col1 == 'S' & lag(Col1) == 'S',1.26,0))))
d1
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
It feels like you didn't complete the if_else:
dat <- data.frame(Col0 =rep(c("grp1","grp2","grp3", "grp4"), each = 4),
Col1 = rep(c("B","S","S","B"), 4),
Col2 = rep(c(1,2,3,4), 4),
Col3 = rep(c(0.1,0.2,0.3,0.4), 4))
d1 <- dat %>%
group_by(Col0) %>%
mutate(Col4 = if_else(Col1 == 'B', Col2,
if_else(Col1 == 'S' & lag(Col1) == "B", 1-(0.2*1),
if_else(Col1 == 'S' & lag(Col1) == 'S',1.26,0))))
d1
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
answered Nov 27 '18 at 10:33
CIAndrewsCIAndrews
28817
28817
Thanks, @CIAndrews, this triggered the idea.
– Prashant Dey
Dec 6 '18 at 9:59
add a comment |
Thanks, @CIAndrews, this triggered the idea.
– Prashant Dey
Dec 6 '18 at 9:59
Thanks, @CIAndrews, this triggered the idea.
– Prashant Dey
Dec 6 '18 at 9:59
Thanks, @CIAndrews, this triggered the idea.
– Prashant Dey
Dec 6 '18 at 9:59
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53497137%2fconditional-cumulative-sum-over-the-same-row-in-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Although stackoverflow.com/questions/14689424/… uses a
data.table, maybe that helps as well– CIAndrews
Nov 27 '18 at 10:21
1
Well, @CIAndrews it's not that simple. Other than this I have seen Ques form stackoverflow.com/questions/16741683/… & stackoverflow.com/questions/49356656/… & stackoverflow.com/questions/42707796/… but don't help solve the problem. This is done easily with excel. I tried but don't know how to apply the same in R
– Prashant Dey
Nov 27 '18 at 10:25