1. Background:

2. Business objective:

The purpose of this exercise is to start slicing and dicing the data to understand key drivers for Total Operating Revenue per Active Card holder and create insights for the same.


3. DATA:

The data used for this was from years 2020-2022. Over 1093 records with around 317 columns were present. From this Total Operating Revenue per Active card holder was to be calculated and drivers for this field was found.


4. EXPLORATION OF DATA:


************INSIGHT 1 STARTS*****************************************************

4a. INSIGHT 1 - IMPLEMENTED INFERENCE ON Mean of Revenue per Active card holder grouped based on Region and Year

Created a summarization of Mean of Revenue per Active card holder grouped based on Region and Year. It was found that Northern Libraries are doing better compared to other regions have downhill. Need to look at avenues to improve the outlook of other regions.

************INSIGHT 1 ENDS*******************************


************INSIGHT 2 STARTS*****************************

4b. INSIGHT 2 - IMPLEMENTED RANKING WITH NAMES AND INFERRED ON INCREASING REVENUE PER CARD HOLDER

a. Created classifications and ranking for Revenue per Active card holder and created a plot over Active Card holders. If Active Card holders increase the Revenue per Active Card holder does not increase. Efforts must be made to increase revenue while new Active Card holders are added new programs or services should be added to increase the revenue or New Libraries must be added so that card holders will have option to move to new libraries helping the revenue per active card holder to be high

b. Created a Box plot and Violin for Revenue per Active Card holder against Library Types. Few libraries have better Revenue per active card holder because of less number of Active Card holders in those regions which confirm our earlier inference

************INSIGHT 2 ENDS*****************************

************INSIGHT 3 STARTS*****************************

4c. INSIGHT 3 - Inference of Change in Active Card holder and Revenue per active card holder.

A plot was created between Change % between 2020-2022 for fields Active Card holders and Revenue per active card holders . Plotting confirmed that increase in Active holders did not increase Revenue per cardholder.

************INSIGHT 3 ENDS*****************************

**************************************************************


5. INSIGHT SUMMARY AND RECOMMENDATIONS

Insights:

Recommendations:

a. Since demand is more in regions other than north, they can increase charges on some of the facilities used and add more services for a premium to increase Revenue per Active card holder.

b. Efforts must be made to increase revenue while new Active Card holders are added like


6. APPENDIX

CODE FOR MERGING DATA AND INITIAL ARTICULATION OF DATA INCLUDING CREATION OF FIELD FOR REVENUE PER ACTIVE CARD HOLDER
################################################################################
######## NAME: NIVEDITHA KM ####################################################
######## ASSIGNMENT 4       ####################################################
################################################################################

#importing tidyverse package
library(tidyverse)
library(dplyr)

################################################################################
#DATA SETS IMPORT
#CSV FOR 2022 FROM ONTARIO SITE WAS HAVING ISSUES. HENCE I CREATED CSV FROM XLS
#FROM THE SITE AND REMOVING THE FIRST ROW
################################################################################

data_2020 <- read.csv("2020_ontario_public_library_statistics_open_data.csv")

data_2021 <- read.csv("2021_ontario_public_library_statistics_open_data.csv")

data_2022 <- read.csv("ontario_public_library_statistics_2022_open_data.csv")

################################################################################
# DATA FROM 2022 2021 AND 2020 HAS BEEN MERGED TOGETHER FOR THE COMMON COLUMNS
#                                  START
################################################################################
#MERGING DATA AND IDENITYING THE COMMON COLUMNS
#mrgList <- list(data_2020, data_2021, data_2022,data_15_18)
mrgList <- list(data_2020, data_2021, data_2022)
mrgColList <- lapply(mrgList,names)
commonCols <- Reduce(intersect,mrgColList)

#COMBINED DATA
data_combined <- rbind(subset(data_2020, select = commonCols),
                       subset(data_2021, select = commonCols),
                       subset(data_2022, select = commonCols))
#OMIT THE NA
#na.omit(data_combined)

data_combined <- data_combined %>% drop_na()

suppressWarnings(as.numeric(gsub(",", "", data_combined, fixed = TRUE)))
##   [1] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
##  [26] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
##  [51] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
##  [76] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [101] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [126] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [151] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [176] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [201] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [226] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [251] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [276] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
## [301] NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
################################################################################
# DATA FROM 2022 2021 AND 2020 HAS BEEN MERGED TOGETHER FOR THE COMMON COLUMNS
#                                  END
################################################################################

