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.

I mean a panel dataset, where I would have 100 days with their corresponding values for each client. — Shirkhanyan, Nov 27 '17 at 07:59
"Panel dataset" is not a concept/term in R. I guess you want to convert the first dataframe into the second dataframe by converting from wide to long and parsing the date from the column names? — Maurits Evers, Nov 27 '17 at 08:05
@Jaap If I understand OP correctly, it's not exactly a duplicate, as OP wants to reshape and parse dates from column names. Please see my `reshape2`+`lubridate`-based solution below. The post's title and main question are not very informative unfortunately... — Maurits Evers, Nov 27 '17 at 08:12
@MauritsEvers Thank you very much for the advice. I am new here, my apologies for posting a non-informative question. I want to do exactly what you have written. And it worked!! — Shirkhanyan, Nov 27 '17 at 08:28
No problem, glad it worked. Welcome to SO;-) This is a great community, and you might find some useful tips by [taking the tour](https://stackoverflow.com/tour) and reading up on how [best to ask questions](https://stackoverflow.com/help/how-to-ask). — Maurits Evers, Nov 27 '17 at 08:36
Related: [*Reshaping data.frame from wide to long format*](https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format) — Jaap, Nov 27 '17 at 08:45

score 2 · Accepted Answer · answered Nov 27 '17 at 08:09

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

score 0 · Answer 2 · answered Nov 27 '17 at 09:21

You could also solve this with:

library(dplyr)
library(tidyr)
library(anytime)

frame %>% 
  gather(day, value, -1) %>%
  mutate(day = anydate(gsub('qqqq','20',day))) %>% 
  arrange(id)

The result:

  id        day value
1  1 2014-11-01   500
2  1 2014-11-02   500
3  2 2014-11-01     0
4  2 2014-11-02     0
5  3 2014-11-01    78
6  3 2014-11-02    78
7  4 2014-11-01    10
8  4 2014-11-02    10

Make a panel using columns of a dataframe

2 Answers2