Styjun

Map vs. Table for index-specific operations on 2D arrays

How is Sword Coast North governed?

What do the screens say after you are set free?

Should I take up a Creative Writing online course?

What does a capital letter mean in tablature?

Adding a (stair/baby) gate without facing walls

Protect a 6 inch air hose from physical damage

Why are Star Wars Rebel Alliance ships named after letters from the Latin alphabet?

Why is “deal 6 damage” a legit phrase?

Ernie and the Superconducting Boxes

Why interlaced CRT scanning wasn't done back and forth?

If I buy and download a game through second Nintendo account do I own it on my main account too?

Need help in optimizing the below helper class

speaker impedence

UX writing: When to use "we"?

Python π = 1 + (1/2) + (1/3) + (1/4) - (1/5) + (1/6) + (1/7) + (1/8) + (1/9) - (1/10) ...1748 Euler

What is realistic quality of computer blueprints quickly backed up before apocalypse and their impact on future design?

Being told my "network" isn't PCI Complaint. I don't even have a server! Do I have to comply?

How to draw twisted cuves?

Overprovisioning SSD on ubuntu. How? Ubuntu 19.04 Samsung SSD 860

How long should I wait to plug in my refrigerator after unplugging it?

Is the EU really banning "toxic propellants" in 2020? How is that going to work?

How to get maximum number that newcount can hold?

Can birds evolve without trees?

Finding best match based on smallest difference between rows

What are the differences between “=” and “<-” in R?Match dataframe rows according to two variables (Indexing)What is the difference between require() and library()?Create an empty data.frameMatching row entries with column names in Rdata.table vs dplyr: can one do something well the other can't or does poorly?% of matching between rows and find duplications based on specific columnfilter rows that match row in different data.frameHow to label clusters of rows based on matches using True/False?Detect differences between two lists of data.frames

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have combined two datasets and it is not a 1 to 1 relationship. I must now identify the best match (between rows) based on a time delay.

I have tried this in MySQL and in R and haven't been able to find anything.

My initial data looks like this:

data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'))

And I want to end up with something like this:

data2 <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'), 
'best_match' = c(TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE))

r

share|improve this question

edited Mar 27 at 1:53

Ronak Shah

72.1k1010 gold badges4848 silver badges8282 bronze badges

asked Mar 27 at 0:23

Jared PetersonJared Peterson

111 bronze badge

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Which time unit is delay? What is best match definition?
  
  – Andrew Bannerman
  Mar 27 at 0:33
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you mean the variable type? 'delay' is of type character currently. And the best match is the smallest amount of total time for each 'sent_id' (since 'sent_id' is not a unique value).
  
  – Jared Peterson
  Mar 27 at 15:31
  

  
  
  
  

add a comment | 

0

I have combined two datasets and it is not a 1 to 1 relationship. I must now identify the best match (between rows) based on a time delay.

I have tried this in MySQL and in R and haven't been able to find anything.

My initial data looks like this:

data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'))

And I want to end up with something like this:

data2 <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'), 
'best_match' = c(TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE))

r

share|improve this question

edited Mar 27 at 1:53

Ronak Shah

72.1k1010 gold badges4848 silver badges8282 bronze badges

asked Mar 27 at 0:23

Jared PetersonJared Peterson

111 bronze badge

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Which time unit is delay? What is best match definition?
  
  – Andrew Bannerman
  Mar 27 at 0:33
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you mean the variable type? 'delay' is of type character currently. And the best match is the smallest amount of total time for each 'sent_id' (since 'sent_id' is not a unique value).
  
  – Jared Peterson
  Mar 27 at 15:31
  

  
  
  
  

add a comment | 

I have combined two datasets and it is not a 1 to 1 relationship. I must now identify the best match (between rows) based on a time delay.

I have tried this in MySQL and in R and haven't been able to find anything.

My initial data looks like this:

data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'))

And I want to end up with something like this:

data2 <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'), 
'best_match' = c(TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE))

r

share|improve this question

edited Mar 27 at 1:53

Ronak Shah

72.1k1010 gold badges4848 silver badges8282 bronze badges

asked Mar 27 at 0:23

Jared PetersonJared Peterson

111 bronze badge

data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'))

data2 <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), 
 "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), 
 "delay" = c('00:00:00','15:00:00','-00:14:59','00:00:01','23:00:05','00:01:00',
 '-18:00:00','15:00:00','23:00:00','00:30:10'), 
'best_match' = c(TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE))

share|improve this question

edited Mar 27 at 1:53

Ronak Shah

72.1k1010 gold badges4848 silver badges8282 bronze badges

