I have a dataframe similar to this
df <- data.frame (month = c("01", "02", "03", "04", "01", "03"),
Year= c("01", "01", "01", "01", "02", "02"),
value= c("12", "20", "34", "18", "22", "27")
)
However I want the columns to be the unique months (ranging from 01-12) and the rows to be years respective values. How do I go about doing this?
There are two steps to this: You describe a table, you can make a table from a data frame using a formula with the ?xtabs function. Unfortunately, I can't get it to use the value column as a 'response'. So I simply replicated each row the number of times given by the value. Consider:
(tab <- xtabs(~Year+month, df[rep(1:6, times=df$value),]))
# month
# Year 01 02 03 04
# 01 1 3 6 2
# 02 4 0 5 0
In your question, you refer to months 1-12, but only 4 are listed in the example. It isn't clear if you want to be able to add extra columns of 0's as well. You can use ?cbind to add a matrix of 0's, and then create names with ?colnames. Consider:
(tab <- cbind(tab, matrix(0, nrow=2, ncol=8)))
# 01 02 03 04
# 01 1 3 6 2 0 0 0 0 0 0 0 0
# 02 4 0 5 0 0 0 0 0 0 0 0 0
colnames(tab) <- c(paste0(0,1:9), "10","11","12")
tab
# 01 02 03 04 05 06 07 08 09 10 11 12
# 01 1 3 6 2 0 0 0 0 0 0 0 0
# 02 4 0 5 0 0 0 0 0 0 0 0 0