#ORDERING THE MERGED DATA
data_combined1 <- data_combined[order(data_combined$Survey.Year.From),]

#DROP ROWS WITH NA IN IT

#data_combined1 <- data_combined1 %>% drop_na()

#VIEW OF COMBINED DATA
#view(data_combined1)

#FOLLOWING PIECE OF CODE IS TO REMOVE COMMAS FROM COLUMNS
data <- data.frame(lapply(data_combined1, function(x) {
  gsub(",", "", x)
  }))

#FOLLOWING PIECE OF CODE IS TO CONVERT ALL NUMERIC COLUMNS TO NUMERIC
is_all_numeric <- function(x) {
  !any(is.na(suppressWarnings(as.numeric(na.omit(x))))) & is.character(x)
}

data %>% 
  mutate_if(is_all_numeric,as.numeric) %>%
  str()
## 'data.frame':    1093 obs. of  317 variables:
##  $ Library.Full.Name                                                                                                                                                                                                                                                             : chr  "Addington Highlands Twp" "Adjala-TosorontioTwp" "Admaston/Bromley Twp" "Ajax" ...
##  $ Library.Number                                                                                                                                                                                                                                                                : chr  "L0005" "L0003" "L0002" "L0032" ...
##  $ Survey.Year.From                                                                                                                                                                                                                                                              : num  2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 ...
##  $ A1.3.Ontario.Library.Service..OLS..Region..English.                                                                                                                                                                                                                           : chr  "Southern Ontario Library Service" "Southern Ontario Library Service" "Southern Ontario Library Service" "Southern Ontario Library Service" ...
##  $ A1.4.Type.of.Library.Service..English.                                                                                                                                                                                                                                        : chr  "Public or Union Library" "Contracting Municipality" "Public or Union Library" "Public or Union Library" ...
##  $ A1.5.Mailing.Address                                                                                                                                                                                                                                                          : chr  "3641 Flinton Rd. Box 72 " "7855 Sideroad 30 R.R. #1 " "Hwy 60 P O Box 130 " "55 Harwood Avenue South " ...
##  $ A1.9.Street.Address                                                                                                                                                                                                                                                           : chr  "3641 Flinton Rd. " "7855 Sideroad 30 R.R. #1 " "5346 Highway 60 " "55 Harwood Avenue South " ...
##  $ A1.10.City.Town                                                                                                                                                                                                                                                               : chr  "Flinton" "Alliston" "Douglas" "Ajax" ...
##  $ A1.11.Province                                                                                                                                                                                                                                                                : chr  "Ontario" "Ontario" "Ontario" "Ontario" ...
##  $ A1.12.Postal.Code                                                                                                                                                                                                                                                             : chr  "K0H1P0" "L9R1V1" "K0J1S0" "L1S2H8" ...
##  $ A1.13.Web.Site.Address                                                                                                                                                                                                                                                        : chr  "www.addingtonhighlandspubliclibrary.ca" "www.adjtos.ca" "http://www.admastonbromleylibrary.com" "ajaxlibrary.ca" ...
##  $ A1.14..No..of.Active.Library.Cardholders                                                                                                                                                                                                                                      : num  910 0 366 25897 0 ...
##  $ B1.1..Net.Balance.brought.forward.from.previous.year                                                                                                                                                                                                                          : num  25345 0 2589 373000 0 ...
##  $ B2.1.1..Public.Library.Operating.Grant..PLOG.                                                                                                                                                                                                                                 : num  11434 13821 5518 83286 1500 ...
##  $ B2.1.2..Pay.Equity.Grant                                                                                                                                                                                                                                                      : num  0 0 0 71368 0 ...
##  $ B2.1.3..First.Nation.Salary.Supplement.Grant                                                                                                                                                                                                                                  : num  0 0 0 0 0 0 0 13000 0 0 ...
##  $ B2.1.4..Total.Provincial.Operating.Funding                                                                                                                                                                                                                                    : num  11434 13821 5518 154654 1500 ...
##  $ B2.3..Contract.Revenue..funds.from.other.municipalities..neighbouring.public.library.boards..Local.Services.Boards.or.First.Nation.Bands.that.contract.for.library.services...Specify.name.of.contracting.organization.and.amount.received.from.each.                         : num  0 0 652 0 0 ...
##  $ B2.4.1.03..Trillium.Foundation.funding                                                                                                                                                                                                                                        : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ B2.4.1.04..Provincial.student.employment.funding                                                                                                                                                                                                                              : num  0 0 3658 0 0 ...
##  $ B2.4.1.05..ServiceOntario                                                                                                                                                                                                                                                     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ B2.4.1.08..Young.Canada.Works                                                                                                                                                                                                                                                 : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ B2.4.1.99..Other.Project.Grant                                                                                                                                                                                                                                                : num  0 0 0 40000 0 0 0 0 0 0 ...
##  $ B2.4.1a..Total.Project.Grants                                                                                                                                                                                                                                                 : num  0 0 3658 40000 0 ...
##  $ B2.5..Donations..monies.donated.by.outside.bodies..e.g..Friends.groups..trust.funds.etc..                                                                                                                                                                                     : num  0 0 1742 38418 0 ...
##  $ B2.6..Self.Generated.Revenue..e.g..fines..fees..sales.fundraising..room.rentals..cafe.revenue..etc..                                                                                                                                                                          : num  625 0 3770 36400 0 ...
##  $ B2.7..Debt.Services.and.Reserve.Fund.Revenues                                                                                                                                                                                                                                 : num  0e+00 0e+00 0e+00 2e+05 0e+00 0e+00 0e+00 0e+00 0e+00 0e+00 ...
##  $ B2.8..Other..Specify.any.other.source.of.funds.not.included.above.and.indicate.the.amount.received.from.each.funding.source.listed..e.g..Connectivity.                                                                                                                        : num  2508 0 6171 18742 0 ...
##  $ B2.9..Total.Operating.Revenues                                                                                                                                                                                                                                                : num  93119 23540 41510 6561320 4100 ...
##  $ B3.1..Provincial                                                                                                                                                                                                                                                              : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ B3.2..Federal                                                                                                                                                                                                                                                                 : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ B3.3..Local                                                                                                                                                                                                                                                                   : num  0 0 0 75122 0 ...
##  $ B3.4..Other..specify.any.other.source.of.funds.not.included.above.and.indicate.the.amount.received.from.each.funding.source.listed..report.each.funding.source.on.a.separate.line.                                                                                            : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ B3.5..Total.Capital.Revenues                                                                                                                                                                                                                                                  : num  0 0 0 75122 0 ...
##  $ B4.01..Materials                                                                                                                                                                                                                                                              : num  9817 0 7738 807280 0 ...
##  $ B4.01.1..General..Include.all.physical.items.that.are.not.electronic..e.g..books..periodicals..etc..                                                                                                                                                                          : num  8534 0 5146 414798 0 ...
##  $ B4.01.2..Electronic..e.g..electronic.subscriptions.and.other.databases..downloadable.media..gaming..software..Playaway..DVDs..and.e.resources.                                                                                                                                : num  1283 0 2592 392482 0 ...
##  $ B4.02.1..Total.funds..not.including.employee.benefits.                                                                                                                                                                                                                        : num  58883 0 19632 3073438 0 ...
##  $ B4.02.2..Employee.Benefits                                                                                                                                                                                                                                                    : num  0 0 0 487818 0 ...
##  $ B4.03..Staff.Training..Total.funds.spent.on.staff.training..including.travel.and.accommodation.                                                                                                                                                                               : num  110 0 50 36195 0 ...
##  $ B4.05.1..Telephone.and.Fax                                                                                                                                                                                                                                                    : num  1742 0 868 2401 0 ...
##  $ B4.06..Computer.Services..Total.spent.on.computer.equipment.and.related.expenses..e.g..computer.maintenance.contracts..etc...new.automated.systems..etc..                                                                                                                     : num  1298 0 2123 135614 0 ...
##  $ B4.07..Debt.Charges.and.Transfers.to.Reserve.Funds                                                                                                                                                                                                                            : num  0 0 0 259250 0 ...
##  $ B4.08..Funds.returned.to.government.sources                                                                                                                                                                                                                                   : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ B4.09..Contract.payments.made.to.library.board.for.library.services..Specify.the.library.board.s..and.amount.given.to.each.board..report.each.payment.on.a.separate.line.                                                                                                     : num  0 23540 0 0 4100 ...
##  $ B4.10..Other..specify.all.other.expenditures.not.reported.above.and.indicate.the.amount.spent.for.each.expenditure.listed..report.each.item.on.a.separate.line.                                                                                                               : num  5350 0 6182 67659 0 ...
##  $ B5.0..Total.Operating.Expenditures                                                                                                                                                                                                                                            : num  83725 23540 42857 5411305 4100 ...
##  $ B6.0..Total.Capital.Expenditures..funds.expended.on.land..building.construction.renovation.or.furniture.                                                                                                                                                                      : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.1.E..English.Print.Titles.Held                                                                                                                                                                                                                                             : num  11409 0 8167 92665 0 ...
##  $ C0.1.F..French.Print.Titles.Held                                                                                                                                                                                                                                              : num  25 0 77 7417 0 ...
##  $ C0.1.O..Other.Print.Titles.Held                                                                                                                                                                                                                                               : num  0 0 0 6310 0 23 0 0 0 0 ...
##  $ C0.1.T..Total.Print.Titles.Held                                                                                                                                                                                                                                               : num  11434 0 8244 106392 0 ...
##  $ C0.2.E..English.Print.Volumes.Held                                                                                                                                                                                                                                            : num  12570 0 8308 146928 0 ...
##  $ C0.2.F..French.Print.Volumes.Held                                                                                                                                                                                                                                             : num  29 0 77 8361 0 ...
##  $ C0.2.O..Other.Print.Volumes.Held                                                                                                                                                                                                                                              : num  0 0 0 6310 0 23 0 0 0 0 ...
##  $ C0.2.T..Total.Print.Volumes.Held                                                                                                                                                                                                                                              : num  12599 0 8385 161599 0 ...
##  $ C0.3.1.E..English.CD.and.DVD.Titles                                                                                                                                                                                                                                           : num  2057 0 1259 13293 0 ...
##  $ C0.3.1.F..French.CD.and.DVD.Titles                                                                                                                                                                                                                                            : num  1 0 0 858 0 ...
##  $ C0.3.1.O..Other.CD.and.DVD.Titles                                                                                                                                                                                                                                             : num  0 0 0 3202 0 ...
##  $ C0.3.1.T..Total.CD.and.DVD.Titles                                                                                                                                                                                                                                             : num  2058 0 1259 17353 0 ...
##  $ C0.3.2.E..English.CD.and.DVD.Copies                                                                                                                                                                                                                                           : num  2157 0 1259 33753 0 ...
##  $ C0.3.2.F..French.CD.and.DVD.Copies                                                                                                                                                                                                                                            : num  0 0 0 1493 0 ...
##  $ C0.3.2.O..Other.CD.and.DVD.Copies                                                                                                                                                                                                                                             : num  0 0 0 3668 0 ...
##  $ C0.3.2.T..Total.CD.and.DVD.Copies                                                                                                                                                                                                                                             : num  2157 0 1259 38914 0 ...
##  $ C0.3.5.E..English.E.Book.Titles                                                                                                                                                                                                                                               : num  0 0 74712 22971 0 ...
##  $ C0.3.5.F..French.E.Book.Titles                                                                                                                                                                                                                                                : num  0 0 550 0 0 ...
##  $ C0.3.5.O..Other.E.Book.Titles                                                                                                                                                                                                                                                 : num  0 0 132 0 0 0 132 0 132 0 ...
##  $ C0.3.5.T..Total.E.Book.Titles                                                                                                                                                                                                                                                 : num  0 0 75394 22971 0 ...
##  $ C0.3.6.E..English.E.Book.Copies                                                                                                                                                                                                                                               : num  0 0 87129 29214 0 ...
##  $ C0.3.6.F..French.E.Book.Copies                                                                                                                                                                                                                                                : num  0 0 550 0 0 ...
##  $ C0.3.6.O..Other.E.Book.Copies                                                                                                                                                                                                                                                 : num  0 0 175 0 0 0 175 0 175 0 ...
##  $ C0.3.6.T..Total.E.Book.Copies                                                                                                                                                                                                                                                 : num  0 0 87854 29214 0 ...
##  $ C0.3.7.E..English.E.Audio.Book.Titles                                                                                                                                                                                                                                         : num  0 0 18537 7840 0 ...
##  $ C0.3.7.F..French.E.Audio.Book.Titles                                                                                                                                                                                                                                          : num  0 0 70 0 0 0 488 0 70 0 ...
##  $ C0.3.7.O..Other.E.Audio.Book.Titles                                                                                                                                                                                                                                           : num  0 0 9 0 0 0 9 0 9 0 ...
##  $ C0.3.7.T..Total.E.Audio.Book.Titles                                                                                                                                                                                                                                           : num  0 0 18616 7840 0 ...
##  $ C0.3.8.E..English.E.Audio.Book.Copies                                                                                                                                                                                                                                         : num  0 0 23526 16913 0 ...
##  $ C0.3.8.F..French.E.Audio.Book.Copies                                                                                                                                                                                                                                          : num  0 0 73 0 0 0 493 0 73 0 ...
##  $ C0.3.8.O..Other.E.Audio.Book.Copies                                                                                                                                                                                                                                           : num  0 0 39 0 0 0 39 0 39 0 ...
##  $ C0.3.8.T..Total.E.Audio.Book.Copies                                                                                                                                                                                                                                           : num  0 0 23638 16913 0 ...
##  $ C0.4.E..English.Special.Collections...Original.Format                                                                                                                                                                                                                         : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.4.F..French.Special.Collections...Original.Format                                                                                                                                                                                                                          : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.4.O..Other.Special.Collections...Original.Format                                                                                                                                                                                                                           : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.4.T..Total.Special.Collections...Original.Format                                                                                                                                                                                                                           : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.5.E..English.Special.Collections...Digital.Format                                                                                                                                                                                                                          : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.5.F..French.Special.Collections...Digital.Format                                                                                                                                                                                                                           : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.5.O..Other.Special.Collections...Digital.Format                                                                                                                                                                                                                            : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C0.5.T..Total.Special.Collections...Digital.Format                                                                                                                                                                                                                            : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C3.1.E..English.Print.Periodical.Titles.Held                                                                                                                                                                                                                                  : num  14 0 0 93 0 0 0 2 19 0 ...
##  $ C3.1.F..French.Print.Periodical.Titles.Held                                                                                                                                                                                                                                   : num  0 0 0 4 0 0 33 0 0 0 ...
##  $ C3.1.O..Other.Print.Periodical.Titles.Held                                                                                                                                                                                                                                    : num  0 0 0 3 0 0 0 0 0 0 ...
##  $ C3.1.T..Total.Print.Periodical.Titles.Held                                                                                                                                                                                                                                    : num  14 0 0 100 0 0 33 2 19 0 ...
##  $ C3.2.0.E..English.Databases.and.Database.Subscriptions                                                                                                                                                                                                                        : num  1 0 0 9 0 0 0 1 13 0 ...
##  $ C3.2.0.F..French.Databases.and.Database.Subscriptions                                                                                                                                                                                                                         : num  0 0 0 3 0 0 0 0 1 0 ...
##  $ C3.2.0.O..Other.Databases.and.Database.Subscriptions                                                                                                                                                                                                                          : num  0 0 0 2 0 0 0 0 0 0 ...
##  $ C3.2.0.T..Total.Databases.and.Database.Subscriptions                                                                                                                                                                                                                          : num  1 0 0 14 0 0 0 1 14 0 ...
##  $ C4.1.F..No                                                                                                                                                                                                                                                                    : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ C4.1.T..Yes                                                                                                                                                                                                                                                                   : num  0 0 0 1 0 0 1 0 0 0 ...
##  $ C4.2..If.yes..how.many                                                                                                                                                                                                                                                        : num  0 0 0 9 0 0 1 0 0 0 ...
##   [list output truncated]
#VALIDATE IF FOLDER EXISTS
#print(file.exists("D:\\Learn Again\\M.Tech\\17 Guelph Program\\library csv"))

