Challenge 4

challenge_4

tidyverse

readxl

lubridate

summarytools

debt_in_trillions

Author

Saaradhaa M

Published

August 21, 2022

Code

library(tidyverse)
library(readxl)
library(lubridate)
library(summarytools)

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

Reading in and describing data

Just trying out pop-up text for fun!

I will be working with the debt in trillions dataset.

Code

# read in data.
debt <- read_excel("_data/debt_in_trillions.xlsx")

# dimensions, column names and basic descriptives.
dim(debt)

[1] 74  8

Code

colnames(debt)

[1] "Year and Quarter" "Mortgage"         "HE Revolving"     "Auto Loan"       
[5] "Credit Card"      "Student Loan"     "Other"            "Total"

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

Code

# check for missing data.
apply(debt, 2, anyNA)

Year and Quarter         Mortgage     HE Revolving        Auto Loan 
           FALSE            FALSE            FALSE            FALSE 
     Credit Card     Student Loan            Other            Total 
           FALSE            FALSE            FALSE            FALSE

This dataset has 74 rows and 8 columns. Except for Year and Quarter, all other columns are in numeric format. A quick Google search shows that each row represents different types of debt in the US for that particular year and quarter. Looking at the median values for each column, mortgage debt seems to be the greatest in value (8.412 trillion!), while other debt has the lowest median value (0.3921). There also seems to be no missing data.

Tidy and Mutate Data

The data needs to be tidied:

Year and Quarter should be split into two separate columns.
For consistency, all debt-type columns should have the same number of decimal places.
The data would be easier to read if we pivoted the debt-type columns (N = 7) such that we only have to look at Year, Quarter, Debt Type and Value.

At the end of the tidying process, we should have 74 * 7 = 514 rows, 4 columns and all decimals to 3 decimal places.

Code

# separate the two columns.
debt <- separate(debt, "Year and Quarter", into=c("year", "quarter"), sep=":Q")

# mutate for consistent decimal places.
debt <- mutate(debt, across(where(is.numeric), ~ round(., 3)))

# pivot.
debt <- debt %>% pivot_longer(cols = where(is.numeric), names_to = "debt_type", values_to = "amount")

# check new dimensions.
dim(debt)

[1] 518   4

Great! Now we have tidy data, where each case is represented by a particular year, quarter, debt_type and associated amount. Moving on to changing column types - we can change quarter to numeric format, debt_type into a factor and year into date format.

Code

# change "quarter" to numeric.
debt$quarter <- as.numeric(debt$quarter)

# change "debt_type" to categories.
debt$debt_type <- as.factor(debt$debt_type)

# change "year" to date.
debt$year <- as.Date(debt$year, format = "%y")
debt$year <- year(debt$year)

# check new dataset.
print(summarytools::dfSummary(debt, varnumbers = FALSE, plain.ascii = FALSE, graph.magnif = 0.50, style = "grid", valid.col = FALSE), 
      method = 'render', table.classes = 'table-condensed')

Data Frame Summary

debt

Dimensions: 518 x 4
Duplicates: 0

Variable

Stats / Values

Freqs (% of Valid)

Graph

Missing

year [numeric]

Mean (sd) : 2011.8 (5.3)

min ≤ med ≤ max:

2003 ≤ 2012 ≤ 2021

IQR (CV) : 9 (0)

19 distinct values

0 (0.0%)

quarter [numeric]

Mean (sd) : 2.5 (1.1)

min ≤ med ≤ max:

1 ≤ 2 ≤ 4

IQR (CV) : 2 (0.5)

1	:	133	(	25.7%	)
2	:	133	(	25.7%	)
3	:	126	(	24.3%	)
4	:	126	(	24.3%	)

0 (0.0%)

debt_type [factor]

1. Auto Loan

2. Credit Card

3. HE Revolving

4. Mortgage

5. Other

6. Student Loan

7. Total

74	(	14.3%	)
74	(	14.3%	)
74	(	14.3%	)
74	(	14.3%	)
74	(	14.3%	)
74	(	14.3%	)
74	(	14.3%	)

0 (0.0%)

amount [numeric]

Mean (sd) : 3.4 (4.4)

min ≤ med ≤ max:

0.2 ≤ 0.8 ≤ 15

IQR (CV) : 7.4 (1.3)

449 distinct values

0 (0.0%)

Generated by summarytools 1.0.1 (R version 4.2.1)
2022-08-21

Ok - we’ve changed our column types as required, and checked that this was done correctly. From the summary table, we can see that 6 values for quarters 3 and 4 have not been keyed in. I want to look at year and quarter to see which values these are - so that for future analyses using year and quarter, we know which year(s) might not have all the information we need.

Code

debt %>% select(year, quarter) %>% arrange(desc(quarter), desc(year))

Looking through the table shows us that the missing values are for 2021 (understandable - the data might not yet be available). This means that our dataset ranges from Q1 of 2003 to Q2 of 2021. When looking at total debt for each year, it might be useful just to look at 2003 to 2020 for completeness.