CODEWITHSUNDEEP
rdplyr
Connie Chen
Connie Chen

Reputation: 1

dplyr package - apply two or more functions to tbl using mutate

I have data looking like this in SQL.

    date_at    price
1 2016-03-02 631USD/1M
2 2016-03-02 741USD/1M
3 2016-03-02 702USD/1M
4 2016-03-02 685USD/1M
5 2016-03-02 738USD/1M
6 2016-03-02 741USD/1M

Trying to use the following codes in R to get the table I want: 

df <- tbl(db,"table") %>%
  mutate(newprice = as.numeric(substr(price,1,regexpr("USD",price)-1))) %>%
  select(date, newprice) %>%
  head()

Namely, I'm trying to mutate the SQL tbl to following, then select variables:

    date_at    price    newprice
1 2016-03-02 631USD/1M  631 
2 2016-03-02 741USD/1M  741
3 2016-03-02 702USD/1M  702
4 2016-03-02 685USD/1M  685
5 2016-03-02 738USD/1M  738
6 2016-03-02 741USD/1M  741

However, I can't create the newprice column with above codes. Got this error:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  function regexpr("unknown", record) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
)

It occurs to me that mutate can only handle one function applied to the variable. What other function should i

Upvotes: 0

Views: 166

Answers (1)

Mario
Mario

Reputation: 316

This could help you:

df<-data.frame(date_at=c("2016-03-02","2016-03-02","2016-03-02","2016-03-02",
                         "2016-03-02","2016-03-02"),
               price=c("631USD/1M","741USD/1M","702USD/1M","685USD/1M",
                        "738USD/1M","741USD/1M"))

df%>%
        tbl_df%>%
        mutate(newprice=as.numeric(substr(price,1,regexpr("USD",price)-1)),
               date_at=as.Date(date_at))%>%
        select(date_at,newprice)%>%
        head()     

Source: local data frame [6 x 2]

     date_at newprice
      (date)    (dbl)
1 2016-03-02      631
2 2016-03-02      741
3 2016-03-02      702
4 2016-03-02      685
5 2016-03-02      738
6 2016-03-02      741

Upvotes: 0

Related Questions