Introduction

In Part 1, we built an application to geographically explore the 500 Cities Project dataset from the CDC. In this post, we will demonstrate other exploratory data analysis (EDA) techniques for exploring a new dataset. The analysis will be done with R packages data.table, ggplot2 and highcharter.

In this post, you will learn how to:

• Build a boxplot
• Build and plot a correlation matrix
• Build a histogram

Load Dataset

It may take a few minutes to download the data. The data is also available here.

library(data.table)
df <- fread('https://chronicdata.cdc.gov/api/views/9z78-nsfp/rows.csv?accessType=DOWNLOAD')

The dataset contains values from cities across the country for 28 separate health measures. The full measure names are cumbersome so for the remainder of the post, we will use the Short_Question_Text which is an abbreviated form of the full measure name. Here are the full measure names and their corresponding abbreviations for reference.

##                                     Measure            Short_Question_Text
## 1  Current lack of health insurance among a               Health Insurance
## 2    Arthritis among adults aged >=18 Years                      Arthritis
## 3  Binge drinking among adults aged >=18 Ye                 Binge Drinking
## 4  High blood pressure among adults aged >=            High Blood Pressure
## 5  Taking medicine for high blood pressure            Taking BP Medication
## 6  Cancer (excluding skin cancer) among adu           Cancer (except skin)
## 7  Current asthma among adults aged >=18 Ye                 Current Asthma
## 8  Coronary heart disease among adults aged         Coronary Heart Disease
## 9  Visits to doctor for routine checkup wit                 Annual Checkup
## 10 Cholesterol screening among adults aged           Cholesterol Screening
## 11 Fecal occult blood test, sigmoidoscopy,     Colorectal Cancer Screening
## 12 Chronic obstructive pulmonary disease am                           COPD
## 13 Physical health not good for >=14 days a                Physical Health
## 14 Older adult men aged >=65 Years who are  Core preventive services for o
## 15 Older adult women aged >=65 Years who ar Core preventive services for o
## 16 Current smoking among adults aged >=18 Y                Current Smoking
## 17 Visits to dentist or dental clinic among                   Dental Visit
## 18 Diagnosed diabetes among adults aged >=1                       Diabetes
## 19 High cholesterol among adults aged >=18                High Cholesterol
## 20 Chronic kidney disease among adults aged         Chronic Kidney Disease
## 21 No leisure-time physical activity among               Physical Activity
## 22 Mammography use among women aged 50–74 Y                    Mammography
## 23 Mental health not good for >=14 days amo                  Mental Health
## 24     Obesity among adults aged >=18 Years                        Obesity
## 25 Papanicolaou smear use among adult women                 Pap Smear Test
## 26 Sleeping less than 7 hours among adults                           Sleep
## 27      Stroke among adults aged >=18 Years                         Stroke
## 28 All teeth lost among adults aged >=65 Ye                     Teeth Loss

Boxplots

Boxplots graphically depict groups of numerical data through their quartiles. Outliers are shown as points above and below the boxes. This is a good first step in EDA because it shows the range of values associated with each measure. Here we will build a boxplot for each measure in the dataset grouped by category. Each boxplot is built from 500 values, one value for each city.

library(ggplot2)
df_subset <- df[df$GeographicLevel == 'City',]

# grouped boxplot
ggplot(df_subset, aes(x=substr(Short_Question_Text, 1,40), y=Data_Value, fill = Category )) + 
    geom_boxplot() + 
  theme(axis.text.x = element_text(angle = 90, hjust = 1), legend.position = 'bottom') +
  facet_wrap(~Category,scales = "free_x") + xlab('Measure') + ylab('Percentage')

The boxplots show a wide distribution for most of the measures, especially among the preventive measures. Next we will analyze dependence between measures.

Correlation Plot

