Retirement Care Costs - Data Analysis Project

Introduction

This is my case study project for the Google Data Analytics Course. I have put this case study together in R Markdown to help illustrate my process and the code all in a single location.

The Case Study Overview

• I was tasked with doing a data analysis project that would positively impact my local community.
• I have had lots of experience volunteering in elderly care retirement homes in the past and one thing that struck me about that experience was how expensive it is for anyone to be able to afford the care they need once they reach retirment age.
• I wanted to dig into the data to help expose the gap between income for seniors and the cost of retirement care in Colorado.
• I hope that showing not only the gap but how many people will be impacted by this will help shed light and raise awareness for individuals and family members that may need to bear the cost of retirement car.e

My Overall Approach

1. Make sure I have the right packages installed and loaded
2. Download and Save the files on my desktop
3. Import the right data into data frames in R
4. Normalize the column names
5. Add new columns to help figure out monthly and daily trends
6. Conduct some analysis
7. Visualize Data
8. Callout Recommendations

Step 1. Ask questions and define the problem

1.1 Key Questions

1. How many seniors are entering retirement age? (Historically, Currently, and in the future)
2. What is the average cost of retirement healthcare in Colorado and how does this compare across multiple states?
3. What is the average salary for retirees and how does that compare to the average cost of care?

1.2 What is the business task?

• Share insights publicly to help raise awareness on the cost of healthcare for seniors.

1.3 Key Stakeholders

• Soon to be Retirees - This is the main audience as I am hoping to raise awareness for this audience before it’s to late. I would be targeting people in their 40’s and 50’s.

• Family of Retirees - The cost of healthcare may be shouldered by the retiree’s family and making sure they are aware of the discrepancy in average cost vs how much they may have saved up is a key objective.

• The Broader Public Audience - I think this is an issue that needs to have more awareness broadly before you even get close to retirement age. This would help people plan more effectively when they are still early on in their careers.

Step 2 : Prepare the data by collecting and storing the information

2.0 Updated General Approach

• There are many types of healthcare available to seniors such as independent Living, Home Care, Memory Care, and Assisted Living.
  • We will focus on Private Room Nursing Home type care for this specific case study.
• What age do you have to be to be considered retired?
  • 62 is the minimum, 67 is when full benefits kick in and 70 is the latest age you can start. -After going through the data I will focus on 65 and up as the retirement cohort due to many databases being structured that way.

2.0.1 Data Source For Data

• FOR POPULATION & AGE - I will be using the Colorado Population dataset all the pop estimates since 1990. Colorado.gov Population Estimate from 1990 to 2050

  • I’ll use this to see changes in the 65+ Colorado population over time.

• FOR INCOME - I will use the ACS Median Income in the Past 12 Months by age shows income by 45-65 range and 65 and over ACS Median Income in the Past 12 Months by age for 2022) - I ended up using the TidyCensus package and calling the data directly from the Census API.

• FOR HEALTHCARE COST - I will be using the gathered data from a Private Org and needs to be added to table - National & State Data Tables (pdf)

  • I have edited this into it’s own spreadsheet as this came in as a pdf. National & State Data Tables

2.2 Data Location

• I downloaded the data from the links above to a Case Study Data Folder on my laptop.

2.3 Data Organization

• The data is coming from multiple sources
• I downloaded everything to a single folder as original sources and then make another folder for edited files.
• Most of my analysis was done in R Studio with some work being done in Google Sheets

2.4 Credibility of the data/ Data Bias/Does this data ROCC?

• This data ROCCCs! It is provided by government agencies apart from the Genworth data for cost of assisted living services which is from a private company.

2.5 Download the data and store it appropriately

• I downloaded all the files mentioned above and source the Income by State data from the census API directly through tidyCensus.

2.6 Utilize R Studio

• I then opened up R Studio and started making a R Markdown file to help structure my process and show my results.

2.7 Install the necessary packages and load them up

chooseCRANmirror(graphics=FALSE, ind=1)
knitr::opts_chunk$set(echo = TRUE)
install.packages("tidyverse")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpcpcUYb/downloaded_packages

install.packages("tidycensus")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpcpcUYb/downloaded_packages

install.packages("ggplot2")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpcpcUYb/downloaded_packages

install.packages("dyplr")

## Warning: package 'dyplr' is not available for this version of R
## 
## A version of this package for your version of R might be available elsewhere,
## see the ideas at
## https://cran.r-project.org/doc/manuals/r-patched/R-admin.html#Installing-packages

install.packages("readxl")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpcpcUYb/downloaded_packages

install.packages("readr")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpcpcUYb/downloaded_packages

install.packages("janitor")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpcpcUYb/downloaded_packages

install.packages("lubridate")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpcpcUYb/downloaded_packages

library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.1     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(tidycensus)
library(ggplot2)
library(dplyr)
library(readxl)
library(readr)
library(janitor)

