Cbinding Problems

Question

I have a dataset as follows and my goal is to cbind each of these dataframes (df1,df2, and df3) by the Date:

df1

Date           COL1      
2015-05-27      5           
2015-05-28      7      
2015-05-29      8       
2015-05-30      7    
2015-05-31      4   
2015-06-01      8  

df2

Date           COL2        
2015-05-28       6       
2015-05-29       9
2015-05-30      10
2015-05-31      11
2015-06-01      12

df3

Date            COL3
2015-01-01      12
2015-01-02       8
 .
 .
 .
 .
2015-06-01      20

I want to cbind these so that it is by Date. Regular cbind doesn't work because it has different number of rows. And when I do cbind.fill, the NA's extend past the dataframe even when I use all.x=TRUE

So the end result should look like this:

   Date        COL3   COL2  COL1
2015-01-01      12     NA    NA
2015-01-02       8     NA    NA
 .              .      .     .
 .              .      .     .
 .              .      .     .
 .              .      .     .
2015-05-31      12     11    4
2015-06-01      20     12    8

Any help would be great, thanks!

What is the logic here? You just want to move the `NA` to the buttom? — David Arenburg, Sep 01 '15 at 19:33
It seems that he wants to arrange `Date2` and `Col2` according to `Date2`, but I'm guesstimating a bit here. @OP, what do you exactly mean? Also, make your example reproducible, for tips [see here](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — Paul Hiemstra, Sep 01 '15 at 19:34
I want to match the Dates together. Col1 corresponds to Date and COL2 corresponds to Date2. My actual dataset is much larger than this and there are more than one NA's but I was just trying to figure out the logic @DavidArenburg — Nick, Sep 01 '15 at 19:36
`merge(df[,1:2],df[,3:4],by.x="Date",by.y="Date2",all=TRUE)` — scoa, Sep 01 '15 at 19:38
Thanks! @scoa That will work for now but for some reason, when I do that, there are 20 extra rows with after everything matches up. Any idea why? Otherwise, I can definitely work with this in the meantime — Nick, Sep 01 '15 at 19:42
it's probably the NA values from Date2 ; if you are sure that there are no dates from Date2 that don't exist in Date, use `all.x=TRUE` rather than `all=TRUE` and that should do it — scoa, Sep 01 '15 at 19:44
Just a word of caution here: As per de-facto standard, data frames are usually supposed to have [independent] observations in each row and variables in each column. By doing what you want to do, you'd be messing the observations all together. If they represent different things, I'd suggest you put them into different data frames (`df1<-df[,1:2]; df2<-df[,3:4]`). Of course, I may be wrong and you could have a very clear reason to have them the way you do... — PavoDive, Sep 01 '15 at 20:13
You need a `merge`, not a `cbind` and you need to put these data frames into a list using `mget` and `ls` combination. See [here](http://stackoverflow.com/questions/8091303/simultaneously-merge-multiple-data-frames-in-a-list) how to take if from there — David Arenburg, Sep 01 '15 at 20:50

score 0 · Answer 1 · answered Sep 01 '15 at 20:55

0

testing5<-merge(df1[,1:2],df2[,1:2],by.x="Date",by.y="Date",all=TRUE)
testing6<-merge(testing5,df3[,1:2],by.x="Date",by.y="Date",all=TRUE)
testing6

answered Sep 01 '15 at 20:55

Nick

833
2
8
11

2

Or you could *not* ignore my comment and make a general solution (for unknown amount of data sets) and just do `Reduce(function(...) merge(..., by = "Date", all = TRUE), mget(ls(pattern = "df\\d+")))` – David Arenburg Sep 01 '15 at 21:07
Or put the data frames in a list and avoid using objects in your global environment. And @Nick could you add a bit of context why this solves your question, that would make the answer more easy to understand. – Paul Hiemstra Sep 01 '15 at 21:26
I am using dygraphs and want to create each of the separate lines in the graph correspond to the same date range, in order to do this, I need to make the dataframes into one – Nick Sep 01 '15 at 21:27

Cbinding Problems

1 Answers1