Homework 5 - Data Pivoting

Load Libraries

library(tidyverse)
library(dplyr)

Load Stock Data

stock_df <- read_csv("C:/Users/User/Downloads/stock_df.csv")

## Rows: 5 Columns: 106
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr   (1): company
## dbl (105): 2019_week1, 2019_week2, 2019_week3, 2019_week4, 2019_week5, 2019_...
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

# Display the original wide format data
stock_df

## # A tibble: 5 × 106
##   company   `2019_week1` `2019_week2` `2019_week3` `2019_week4` `2019_week5`
##   <chr>            <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
## 1 Amazon          1848.        1641.        1696.        1671.        1626. 
## 2 Apple             73.4         38.1         39.2         39.4         41.6
## 3 Facebook         205.         144.         150.         149.         166. 
## 4 Google          1337.        1057.        1098.        1091.        1111. 
## 5 Microsoft        158.         103.         108.         107.         103. 
## # ℹ 100 more variables: `2019_week6` <dbl>, `2019_week7` <dbl>,
## #   `2019_week8` <dbl>, `2019_week9` <dbl>, `2019_week10` <dbl>,
## #   `2019_week11` <dbl>, `2019_week12` <dbl>, `2019_week13` <dbl>,
## #   `2019_week14` <dbl>, `2019_week15` <dbl>, `2019_week16` <dbl>,
## #   `2019_week17` <dbl>, `2019_week18` <dbl>, `2019_week19` <dbl>,
## #   `2019_week20` <dbl>, `2019_week21` <dbl>, `2019_week22` <dbl>,
## #   `2019_week23` <dbl>, `2019_week24` <dbl>, `2019_week25` <dbl>, …

Pivot Stock Data

Import data stock_df.csv and reshape it from wide to long format (stock_df_long) as in the following:

stock_df_long <- stock_df %>% 
  pivot_longer(cols = !company,
               names_to = c("year", "week"),
               names_sep = "_week",
               names_transform = list(year = as.integer, week = as.integer),
               values_to = "price")

# Display the pivoted long format data
stock_df_long

## # A tibble: 525 × 4
##    company  year  week price
##    <chr>   <int> <int> <dbl>
##  1 Amazon   2019     1 1848.
##  2 Amazon   2019     2 1641.
##  3 Amazon   2019     3 1696.
##  4 Amazon   2019     4 1671.
##  5 Amazon   2019     5 1626.
##  6 Amazon   2019     6 1588.
##  7 Amazon   2019     7 1608.
##  8 Amazon   2019     8 1632.
##  9 Amazon   2019     9 1672.
## 10 Amazon   2019    10 1621.
## # ℹ 515 more rows

Explanation

The pivot_longer() function reshapes the data:

• cols = !company: Pivot all columns except the company column
• names_to = c("year", "week"): Split column names into two new columns (year and week)
• names_sep = "_week": Use “_week” as the separator between year and week
• names_transform = list(year = as.integer, week = as.integer): Convert year and week to integers
• values_to = "price": Store the stock prices in a column named “price”

The result transforms the original wide format (5 rows × 106 columns) into long format (525 rows × 4 columns), making it easier to analyze and visualize the time series data.