MBAS 921: Week 2 Tutorial

This week’s tutorial deals with the very first step in working with data: plotting it
(descriptive analytics)! In particular, we do the following:

i. An Excel file named “Tutorial_2.xlsx” is given. The file contains Monthly Retail
Turnover by state[1], for all months from April 1982 to June 2025. The data
includes the turnover for NSW, VIC, QLD, SA, WA, TAS, NT, ACT, and a Total value.
Open the file in Excel and check for any missing values.

Data for NT starts from April 1988.

ii. Read the file using RStudio (you can install the package “readxl”, and then use
“read_excel”). Store the file into a variable named “raw_data”.

It is good to create a project first (File -> New Project -> New Directory -> New
Project) and store your files there. Here in the following code, I have created a
project, and inside that project, I have a folder named “Week_2”, where the Excel
file is stored. Try it a few times with different folders to make sure you can locate
the path correctly. The first two lines below install and load a package named
“raedxl” which is needed to upload an Excel file into R. The third line reads the
file and stores the content in an object named “raw_data” (you may use any
name), and then the last line prints the contents of the file.

[Link]("readxl")
library(readxl)

raw_data <- read_excel("Week_2/Tutorial_2.xlsx")

raw_data

iii. Store the raw data in a new variable named “data”, while blank rows are removed
(you can use “[Link]”).

We pass our “raw_data” to a function named “[Link]”, which removes any row
in the data containing at least one cell with no information, i.e., shown as “NA”. It
stores the updated data into file named “data” (again, this is the name that I have
chosen, you may shoose something else, but if you did, make sure you use your
file name in the following parts).
data <- [Link](raw_data)

iv. Plot a bar chart for the average trade value of each state: 8 bars in total (you can
calculate the average values first, using “colMeans”, and then plot the bar chart
using “geom_col” from “ggplot”).
 The idea is to use R for making plots, as any analysis with data should start by
looking at the data and describing it. For the bar chart, we first need to calculate
mean values for each state, which can be done by the function “colMeans”. The
function takes the data as input, as calculates the mean of each column.
However, if we write “colMeans(data)”, it will pass on an error because some
columns are not numeric and so there is no mean to be calculated. That is the
reason why we specifically mention which columns of “data” to pass on to the
function (all or a selection of the states that we want to work on). The result will
be the means in a row, which we change to a column format using “[Link]”
function. The result is stored in an object named “means_df”, with two columns
“State” and “Ave”. When we print, the first row is also the name of the states, but
that is just the row name which can not be used in the next part where we want
to plot the bar chart. That’s why we need the column “State”.

library(ggplot2)

states <- c("NSW","VIC","QLD","SA","WA","TAS","NT","ACT")

state_means <- colMeans(data[, states])

means_df <- [Link](State = names(state_means), Avg = state_means)

Then, we can plot the bar chart using “ggplot”, which is a library for making plots.
We first need to specify the source data, that is “means_df”, and then specify the
columns to be used for “x” and “y” in the plot. The next part “geom_col()”
specifies the type of plot we need (this one means a bar chart), and then we set
the labels as we want.

ggplot(means_df, aes(x = State, y = Avg)) +

geom_col() +
labs(title = "Average Retail Turnover by State", y = "Average turnover")

v. What do you observe from the plot? In what ways readability of the plot can be
improved? Apply them.

Descriptive analytics, which is mostly about plotting data, is not just electing a
random plot type and plotting it. It is a vital part of analysis and in a report, one of
the main ways to convey the meaning and insights. The first chart we had in the
previous section is not at all a good chart. It can be improved by:
- Sorting bars from high to low to make the comparisons easier. After all, the
purpose of having a bar chart is to compare among the categories (here,
states). Without sorting, comparison could be very difficult if values are close
or there are too many categories.
 - Adding numeric labels to each bar. If not, the reader will have a difficult time
understanding numbers and will end up guessing it.
- Adding colours can also enhance the readability of the chart.

Here are the codes for the rest of the session, but as we have not covered them
in full, I leave them with minimal explanation so that you explore further up to our
next tutorial:

To sort from high to low:

means_df <- means_df[order(-means_df$Avg), ]

means_df$State <- factor(means_df$State, levels = means_df$State)

To have colors and labels:

[Link](1)
rand_col <- sample(colors(), nrow(means_df))

ggplot(means_df, aes(x = State, y = Avg, fill = State)) +

geom_col() +
geom_text(aes(label = round(Avg, 1)), vjust = -0.3, size = 3) +
scale_fill_manual(values = rand_col) +
ylim(0, max(means_df$Avg) * 1.1) +
labs(title = "Average Retail Turnover by State (Sorted)", y = "Average turnover") +
theme([Link] = "none")

vi. Plot a histogram of the trade values of NSW (you can use “geom_histogram” from
“ggplot”). Add a distribution curve to the plot (use “geom_density” from
“ggplot”).

Histogram:

ggplot(data, aes(x = NSW)) +

geom_histogram(bins = 12) +
labs(title = "NSW Turnover: Histogram", x = "NSW turnover", y = "Count")

Adding distribution curve:

ggplot(data, aes(x = NSW)) +

geom_histogram(aes(y = after_stat(density)), bins = 12) +
geom_density() +
labs(title = "NSW Turnover: Histogram + Density Curve", x = "NSW turnover", y =
"Density")
vii. Plot a time series of the NSW trade values over time (use “geom_line”).

We can use line chart from ggplot, as shown below, to do this part.

library(lubridate)

data2 <- data %>%

mutate(Month = yearmonth(`Month-year`))

ggplot(data2, aes(x = Month, y = NSW)) +

geom_line() +
labs(title = "NSW Turnover Over Time", x = "Month", y = "NSW turnover")

viii. Now, store the data as a “tsibble” and try “autoplot”. Compare with the line chart
used in the previous part.

In case the data is tsibble, autoplot will plot time series automatically as this is
the most suitable plot for this data. Later on, this is the preferred approach plot
time series (compared to the line chart above).

library(fpp3)

nsw_ts <- data2 %>%

select(Month, NSW) %>%
as_tsibble(index = Month)

autoplot(nsw_ts, NSW) +
labs(title = "NSW Turnover (autoplot)", x = "Month", y = "NSW turnover")

[1] Sorce: [Link]

trade-australia/latest-release#analysis-by-industry