Museums Data Analysis

Analysis of a public United States Museums dataset from Kaggle: https://www.kaggle.com/imls/museum-directory

The museum dataset is a list of museums and related organizations in the United States. The data file includes basic information about each organization (name, address, phone, website, and revenue) plus the museum type or discipline.

We are interested in extracting some insights about museums in the US, such as

• Which city or state has the most museums per capita?
• How many zoos or aquariums exist in the United States?
• What museum or related organization had the highest revenue last year?
• How does the composition of museum types differ across the country?

library(tidyverse)
library(ggplot2)
library(gghighlight)

#Importing the csv file to a books Data Frame
museum_data <- read.csv("museums.csv")

#Viewing the Data Frame
head(museum_data )

Data Cleaning and Preparation

In this stage the data is checked for accuracy and completeness prior to beginning the analysis. Some of the issues addressed are as follows:

• Remove extraneous data
• Check for in missing values
• Replace missing values
• Delete data that cannot be corrected/replaced
• Correct any data formatting issues
• Creating new features
• Identify errors revealed when new variables are created

Remove Extraneous Data

#Identifying the column names
colnames(museum_data)

 [1] "Museum.ID"                                "Museum.Name"                              "Legal.Name"                              
 [4] "Alternate.Name"                           "Museum.Type"                              "Institution.Name"                        
 [7] "Street.Address..Administrative.Location." "City..Administrative.Location."           "State..Administrative.Location."         
[10] "Zip.Code..Administrative.Location."       "Street.Address..Physical.Location."       "City..Physical.Location."                
[13] "State..Physical.Location."                "Zip.Code..Physical.Location."             "Phone.Number"                            
[16] "Latitude"                                 "Longitude"                                "Locale.Code..NCES."                      
[19] "County.Code..FIPS."                       "State.Code..FIPS."                        "Region.Code..AAM."                       
[22] "Employer.ID.Number"                       "Tax.Period"                               "Income"                                  
[25] "Revenue"                                 

There are a lot of columns in the dataframe, however not all of them are useful. Therefore, we will only select a subset of the dataframe containing the columns we are interested in.

#Creating a character vector with all the columns names we are interested in
keep_col <- c("Museum.ID","Legal.Name","Museum.Type","City..Administrative.Location.","State..Administrative.Location.","Zip.Code..Administrative.Location.","Income","Revenue"   )

museum <-museum_data[keep_col]

head(museum)

Missing Values

#Identifying total number of missing values
sum(is.na(museum))

[1] 20893

There are a significant number of missing values. Let’s check which columns have missing values

#Identifying total number of missing values
summary(museum)

   Museum.ID          Legal.Name        Museum.Type        City..Administrative.Location. State..Administrative.Location.
 Min.   :8.400e+09   Length:33072       Length:33072       Length:33072                   Length:33072                   
 1st Qu.:8.402e+09   Class :character   Class :character   Class :character               Class :character               
 Median :8.404e+09   Mode  :character   Mode  :character   Mode  :character               Mode  :character               
 Mean   :8.404e+09                                                                                                       
 3rd Qu.:8.405e+09                                                                                                       
 Max.   :8.410e+09                                                                                                       
                                                                                                                         
 Zip.Code..Administrative.Location.     Income              Revenue          
 Length:33072                       Min.   :-9.230e+02   Min.   :  -2127393  
 Class :character                   1st Qu.: 0.000e+00   1st Qu.:         0  
 Mode  :character                   Median : 8.781e+03   Median :      3307  
                                    Mean   : 1.070e+08   Mean   :  20976047  
                                    3rd Qu.: 2.164e+05   3rd Qu.:    167696  
                                    Max.   : 8.318e+10   Max.   :5840349457  
                                    NA's   :10111        NA's   :10782       

As seen above the Income and Revenue Columns have missing values. These columns also seem to have negative values, which we have to correct. As we do not have a way to accurately replace the missing data we will be dropping the associated rows.

Dropping Rows with Missing Values

#Removing rows with missing data
#The complete.cases() function will examine a dataframe and return a result vector of the rows which contain missing values.
museum <- na.omit(museum)

#Checking for any remaining missing values
sum(is.na(museum))

[1] 0

There are no more missing values.

Correcting Formatting Issues

Income and Revenue Columns have negative values. The negative values could represent two scenarios:

• Museums are operating at a loss, which accounts for the negative income and negative revenue.
• Error in the data input process which resulted in a negative value being entered.