The second EDA technique we will demonstrate is a correlation plot which is used to show dependence between measures. The correlation between measures is given by a number between -1 and 1 (1 means perfectly positively correlated and -1 means perfectly negatively correlated). The dataset contains values for each measure at three different geographic levels: US, City, and Census Tract. To compute the correlations, we will use data at the Census Tract level which is the smallest geographic level. The correlation plot will compare the measure values at each Census Tract to determine their correlation coefficient.

The steps to compute a correlation matrix in R are as follows:

• Subset the dataset to select only the necessary columns
• Convert the dataset to a wide-format
• Calculate correlations
• Plot the correlation matrix

Subset the dataset

We only want the location (UniqueID), the measure (Short_Question_Text) and the value (Data_Value) columns in our dataset. We only want rows where GeographicLevel is Census Tract. We subset the dataset to remove the rows and columns which aren’t relevant to our analysis.

df_subset <- df[GeographicLevel == 'Census Tract', c('UniqueID','Short_Question_Text', 'Data_Value')]
head(df_subset)

##               UniqueID Short_Question_Text Data_Value
## 1: 0107000-01073000100    Health Insurance       27.6
## 2: 0107000-01073000300    Health Insurance       32.2
## 3: 0107000-01073000400    Health Insurance       31.8
## 4: 0107000-01073000500    Health Insurance       33.7
## 5: 0107000-01073000700    Health Insurance       38.4
## 6: 0107000-01073000800    Health Insurance       26.5

Convert to Wide Format

The R correlation function cor() requires that the dataset is in wide-format. The dcast function from the data.table package simplies the task of converting the dataset into wide-format. Essentially, the dcast function creates a new column for each unique Short_Question_Text (i.e. it ‘casts’ the Short_Question_Text into wide format) and inputs the Data_Value as the value for that column. The UniqueID is designated as the row name because it is not actually part of the correlation calculation.

df_wide <- dcast(df_subset, UniqueID ~ Short_Question_Text, value.var = 'Data_Value')
row.names(df_wide) <- df_wide$UniqueID 
df_wide$UniqueID <- NULL
df_wide <- df_wide[complete.cases(df_wide),] #Removes any rows with NA values
head(df_wide[,1:5])

##    Annual Checkup Arthritis Binge Drinking COPD Cancer (except skin)
## 1:           76.6      34.0           10.3 11.2                  5.5
## 2:           74.0      32.8           11.0 11.1                  5.0
## 3:           77.5      37.2            9.3 12.9                  5.6
## 4:           78.7      40.1            8.4 14.4                  6.1
## 5:           78.4      40.2            7.4 15.6                  6.0
## 6:           81.1      40.7            8.8 12.5                  7.0

Compute Correlation Matrix

Now that the data is in the appropriate format, we can use the R function cor() to compute the correlations. We apply the cor() function to the wide-format data to compute the correlation between measures.

cor_plot <- cor(df_wide)
cor_plot <- round(cor_plot, 2)
head(cor_plot[,1:5])

##                       Annual Checkup Arthritis Binge Drinking  COPD
## Annual Checkup                  1.00      0.57          -0.35  0.29
## Arthritis                       0.57      1.00          -0.61  0.81
## Binge Drinking                 -0.35     -0.61           1.00 -0.63
## COPD                            0.29      0.81          -0.63  1.00
## Cancer (except skin)            0.47      0.65          -0.25  0.20
## Cholesterol Screening           0.65      0.44          -0.14 -0.03
##                       Cancer (except skin)
## Annual Checkup                        0.47
## Arthritis                             0.65
## Binge Drinking                       -0.25
## COPD                                  0.20
## Cancer (except skin)                  1.00
## Cholesterol Screening                 0.75

As you can see, the output is a dataframe with values between 1 and -1. We will plot this result to make it easier to understand and analyze.

Plot the Correlation Matrix with Highcharts

Highcharter is an R wrapper for the Highcharts library. Highcharts is a data visualization library which makes it simple to develop interactive charts. The hchart() function can be applied to the R correlation function output to build a correlation plot.