#write.csv(data, "D:\\Learn Again\\M.Tech\\17 Guelph Program\\library csv\\data.csv", row.names=FALSE)

#print(str(data))

data$B2.9..Total.Operating.Revenues <- 
  as.numeric(data$B2.9..Total.Operating.Revenues)

data$A1.14..No..of.Active.Library.Cardholders <- 
  as.numeric(data$A1.14..No..of.Active.Library.Cardholders)

data$B5.0..Total.Operating.Expenditures <- 
  as.numeric(data$B5.0..Total.Operating.Expenditures)

#CALCULATE REVENUE FOR ACTIVE CARD
data$revenue_active_card = 
  data$B2.9..Total.Operating.Revenues/data$A1.14..No..of.Active.Library.Cardholders

#CALCULATE PROFIT/LOSS
data$Profit_Loss = 
  data$B2.9..Total.Operating.Revenues - data$B5.0..Total.Operating.Expenditures

#data %>% mutate(revenue_active_card = ifelse(revenue_active_card < 0,0, revenue_active_card))

#view(data)

# remove row with na value in year column
data<- subset(data,!is.na(data$Survey.Year.From))

#REPLACE inf with 0
my_data <- do.call(data.frame,                      # Replace Inf in data by 0
                   lapply(data,
                          function(x) replace(x, is.infinite(x), NA)))

