Library definitions

library(knitr)
library(gdata)
library(stringr)
library(tidyr)
library(dplyr)
library(zipcode)
library(ggplot2)

Dataset url location:

url: https://www.ssa.gov/policy/docs/statcomps/

I will be exploring the OASDI Beneficiaries by State and ZIP Code, 2015.

This annual publication focuses on the Social Security beneficiary population at the ZIP Code level. It presents basic program data on the number and type of beneficiaries and the amount of benefits paid in each state, Social Security Administration field office, and ZIP Code. It also shows the number of beneficiaries aged 65 or older.

This annual publication focuses on the Social Security beneficiary population-people receiving Old-Age, Survivors, and Disability Insurance (OASDI) benefits-at the ZIP Code level. It presents basic program data on the number and type of beneficiaries and the amount of benefits paid in each state, Social Security Administration field office, and ZIP Code. It also shows the number of men and women aged 65 or older receiving benefits. The data include only persons whose benefits are currently payable. Those whose benefits were withheld are excluded.

Cherice Jefferies in the Office of Statistical Analysis and Support programmed and compiled the data for this report. Staff of the Office of Information Resources edited the report and prepared it for web publication.

This is a complete Dataset from the federal government website managed by the Social Security Administration.

Last Updated:

This is a complete set of all data for 2015.

Date: October 2016.

Data Provided by:

Office of Retirement and Disability Policy. Office of Research, Evaluation, and Statistics.

Dataset Owner:

Social Security Administration (SSA) and the Government of the United States of America.

Dictionary

This dataset does not seem to have a dictionary. The download link is for a .xlsx file containing the desired data. However there’s a .pdf file describing the data.

Filename: oasdi_zip15.xlsx

For simplicity reasons, I will read the raw data directly from the source.

URL and Raw data name and location definitions:

url <- "https://www.ssa.gov/policy/docs/statcomps/oasdi_zip/2015/"
xlsxfile <- "oasdi_zip15.xlsx"
rm(xlsxfile)

I tried loading the data from the original location as raw as possible from the .XLSX file but found several problems trying to read it. I ended up unmerging the cells by opening the excel file for the State of New York sheet and saving as a .csv file.

For reproducibility purposes I have uploaded the untoched .csv file onto my GitHub repository.

url <- "https://raw.githubusercontent.com/dvillalobos/MSDA/master/607/Projects/Project2/"
csvfile <- "oasdi_zip15.csv"

Function to download .csv file, and extract information from it

downloadCSV <- function(myurl, mycsvfile){
  myurl <- paste(myurl,mycsvfile, sep="")
  my.data <- read.csv(myurl, header=FALSE, stringsAsFactors =FALSE )
  head(my.data)
  return(my.data)
}

my.data <- downloadCSV(myurl= url, mycsvfile= csvfile)

kable(head(my.data))

Excluding Information:

Excluding top and bottom unwanted Rows:

This procedure will exclude the unwanted information contained in the first six rows, then I will exclude the information contained in at the bottom of the file 1995 to 2002 becoming from 1990 to the end in the new data frame.

my.new.data <- my.data[-c(1:6), ]
my.new.data <-my.new.data[-c(1989:3003), ]

Exclude unwanted V3 column:

str(my.new.data)

## 'data.frame':    1988 obs. of  13 variables:
##  $ V1 : chr  "Albany" "" "" "" ...
##  $ V2 : chr  "" "12007" "12009" "12023" ...
##  $ V3 : chr  "" "" "" "" ...
##  $ V4 : chr  "54,020" "50" "1,520" "490" ...
##  $ V5 : chr  "37,555" "40" "1,145" "340" ...
##  $ V6 : chr  "8,015" "10" "175" "60" ...
##  $ V7 : chr  "3,175" "0" "75" "35" ...
##  $ V8 : chr  "1,575" "0" "65" "20" ...
##  $ V9 : chr  "3,700" "0" "60" "35" ...
##  $ V10: chr  "71,518" "74" "2,095" "630" ...
##  $ V11: chr  "54,461" "58" "1,669" "470" ...
##  $ V12: chr  "4,301" "0" "105" "46" ...
##  $ V13: chr  "39,255" "40" "1,175" "350" ...

