Challenge 4 Instructions

challenge_4
abc_poll
eggs
fed_rates
hotel_bookings
debt
More data wrangling: pivoting
Author

Kevin Martell Luya

Published

April 23, 2023

Code 
library(tidyverse)
library(readxl)
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 set using both words and any supporting information (e.g., tables, etc)
  2. tidy data (as needed, including sanity checks)
  3. identify variables that need to be mutated
  4. mutate variables and sanity check all mutations

Read in data

Read in one (or more) of the following datasets, using the correct R package and command.

  • abc_poll.csv ⭐
  • poultry_tidy.xlsx or organiceggpoultry.xls⭐⭐
  • FedFundsRate.csv⭐⭐⭐
  • hotel_bookings.csv⭐⭐⭐⭐
  • debt_in_trillions.xlsx ⭐⭐⭐⭐⭐
Code 
debt <- read_xlsx("./_data/debt_in_trillions.xlsx")
debt
# A tibble: 74 × 8
   `Year and Quarter` Mortgage HE Revolvin…¹ Auto …² Credi…³ Stude…⁴ Other Total
   <chr>                 <dbl>         <dbl>   <dbl>   <dbl>   <dbl> <dbl> <dbl>
 1 03:Q1                  4.94         0.242   0.641   0.688   0.241 0.478  7.23
 2 03:Q2                  5.08         0.26    0.622   0.693   0.243 0.486  7.38
 3 03:Q3                  5.18         0.269   0.684   0.693   0.249 0.477  7.56
 4 03:Q4                  5.66         0.302   0.704   0.698   0.253 0.449  8.07
 5 04:Q1                  5.84         0.328   0.72    0.695   0.260 0.446  8.29
 6 04:Q2                  5.97         0.367   0.743   0.697   0.263 0.423  8.46
 7 04:Q3                  6.21         0.426   0.751   0.706   0.33  0.41   8.83
 8 04:Q4                  6.36         0.468   0.728   0.717   0.346 0.423  9.04
 9 05:Q1                  6.51         0.502   0.725   0.71    0.364 0.394  9.21
10 05:Q2                  6.70         0.528   0.774   0.717   0.374 0.402  9.49
# … with 64 more rows, and abbreviated variable names ¹​`HE Revolving`,
#   ²​`Auto Loan`, ³​`Credit Card`, ⁴​`Student Loan`

Briefly describe the data

The data shows year and quaters and different types of loans. Regarding the first column, we can modify the year and quarter format. ## Tidy Data (as needed)

Is your data already tidy, or is there work to be done? Be sure to anticipate your end result to provide a sanity check, and document your work here.

As data it is not tidy, there is work to be done.

Code 
summary(debt)
 Year and Quarter      Mortgage       HE Revolving      Auto Loan     
 Length:74          Min.   : 4.942   Min.   :0.2420   Min.   :0.6220  
 Class :character   1st Qu.: 8.036   1st Qu.:0.4275   1st Qu.:0.7430  
 Mode  :character   Median : 8.412   Median :0.5165   Median :0.8145  
                    Mean   : 8.274   Mean   :0.5161   Mean   :0.9309  
                    3rd Qu.: 9.047   3rd Qu.:0.6172   3rd Qu.:1.1515  
                    Max.   :10.442   Max.   :0.7140   Max.   :1.4150  
  Credit Card      Student Loan        Other            Total       
 Min.   :0.6590   Min.   :0.2407   Min.   :0.2960   Min.   : 7.231  
 1st Qu.:0.6966   1st Qu.:0.5333   1st Qu.:0.3414   1st Qu.:11.311  
 Median :0.7375   Median :0.9088   Median :0.3921   Median :11.852  
 Mean   :0.7565   Mean   :0.9189   Mean   :0.3831   Mean   :11.779  
 3rd Qu.:0.8165   3rd Qu.:1.3022   3rd Qu.:0.4154   3rd Qu.:12.674  
 Max.   :0.9270   Max.   :1.5840   Max.   :0.4860   Max.   :14.957

As we can see, Year and Quarter column summary does not tell us much in terms of statistical data. Let’s fix this.

Identify variables that need to be mutated

Are there any variables that require mutation to be usable in your analysis stream? For example, are all time variables correctly coded as dates? Are all string variables reduced and cleaned to sensible categories? Do you need to turn any variables into factors and reorder for ease of graphics and visualization?

Document your work here.

Code 
debt_quarte_format <- debt %>%
  mutate(date = parse_date_time(`Year and Quarter`,orders = "yq"))%>%
  select(!contains("Year and Quarter"))

debt_quarte_format
# A tibble: 74 × 8
   Mortgage HE Revolvi…¹ Auto …² Credi…³ Stude…⁴ Other Total date               
      <dbl>        <dbl>   <dbl>   <dbl>   <dbl> <dbl> <dbl> <dttm>             
 1     4.94        0.242   0.641   0.688   0.241 0.478  7.23 2003-01-01 00:00:00
 2     5.08        0.26    0.622   0.693   0.243 0.486  7.38 2003-04-01 00:00:00
 3     5.18        0.269   0.684   0.693   0.249 0.477  7.56 2003-07-01 00:00:00
 4     5.66        0.302   0.704   0.698   0.253 0.449  8.07 2003-10-01 00:00:00
 5     5.84        0.328   0.72    0.695   0.260 0.446  8.29 2004-01-01 00:00:00
 6     5.97        0.367   0.743   0.697   0.263 0.423  8.46 2004-04-01 00:00:00
 7     6.21        0.426   0.751   0.706   0.33  0.41   8.83 2004-07-01 00:00:00
 8     6.36        0.468   0.728   0.717   0.346 0.423  9.04 2004-10-01 00:00:00
 9     6.51        0.502   0.725   0.71    0.364 0.394  9.21 2005-01-01 00:00:00
10     6.70        0.528   0.774   0.717   0.374 0.402  9.49 2005-04-01 00:00:00
# … with 64 more rows, and abbreviated variable names ¹​`HE Revolving`,
#   ²​`Auto Loan`, ³​`Credit Card`, ⁴​`Student Loan`

Any additional comments? Now let see the date summary

Code 
summary(debt_quarte_format$date)
                      Min.                    1st Qu. 
"2003-01-01 00:00:00.0000" "2007-07-24 00:00:00.0000" 
                    Median                       Mean 
"2012-02-15 12:00:00.0000" "2012-02-15 06:09:43.7837" 
                   3rd Qu.                       Max. 
"2016-09-08 00:00:00.0000" "2021-04-01 00:00:00.0000"