#write.csv(my_data, "D:\\Learn Again\\M.Tech\\17 Guelph Program\\library csv\\data.csv", row.names=FALSE)


my_data$revenue_active_card <- 
  as.numeric(my_data$revenue_active_card)
**************************************************************************************
************INSIGHT 1 STARTS*****************************************************
**************************************************************************************
INSIGHT 1 - IMPLEMENTED INFERENCE ON Mean of Revenue per Active card holder grouped based on Region and Year
Created a summarization of Mean of Revenue per Active card holder grouped based on Region and Year. It was found that Northern Libraries are doing better compared to other regions have downhill. Need to look at avenues to improve the outlook of other regions.
#CREATE A DATAFRAME FOR INSIGHT 1

corr_df <- my_data

#DROPPED NA FROM THE REVENUE/ACTIVE CARD FIELD ELSE THE NA HAMPERS CALCULATION

corr_lib<- subset(corr_df,(corr_df$revenue_active_card!='NA'))

#view(corr_lib)

#SUMMARIZE DATA BASED ON (LIBRARY REGION, SURVEY YEAR) vs MEAN OF REVENUE/ACTIVE CARD SUMMARY SHOWS THAT NORTHERN LIBRARIES ARE DOING BETTER WITH RESPECT TO MEAN OF REVENUE/ACTIVE CARD HOLDER AND OTHER REGIONS HAVE GONE DOWNHILL. NEED TO LOOK AT AVENUES TO INCREASE REVENUE/ACTIVE CARD HOLDER IN THESE REGIONS

