Make a panel using columns of a dataframe

Question

I have a dataframe in R where the first column represents the client IDs and the other columns are daily dates. For example

id <- seq(1:4)
qqqq141101 <- c(500, 0, 78, 10)
qqqq141102 <- c(500, 0, 78, 10)
frame <- data.frame(id, qqqq141101 , qqqq141102)
frame

I want to make a panel where I would have two dates for each client with corresponding values. Like this

id <- c(1,1,2,2,3,3,4,4)
day <- as.Date(c('2014-11-01', '2014-11-02', '2014-11-01', '2014-11-02','2014-11-01', '2014-11-02','2014-11-01', '2014-11-02'))
value <- c(500,500, 0, 0, 78, 78, 10, 10)
frame <- data.frame(id, day , value)
frame

My actual data consists of over 400 clients and over 100 day variables. I would very much appreciate any help.

Answer 1

Something like this (requires R libraries reshape2 and lubridate).

# Your sample data
id <- seq(1:4)
qqqq141101 <- c(500, 0, 78, 10)
qqqq141102 <- c(500, 0, 78, 10)
frame <- data.frame(id, qqqq141101 , qqqq141102)

# Wide to long dataframe
require(reshape2);
df <- melt(frame, id.vars = "id");

# Get dates from column names
require(lubridate);
df$variable <- ymd(gsub("qqqq", "", df$variable));

# Order by id then date
df <- df[order(df$id, df$variable), ];
df;
#  id   variable value
#1  1 2014-11-01   500
#5  1 2014-11-02   500
#2  2 2014-11-01     0
#6  2 2014-11-02     0
#3  3 2014-11-01    78
#7  3 2014-11-02    78
#4  4 2014-11-01    10
#8  4 2014-11-02    10