0

Dividing by Zero Error encountered

Hi, Stack community! I came across this error working today. I am not sure exactly how to work around it. I read to use a na.rm in my mutate, and I tried it, but it didn't work. I could be completely wrong. 


library("DBI")
library("dbplyr")
library("odbc")
library("dplyr")
library("stringr")
library("tidyverse")
library("lubridate")


  select(CustomerID, PostalCodeID, OrderID, ItemID, WrittenSales, WrittenUnits, TransCodeID, SalesType, ProductID, ProductName, GroupID, SubGroupID, CategoryID, TransDate, LocationID, LocationName) %>%

  filter(SalesType == "W",
         LocationID %in% Louisville) %>%

  group_by(CustomerID, PostalCodeID, WrittenSales, TransCodeID, SalesType, ProductID, ProductName, GroupID, SubGroupID, CategoryID, TransDate, LocationID, LocationName) %>%
  summarise(WrittenUnits_purchased = sum(WrittenUnits)) %>%
  ungroup() %>%

  group_by(CustomerID) %>%
  mutate(prop_of_total = WrittenUnits_purchased/sum(WrittenUnits_purchased)) %>%
  ungroup()```
r2evans
  • 141,215
  • 6
  • 77
  • 149
72o
  • 43
  • 9
  • 1
    Where did you try to incorporate `na.rm`? It should probably be an argument in `sum(WrittenUnits_purchased)`, since that's the denominator that could potentially be zero, and `sum` can accept `na.rm` – Punintended Feb 13 '20 at 18:43
  • 1
    It wasn't an R error. It was an MS SQL error. Voting to close, since the error obviously comes from a problem with data where there is a zero cound to some item or levle in some specific category. Needs code to sanitize the values before divisions, probably calculations of proportions. – IRTFM Feb 13 '20 at 18:45
  • Please do not post an image of code/data/errors: it cannot be copied or searched (SEO), it breaks screen-readers, and it may not fit well on some mobile devices. Ref: https://meta.stackoverflow.com/a/285557/3358272 (and https://xkcd.com/2116/). Please just include the code or data (e.g., `dput(head(x))` or `data.frame(...)`) directly. – r2evans Feb 13 '20 at 19:12
  • FYI, in addition to not having usable data, your code is incomplete (unless your frame really is stored in a variable named `CustomerID`). – r2evans Feb 13 '20 at 19:21

1 Answers1

1

While this is a SQL problem, it can be mitigated in your code.

Setup:

# library(odbc) or similar, for the DB driver
# con <- DBI::dbConnect(...)
DBI::dbExecute(con, "create table r2 (x int, y int)")
# [1] 0
DBI::dbExecute(con, "insert into r2 (x,y) values (1,1),(2,0)")
# [1] 2
DBI::dbGetQuery(con, "select * from r2")
#   x y
# 1 1 1
# 2 2 0

Demonstration of the problem in base R, and the SQL-based fix:

DBI::dbGetQuery(con, "select x/y as xy from r2")
# Error in result_fetch(res@ptr, n) : 
#   nanodbc/nanodbc.cpp:2593: 22012: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Divide by zero error encountered. 
# Warning in dbClearResult(rs) : Result already cleared
DBI::dbGetQuery(con, "select (case when y = 0 then null else x/y end) as xy from r2")
#   xy
# 1  1
# 2 NA

Since you're using dbplyr, here's that side of things:

library(dplyr)
library(dbplyr)
tbl(con, "r2") %>%
  collect()
# # A tibble: 2 x 2
#       x     y
#   <int> <int>
# 1     1     1
# 2     2     0
tbl(con, "r2") %>%
  mutate(xy = x/y) %>%
  collect()
# Error in result_fetch(res@ptr, n) : 
#   nanodbc/nanodbc.cpp:2593: 22012: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Divide by zero error encountered. 
# Warning in dbClearResult(res) : Result already cleared
tbl(con, "r2") %>%
  mutate(xy = if_else(y == 0, NA, x/y)) %>%
  collect()
# # A tibble: 2 x 3
#       x     y    xy
#   <int> <int> <int>
# 1     1     1     1
# 2     2     0    NA
r2evans
  • 141,215
  • 6
  • 77
  • 149