How to bind every n columns one under the other as rows (wide to long)?

Question

Here is a dataset that I have without column names:

ADNAC TI     0.09   ADNAC TI     0.09   ADNAC TI     0.09   ADNAC TI     0.08 
AEFES TI     3.76   AEFES TI     3.48   AEFES TI     3.74   AEFES TI     3.24 
AGHOL TI     0.43   AGHOL TI     0.37   AGHOL TI     0.31   AGHOL TI     0.39 
AGYO TI      0.09   AGYO TI      0.08   AGYO TI      0.08   AGYO TI      0.08 
AKBNK TI     9.21   AKBNK TI     8.30   AKBNK TI     8.26   AKBNK TI     10.57 
AKCNS TI     0.45   AKCNS TI     0.43   AKCNS TI     0.43   AKCNS TI     0.47 
AKENR TI     0.53   AKENR TI     0.46   AKENR TI     0.45   AKENR TI     0.43

Every two columns side by side represent a single day (in this case the string and its respective value). There are 100 rows total and 382 columns, therefore 191 days. What I'd like to do is stack every day one after the other as rows, independent of their day. So I would have 100x191 rows total with just 2 columns instead, the string (stock name) and the corresponding value. Which would look like:

ADNAC TI     0.09 
AEFES TI     3.76 
AGHOL TI     0.43 
AGYO TI      0.09 
AKBNK TI     9.21 
AKCNS TI     0.45 
AKENR TI     0.53 
ADNAC TI     0.09 
AEFES TI     3.48 
AGHOL TI     0.37 
AGYO TI      0.08 
AKBNK TI     8.30 
AKCNS TI     0.43 
AKENR TI     0.46 
ADNAC TI     0.09 
AEFES TI     3.74 
AGHOL TI     0.31 
AGYO TI      0.08 
AKBNK TI     8.26 
AKCNS TI     0.43 
AKENR TI     0.45 
ADNAC TI     0.08 
AEFES TI     3.24 
AGHOL TI     0.39 
AGYO TI      0.08 
AKBNK TI     10.57 
AKCNS TI     0.47 
AKENR TI     0.43

What I've tried so far is to split the data: data_long <- split.default(data_wide, rep(1, each = 2))

And several other unsuccessful attempts of reshape or melt which mostly was a failure due to indexing issues. I tried to do this by assigning A and B for the stock name and value for each day which would go on as B and B1 and so forth. But mixed string and numeric propagating in Excel did not turn out how I expected it to be, so I had to manually do it which is pointless.

Ronak Shah · Accepted Answer · 2020-04-19T14:59:09.853

An easy way would be to use vector recycling to select alternate columns, unlist them to get data as a vector and create a new dataframe.

data.frame(stock = unlist(df[c(TRUE, FALSE)]),value = unlist(df[c(FALSE, TRUE)]), 
            row.names = NULL, stringsAsFactors = FALSE)


#     stock value
#1  ADNACTI  0.09
#2  AEFESTI  3.76
#3  AGHOLTI  0.43
#4   AGYOTI  0.09
#5  AKBNKTI  9.21
#6  AKCNSTI  0.45
#7  AKENRTI  0.53
#8  ADNACTI  0.09
#9  AEFESTI  3.48
#10 AGHOLTI  0.37
#11  AGYOTI  0.08
#12 AKBNKTI  8.30
#13 AKCNSTI  0.43
#14 AKENRTI  0.46

Using split.default we can do :

df[c(TRUE, FALSE)] <- lapply(df[c(TRUE, FALSE)], as.character)
do.call(cbind.data.frame, lapply(split.default(df, c(TRUE, FALSE)), unlist))

data

df <- structure(list(V1 = structure(1:7, .Label = c("ADNACTI", "AEFESTI", 
"AGHOLTI", "AGYOTI", "AKBNKTI", "AKCNSTI", "AKENRTI"), class = "factor"), 
V2 = c(0.09, 3.76, 0.43, 0.09, 9.21, 0.45, 0.53), V3 = structure(1:7, 
.Label = c("ADNACTI","AEFESTI", "AGHOLTI", "AGYOTI", "AKBNKTI", "AKCNSTI", 
"AKENRTI"), class = "factor"), V4 = c(0.09, 3.48, 0.37, 0.08, 8.3, 
0.43, 0.46)), class = "data.frame", row.names = c(NA, -7L))

How to bind every n columns one under the other as rows (wide to long)?

1 Answers1