Cleaning Raw Sectors Merges

Import Dataset

UR <- read_excel("UnemRate/Merge UnemRate ballance V2 FULL-Location.xlsx")
UR$YEAR<-format(UR$DATE,"%Y")
UR$FIPS<-str_pad(UR$FIPS,width = 5,side = "left",pad = 0)


CL<-read_excel("ALLCOUNTY/Civil Labor Ballance.xlsx")
CL$YEAR<-format(CL$DATE,"%Y")
CL$FIPS<-str_pad(CL$FIPS,width = 5,side = "left",pad = 0)


EM <- read_excel("Employee Person/Merge Employee Person Full-Location Ballance.xlsx")
EM$YEAR<-format(EM$DATE,"%Y")
EM$FIPS<-str_pad(EM$FIPS,width = 5,side = "left",pad = 0)


Education <- read_excel("Education/Education.xlsx")

Median_Income <- read_excel("Median Income/Median Income.xlsx")

Population_County <- read_excel("Population/Population County.xlsx")

Cleaning Sectors

### CLEAN EDCUATION
# High School Level
Edu_HS<-Education%>%
  filter(str_detect(series_id,pattern = "HC01ESTVC16"))

colnames(Edu_HS)[3]="Edu.HS"

Edu_HS<-Edu_HS[,1:3]
Edu_HS$FIPS<-str_remove(Edu_HS$series_id,pattern = "HC01ESTVC16")
Edu_HS$YEAR<-format(Edu_HS$date,"%Y")

# Bacheloor Degree
Edu_Bsc<-Education%>%
  filter(str_detect(series_id,pattern = "HC01ESTVC17"))

colnames(Edu_Bsc)[3]="Edu.Bsc"
Edu_Bsc<-Edu_Bsc[,1:3]
Edu_Bsc$FIPS<-str_remove(Edu_Bsc$series_id,pattern = "HC01ESTVC17")
Edu_Bsc$YEAR<-format(Edu_Bsc$date,"%Y")


# 25 Year Education
Edu_25<-Education%>%
  filter(str_detect(series_id,pattern = "S1501ACSTOTAL0"))

colnames(Edu_25)[3]<-"Edu.25"

Edu_25<-Edu_25[,1:3]
Edu_25$FIPS<-str_remove(Edu_25$series_id,pattern = "S1501ACSTOTAL0")
Edu_25$YEAR<-format(Edu_25$date,"%Y")


### MEDIAN INCOME
MeI<-Median_Income[,c(1,2,3,6)]
colnames(MeI)[3]<-"Median.Income"
MeI$YEAR<-format(MeI$date,"%Y")

### Population
Pop<-Population_County[,c(1,2,3,7,8)]
colnames(Pop)[3]<-"Population"

Merge All Together

### NOTE : Here in sectors I subset just coloumn 3 to 5, since its information we favor, while coloumn 1 and 2 is code and date informations that less favor for futher analysis

## Civil Labor
CL1<-left_join(CL,Pop[,3:5],by=c("YEAR"="Year","FIPS"="fips"))%>%
  left_join(MeI[,3:5],by=c("YEAR","FIPS"="fips"))%>%
  left_join(Edu_25[,3:5],by=c("FIPS","YEAR"))%>%
  left_join(Edu_Bsc[,3:5],by=c("FIPS","YEAR"))%>%
  left_join(Edu_HS[,3:5],by=c("FIPS","YEAR"))

CL1<-CL1%>%distinct(DATE,FIPS,.keep_all =T)

## Employee Person
EM1<-left_join(EM,Pop[,3:5],by=c("YEAR"="Year","FIPS"="fips"))%>%
  left_join(MeI[,3:5],by=c("YEAR","FIPS"="fips"))%>%
  left_join(Edu_25[,3:5],by=c("FIPS","YEAR"))%>%
  left_join(Edu_Bsc[,3:5],by=c("FIPS","YEAR"))%>%
  left_join(Edu_HS[,3:5],by=c("FIPS","YEAR"))

EM1<-EM1%>%distinct(DATE,FIPS,.keep_all =T)

## Unemployee Rate
UR1<-left_join(UR,Pop[,3:5],by=c("YEAR"="Year","FIPS"="fips"))%>%
  left_join(MeI[,3:5],by=c("YEAR","FIPS"="fips"))%>%
  left_join(Edu_25[,3:5],by=c("FIPS","YEAR"))%>%
  left_join(Edu_Bsc[,3:5],by=c("FIPS","YEAR"))%>%
  left_join(Edu_HS[,3:5],by=c("FIPS","YEAR"))

UR1<-UR1%>%distinct(DATE,FIPS,.keep_all =T)