Find time interval difference between two intervals, grouped by column
I'm trying to take the difference in time between two rows (cohort$Pharm_FillDate), if and only if they are duplicates within two other columns (cohort$Pharm_FillDate and cohort$DrugName) grouped by a third (cohort$PatientID), and there are multiple (>10k) combinations. I've tried a number of threads (close, but not exactly right given only 2 defined groups to sort by: How can I find the first and last occurrences of an element in a data.frame?).
I've been trying various dplyr options, but I think I'm not facile enough with the permutations.
My dataframe (cohort) looks like this:
PatientID (factor), DrugName (character string), Pharm_FillDate (YYYY-MM-DD)
<PatientID> <DrugName> <Pharm_FillDate>
A Aspirin 2018-11-01
A Aspirin 2018-11-05
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01
A Ibuprofen 2018-11-02
B Metformin 2017-10-01
B Lisinopril 2018-01-01
I can successfully get what I want if I am ONLY dealing with one patient, but am trying to figure out how I can do this for every patient (n = 33,000).
This is an example of something that works for only one patient, but in multiple instances of duplicated drugs (i.e. Ibuprofen for Patient A - I want to capture the time difference between each instance - using something like tail() or last() hasn't been successful), OR to work through multiple Patient ID's, then I get stuck.
Also, reformatting my dataset as wide wouldn't work as I have a number of other variables that don't work well with that.
cohort$Days_Between_Fills<- ifelse(duplicated(cohort$Drug_Name),
as.numeric(paste(
difftime(cohort$Pharm_FillDate[1],
cohort$Pharm_FillDate[2:length(cohort$Pharm_FillDate)])[3])), "")
A desired output would give something like this:
<PatientID> <DrugName> <Pharm_FillDate> <Days_Between_Fills>
A Aspirin 2018-11-01
A Aspirin 2018-11-05 4
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01 31
A Ibuprofen 2018-11-02 1
A Advil 2018-09-30
B Metformin 2017-10-01
B Lisinopril 2018-01-01
B Metformin 2017-10-15 14
Thanks so much -
duplicates grouping difftime
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
add a comment |
I'm trying to take the difference in time between two rows (cohort$Pharm_FillDate), if and only if they are duplicates within two other columns (cohort$Pharm_FillDate and cohort$DrugName) grouped by a third (cohort$PatientID), and there are multiple (>10k) combinations. I've tried a number of threads (close, but not exactly right given only 2 defined groups to sort by: How can I find the first and last occurrences of an element in a data.frame?).
I've been trying various dplyr options, but I think I'm not facile enough with the permutations.
My dataframe (cohort) looks like this:
PatientID (factor), DrugName (character string), Pharm_FillDate (YYYY-MM-DD)
<PatientID> <DrugName> <Pharm_FillDate>
A Aspirin 2018-11-01
A Aspirin 2018-11-05
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01
A Ibuprofen 2018-11-02
B Metformin 2017-10-01
B Lisinopril 2018-01-01
I can successfully get what I want if I am ONLY dealing with one patient, but am trying to figure out how I can do this for every patient (n = 33,000).
This is an example of something that works for only one patient, but in multiple instances of duplicated drugs (i.e. Ibuprofen for Patient A - I want to capture the time difference between each instance - using something like tail() or last() hasn't been successful), OR to work through multiple Patient ID's, then I get stuck.
Also, reformatting my dataset as wide wouldn't work as I have a number of other variables that don't work well with that.
cohort$Days_Between_Fills<- ifelse(duplicated(cohort$Drug_Name),
as.numeric(paste(
difftime(cohort$Pharm_FillDate[1],
cohort$Pharm_FillDate[2:length(cohort$Pharm_FillDate)])[3])), "")
A desired output would give something like this:
<PatientID> <DrugName> <Pharm_FillDate> <Days_Between_Fills>
A Aspirin 2018-11-01
A Aspirin 2018-11-05 4
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01 31
A Ibuprofen 2018-11-02 1
A Advil 2018-09-30
B Metformin 2017-10-01
B Lisinopril 2018-01-01
B Metformin 2017-10-15 14
Thanks so much -
duplicates grouping difftime
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
add a comment |
I'm trying to take the difference in time between two rows (cohort$Pharm_FillDate), if and only if they are duplicates within two other columns (cohort$Pharm_FillDate and cohort$DrugName) grouped by a third (cohort$PatientID), and there are multiple (>10k) combinations. I've tried a number of threads (close, but not exactly right given only 2 defined groups to sort by: How can I find the first and last occurrences of an element in a data.frame?).
I've been trying various dplyr options, but I think I'm not facile enough with the permutations.
My dataframe (cohort) looks like this:
PatientID (factor), DrugName (character string), Pharm_FillDate (YYYY-MM-DD)
<PatientID> <DrugName> <Pharm_FillDate>
A Aspirin 2018-11-01
A Aspirin 2018-11-05
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01
A Ibuprofen 2018-11-02
B Metformin 2017-10-01
B Lisinopril 2018-01-01
I can successfully get what I want if I am ONLY dealing with one patient, but am trying to figure out how I can do this for every patient (n = 33,000).
This is an example of something that works for only one patient, but in multiple instances of duplicated drugs (i.e. Ibuprofen for Patient A - I want to capture the time difference between each instance - using something like tail() or last() hasn't been successful), OR to work through multiple Patient ID's, then I get stuck.
Also, reformatting my dataset as wide wouldn't work as I have a number of other variables that don't work well with that.
cohort$Days_Between_Fills<- ifelse(duplicated(cohort$Drug_Name),
as.numeric(paste(
difftime(cohort$Pharm_FillDate[1],
cohort$Pharm_FillDate[2:length(cohort$Pharm_FillDate)])[3])), "")
A desired output would give something like this:
<PatientID> <DrugName> <Pharm_FillDate> <Days_Between_Fills>
A Aspirin 2018-11-01
A Aspirin 2018-11-05 4
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01 31
A Ibuprofen 2018-11-02 1
A Advil 2018-09-30
B Metformin 2017-10-01
B Lisinopril 2018-01-01
B Metformin 2017-10-15 14
Thanks so much -
duplicates grouping difftime
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
I'm trying to take the difference in time between two rows (cohort$Pharm_FillDate), if and only if they are duplicates within two other columns (cohort$Pharm_FillDate and cohort$DrugName) grouped by a third (cohort$PatientID), and there are multiple (>10k) combinations. I've tried a number of threads (close, but not exactly right given only 2 defined groups to sort by: How can I find the first and last occurrences of an element in a data.frame?).
I've been trying various dplyr options, but I think I'm not facile enough with the permutations.
My dataframe (cohort) looks like this:
PatientID (factor), DrugName (character string), Pharm_FillDate (YYYY-MM-DD)
<PatientID> <DrugName> <Pharm_FillDate>
A Aspirin 2018-11-01
A Aspirin 2018-11-05
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01
A Ibuprofen 2018-11-02
B Metformin 2017-10-01
B Lisinopril 2018-01-01
I can successfully get what I want if I am ONLY dealing with one patient, but am trying to figure out how I can do this for every patient (n = 33,000).
This is an example of something that works for only one patient, but in multiple instances of duplicated drugs (i.e. Ibuprofen for Patient A - I want to capture the time difference between each instance - using something like tail() or last() hasn't been successful), OR to work through multiple Patient ID's, then I get stuck.
Also, reformatting my dataset as wide wouldn't work as I have a number of other variables that don't work well with that.
cohort$Days_Between_Fills<- ifelse(duplicated(cohort$Drug_Name),
as.numeric(paste(
difftime(cohort$Pharm_FillDate[1],
cohort$Pharm_FillDate[2:length(cohort$Pharm_FillDate)])[3])), "")
A desired output would give something like this:
<PatientID> <DrugName> <Pharm_FillDate> <Days_Between_Fills>
A Aspirin 2018-11-01
A Aspirin 2018-11-05 4
A Ibuprofen 2018-10-10
A Ibuprofen 2018-11-01 31
A Ibuprofen 2018-11-02 1
A Advil 2018-09-30
B Metformin 2017-10-01
B Lisinopril 2018-01-01
B Metformin 2017-10-15 14
Thanks so much -
duplicates grouping difftime
duplicates grouping difftime
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
asked Nov 24 '18 at 0:45
lsakwa18lsakwa18
11
11
add a comment |
add a comment |
0
active
oldest
votes
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%2f53454257%2ffind-time-interval-difference-between-two-intervals-grouped-by-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53454257%2ffind-time-interval-difference-between-two-intervals-grouped-by-column%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
