Retirement Care Costs

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?
4. Based on average life expectancy how much would someone need to save to take cover their healthcare needs?

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.

1.4 Key Definitions

• There are many types of healthcare available to seniors such as independent Living, Home Care, Memory Care, and Assisted Living.
  • We will focus on Assisted Living or Retirement 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.

1.5 Approach

• I will use the retirement range of 67 as the prime age for retirement.
  • I will consider the 62-70 as the selected range for retirees as 62 is the earliest you can start receiving social security benefits and 70 is the latest age you can postpone getting benefits.
• I will focus on Assisted Living or Retirement Home type care for this specific case study.

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

2.1 Data Sourcing

• Nationwide Demographics data to find the amount of retirees nationally

  • From US Census - Need to download past 5 years- S2001 | Earnings in the Past 12 Months (in 2022 Inflation-Adjusted Dollars)

  • The Decennial Census seems to have the most info per state and by demographic although it doesn’t have exact age information. I should be able to use the 65-69 years column as 60-64 years can’t be drilled down into further S2001 | Earnings in the Past 12 Months (in 2022 Inflation-Adjusted Dollars)

  • The American Community Survey is more granular and is collected every year.I should use this instead of the Decennial census 2022 ACS Selected Population Profile)

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

• Colorado Demographic data to find the amount of retirees in Colorado

  • This info can be found in the ACS Pop & Income Surveys

• Colorado Salary data for 67 age range

  • This info can be found in the ACS Pop & Income Surveys

• Cost of Assisted Living for Colorado and Nationwide

  • 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

• National health expenditures for Medicare & Medicaid Services

  • These tables were too broad - National Health Expenditures by type of service and source of funds, CY 1960-2022

  • Helpful documentation on Definitions & SourcesDefinitions, Sources, and Methods (PDF)

  • This is more helpful and should be used Health Expenditures by State of Residence, 1991-2020

2.1.1 Data Sourcing Takeaways

• Data Range - We should focus on 2010-2020 data

  • The health expenditures and demographic population/age data ranges from 1991-2020 with Decennial Census for every 10 years.

• Age Range - I will need to focus on 65 and up as I can’t split the 55-64 range

  • The Age data is broken up by 55-64 years and then 65-74 years and finally 75 years and over

2.2 Data Location

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

  • US Census ACS Folder has
    • State Median Income by Age excel files
    • State Population by Age

2.3 Data Organization

• The data is coming from multiple sheets. I will download this all to a single folder as original sources and then make another folder for edited files.

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 the two files for Q1 of 2019 and 2020

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//RtmpHl2ssv/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//RtmpHl2ssv/downloaded_packages

install.packages("readr")

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

install.packages("janitor")

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

install.packages("lubridate")

## 
## The downloaded binary packages are in
##  /var/folders/1v/dl3wj8hj7_qdscktqmzf7w5w0000gn/T//RtmpHl2ssv/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.0     ✔ 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(dplyr)
library(readxl)
library(readr)
library(janitor)

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

library(lubridate)

2.7 Import the Data and Create Data Frames

Adding placeholder code for now

library(readxl) National_Demo_Raw<- read_excel(‘Placeholder’) State_Demo_Raw<- read_excel(‘Placeholder’) State_SalaryData_Raw<- read_excel(‘Placeholder’) National_CostofCare_Raw<- read_excel(‘Placeholder’)

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

3.1 Checking for Naming Inconsistencies

Adding placeholder code for now

glimpse(Placeholder) glimpse(Placeholder)

Step 4 : Analyze the data

Step 5 : Share my findings

Step 6 : Act

6.1 Findings

6.2 Recommendations