convert data from many rows to many columns in R?

Question

I have data that comes out of a DB in a normalized way with a field for year, state, and value. I would like to do analysis on the data and need it formatted where each year is a field and not a record.So I would like the data where each record is a state and then theres a field for each year and each value for those fields are the value for that year and that state. Is there a command for doing this?

So I have:

State, Year, Value
KY, 1998, 56
KY, 1997, 78
IL, 1998, 48
IL, 1997, 72

and I want:

State, 1997_value, 1998_value
KY, 78, 56
IL, 72, 48

Answer 1

You want to use the reshape() function.

reshape(data, idvar="State", timevar="Year", direction="wide")

Answer 2

Another option is to use the reshape package, created by the inimitable Hadley Wickham:

library(reshape)

tuna<-melt(data,id.vars=c("State","Year"))

cast(tuna,State~Year~variable)

Answer 3

You can even combine the melt and cast lines into one call to the recast function.

ds <- data.frame(State = c("KY", "KY", "IL", "IL"), 
Year = c(1998, 1997, 1998, 1997), 
Value = c(56, 78, 48, 72))

library(reshape)
recast(ds, State ~ Year, id.var = c("State", "Year"))