How to Fill Gaps in Large Stock Data Universes Using tidyr and dplyr – Part II

Get started with Part I.

Another approach

There’s also a more verbose way to achieve our aim, and I’m showing it here because I think it’s useful to see how different functions and libraries connect and cross-over in the tidyverse (right now I’m fascinated by the intersection of the purrr::map functions and the dplyr::summarise_if, _at, _all functions…but that’s a story for another time).

The verbose approach is as follows:

• use tidyr::pivot_wide to reshape the data to row per date, with a column for each stock
• use tidyr::pivot_long to reshape it back to its longer format.

Let’s do it step by step…

First, we make it wide:

widedata <- testdata %>%
  pivot_wider(id_cols = date, names_from = ticker, values_from = returns)
widedata

## # A tibble: 3 x 4
##    date  AMZN    FB  TSLA
##   <dbl> <dbl> <dbl> <dbl>
## 1     1  0.01  0.02 NA   
## 2     2  0.03  0.04  0.05
## 3     3  0.06 NA     0.07

Where we had missing rows, we now have NA.

Now we make it long again:

tidydata <- widedata %>%
  pivot_longer(-date, names_to = 'ticker', values_to =  'returns')
tidydata

## # A tibble: 9 x 3
##    date ticker returns
##   <dbl> <chr>    <dbl>
## 1     1 AMZN      0.01
## 2     1 FB        0.02
## 3     1 TSLA     NA   
## 4     2 AMZN      0.03
## 5     2 FB        0.04
## 6     2 TSLA      0.05
## 7     3 AMZN      0.06
## 8     3 FB       NA   
## 9     3 TSLA      0.07

And again we have a row for every date for every stock.

tidydata %>%
  group_by(ticker) %>%
  summarise(count = n())

## # A tibble: 3 x 2
##   ticker count
##   <chr>  <int>
## 1 AMZN       3
## 2 FB         3
## 3 TSLA       3

Here’s the complete pipeline:

testdata %>%
  pivot_wider(id_cols = date, names_from = ticker, values_from = returns) %>%
  pivot_longer(-date, names_to = 'ticker', values_to =  'returns')

## # A tibble: 9 x 3
##    date ticker returns
##   <dbl> <chr>    <dbl>
## 1     1 AMZN      0.01
## 2     1 FB        0.02
## 3     1 TSLA     NA   
## 4     2 AMZN      0.03
## 5     2 FB        0.04
## 6     2 TSLA      0.05
## 7     3 AMZN      0.06
## 8     3 FB       NA   
## 9     3 TSLA      0.07

Stay tuned for the next installment in which the author will explore what happens if we have more than one variable in our original data.

Visit Robot Wealth website for additional insight: https://robotwealth.com/how-to-fill-gaps-in-large-stock-data-universes-using-tidyr-and-dplyr/

Past performance is not indicative of future results.

Any stock, options or futures symbols displayed are for illustrative purposes only and are not intended to portray recommendations.