# data prep:  keep only data with yearly pay basis; from new variables
# NM*; output allnm, the collective data for all years
dataprep <- function() {
   for (yr in 2001:2009) {
      tmp <-
      read.csv(paste(yr,".csv",sep=""),header=T,stringsAsFactors=FALSE)
      tmp$NMYear <- yr
      # restrict to those using annual pay period
      if (yr <= 2004) {
         tmp$NMSalary <- tmp$Salary
         tmp$NMPrvlWg <- tmp$Prevail_Wage
         tmp$NMPrvlWgLvl <- NA
         tmp$NMJobTitle <- tmp$OCC_Title
         tmp$NMJobCode <- tmp$Occ_Code
         tmp$NMEmpName <- tmp$Emp_Name
         tmp$NMNation <- NA
         goodrows <- tmp$Unit_of_Pay == "A"
         if (yr == 2001) {
            allnm <<- tmp[goodrows,28:35]
         } else {
            allnm <<- rbind(allnm,tmp[goodrows,28:35])
         }
      } else if (yr == 2005) {
         tmp$NMSalary <- tmp$Wage_Offered_From
         tmp$NMPrvlWg <- tmp$Prevailing_Wage_Amount
         tmp$NMPrvlWgLvl <- tmp$Prevailing_Wage_Level
         tmp$NMJobTitle <- tmp$Prevailing_Wage_Job_Title
         tmp$NMJobCode <- tmp$Prevailing_Wage_SOC_CODE
         tmp$NMEmpName <- tmp$Employer_Name
         tmp$NMNation <- tmp$Alien_Citizenship_Country
         goodrows <- tmp$Wage_Per == "Year"
         allnm <<- rbind(allnm,tmp[goodrows,24:31])
      } else if (yr == 2006) {
         tmp$NMSalary <- tmp$Wage_Offered_From
         tmp$NMPrvlWg <- tmp$Prevailing_Wage_Amount
         tmp$NMPrvlWgLvl <- tmp$Prevailing_Wage_Level
         tmp$NMJobTitle <- tmp$Prevailing_Wage_Job_Title
         tmp$NMJobCode <- tmp$Prevailing_Wage_SOC_CODE
         tmp$NMEmpName <- tmp$Employer_Name
         tmp$NMNation <- tmp$Alien_Citizenship_Country
         goodrows <- tmp$Wage_Per == "Year"
         allnm <<- rbind(allnm,tmp[goodrows,25:32])
      } else if (yr == 2007) {
         tmp$NMSalary <- tmp$WAGE_OFFER_FROM_9089
         tmp$NMPrvlWg <- tmp$PW_AMOUNT_9089
         tmp$NMPrvlWgLvl <- tmp$PW_LEVEL_9089
         tmp$NMJobTitle <- tmp$PW_JOB_TITLE_9089
         tmp$NMJobCode <- tmp$PW_SOC_CODE
         tmp$NMEmpName <- tmp$EMPLOYER_NAME
         tmp$NMNation <- tmp$COUNTRY_OF_CITZENSHIP
         goodrows <- tmp$PW_UNIT_OF_PAY_9089=="yr" 
         allnm <<- rbind(allnm,tmp[goodrows,26:33])
      } else if (yr == 2008) {
         tmp$NMSalary <- tmp$WAGE_OFFER_FROM_9089
         tmp$NMPrvlWg <- tmp$PW_AMOUNT_9089
         tmp$NMPrvlWgLvl <- tmp$PW_LEVEL_9089
         tmp$NMJobTitle <- tmp$PW_JOB_TITLE_9089
         tmp$NMJobCode <- tmp$PW_SOC_CODE
         tmp$NMEmpName <- tmp$EMPLOYER_NAME
         tmp$NMNation <- tmp$COUNTRY_OF_CITZENSHIP
         goodrows <- tmp$PW_UNIT_OF_PAY_9089=="YR" 
         allnm <<- rbind(allnm,tmp[goodrows,26:33])
      } else if (yr == 2009) {
         tmp$NMSalary <- tmp$WAGE.OFFER.FROM.9089
         tmp$NMPrvlWg <- tmp$PW.AMOUNT.9089
         tmp$NMPrvlWgLvl <- tmp$PW.LEVEL.9089
         tmp$NMJobTitle <- tmp$PW.JOB.TITLE.9089
         tmp$NMJobCode <- tmp$PW.SOC.CODE
         tmp$NMEmpName <- tmp$EMPLOYER.NAME
         tmp$NMNation <- tmp$COUNTRY.OF.CITZENSHIP
         goodrows <- tmp$PW.UNIT.OF.PAY.9089=="yr" 
         allnm <<- rbind(allnm,tmp[goodrows,26:33])
      }
      tmp <- tmp[goodrows,]
      assign(paste("all",yr,sep=""),tmp,pos=.GlobalEnv)
   }
   tm <- allnm$NMSalary / allnm$NMPrvlWg
   allnm <<- cbind(allnm,tm)
   # eliminate weirds
   allnm <<- allnm[!is.na(allnm$NMPrvlWg),]
   allnm <<- allnm[allnm$NMPrvlWg > 35000,]
   se <<- allnm[grepl("Software Engineer",allnm$NMJobTitle),]
   # major rule change in prevailing wage starting in 2005
   allnm05 <<- allnm[allnm$NMYear >= 2005,]
   se05 <<- se[se$NMYear >= 2005,]
}

# find median wage ratio for the given firm
medwr <- function(firm) {
   cat(firm," ")
   cat(median(allnm05[allnm05$NMEmpName==firm,9],na.rm=T))
   cat(" n =",sum(allnm05$NMEmpName==firm,na.rm=T),"\n")
}

# find median wage ratio for the given firm, among software engineers
medwrse <- function(firm) {
   cat(firm,", Soft. Eng. ")
   cat(median(se05[se05$NMEmpName==firm,9],na.rm=T))
   cat(" n =",sum(se05$NMEmpName==firm,na.rm=T),"\n")
}

# find % with WR > 1.5 for the given firm
wr15 <- function(firm) {
   cat(firm," ")
   firmdata <- allnm05[allnm05$NMEmpName==firm,]
   nbig <- sum(firmdata$tm > 1.5,na.rm=T)
   nfull <- nrow(firmdata)
   cat(nbig/nfull,"\n")
}

# find median wage ratio for the given firm and given nation
medwrfn <- function(firm,nation) {
   cat(firm," ")
   nationals <- 
      allnm05[allnm05$NMEmpName==firm & allnm05$NMNation==nation,]
   cat(median(nationals$tm,na.rm=T))
   cat(" n =",nrow(nationals),"\n")
}

explore <- function() {
   print(median(allnm05$tm,na.rm=T))
   # I did not report if n < 50
   medwr("MICROSOFT CORPORATION")
   medwr("INTEL CORPORATION")
   medwr("GOOGLE INC.")
   medwr("CISCO SYSTEMS, INC.")
   medwr("ORACLE USA, INC.")
   medwr("HEWLETT-PACKARD COMPANY")
   medwr("MOTOROLA, INC.")
   medwr("QUALCOMM, INC.")
   medwr("EBAY INC")
   medwr("PAYPAL INC")
   medwrse("MICROSOFT CORPORATION")
   medwrse("INTEL CORPORATION")
   medwrse("GOOGLE INC.")
   medwrse("CISCO SYSTEMS, INC.")
   medwrse("ORACLE USA, INC.")
   medwrse("HEWLETT-PACKARD COMPANY")
   medwrse("MOTOROLA, INC.")
   medwrse("QUALCOMM, INC.")
   medwrse("EBAY INC")
   medwrse("PAYPAL INC")
}