Challenge 2

challenge_2

Data wrangling: using group() and summarise()

Author

Priya Marla

Published

December 27, 2022

Code

library(tidyverse)

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

Challenge Overview

Today’s challenge is to

read in a data set, and describe the data using both words and any supporting information (e.g., tables, etc)
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.xlsx ⭐
FAOstat*.csv ⭐⭐⭐
hotel_bookings ⭐⭐⭐⭐

Code

#loading the data from posts/_data folder
railroad <- read_csv("_data/railroad_2012_clean_county.csv")
#executed dim to know the dimentions i.e number of rows and columns of the dataset
dim(railroad)

[1] 2930    3

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).

Code

#executed colnames to get the column names in the dataset
colnames(railroad)

[1] "state"           "county"          "total_employees"

Code

#executed head to get the first 5 rows of the dataset
head(railroad)

# A tibble: 6 × 3
  state county               total_employees
  <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

#getting the distinct number of states and counties in the dataset
railroad%>%
  select(state)%>%
  n_distinct(.)

[1] 53

Code

railroad%>%
  select(county)%>%
  n_distinct(.)

[1] 1709

Code

#maximum of total employees in the dataset
max_count <- summarise(railroad, max(total_employees))
max_count

# A tibble: 1 × 1
  `max(total_employees)`
                   <dbl>
1                   8207

Code

#minimum of total employees in the dataset
min_count <- summarise(railroad, min(total_employees))
min_count

# A tibble: 1 × 1
  `min(total_employees)`
                   <dbl>
1                      1

Code

#mean of total employees in the dataset
mean_count <- summarise(railroad, mean(total_employees))
mean_count

# A tibble: 1 × 1
  `mean(total_employees)`
                    <dbl>
1                    87.2

By reading the data from “railroad_2012_clean_county.csv” dataset, I can see that this dataset describes the statistics of number of employees working in the different states and counties for the railroad in the year 2012. There are about 2930 rows and 3 columns where each row represents the total number of employees in each state and county. There a total of 53 states and 1709 counties. The maximum number of employee count recorded is 8207 and minimum count is 1. The average of total_employees in the entire dataset is 87.17816.

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.

Code

#total employees in all states and counties
summarise(railroad, sum(total_employees, na.rm = TRUE))

# A tibble: 1 × 1
  `sum(total_employees, na.rm = TRUE)`
                                 <dbl>
1                               255432

Code

#total employees in county addison
railroad %>%
  filter(county == "ADDISON") %>%
  select_all() %>%
  summarise(total_employees = sum(total_employees, na.rm = TRUE))

# A tibble: 1 × 1
  total_employees
            <dbl>
1               8

Code

#sum of total employees of each state
railroad %>%
  group_by(state) %>%
  select_all() %>%
  summarise(sum_total_employees = sum(total_employees)) %>%
  arrange(desc(sum_total_employees), .by_group=FALSE)

# A tibble: 53 × 2
   state sum_total_employees
   <chr>               <dbl>
 1 TX                  19839
 2 IL                  19131
 3 NY                  17050
 4 NE                  13176
 5 CA                  13137
 6 PA                  12769
 7 OH                   9056
 8 GA                   8605
 9 IN                   8537
10 MO                   8419
# … with 43 more rows

Code

#mean of total employees of each state
railroad %>%
  group_by(state) %>%
  select_all() %>%
  summarise(mean_total_employees = mean(total_employees)) %>%
  arrange(desc(mean_total_employees), .by_group=FALSE)

# A tibble: 53 × 2
   state mean_total_employees
   <chr>                <dbl>
 1 DE                    498.
 2 NJ                    397.
 3 CT                    324 
 4 MA                    282.
 5 NY                    280.
 6 DC                    279 
 7 CA                    239.
 8 AZ                    210.
 9 PA                    196.
10 MD                    196.
# … with 43 more rows

Code

#median of total employees of each state
railroad %>%
  group_by(state) %>%
  select_all() %>%
  summarise(median_total_employees = median(total_employees))%>%
  arrange(desc(median_total_employees), .by_group=FALSE)

# A tibble: 53 × 2
   state median_total_employees
   <chr>                  <dbl>
 1 NJ                      296 
 2 DC                      279 
 3 MA                      271 
 4 DE                      158 
 5 CT                      125 
 6 MD                      108.
 7 AZ                       94 
 8 PA                       85 
 9 NY                       71 
10 CA                       61 
# … with 43 more rows

Code

#min of total employees of each state
railroad %>%
  group_by(state) %>%
  select_all() %>%
  summarise(min_total_employees = min(total_employees)) %>%
  arrange(desc(min_total_employees), .by_group=FALSE)

# A tibble: 53 × 2
   state min_total_employees
   <chr>               <dbl>
 1 DC                    279
 2 DE                     62
 3 MA                     44
 4 CT                     26
 5 NJ                     19
 6 RI                      8
 7 NY                      5
 8 AZ                      3
 9 IN                      3
10 OH                      3
# … with 43 more rows

Code

#max of total employees of each state
railroad %>%
  group_by(state) %>%
  select_all() %>%
  summarise(max_total_employees = max(total_employees)) %>%
  arrange(desc(max_total_employees), .by_group=FALSE)

# A tibble: 53 × 2
   state max_total_employees
   <chr>               <dbl>
 1 IL                   8207
 2 TX                   4235
 3 NE                   3797
 4 NY                   3685
 5 VA                   3249
 6 FL                   3073
 7 CA                   2888
 8 MO                   2055
 9 IN                   1999
10 PA                   1649
# … with 43 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.

Data Analysis has been performed on the column state. Here each row represents a state and there are 53 unique states in the data set. State “TX” has recorded highest number of employees i.e 19839 and least is “AP” with 1 employee. Even though “TX” recorded highest employee count, the minimum number of employee in one of the counties is 1 and highest is 4235 making the average as 89.76. Highest average of employees are recorded in state “DE” and least in “AP”. The minimum employees in most of the states in a particular county is 1 which is way less than state mean.