reshape adding two columns to data.frame

Question

I have a data.frame that looks like this

    timestamp value.x station value.y   parameter.x   value parameter.y
1   1/1/2010  0.6     abc     188,000   AREA PLANTED    22  PROGRESS
2   1/1/2010  0.6     abc     156.3     YIELD           NA  NA
3   1/1/2010  -10     def     188,000   AREA PLANTED    22  PROGRESS
4   1/1/2010  -10     def     156.3     YIELD           NA  NA

And I want to use reshape to make it look like this:

    timestamp   value.x station AREA PLANTED    YIELD   PROGRESS
1   1/1/2010    0.6     abc     188,000         156.3   22       
3   1/1/2010    -10     def     188,000         156.3   22

I tried

reshape(data = b, varying = list(c('value.y', 'parameter.x', 'value', 'parameter.y')), 
        v.names = c('AREA PLANTED', 'YIELD', 'PROGRESS'), 
        timevar = row.names(b), 
        times = b$timestamp, direction = 'wide', idvar = b$station)

But it says

Error in [.data.frame(data, , idvar) : undefined columns selected

I tried changing it a bit, but no matter what I do it keeps throwing this error.

Answer 1

This uses reshape2. I don't think it is possible to cast the dataframe in a single step. Note that it appears that the input is the result of some other join operation (because some of the names have .x and . suffixes). I guess that join could be improved to avoid this complication

df <- read.table(header=TRUE, stringsAsFactors = FALSE, text = 
"timestamp value.x station value.y   parameter.x   value parameter.y
1/1/2010  0.6     abc     188,000   AREAPLANTED    22  PROGRESS
1/1/2010  0.6     abc     156.3     YIELD           NA  NA
1/1/2010  -10     def     188,000   AREAPLANTED    22  PROGRESS
1/1/2010  -10     def     156.3     YIELD           NA  NA
")

library(reshape2)

# extract the last two columns into a variable/value and make unique
df1 <- unique(df[!is.na(df$value),c("timestamp", "value.x", "station", "parameter.y", "value")])
names(df1) <- c("timestamp", "value.x", "station", "variable", "value")

# extract columns 4,5 into a variable value
df2 <- df[,c("timestamp", "value.x", "station", "parameter.x", "value.y")]
names(df2) <- c("timestamp", "value.x", "station", "variable", "value")

# cast
dcast(rbind(df1, df2), timestamp + value.x + station ~ variable, value.var = "value")

#   timestamp value.x station AREAPLANTED PROGRESS YIELD
# 1  1/1/2010   -10.0     def     188,000       22 156.3
# 2  1/1/2010     0.6     abc     188,000       22 156.3

Answer 2

Still in base R, consider a merge between two reshape dataframes as you require. Your current setup uses arguments intended for the wide to long reshape and not vice versa as you need.

mdf <- merge(
  reshape(b, timevar="parameter.x",
        v.names = c("value.y"),
        idvar = c("timestamp", "value.x", "station"),
        direction = "wide",
        drop = c("value", "parameter.y")),

  reshape(b[!is.na(b$value),], timevar="parameter.y",
        v.names = c("value"),
        idvar = c("timestamp", "value.x", "station"),
        direction = "wide",
        drop = c("value.y", "parameter.x")),
  by=c("timestamp", "value.x", "station")
)

names(mdf) <- gsub("(value\\.y\\.|value\\.)", "", names(mdf))

mdf    
#   timestamp     x station AREA PLANTED YIELD PROGRESS
# 1  1/1/2010 -10.0     def      188,000 156.3       22
# 2  1/1/2010   0.6     abc      188,000 156.3       22