DATA 608 – Knowledge and Visual Analytics: Assignment 1

Principles of Data Visualization and Introduction to ggplot2

I have provided you with data about the 5,000 fastest growing companies in the US, as compiled by Inc. magazine. lets read this in:

inc <- read.csv("https://raw.githubusercontent.com/charleyferrari/CUNY_DATA_608/master/module1/Data/inc5000_data.csv", header= TRUE)

And lets preview this data:

head(inc)

##   Rank                         Name Growth_Rate   Revenue
## 1    1                         Fuhu      421.48 1.179e+08
## 2    2        FederalConference.com      248.31 4.960e+07
## 3    3                The HCI Group      245.45 2.550e+07
## 4    4                      Bridger      233.08 1.900e+09
## 5    5                       DataXu      213.37 8.700e+07
## 6    6 MileStone Community Builders      179.38 4.570e+07
##                       Industry Employees         City State
## 1 Consumer Products & Services       104   El Segundo    CA
## 2          Government Services        51     Dumfries    VA
## 3                       Health       132 Jacksonville    FL
## 4                       Energy        50      Addison    TX
## 5      Advertising & Marketing       220       Boston    MA
## 6                  Real Estate        63       Austin    TX

summary(inc)

##       Rank          Name            Growth_Rate         Revenue         
##  Min.   :   1   Length:5001        Min.   :  0.340   Min.   :2.000e+06  
##  1st Qu.:1252   Class :character   1st Qu.:  0.770   1st Qu.:5.100e+06  
##  Median :2502   Mode  :character   Median :  1.420   Median :1.090e+07  
##  Mean   :2502                      Mean   :  4.612   Mean   :4.822e+07  
##  3rd Qu.:3751                      3rd Qu.:  3.290   3rd Qu.:2.860e+07  
##  Max.   :5000                      Max.   :421.480   Max.   :1.010e+10  
##                                                                         
##    Industry           Employees           City              State          
##  Length:5001        Min.   :    1.0   Length:5001        Length:5001       
##  Class :character   1st Qu.:   25.0   Class :character   Class :character  
##  Mode  :character   Median :   53.0   Mode  :character   Mode  :character  
##                     Mean   :  232.7                                        
##                     3rd Qu.:  132.0                                        
##                     Max.   :66803.0                                        
##                     NA's   :12

Think a bit on what these summaries mean. Use the space below to add some more relevant non-visual exploratory information you think helps you understand this data:

Top Industries by Total Revenue:

# distinct industries 
inc %>%
  summarize(DISTINCT_INDUSTRIES = n_distinct(Industry))

##   DISTINCT_INDUSTRIES
## 1                  25

sum1 <- inc %>%
  group_by(Industry) %>%
  summarize(NUM_COMPANIES = n(),
            PCT_TOTAL_COMPANIES = n()/nrow(.),
            INDUSTRY_REVENUE = sum(Revenue),
            PCT_TOTAL_REVENUE = sum(Revenue)/ sum(inc$Revenue),
            INDUSTRY_EMPLOYEES = sum(Employees, na.rm = TRUE)) 

# top 10 industries
kable(
  sum1 %>%
    arrange(desc(INDUSTRY_REVENUE)) %>%
    top_n(.,10)
)

Industry	NUM_COMPANIES	PCT_TOTAL_COMPANIES	INDUSTRY_REVENUE	PCT_TOTAL_REVENUE	INDUSTRY_EMPLOYEES
Business Products & Services	482	0.0963807	26367900000	0.1093374	117357
IT Services	733	0.1465707	20681300000	0.0857573	102788
Health	355	0.0709858	17863400000	0.0740725	82430
Consumer Products & Services	203	0.0405919	14956400000	0.0620183	45464
Financial Services	260	0.0519896	13150900000	0.0545316	47693
Food & Beverage	131	0.0261948	12911300000	0.0535381	65911
Manufacturing	256	0.0511898	12684000000	0.0525956	43942
Human Resources	196	0.0391922	9246100000	0.0383400	226980
Software	342	0.0683863	8140600000	0.0337559	51262
Security	73	0.0145971	3812800000	0.0158102	41059

There are 25 distinct industries in the data set.
The top industry by total revenue is Business Products & Services, which accounts for ~9.6% of the top 5000 companies and ~11% of the total revenue.
Three of the top 10 industries relate to tech and security.

Top States by Total Revenue:

sum2 <- inc %>%
  group_by(State) %>%
  summarize(NUM_COMPANIES = n(),
            PCT_TOTAL_COMPANIES = n()/nrow(.),
            INDUSTRY_REVENUE = sum(Revenue),
            PCT_TOTAL_REVENUE = sum(Revenue)/ sum(inc$Revenue),
            INDUSTRY_EMPLOYEES = sum(Employees, na.rm = TRUE)) 

# top 5 states
kable(
  sum2 %>%
  arrange(desc(INDUSTRY_REVENUE)) %>%
  top_n(.,5)
)

State	NUM_COMPANIES	PCT_TOTAL_COMPANIES	INDUSTRY_REVENUE	PCT_TOTAL_REVENUE	INDUSTRY_EMPLOYEES
IL	273	0.0545891	33244300000	0.1378511	103266
CA	701	0.1401720	23457900000	0.0972707	161219
TX	387	0.0773845	22164200000	0.0919063	90765
NY	311	0.0621876	18260400000	0.0757187	84370
DE	16	0.0031994	676800000	0.0028064	68544

The top state by revenue is Illinois, and it accounts for almost 42% more revenue and less than half the number of companies than the second highest state, California.
Only ~5% of the total companies come from Illinois, but they account for almost 14% of the total revenue.

Question 1

