Average Monthly Sales in R

I found many dataset for sales dealing with Excel, then I tried to use R instead of Excel to measure the average monthly sales instead of excel, because some features in excel not showing the exact values when we use pivot tables, especially when we dealing with groups, but because of the power of R in statistics, the numbers and calculation results display in different ways when we grouping data and measuring average monthly sales using R.

Import Data Sales from Excel to R

The data set of sales, contains data related to real estate and properties, type of sales( House, Townhouse, Unit) and Price, and dates. We need to upload dataset into R that stored in excel, so we need to use read_xlsx function, by using the package from library(readxl):

library(readxl)

# upload data: 
sales <- read_xlsx("sales.xlsx")

# check the data: 
str(sales)

Subset data sales to last 12 months

Now we need to do some data manipulation, first we need to convert date as date in R by using the package of lubridate:

library(lubridate)

# Convert date type as date to make sure that R dealing with dates: 
sales$Date <- as.Date(sales$Date)


# subset sales for last 12 months from October 2016 to Sep 2017: 
last_12_months <- sales[sales$Date >= "2016-10-01",]


# Select required columns for monthly average sales: 
last_12_months <- last_12_months[, c('Date', 'Month', 'Year', 'Price', 'Type')]

# Make pretty data table for last 12 months data frame using library(DT)
library(DT)

pretty_headers <- gsub("[.]", " ", colnames(last_12_months)) %>%
  str_to_title()

last_12_months %>%
  datatable(
    rownames = FALSE,
    colnames = pretty_headers,
    extensions = c("Responsive", "Buttons"),
    filter = list(position = "top"),
    options = list(language = list(sSearch = "Filter:"),
                   buttons = I("colvis"),
                   dom = "Bfrtip")
  ) 

Calculate Average Monthly Sales

Now we need to calculate average monthly sales for the last 12 months sales, for that we need to group the data by month, year and type of sales, then we need to make a pivot wider for type of sales column,

# Create group data for average monthly sales: 
sales_avg <- last_12_months %>% 
group_by(Month, Year, Type) %>%
summarize(avg_sales = mean(Prices))

# create dataset for average monthly sales pivot wider type of sales: 
avg_monthly_sales <- 
pivot_wider(sales_avg, names_from = Type, values_from = avg_sales) %>% 
arrange(Year)

sales_date <- make_date(year = avg_monthly_sales$Year, month = avg_monthly_sales$Month)
avg_monthly_sales$sales_date <- sales_date

head(avg_monthly_sales, 10)

Now we need again to gather data for line chart by type of sale, and create yAxis = Average Sales, and xAxis = Date:

dat <- gather(avg_monthly_sales, "Type", "Price", -sales_date, -Month, -Year)

plot1 <- ggplot(dat, aes(x = sales_date, y = Price, color = Type )) + 
  geom_line(linewidth = 1)+
  geom_point(size = 2.5)+
  ylim(min = 200000, max = 1600000)+
  theme(legend.position = "bottom", 
        axis.text.x = element_text(angle = 45, hjust = 1))+ 
  scale_x_continuous("Date", labels = as.Date(sales_date), breaks = sales_date)+
  labs(title = "Average Monthly Sales",
        y = "Average Sale")

plot1

Make some pretty chart

We can use ggrepl package in R to label each line in chart, for geom_text_repl we need to create data_ends set that will include end of each line annotate, like this:

library(ggrepl)
# check data tails for creating data_ends to label each line in chart: 
tail(dat, 5)
data_ends <- dat %>% filter(sales_date == "2017-09-01")

# using ggrepl package to create label for each line: 
plot1+
  geom_text_repel(aes(label = Type), 
data = data_ends, 
fontface = "plain", 
color = "black", 
size = 3, 
hjust = 1, 
vjust = 1)

We can also use plotly package to make interactive chart:

library(plotly)

ggplotly(plot1)

Sales Method Pie Chart

Here I will create pie chart using highcharter package, to make pie chart percent for sales method (Auction, Sale)

library(highcharter)
x <- sales[,5] %>% group_by(Method) %>% mutate(Count = n()) %>% unique() 

sum_of_obsrv <- sum(x$Count) 

highchart() %>%
  hc_chart(type = "pie") %>%
  hc_title(text = "Sales Method") %>%
  hc_add_series(data = list_parse2(x), name = "Method",
                showInLegend = TRUE, dataLabels = list(enabled = TRUE, format = "{point.name}: {point.percentage:.1f}%")) %>% hc_exporting(enabled = TRUE, filename = "mychart")

End.

Thank You