asked Mar 27 at 0:23

Jared PetersonJared Peterson

111 bronze badge

share|improve this question

edited Mar 27 at 1:53

Ronak Shah

72.1k1010 gold badges4848 silver badges8282 bronze badges

asked Mar 27 at 0:23

Jared PetersonJared Peterson

111 bronze badge

edited Mar 27 at 1:53

Ronak Shah

72.1k1010 gold badges4848 silver badges8282 bronze badges

edited Mar 27 at 1:53

Ronak Shah

72.1k1010 gold badges4848 silver badges8282 bronze badges

asked Mar 27 at 0:23

Jared PetersonJared Peterson

111 bronze badge

asked Mar 27 at 0:23

Jared PetersonJared Peterson

111 bronze badge

1 Answer
1

active

oldest

votes

0

Got rid of the negative signs in the delay and did the following.

test_data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), "delay" = c('00:00:00','15:00:00','00:14:59','00:00:01','23:00:05','00:01:00','18:00:00','15:00:00','23:00:00','00:30:10'))

received_id <-unique(test_data$recieved_id)
sent_id_2 <-unique(test_data$sent_id)

library(dplyr)

new.frame <- data.frame("sent_id" = NA,
"recieved_id" = NA,
"delay" = NA)

for(i in 1:length(test_data$sent_id))
new.frame[i,] <- arrange(test_data %>% filter(sent_id == sent_id_2[i]), delay)[1,]


Cleaned it up a bit for the actual code. But this will get you there.

share|improve this answer

answered Mar 27 at 23:18

Jared PetersonJared Peterson

111 bronze badge

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55368054%2ffinding-best-match-based-on-smallest-difference-between-rows%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Got rid of the negative signs in the delay and did the following.

test_data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), "delay" = c('00:00:00','15:00:00','00:14:59','00:00:01','23:00:05','00:01:00','18:00:00','15:00:00','23:00:00','00:30:10'))

received_id <-unique(test_data$recieved_id)
sent_id_2 <-unique(test_data$sent_id)

library(dplyr)

new.frame <- data.frame("sent_id" = NA,
"recieved_id" = NA,
"delay" = NA)

for(i in 1:length(test_data$sent_id))
new.frame[i,] <- arrange(test_data %>% filter(sent_id == sent_id_2[i]), delay)[1,]


Cleaned it up a bit for the actual code. But this will get you there.

share|improve this answer

answered Mar 27 at 23:18

Jared PetersonJared Peterson

111 bronze badge

add a comment | 

0

Got rid of the negative signs in the delay and did the following.

test_data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), "delay" = c('00:00:00','15:00:00','00:14:59','00:00:01','23:00:05','00:01:00','18:00:00','15:00:00','23:00:00','00:30:10'))

received_id <-unique(test_data$recieved_id)
sent_id_2 <-unique(test_data$sent_id)

library(dplyr)

new.frame <- data.frame("sent_id" = NA,
"recieved_id" = NA,
"delay" = NA)

for(i in 1:length(test_data$sent_id))
new.frame[i,] <- arrange(test_data %>% filter(sent_id == sent_id_2[i]), delay)[1,]


Cleaned it up a bit for the actual code. But this will get you there.

share|improve this answer

answered Mar 27 at 23:18

Jared PetersonJared Peterson

111 bronze badge

add a comment | 

Got rid of the negative signs in the delay and did the following.

test_data <- data.frame("sent_id" = c(1,1,2,2,3,3,3,4,4,4), "recieved_id" = c(100,101,100,101,105,106,107,105,106,107), "delay" = c('00:00:00','15:00:00','00:14:59','00:00:01','23:00:05','00:01:00','18:00:00','15:00:00','23:00:00','00:30:10'))

received_id <-unique(test_data$recieved_id)
sent_id_2 <-unique(test_data$sent_id)

library(dplyr)

new.frame <- data.frame("sent_id" = NA,
"recieved_id" = NA,
"delay" = NA)

for(i in 1:length(test_data$sent_id))
new.frame[i,] <- arrange(test_data %>% filter(sent_id == sent_id_2[i]), delay)[1,]


Cleaned it up a bit for the actual code. But this will get you there.

share|improve this answer

answered Mar 27 at 23:18

Jared PetersonJared Peterson

111 bronze badge

share|improve this answer

answered Mar 27 at 23:18

Jared PetersonJared Peterson

111 bronze badge

answered Mar 27 at 23:18

Jared PetersonJared Peterson

111 bronze badge

answered Mar 27 at 23:18

Jared PetersonJared Peterson

111 bronze badge