r - Combine columns from one data frame into new data frame, and filter

Question

Material    DocDate    Name  Address    Unit    Price
1258486   3/17/2017   FEHLIG BROS BOX    asd     8.95
1258486   5/11/2017   FEHLIG BROS BOX    asd     9.5
1258486   12/11/2017  FEHLIG BROS_BOX    asd     10.5
1250000   12/20/2017  Krones ALPHA       afg     11.5

I have a above data frame. I need to frame like below based on dates(3/17/2017) appears first. So i need below output

Material         Name/address/Unit Price
1258486     FEHLIG BROS BOX/asd/8.95/9.5/10.5
1250000     Krones/ALPHA/afg/11.5

Answer 1

Here's an alternative using dplyr. First the sample data:

data <- data.frame(stringsAsFactors=FALSE,
                   Material   = c(1258486L, 1258486L),
                   DocDate    = c("3/17/2017", "5/11/2017"),
                   Name       = c("FEHLIG BROS BOX", "FEHLIG BROS BOX"),
                   Address    = c("asd", "asd"),
                   Unit_Price = c(8.95, 9.5))

And then here are one set of steps to get your answer. (BTW, I believe all the solutions provided so far would give you multiple rows of output if there are multiple Material rows that share the same "earliest date." You might want another term like Unit_Price == min(Unit_Price) inside the filter if there's a tie-breaker that makes sense here.)

library(dplyr)
output <- data %>%

  # convert DocDate to a date
  mutate(DocDate = as.Date(DocDate,'%m/%d/%Y')) %>%

  # For each Material...
  group_by(Material) %>% 

  # just keep the line(s) with the first date...
  filter(DocDate == min(DocDate)) %>% ungroup() %>% 

  # and combine fields
  mutate(`Name/address/Unit Price` = paste(Name, Address, Unit_Price, sep = "/")) %>%

  # just the requested columns
  select(Material, `Name/address/Unit Price`)

output
# A tibble: 1 x 2
  Material `Name/address/Unit Price`
     <int> <chr>                    
1  1258486 FEHLIG BROS BOX/asd/8.95 

(EDIT: fixed typos in code)