Mixing
R: how to get last sold out date for each buying?
-1
let say i'm a fruit vendor, and i have 2 table. 1 for buying and 1 for sales like bellow,
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,
"Apple", 20180101, 15,
"Apple", 20180105, 20,
"Banana", 20180102, 18,
"Banana", 20180109, 14
)
fruitSold <- tribble(
~Date, ~name, ~sold,
20180101, 'Apple', 5,
20180102, 'Apple', 3,
20180102, 'Banana', 3,
20180103, 'Apple', 1,
20180103, 'Banana', 4,
20180104, 'Apple', 2,
20180104, 'Banana', 2,
20180105, 'Apple', 1,
20180105, 'Banana', 2,
20180106, 'Apple', 2,
20180106, 'Banana', 3,
20180107, 'Apple', 2,
20180107, 'Banana', 1,
20180108, 'Apple', 0,
20180108, 'Banana', 3,
20180109, 'Apple', 2,
20180109, 'Banana', 1,
20180110, 'Apple', 3,
20180110, 'Banana', 1
)
I want to get last sold out date for each buying. like this.
name | Date | Qty | LastSoldOut
"Apple" | 20180101 | 15 | 20180107
"Apple" | 20180105 | 20 | NA
"Banana" | 20180102 | 18 | 20180109
"Banana" | 20180109 | 14 | NA
Is there anyone can help?
r dplyr data.table
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
add a comment |
-1
let say i'm a fruit vendor, and i have 2 table. 1 for buying and 1 for sales like bellow,
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,
"Apple", 20180101, 15,
"Apple", 20180105, 20,
"Banana", 20180102, 18,
"Banana", 20180109, 14
)
fruitSold <- tribble(
~Date, ~name, ~sold,
20180101, 'Apple', 5,
20180102, 'Apple', 3,
20180102, 'Banana', 3,
20180103, 'Apple', 1,
20180103, 'Banana', 4,
20180104, 'Apple', 2,
20180104, 'Banana', 2,
20180105, 'Apple', 1,
20180105, 'Banana', 2,
20180106, 'Apple', 2,
20180106, 'Banana', 3,
20180107, 'Apple', 2,
20180107, 'Banana', 1,
20180108, 'Apple', 0,
20180108, 'Banana', 3,
20180109, 'Apple', 2,
20180109, 'Banana', 1,
20180110, 'Apple', 3,
20180110, 'Banana', 1
)
I want to get last sold out date for each buying. like this.
name | Date | Qty | LastSoldOut
"Apple" | 20180101 | 15 | 20180107
"Apple" | 20180105 | 20 | NA
"Banana" | 20180102 | 18 | 20180109
"Banana" | 20180109 | 14 | NA
Is there anyone can help?
r dplyr data.table
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
1
What is the logic of selecting theLastSoldOutdate? For "Apple" you also have dates which are greater than "20180107"
– Ronak Shah
Nov 21 '18 at 5:00
you need to provide some unique identifier for each buying item , otherwise for system everything will be same
– Hunaidkhan
Nov 21 '18 at 5:06
@RonakShah, ah, i mean, on January 1st I bought 15 Apples, I want to know when that 15 apples sold out.
– Rizal Chandra
Nov 28 '18 at 11:14
add a comment |
-1
-1
-1
let say i'm a fruit vendor, and i have 2 table. 1 for buying and 1 for sales like bellow,
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,
"Apple", 20180101, 15,
"Apple", 20180105, 20,
"Banana", 20180102, 18,
"Banana", 20180109, 14
)
fruitSold <- tribble(
~Date, ~name, ~sold,
20180101, 'Apple', 5,
20180102, 'Apple', 3,
20180102, 'Banana', 3,
20180103, 'Apple', 1,
20180103, 'Banana', 4,
20180104, 'Apple', 2,
20180104, 'Banana', 2,
20180105, 'Apple', 1,
20180105, 'Banana', 2,
20180106, 'Apple', 2,
20180106, 'Banana', 3,
20180107, 'Apple', 2,
20180107, 'Banana', 1,
20180108, 'Apple', 0,
20180108, 'Banana', 3,
20180109, 'Apple', 2,
20180109, 'Banana', 1,
20180110, 'Apple', 3,
20180110, 'Banana', 1
)
I want to get last sold out date for each buying. like this.
name | Date | Qty | LastSoldOut
"Apple" | 20180101 | 15 | 20180107
"Apple" | 20180105 | 20 | NA
"Banana" | 20180102 | 18 | 20180109
"Banana" | 20180109 | 14 | NA
Is there anyone can help?
r dplyr data.table
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
let say i'm a fruit vendor, and i have 2 table. 1 for buying and 1 for sales like bellow,
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,
"Apple", 20180101, 15,
"Apple", 20180105, 20,
"Banana", 20180102, 18,
"Banana", 20180109, 14
)
fruitSold <- tribble(
~Date, ~name, ~sold,
20180101, 'Apple', 5,
20180102, 'Apple', 3,
20180102, 'Banana', 3,
20180103, 'Apple', 1,
20180103, 'Banana', 4,
20180104, 'Apple', 2,
20180104, 'Banana', 2,
20180105, 'Apple', 1,
20180105, 'Banana', 2,
20180106, 'Apple', 2,
20180106, 'Banana', 3,
20180107, 'Apple', 2,
20180107, 'Banana', 1,
20180108, 'Apple', 0,
20180108, 'Banana', 3,
20180109, 'Apple', 2,
20180109, 'Banana', 1,
20180110, 'Apple', 3,
20180110, 'Banana', 1
)
I want to get last sold out date for each buying. like this.
name | Date | Qty | LastSoldOut
"Apple" | 20180101 | 15 | 20180107
"Apple" | 20180105 | 20 | NA
"Banana" | 20180102 | 18 | 20180109
"Banana" | 20180109 | 14 | NA
Is there anyone can help?
r dplyr data.table
r dplyr data.table
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
asked Nov 21 '18 at 4:56
Rizal ChandraRizal Chandra
43
43
1
What is the logic of selecting theLastSoldOutdate? For "Apple" you also have dates which are greater than "20180107"
– Ronak Shah
Nov 21 '18 at 5:00
you need to provide some unique identifier for each buying item , otherwise for system everything will be same
– Hunaidkhan
Nov 21 '18 at 5:06
@RonakShah, ah, i mean, on January 1st I bought 15 Apples, I want to know when that 15 apples sold out.
– Rizal Chandra
Nov 28 '18 at 11:14
add a comment |
1
What is the logic of selecting theLastSoldOutdate? For "Apple" you also have dates which are greater than "20180107"
– Ronak Shah
Nov 21 '18 at 5:00
you need to provide some unique identifier for each buying item , otherwise for system everything will be same
– Hunaidkhan
Nov 21 '18 at 5:06
@RonakShah, ah, i mean, on January 1st I bought 15 Apples, I want to know when that 15 apples sold out.
– Rizal Chandra
Nov 28 '18 at 11:14
1
1
What is the logic of selecting the
LastSoldOut date? For "Apple" you also have dates which are greater than "20180107"– Ronak Shah
Nov 21 '18 at 5:00
What is the logic of selecting the
LastSoldOut date? For "Apple" you also have dates which are greater than "20180107"– Ronak Shah
Nov 21 '18 at 5:00
you need to provide some unique identifier for each buying item , otherwise for system everything will be same
– Hunaidkhan
Nov 21 '18 at 5:06
you need to provide some unique identifier for each buying item , otherwise for system everything will be same
– Hunaidkhan
Nov 21 '18 at 5:06
@RonakShah, ah, i mean, on January 1st I bought 15 Apples, I want to know when that 15 apples sold out.
– Rizal Chandra
Nov 28 '18 at 11:14
@RonakShah, ah, i mean, on January 1st I bought 15 Apples, I want to know when that 15 apples sold out.
– Rizal Chandra
Nov 28 '18 at 11:14
add a comment |
2 Answers
2
active
oldest
votes
0
1) Here is a possible approach using data.table non-equi join:
#calculate the available stock at each date
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
#calculate the fruits sold up to date
cumsold <- fruitSold[, .(Date, SoldToDate=cumsum(sold)), .(name)]
#use non-equi join to find the first date where
#sold to date is greater than available stock as per OP
cumsold[
FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail),
#for each row in FruitBought, find that first date
.(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][,
-(1L:3L)] #remove the joining columns
2) You can also use the data.table's roll argument by slightly tweaking the number sold to date:
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
cumsoldTweak <- fruitSold[, .(Date, SoldToDate=cumsum(sold)-1e-2), .(name)]
cumsoldTweak[FruitBought, on=c("name", SoldToDate="CumAvail"), roll=-Inf,
.(name, Date=i.Date, Qty, LastSoldOut=Date)]
output:
Name Date Qty LastSoldOut
1: Apple 20180101 15 20180107
2: Apple 20180105 20 NA
3: Banana 20180102 18 20180109
4: Banana 20180109 14 NA
data:
library(data.table)
#data.table 1.11.4 Latest news: http://r-datatable.com
FruitBought <- fread("name,Date,Qty
Apple,20180101,15
Apple,20180105,20
Banana,20180102,18
Banana,20180109,14")
#order is important before doing cumsum
setorder(FruitBought, name, Date)
fruitSold <- fread("Date,name,sold
20180101,Apple,5
20180102,Apple,3
20180102,Banana,3
20180103,Apple,1
20180103,Banana,4
20180104,Apple,2
20180104,Banana,2
20180105,Apple,1
20180105,Banana,2
20180106,Apple,2
20180106,Banana,3
20180107,Apple,2
20180107,Banana,1
20180108,Apple,0
20180108,Banana,3
20180109,Apple,2
20180109,Banana,1
20180110,Apple,3
20180110,Banana,1")
#order is important before doing cumsum
setorder(fruitSold, Date, name)
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
It works. But i having some difficulties to understand what happen. since i'm not familiar with data.table package. :D oncumsold[ FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail), #for each row in FruitBought, find that first date .(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][, -(1L:3L)] #remove the joining columnsis that the syntax for joining.. I'll try to understand your code first. by the way your code is works, and i'd like to say thanks. Regard,
– Rizal Chandra
Nov 28 '18 at 11:19
add a comment |
0
You can do something like this to get the desired output
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,~id,
"Apple", 20180101, 15,1,
"Apple", 20180105, 20,2,
"Banana", 20180102, 18,1,
"Banana", 20180109, 14,2,
)
fruitSold <- tribble(
~Date, ~name, ~sold,~id,
20180101, 'Apple', 5,1,
20180102, 'Apple', 3,1,
20180102, 'Banana', 3,1,
20180103, 'Apple', 1,1,
20180103, 'Banana', 4,1,
20180104, 'Apple', 2,1,
20180104, 'Banana', 2,1,
20180105, 'Apple', 1,1,
20180105, 'Banana', 2,1,
20180106, 'Apple', 2,1,
20180106, 'Banana', 3,1,
20180107, 'Apple', 2,1,
20180107, 'Banana', 1,1,
20180108, 'Apple', 0,2,
20180108, 'Banana', 3,1,
20180109, 'Apple', 2,2,
20180109, 'Banana', 1,1,
20180110, 'Apple', 3,2,
20180110, 'Banana', 1,1
)
fruitSold$Date <- lubridate::ymd(fruitSold$Date)
FruitBought$Date <- lubridate::ymd(FruitBought$Date )
colnames(fruitSold)
sold <- as.data.frame(fruitSold %>% group_by(name,id) %>% summarise(last_date = max(Date)))
colnames(sold)
colnames(FruitBought)
final <- left_join(FruitBought,sold, by = c("name","id"))
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
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
});
}
});
draft saved
draft discarded
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%2f53405496%2fr-how-to-get-last-sold-out-date-for-each-buying%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
1) Here is a possible approach using data.table non-equi join:
#calculate the available stock at each date
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
#calculate the fruits sold up to date
cumsold <- fruitSold[, .(Date, SoldToDate=cumsum(sold)), .(name)]
#use non-equi join to find the first date where
#sold to date is greater than available stock as per OP
cumsold[
FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail),
#for each row in FruitBought, find that first date
.(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][,
-(1L:3L)] #remove the joining columns
2) You can also use the data.table's roll argument by slightly tweaking the number sold to date:
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
cumsoldTweak <- fruitSold[, .(Date, SoldToDate=cumsum(sold)-1e-2), .(name)]
cumsoldTweak[FruitBought, on=c("name", SoldToDate="CumAvail"), roll=-Inf,
.(name, Date=i.Date, Qty, LastSoldOut=Date)]
output:
Name Date Qty LastSoldOut
1: Apple 20180101 15 20180107
2: Apple 20180105 20 NA
3: Banana 20180102 18 20180109
4: Banana 20180109 14 NA
data:
library(data.table)
#data.table 1.11.4 Latest news: http://r-datatable.com
FruitBought <- fread("name,Date,Qty
Apple,20180101,15
Apple,20180105,20
Banana,20180102,18
Banana,20180109,14")
#order is important before doing cumsum
setorder(FruitBought, name, Date)
fruitSold <- fread("Date,name,sold
20180101,Apple,5
20180102,Apple,3
20180102,Banana,3
20180103,Apple,1
20180103,Banana,4
20180104,Apple,2
20180104,Banana,2
20180105,Apple,1
20180105,Banana,2
20180106,Apple,2
20180106,Banana,3
20180107,Apple,2
20180107,Banana,1
20180108,Apple,0
20180108,Banana,3
20180109,Apple,2
20180109,Banana,1
20180110,Apple,3
20180110,Banana,1")
#order is important before doing cumsum
setorder(fruitSold, Date, name)
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
It works. But i having some difficulties to understand what happen. since i'm not familiar with data.table package. :D oncumsold[ FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail), #for each row in FruitBought, find that first date .(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][, -(1L:3L)] #remove the joining columnsis that the syntax for joining.. I'll try to understand your code first. by the way your code is works, and i'd like to say thanks. Regard,
– Rizal Chandra
Nov 28 '18 at 11:19
add a comment |
0
1) Here is a possible approach using data.table non-equi join:
#calculate the available stock at each date
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
#calculate the fruits sold up to date
cumsold <- fruitSold[, .(Date, SoldToDate=cumsum(sold)), .(name)]
#use non-equi join to find the first date where
#sold to date is greater than available stock as per OP
cumsold[
FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail),
#for each row in FruitBought, find that first date
.(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][,
-(1L:3L)] #remove the joining columns
2) You can also use the data.table's roll argument by slightly tweaking the number sold to date:
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
cumsoldTweak <- fruitSold[, .(Date, SoldToDate=cumsum(sold)-1e-2), .(name)]
cumsoldTweak[FruitBought, on=c("name", SoldToDate="CumAvail"), roll=-Inf,
.(name, Date=i.Date, Qty, LastSoldOut=Date)]
output:
Name Date Qty LastSoldOut
1: Apple 20180101 15 20180107
2: Apple 20180105 20 NA
3: Banana 20180102 18 20180109
4: Banana 20180109 14 NA
data:
library(data.table)
#data.table 1.11.4 Latest news: http://r-datatable.com
FruitBought <- fread("name,Date,Qty
Apple,20180101,15
Apple,20180105,20
Banana,20180102,18
Banana,20180109,14")
#order is important before doing cumsum
setorder(FruitBought, name, Date)
fruitSold <- fread("Date,name,sold
20180101,Apple,5
20180102,Apple,3
20180102,Banana,3
20180103,Apple,1
20180103,Banana,4
20180104,Apple,2
20180104,Banana,2
20180105,Apple,1
20180105,Banana,2
20180106,Apple,2
20180106,Banana,3
20180107,Apple,2
20180107,Banana,1
20180108,Apple,0
20180108,Banana,3
20180109,Apple,2
20180109,Banana,1
20180110,Apple,3
20180110,Banana,1")
#order is important before doing cumsum
setorder(fruitSold, Date, name)
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
It works. But i having some difficulties to understand what happen. since i'm not familiar with data.table package. :D oncumsold[ FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail), #for each row in FruitBought, find that first date .(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][, -(1L:3L)] #remove the joining columnsis that the syntax for joining.. I'll try to understand your code first. by the way your code is works, and i'd like to say thanks. Regard,
– Rizal Chandra
Nov 28 '18 at 11:19
add a comment |
0
0
0
1) Here is a possible approach using data.table non-equi join:
#calculate the available stock at each date
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
#calculate the fruits sold up to date
cumsold <- fruitSold[, .(Date, SoldToDate=cumsum(sold)), .(name)]
#use non-equi join to find the first date where
#sold to date is greater than available stock as per OP
cumsold[
FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail),
#for each row in FruitBought, find that first date
.(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][,
-(1L:3L)] #remove the joining columns
2) You can also use the data.table's roll argument by slightly tweaking the number sold to date:
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
cumsoldTweak <- fruitSold[, .(Date, SoldToDate=cumsum(sold)-1e-2), .(name)]
cumsoldTweak[FruitBought, on=c("name", SoldToDate="CumAvail"), roll=-Inf,
.(name, Date=i.Date, Qty, LastSoldOut=Date)]
output:
Name Date Qty LastSoldOut
1: Apple 20180101 15 20180107
2: Apple 20180105 20 NA
3: Banana 20180102 18 20180109
4: Banana 20180109 14 NA
data:
library(data.table)
#data.table 1.11.4 Latest news: http://r-datatable.com
FruitBought <- fread("name,Date,Qty
Apple,20180101,15
Apple,20180105,20
Banana,20180102,18
Banana,20180109,14")
#order is important before doing cumsum
setorder(FruitBought, name, Date)
fruitSold <- fread("Date,name,sold
20180101,Apple,5
20180102,Apple,3
20180102,Banana,3
20180103,Apple,1
20180103,Banana,4
20180104,Apple,2
20180104,Banana,2
20180105,Apple,1
20180105,Banana,2
20180106,Apple,2
20180106,Banana,3
20180107,Apple,2
20180107,Banana,1
20180108,Apple,0
20180108,Banana,3
20180109,Apple,2
20180109,Banana,1
20180110,Apple,3
20180110,Banana,1")
#order is important before doing cumsum
setorder(fruitSold, Date, name)
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
1) Here is a possible approach using data.table non-equi join:
#calculate the available stock at each date
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
#calculate the fruits sold up to date
cumsold <- fruitSold[, .(Date, SoldToDate=cumsum(sold)), .(name)]
#use non-equi join to find the first date where
#sold to date is greater than available stock as per OP
cumsold[
FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail),
#for each row in FruitBought, find that first date
.(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][,
-(1L:3L)] #remove the joining columns
2) You can also use the data.table's roll argument by slightly tweaking the number sold to date:
FruitBought[, CumAvail:=cumsum(Qty), by=.(name)]
cumsoldTweak <- fruitSold[, .(Date, SoldToDate=cumsum(sold)-1e-2), .(name)]
cumsoldTweak[FruitBought, on=c("name", SoldToDate="CumAvail"), roll=-Inf,
.(name, Date=i.Date, Qty, LastSoldOut=Date)]
output:
Name Date Qty LastSoldOut
1: Apple 20180101 15 20180107
2: Apple 20180105 20 NA
3: Banana 20180102 18 20180109
4: Banana 20180109 14 NA
data:
library(data.table)
#data.table 1.11.4 Latest news: http://r-datatable.com
FruitBought <- fread("name,Date,Qty
Apple,20180101,15
Apple,20180105,20
Banana,20180102,18
Banana,20180109,14")
#order is important before doing cumsum
setorder(FruitBought, name, Date)
fruitSold <- fread("Date,name,sold
20180101,Apple,5
20180102,Apple,3
20180102,Banana,3
20180103,Apple,1
20180103,Banana,4
20180104,Apple,2
20180104,Banana,2
20180105,Apple,1
20180105,Banana,2
20180106,Apple,2
20180106,Banana,3
20180107,Apple,2
20180107,Banana,1
20180108,Apple,0
20180108,Banana,3
20180109,Apple,2
20180109,Banana,1
20180110,Apple,3
20180110,Banana,1")
#order is important before doing cumsum
setorder(fruitSold, Date, name)
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
edited Nov 22 '18 at 6:14
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
answered Nov 21 '18 at 6:07
chinsoon12chinsoon12
8,83611219
8,83611219
It works. But i having some difficulties to understand what happen. since i'm not familiar with data.table package. :D oncumsold[ FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail), #for each row in FruitBought, find that first date .(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][, -(1L:3L)] #remove the joining columnsis that the syntax for joining.. I'll try to understand your code first. by the way your code is works, and i'd like to say thanks. Regard,
– Rizal Chandra
Nov 28 '18 at 11:19
add a comment |
It works. But i having some difficulties to understand what happen. since i'm not familiar with data.table package. :D oncumsold[ FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail), #for each row in FruitBought, find that first date .(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][, -(1L:3L)] #remove the joining columnsis that the syntax for joining.. I'll try to understand your code first. by the way your code is works, and i'd like to say thanks. Regard,
– Rizal Chandra
Nov 28 '18 at 11:19
It works. But i having some difficulties to understand what happen. since i'm not familiar with data.table package. :D on
cumsold[ FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail), #for each row in FruitBought, find that first date .(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][, -(1L:3L)] #remove the joining columns is that the syntax for joining.. I'll try to understand your code first. by the way your code is works, and i'd like to say thanks. Regard,– Rizal Chandra
Nov 28 '18 at 11:19
It works. But i having some difficulties to understand what happen. since i'm not familiar with data.table package. :D on
cumsold[ FruitBought, on=.(name=name, Date>=Date, SoldToDate>CumAvail), #for each row in FruitBought, find that first date .(Name=i.name, Date=i.Date, Qty, LastSoldOut=x.Date[1L]), by=.EACHI][, -(1L:3L)] #remove the joining columns is that the syntax for joining.. I'll try to understand your code first. by the way your code is works, and i'd like to say thanks. Regard,– Rizal Chandra
Nov 28 '18 at 11:19
add a comment |
0
You can do something like this to get the desired output
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,~id,
"Apple", 20180101, 15,1,
"Apple", 20180105, 20,2,
"Banana", 20180102, 18,1,
"Banana", 20180109, 14,2,
)
fruitSold <- tribble(
~Date, ~name, ~sold,~id,
20180101, 'Apple', 5,1,
20180102, 'Apple', 3,1,
20180102, 'Banana', 3,1,
20180103, 'Apple', 1,1,
20180103, 'Banana', 4,1,
20180104, 'Apple', 2,1,
20180104, 'Banana', 2,1,
20180105, 'Apple', 1,1,
20180105, 'Banana', 2,1,
20180106, 'Apple', 2,1,
20180106, 'Banana', 3,1,
20180107, 'Apple', 2,1,
20180107, 'Banana', 1,1,
20180108, 'Apple', 0,2,
20180108, 'Banana', 3,1,
20180109, 'Apple', 2,2,
20180109, 'Banana', 1,1,
20180110, 'Apple', 3,2,
20180110, 'Banana', 1,1
)
fruitSold$Date <- lubridate::ymd(fruitSold$Date)
FruitBought$Date <- lubridate::ymd(FruitBought$Date )
colnames(fruitSold)
sold <- as.data.frame(fruitSold %>% group_by(name,id) %>% summarise(last_date = max(Date)))
colnames(sold)
colnames(FruitBought)
final <- left_join(FruitBought,sold, by = c("name","id"))
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
add a comment |
0
You can do something like this to get the desired output
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,~id,
"Apple", 20180101, 15,1,
"Apple", 20180105, 20,2,
"Banana", 20180102, 18,1,
"Banana", 20180109, 14,2,
)
fruitSold <- tribble(
~Date, ~name, ~sold,~id,
20180101, 'Apple', 5,1,
20180102, 'Apple', 3,1,
20180102, 'Banana', 3,1,
20180103, 'Apple', 1,1,
20180103, 'Banana', 4,1,
20180104, 'Apple', 2,1,
20180104, 'Banana', 2,1,
20180105, 'Apple', 1,1,
20180105, 'Banana', 2,1,
20180106, 'Apple', 2,1,
20180106, 'Banana', 3,1,
20180107, 'Apple', 2,1,
20180107, 'Banana', 1,1,
20180108, 'Apple', 0,2,
20180108, 'Banana', 3,1,
20180109, 'Apple', 2,2,
20180109, 'Banana', 1,1,
20180110, 'Apple', 3,2,
20180110, 'Banana', 1,1
)
fruitSold$Date <- lubridate::ymd(fruitSold$Date)
FruitBought$Date <- lubridate::ymd(FruitBought$Date )
colnames(fruitSold)
sold <- as.data.frame(fruitSold %>% group_by(name,id) %>% summarise(last_date = max(Date)))
colnames(sold)
colnames(FruitBought)
final <- left_join(FruitBought,sold, by = c("name","id"))
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
add a comment |
0
0
0
You can do something like this to get the desired output
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,~id,
"Apple", 20180101, 15,1,
"Apple", 20180105, 20,2,
"Banana", 20180102, 18,1,
"Banana", 20180109, 14,2,
)
fruitSold <- tribble(
~Date, ~name, ~sold,~id,
20180101, 'Apple', 5,1,
20180102, 'Apple', 3,1,
20180102, 'Banana', 3,1,
20180103, 'Apple', 1,1,
20180103, 'Banana', 4,1,
20180104, 'Apple', 2,1,
20180104, 'Banana', 2,1,
20180105, 'Apple', 1,1,
20180105, 'Banana', 2,1,
20180106, 'Apple', 2,1,
20180106, 'Banana', 3,1,
20180107, 'Apple', 2,1,
20180107, 'Banana', 1,1,
20180108, 'Apple', 0,2,
20180108, 'Banana', 3,1,
20180109, 'Apple', 2,2,
20180109, 'Banana', 1,1,
20180110, 'Apple', 3,2,
20180110, 'Banana', 1,1
)
fruitSold$Date <- lubridate::ymd(fruitSold$Date)
FruitBought$Date <- lubridate::ymd(FruitBought$Date )
colnames(fruitSold)
sold <- as.data.frame(fruitSold %>% group_by(name,id) %>% summarise(last_date = max(Date)))
colnames(sold)
colnames(FruitBought)
final <- left_join(FruitBought,sold, by = c("name","id"))
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
You can do something like this to get the desired output
library(tibble)
library(tidyverse)
FruitBought <- tribble(
~name, ~Date, ~Qty,~id,
"Apple", 20180101, 15,1,
"Apple", 20180105, 20,2,
"Banana", 20180102, 18,1,
"Banana", 20180109, 14,2,
)
fruitSold <- tribble(
~Date, ~name, ~sold,~id,
20180101, 'Apple', 5,1,
20180102, 'Apple', 3,1,
20180102, 'Banana', 3,1,
20180103, 'Apple', 1,1,
20180103, 'Banana', 4,1,
20180104, 'Apple', 2,1,
20180104, 'Banana', 2,1,
20180105, 'Apple', 1,1,
20180105, 'Banana', 2,1,
20180106, 'Apple', 2,1,
20180106, 'Banana', 3,1,
20180107, 'Apple', 2,1,
20180107, 'Banana', 1,1,
20180108, 'Apple', 0,2,
20180108, 'Banana', 3,1,
20180109, 'Apple', 2,2,
20180109, 'Banana', 1,1,
20180110, 'Apple', 3,2,
20180110, 'Banana', 1,1
)
fruitSold$Date <- lubridate::ymd(fruitSold$Date)
FruitBought$Date <- lubridate::ymd(FruitBought$Date )
colnames(fruitSold)
sold <- as.data.frame(fruitSold %>% group_by(name,id) %>% summarise(last_date = max(Date)))
colnames(sold)
colnames(FruitBought)
final <- left_join(FruitBought,sold, by = c("name","id"))
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
edited Nov 21 '18 at 5:21
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
answered Nov 21 '18 at 5:04
HunaidkhanHunaidkhan
821114
821114
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53405496%2fr-how-to-get-last-sold-out-date-for-each-buying%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
1
What is the logic of selecting the
LastSoldOutdate? For "Apple" you also have dates which are greater than "20180107"– Ronak Shah
Nov 21 '18 at 5:00
you need to provide some unique identifier for each buying item , otherwise for system everything will be same
– Hunaidkhan
Nov 21 '18 at 5:06
@RonakShah, ah, i mean, on January 1st I bought 15 Apples, I want to know when that 15 apples sold out.
– Rizal Chandra
Nov 28 '18 at 11:14