Match and find the earliest value in R -

Match and find the earliest value in R -

i have 2 tables, 1 sales history, other key clients.

the sales history table looks this(contains 500,000 rows):

client no. transaction date sales amount abc 1/12/2014 100 def 2/28/2014 200 hij 3/01/2014 300 abc 2/18/2014 400 abc 5/26/2014 500 xyz 7/15/2014 600 def 8/23/2014 700 hij 9/19/2014 800

and key clients table looks this:

client no. abc def xyz

now want r that, based on key clients table, need create new table matching earliest sales amount sales history table. desired results looks this:

client no. earliest sales amount abc 100 def 200 xyz 600

i recommend using data.tables binary join, utilize .eachi this

library(data.table) setkey(setdt(df)[, transaction.date := as.idate(transaction.date, "%m/%d/%y")], client.no.) df[key, .(earliest.sales.amont = sales.amount[which.min(transaction.date)]), = .eachi] # client.no. earliest.sales.amont # 1: abc 100 # 2: def 200 # 3: xyz 600

first, convert info set (df) data.table class using setdt, convert transaction.date date class r understand mean while looking minimum date , key client.no. in order perform binary join (this efficient big info set yours). then, while performing binary join on key table (key) utilize by = .eachi in order locate sale.amount within earliest date in info set.

r