In the absence of specific context, we do not know how or if these values need to be corrected. Therefore, we will assume that these negative values represent inaccurate entries and we will be dropping these values.

#Removing all rows with income and revenue less than 0
museum <- museum %>% filter((Income >= 0) & (Revenue >= 0))

Duplicate Data

#Number of Museum/Institution Names in the dataframe
length(museum$Legal.Name)

[1] 22250

#Number of unique Museum/Institution Names in the dataframe
length(unique(museum$Legal.Name))

[1] 20276

As we see here, there are clearly some museum names that have been repeated. ### Removing Duplicates

#Keeping only rows with distinct Legal.Name values
museum <- museum %>% distinct(Legal.Name, .keep_all = TRUE)

Exploratory Data Analysis

In this stage, we will examine the data to identify any patterns, trends and relationships between the variables. It will help us analyze the data and extract insights that can be used to make decisions.

Data Visualization will give us a clear idea of what the data means by giving it visual context.

Statistics

To understand the museum data at a high level we can start be looking at income and revenue in more detail

#Calculating average income, total income, average revenue and total revenue
avg_inc = mean(museum$Income) 
max_inc = max(museum$Income) 
avg_rev = mean(museum$Revenue) 
max_rev = max(museum$Revenue) 

#Calculating number of museums with zero income and revenue
zero_inc_rev <- museum %>% filter((Income == 0) & (Revenue == 0))

cat('The average museum income:', avg_inc, ' and the highest museum income:', max_inc, '\nThe average museum revenue', avg_rev, ' and the highest revenue:', max_rev, '\nThe number of museums with no income and revenue are:', length(zero_inc_rev))

The average museum income: 18073371  and the highest museum income: 83181439574 
The average museum revenue 8011790  and the highest revenue: 5840349457 
The number of museums with no income and revenue are: 8

#Number of unique Museum Types, Cities and States
length(unique(museum$Museum.Type))

[1] 9

length(unique(museum$City..Administrative.Location.))

[1] 7236

length(unique(museum$State..Administrative.Location.))

[1] 51

We have museum data for museums of 9 Types, located in 7236 Cities across 51 States.

Grouping Museum Data by Type, City and State

We can create a few functions to help us aggregate the museum data and

#Creating a function to create a dataframe with aggregate data
museum_group <- function(col_name) {
  
  #Grouping by col_name
  group_name <- museum %>% group_by(.dots = col_name)
  
  #Creating a data frame to store the summarized values of museums by col_name
  #tally() gives us a count of how many museums belong to the category
  museum_group_name <- group_name %>% tally()
  
  #Renaming the columns
  colnames(museum_group_name)[which(names(museum_group_name) == "n")] <- "Museum_Count"
  
  #Summarizing by average income
  group_name_average_income <- group_name %>% summarise(Income = mean(Income))
  museum_group_name$Average_Income<- group_name_average_income$Income
  
  #Summarizing by total income
  group_name_total_income <- group_name %>% summarise(Income = sum(Income))
  museum_group_name$Total_Income<- group_name_total_income$Income
  
  #Summarizing by average revenue
  group_name_average_revenue <- group_name %>% summarise(Revenue = mean(Revenue))
  museum_group_name$Average_Revenue<- group_name_average_revenue$Revenue
  
  #Summarizing by total revenue
  group_name_total_revenue <- group_name %>% summarise(Revenue = sum(Revenue))
  museum_group_name$Total_Revenue<- group_name_total_revenue$Revenue
  
  #Returning a dataframe
  return (museum_group_name)
}

#Creating a function to output maximum values from the dataframe with aggregate data
museum_group_max <- function(df,col_name) {
    
  avg_inc_max <- df %>% filter(Average_Income == max(df$Average_Income))
  tot_inc_max <- df %>% filter(Total_Income == max(df$Total_Income))
  avg_rev_max <- df %>% filter(Average_Revenue == max(df$Average_Revenue))
  tot_rev_max <- df %>% filter(Total_Revenue == max(df$Total_Revenue))
  
  #Calculating the highest museum count and associated col_name value
  count_max <- df %>% filter(Museum_Count == max(df$Museum_Count))
  
  return (cat('Museum Types with highest average income:', avg_inc_max[[col_name]], ' highest total income:', tot_inc_max[[col_name]], '\nhighest average revenue', avg_rev_max[[col_name]], ' highest total revenue:',tot_rev_max[[col_name]], '\n\n', count_max[[col_name]], 'which has',count_max$Museum_Count, ' museums, has the most museums.'))
}