library(highcharter)
hchart(cor_plot)

Reorder the Correlation Plot

While this is better than the tabular form, we can make it more clear by grouping correlated features together. We will use a helper function which I found here to reorder the correlation plot.

reorder_cormat <- function(cormat){
# Use correlation between variables as distance
dd <- as.dist((1-cormat)/2)
hc <- hclust(dd)
cormat <-cormat[hc$order, hc$order]
}

cor_plot <- reorder_cormat(cor_plot)
hchart(cor_plot)

The correlation plot yields some interesting insights. The first thing I noticed is that binge drinking is negatively correlated with many of the poor health outcomes (e.g. obesity, high cholesterol, stroke, diabetes). My first thought on this is that binge drinking is more common among young people who generally don’t have as many health issues as older people. However we would need to analyze data on binge drinking more extensively to derive solid conclusions.

Another insight is lack of health insurance is positively correlated with negative health outcomes (i.e. people without health insurance experience worse health outcomes). This is interesting although not surprising. In the next section, we will drill into the health insurance measure to analyze how it differs across the nation.

Health Insurance Histograms

First, we will compute the summary statistics and build a histogram for health insurance across the dataset by city.

df_health <- subset(df, Short_Question_Text == 'Health Insurance')
df_health <- subset(df_health, GeographicLevel == 'City' & DataValueTypeID == 'AgeAdjPrv')
summary(df_health$Data_Value)

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##    4.10   13.28   17.40   18.21   22.12   49.00

There is a wide range of health insurance coverage across the cities in the dataset with a difference between the best and worst of about 45 percent.

hist(df_health$Data_Value, col = 'gray', breaks = 20)

The histogram shows the data is skewed to the right.

Next, we will find the cities which have the highest percentage of their population lacking health insurance.

library(ggplot2)
df_health <- subset(df, Short_Question_Text == 'Health Insurance')
df_health <- subset(df_health, GeographicLevel == 'City' & DataValueTypeID == 'AgeAdjPrv')
df_health <- df_health[order(df_health$Data_Value, decreasing = TRUE),]
df_health$City <- paste0(df_health$CityName, ',', df_health$StateAbbr)

ggplot(df_health[1:20,], hcaes(x = reorder(City, Data_Value), y = Data_Value, fill = Data_Value)) +
geom_bar(stat = "identity",col = "black")+
  theme(axis.text.x = element_text(angle = 90, hjust = 1), legend.position = 'none') +
  ggtitle('The 20 cities in the USA with the lowest percentage of health insurance', subtitle = 'Texas leads the way with 6 of the top 7') +
  xlab('City') + ylab('Percent Uninsured')

Pharr, TX leads the way with nearly half of their population lacking health insurance and Texas leads the way with 6 of the top 7 cities.

However Texas is a large state and they may simply have more cities in the dataset than other states. Next we will calculate the ratio of cities from each state in the top 100 with regards to lack of health insurance in comparison to the total number of cities in the dataset. The steps to complete this calculation are:

• Select the top 100 cities by lack of health insurance
• Count cities in the top 100 for each state
• Count the total cities in the dataset for each state
• Divide the number of cities in the top 100 by the total number of cities in the dataset

Select the top 100 cities by lack of health insurance.

#Subset dataset
df_health <- subset(df, Short_Question_Text == 'Health Insurance')
df_health <- subset(df_health, GeographicLevel == 'City' & DataValueTypeID == 'AgeAdjPrv')
df_health <- df_health[order(df_health$Data_Value, decreasing = TRUE),] #Order rows by Data_Value
df_health_100 <- df_health[1:100,] #Select only the top 100 rows
head(df_health_100[,1:5])

