CODEWITHSUNDEEP
rdplyr
ArzaanK
ArzaanK

Reputation: 195

dplyr dynamically generate column to mutate

I have a dataframe which has a column for a date, another column for an amount, and various columns that contain years. I want to put the values from the amount column into the year column that corresponds to year in the date column. For example, 

ID    Date         Amount        2010    2011    2012
01    2010/05/05   200           200
02    2011/05/05   300                   300
03    2012/05/05   400                           400

Is there a way I can dynamically choose which column to mutate based on the value of the date column?

Upvotes: 0

Views: 440

Answers (2)

Uwe
Uwe

Reputation: 42592

For the sake of completeness, there is also a "one-liner" using data.table:

library(data.table)
dcast(DF, ID + Date + Amount ~ year(Date), fill = "")

Note that the year columns are computed on-the-fly. So there is no need to create a helper y variable before reshaping.

  ID       Date Amount 2010 2011 2012
1 01 2010-05-05    200  200          
2 02 2011-05-05    300       300     
3 03 2012-05-05    400            400

Data

DF <- structure(list(ID = c("01", "02", "03"), Date = structure(c(14734, 
15099, 15465), class = "Date"), Amount = c(200L, 300L, 400L)), .Names = c("ID", 
"Date", "Amount"), row.names = c(NA, -3L), class = "data.frame")

Upvotes: 1

lebelinoz
lebelinoz

Reputation: 5068

After transforming the date to a year using lubridate's year, you can use spread from the tidyr package to spread your data out:

library(dplyr)
library(tidyr)
df = read.table(text = "ID    Date         Amount
            01    2010/05/05   200        
            02    2011/05/05   300     
            03    2012/05/05   400 ", header= TRUE)

df %>% 
    mutate(y = lubridate::year(Date)) %>% 
    spread(key = y, value = Amount)

The missing data points will be NA's. If you prefer blank strings like in your sample, instead try:

... %>%
    spread(key = y, value = Amount, fill = "")

Note that the resulting dataframe no longer has an Amount column, but you can get it back by merging to the original data.

Upvotes: 4

Related Questions