Transpose a data frame and add column names as variables in R

December 13, 2021

I have a time series data frame that is wide and has individual stock data as the column names.
I would like to turn this data frame into long format without taking away the ability to see what ticker the data belongs to.

Here is the data below.

df = structure(list(Date = structure(c(1607922000, 1608008400), class = c("POSIXct", 
"POSIXt"), tzone = ""), AAPL.Close = c(0.32982465, 0.34001608
), MSFT.Close = c(0.26307234, 0.27235893), GS.Close = c(0.30742572, 
0.29825025), QQQ.Close = c(0.25350002, 0.24456267)), row.names = 1:2, class = "data.frame")


  Date        AAPL.Close MSFT.Close GS.Close QQQ.Close
1 2020-12-14  0.3298246  0.2630723 0.3074257 0.2535000
2 2020-12-15  0.3400161  0.2723589 0.2982502 0.2445627

I would like the new data frame to look like this.

Date          Data     Ticker
2020-12-14   .3298     AAPL 
2020-12-15   .3400     AAPL
2020-12-14   .260      MSFT
2020-12-15   .27       MSFT
.
.

Thank you for your help

>Solution :

We could use pivot_longer

library(tidyr)
library(dplyr)
df %>% 
   mutate(Date = as.Date(Date)) %>%
   pivot_longer(cols = -Date, names_to = c("Ticker", ".value"), 
       names_sep = "\\.") %>% 
   rename(Data = Close)