Pivot table from column to row in excel or R -

this question has answer here:

• reshape data wide long? [duplicate] 3 answers

i have table header

id x.1960 x.1970 x.1980 x.1990 x.2000 y.1960 y.1970 y.1980 y.1990 y.2000 

i want pivot table

id time x y 

what best way in excel or r?

something using base r reshape:

get data first

test <- read.table(text="id x.1960 x.1970 x.1980 x.1990 x.2000 y.1960 y.1970 y.1980 y.1990 y.2000 1 2 3 4 5 6 7 8 9 10 b 10 20 30 40 50 60 70 80 90 100",header=true) 

then reshape:

reshape(   test,   idvar="id",   varying=list(2:6,7:11),   direction="long",   v.names=c("x","y"),   times=seq(1960,2000,10) ) 

resulting in:

       id time  x   y a.1960  1960  1   6 b.1960  b 1960 10  60 a.1970  1970  2   7 b.1970  b 1970 20  70 a.1980  1980  3   8 b.1980  b 1980 30  80 a.1990  1990  4   9 b.1990  b 1990 40  90 a.2000  2000  5  10 b.2000  b 2000 50 100