library(plyr); library(dplyr)

mu <- ddply(corr_lib, c("A1.3.Ontario.Library.Service..OLS..Region..English.","Survey.Year.From"), summarise, grp.mean=mean(revenue_active_card))

#BELOW IS MEAN OF REVENUE PER ACTIVE CARD HOLDER GROUPED BY REGION AND YEAR-INSIGHT 1

mu
##   A1.3.Ontario.Library.Service..OLS..Region..English. Survey.Year.From grp.mean
## 1                     Ontario Library Service - North             2020 394.7626
## 2                     Ontario Library Service - North             2021 562.1338
## 3                     Ontario Library Service - North             2022 647.3989
## 4                    Southern Ontario Library Service             2020 192.1949
## 5                    Southern Ontario Library Service             2021 494.2468
## 6                    Southern Ontario Library Service             2022 277.6626
## 7                                             Toronto             2020 259.9803
## 8                                             Toronto             2021 310.6185
## 9                                             Toronto             2022 288.7792
#PLOTS FOR MEAN OF REVENUE PER ACTIVE CARD HOLDER OVER REGION AND YEAR - INSIGHT 1
d_ply(mu, "A1.3.Ontario.Library.Service..OLS..Region..English.", transform, {
    plot(Survey.Year.From, grp.mean, type = "l")
    mtext(unique(A1.3.Ontario.Library.Service..OLS..Region..English.), line = -1.5)
})

