You can do what the question asks with the xtabs
after reformatting the data from wide format to long format.
In this solution row.names(df2)
gives the dates.
df2 <- reshape2::melt(df, id.vars = 'LOCATION')
df2 <- xtabs(value ~ variable + LOCATION, df2)
df2 <- as.data.frame.matrix(df2)
# EU28 JPN USA
#jan.1999 86.7 99.76086 90.87153
#fev.1999 85.5 99.76086 91.62469
#mar.1999 85.9 101.47350 91.56094
To have a column with the dates may be
df2 <- cbind.data.frame(Data = row.names(df2), df2)
row.names(df2) <- NULL
df2
# Data EU28 JPN USA
#1 jan.1999 86.7 99.76086 90.87153
#2 fev.1999 85.5 99.76086 91.62469
#3 mar.1999 85.9 101.47350 91.56094
Another way is with the package dplyr
. In the solution below the first column is the column of dates.
library(tidyverse)
df %>%
gather(variable, value, -LOCATION) %>%
xtabs(formula = value ~ variable + LOCATION, data = .) %>%
as.data.frame.matrix() %>%
rownames_to_column(var = "Data")
# Data EU28 JPN USA
#1 fev.1999 85.5 99.76086 91.62469
#2 jan.1999 86.7 99.76086 90.87153
#3 mar.1999 85.9 101.47350 91.56094
Dice.
df <- read.table(text = "
LOCATION 'jan 1999' 'fev 1999' 'mar 1999'
EU28 86.70000 85.50000 85.90000
JPN 99.76086 99.76086 101.47350
USA 90.87153 91.62469 91.56094
", header = TRUE)
Returns the error
Error in dimnames(x) <- dn : 
 length of 'dimnames' [2] not equal to array extent
– Alexandre Sanches
Possibly, R is interpreting the columns separated by space as two columns and not one. Try replacing the pattern 'month year' with 'month year'. I’ll edit my answer to show this
– Lucas
Thanks for the solution!
– Alexandre Sanches