How to convert blank & -1 to 0 in imported CSV?

Hi all,
I tried searching for similar cases, but somehow, I am getting the opposite responses.
Here goes my query:

I am trying to import large data set from excel to R, which consists sales data.
column consists weekly sales qty.
Row consists the product ID against which sales happened.

this raw data has many excel cells where sales value is negative (example: -5, because of return of sold product), some cells are blank (because of no sell on particular week.).
My requirement:
While doing read.csv function, I want to directly convert "blank cell" and "negative values" to numerical "0".
Please advice.

Hi rdubai,

This is a case where the dplyr::case_when() function is really helpful.

See the reproducible example below, in which I create a toy dataset and read it in as character vectors.

Then I use mutate() and case_when() to identify cases with blank cells or a single space in cells and convert these to zero (character "0"). Then I can convert these to numeric, and the product_id into a factor.

library(tidyverse)

excel_data <- data.frame( # create data
  product_id = c(rep('001',5), rep('002',5),
                 rep('003',5), rep('004',5)),
  weekly_sales_qty = c(12,-5,""," ",12, "",6, 32, -2,3,
                       4,-1,"", 4, 5,7, 3, -3, "", 14),
  stringsAsFactors = FALSE #read in as character vectors
)

head(excel_data) # take a look at data with blanks
#>   product_id weekly_sales_qty
#> 1        001               12
#> 2        001               -5
#> 3        001                 
#> 4        001                 
#> 5        001               12
#> 6        002

excel_data %>% 
  mutate(weekly_sales_qty = case_when( # conversions
    weekly_sales_qty == "" ~ "0", # truly blank to zero
    weekly_sales_qty == " " ~ "0", # one space to zero
    TRUE ~ weekly_sales_qty # the rest remain the same
  )) %>% # now switch to numeric for qty
  mutate(weekly_sales_qty = as.numeric(weekly_sales_qty)) %>% # now switch to factor for product_id
  mutate(product_id = as.factor(product_id)) ->
clean_data

clean_data # show data
#>    product_id weekly_sales_qty
#> 1         001               12
#> 2         001               -5
#> 3         001                0
#> 4         001                0
#> 5         001               12
#> 6         002                0
#> 7         002                6
#> 8         002               32
#> 9         002               -2
#> 10        002                3
#> 11        003                4
#> 12        003               -1
#> 13        003                0
#> 14        003                4
#> 15        003                5
#> 16        004                7
#> 17        004                3
#> 18        004               -3
#> 19        004                0
#> 20        004               14

Created on 2019-11-05 by the reprex package (v0.3.0)

for guidance on making your own reprex (which helps get questions answered), take a look at

Hello, great to see your response. I am just putting it in work. But I see this part will only identify blanks, spaces to zero. Another portion of my query is to convert any negative sales qty to 0. example: -15 to 0. How do I do it ? thanks a lot for response! :slight_smile:

It would be easier to help you with your specific problem if you provide a proper REPRoducible EXample (reprex) or at least sample data on a copy/paste friendly format (like explained in the link).

Yes, i was trying to prepare that, by looking at the steps. But, I got an error. Let me do it again and paste here. thanks

I tried to post the reprex, but got error as limit is 32000, but my data is 97000.

So, what do I do here?

rm(list = ls())

#set directory
setwd("d:\\Users\\SaikatH\\AFG\\AF Work\\R\\DemandPlanning\\DemandPlanning")

# load required packages
library(readr)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(ggplot2)
library(forecast)
#> Registered S3 method overwritten by 'xts':
#>   method     from
#>   as.zoo.xts zoo
#> Registered S3 method overwritten by 'quantmod':
#>   method            from
#>   as.zoo.data.frame zoo
#> Registered S3 methods overwritten by 'forecast':
#>   method             from    
#>   fitted.fracdiff    fracdiff
#>   residuals.fracdiff fracdiff
library(reprex)

# import data
sales <- read.csv("SalesHistoryTop50SKU_reprex.csv", header = TRUE, blank.lines.skip = TRUE)

