DATA 607 Project 2

About the Data

The data we want to use for this is county level data. We want the most robust numbers so we will be using the latest 5 year estimates available.

Table 1. C24010 Sex by Occupation for Civilain Employed Pop 16+

Developing a Workplan

In order to make a tidy data frame the first step will be to transform it from wide to long form. Then the gender and occupation needs to be split out from the column header. Spliting the gender will be simple enough but extracting the occupations will require a bit of cleanup. I will use the following function to strip out the non-occupational parts of the string, then replace the periods added to the columns by R with spaces, remove the duplicate spaces and strip off leading and trailing white space.

get_occupation <- function(string){
  string <- str_remove(string, "SEX.BY.OCCUPATION.FOR.THE.CIVILIAN.EMPLOYED.POPULATION.16.YEARS.AND.OVER..")
  string <- str_remove(string, "B24022..Sex.by.Occupation.and.Median.Earnings.in.the.Past.12.Months..in.2015.Inflation.Adjusted.Dollars..for.the.Full.Time..Year.Round.Civilian.Employed.Population.16.Years.and.Over")
  string <- str_remove(string, "Female")
  string <- str_remove(string, "Male")
  string <- str_replace_all(string, "[.]", " ")
  string <- str_replace_all(string, "  ", " ")
  string <- trimws(string)
  # Simplify occupations
  # Select everything after the last capital letter started sequence
  search_for <- str_extract(string, "[A-Z][[:alpha:] ]+[A-Z]")
  replace_with <- str_sub(search_for, start=-1)
  string <- str_replace_all(string, search_for, replace_with)
  string
}

Now that I have the get_occupation function I am ready to apply it to the dataset.

Execution of the Plan

I will start with the worker counts by sex and occupation.

acs_df_C24010 <- acs_df_C24010 %>%
  rename(County = X) %>%
  gather(key = Header, value = Workers, -County) %>%
  mutate(Occupation = get_occupation(Header),
         Gender = ifelse(grepl("Female", Header), "Female", ifelse(grepl("Male", Header), "Male", "Total"))) %>% 
  filter(Gender != "Total") %>%
  select(-Header) %>%
  drop_na()

Thre are 193200 rows in acs_df_C24010. Now I will apply the same prcess to the median earnings data.

acs_df_B24022 <- acs_df_B24022 %>%
  rename(County = X) %>%
  gather(key = Header, value = "Earnings", -County) %>%
  mutate(Occupation = get_occupation(Header),
         Gender = ifelse(grepl("Female", Header), "Female", ifelse(grepl("Male", Header), "Male", "Total"))) %>%
  filter(Gender != "Total") %>%
  select(-Header)

Thre are 231840 rows in acs_df_B24022. The final step in assembling the tidy dataset is to merge the two data frames togther.

acs_df <- merge(acs_df_C24010, acs_df_B24022)

There are 193200 rows in the combined data frame. Since I want to look at the gender concentrations withing the occupation I will need to create that varaible.

acs_df <- acs_df %>%
  select(Occupation, County, Workers) %>%
  group_by(Occupation, County) %>%
  summarise(Total = sum(Workers)) %>% 
  merge(acs_df) %>%
  mutate(Concentration = Workers / Total) %>%
  select(-Total)

Let’s do a quick sanity check on the data. Let’s examine the Workers, Earnigns and Concentration

acs_df <- acs_df %>%
  filter(Earnings > 0)

acs_df <- acs_df %>%
  filter(Gender == "Male") %>%
  select(County, Occupation, Earnings) %>%
  rename(Male_Earnings = Earnings) %>%
  merge(acs_df) %>%
  filter(Gender == "Female") %>%
  mutate(Ratio = Earnings / Male_Earnings)

Occupation with the the Largest and Smallest Pay Gap

The female workers earn 0.04 to 12.79 dollars for every dollar a male worker earns. That’s a considerable range! The median is 0.78 as shown by the verticle line in the graph below.

acs_avg_df <- acs_df %>%
  group_by(Occupation) %>%
  summarise(Ratio = mean(Ratio), Concentration = mean(Concentration), N = n())

Pay Gap and Concentration Relationship

Is there any relationship between the equality of gender representation and pay? Our previous example looked at aggregate trends and it appeared to not have a strong relationship. Let’s examine all the observations.

cor(acs_df$Concentration, acs_df$Ratio)

## [1] -0.02527091

About the Data

These estimates of the international migrants have been equated with the foreignborn population whenever this information is available, which is the case in most countries or areas. It is for the mid-point of each year.

Table 7. First Look at UN Data

Developing a Workplan

There is a lot of data at the begining of the file that needs to be overlooked. When I read in the data I will skip down to the row after the one that begins with “year”. The country names are in that row. Thre are “..” that will need to be changed to NAs. The data will need to be renamed. I won’t need the sort order, code or type of data columns so I will drop them.

Execution of the Plan

un_df <- read.xlsx('data/UN_MigrantStockByOriginAndDestination_2017.xlsx', 'Table 1', startRow = 16, na.strings = "..") %>%
  rename(Year = X1,
         Sort.order = X2,
         Destination = X3,
         Notes = X4,
         Code = X5,
         Type.of.data = X6) %>%
  select(-Sort.order, -Code, -Notes)

Now that these steps have been taken let’s take a look at the data frame to determine what to do next.