Create a graph that shows the distribution of companies in the dataset by State (ie how many are in each state). There are a lot of States, so consider which axis you should use. This visualization is ultimately going to be consumed on a ‘portrait’ oriented screen (ie taller than wide), which should further guide your layout choices.

p <- ggplot(inc, aes(x=fct_rev(fct_infreq(State)))) + 
  geom_histogram(stat="count") + 
  ggtitle("Distribution of Top 5000 Companies by State") +
  xlab("State") + ylab("Number of Companies") + 
  coord_flip() 

p

Question 2

Lets dig in on the state with the 3rd most companies in the data set. Imagine you work for the state and are interested in how many people are employed by companies in different industries. Create a plot that shows the average and/or median employment by industry for companies in this state (only use cases with full data, use R’s complete.cases() function.) In addition to this, your graph should show how variable the ranges are, and you should deal with outliers.

First, let’s subset our data to state with the 3rd most companies. We’ll also eliminate any records that have null values. We can see that the state we’re working with is NY:

thirdState <- inc %>% 
  add_count(State) %>% 
  arrange(desc(n)) %>%
  filter(State == unique(State)[3]) %>%
  select(-n) 

thirdState <- thirdState[complete.cases(thirdState), ]

unique(thirdState$State)

## [1] "NY"

Next, let’s take a look at each Industry separately. This will help us to identify any outliers (dots above or below the body of the boxplot). We can see that many of the Industries have some outliers in the dataset.

vals <- ggplot(thirdState %>% filter(complete.cases(.) == TRUE), aes(x=Industry, y=Employees)) + 
    geom_boxplot() +
  facet_wrap(~Industry, scale="free")

vals

Now we will define a function to remove the outliers from each Industry separately and apply it to our dataframe. We can confirm that our outliers are removed by checking the rowcounts of the original NY dataset to the new dataset.

# function to remove outliers
removeOutliers <- function(df){
  outliers <- boxplot.stats(df$Employees)$out
  df <- df %>%
    filter(!Employees %in% outliers)
  return(df)
}

# group data into separate dataframes based on Industry
groupedData <- thirdState %>% 
  group_by(Industry) %>%
  group_split()

# apply removeOutliers function to dataframe
finalData <- do.call("rbind", lapply(groupedData, removeOutliers))

paste0('Num rows in original dataset: ', nrow(thirdState), ' | Num rows in new dataset: ', nrow(finalData))

## [1] "Num rows in original dataset: 311 | Num rows in new dataset: 280"

Finally, we can create a plot that shows the average employment by industry:

ggplot(finalData, aes(x=reorder(Industry, Employees, mean), y=Employees)) + 
  stat_summary(fun="mean", geom="bar") + 
  ggtitle(paste0("Average number of employees by Industry for ",unique(finalData$State))) +
  xlab("Industry") + ylab("Employee Count") + 
  coord_flip() + 
  stat_summary(aes(label=round(..y..,2)), fun=mean, geom="text", vjust = 0.5, hjust = -0.05) +
  expand_limits(y = 300)

And similarly, we can create a plot that shows the median employment by industry:

# median  
ggplot(finalData, aes(x=reorder(Industry, Employees, median), y=Employees)) + 
  stat_summary(fun="median", geom="bar") + 
  ggtitle(paste0("Median number of employees by Industry for ",unique(finalData$State))) +
  xlab("Industry") + ylab("Employee Count") + 
  coord_flip() + 
  stat_summary(aes(label=round(..y..,2)), fun=median, geom="text", vjust = 0.5, hjust = -0.05)

Question 3

Now imagine you work for an investor and want to see which industries generate the most revenue per employee. Create a chart that makes this information clear. Once again, the distribution per industry should be shown.

First, we can group our data by Industry and calculate the revenue by employee:

revByEmployee <- inc %>%
  group_by(Industry) %>%
  summarize(TOTAL_REVENUE = sum(Revenue, na.rm=TRUE),
            TOTAL_EMPLOYEES = sum(Employees, na.rm = TRUE),
            REV_PER_EMPLOYEE = sum(Revenue, na.rm=TRUE)/ sum(Employees, na.rm = TRUE)) %>%
  arrange(desc(REV_PER_EMPLOYEE)) %>%
  ungroup()

revByEmployee

## # A tibble: 25 x 4
##    Industry                     TOTAL_REVENUE TOTAL_EMPLOYEES REV_PER_EMPLOYEE
##    <chr>                                <dbl>           <int>            <dbl>
##  1 Computer Hardware              11885700000            9714         1223564.
##  2 Energy                         13771600000           26437          520921.
##  3 Construction                   13174300000           29099          452741.
##  4 Logistics & Transportation     14840500000           39994          371068.
##  5 Consumer Products & Services   14956400000           45464          328972.
##  6 Insurance                       2337900000            7339          318558.
##  7 Manufacturing                  12684000000           43942          288653.
##  8 Retail                         10257400000           37068          276718.
##  9 Financial Services             13150900000           47693          275741.
## 10 Environmental Services          2638800000           10155          259852.
## # ... with 15 more rows

Now, we can plot this information:

ggplot(revByEmployee, aes(x=reorder(Industry, REV_PER_EMPLOYEE), y=REV_PER_EMPLOYEE)) + 
  geom_bar(stat="identity") +
  ggtitle(paste0("Average revenue per employee by Industry for ",unique(finalData$State))) +
  xlab("Industry") + ylab("Revenue") + 
  coord_flip() + 
  stat_summary(aes(label=round(..y..,2)), geom="text", vjust = 0.5, hjust = -0.05) +
  expand_limits(y = 1500000)

DATA 608 – Knowledge and Visual Analytics: Assignment 1

Amber Ferger

9/6/2020

Question 1

Question 2

Question 3