-2

I have two dataframes that has the following format: 

Date          COL1    COL2  COL3   COL4 
7/1/2015    Company1    0    0       0       
7/2/2015    Company1    1    0       0  
7/3/2015    Company1    0    0       8  
7/4/2015    Company1    0    3       0  
7/1/2015    Company2    0    0       0  
7/2/2015    Company2    5    9       10 
7/3/2015    Company2    0    0       0  
7/4/2015    Company2    1    0       0
7/1/2015    Company3    0    0       0

The 2nd dataframe looks like this: 

   Date       COL1    COL2  COL3   COL4 
7/1/2014    Company1    1    6       4       
7/3/2014    Company1    4    9       5  
7/4/2014    Company1    5    2       6  
7/5/2014    Company1    4    2       8  
7/2/2014    Company2    8    9       32 
7/3/2014    Company2    9    12      8  
7/1/2014    Company3    4    5       6

I want to merge them so that it looks like this in the end:

   Date       COL1    COL2  COL3   COL4     Date       COL1     COL2   COL3   COL4
7/1/2014    Company1    1    6       4    7/1/2015   Company1     0     0      0
7/2/2014    Company1    0    0       0    7/2/2015   Company1     1     0      0
7/3/2014    Company1    4    9       5    7/3/2015   Company1     0     0      8
7/4/2014    Company1    5    2       6    7/4/2015   Company1     0     3      0
7/5/2014    Company1    4    2       8    7/5/2015   Company1     0     0      0
7/1/2014    Company2    0    0       0    7/1/2015   Company2     0     0      0
7/2/2014    Company2    8    9       32   7/2/2015   Company2     5     9     10
7/3/2014    Company2    9    12      8    7/3/2015   Company2     0     0      0
7/4/2014    Company2    0    0       0    7/4/2015   Company2     1     0      0
7/1/2014    Company3    4    5       6    7/1/2015   Company3     0     0      0

So basically I want to merge by Date and Company. The goal is to have the dates lined up even if there are missing data points from one of the dataframes, and replace it with 0's.

If anyone can help it would be greatly appreciated!

Jaap
  • 81,064
  • 34
  • 182
  • 193
Nick
  • 833
  • 2
  • 8
  • 11
  • Your dates are a year apart between the two dataframes. Is that intentional? – A. Webb Jul 30 '15 at 13:12
  • yes, if it weren't a year apart, I know it would be a simple merge but I'm finding it difficult to do this due to the year separation. @A.Webb – Nick Jul 30 '15 at 13:13
  • Create Month and Day columns from Date column, then merge on 3 columns Company,Month,Day. – zx8754 Jul 30 '15 at 13:19
  • 1
    possible duplicate of [How to join (merge) data frames (inner, outer, left, right)?](http://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) – zx8754 Jul 30 '15 at 13:28

1 Answers1

2

You could try something like this:

#parse dates
df1$Date <- as.Date(df1$Date,format="%m/%d/%Y")
df2$Date <- as.Date(df2$Date,format="%m/%d/%Y")

#create day/month column
df1$day_month <- format(df1$Date, "%m/%d")
df2$day_month <- format(df2$Date, "%m/%d")

#merge using day/month and company, all=T will print all rows
merge(df1,df2,by=c("day_month","COL1"),all=T)
NicE
  • 21,165
  • 3
  • 51
  • 68
  • Thanks for your help! The only thing I would want is to sort it by date and then by company. So you would have the dates listed 7/1/14-7/8/14 for one company and then start over again with 7/1/14-7/8/14 for the next company. @NicE – Nick Jul 30 '15 at 13:26
  • Actually @NicE I was able to export the result you gave me into Excel and then just sorted it that way. Saved me man! – Nick Jul 30 '15 at 13:30
  • 2
    Simply swap the order of the `by`. – A. Webb Jul 30 '15 at 13:30