#Creating a function to output minimum values from the dataframe with aggregate data
museum_group_min <- function(df,col_name) {

  avg_inc_min <- df %>% filter(Average_Income == min(df$Average_Income))
  tot_inc_min <- df %>% filter(Total_Income == min(df$Total_Income))
  avg_rev_min <- df %>% filter(Average_Revenue == min(df$Average_Revenue))
  tot_rev_min <- df %>% filter(Total_Revenue == min(df$Total_Revenue))

  #Calculating the lowest museum count and associated col_name value
  count_min <- df %>% filter(Museum_Count == min(df$Museum_Count))
  
  return (cat('\n\nMuseums with lowest average income:', avg_inc_min[[col_name]], ' lowest total income:', tot_inc_min[[col_name]], '\nlowest average revenue',avg_rev_min[[col_name]], ' lowest total revenue:', tot_rev_min[[col_name]],'\n\n', count_min[[col_name]], 'which has',count_min$Museum_Count, ' museums, has the least museums.'))
}

Type

To understand the museum data better we can group the data by Type and take a look at the average and total Income and Revenue.

#Grouping by type
museum_type <- museum_group('Museum.Type')

Warning: The `.dots` argument of `group_by()` is deprecated as of dplyr 1.0.0.
This warning is displayed once every 8 hours.
Call `lifecycle::last_warnings()` to see where this warning was generated.

#Renaming the column
colnames(museum_type)[which(names(museum_type) == "Museum.Type")] <- "Type"

head(museum_type)

NA

museum_group_max(museum_type,'Type')

Museum Types with highest average income: SCIENCE & TECHNOLOGY MUSEUM OR PLANETARIUM  highest total income: ART MUSEUM 
highest average revenue ART MUSEUM  highest total revenue: ART MUSEUM 

 HISTORIC PRESERVATION which has 11340  museums, has the most museums.

museum_group_min(museum_type,'Type')

Museums with lowest average income: HISTORIC PRESERVATION  lowest total income: CHILDREN'S MUSEUM 
lowest average revenue HISTORIC PRESERVATION  lowest total revenue: CHILDREN'S MUSEUM 

 NATURAL HISTORY MUSEUM which has 205  museums, has the least museums.

• Science & Technology Museums/Planetariums have the highest average income.
• Art Museums have the highest total income, average revenue and total revenue.
• Historic Preservations have the lowest average income and average revenue, and this museum type has the most museums as well.
• Children’s Museums have the lowest total income and total revenue.
• Natural History Museums have the least museums among all museum types.

#plotting bar graph for museum type and number of Museums
ggplot(data=museum_type,aes(x=Type,y=Museum_Count, fill=Type)) + geom_bar(stat="identity") + labs(title="Number of Museums by Type")+ theme(axis.title.x=element_blank(), axis.text.x=element_blank(), axis.ticks.x=element_blank())

Total Income by Museum Type

#Creating a Pie Chart 
type_income = museum_type$Total_Income
type_labels = museum_type$Type

# Plot the chart.
pie(type_income, labels=type_labels, main = "Distribution of Total Income by Museum Type",col = rainbow(length(type_income)))

As seen above Art Museums make up the biggest portion of all museum income in the US, while Children’s Museums make up the smallest portion of all museum income in the US. We can check to see if a similar pattern is observed with revenue.

Total Revenue by Museum Type

#Creating a Pie Chart 
type_revenue = museum_type$Total_Revenue
type_labels = museum_type$Type

# Plot the chart.
pie(type_revenue, labels=type_labels, main = "Distribution of Total Revenue by Museum Type",col = rainbow(length(type_revenue)))

Once again Art Museums make up the biggest portion of all museum revenue in the US, while Children’s Museums make up the smallest portion of all museum revenue in the US. For the museum data the revenue always lower than income so we could assume that the revenue is what the museum is left over with after deducting taxes and other expenses.

• It it noteworthy that the Art Museum’s revenue makes up a bigger portion of all museum revenue.
• The Art Museum’s is retaining a larger portion of its income as opposed to the other museum types.
• If this were not the case then we would expect the distribution to be similar to that of income.

City

We can group the data by City as well and take a look at the average and total Income and Revenue.

#Grouping by city
museum_city <- museum_group('City..Administrative.Location.')

#Renaming the column
colnames(museum_city)[which(names(museum_city) == "City..Administrative.Location.")] <- "City"

head(museum_city)

