API Call per Row of data frame in R

Question

I have one column in a dataframe that consists of ticker codes such as AAPL (for Apple stock), TWTR (for Twitter), and many more. I am trying to create a new column where it will return the number of stocks for each ticker code that have been computed from the stock API data.

But when I ran the code below, the new column "Stock Quantity" returned NA for every row. Does anyone have the solution to this?

library(Quandl)

portfolio <- data.frame(Code=c("AAPL", "TWTR", "MSFT"),
                startingPeriod=c("2015-01-01", "2015-01-01", "2015-01-01"),
                Investment=c("5000", "10000", "15000"), 
                stringsAsFactors=FALSE)


numberofStock <- function(pf) {

API <- Quandl(paste0("WIKI/", pf$Code), type = "raw", 
            start_date = pf$startingPeriod, end_date=Sys.Date())

pf["StockQuantity"] <- floor(pf$Investment_01 / tail(API$Open,1))

return(pf)
}


numberofStock(portfolio)

seasmith · Answer 1 · 2016-04-30T21:50:58.730

0

You might want to start by converting portfolio$startingPeriod to data type Date by using as.Date. Also, you are passing two vectors, portfolio$Code and portfolio$startingPeriod, to the function Quandl(). You may want to try using an lapply() function to iterate each value of those two functions.

EDIT: You will also need to convert portfolio$Investment to numeric using as.numeric(). Here is how the code should look:

portfolio <- data.frame(Code=c("AAPL", "TWTR", "MSFT"),
                        startingPeriod=as.Date(c("2015-01-01", "2015-01-01", "2015-01-01")),
                        Investment=as.numeric(c("5000", "10000", "15000")), 
                        stringsAsFactors=FALSE)


numberofStock <- function(pf) {lapply(seq_along(nrow(portfolio)), function(x){
    API <- Quandl(paste0("WIKI/", pf$Code[x]), type = "raw", 
                  start_date = pf$startingPeriod[x], end_date=Sys.Date())

    pf["StockQuantity"] <- floor(pf$Investment[x] / tail(API$Open,1))

    return(pf)
    })
}

numberofStock(portfolio)

edited Apr 30 '16 at 21:50

answered Apr 30 '16 at 16:05

seasmith

889
9
16

Thank you. I ran you code and it has successfully removed the NA from the output. However, I notice that the new column "Stock Quantity" are returning the same value of 44. The result should be 44, 275 and 321 respectively. What could be the issue? – Fxs7576 May 01 '16 at 07:49
@Fxs7576 Try replacing `seq_along(nrow(portfolio))` with `seq(1:nrow(pf))`, and replacing `return(pf)` with `return(pf[x,]`. The first change will allow iteration over all rows (since the original iteration was just looping once). The second change will return the specific row that the loop applied to.I think you can then use something like `plyr::ldply()` to union all of the rows into a single data frame. – seasmith May 01 '16 at 18:21
It works like a charm. In addition to your code, I used "y <- do.call(rbind.data.frame, lapply(seq(1:nrow(pf)), function(x) { ... }))return(y)} to combine all the rows into a single data frame. I really appreciate for your help. – Fxs7576 May 03 '16 at 01:56

bramtayl · Accepted Answer · 2016-04-30T16:23:52.013

0

Here's a start.

library(dplyr)

company.initial = 
  portfolio %>%
  mutate(Investment = as.numeric(Investment) ) %>%
  group_by(Code) %>%
  summarize(start_date = min(startingPeriod),
            total_investment = sum(Investment) )

company__date = 
  company.initial %>%
  group_by(Code) %>%
  do(with(.,
     Quandl(paste0("WIKI/", Code), 
            type = "raw",
            start_date = start_date, 
            end_date = Sys.Date() ) ) )

company = 
  company__date %>%
  group_by(Code) %>%
  summarize(last_open = last(Open)) %>%
  left_join(company.initial) %>%
  mutate(StockQuantity = total_investment / last_open)

edited Apr 30 '16 at 16:23

answered Apr 30 '16 at 16:13

bramtayl

4,004
2
11
18

Thank you very much. Your code works and it iis much simpler and neater. – Fxs7576 May 10 '16 at 06:16
By the way, can you explain why we have to use both 'Do' and 'With' functions for this purpose? – Fxs7576 May 11 '16 at 03:37
When the code inside a do loop is evaluated, it is evaluated with . as a chunk of the dataframe. In order to get columns in the chunk, you can either do .$Code, or you can evaluate Code within in the environment of . – bramtayl May 11 '16 at 04:44

API Call per Row of data frame in R

2 Answers2