**************************************************************
************INSIGHT 1 ENDS*******************************
************INSIGHT 2 STARTS*****************************
**************************************************************
INSIGHT 2 - IMPLEMENTED RANKING WITH NAMES AND INFERRED ON INCREASING REVENUE PER CARD HOLDER
a. Created classifications and ranking for Revenue per Active card holder and created a plot over Active Card holders. If Active Card holders increase the Revenue per Active Card holder does not increase. Efforts must be made to increase revenue while new Active Card holders are added new programs or services should be added to increase the revenue or New Libraries must be added so that card holders will have option to move to new libraries helping the revenue per active card holder to be high
b. Created a Box plot and Violin for Revenue per Active Card holder against Library Types. Few libraries have better Revenue per active card holder because of less number of Active Card holders in those regions which confirm our earlier inference
# OBTAIN REVENUE, EXPENDITURE, ACTIVE CARD HOLDER, REVENUE/ACTIVE CARD HOLDER
# AND PROFIT-LOSS FOR VARIOUS LIBRARIES GROUPED OVER YEARS 2020-2022

rev_exp_revact_pl <- reshape(my_data[c('Library.Number','Library.Full.Name','Survey.Year.From',
                            'A1.14..No..of.Active.Library.Cardholders',
                            'B2.9..Total.Operating.Revenues',
                            'B5.0..Total.Operating.Expenditures',
                            'Profit_Loss',
                            'revenue_active_card')],direction= "wide",
                  idvar=c("Library.Number","Library.Full.Name"),timevar="Survey.Year.From")



