Affected Parish

Import Dataset

## Import Raw Unballance Dataset that contain all county
Eperson4<- read_excel("ALL Civil Labor Force_1906 Unballance.xlsx",sheet = "Employee")
CLabor4<-read_excel("ALL Civil Labor Force_1906 Unballance.xlsx",sheet = "CivilLabor")
URate4<-read_excel("ALL Civil Labor Force_1906 Unballance.xlsx",sheet = "URate")

## List of Affected Parish in LA
# FIPS Affected Parish in LA
FIPS_aff = c("22051", "22071", "22103", "22089", "22087", "22095", "22075")

## Cheack correctness of list of Fips
filter(countypop, fips %in% FIPS_aff )[1:3]

## # A tibble: 7 × 3
##   fips  abbr  county                     
##   <chr> <chr> <chr>                      
## 1 22051 LA    Jefferson Parish           
## 2 22071 LA    Orleans Parish             
## 3 22075 LA    Plaquemines Parish         
## 4 22087 LA    St. Bernard Parish         
## 5 22089 LA    St. Charles Parish         
## 6 22095 LA    St. John the Baptist Parish
## 7 22103 LA    St. Tammany Parish

Employee Person

## Employee Person
LA_Emp<-Eperson4%>%
  mutate(date=as.Date(date))%>%                    #Convert Posixt to Date format
  filter(state=="LA"& !fips%in%FIPS_aff,           #Filter State is LA and Fips out of Affected County
         date=="2005-08-01"|date=="2006-07-01")%>% #Filter Focus Date
  group_by(fips)%>%                                #Group by FIPS 
  mutate(Change=100*(value[2]-value[1])/value[1])  #Calculate Change From Aug-2005 to July-2006

LA_Emp$fips%>%unique()%>%length()                  # Check number of unique FIps in LA

## [1] 57

aff_Emp<-Eperson4%>%
  mutate(date=as.Date(date))%>%                    #Convert Posixt to Date format
  filter(fips%in%FIPS_aff,                         #Filter list in Affected County
         date=="2005-08-01"|date=="2006-07-01")%>% #Filter Focus Date
  group_by(fips)%>%
  mutate(Change=100*(value[2]-value[1])/value[1])


US_Emp<-Eperson4%>%
  mutate(date=as.Date(date))%>%                    #Convert Posixt to Date format
  filter(state!="LA",                              #Filter State that non LA
         date=="2005-08-01"|date=="2006-07-01")%>%
  group_by(fips)%>%
  mutate(Change=100*(value[2]-value[1])/value[1])

# NOTE : WE DONT USE LIST OF AFFECTED COUNTY SINCE ITS INCLUDE IN LA

## ARRANGE THE Average AREA Effect
df_Emp<-data.frame("Area"=c("Affected Parish","Neighbor Parish", "US National"),
                   "Employee Person"=c(mean(aff_Emp$Change),mean(LA_Emp$Change),mean(US_Emp$Change)))

Civil Labor

# Civil Labor
LA_Civ<-CLabor4%>%
  mutate(date=as.Date(date))%>%                    #Convert Posixt to Date format
  filter(state=="LA"& !fips%in%FIPS_aff,           #Filter State is LA and Fips out of Affected County
         date=="2005-08-01"|date=="2006-07-01")%>% #Filter Focus Date
  group_by(fips)%>%                                #Group by FIPS 
  mutate(Change=100*(value[2]-value[1])/value[1])  #Calculate Change From Aug-2005 to July-2006

LA_Civ$fips%>%unique()%>%length()                  # Check number of unique FIps in LA

## [1] 57

aff_Civ<-CLabor4%>%
  mutate(date=as.Date(date))%>%                    #Convert Posixt to Date format
  filter(fips%in%FIPS_aff,                         #Filter list in Affected County
         date=="2005-08-01"|date=="2006-07-01")%>% #Filter Focus Date
  group_by(fips)%>%
  mutate(Change=100*(value[2]-value[1])/value[1])


US_Civ<-CLabor4%>%
  mutate(date=as.Date(date))%>%                    #Convert Posixt to Date format
  filter(state!="LA",                              #Filter State that non LA
         date=="2005-08-01"|date=="2006-07-01")%>%
  group_by(fips)%>%
  mutate(Change=100*(value[2]-value[1])/value[1])


df_Civ<-data.frame("Area"=c("Affected Parish","Neighbor Parish", "US National"),
                   "Civil Labor"=c(mean(aff_Civ$Change),mean(LA_Civ$Change),mean(US_Civ$Change)))

Unemployee Rate

## UnemRate
LA_UR<-URate4%>%
  mutate(date=as.Date(date))%>%
  filter(state=="LA"& !fips%in%FIPS_aff,
         date=="2005-08-01"|date=="2006-07-01")%>%
  group_by(fips)%>%
  mutate(Change=(value[2]-value[1]),
         Change_p=100*(value[2]-value[1])/value[1])

LA_UR$fips%>%unique()%>%length()

## [1] 57

aff_UR<-URate4%>%
  mutate(date=as.Date(date))%>%
  filter(fips%in%FIPS_aff,
         date=="2005-08-01"|date=="2006-07-01")%>%
  group_by(fips)%>%
  mutate(Change=(value[2]-value[1]),
         Change_p=100*(value[2]-value[1])/value[1])


aff_UR$fips%>%unique()%>%length()

## [1] 7

US_UR<-URate4%>%
  mutate(date=as.Date(date))%>%
  filter(state!="LA",
         date=="2005-08-01"|date=="2006-07-01")%>%
  group_by(fips)%>%
  mutate(Change=(value[2]-value[1]),
         Change_p=100*(value[2]-value[1])/value[1])

df_UR<-data.frame("Area"=c("Affected Parish","Neighbor Parish", "US National"),
                  "Unemployee Rate"=c(mean(aff_UR$Change),mean(LA_UR$Change),mean(US_UR$Change)),
                  "Unemployee Rate 2"=c(mean(aff_UR$Change_p),mean(LA_UR$Change_p),mean(US_UR$Change_p)))

Arrange Table

df_table<-df_Civ%>%
  left_join(df_Emp)%>%
  left_join(df_UR)

## Joining with `by = join_by(Area)`
## Joining with `by = join_by(Area)`

df_table

##              Area Civil.Labor Employee.Person Unemployee.Rate Unemployee.Rate.2
## 1 Affected Parish  -17.394856      -16.594595      -0.9714286        -17.932814
## 2 Neighbor Parish    4.295020        6.469592      -1.9298246        -28.862379
## 3     US National    2.058887        1.888285       0.1368035          2.878607