##    Year StateAbbr StateDesc    CityName GeographicLevel
## 1: 2014        TX     Texas       Pharr            City
## 2: 2014        TX     Texas Brownsville            City
## 3: 2014        TX     Texas      Laredo            City
## 4: 2014        FL   Florida     Hialeah            City
## 5: 2014        TX     Texas     Mission            City
## 6: 2014        TX     Texas    Edinburg            City

Count the number of cities in the top 100 by state using the aggregate function.

agg_100 <- aggregate(df_health_100$Year, by = list(df_health_100$StateDesc), FUN = length) 
agg_100 <- agg_100[order(agg_100$x, decreasing = TRUE),]
head(agg_100)

##       Group.1  x
## 21      Texas 29
## 3  California 25
## 6     Florida 10
## 16 New Jersey  7
## 7     Georgia  4
## 12  Louisiana  3

Plot the results.

library(ggplot2)
ggplot(agg_100, aes(x = reorder(Group.1, x), y = x, fill = x)) +
geom_bar(stat = "identity",col = "black")+
  theme(axis.text.x = element_text(angle = 90, hjust = 1), legend.position = 'none') + 
  ggtitle('Number of cities in top 100 for percent uninsured by state', subtitle = 'Texas and California lead the way by a wide margin') +
  xlab('State') + ylab('Percent Uninsured')

We can see Texas and California lead the way with the most cities in the top 100. This makes sense because they are two of the largest states.

Count the number of cities in the dataset for each state.

df_health <- subset(df, Short_Question_Text == 'Health Insurance')
df_health <- subset(df_health, GeographicLevel == 'City' & DataValueTypeID == 'AgeAdjPrv')
agg <- aggregate(df_health$Year, by = list(df_health$StateDesc), FUN = length)
agg <- agg[order(agg$x, decreasing = TRUE),]

#Plot the top 20
ggplot(agg[1:20,], aes(x = reorder(Group.1, x), y = x, fill = x)) +
geom_bar(stat = "identity",col = "black") +
  theme(axis.text.x = element_text(angle = 90, hjust = 1), legend.position = 'none') + 
  ggtitle('The number of cities from each state in the 500 Cities dataset (Top 20)', subtitle = 'California leads the way by a wide margin') + 
  xlab('State') + ylab('Number of Cities') 

California and Texas also lead the way with the most cities in the top 100. Now, we will divide the number of cities in the top 100 by the total number of cities in the dataset to create a more accurate comparison of states.

agg_merged <- setNames(merge(agg_100, agg, by = 'Group.1'), c('city', 'health_cities', 'total_cities'))
agg_merged$Ratio <- agg_merged$health_cities / agg_merged$total_cities
head(agg_merged)

##          city health_cities total_cities      Ratio
## 1     Arizona             1           12 0.08333333
## 2    Arkansas             1            5 0.20000000
## 3  California            25          121 0.20661157
## 4    Colorado             1           14 0.07142857
## 5 Connecticut             1            8 0.12500000
## 6     Florida            10           33 0.30303030

Plot the ratio to see how the states compare.

ggplot(agg_merged, aes(x = reorder(city, Ratio), y = Ratio, fill = Ratio)) +
geom_bar(stat = 'identity', color = 'black')+
  theme(axis.text.x = element_text(angle = 90, hjust = 1), legend.position = 'none') +
  ggtitle('Ratio of number of cities in the top 100 over number of cities in the dataset') +
  xlab('State')

The plot now paints a different picture then the raw data. New Jersey leads the way with nearly 80% of their cities in the top 100.

Th purpose of this post was to demonstrate common exploratory data analysis techniques. The goal of exploratory data analysis is to provide an understanding of the dataset and generate questions to analyze further. From this analysis, I would be curious as to why New Jersey lacks health insurance. I would also be curious if the lack of health insurance actually causes poor health outcomes, or if there are other factors in play.

As you delve deeper into datasets, you will almost always generate questions that you didn’t think of prior to starting the analyis. This is the one of the key values of EDA.