# reading sales 
sales 
#>   Sum.of.Actual.Sales.Qty       X     X.1    X.2     X.3     X.4     X.5
#> 1 Sum of Actual Sales Qty  1.2017  1.2018 1.2019  2.2017  2.2018  3.2017
#> 2                  922691  9.0000  5.0000     NA  5.0000  2.0000  5.0000
#> 3                  933647  3.0000 27.0000 5.0000      NA 24.0000  1.0000
#> 4                  934671      NA  7.0000 1.0000      NA  3.0000      NA
#> 5                  934673      NA  8.0000     NA      NA 10.0000      NA
#> 6                  946965 11.0000  1.0000     NA 15.0000  4.0000 15.0000
#>       X.6     X.7     X.8    X.9   X.10   X.11    X.12    X.13   X.14
#> 1  3.2018  4.2017  4.2018 5.2017 5.2018 6.2017  6.2018  7.2017 7.2018
#> 2  7.0000  6.0000  3.0000 6.0000 8.0000 2.0000  3.0000 13.0000 7.0000
#> 3 26.0000  6.0000 10.0000 2.0000 6.0000     NA -1.0000      NA 1.0000
#> 4      NA      NA  2.0000     NA     NA     NA      NA      NA     NA
#> 5  8.0000      NA  4.0000     NA 2.0000     NA  5.0000      NA     NA
#> 6      NA 21.0000  1.0000 9.0000 1.0000 6.0000  4.0000  5.0000 7.0000
#>     X.15    X.16    X.17    X.18    X.19     X.20    X.21     X.22    X.23
#> 1 8.2017  8.2018  9.2017  9.2018 10.2017  18.2018 19.2017  19.2018 20.2017
#> 2 9.0000 11.0000 10.0000  8.0000  7.0000   8.0000 18.0000       NA 32.0000
#> 3     NA  1.0000      NA      NA      NA       NA  1.0000   1.0000  2.0000
#> 4     NA -5.0000 -6.0000 -7.0000  3.0000 -15.0000  1.0000 -10.0000  1.0000
#> 5     NA      NA      NA  3.0000      NA       NA      NA       NA  3.0000
#> 6 5.0000  1.0000  7.0000  3.0000  1.0000   4.0000  4.0000   5.0000  7.0000
#>      X.24    X.25    X.26    X.27    X.28     X.29     X.30    X.31
#> 1 20.2018 21.2017 21.2018 22.2017 22.2018  23.2017  23.2018 24.2017
#> 2  1.0000 19.0000  1.0000 11.0000      NA   9.0000   1.0000 18.0000
#> 3  1.0000      NA  4.0000  1.0000      NA -18.0000 -22.0000      NA
#> 4 -3.0000  1.0000  1.0000      NA  1.0000       NA   2.0000  1.0000
#> 5      NA -1.0000      NA  2.0000      NA   1.0000       NA      NA
#> 6  5.0000  3.0000  5.0000  1.0000  8.0000   6.0000   4.0000  2.0000
#>      X.32    X.33    X.34    X.35    X.36    X.37    X.38    X.39    X.40
#> 1 24.2018 25.2017 25.2018 26.2017 26.2018 27.2017 27.2018 28.2017 28.2018
#> 2      NA  5.0000 15.0000  8.0000 16.0000 19.0000  7.0000 15.0000 25.0000
#> 3      NA      NA  1.0000      NA      NA      NA      NA  1.0000      NA
#> 4      NA  1.0000  1.0000      NA  3.0000      NA  1.0000      NA      NA
#> 5      NA  1.0000      NA  2.0000  1.0000  1.0000  1.0000      NA      NA
#> 6  8.0000  3.0000  2.0000      NA  4.0000  1.0000  2.0000      NA  1.0000
#>      X.41    X.42    X.43    X.44    X.45    X.46    X.47    X.48    X.49
#> 1 29.2017 29.2018 30.2017 30.2018 31.2017 47.2018 48.2017 48.2018 49.2017
#> 2 11.0000 19.0000 16.0000  9.0000 20.0000  2.0000  6.0000  2.0000  6.0000
#> 3      NA      NA  1.0000  4.0000      NA 19.0000 36.0000 14.0000 21.0000
#> 4      NA  1.0000      NA      NA      NA 25.0000  6.0000 16.0000  4.0000
#> 5      NA      NA  1.0000  2.0000  1.0000  9.0000  9.0000  6.0000  8.0000
#> 6      NA  1.0000  1.0000  3.0000  5.0000      NA      NA  5.0000      NA
#>      X.50    X.51    X.52    X.53    X.54    X.55    X.56        X.57
#> 1 49.2018 50.2017 50.2018 51.2017 51.2018 52.2017 52.2018 Grand Total
#> 2 -2.0000 12.0000  2.0000  5.0000      NA  9.0000      NA        1182
#> 3 14.0000 23.0000 17.0000  2.0000 14.0000 26.0000 21.0000         506
#> 4 24.0000 -1.0000 12.0000 -1.0000  7.0000  5.0000  3.0000         271
#> 5  7.0000  5.0000  6.0000  6.0000  4.0000  8.0000 11.0000         246
#> 6  4.0000      NA  1.0000      NA  1.0000      NA  2.0000         343
head(sales)
#>   Sum.of.Actual.Sales.Qty       X     X.1    X.2     X.3     X.4     X.5
#> 1 Sum of Actual Sales Qty  1.2017  1.2018 1.2019  2.2017  2.2018  3.2017
#> 2                  922691  9.0000  5.0000     NA  5.0000  2.0000  5.0000
#> 3                  933647  3.0000 27.0000 5.0000      NA 24.0000  1.0000
#> 4                  934671      NA  7.0000 1.0000      NA  3.0000      NA
#> 5                  934673      NA  8.0000     NA      NA 10.0000      NA
#> 6                  946965 11.0000  1.0000     NA 15.0000  4.0000 15.0000
#>       X.6     X.7     X.8    X.9   X.10   X.11    X.12    X.13   X.14
#> 1  3.2018  4.2017  4.2018 5.2017 5.2018 6.2017  6.2018  7.2017 7.2018
#> 2  7.0000  6.0000  3.0000 6.0000 8.0000 2.0000  3.0000 13.0000 7.0000
#> 3 26.0000  6.0000 10.0000 2.0000 6.0000     NA -1.0000      NA 1.0000
#> 4      NA      NA  2.0000     NA     NA     NA      NA      NA     NA
#> 5  8.0000      NA  4.0000     NA 2.0000     NA  5.0000      NA     NA
#> 6      NA 21.0000  1.0000 9.0000 1.0000 6.0000  4.0000  5.0000 7.0000
#>     X.15    X.16    X.17    X.18    X.19     X.20    X.21     X.22    X.23
#> 1 8.2017  8.2018  9.2017  9.2018 10.2017  18.2018 19.2017  19.2018 20.2017
#> 2 9.0000 11.0000 10.0000  8.0000  7.0000   8.0000 18.0000       NA 32.0000
#> 3     NA  1.0000      NA      NA      NA       NA  1.0000   1.0000  2.0000
#> 4     NA -5.0000 -6.0000 -7.0000  3.0000 -15.0000  1.0000 -10.0000  1.0000
#> 5     NA      NA      NA  3.0000      NA       NA      NA       NA  3.0000
#> 6 5.0000  1.0000  7.0000  3.0000  1.0000   4.0000  4.0000   5.0000  7.0000
#>      X.24    X.25    X.26    X.27    X.28     X.29     X.30    X.31
#> 1 20.2018 21.2017 21.2018 22.2017 22.2018  23.2017  23.2018 24.2017
#> 2  1.0000 19.0000  1.0000 11.0000      NA   9.0000   1.0000 18.0000
#> 3  1.0000      NA  4.0000  1.0000      NA -18.0000 -22.0000      NA
#> 4 -3.0000  1.0000  1.0000      NA  1.0000       NA   2.0000  1.0000
#> 5      NA -1.0000      NA  2.0000      NA   1.0000       NA      NA
#> 6  5.0000  3.0000  5.0000  1.0000  8.0000   6.0000   4.0000  2.0000
#>      X.32    X.33    X.34    X.35    X.36    X.37    X.38    X.39    X.40
#> 1 24.2018 25.2017 25.2018 26.2017 26.2018 27.2017 27.2018 28.2017 28.2018
#> 2      NA  5.0000 15.0000  8.0000 16.0000 19.0000  7.0000 15.0000 25.0000
#> 3      NA      NA  1.0000      NA      NA      NA      NA  1.0000      NA
#> 4      NA  1.0000  1.0000      NA  3.0000      NA  1.0000      NA      NA
#> 5      NA  1.0000      NA  2.0000  1.0000  1.0000  1.0000      NA      NA
#> 6  8.0000  3.0000  2.0000      NA  4.0000  1.0000  2.0000      NA  1.0000
#>      X.41    X.42    X.43    X.44    X.45    X.46    X.47    X.48    X.49
#> 1 29.2017 29.2018 30.2017 30.2018 31.2017 47.2018 48.2017 48.2018 49.2017
#> 2 11.0000 19.0000 16.0000  9.0000 20.0000  2.0000  6.0000  2.0000  6.0000
#> 3      NA      NA  1.0000  4.0000      NA 19.0000 36.0000 14.0000 21.0000
#> 4      NA  1.0000      NA      NA      NA 25.0000  6.0000 16.0000  4.0000
#> 5      NA      NA  1.0000  2.0000  1.0000  9.0000  9.0000  6.0000  8.0000
#> 6      NA  1.0000  1.0000  3.0000  5.0000      NA      NA  5.0000      NA
#>      X.50    X.51    X.52    X.53    X.54    X.55    X.56        X.57
#> 1 49.2018 50.2017 50.2018 51.2017 51.2018 52.2017 52.2018 Grand Total
#> 2 -2.0000 12.0000  2.0000  5.0000      NA  9.0000      NA        1182
#> 3 14.0000 23.0000 17.0000  2.0000 14.0000 26.0000 21.0000         506
#> 4 24.0000 -1.0000 12.0000 -1.0000  7.0000  5.0000  3.0000         271
#> 5  7.0000  5.0000  6.0000  6.0000  4.0000  8.0000 11.0000         246
#> 6  4.0000      NA  1.0000      NA  1.0000      NA  2.0000         343

