0

I have a rather complicated task that I need to carry out so bear with me. I am guessing it will be possible, but let me know if not.

Suppose I have the following data

set.seed(123)
date1 <- c(seq(as.Date("2011-11-1"),as.Date("2012-1-1"),by = "months"),seq(as.Date("2011-12-1"),as.Date("2012-3-1"),by = "months"))
date2 <- c(seq(as.Date("2011-12-1"),as.Date("2012-1-1"),by = "months"),seq(as.Date("2011-11-1"),as.Date("2012-1-1"),by = "months"))
variables <- c(rep("Number of Coins",3),rep("Number of Shoes",4),rep("Number of Coins",2),rep("Number of Shoes",3))
date <- c(date1,date2)
names <- c(rep("Jim",7),rep("Arnold",5))
value <- rnorm(12)
df <- data.frame(names, date, variables, value)

    names       date       variables       value
1     Jim 2011-11-01 Number of Coins -0.56047565
2     Jim 2011-12-01 Number of Coins -0.23017749
3     Jim 2012-01-01 Number of Coins  1.55870831
4     Jim 2011-12-01 Number of Shoes  0.07050839
5     Jim 2012-01-01 Number of Shoes  0.12928774
6     Jim 2012-02-01 Number of Shoes  1.71506499
7     Jim 2012-03-01 Number of Shoes  0.46091621
8  Arnold 2011-12-01 Number of Coins -1.26506123
9  Arnold 2012-01-01 Number of Coins -0.68685285
10 Arnold 2011-11-01 Number of Shoes -0.44566197
11 Arnold 2011-12-01 Number of Shoes  1.22408180
12 Arnold 2012-01-01 Number of Shoes  0.35981383

The problem with this data is that the variable name takes up a column. I would like to create two columns for Number of Shoes and Number of Coins, but I want to make sure the dates stay intact. Ideally I would like turn this data frame into this

    names    date Number.of.Coins Number.of.Shoes
1     Jim 11/1/11      -0.5604756              NA
2     Jim 12/1/11      -0.2301775      0.07050839
3     Jim  1/1/12       1.5587083      0.12928773
4     Jim  2/1/12              NA      1.71506499
5     Jim  3/1/12              NA      0.46091621
6 Arnold  11/1/11              NA     -0.44566197
7  Arnold 12/1/11      -1.2650612      1.22408180
8  Arnold  1/1/12      -0.6868529      0.35981383

So the date range will be the min date for each variable to the max date for each variable. This will create the need for NAs. I want to do this within each name. Hope that makes sense!

theamateurdataanalyst
  • 2,794
  • 4
  • 38
  • 72
  • You can use dcast-melt from reshape2 http://seananderson.ca/2013/10/19/reshape.html – ajkl Jan 07 '15 at 00:54

2 Answers2

2

As suggested @ Ajinkya Kale, you can handle this task using the reshape2 package.

dcast(df, names + date ~ variables, value.var = "value")

If you want to make sure that the order of date is in chronological order, you could use arrange() in the dplyr package.

arrange(dcast(df, names + date ~ variables, value.var = "value"), names, date)

#   names       date Number of Coins Number of Shoes
#1 Arnold 2011-11-01              NA     -0.44566197
#2 Arnold 2011-12-01      -1.2650612      1.22408180
#3 Arnold 2012-01-01      -0.6868529      0.35981383
#4    Jim 2011-11-01      -0.5604756              NA
#5    Jim 2011-12-01      -0.2301775      0.07050839
#6    Jim 2012-01-01       1.5587083      0.12928774
#7    Jim 2012-02-01              NA      1.71506499
#8    Jim 2012-03-01              NA      0.46091621
jazzurro
  • 23,179
  • 35
  • 66
  • 76
0

Another option is to use spread from tidyr

library(tidyr)
spread(df, variables, value)
#   names       date Number of Coins Number of Shoes
#1 Arnold 2011-11-01              NA     -0.44566197
#2 Arnold 2011-12-01      -1.2650612      1.22408180
#3 Arnold 2012-01-01      -0.6868529      0.35981383
#4    Jim 2011-11-01      -0.5604756              NA
#5    Jim 2011-12-01      -0.2301775      0.07050839
#6    Jim 2012-01-01       1.5587083      0.12928774
#7    Jim 2012-02-01              NA      1.71506499
#8    Jim 2012-03-01              NA      0.46091621
akrun
  • 874,273
  • 37
  • 540
  • 662