0

What I have (obviously I'm presenting a very small fraction of my current data):

my_df <- structure(list(X = structure(c(48.75, 49.25), .Dim = 2L), Y = structure(c(17.25, 17.25), .Dim = 2L), Time = structure(c(14625, 14626), .Dim = 2L, class = "Date"), spei = c(-0.460236400365829, -0.625695407390594)), .Names = c("X", "Y", "Time", "spei"), row.names = 1:2, class = "data.frame")

What I need:

new_df <- structure(list(X = structure(c(48.75, 49.25), .Dim = 2L), Y = structure(c(17.25, 17.25), .Dim = 2L), "2010-01-16" = c(-0.460236400365829, NaN), "2010-01-17" = c(NaN, -0.625695407390594)), .Names = c("X", "Y", "2010-01-16", "2010-01-17"), row.names = 1:2, class = "data.frame")

What is the easiest way of doing this? I thought about writing a for loop, but I guess that apply/sapply might help on this?

transformation needed

matandked
  • 1,527
  • 4
  • 26
  • 51

2 Answers2

2

You can use library tidyr and its spread function like this:

library(tidyr)
spread(my_df, Time, spei)
      X     Y 2010-01-16 2010-01-17
1 48.75 17.25 -0.4602364         NA
2 49.25 17.25         NA -0.6256954
Gopala
  • 10,363
  • 7
  • 45
  • 77
  • I like this solution more than reshape. However, is there any parameter to sort columns by date in spread? – matandked Jan 28 '16 at 15:23
  • You can use use `order` or `dplyr` packages `arrange` to pre-sort the data frame on date column and then do spread. – Gopala Jan 28 '16 at 15:30
2

Without any additional packages you could do that with reshape():

reshape(my_df, idvar = c('X', 'Y'), timevar = "Time", direction = 'wide')

Which gives: 

      X     Y spei.2010-01-16 spei.2010-01-17
1 48.75 17.25      -0.4602364              NA
2 49.25 17.25              NA      -0.6256954
abel
  • 470
  • 5
  • 14