Search code examples
rspread

Take maximum values and move into new columns

ID Date      Paid
1  1/1/2006  $500
1  2/5/2010  $300
2  5/3/2013  $600
2  6/7/2018  $700

I want to take the maximum date by ID and spread the information in those rows into new columns:

ID Date      Paid   Max. Date Max. Paid
1  1/1/2006  $500   2/5/2010  $300
2  5/3/2013  $600   6/7/2018  $700

How can I do this?

Solution

  • We can do

    library(dplyr)
df1 %>%  
    mutate(Date = as.Date(Date, '%m/%d/%Y')) %>%
    arrange(ID, Date) %>%
    group_by(ID) %>% 
    summarise(Max.Date = last(Date), Max.Paid = last(Paid), 
          Paid = first(Paid), Date = first(Date))    
# A tibble: 2 x 5
#     ID Max.Date   Max.Paid Paid  Date      
#  <int> <date>     <chr>    <chr> <date>    
#1     1 2010-02-05 $300     $500  2006-01-01
#2     2 2018-06-07 $700     $600  2013-05-03

    data

    df1 <- structure(list(ID = c(1L, 1L, 2L, 2L), Date = c("1/1/2006", "2/5/2010", 
"5/3/2013", "6/7/2018"), Paid = c("$500", "$300", "$600", "$700"
)), class = "data.frame", row.names = c(NA, -4L))