## 
## Attaching package: 'janitor'
## 
## The following objects are masked from 'package:stats':
## 
##     chisq.test, fisher.test

library(lubridate)

census_api_key("899595acd1a9b3289bb259cd44cd94252835f372", install = TRUE , overwrite=TRUE)

## Your original .Renviron will be backed up and stored in your R HOME directory if needed.
## Your API key has been stored in your .Renviron and can be accessed by Sys.getenv("CENSUS_API_KEY"). 
## To use now, restart R or run `readRenviron("~/.Renviron")`

## [1] "899595acd1a9b3289bb259cd44cd94252835f372"

Step 3 : Process the data by cleaning and checking the information

3.1 Census Variables Check

In order for me to find and pull the correct data from the census database I need to load the variable codes into a viewable dataframe

v16 <- load_variables(2022, "acs1", cache = TRUE)

3.2 Use Tidycensus to pull the correct codes

Pulling out all of the census data per state for over 65 population. - Rows are grouped by income bracket - I called each one individually and created legible aliases for them

Incomebystate2022_RAW <- get_acs(
  geography = "state", 
  variables = c(totalOver65 = "B19037_053E",
                e01_lessthan10k = "B19037_054E",
                e02_10kto14k = "B19037_055E",
                e03_15kto19k = "B19037_056E",
                e04_20kto24k = "B19037_057E",
                e05_25kto29k = "B19037_058E",
                e06_30kto34k = "B19037_059E",
                e07_35kto39k = "B19037_060E",
                e08_40kto44k = "B19037_061E",
                e09_45kto49k = "B19037_062E",
                e10_50kto54k = "B19037_063E",
                e11_55kto59k = "B19037_064E",
                e12_60kto74k = "B19037_065E",
                e13_75kto99k = "B19037_066E",
                e14_100kto124k = "B19037_067E",
                e15_125kto149k = "B19037_068E",
                e16_200Kplus = "B19037_069E"),
  output = "wide",
  year = 2022
)

## Getting data from the 2018-2022 5-year ACS

3.3 Take the new table and drop the old code colums

Now i need to remove the Margin of error columns that have the suffix M and rename columns

# Remove column if its name ends with a specific suffix
library(dplyr)
suffix_to_remove <- "M"
Incomebystate2022_Clean <- select(Incomebystate2022_RAW, -ends_with(suffix_to_remove))

3.4 Rename NAME column to state

library(dplyr)
Incomebystate2022_Clean <- rename(Incomebystate2022_Clean, state = NAME)

3.5 Reformatting Data Check

I need to reformat the data in order to visualize it better in a longer format

   Incomebystate2022_Long <- pivot_longer(
    Incomebystate2022_Clean,
    4:19, 
    names_to = "income", 
    values_to = "count"  )

3.6 Reformatting Data Check

IncomeColorado22 <- Incomebystate2022_Long %>%
  filter(state=="Colorado")

Step 4 : Analyze the data

4.1 Import the Data and Create Data Frames for Healthcare Cost by State

library(readxl)

National_CostofCare_Raw<- read_excel('AnnualMedianCosts Healthcarebystate.xlsx')

4.2 Clean Up Dataframe for Colorado Population for 65+ for past 3 years

• I cleaned and analyzed this data in Excel
• I also made a chart of the last 3 years (see below)

Step 5 : Share my findings

5.1 Visulization for Colorado Income

Now lets plot the income ranges for Colorado residents ages 65+

library(ggplot2)

ColPlot<- ggplot(IncomeColorado22, aes(y = income, x = count, fill=count)) +

  geom_col()
 
ColPlot <- ColPlot + labs(title = "Colorado Yearly Household Income for Age 65+ ",
              caption = "Data source: US Census Data")
 
 ColPlot

5.2 Visualization of Average cost of care by state

library(ggplot2)

NatCost<- ggplot(National_CostofCare_Raw, aes(y = Location, x = NursingHome_PrivateRoom, fill=NursingHome_PrivateRoom)) +
  geom_col()
 
NatCost <- NatCost + labs(title = "Yearly Cost of Nursing Home Care by State",
              caption = "Data source: Wentworth Study")

National Nursing Home Costs By State

5.3 Visualize Colorado Population for 65+

Population for 65+ in Colorado

Step 6 : Act

6.1 Findings

After looking at the data we can see that - Yearly Income for 65+ in colorado is mostly in the 60K to $74K Region - Healthcare costs are $116,709 for a private room - That means that people are coming up short by $42,709

6.2 Recommendations

• People should be saving to have at least $116,709 yearly in order to maintain their quality of life.
• This may mean investments, continuing to work, or getting additional help from family.
• Families should be budgetging for a cost of $116,709 for private room nursing home care or think about alternate options before hospice such as living with family.