Challenge 2 Instructions

Data wrangling: using group() and summarise()

Code

library(tidyverse)

knitr::opts_chunk$set(echo = TRUE, warning=FALSE, message=FALSE)

Challenge Overview

Today’s challenge is to

1. read in a data set, and describe the data using both words and any supporting information (e.g., tables, etc)
2. provide summary statistics for different interesting groups within the data, and interpret those statistics

Read in the Data

Read in one (or more) of the following data sets, available in the posts/_data folder, using the correct R package and command.

• railroad*.csv or StateCounty2012.xls ⭐
• FAOstat*.csv or birds.csv ⭐⭐⭐
• hotel_bookings.csv ⭐⭐⭐⭐

Code

library(readxl)
railroad <- read_excel(
  "./_data/StateCounty2012.xls",
  skip = 4,
  col_names = c( "STATE","_trash","COUNTY","_trash","TOTAL")) 
  
# deleting trash columns with no values
railroad <- select(railroad, !contains("_trash"))

# removing table notes
railroad <- head(railroad, -4)

# Deleting the total count rows per state
railroad <- filter(railroad, !str_detect(STATE, "Total"))

# setting state Canada as county canada
railroad <- mutate(railroad,COUNTY = ifelse( STATE == "CANADA", "CANADA", COUNTY))

# railroad <-
head(railroad)

# A tibble: 6 × 3
  STATE COUNTY               TOTAL
  <chr> <chr>                <dbl>
1 AE    APO                      2
2 AK    ANCHORAGE                7
3 AK    FAIRBANKS NORTH STAR     2
4 AK    JUNEAU                   3
5 AK    MATANUSKA-SUSITNA        2
6 AK    SITKA                    1

Code

tail(railroad)

# A tibble: 6 × 3
  STATE  COUNTY     TOTAL
  <chr>  <chr>      <dbl>
1 WY     SUBLETTE       3
2 WY     SWEETWATER   196
3 WY     UINTA         49
4 WY     WASHAKIE      10
5 WY     WESTON        37
6 CANADA CANADA       662

Add any comments or documentation as needed. More challenging data may require additional code chunks and documentation.

Describe the data

Using a combination of words and results of R commands, can you provide a high level description of the data? Describe as efficiently as possible where/how the data was (likely) gathered, indicate the cases and variables (both the interpretation and any details you deem useful to the reader to fully understand your chosen data).

Let’s show a brief summary of railroad dataset.

Code

summary(railroad)

    STATE              COUNTY              TOTAL        
 Length:2931        Length:2931        Min.   :   1.00  
 Class :character   Class :character   1st Qu.:   7.00  
 Mode  :character   Mode  :character   Median :  21.00  
                                       Mean   :  87.37  
                                       3rd Qu.:  65.00  
                                       Max.   :8207.00  

Notice that summary is that helpful in term of having general idea of this dataset. Let’s dive into the columns of this data set.

Provide Grouped Summary Statistics

Conduct some exploratory data analysis, using dplyr commands such as group_by(), select(), filter(), and summarise(). Find the central tendency (mean, median, mode) and dispersion (standard deviation, mix/max/quantile) for different subgroups within the data set.

Let’s count the number of different states and counties usign summary, and across in-built functions.

Code

railroad %>%
  summarise(across(c(STATE,COUNTY), n_distinct))

# A tibble: 1 × 2
  STATE COUNTY
  <int>  <int>
1    54   1710

The information is getting to started to make more sense than the provious summary. There are 53 different states plus Canada(as state), in total 54 states; however, the number of counties might be duplicated. Let’s group the data by states and see the number of counties and total employees.

Code

grouped_by_state <- railroad%>%
  group_by(STATE)%>%
  summarise("num_employees" = sum(TOTAL),
            "total_counties"= n())%>%
  arrange(desc(num_employees))
grouped_by_state

# A tibble: 54 × 3
   STATE num_employees total_counties
   <chr>         <dbl>          <int>
 1 TX            19839            221
 2 IL            19131            103
 3 NY            17050             61
 4 NE            13176             89
 5 CA            13137             55
 6 PA            12769             65
 7 OH             9056             88
 8 GA             8605            152
 9 IN             8537             92
10 MO             8419            115
# … with 44 more rows

Now let’s check the counties that has the highest number of employees, using simple division criterion: the number of total employees per state divided by its total number of counties.

Code

grouped_by_state%>%
  group_by(STATE)%>%
  summarise("num_of_employees_per_county" = num_employees/total_counties)%>%
  arrange(desc(num_of_employees_per_county))

# A tibble: 54 × 2
   STATE  num_of_employees_per_county
   <chr>                        <dbl>
 1 CANADA                        662 
 2 DE                            498.
 3 NJ                            397.
 4 CT                            324 
 5 MA                            282.
 6 NY                            280.
 7 DC                            279 
 8 CA                            239.
 9 AZ                            210.
10 PA                            196.
# … with 44 more rows

Explain and Interpret

Be sure to explain why you choose a specific group. Comment on the interpretation of any interesting differences between groups that you uncover. This section can be integrated with the exploratory data analysis, just be sure it is included.

We see Canada as the state that has the county with the highest number of railroad employees. However, this conclusion is a bit off. To conclude this, we assumed the following: each state county is comparable to each other in terms of area (square kilometers), where the number of railroad employees is distributed evenly along the county. Because we defined Canada as a one-county state when doing data wrangling, its num_of_employees_per_county value is the highest: the entire state is the county.

We see in the second place IL state. In this case, the num_of_employees_per_county is close to Canada, which has 103 counties. The division criterion makes more sense for this case since the number of employees is less concentrated than in Canada with one estate.