How to merge two dataframes base on some conditions?

Question

I have a dataframe called PATIENTS which looks like this:

  ID   ARR_DATETIME
  1    2013-01-01 03:00:00
  2    2013-05-12 01:00:00
  3    2013-06-23 14:00:00

I have anothe rdataframe called CENSUS and it looks like this:

  DATETIME              COUNT
  2013-01-01 01:00:00    4
  2013-01-01 02:00:00    5
  2013-01-01 03:00:00    9
  ...
  2013-05-12 01:00:00    8
  ...
  2013-06-23 14:00:00   6

What I want is to add another column "COUNT" to my PATIENTS dataframe based on the conditional statement that only the DATETIME matches the ARR_DATETIME.

  ID   ARR_DATETIME           COUNT
  1    2013-01-01 03:00:00    9
  2    2013-05-12 01:00:00    8
  3    2013-06-23 14:00:00    6

I implemented a nested for loop but due to the size of my data, it almost takes forever.

Any suggestions?

It would be useful if you could add a reproducible example with expected output to see exactly what you want — drJones, Mar 06 '14 at 20:46
Hey, it is better because now we can see what you want, but still not reproducible. See [here](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — drJones, Mar 07 '14 at 01:09

maloneypatr · Answer 1 · 2014-03-07T12:23:34.837

0

I believe you're looking to use the merge function.

CENSUS
DATETIME         COUNT
1/1/2013 4:00        9
1/1/2013 5:00        8
1/1/2013 6:00        3
1/1/2013 7:00        7

PATIENTS
name        ARR_DATETIME
Joe A.     1/1/2013 7:00
Pat J.     1/1/2013 5:00
Thomas L.  1/1/2013 6:00

# EDIT :: I DID NOT PUT QUOTATIONS AROUND THE by.x & by.y arguments.
# TRY THIS
merge(PATIENTS, CENSUS, by.x = 'ARR_DATETIME', by.y = 'DATETIME')

edited Mar 07 '14 at 12:23

answered Mar 06 '14 at 20:55

maloneypatr

3,562
4
23
33

I tried but R gave me a errorError in sort.list(bx[m$xi]) : 'x' must be atomic for 'sort.list' Have you called 'sort' on a list? – user2350622 Mar 06 '14 at 23:28
Are both of these structures data.frames? If you type `str(PATIENTS)` and `str(CENSUS)`, is `CENSUS$DATETIME` and `PATIENTS$ARR_DATETIME` of the same type? – maloneypatr Mar 07 '14 at 12:16
ya I just found the issue. I should turn both ARR_DATETIME and DATETIME to character type instead of Date. – user2350622 Mar 08 '14 at 03:00

score 0 · Accepted Answer · answered Mar 11 '14 at 03:35

0

merge(PATIENTS, CENSUS, by.x = "ARR_DATETIME", by.y = "DATETIME")

But you have to make sure ARR_DATETIME & DATETIME are in the format of character, not Date objects.

answered Mar 11 '14 at 03:35

user2350622

77
1
3
13

How to merge two dataframes base on some conditions?

2 Answers2