#write.csv(df_rev, "D:\\Learn Again\\M.Tech\\17 Guelph Program\\library csv\\df_rev.csv", row.names=FALSE)

# rename columns
names(rev_exp_revact_pl)<- c("Library.Number","Library.Full.Name","Activecard_2020","Tot_Opr_Rev_2020","Tot_Opr_Exp_2020","Profit_Loss_2020","Avgrev_2020"
                             ,"Activecard_2021","Tot_Opr_Rev_2021","Tot_Opr_Exp_2021","Profit_Loss_2021","Avgrev_2021"
                             ,"Activecard_2022","Tot_Opr_Rev_2022","Tot_Opr_Exp_2022","Profit_Loss_2022","Avgrev_2022")

#write.csv(rev_exp_revact_pl, "D:\\Learn Again\\M.Tech\\17 Guelph Program\\library csv\\rev_exp_revact_pl.csv", row.names=FALSE)

# IGNORE LIBRARIES WITH NO ACTIVE CARD HOLDERS

rev_exp_revact_pl<- subset(rev_exp_revact_pl,(rev_exp_revact_pl$Activecard_2020!=0) 
                           | (rev_exp_revact_pl$Activecard_2021!=0) 
                           | (rev_exp_revact_pl$Activecard_2022!=0))

rev_exp_revact_pl<- subset(rev_exp_revact_pl,!is.na(rev_exp_revact_pl$Activecard_2022))


#CREATE BINS OF 5

rev_exp_revact_pl <- rev_exp_revact_pl %>% mutate(Rank_Activecard_2022 = ntile(Activecard_2022, n=5))
rev_exp_revact_pl <- rev_exp_revact_pl %>% mutate(Rank_Avgrev_2022 = ntile(Avgrev_2022, n=5))
rev_exp_revact_pl <- rev_exp_revact_pl %>% mutate(Rank_Profit_Loss_2022 = ntile(Profit_Loss_2022, n=5))

view(rev_exp_revact_pl)

#df_highrev <- rev_exp_revact_pl[rev_exp_revact_pl$Rank_Avgrev_2022 == 5,]

#df_highrev$Rank_Activecard_2022<-as.factor(df_highrev$Rank_Activecard_2022)

rev_exp_revact_pl$Rank_Activecard_2022<-as.factor(rev_exp_revact_pl$Rank_Activecard_2022)

top_library <- c(1,2,3,4,5)
description <- factor(c('5th Rank','4th Rank','3rd Rank','2nd Rank','1st Rank'))

rev_exp_revact_pl$activecard_2022tier<- description[match(rev_exp_revact_pl$Rank_Activecard_2022,top_library)]
rev_exp_revact_pl$avgrev_2022tier<- description[match(rev_exp_revact_pl$Rank_Avgrev_2022,top_library)]
rev_exp_revact_pl$profit_loss_2022tier<- description[match(rev_exp_revact_pl$Rank_Profit_Loss_2022,top_library)]


view(rev_exp_revact_pl)
################################################################################
#########################   INSIGHT2   #########################################
################################################################################

