Building a Visualisation Dashboard Using R.
The dataset used in this project is from BEIS Departmental Spending over £25,000.
I started by collating 3 months worth of data from BEIS.
If you would like to view the end result, please click here.
The purpose of this dashboard is to visualise spendings by BEIS over the course of 3 months. This dashboard provides insights into 3 different categories, cumulative spendings above 1 million, 10 million and 100 million GBP. It also highlights the sectors where these amounts were allocated to, including the expense area, suppliers, and the date of each transaction.
Collating 3 months data.
april <- read.csv("beis-departmental-spend-over-25000-april-2023.csv") #loads the data for the month of april
may <- read.csv("beis-departmental-spend-over-25000-may-2023.csv") #loads data for the month of may
june <- read.csv("beis-departmental-spend-over-25000-june-2023 (2).csv") #loads the data for the month of june
joined_data <- rbind(june, april, may) #joins the 3-month data together into one
Unique_spendings <- unique(joined_data) # removes duplicates and allows only unique values.
The main focus of this project is on Energy spendings (Nuclear inclusive). In the codes below, energy spending rows are selected based on the Expense.area column.
filtered_rows <- Unique_spendings[grepl("Energy|Nuclear", Unique_spendings$Expense.Area, ignore.case = TRUE), ] #Selects rows based on the Expense.Area column, rows that have the word Energy or Nuclear in them. ignore.case ignores the matching patterns i.e either lowercase or uppercase
spendings_above_100million <- filtered_rows[filtered_rows$Amount > 100000000, ] #filter data by spending amount, greater than 100 million spendings_above_10million <- filtered_rows[filtered_rows$Amount > 10000000, ]#filter data by spending amount, greater than 10 million spendings_above_1million <- filtered_rows[filtered_rows$Amount > 1000000, ]#filter data by spending amount, greater than 1 million
Most of the descriptions and names of these rows are very long and if we try to use them that way, our visualisation will be cluttered and become a mess. So, my approach is to reduce the length of this texts. I can use "Desc" instead of writing "Description" in full. This will be applied to other rows to maintain their respective lengths.
In this dataset, my visualisation will be based on the following columns.
Date.of.Payment, Expense.Type, Expense.Area, Supplier, Amount, Description and Supplier.Type
#The following codes below will declutter the dashboard and ensure that the plots aren't filled with long names spendings_above_100million$Date.of.Payment <- substr(spendings_above_100million$Date.of.Payment, 1, 10) #retains the first 10 letters from the Supplier name. spendings_above_10million$Date.of.Payment <- substr(spendings_above_10million$Date.of.Payment, 1, 10) #retains the first 10 letters spendings_above_1million$Date.of.Payment <- substr(spendings_above_1million$Date.of.Payment, 1, 10) #retains the first 10 letters spendings_above_100million$Expense.Type <- substr(spendings_above_100million$Expense.Type, 1, 3) #retains the first 3 letters from the Supplier name. spendings_above_10million$Expense.Type <- substr(spendings_above_10million$Expense.Type, 1, 3) #retains the first 3 letters spendings_above_1million$Expense.Type <- substr(spendings_above_1million$Expense.Type, 1, 3) #retains the first 3 letters spendings_above_100million$Expense.Area <- substr(spendings_above_100million$Expense.Area, 1, 5) #retains the first 5 letters from the Supplier name. spendings_above_10million$Expense.Area <- substr(spendings_above_10million$Expense.Area, 1, 5) #retains the first 5 letters spendings_above_1million$Expense.Area <- substr(spendings_above_1million$Expense.Area, 1, 5) #retains the first 5 letters spendings_above_100million$Supplier <- substr(spendings_above_100million$Supplier, 1, 3) #retains the first 3 letters from the Supplier name. spendings_above_10million$Supplier <- substr(spendings_above_10million$Supplier, 1, 3) #retains the first 3 letters spendings_above_1million$Supplier <- substr(spendings_above_1million$Supplier, 1, 3) #retains the first 3 letters spendings_above_100million$Description <- substr(spendings_above_100million$Description, 1, 5) #retains the first 5 letters from the Supplier name. spendings_above_10million$Description <- substr(spendings_above_10million$Description, 1, 5) #retains the first 5 letters spendings_above_1million$Description <- substr(spendings_above_1million$Description, 1, 5) #retains the first 5 letters
Unlike Python, In R, string indexing starts from 1 and not 0.
To visualise this dataset, I used flex_dashboard. Although Rshiny is the goto for creating dashboards, flexdashboard is free and simpler to implement.