sales %>% 
  
  mutate(weekly_sales_qty = case_when( # conversions 
    
    sales == "" ~ "0", # truly blank to zero
    
    sales == " " ~ "0", # one space to zero
    
    TRUE ~ sales # the rest remain the same
  )) %>% # now switch to numeric for qty
  
  mutate(sales = as.numeric(sales)) %>% # now switch to factor for product_id
  
  mutate(product_id = as.factor(product_id)) ->
  
  clean_data
#> Error: `TRUE ~ sales` must be length 354 or one, not 59


# examine dataset to see the structure
str(sales)
#> 'data.frame':    6 obs. of  59 variables:
#>  $ Sum.of.Actual.Sales.Qty: Factor w/ 6 levels "922691","933647",..: 6 1 2 3 4 5
#>  $ X                      : num  1.2 9 3 NA NA ...
#>  $ X.1                    : num  1.2 5 27 7 8 ...
#>  $ X.2                    : num  1.2 NA 5 1 NA ...
#>  $ X.3                    : num  2.2 5 NA NA NA ...
#>  $ X.4                    : num  2.2 2 24 3 10 ...
#>  $ X.5                    : num  3.2 5 1 NA NA ...
#>  $ X.6                    : num  3.2 7 26 NA 8 ...
#>  $ X.7                    : num  4.2 6 6 NA NA ...
#>  $ X.8                    : num  4.2 3 10 2 4 ...
#>  $ X.9                    : num  5.2 6 2 NA NA ...
#>  $ X.10                   : num  5.2 8 6 NA 2 ...
#>  $ X.11                   : num  6.2 2 NA NA NA ...
#>  $ X.12                   : num  6.2 3 -1 NA 5 ...
#>  $ X.13                   : num  7.2 13 NA NA NA ...
#>  $ X.14                   : num  7.2 7 1 NA NA ...
#>  $ X.15                   : num  8.2 9 NA NA NA ...
#>  $ X.16                   : num  8.2 11 1 -5 NA ...
#>  $ X.17                   : num  9.2 10 NA -6 NA ...
#>  $ X.18                   : num  9.2 8 NA -7 3 ...
#>  $ X.19                   : num  10.2 7 NA 3 NA ...
#>  $ X.20                   : num  18.2 8 NA -15 NA ...
#>  $ X.21                   : num  19.2 18 1 1 NA ...
#>  $ X.22                   : num  19.2 NA 1 -10 NA ...
#>  $ X.23                   : num  20.2 32 2 1 3 ...
#>  $ X.24                   : num  20.2 1 1 -3 NA ...
#>  $ X.25                   : num  21.2 19 NA 1 -1 ...
#>  $ X.26                   : num  21.2 1 4 1 NA ...
#>  $ X.27                   : num  22.2 11 1 NA 2 ...
#>  $ X.28                   : num  22.2 NA NA 1 NA ...
#>  $ X.29                   : num  23.2 9 -18 NA 1 ...
#>  $ X.30                   : num  23.2 1 -22 2 NA ...
#>  $ X.31                   : num  24.2 18 NA 1 NA ...
#>  $ X.32                   : num  24.2 NA NA NA NA ...
#>  $ X.33                   : num  25.2 5 NA 1 1 ...
#>  $ X.34                   : num  25.2 15 1 1 NA ...
#>  $ X.35                   : num  26.2 8 NA NA 2 ...
#>  $ X.36                   : num  26.2 16 NA 3 1 ...
#>  $ X.37                   : num  27.2 19 NA NA 1 ...
#>  $ X.38                   : num  27.2 7 NA 1 1 ...
#>  $ X.39                   : num  28.2 15 1 NA NA ...
#>  $ X.40                   : num  28.2 25 NA NA NA ...
#>  $ X.41                   : num  29.2 11 NA NA NA ...
#>  $ X.42                   : num  29.2 19 NA 1 NA ...
#>  $ X.43                   : num  30.2 16 1 NA 1 ...
#>  $ X.44                   : num  30.2 9 4 NA 2 ...
#>  $ X.45                   : num  31.2 20 NA NA 1 ...
#>  $ X.46                   : num  47.2 2 19 25 9 ...
#>  $ X.47                   : num  48.2 6 36 6 9 ...
#>  $ X.48                   : num  48.2 2 14 16 6 ...
#>  $ X.49                   : num  49.2 6 21 4 8 ...
#>  $ X.50                   : num  49.2 -2 14 24 7 ...
#>  $ X.51                   : num  50.2 12 23 -1 5 ...
#>  $ X.52                   : num  50.2 2 17 12 6 ...
#>  $ X.53                   : num  51.2 5 2 -1 6 ...
#>  $ X.54                   : num  51.2 NA 14 7 4 ...
#>  $ X.55                   : num  52.2 9 26 5 8 ...
#>  $ X.56                   : num  52.2 NA 21 3 11 ...
#>  $ X.57                   : Factor w/ 6 levels "1182","246","271",..: 6 1 5 3 2 4
colnames(sales)
#>  [1] "Sum.of.Actual.Sales.Qty" "X"                      
#>  [3] "X.1"                     "X.2"                    
#>  [5] "X.3"                     "X.4"                    
#>  [7] "X.5"                     "X.6"                    
#>  [9] "X.7"                     "X.8"                    
#> [11] "X.9"                     "X.10"                   
#> [13] "X.11"                    "X.12"                   
#> [15] "X.13"                    "X.14"                   
#> [17] "X.15"                    "X.16"                   
#> [19] "X.17"                    "X.18"                   
#> [21] "X.19"                    "X.20"                   
#> [23] "X.21"                    "X.22"                   
#> [25] "X.23"                    "X.24"                   
#> [27] "X.25"                    "X.26"                   
#> [29] "X.27"                    "X.28"                   
#> [31] "X.29"                    "X.30"                   
#> [33] "X.31"                    "X.32"                   
#> [35] "X.33"                    "X.34"                   
#> [37] "X.35"                    "X.36"                   
#> [39] "X.37"                    "X.38"                   
#> [41] "X.39"                    "X.40"                   
#> [43] "X.41"                    "X.42"                   
#> [45] "X.43"                    "X.44"                   
#> [47] "X.45"                    "X.46"                   
#> [49] "X.47"                    "X.48"                   
#> [51] "X.49"                    "X.50"                   
#> [53] "X.51"                    "X.52"                   
#> [55] "X.53"                    "X.54"                   
#> [57] "X.55"                    "X.56"                   
#> [59] "X.57"
view(sales)
#> Error in view(sales): could not find function "view"
summary(sales)
#>             Sum.of.Actual.Sales.Qty       X               X.1        
#>  922691                 :1          Min.   : 1.202   Min.   : 1.000  
#>  933647                 :1          1st Qu.: 2.550   1st Qu.: 2.151  
#>  934671                 :1          Median : 6.000   Median : 6.000  
#>  934673                 :1          Mean   : 6.050   Mean   : 8.200  
#>  946965                 :1          3rd Qu.: 9.500   3rd Qu.: 7.750  
#>  Sum of Actual Sales Qty:1          Max.   :11.000   Max.   :27.000  
#>                                     NA's   :2                        
#>       X.2             X.3              X.4              X.5        
#>  Min.   :1.000   Min.   : 2.202   Min.   : 2.000   Min.   : 1.000  
#>  1st Qu.:1.101   1st Qu.: 3.601   1st Qu.: 2.401   1st Qu.: 2.651  
#>  Median :1.202   Median : 5.000   Median : 3.500   Median : 4.101  
#>  Mean   :2.401   Mean   : 7.401   Mean   : 7.534   Mean   : 6.050  
#>  3rd Qu.:3.101   3rd Qu.:10.000   3rd Qu.: 8.500   3rd Qu.: 7.500  
#>  Max.   :5.000   Max.   :15.000   Max.   :24.000   Max.   :15.000  
#>  NA's   :3       NA's   :3                         NA's   :2       
#>       X.6              X.7              X.8              X.9       
#>  Min.   : 3.202   Min.   : 4.202   Min.   : 1.000   Min.   :2.000  
#>  1st Qu.: 6.050   1st Qu.: 5.550   1st Qu.: 2.250   1st Qu.:4.401  
#>  Median : 7.500   Median : 6.000   Median : 3.500   Median :5.601  
#>  Mean   :11.050   Mean   : 9.300   Mean   : 4.034   Mean   :5.550  
#>  3rd Qu.:12.500   3rd Qu.: 9.750   3rd Qu.: 4.151   3rd Qu.:6.750  
#>  Max.   :26.000   Max.   :21.000   Max.   :10.000   Max.   :9.000  
#>  NA's   :2        NA's   :2                         NA's   :2      
#>       X.10            X.11            X.12             X.13       
#>  Min.   :1.000   Min.   :2.000   Min.   :-1.000   Min.   : 5.000  
#>  1st Qu.:2.000   1st Qu.:4.000   1st Qu.: 3.000   1st Qu.: 6.101  
#>  Median :5.202   Median :6.000   Median : 4.000   Median : 7.202  
#>  Mean   :4.440   Mean   :4.734   Mean   : 3.440   Mean   : 8.401  
#>  3rd Qu.:6.000   3rd Qu.:6.101   3rd Qu.: 5.000   3rd Qu.:10.101  
#>  Max.   :8.000   Max.   :6.202   Max.   : 6.202   Max.   :13.000  
#>  NA's   :1       NA's   :3       NA's   :1        NA's   :3       
#>       X.14            X.15            X.16             X.17       
#>  Min.   :1.000   Min.   :5.000   Min.   :-5.000   Min.   :-6.000  
#>  1st Qu.:5.500   1st Qu.:6.601   1st Qu.: 1.000   1st Qu.: 3.750  
#>  Median :7.000   Median :8.202   Median : 1.000   Median : 8.101  
#>  Mean   :5.550   Mean   :7.401   Mean   : 3.240   Mean   : 5.050  
#>  3rd Qu.:7.050   3rd Qu.:8.601   3rd Qu.: 8.202   3rd Qu.: 9.401  
#>  Max.   :7.202   Max.   :9.000   Max.   :11.000   Max.   :10.000  
#>  NA's   :2       NA's   :3       NA's   :1        NA's   :2       
#>       X.18             X.19           X.20             X.21      
#>  Min.   :-7.000   Min.   : 1.0   Min.   :-15.00   Min.   : 1.00  
#>  1st Qu.: 3.000   1st Qu.: 2.5   1st Qu.: -0.75   1st Qu.: 1.00  
#>  Median : 3.000   Median : 5.0   Median :  6.00   Median : 4.00  
#>  Mean   : 3.240   Mean   : 5.3   Mean   :  3.80   Mean   : 8.64  
#>  3rd Qu.: 8.000   3rd Qu.: 7.8   3rd Qu.: 10.55   3rd Qu.:18.00  
#>  Max.   : 9.202   Max.   :10.2   Max.   : 18.20   Max.   :19.20  
#>  NA's   :1        NA's   :2      NA's   :2        NA's   :1      
#>       X.22             X.23            X.24            X.25      
#>  Min.   :-10.00   Min.   : 1.00   Min.   :-3.00   Min.   :-1.00  
#>  1st Qu.: -1.75   1st Qu.: 2.25   1st Qu.: 1.00   1st Qu.: 1.00  
#>  Median :  3.00   Median : 5.00   Median : 1.00   Median : 3.00  
#>  Mean   :  3.80   Mean   :10.87   Mean   : 4.84   Mean   : 8.64  
#>  3rd Qu.:  8.55   3rd Qu.:16.90   3rd Qu.: 5.00   3rd Qu.:19.00  
#>  Max.   : 19.20   Max.   :32.00   Max.   :20.20   Max.   :21.20  
#>  NA's   :2                        NA's   :1       NA's   :1      
#>       X.26            X.27            X.28           X.29       
#>  Min.   : 1.00   Min.   : 1.00   Min.   : 1.0   Min.   :-18.00  
#>  1st Qu.: 1.00   1st Qu.: 1.00   1st Qu.: 4.5   1st Qu.:  1.00  
#>  Median : 4.00   Median : 2.00   Median : 8.0   Median :  6.00  
#>  Mean   : 6.44   Mean   : 7.44   Mean   :10.4   Mean   :  4.24  
#>  3rd Qu.: 5.00   3rd Qu.:11.00   3rd Qu.:15.1   3rd Qu.:  9.00  
#>  Max.   :21.20   Max.   :22.20   Max.   :22.2   Max.   : 23.20  
#>  NA's   :1       NA's   :1       NA's   :3      NA's   :1       
#>       X.30             X.31            X.32            X.33      
#>  Min.   :-22.00   Min.   : 1.00   Min.   : 8.00   Min.   : 1.00  
#>  1st Qu.:  1.00   1st Qu.: 1.75   1st Qu.:12.05   1st Qu.: 1.00  
#>  Median :  2.00   Median :10.00   Median :16.10   Median : 3.00  
#>  Mean   :  1.64   Mean   :11.30   Mean   :16.10   Mean   : 7.04  
#>  3rd Qu.:  4.00   3rd Qu.:19.55   3rd Qu.:20.15   3rd Qu.: 5.00  
#>  Max.   : 23.20   Max.   :24.20   Max.   :24.20   Max.   :25.20  
#>  NA's   :1        NA's   :2       NA's   :4       NA's   :1      
#>       X.34            X.35            X.36            X.37      
#>  Min.   : 1.00   Min.   : 2.00   Min.   : 1.00   Min.   : 1.00  
#>  1st Qu.: 1.00   1st Qu.: 5.00   1st Qu.: 3.00   1st Qu.: 1.00  
#>  Median : 2.00   Median : 8.00   Median : 4.00   Median :10.00  
#>  Mean   : 8.84   Mean   :12.07   Mean   :10.04   Mean   :12.05  
#>  3rd Qu.:15.00   3rd Qu.:17.10   3rd Qu.:16.00   3rd Qu.:21.05  
#>  Max.   :25.20   Max.   :26.20   Max.   :26.20   Max.   :27.20  
#>  NA's   :1       NA's   :3       NA's   :1       NA's   :2      
#>       X.38            X.39            X.40            X.41      
#>  Min.   : 1.00   Min.   : 1.00   Min.   : 1.00   Min.   :11.00  
#>  1st Qu.: 1.00   1st Qu.: 8.00   1st Qu.:13.00   1st Qu.:15.55  
#>  Median : 2.00   Median :15.00   Median :25.00   Median :20.10  
#>  Mean   : 7.64   Mean   :14.73   Mean   :18.07   Mean   :20.10  
#>  3rd Qu.: 7.00   3rd Qu.:21.60   3rd Qu.:26.60   3rd Qu.:24.65  
#>  Max.   :27.20   Max.   :28.20   Max.   :28.20   Max.   :29.20  
#>  NA's   :1       NA's   :3       NA's   :3       NA's   :4      
#>       X.42            X.43            X.44            X.45     
#>  Min.   : 1.00   Min.   : 1.00   Min.   : 2.00   Min.   : 1.0  
#>  1st Qu.: 1.00   1st Qu.: 1.00   1st Qu.: 3.00   1st Qu.: 4.0  
#>  Median :10.00   Median : 1.00   Median : 4.00   Median :12.5  
#>  Mean   :12.55   Mean   : 9.84   Mean   : 9.64   Mean   :14.3  
#>  3rd Qu.:21.55   3rd Qu.:16.00   3rd Qu.: 9.00   3rd Qu.:22.8  
#>  Max.   :29.20   Max.   :30.20   Max.   :30.20   Max.   :31.2  
#>  NA's   :2       NA's   :1       NA's   :1       NA's   :2     
#>       X.46            X.47            X.48            X.49      
#>  Min.   : 2.00   Min.   : 6.00   Min.   : 2.00   Min.   : 4.00  
#>  1st Qu.: 9.00   1st Qu.: 6.00   1st Qu.: 5.25   1st Qu.: 6.00  
#>  Median :19.00   Median : 9.00   Median :10.00   Median : 8.00  
#>  Mean   :20.44   Mean   :21.04   Mean   :15.20   Mean   :17.64  
#>  3rd Qu.:25.00   3rd Qu.:36.00   3rd Qu.:15.50   3rd Qu.:21.00  
#>  Max.   :47.20   Max.   :48.20   Max.   :48.20   Max.   :49.20  
#>  NA's   :1       NA's   :1                       NA's   :1      
#>       X.50            X.51            X.52            X.53      
#>  Min.   :-2.00   Min.   :-1.00   Min.   : 1.00   Min.   :-1.00  
#>  1st Qu.: 4.75   1st Qu.: 5.00   1st Qu.: 3.00   1st Qu.: 2.00  
#>  Median :10.50   Median :12.00   Median : 9.00   Median : 5.00  
#>  Mean   :16.03   Mean   :17.84   Mean   :14.70   Mean   :12.64  
#>  3rd Qu.:21.50   3rd Qu.:23.00   3rd Qu.:15.75   3rd Qu.: 6.00  
#>  Max.   :49.20   Max.   :50.20   Max.   :50.20   Max.   :51.20  
#>                  NA's   :1                       NA's   :1      
#>       X.54            X.55            X.56                X.57  
#>  Min.   : 1.00   Min.   : 5.00   Min.   : 2.00   1182       :1  
#>  1st Qu.: 4.00   1st Qu.: 8.00   1st Qu.: 3.00   246        :1  
#>  Median : 7.00   Median : 9.00   Median :11.00   271        :1  
#>  Mean   :15.44   Mean   :20.04   Mean   :17.84   343        :1  
#>  3rd Qu.:14.00   3rd Qu.:26.00   3rd Qu.:21.00   506        :1  
#>  Max.   :51.20   Max.   :52.20   Max.   :52.20   Grand Total:1  
#>  NA's   :1       NA's   :1       NA's   :1

