0

I have multiple, equal-sized dataframes containing different daily values (e.g. yield, bid-ask spread,...) for several financial instruments, which I want to merge into one dataframe. Let's say I have:

X

Date             A          B         C       
2020-12-16       XA1        XB1       XC1
2020-12-15       XA2        XB2       XC2
2020-12-14       XA3        XB2       XC3
...

and:

Y

Date             A          B         C       
2020-12-16       YA1        YB1       YC1
2020-12-15       YA2        YB2       YC2
2020-12-14       YA3        YB2       YC3
...

And I would like to get:

Z

ISIN      Date             X          Y                
A         2020-12-16       XA1        YA1      
A         2020-12-15       XA2        YA2      
A         2020-12-14       XA3        YA3 
B         2020-12-16       XB1        YB1 
B         2020-12-15       XB2        YB2 
B         2020-12-14       XB3        YB3 
C         2020-12-16       XC1        YC1 
C         2020-12-15       XC2        YC2 
C         2020-12-14       XC3        YC3 
...

So far I managed to create a dataframe in this simplistic way:

X = c(as.matrix(X[,2:ncol(X)]))
Y = c(as.matrix(Y[,2:ncol(Y)]))

df = data.frame(X,Y)

However, as you can see the results does not include neither the ISIN nor the date. I'm pretty sure there is a logical and efficient solution to solve this problem, which however I didn't figure it out yet. Any help would be much appreciated!

Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
PDO
  • 1
  • 1
    Can you provide a [reproducible example]https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example? Are dates guaranteed to be unique? ordered? the same timeframe for both? What should happen if they are not? – Robin Gertenbach Mar 17 '21 at 12:32
  • Without a reprex (see above comment) its harder to help, but using `pivot_longer()` to make long forms of both data.frames first and then using a join (see `full_join()` or `inner_join()`) will get you really close to what you're looking for. – Jeffrey Brabec Mar 17 '21 at 12:45
  • Dear both, thank you a lot for your comments and sorry for not priving a reprex (this was my first question). Anyway, I used the method proposed by Jeffrey and it worked perfectly. Thanks again and have a great day. – PDO Mar 17 '21 at 14:45

0 Answers0