Sum previous instances that match the same ID [duplicate]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
This question already has an answer here:
cumsum by group
2 answers
I have this example dataset:
df <- data.frame(ID = c(1, 1, 1, 2, 2, 2), A = c("2018-10-12",
"2018-10-12", "2018-10-13", "2018-10-14", "2018-10-15", "2018-10-16"),
B = c(1, 5, 7, 2, 54, 202))
ID A B
1 1 2018-10-12 1
2 1 2018-10-12 5
3 1 2018-10-13 7
4 2 2018-10-14 2
5 2 2018-10-15 54
6 2 2018-10-16 202
What I'm trying to do is create a column C that is the sum of B but only for dates before each respective row. For instance, the output I'm seeking is:
ID A B C
1 1 2018-10-12 1 1
2 1 2018-10-12 5 6
3 1 2018-10-13 7 13
4 2 2018-10-14 2 2
5 2 2018-10-15 54 56
6 2 2018-10-16 202 258
I generally will use subsets to do individual sumifs when I have those questions, but I'm not sure how to do this in a new column.
My end goal is to determine the dates that each ID (if applicable) crosses 50.
Thanks!
r
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
marked as duplicate by Henrik r
Users with the r badge can single-handedly close r questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 19:27
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
cumsum by group
2 answers
I have this example dataset:
df <- data.frame(ID = c(1, 1, 1, 2, 2, 2), A = c("2018-10-12",
"2018-10-12", "2018-10-13", "2018-10-14", "2018-10-15", "2018-10-16"),
B = c(1, 5, 7, 2, 54, 202))
ID A B
1 1 2018-10-12 1
2 1 2018-10-12 5
3 1 2018-10-13 7
4 2 2018-10-14 2
5 2 2018-10-15 54
6 2 2018-10-16 202
What I'm trying to do is create a column C that is the sum of B but only for dates before each respective row. For instance, the output I'm seeking is:
ID A B C
1 1 2018-10-12 1 1
2 1 2018-10-12 5 6
3 1 2018-10-13 7 13
4 2 2018-10-14 2 2
5 2 2018-10-15 54 56
6 2 2018-10-16 202 258
I generally will use subsets to do individual sumifs when I have those questions, but I'm not sure how to do this in a new column.
My end goal is to determine the dates that each ID (if applicable) crosses 50.
Thanks!
r
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
marked as duplicate by Henrik r
Users with the r badge can single-handedly close r questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 19:27
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
cumsum by group
2 answers
I have this example dataset:
df <- data.frame(ID = c(1, 1, 1, 2, 2, 2), A = c("2018-10-12",
"2018-10-12", "2018-10-13", "2018-10-14", "2018-10-15", "2018-10-16"),
B = c(1, 5, 7, 2, 54, 202))
ID A B
1 1 2018-10-12 1
2 1 2018-10-12 5
3 1 2018-10-13 7
4 2 2018-10-14 2
5 2 2018-10-15 54
6 2 2018-10-16 202
What I'm trying to do is create a column C that is the sum of B but only for dates before each respective row. For instance, the output I'm seeking is:
ID A B C
1 1 2018-10-12 1 1
2 1 2018-10-12 5 6
3 1 2018-10-13 7 13
4 2 2018-10-14 2 2
5 2 2018-10-15 54 56
6 2 2018-10-16 202 258
I generally will use subsets to do individual sumifs when I have those questions, but I'm not sure how to do this in a new column.
My end goal is to determine the dates that each ID (if applicable) crosses 50.
Thanks!
r
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
This question already has an answer here:
cumsum by group
2 answers
I have this example dataset:
df <- data.frame(ID = c(1, 1, 1, 2, 2, 2), A = c("2018-10-12",
"2018-10-12", "2018-10-13", "2018-10-14", "2018-10-15", "2018-10-16"),
B = c(1, 5, 7, 2, 54, 202))
ID A B
1 1 2018-10-12 1
2 1 2018-10-12 5
3 1 2018-10-13 7
4 2 2018-10-14 2
5 2 2018-10-15 54
6 2 2018-10-16 202
What I'm trying to do is create a column C that is the sum of B but only for dates before each respective row. For instance, the output I'm seeking is:
ID A B C
1 1 2018-10-12 1 1
2 1 2018-10-12 5 6
3 1 2018-10-13 7 13
4 2 2018-10-14 2 2
5 2 2018-10-15 54 56
6 2 2018-10-16 202 258
I generally will use subsets to do individual sumifs when I have those questions, but I'm not sure how to do this in a new column.
My end goal is to determine the dates that each ID (if applicable) crosses 50.
Thanks!
This question already has an answer here:
cumsum by group
2 answers
r
r
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
asked Nov 16 '18 at 19:14
deslaurdeslaur
133
133
marked as duplicate by Henrik r
Users with the r badge can single-handedly close r questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 19:27
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Henrik r
Users with the r badge can single-handedly close r questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 16 '18 at 19:27
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
We can do a group by cumulative sum to create the 'C' column
library(dplyr)
df %>%
group_by(ID) %>%
mutate(C = cumsum(B))
Or use data.table
library(data.table)
setDT(df)[, C := cumsum(B), by = ID]
or with base R
df$C <- with(df, ave(B, ID, FUN = cumsum))
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
Thanks! This seems to work. I used the base R method, however it seems that I need to have the data sorted by investment date for it to work correctly, right?
– deslaur
Nov 16 '18 at 19:55
@deslaur You can order byinvestmentdatebefore and then apply thecumsumon theordered data
– akrun
Nov 16 '18 at 19:56
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
We can do a group by cumulative sum to create the 'C' column
library(dplyr)
df %>%
group_by(ID) %>%
mutate(C = cumsum(B))
Or use data.table
library(data.table)
setDT(df)[, C := cumsum(B), by = ID]
or with base R
df$C <- with(df, ave(B, ID, FUN = cumsum))
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
Thanks! This seems to work. I used the base R method, however it seems that I need to have the data sorted by investment date for it to work correctly, right?
– deslaur
Nov 16 '18 at 19:55
@deslaur You can order byinvestmentdatebefore and then apply thecumsumon theordered data
– akrun
Nov 16 '18 at 19:56
add a comment |
We can do a group by cumulative sum to create the 'C' column
library(dplyr)
df %>%
group_by(ID) %>%
mutate(C = cumsum(B))
Or use data.table
library(data.table)
setDT(df)[, C := cumsum(B), by = ID]
or with base R
df$C <- with(df, ave(B, ID, FUN = cumsum))
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
Thanks! This seems to work. I used the base R method, however it seems that I need to have the data sorted by investment date for it to work correctly, right?
– deslaur
Nov 16 '18 at 19:55
@deslaur You can order byinvestmentdatebefore and then apply thecumsumon theordered data
– akrun
Nov 16 '18 at 19:56
add a comment |
We can do a group by cumulative sum to create the 'C' column
library(dplyr)
df %>%
group_by(ID) %>%
mutate(C = cumsum(B))
Or use data.table
library(data.table)
setDT(df)[, C := cumsum(B), by = ID]
or with base R
df$C <- with(df, ave(B, ID, FUN = cumsum))
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
We can do a group by cumulative sum to create the 'C' column
library(dplyr)
df %>%
group_by(ID) %>%
mutate(C = cumsum(B))
Or use data.table
library(data.table)
setDT(df)[, C := cumsum(B), by = ID]
or with base R
df$C <- with(df, ave(B, ID, FUN = cumsum))
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
answered Nov 16 '18 at 19:15
akrunakrun
422k13209285
422k13209285
Thanks! This seems to work. I used the base R method, however it seems that I need to have the data sorted by investment date for it to work correctly, right?
– deslaur
Nov 16 '18 at 19:55
@deslaur You can order byinvestmentdatebefore and then apply thecumsumon theordered data
– akrun
Nov 16 '18 at 19:56
add a comment |
Thanks! This seems to work. I used the base R method, however it seems that I need to have the data sorted by investment date for it to work correctly, right?
– deslaur
Nov 16 '18 at 19:55
@deslaur You can order byinvestmentdatebefore and then apply thecumsumon theordered data
– akrun
Nov 16 '18 at 19:56
Thanks! This seems to work. I used the base R method, however it seems that I need to have the data sorted by investment date for it to work correctly, right?
– deslaur
Nov 16 '18 at 19:55
Thanks! This seems to work. I used the base R method, however it seems that I need to have the data sorted by investment date for it to work correctly, right?
– deslaur
Nov 16 '18 at 19:55
@deslaur You can order by
investmentdate before and then apply the cumsum on the ordered data– akrun
Nov 16 '18 at 19:56
@deslaur You can order by
investmentdate before and then apply the cumsum on the ordered data– akrun
Nov 16 '18 at 19:56
add a comment |