options(repr.plot.width = 100, repr.plot.height = 100)
P1 <- ggplot(rev_exp_revact_pl, aes(x =Avgrev_2022   , y =Activecard_2022  )) +
  geom_point(aes(colour = avgrev_2022tier), size = 3) + # Points and color by group
  scale_y_continuous(limits=c(0, 250000)) +
  scale_x_continuous(limits=c(0, 2000)) +
  scale_color_discrete("Ranks of cardholder") +  # Change legend title
  xlab("Revenue per cardholder (per library)") +             
  ylab("Number of active cardholder (per library)")  +
  ggtitle("Revenue per cardholder vs. Number of Active cardholder") +
  theme(axis.line = element_line(colour = "black", size = 0.1))

P1

############## VIOLIN PLOT #################

library(plotly)

library(ggplot2)
#update.packages("ggplot2")

#FIRST NATIONS LIBRARY HAVE THE BEST REVENUE PER ACTIVE CARD HOLDER THIS CAN BE
#SEEN FROM THE VIOLIN PLOT

fig <- my_data %>%
  plot_ly(
    x = ~A1.4.Type.of.Library.Service..English.,
    y = ~revenue_active_card,
    split = ~A1.4.Type.of.Library.Service..English.,
    type = 'violin',
    box = list(
      visible = T
    ),
    meanline = list(
      visible = T
    )
  ) 

fig <- fig %>%
  layout(
    xaxis = list(
      title = "LIBRARY SERVICE"
    ),
    yaxis = list(
      title = "REVENUE PER ACTIVE CARD HOLDER",
      zeroline = F
    )
  )

fig
vio_lib<- subset(my_data,(my_data$revenue_active_card!='NA'))

p<-ggplot(vio_lib, aes(x=A1.4.Type.of.Library.Service..English., y=revenue_active_card, color=A1.4.Type.of.Library.Service..English.)) +
  geom_boxplot(width=0.7)+
  ylim(0,3000) +
  labs(title="REVENUE PER LIBRARY SERVICE",x="Library Service", y = "Revenue per active card holder") + guides(x = guide_axis(angle = 45))
p

************INSIGHT 2 ENDS*****************************
**************************************************************
************INSIGHT 3 STARTS*****************************
**************************************************************
INSIGHT 3 - Inference of Change in Active Card holder and Revenue per active card holder.
A plot was created between Change % between 2020-2022 for fields Active Card holders and Revenue per active card holders . Plotting confirmed that increase in Active holders did not increase Revenue per cardholder.
rev_exp_revact_pl$Act_card_grw_20_22 <- round(rev_exp_revact_pl$Activecard_2022 / rev_exp_revact_pl$Activecard_2020 * 100,0)

rev_exp_revact_pl$Avgrevgrowth_20_22 <- round(rev_exp_revact_pl$Avgrev_2022 / rev_exp_revact_pl$Avgrev_2020 * 100,0)

rev_exp_revact_pl$activecardchange <-cut(rev_exp_revact_pl$Act_card_grw_20_22, 
                                         breaks=c(-Inf,90,99,101,110,+Inf),
                                         labels=c('Decrease >10%','Decrease ','Unchange','Increase <=10%','increase >10%'))


my_plot2 <- ggplot(rev_exp_revact_pl, aes(x=Avgrevgrowth_20_22, y=Act_card_grw_20_22
                               , color=activecardchange))

my_plot2 + geom_point() + geom_smooth() +
  scale_color_discrete("Change % No of Act Cardhold between 2020-2022") +
  xlab("Change in Average Revenue per Actv Card Holder (%)") + 
  ggtitle("Change in - Active cardholder numbers vs Change in revenue per Actv Card holders 2020-2022") +
  ylab("Change in Number of Active cardholders between 20-22")  +
  scale_x_continuous(limits=c(0, 5000)) 

************INSIGHT 3 ENDS*****************************
**************************************************************

APPENDIX ENDS

NOTE:

Violin plot was created with plotly just to explore plotly’s interactive feature. I had converted the HTML to PDF to avoid confusion caused last time. However I had published the report in RPubs as well here so that report can be viewed intact in HTML format as well https://rpubs.com/Nivikm/Assignment4/

Tried to just paste the code alone in Appendix(without the code executing) however it was getting executed.