Introduction: Fortune 500 Companies R

Overview

The purpose of this introductory notebook is to get our feet wet with R, starting off with a simple, but certainly not boring, dataset. We will be using the Fortune 500 companies list from 1955-2005.

We’ll do some simple plotting with ggplot to explore the data in greater detail and addressing issues with the data as we find them.

library(tidyverse)
library(DT)

Data

The external data used in this notebook can be obtained from: https://www.dataquest.io/blog/large_files/fortune500.csv

The CSV file contains information about fortune 500 companies spanning the years 1955 - 2005.
There are 25500 rows representing 51 (years) sets of 500 (companies).

The provided columns include:

• Year - year that the fortune 500 list was published
• Rank - company’s position on the fortune 500 list
• Company - name of the company
• Revenue (in millions) - company’s revenue for that year
• Profit (in millions) - company’s profit for that year

f500 <- read.csv('data/fortune500.csv')
# f500 <- datatable(f500, 
#          #extensions = c('FixedColumns',"FixedHeader"),
#           options = list(scrollX = TRUE, 
#                          paging=TRUE,
#                          fixedHeader=FALSE))
# #(f500 <- read.csv('data/fortune500.csv'))
head(f500, n=20)

# Remove the trailing peroid from column names 
colnames(f500) <- gsub('\\.$','',colnames(f500))

Exploratory Data Analysis

The dataset has a handful of missing values which we will need to deal with and some interesting duplicates that may or may not be data errors.

all(complete.cases(f500))

## [1] TRUE

str(f500)

## 'data.frame':    25500 obs. of  5 variables:
##  $ Year                : int  1955 1955 1955 1955 1955 1955 1955 1955 1955 1955 ...
##  $ Rank                : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ Company             : Factor w/ 1887 levels "3Com","3M","A-T-O",..: 724 618 1727 718 607 369 145 787 1171 561 ...
##  $ Revenue..in.millions: num  9824 5661 3250 2959 2511 ...
##  $ Profit..in.millions : Factor w/ 6977 levels "-0.1","-0.2",..: 6486 5688 2940 3143 2864 2733 1341 2774 2787 4313 ...

The complete cases statement here is telling us that there is no missing values, however, something is amiss in the Profit..in.millions field. It was read in as a factor rather than a double, a strong indication that some other value may be used as a stand-in for N/A

summary(f500$Profit..in.millions)

##    N.A.       4       3     5.7       6     3.1     2.6       5       7 
##     369      73      71      67      67      66      65      65      65 
##     3.7     4.1     4.7     3.4     3.6     4.8     6.1     4.3     6.6 
##      64      64      64      63      62      62      62      60      60 
##     2.8     4.2     6.2     2.4     3.5     9.4     2.7     4.9     5.4 
##      59      59      59      58      58      58      56      56      56 
##     5.8     9.6     2.2     5.5     6.5     5.1     1.9     3.3     3.8 
##      56      56      55      55      55      54      53      53      53 
##     4.6       8     4.4     5.2     6.9     7.3    10.1     2.5     3.2 
##      53      53      52      52      52      52      51      51      51 
##     5.6     7.4    12.1     6.3     6.4     8.1     8.4     2.9     7.1 
##      51      51      50      50      50      50      50      49      49 
##     7.2       9     4.5     7.8    10.2    11.1     3.9     7.7     7.9 
##      49      49      48      48      47      47      47      47      47 
##       2     2.3     5.9    10.4    22.1     6.7     7.6     8.2     1.4 
##      46      46      46      45      45      45      45      45      44 
##     1.8    10.8    11.3     8.9    13.1      14    15.2     7.5     8.3 
##      44      44      44      44      43      43      43      43      43 
##     8.7     1.3    10.7     5.3      13      19     9.1       1     1.7 
##      43      42      42      42      41      41      41      40      40 
##    14.1      15     8.8     9.5     1.1     1.2    12.4     9.2     1.6 
##      40      40      40      40      39      39      39      39      38 
## (Other) 
##   20152

The string N.A. is likely the root of this issue

f500 %>% filter(Profit..in.millions == 'N.A.')

f500$Profit..in.millions <- as.double(gsub('N\\.A\\.',0.0,f500$Profit..in.millions))

Here, we can use gsub again to replace all the N.A. values with 0.0, casting it to a double

sum(table(f500$Company) > 50)

## [1] 74

There are 74 companies who have been included all 51 years

head(f500 %>% count(Company, sort=TRUE), n=20)

It appears that CBS and OfficeMax, have duplicate entries in certain years, let’s see if we can track down the erroneous years.

f500 %>% 
  filter(Company %in% c("CBS", "OfficeMax")) %>% 
  group_by(Year, Company) %>% 
  count() %>% 
  filter(n > 1)

CBS is duplicated in years 1972-81, 1995 and OfficeMax is duplicated between 1996-2003. Since we only can assume that this is an error and cannot know for sure without understanding the data collection process, we will leave these entries be for now. It is also possible that these duplication are due to a merger between companies in later years, given that all company names are represented by their 2005 equivalent.

f500 %>% 
  filter(Company %in% c('Kellogg','3M','Pfizer')) %>% # Choose 3 arbitrary companies
  ggplot(mapping = aes(Year, Rank, color=Company)) +
  geom_line(aes(linetype=Company), size=1.2) +
  labs(title='Fortune 500 Rank - 3M, Kellogg, and Pfizer (1955-2005)') +
  scale_y_continuous(breaks = seq(0, 300, by=30), limits=c(300,1), trans = 'reverse') +
  geom_vline(xintercept = 1994)

head(sprdf <-f500 %>% 
  filter(Year %in% 1993:1996) %>% 
  select(c(Year,Company,Rank)) %>% 
  spread(Year, Company), n=30)

length(setdiff(sprdf$`1994`,sprdf$`1995`))

## [1] 291

length(setdiff(sprdf$`1993`,sprdf$`1994`))

## [1] 33

f55.99 <- f500 %>% 
  filter(Year %in% 1955:1999) %>% 
  select(c(Year,Company,Rank)) %>% 
  spread(Year, Company)

yoy.diff <- vector()
for(i in 2:45) yoy.diff <- c(yoy.diff, length(setdiff(f55.99[,i],f55.99[,i+1])))
yoy <- data.frame(cbind(Year=as.numeric(colnames(f55.99[3:46])),diff.from.prev = as.numeric(yoy.diff)))

yoy %>% 
  ggplot(aes(Year, diff.from.prev)) +
  geom_line() + geom_point() +
  labs(title="Number of differing companies compared to previous year") +
  scale_x_continuous(breaks = seq(1955,2000,5)) + 
  scale_y_continuous(breaks = seq(0,300,50))

Conclusions

This notebook took a brief look at the Fortune 500 companies dataset (1955-2005). Our analysis led to several interesting discoveries, namely, we found that CBS and OfficeMax are included twice in certain years, and from 1994 to 1995 there was a dramatic shift in included companies. Despite the chosen dataset being relatively simple, we still managed to extract some useful information out of it. We did not even use the revenue or profits columns, so there is still a lot of room for exploration.