ykjk ykjk - 1 month ago 8
R Question

Extract all rows from df2 that match with info from 2 columns of df1

So I know that similar questions have been posed here and here but none of them seemed to work for my case.

So I have 2 df's:

df1 is the smaller df that contains date, a stock and the corresponding number of trades of that stock, much like this:

date | symbol |trades
05.01.2010 | AAPL |34500
05.01.2010 | AET |26900
05.01.2010 | ABL |47300


df2 is the bigger one, it contains date, stock and trades as well as 27 other characteristics:

date |symbol| Correlation 2
2010-01-05 |AET | 0,765
2010-01-05 |ABL | 0,315
2010-01-05 |QXY | 0,2157
2010-01-05 |AAPL | 0,31


Since I need to determine the correlation between values from a wide format of
df1
to
df2
I need to extract all those rows from df2 that match with date AND symbol from
df1
.
So all that is left in
df3
would be
AET
and
ABL
, but
QXY
never showed in
df1
so I don't need it in
df3
either.

The other measure that I would like to use for the correlation is in wide format(df1_wide), so I would need some code that would allow me to extract only the correlation 2 (with date and align it the same way as the symbols would be aligned) so I can swap the format to wide format as well and calculate my correlation (this would be df3_wide)

Example of df1_wide:

date | AAPL | AET
05.01.2010 | 34500 | 26900


And for my correlation the corresponding df3_wide:

date | AAPL | AET
05.01.2010 | 0,31 | 0,765

Answer

you need to set the same dates formats and then read carefully merge it can do two merging on two keys as well. Give it a go, if you can't do it, I can help you. Try to paste some code from dput(your_data_frame) so it is easily reproducible.

Comments