my.new.data <- my.new.data %>% subset(select=-c(V3))
str(my.new.data)

## 'data.frame':    1988 obs. of  12 variables:
##  $ V1 : chr  "Albany" "" "" "" ...
##  $ V2 : chr  "" "12007" "12009" "12023" ...
##  $ V4 : chr  "54,020" "50" "1,520" "490" ...
##  $ V5 : chr  "37,555" "40" "1,145" "340" ...
##  $ V6 : chr  "8,015" "10" "175" "60" ...
##  $ V7 : chr  "3,175" "0" "75" "35" ...
##  $ V8 : chr  "1,575" "0" "65" "20" ...
##  $ V9 : chr  "3,700" "0" "60" "35" ...
##  $ V10: chr  "71,518" "74" "2,095" "630" ...
##  $ V11: chr  "54,461" "58" "1,669" "470" ...
##  $ V12: chr  "4,301" "0" "105" "46" ...
##  $ V13: chr  "39,255" "40" "1,175" "350" ...

Renaming Columns

names(my.new.data) <- c("County","Zipcode","n Total", "n Retired", "n Disabled", "n Widow & Parents", "n Spouses", "n Children", "$ All Beneficiaries", "$ Retired", "$ Widow & Parents", "n OASDI 65 & older")

Need to split data into 2 data frames

1. Zip Code Table which is going to include:

• Data for Numbers

• Data for Monthly Benefits

2. County Table which is going to include:

• Data for Numbers

• Data for Monthly Benefits

Separate results:

First I will separate County Summary data from zip code data.

# Creating a County Data Frame
my.new.data$County <- str_replace_all(my.new.data$County," ","")
my.county.data <- my.new.data %>% subset(County != "")
rownames(my.county.data) <- NULL

County Summary Table.

Zip Code Table by employing anti_join() from dplyr function.

my.zipcode.data <- anti_join(my.new.data, my.county.data, by="County")
my.zipcode.data <- my.zipcode.data %>% subset(select=-c(County))

Zip Code table.

Geographical distribution:

Distribution by Region:

Distribution of OASDI Beneficiaries during 2015 by the zipcode (Region).

# Merge Zipcodes with the zipcode library
USzipCodes <- my.zipcode.data
USzipCodes$Zipcode <- clean.zipcodes(USzipCodes$Zipcode)
data(zipcode)
USzipCodes <- merge(USzipCodes, zipcode, by.x='Zipcode', by.y='zip')

# Creating ggplot of matches ZipCodes
g <- ggplot(data=USzipCodes) + geom_point(aes(x=longitude, y=latitude, colour=1))

# simplify display and limit to the "lower 48"
g <- g + theme_bw() + scale_x_continuous(limits = c(-80,-72), breaks = NULL)
g <- g + scale_y_continuous(limits = c(40,45), breaks = NULL)
g <- g + theme(legend.position="none")
g

Zip codes in which children have not received benefits.

Geographical distribution.

# Creating ggplot of matches ZipCodes
g <- ggplot(data=USzipCodesChild) + geom_point(aes(x=longitude, y=latitude, colour=3))

# simplify display and limit to the "lower 48"
g <- g + theme_bw() + scale_x_continuous(limits = c(-80,-72), breaks = NULL)
g <- g + scale_y_continuous(limits = c(40,45), breaks = NULL)
g <- g + theme(legend.position="none")
g

Conclusions

This is an interesting analysis and I believe it can play a great role in local discoveries related to OASDI Beneficiaries since it covers immediate surrounding areas.

For example, from the Region distribution we can visualize how the distribution is over the respective zipcodes having a better perspective on how respective populations are distributed.