# convert our sales data to a time series object
salesTS <- ts(sales$ActualSalesQty, frequency = 52, start = c(2016,1), end = c(2018,52))
#> Error in ts(sales$ActualSalesQty, frequency = 52, start = c(2016, 1), : 'ts' object must have one or more observations
class(salesTS)
#> Error in eval(expr, envir, enclos): object 'salesTS' not found

library("TTR")
#PLt the ts
plot.ts(salesTS)
#> Error in NCOL(x): object 'salesTS' not found

# Plot
#options(repr.plot.width = 6, repr.plot.height = 5)
salesDecomp <- decompose(salesTS)
#> Error in decompose(salesTS): object 'salesTS' not found
plot(salesDecomp)
#> Error in plot(salesDecomp): object 'salesDecomp' not found

# log transform time series data -- check if required for ACE
salesLogHW <- HoltWinters(salesTS, alpha= 0.5, beta = NULL, gamma = NULL) 
#> Error in as.ts(x): object 'salesTS' not found


#options(repr.plot.width = 6, repr.plot.height = 4)
plot(salesLogHW)
#> Error in plot(salesLogHW): object 'salesLogHW' not found

# forecast next year's sales
nextYearSales <- forecast(salesLogHW, h=26)
#> Error in forecast(salesLogHW, h = 26): object 'salesLogHW' not found

# plot
plot(nextYearSales)
#> Error in plot(nextYearSales): object 'nextYearSales' not found

nextYearSales
#> Error in eval(expr, envir, enclos): object 'nextYearSales' not found
View(nextYearSales)
#> Error in as.data.frame(x): object 'nextYearSales' not found
write.csv(nextYearSales,"Forecast.csv",row.names = FALSE)
#> Error in is.data.frame(x): object 'nextYearSales' not found

Created on 2019-11-05 by the reprex package (v0.3.0)

Please read the guide more carefully, we don't need your whole dataset, we just need a small sample and just the part of your code that is relevant for the issue.

You can use a case-when line
Case-when(sales-qty <0 ~ “0”)

thanks, I will use this.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.