[R] Get Specific Records from Another DataFrame

Jeff Newmiller jdnewmil at dcn.davis.ca.us
Sun Mar 25 08:25:47 CEST 2018


I suggest that you read the vignette for the data table package. That package uses some odd syntax compared to base R but has some features designed especially for these kinds of problems. 
-- 
Sent from my phone. Please excuse my brevity.

On March 24, 2018 5:03:44 PM PDT, M Can <mscbuysell at gmail.com> wrote:
>Hello
>I have been struggling with this simple looking problem. I have two
>dataframes. The first one contains ID, date, and revenue information
>for
>specific suppliers.
>
>id lastdate depvar
>A 5/10/2017 10
>B 8/16/2017 20
>C 2/14/2017 30
>D 9/5/2017 40
>E 8/1/2017 50
>F 11/4/2017 60
>G 6/22/2017 70
>The second dataframe contains timeseries data of each supplier in
>different
>columns. For example Column A are the transaction dates of supplier A
>and
>A_indvar is an independent variable value for each date.
>
>A_date A_indvar B_date B_indvar …
>1/1/2017 49 2/5/2017 50
>1/2/2017 42 2/6/2017 62
>1/3/2017 10 2/7/2017 88
>1/4/2017 37 2/8/2017 36
>1/5/2017 84 2/9/2017 71
>1/6/2017 47 2/10/2017 36
>1/7/2017 91 2/11/2017 98
>Now, I would like to run a regression equation using specific
>independent
>variable values from the second dataset. For example, let say I want to
>look at the independent variable values of each supplier 30 days before
>the
>last date given in the first dataset. I want to locate those from the
>second dataset and enter them into the first dataset. So that the first
>dataset will look like:
>
>id lastdate depvar indvar-30
>A 5/10/2017 10 55
>B 8/16/2017 20 62
>C 2/14/2017 30 74
>D 9/5/2017 40 45
>E 8/1/2017 50 35
>F 11/4/2017 60 56
>G 6/22/2017 70 48
>I am able to create an array that takes all last dates and then
>subtracts
>30 from them. Then how can I go to the second table and grab the value
>of
>the independent variables for those specific dates? Timeseries of the
>suppliers have different start dates.
>
>I feel like I need to use something similar to vlookup in Excel. But a
>value in the first dataset (let's say Supplier A) becomes a variable
>name
>in dataset two.
>
>Any hint is appreciated.
>
>Thank you.
>
>	[[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.



More information about the R-help mailing list