museum_group_max(museum_city,'City')

Museum Types with highest average income: AMADO  highest total income: AMADO 
highest average revenue AMADO  highest total revenue: WASHINGTON 

 NEW YORK which has 172  museums, has the most museums.

• Amado has the highest average income, average revenue and total income.
• Washington has the highest total revenue.
• New York City has the most museums.

Number of Museums vs. Total Income

#plotting scatter plot for number of museums vs. total income from museums in each city
ggplot(data=museum_city,aes(x=Museum_Count,y=Total_Income)) + geom_point() + labs(title="City Level Data: Number of Museums vs. Total Income") + gghighlight(Total_Income > 10000000000)

As seen above the vast majority of cities make under 10 Billion in income from museums.

Number of Museums vs. Total Revenue

#plotting scatter plot for number of museums vs. total income from museums in each state
ggplot(data=museum_city,aes(x=Museum_Count,y=Total_Revenue)) + geom_point() + labs(title="City Level Data: Number of Museums vs. Total Revenue") + gghighlight(Total_Revenue > 2000000000)

As seen above the vast majority of cities make under 2 Billion in revenue from museums.

State

We can group the data by State as well and take a look at the average and total Income and Revenue.

#Grouping by state
museum_state <- museum_group('State..Administrative.Location.')

#Renaming the column
colnames(museum_state)[which(names(museum_state) == "State..Administrative.Location.")] <- "State"

head(museum_state)

museum_group_max(museum_state,'State')

Museum Types with highest average income: AZ  highest total income: AZ 
highest average revenue DC  highest total revenue: CA 

 CA which has 1596  museums, has the most museums.

museum_group_min(museum_state,'State')

Museums with lowest average income: ND  lowest total income: ND 
lowest average revenue ND  lowest total revenue: ND 

 UT which has 63  museums, has the least museums.

• Arizona (AZ) has the highest average and total income.
• Washington DC (DC) has the highest average revenue.
• California (CA) has the highest total revenue and and also the most museums.
• North Dakota (ND) has the lowest average and total income and revenue.
• Utah (UT) has the least museums.

Number of Museums vs. Total Income

#plotting scatter plot for number of museums vs. total income from museums in each state
ggplot(data=museum_state,aes(x=Museum_Count,y=Total_Income)) + geom_point() + labs(title="State Level Data: Number of Museums vs. Total Income") + gghighlight(Total_Income > 10000000000)

Similar to the city level data, the majority of states make under 10 Billion in income from museums.

Number of Museums vs. Total Revenue

#plotting scatter plot for number of museums vs. total income from museums in each state
ggplot(data=museum_state,aes(x=Museum_Count,y=Total_Revenue)) + geom_point() + labs(title="State Level Data: Number of Museums vs. Total Revenue") + gghighlight(Total_Revenue > 5000000000)

The majority of states make under 5 Billion in revenue from museums.

Summary of Data Analysis

We have museum data for museums of 9 Types, located in 7236 Cities across 51 States.

Museum Types

• Science & Technology Museums/Planetariums have the highest average income.
• Art Museums have the highest total income, average revenue and total revenue.
• Historic Preservations have the lowest average income and average revenue, and this museum type has the most museums as well.
• Children’s Museums have the lowest total income and total revenue.
• Natural History Museums have the least museums among all museum types.

Art Museums

Art Museums make up the biggest portion of all museum income and revenue in the US, while Children’s Museums make up the smallest portion of all museum income and revenue For the museum data the revenue always lower than income so we could assume that the revenue is what the museum is left over with after deducting taxes and other expenses.

• It is noteworthy that the Art Museums revenue makes up a bigger portion of all museum revenue.
• The Art Museums are retaining a larger portion of its income as opposed to the other museum types.
• If this were not the case then we would expect the distribution to be similar to that of income.

City

• Amado has the highest average income, average revenue and total income.
• Washington has the highest total revenue.
• New York City has the most museums.

The vast majority of cities make under 10 Billion in income and under 2 Billion in revenue from museums.

State

• Arizona (AZ) has the highest average and total income.
• Washington DC (DC)has the highest average revenue.
• California (CA) has the highest total revenue and also the most museums.
• North Dakota (ND) has the lowest average and total income and revenue.
• Utah (UT) has the least museums.

The majority of states make under 10 Billion in income and under 5 Billion in revenue from museums. If we take into context the city level data, it becomes clear that the majority of income and revenue generated from museums in each state comes from just a few cities in that state.