-1

I have a data frame that has aggregate data over every 5 days(called a week in the dataset).

    > dput(Sample3)
structure(list(Firm = c("ENG", "ENG", "ENG", "ENG", "ENG", "AUS", 
"AUS", "AUS", "AUS", "AUS", "NZ", "NZ", "NZ", "NZ", "NZ"), Week = c(1, 
2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, 3, 4, 5), TotalSales = c(200, 
155, 195, 150, 155, 140, 145, 150, 155, 160, 120, 125, 130, 135, 
140)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-15L), .Names = c("Firm", "Week", "TotalSales"))

I want to join it with another data frame that has daily output values for three production departments AP,BG, and CK.

    > dput(Sample2)
structure(list(Firm = c("ENG", "ENG", "ENG", "ENG", "ENG", "ENG", 
"ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", 
"ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", 
"ENG", "ENG", "ENG", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", 
"NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", 
"NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ"), Date = structure(c(1451865600, 
1451952000, 1452038400, 1452124800, 1452211200, 1452470400, 1452556800, 
1452643200, 1452729600, 1452816000, 1453075200, 1453161600, 1453248000, 
1453334400, 1453420800, 1453680000, 1453852800, 1453939200, 1454025600, 
1454284800, 1454371200, 1454457600, 1454544000, 1454630400, 1454889600, 
1454976000, 1455062400, 1451865600, 1451952000, 1452038400, 1452124800, 
1452211200, 1452470400, 1452556800, 1452643200, 1452729600, 1452816000, 
1453075200, 1453161600, 1453248000, 1453334400, 1453420800, 1453680000, 
1453852800, 1453939200, 1454025600, 1454284800, 1454371200, 1454457600, 
1454544000, 1454630400, 1454889600, 1454976000, 1455062400, 1451865600, 
1451952000, 1452038400, 1452124800, 1452211200, 1452470400, 1452556800, 
1452643200, 1452729600, 1452816000, 1453075200, 1453161600, 1453248000, 
1453334400, 1453420800, 1453680000, 1453852800, 1453939200, 1454025600, 
1454284800, 1454371200, 1454457600, 1454544000, 1454630400, 1454889600, 
1454976000, 1455062400), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    AP = c(75451.0611, 70753.7178, 72662.012, 77799.73, 79414.4391, 
    81469.5286, 81029.1539, 82203.4883, 90183.0465, 91850.0159, 
    92516.8063, 94350.4702, 91683.3215, 95017.2605, 97184.3196, 
    97684.4091, 98184.5051, 99017.9898, 100851.6537, 99684.7738, 
    99518.0794, 100351.5641, 99684.7738, 99518.0794, 100018.1689, 
    101185.0489, 103852.1975, 81469.5286, 81029.1539, 82203.4883, 
    90183.0465, 91850.0159, 104852.383, 106019.2565, 100518.2585, 
    98017.8043, 108686.4052, 81469.5286, 81029.1539, 82203.4883, 
    90183.0465, 91850.0159, 110686.7762, 111853.6497, 115354.2831, 
    115854.379, 116687.8638, 104852.383, 106019.2565, 100518.2585, 
    98017.8043, 81469.5286, 81029.1539, 82203.4883, 75451.0611, 
    70753.7178, 72662.012, 77799.73, 79414.4391, 81469.5286, 
    81029.1539, 82203.4883, 90183.0465, 91850.0159, 92516.8063, 
    94350.4702, 91683.3215, 95017.2605, 97184.3196, 97684.4091, 
    98184.5051, 99017.9898, 100851.6537, 99684.7738, 99518.0794, 
    100351.5641, 99684.7738, 99518.0794, 100018.1689, 101185.0489, 
    103852.1975), BG = c(104852.383, 106019.2565, 100518.2585, 
    98017.8043, 108686.4052, 114854.1999, 116021.0734, 117354.6541, 
    113020.536, 114020.7151, 110686.7762, 111853.6497, 115354.2831, 
    115854.379, 116687.8638, 117021.2526, 115187.5887, 114354.104, 
    113520.6192, 109853.2914, 109186.5011, 107686.226, 107352.8372, 
    106852.7413, 116687.8638, 117021.2526, 115187.5887, 75451.0611, 
    70753.7178, 72662.012, 77799.73, 79414.4391, 81469.5286, 
    81029.1539, 82203.4883, 90183.0465, 91850.0159, 92516.8063, 
    94350.4702, 91683.3215, 95017.2605, 97184.3196, 97684.4091, 
    98184.5051, 99017.9898, 100851.6537, 99684.7738, 99518.0794, 
    100351.5641, 99684.7738, 99518.0794, 100018.1689, 101185.0489, 
    103852.1975, 81469.5286, 81029.1539, 82203.4883, 90183.0465, 
    91850.0159, 104852.383, 106019.2565, 100518.2585, 98017.8043, 
    108686.4052, 81469.5286, 81029.1539, 82203.4883, 90183.0465, 
    91850.0159, 110686.7762, 111853.6497, 115354.2831, 115854.379, 
    116687.8638, 104852.383, 106019.2565, 100518.2585, 98017.8043, 
    81469.5286, 81029.1539, 82203.4883), CK = c(81469.5286, 81029.1539, 
    82203.4883, 90183.0465, 91850.0159, 104852.383, 106019.2565, 
    100518.2585, 98017.8043, 108686.4052, 81469.5286, 81029.1539, 
    82203.4883, 90183.0465, 91850.0159, 110686.7762, 111853.6497, 
    115354.2831, 115854.379, 116687.8638, 104852.383, 106019.2565, 
    100518.2585, 98017.8043, 81469.5286, 81029.1539, 82203.4883, 
    104852.383, 106019.2565, 100518.2585, 98017.8043, 108686.4052, 
    114854.1999, 116021.0734, 117354.6541, 113020.536, 114020.7151, 
    110686.7762, 111853.6497, 115354.2831, 115854.379, 116687.8638, 
    117021.2526, 115187.5887, 114354.104, 113520.6192, 109853.2914, 
    109186.5011, 107686.226, 107352.8372, 106852.7413, 116687.8638, 
    117021.2526, 115187.5887, 75451.0611, 70753.7178, 72662.012, 
    77799.73, 79414.4391, 81469.5286, 81029.1539, 82203.4883, 
    90183.0465, 91850.0159, 92516.8063, 94350.4702, 91683.3215, 
    95017.2605, 97184.3196, 97684.4091, 98184.5051, 99017.9898, 
    100851.6537, 99684.7738, 99518.0794, 100351.5641, 99684.7738, 
    99518.0794, 100018.1689, 101185.0489, 103852.1975), Week = c(1, 
    1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 
    5, 5, 5, 5, 5, 6, 6, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 
    3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 6, 6, 1, 1, 1, 1, 
    1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 
    5, 5, 6, 6)), .Names = c("Firm", "Date", "AP", "BG", "CK", 
"Week"), row.names = c(NA, -81L), class = c("tbl_df", "tbl", 
"data.frame"))

I want to make a data frame that looks like this 

    > dput(SampleFinal)
structure(list(Firm = c("ENG", "ENG", "ENG", "ENG", "ENG", "ENG", 
"ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", 
"ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", "ENG", 
"ENG", "ENG", "ENG", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", 
"NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", 
"NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ", "NZ"), Date = structure(c(1451865600, 
1451952000, 1452038400, 1452124800, 1452211200, 1452470400, 1452556800, 
1452643200, 1452729600, 1452816000, 1453075200, 1453161600, 1453248000, 
1453334400, 1453420800, 1453680000, 1453852800, 1453939200, 1454025600, 
1454284800, 1454371200, 1454457600, 1454544000, 1454630400, 1454889600, 
1454976000, 1455062400, 1451865600, 1451952000, 1452038400, 1452124800, 
1452211200, 1452470400, 1452556800, 1452643200, 1452729600, 1452816000, 
1453075200, 1453161600, 1453248000, 1453334400, 1453420800, 1453680000, 
1453852800, 1453939200, 1454025600, 1454284800, 1454371200, 1454457600, 
1454544000, 1454630400, 1454889600, 1454976000, 1455062400, 1451865600, 
1451952000, 1452038400, 1452124800, 1452211200, 1452470400, 1452556800, 
1452643200, 1452729600, 1452816000, 1453075200, 1453161600, 1453248000, 
1453334400, 1453420800, 1453680000, 1453852800, 1453939200, 1454025600, 
1454284800, 1454371200, 1454457600, 1454544000, 1454630400, 1454889600, 
1454976000, 1455062400), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    Week = c("1", "1", "1", "1", "1", "2", "2", "2", "2", "2", 
    "3", "3", "3", "3", "3", "4", "4", "4", "4", "4", "5", "5", 
    "5", "5", "5", "NA", "NA", "1", "1", "1", "1", "1", "2", 
    "2", "2", "2", "2", "3", "3", "3", "3", "3", "4", "4", "4", 
    "4", "4", "5", "5", "5", "5", "5", "NA", "NA", "1", "1", 
    "1", "1", "1", "2", "2", "2", "2", "2", "3", "3", "3", "3", 
    "3", "4", "4", "4", "4", "4", "5", "5", "5", "5", "5", "NA", 
    "NA"), TotalSales = c("200", "200", "200", "200", "200", 
    "155", "155", "155", "155", "155", "195", "195", "195", "195", 
    "195", "150", "150", "150", "150", "150", "155", "155", "155", 
    "155", "155", "NA", "NA", "140", "140", "140", "140", "140", 
    "145", "145", "145", "145", "145", "150", "150", "150", "150", 
    "150", "155", "155", "155", "155", "155", "160", "160", "160", 
    "160", "160", "NA", "NA", "120", "120", "120", "120", "120", 
    "125", "125", "125", "125", "125", "130", "130", "130", "130", 
    "130", "135", "135", "135", "135", "135", "140", "140", "140", 
    "140", "140", "NA", "NA"), AP = c("75451.061100000006", "70753.717799999999", 
    "72662.012000000002", "77799.73", "79414.439100000003", "81469.528600000005", 
    "81029.153900000005", "82203.488299999997", "90183.046499999997", 
    "91850.015899999999", "92516.806299999997", "94350.470199999996", 
    "91683.321500000005", "95017.260500000004", "97184.319600000003", 
    "97684.409100000004", "98184.505099999995", "99017.989799999996", 
    "100851.6537", "99684.773799999995", "99518.079400000002", 
    "100351.5641", "99684.773799999995", "99518.079400000002", 
    "100018.1689", "NA", "NA", "81469.528600000005", "81029.153900000005", 
    "82203.488299999997", "90183.046499999997", "91850.015899999999", 
    "104852.383", "106019.2565", "100518.2585", "98017.804300000003", 
    "108686.40519999999", "81469.528600000005", "81029.153900000005", 
    "82203.488299999997", "90183.046499999997", "91850.015899999999", 
    "110686.77619999999", "111853.64969999999", "115354.2831", 
    "115854.379", "116687.86380000001", "104852.383", "106019.2565", 
    "100518.2585", "98017.804300000003", "81469.528600000005", 
    "NA", "NA", "75451.061100000006", "70753.717799999999", "72662.012000000002", 
    "77799.73", "79414.439100000003", "81469.528600000005", "81029.153900000005", 
    "82203.488299999997", "90183.046499999997", "91850.015899999999", 
    "92516.806299999997", "94350.470199999996", "91683.321500000005", 
    "95017.260500000004", "97184.319600000003", "97684.409100000004", 
    "98184.505099999995", "99017.989799999996", "100851.6537", 
    "99684.773799999995", "99518.079400000002", "100351.5641", 
    "99684.773799999995", "99518.079400000002", "100018.1689", 
    "NA", "NA"), BG = c("104852.383", "106019.2565", "100518.2585", 
    "98017.804300000003", "108686.40519999999", "114854.19990000001", 
    "116021.07339999999", "117354.6541", "113020.53599999999", 
    "114020.7151", "110686.77619999999", "111853.64969999999", 
    "115354.2831", "115854.379", "116687.86380000001", "117021.25260000001", 
    "115187.58869999999", "114354.10400000001", "113520.6192", 
    "109853.2914", "109186.50109999999", "107686.226", "107352.83719999999", 
    "106852.74129999999", "116687.86380000001", "NA", "NA", "75451.061100000006", 
    "70753.717799999999", "72662.012000000002", "77799.73", "79414.439100000003", 
    "81469.528600000005", "81029.153900000005", "82203.488299999997", 
    "90183.046499999997", "91850.015899999999", "92516.806299999997", 
    "94350.470199999996", "91683.321500000005", "95017.260500000004", 
    "97184.319600000003", "97684.409100000004", "98184.505099999995", 
    "99017.989799999996", "100851.6537", "99684.773799999995", 
    "99518.079400000002", "100351.5641", "99684.773799999995", 
    "99518.079400000002", "100018.1689", "NA", "NA", "81469.528600000005", 
    "81029.153900000005", "82203.488299999997", "90183.046499999997", 
    "91850.015899999999", "104852.383", "106019.2565", "100518.2585", 
    "98017.804300000003", "108686.40519999999", "81469.528600000005", 
    "81029.153900000005", "82203.488299999997", "90183.046499999997", 
    "91850.015899999999", "110686.77619999999", "111853.64969999999", 
    "115354.2831", "115854.379", "116687.86380000001", "104852.383", 
    "106019.2565", "100518.2585", "98017.804300000003", "81469.528600000005", 
    "NA", "NA"), CK = c("81469.528600000005", "81029.153900000005", 
    "82203.488299999997", "90183.046499999997", "91850.015899999999", 
    "104852.383", "106019.2565", "100518.2585", "98017.804300000003", 
    "108686.40519999999", "81469.528600000005", "81029.153900000005", 
    "82203.488299999997", "90183.046499999997", "91850.015899999999", 
    "110686.77619999999", "111853.64969999999", "115354.2831", 
    "115854.379", "116687.86380000001", "104852.383", "106019.2565", 
    "100518.2585", "98017.804300000003", "81469.528600000005", 
    "NA", "NA", "104852.383", "106019.2565", "100518.2585", "98017.804300000003", 
    "108686.40519999999", "114854.19990000001", "116021.07339999999", 
    "117354.6541", "113020.53599999999", "114020.7151", "110686.77619999999", 
    "111853.64969999999", "115354.2831", "115854.379", "116687.86380000001", 
    "117021.25260000001", "115187.58869999999", "114354.10400000001", 
    "113520.6192", "109853.2914", "109186.50109999999", "107686.226", 
    "107352.83719999999", "106852.74129999999", "116687.86380000001", 
    "NA", "NA", "75451.061100000006", "70753.717799999999", "72662.012000000002", 
    "77799.73", "79414.439100000003", "81469.528600000005", "81029.153900000005", 
    "82203.488299999997", "90183.046499999997", "91850.015899999999", 
    "92516.806299999997", "94350.470199999996", "91683.321500000005", 
    "95017.260500000004", "97184.319600000003", "97684.409100000004", 
    "98184.505099999995", "99017.989799999996", "100851.6537", 
    "99684.773799999995", "99518.079400000002", "100351.5641", 
    "99684.773799999995", "99518.079400000002", "100018.1689", 
    "NA", "NA")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-81L), .Names = c("Firm", "Date", "Week", "TotalSales", "AP", 
"BG", "CK"))

It has the sales value for every week repeated for 5 date values for each firm. New data frame should have all other columns in Sample2.

Also suggest how to make a data frame when every fifth date value of data frame(Sample2) is matched with weekly sales value from data frame(Sample3). The data frames are large so I have given the required links. Please suggest solutions without Data.table package. Thank you very much.

Kindly do not ignore the panel setting of the question before commenting.

Polar Bear
  • 731
  • 1
  • 7
  • 21
  • 1
    Just some version of `dplyr::right_join(Sample3, Sample2)`, no? – alistaire May 19 '16 at 03:49
  • @alistaire thank you very much. Its working. Please write an answer so I can accept it. – Polar Bear May 19 '16 at 04:38
  • 1
    @PolarBear I've investigated your question, and there are two aspects of `SampleFinal` that cause it to differ from what you'd get under a normal merge, whether using base R, dplyr, or any other merge function: (1) The columns `Week`, `TotalSales`, `AP`, `BG`, and `CK` are all character vectors, rather than numeric. I would expect these to be numeric, as they are in the inputs `Sample2` and `Sample3`. (2) In the last two days 2016-02-09 and 2016-02-10, you have NAs in all columns except `Firm` and `Date`, while a merge would populate them (except for `TotalSales`, which fails to join). – bgoldst May 19 '16 at 05:41
  • Can you clarify if these two aspects of `SampleFinal` are desired, or unintentional? – bgoldst May 19 '16 at 05:43
  • 1:No. The columns you mentioned should be numeric and I forgot to correct their classes before using dput(). 2: Yes "NAs in all columns except Firm and Date" are intentionally left blank. As I have some week data missing for some dates in the end for sample2. Thanks for understanding what I am trying to do. – Polar Bear May 19 '16 at 06:00
  • merge() often mess up row orders (see:http://www.r-statistics.com/2012/01/merging-two-data-frame-objects-while-preserving-the-rows-order/). And altering row order makes the final dataset useless. – Polar Bear May 19 '16 at 06:16

1 Answers1

1

We can use the base R merge() function, passing the appropriate "all" argument (all.x with Sample2 as x) to preserve keys that fail to join with Sample3.

We can then achieve your idiosyncratic requirement by explicitly assigning NA to the cells that you want to nullify after the merge.

res <- merge(Sample2,Sample3,all.x=T);
res[is.na(res$TotalSales),c('Week','AP','BG','CK')] <- NA;
res;
##    Firm Week       Date        AP        BG        CK TotalSales
## 1   AUS    1 2016-01-06  82203.49  72662.01 100518.26        140
## 2   AUS    1 2016-01-07  90183.05  77799.73  98017.80        140
## 3   AUS    1 2016-01-05  81029.15  70753.72 106019.26        140
## 4   AUS    1 2016-01-08  91850.02  79414.44 108686.41        140
## 5   AUS    1 2016-01-04  81469.53  75451.06 104852.38        140
## 6   AUS    2 2016-01-13 100518.26  82203.49 117354.65        145
## 7   AUS    2 2016-01-11 104852.38  81469.53 114854.20        145
## 8   AUS    2 2016-01-14  98017.80  90183.05 113020.54        145
## 9   AUS    2 2016-01-15 108686.41  91850.02 114020.72        145
## 10  AUS    2 2016-01-12 106019.26  81029.15 116021.07        145
## 11  AUS    3 2016-01-19  81029.15  94350.47 111853.65        150
## 12  AUS    3 2016-01-22  91850.02  97184.32 116687.86        150
## 13  AUS    3 2016-01-20  82203.49  91683.32 115354.28        150
## 14  AUS    3 2016-01-18  81469.53  92516.81 110686.78        150
## 15  AUS    3 2016-01-21  90183.05  95017.26 115854.38        150
## 16  AUS    4 2016-01-27 111853.65  98184.51 115187.59        155
## 17  AUS    4 2016-01-28 115354.28  99017.99 114354.10        155
## 18  AUS    4 2016-01-29 115854.38 100851.65 113520.62        155
## 19  AUS    4 2016-01-25 110686.78  97684.41 117021.25        155
## 20  AUS    4 2016-02-01 116687.86  99684.77 109853.29        155
## 21  AUS    5 2016-02-02 104852.38  99518.08 109186.50        160
## 22  AUS    5 2016-02-08  81469.53 100018.17 116687.86        160
## 23  AUS    5 2016-02-03 106019.26 100351.56 107686.23        160
## 24  AUS    5 2016-02-04 100518.26  99684.77 107352.84        160
## 25  AUS    5 2016-02-05  98017.80  99518.08 106852.74        160
## 26  AUS   NA 2016-02-10        NA        NA        NA         NA
## 27  AUS   NA 2016-02-09        NA        NA        NA         NA
## 28  ENG    1 2016-01-07  77799.73  98017.80  90183.05        200
## 29  ENG    1 2016-01-06  72662.01 100518.26  82203.49        200
## 30  ENG    1 2016-01-08  79414.44 108686.41  91850.02        200
## 31  ENG    1 2016-01-04  75451.06 104852.38  81469.53        200
## 32  ENG    1 2016-01-05  70753.72 106019.26  81029.15        200
## 33  ENG    2 2016-01-11  81469.53 114854.20 104852.38        155
## 34  ENG    2 2016-01-14  90183.05 113020.54  98017.80        155
## 35  ENG    2 2016-01-15  91850.02 114020.72 108686.41        155
## 36  ENG    2 2016-01-12  81029.15 116021.07 106019.26        155
## 37  ENG    2 2016-01-13  82203.49 117354.65 100518.26        155
## 38  ENG    3 2016-01-22  97184.32 116687.86  91850.02        195
## 39  ENG    3 2016-01-20  91683.32 115354.28  82203.49        195
## 40  ENG    3 2016-01-18  92516.81 110686.78  81469.53        195
## 41  ENG    3 2016-01-19  94350.47 111853.65  81029.15        195
## 42  ENG    3 2016-01-21  95017.26 115854.38  90183.05        195
## 43  ENG    4 2016-01-27  98184.51 115187.59 111853.65        150
## 44  ENG    4 2016-01-28  99017.99 114354.10 115354.28        150
## 45  ENG    4 2016-01-29 100851.65 113520.62 115854.38        150
## 46  ENG    4 2016-01-25  97684.41 117021.25 110686.78        150
## 47  ENG    4 2016-02-01  99684.77 109853.29 116687.86        150
## 48  ENG    5 2016-02-03 100351.56 107686.23 106019.26        155
## 49  ENG    5 2016-02-04  99684.77 107352.84 100518.26        155
## 50  ENG    5 2016-02-02  99518.08 109186.50 104852.38        155
## 51  ENG    5 2016-02-05  99518.08 106852.74  98017.80        155
## 52  ENG    5 2016-02-08 100018.17 116687.86  81469.53        155
## 53  ENG   NA 2016-02-10        NA        NA        NA         NA
## 54  ENG   NA 2016-02-09        NA        NA        NA         NA
## 55   NZ    1 2016-01-05  70753.72  81029.15  70753.72        120
## 56   NZ    1 2016-01-06  72662.01  82203.49  72662.01        120
## 57   NZ    1 2016-01-07  77799.73  90183.05  77799.73        120
## 58   NZ    1 2016-01-04  75451.06  81469.53  75451.06        120
## 59   NZ    1 2016-01-08  79414.44  91850.02  79414.44        120
## 60   NZ    2 2016-01-12  81029.15 106019.26  81029.15        125
## 61   NZ    2 2016-01-13  82203.49 100518.26  82203.49        125
## 62   NZ    2 2016-01-11  81469.53 104852.38  81469.53        125
## 63   NZ    2 2016-01-14  90183.05  98017.80  90183.05        125
## 64   NZ    2 2016-01-15  91850.02 108686.41  91850.02        125
## 65   NZ    3 2016-01-18  92516.81  81469.53  92516.81        130
## 66   NZ    3 2016-01-19  94350.47  81029.15  94350.47        130
## 67   NZ    3 2016-01-22  97184.32  91850.02  97184.32        130
## 68   NZ    3 2016-01-20  91683.32  82203.49  91683.32        130
## 69   NZ    3 2016-01-21  95017.26  90183.05  95017.26        130
## 70   NZ    4 2016-01-25  97684.41 110686.78  97684.41        135
## 71   NZ    4 2016-01-27  98184.51 111853.65  98184.51        135
## 72   NZ    4 2016-02-01  99684.77 116687.86  99684.77        135
## 73   NZ    4 2016-01-28  99017.99 115354.28  99017.99        135
## 74   NZ    4 2016-01-29 100851.65 115854.38 100851.65        135
## 75   NZ    5 2016-02-02  99518.08 104852.38  99518.08        140
## 76   NZ    5 2016-02-05  99518.08  98017.80  99518.08        140
## 77   NZ    5 2016-02-08 100018.17  81469.53 100018.17        140
## 78   NZ    5 2016-02-03 100351.56 106019.26 100351.56        140
## 79   NZ    5 2016-02-04  99684.77 100518.26  99684.77        140
## 80   NZ   NA 2016-02-10        NA        NA        NA         NA
## 81   NZ   NA 2016-02-09        NA        NA        NA         NA

We can fix up the row order afterward manually, and fix the row names too if you want:

res <- res[order(match(res$Firm,unique(Sample3$Firm)),res$Date),];
rownames(res) <- NULL;
res;
##    Firm Week       Date        AP        BG        CK TotalSales
## 1   ENG    1 2016-01-04  75451.06 104852.38  81469.53        200
## 2   ENG    1 2016-01-05  70753.72 106019.26  81029.15        200
## 3   ENG    1 2016-01-06  72662.01 100518.26  82203.49        200
## 4   ENG    1 2016-01-07  77799.73  98017.80  90183.05        200
## 5   ENG    1 2016-01-08  79414.44 108686.41  91850.02        200
## 6   ENG    2 2016-01-11  81469.53 114854.20 104852.38        155
## 7   ENG    2 2016-01-12  81029.15 116021.07 106019.26        155
## 8   ENG    2 2016-01-13  82203.49 117354.65 100518.26        155
## 9   ENG    2 2016-01-14  90183.05 113020.54  98017.80        155
## 10  ENG    2 2016-01-15  91850.02 114020.72 108686.41        155
## 11  ENG    3 2016-01-18  92516.81 110686.78  81469.53        195
## 12  ENG    3 2016-01-19  94350.47 111853.65  81029.15        195
## 13  ENG    3 2016-01-20  91683.32 115354.28  82203.49        195
## 14  ENG    3 2016-01-21  95017.26 115854.38  90183.05        195
## 15  ENG    3 2016-01-22  97184.32 116687.86  91850.02        195
## 16  ENG    4 2016-01-25  97684.41 117021.25 110686.78        150
## 17  ENG    4 2016-01-27  98184.51 115187.59 111853.65        150
## 18  ENG    4 2016-01-28  99017.99 114354.10 115354.28        150
## 19  ENG    4 2016-01-29 100851.65 113520.62 115854.38        150
## 20  ENG    4 2016-02-01  99684.77 109853.29 116687.86        150
## 21  ENG    5 2016-02-02  99518.08 109186.50 104852.38        155
## 22  ENG    5 2016-02-03 100351.56 107686.23 106019.26        155
## 23  ENG    5 2016-02-04  99684.77 107352.84 100518.26        155
## 24  ENG    5 2016-02-05  99518.08 106852.74  98017.80        155
## 25  ENG    5 2016-02-08 100018.17 116687.86  81469.53        155
## 26  ENG   NA 2016-02-09        NA        NA        NA         NA
## 27  ENG   NA 2016-02-10        NA        NA        NA         NA
## 28  AUS    1 2016-01-04  81469.53  75451.06 104852.38        140
## 29  AUS    1 2016-01-05  81029.15  70753.72 106019.26        140
## 30  AUS    1 2016-01-06  82203.49  72662.01 100518.26        140
## 31  AUS    1 2016-01-07  90183.05  77799.73  98017.80        140
## 32  AUS    1 2016-01-08  91850.02  79414.44 108686.41        140
## 33  AUS    2 2016-01-11 104852.38  81469.53 114854.20        145
## 34  AUS    2 2016-01-12 106019.26  81029.15 116021.07        145
## 35  AUS    2 2016-01-13 100518.26  82203.49 117354.65        145
## 36  AUS    2 2016-01-14  98017.80  90183.05 113020.54        145
## 37  AUS    2 2016-01-15 108686.41  91850.02 114020.72        145
## 38  AUS    3 2016-01-18  81469.53  92516.81 110686.78        150
## 39  AUS    3 2016-01-19  81029.15  94350.47 111853.65        150
## 40  AUS    3 2016-01-20  82203.49  91683.32 115354.28        150
## 41  AUS    3 2016-01-21  90183.05  95017.26 115854.38        150
## 42  AUS    3 2016-01-22  91850.02  97184.32 116687.86        150
## 43  AUS    4 2016-01-25 110686.78  97684.41 117021.25        155
## 44  AUS    4 2016-01-27 111853.65  98184.51 115187.59        155
## 45  AUS    4 2016-01-28 115354.28  99017.99 114354.10        155
## 46  AUS    4 2016-01-29 115854.38 100851.65 113520.62        155
## 47  AUS    4 2016-02-01 116687.86  99684.77 109853.29        155
## 48  AUS    5 2016-02-02 104852.38  99518.08 109186.50        160
## 49  AUS    5 2016-02-03 106019.26 100351.56 107686.23        160
## 50  AUS    5 2016-02-04 100518.26  99684.77 107352.84        160
## 51  AUS    5 2016-02-05  98017.80  99518.08 106852.74        160
## 52  AUS    5 2016-02-08  81469.53 100018.17 116687.86        160
## 53  AUS   NA 2016-02-09        NA        NA        NA         NA
## 54  AUS   NA 2016-02-10        NA        NA        NA         NA
## 55   NZ    1 2016-01-04  75451.06  81469.53  75451.06        120
## 56   NZ    1 2016-01-05  70753.72  81029.15  70753.72        120
## 57   NZ    1 2016-01-06  72662.01  82203.49  72662.01        120
## 58   NZ    1 2016-01-07  77799.73  90183.05  77799.73        120
## 59   NZ    1 2016-01-08  79414.44  91850.02  79414.44        120
## 60   NZ    2 2016-01-11  81469.53 104852.38  81469.53        125
## 61   NZ    2 2016-01-12  81029.15 106019.26  81029.15        125
## 62   NZ    2 2016-01-13  82203.49 100518.26  82203.49        125
## 63   NZ    2 2016-01-14  90183.05  98017.80  90183.05        125
## 64   NZ    2 2016-01-15  91850.02 108686.41  91850.02        125
## 65   NZ    3 2016-01-18  92516.81  81469.53  92516.81        130
## 66   NZ    3 2016-01-19  94350.47  81029.15  94350.47        130
## 67   NZ    3 2016-01-20  91683.32  82203.49  91683.32        130
## 68   NZ    3 2016-01-21  95017.26  90183.05  95017.26        130
## 69   NZ    3 2016-01-22  97184.32  91850.02  97184.32        130
## 70   NZ    4 2016-01-25  97684.41 110686.78  97684.41        135
## 71   NZ    4 2016-01-27  98184.51 111853.65  98184.51        135
## 72   NZ    4 2016-01-28  99017.99 115354.28  99017.99        135
## 73   NZ    4 2016-01-29 100851.65 115854.38 100851.65        135
## 74   NZ    4 2016-02-01  99684.77 116687.86  99684.77        135
## 75   NZ    5 2016-02-02  99518.08 104852.38  99518.08        140
## 76   NZ    5 2016-02-03 100351.56 106019.26 100351.56        140
## 77   NZ    5 2016-02-04  99684.77 100518.26  99684.77        140
## 78   NZ    5 2016-02-05  99518.08  98017.80  99518.08        140
## 79   NZ    5 2016-02-08 100018.17  81469.53 100018.17        140
## 80   NZ   NA 2016-02-09        NA        NA        NA         NA
## 81   NZ   NA 2016-02-10        NA        NA        NA         NA

Proof of equivalence:

identical(res,transform(as.data.frame(SampleFinal),Week=as.double(replace(Week,Week=='NA',NA)),TotalSales=as.double(replace(TotalSales,TotalSales=='NA',NA)),AP=as.double(replace(AP,AP=='NA',NA)),BG=as.double(replace(BG,BG=='NA',NA)),CK=as.double(replace(CK,CK=='NA',NA)))[,match(names(res),names(SampleFinal))]);
## [1] TRUE
bgoldst
  • 34,190
  • 6
  • 38
  • 64