Appendix 10: R Script for Visualization and Data Manipulation of Bill Referral

1

Appendix 10: R Script for Visualization and Data Manipulation of Bill Referral

The aim is to prepare and manipulate the raw data of Bill Referrals so that it could be classify to several tiers to distinguish the dominant congressional committees from others.

The data is retrieved on 05/24/2017 from CONGRESS.GOV. The selected subject is “Oil and Gas” with following term: “Oil and gas royalties”, “Oil fields”, “Oil pollution”, “Oil shales”,

“Oil spills and wildlife”, “Oil well drilling”, “Petroleum”, “Petroleum engineering”,

“Petroleum in submerged lands”, “Petroleum industry”, “Petroleum pipelines”, “Petroleum recycling”, “Petroleum refineries”, “Petroleum reserves”, and “Petroleum storage”.

Setting the directory

#install.packages("readxl") library(readxl)

sessionInfo()

## R version 3.6.2 (2019-12-12)

## Platform: x86_64-w64-mingw32/x64 (64-bit)

## Running under: Windows 10 x64 (build 19041)

##

## Matrix products: default

##

## locale:

## [1] LC_COLLATE=English_United States.1252

## [2] LC_CTYPE=English_United States.1252

## [3] LC_MONETARY=English_United States.1252

## [4] LC_NUMERIC=C

## [5] LC_TIME=English_United States.1252

##

## attached base packages:

## [1] stats graphics grDevices utils datasets methods base

##

## other attached packages:

## [1] readxl_1.3.1

##

## loaded via a namespace (and not attached):

## [1] compiler_3.6.2 magrittr_1.5 tools_3.6.2 htmltools_0.4.0

## [5] yaml_2.2.1 Rcpp_1.0.3 cellranger_1.1.0 stringi_1.4.6

## [9] rmarkdown_2.1 knitr_1.28 stringr_1.4.0 xfun_0.12

## [13] digest_0.6.25 rlang_0.4.5 evaluate_0.14

Importing the file

Bill_Referral <- read_excel("All_Bill_Referral.xlsx")

2

Data prepartion

Coding the joint referral by giving different number to each bill that was referred to more than one committee.

x = 1

ID <- unique(Bill_Referral$`Legislation Number`) # to remove the duplicates for (i in ID) {

J <- which(i == Bill_Referral$`Legislation Number`) if(length(J)>1) {

Bill_Referral$`Joint Referral`[J] <- x x = x + 1

} }

Creating columns for years and quarters

Bill_Referral["Years"] <- format(as.Date(Bill_Referral$`Date of Introduction`

),

"%Y")

Bill_Referral["Quraters"] <- quarters(Bill_Referral$`Date of Introduction`)

Counting the number of bills w/ & w/o the joint referrals

yr <- unique(Bill_Referral$Years) length(yr)

## [1] 20

1. The number of the bill referrals after separating joint referrals num <- c()

for (i in yr) {

N <- length(which(i == Bill_Referral$Years)) num <- append(num, N,length(num))

}

par(mar = c(2, 3, 4, 3))

plot(yr,num, main = "The Separated Bill Referrals")

lines(yr,num)

3

2. The number of the bill referrals before separating joint referrals numJ <- c()

for (i in yr) {

N <- which(i == Bill_Referral$Years)

N1 <- Bill_Referral$`Legislation Number`[N]

N1 <- length(unique(N1))

numJ <- append(numJ, N1,length(num)) }

par(mar = c(2, 3, 4, 3))

plot(yr,numJ, main = "The Joint Bill Referrals")

lines(yr,numJ)

4

Creating dataframe for the number of bill w/ & w/o joint referrals Num_Bills <- data.frame(yr, num, numJ)

# Changing the name of the columns

colnames(Num_Bills)[colnames(Num_Bills)=="yr"] <- "Years"

colnames(Num_Bills)[colnames(Num_Bills)=="num"] <- "After_seprate"

colnames(Num_Bills)[colnames(Num_Bills)=="numJ"] <- "Before_seprate"

Num_Bills

## Years After_seprate Before_seprate

## 1 1997 76 52

## 2 1998 48 34

## 3 1999 108 76

## 4 2000 44 37

## 5 2001 126 72

## 6 2002 26 22

## 7 2003 86 51

## 8 2004 50 32

## 9 2005 96 53

## 10 2006 57 34

## 11 2007 153 79

## 12 2008 54 36

## 13 2009 92 50

## 14 2010 54 39

## 15 2011 124 69

5

## 16 2012 49 33

## 17 2013 91 53

## 18 2014 65 34

## 19 2015 139 93

## 20 2016 49 29

Counting the number of bill referrals for each committee Com <- unique(Bill_Referral$`Committee Abbreviation`) Com <- Com[-which(is.na(Com))] # removing the NA from the vector # Adding columns for referral by committees to dataframe of "Num_Bills" for (i in Com) { Num_Bills[i] <- NA } Counting the bill referrals for each committees for (i in Com) { Cl <- which(names(Num_Bills)==i) for (n in yr) { x <- which(Num_Bills$Years == n) N <- which(Bill_Referral$Years == n & Bill_Referral$`Committee Abbreviati on` == i) Num_Bills[x,Cl] <- length(N) } } Num_Bills

## Years After_seprate Before_seprate NR.H EPW.S ENR.S EC.H WM.H AS.S AS.H ST.H ## 1 1997 76 52 12 3 8 7 7 3 2 3

## 2 1998 48 34 5 0 1 2 3 3 4 1

## 3 1999 108 76 18 4 18 9 7 1 1 2

## 4 2000 44 37 2 1 3 0 0 2 1 0

## 5 2001 126 72 12 6 11 12 8 4 2 3

## 6 2002 26 22 2 0 0 1 0 1 1 1

## 7 2003 86 51 13 5 5 5 6 2 1 5

## 8 2004 50 32 4 1 3 2 4 0 1 1

## 9 2005 96 53 7 4 5 5 5 0 1 4

## 10 2006 57 34 4 0 6 8 4 0 1 1

## 11 2007 153 79 9 6 10 6 5 2 13 8

## 12 2008 54 36 4 1 0 4 3 2 2 2

## 13 2009 92 50 5 1 12 2 6 2 2 7

## 14 2010 54 39 4 2 6 2 1 2 1 1

## 15 2011 124 69 18 5 11 10 8 2 3 1

## 16 2012 49 33 9 1 4 4 3 0 1 1

## 17 2013 91 53 19 1 14 6 1 1 2 3

## 18 2014 65 34 8 2 4 6 2 0 0 1

## 19 2015 139 93 12 4 40 14 7 0 1 4

## 20 2016 49 29 5 1 5 3 2 1 1 0

6

## App.H FA.H TI.H Bud.H Bud.S Fin.S App.S IA.S CST.S OGR.H Rul.H HSGA.S Agr.H

## 1 6 1 6 4 1 2 4 1 2 2 1 1 0

## 2 9 1 1 0 0 2 8 1 0 0 0 0 1

## 3 8 3 5 3 1 3 6 1 4 0 1 0 3

## 4 9 2 2 0 0 0 6 0 1 0 0 0 0

## 5 8 5 11 4 0 1 10 1 5 1 0 1 3

## 6 5 2 2 0 0 1 5 3 0 0 0 0 0

## 7 8 1 5 2 0 1 4 3 2 3 1 0 2

## 8 6 3 4 3 0 0 7 0 1 0 0 0 2

## 9 7 5 7 2 2 1 7 1 5 1 0 2 2

## 10 6 2 3 0 1 1 6 0 2 1 0 0 0

## 11 6 17 4 3 1 3 5 0 6 7 4 0 3

## 12 1 2 2 1 0 3 3 0 1 1 1 0 1

## 13 7 3 5 4 0 2 4 1 5 3 1 0 2

## 14 1 0 6 1 1 0 2 0 5 2 0 0 0

## 15 7 5 11 4 0 0 3 2 5 4 1 0 1

## 16 3 1 2 0 0 2 3 0 0 1 0 0 1

## 17 5 4 4 3 0 0 5 0 1 2 0 1 3

## 18 4 3 5 1 0 0 5 1 3 3 1 1 3

## 19 8 2 6 2 1 3 6 2 4 1 0 1 1

## 20 5 2 3 0 0 1 5 0 1 1 0 0 0

## FS.H Jud.H VA.H VA.S FR.S BHUA.S SB.H SBE.S Jud.S EL.H Int.H Int.S HS.H HA.H

## 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0

## 2 1 1 1 1 0 0 0 0 0 0 0 0 0 0

## 3 1 1 0 0 2 1 1 1 0 0 0 0 0 0

## 4 0 0 0 0 6 1 0 0 1 0 0 0 0 0

## 5 3 1 0 0 3 2 1 1 0 3 1 0 0 0

## 6 1 0 0 0 0 0 0 0 0 0 0 0 0 0

## 7 1 3 0 0 3 0 0 0 1 2 1 0 0 0

## 8 1 0 0 0 1 0 0 0 1 1 1 1 0 0

## 9 3 2 0 0 4 0 0 0 3 3 0 0 1 0

## 10 2 0 0 0 1 1 0 1 1 1 0 0 0 0

## 11 5 4 1 0 4 5 2 1 1 5 0 2 1 0

## 12 2 2 0 0 0 1 0 1 0 2 1 1 0 0

## 13 3 1 1 0 3 3 0 0 0 2 0 0 2 0

## 14 0 3 1 2 0 0 0 0 0 1 0 0 0 0

## 15 5 3 0 0 2 1 0 0 1 0 1 0 0 2

## 16 1 3 0 0 1 1 0 0 2 0 0 0 0 0

## 17 3 3 0 0 0 1 0 0 1 2 0 0 0 0

## 18 2 5 0 0 2 0 1 0 0 0 0 0 0 0

## 19 1 1 1 0 0 2 0 0 1 3 1 0 2 0

## 20 2 4 0 0 1 0 1 0 0 3 0 0 0 0

## ANF.S HELP.S

## 1 0 0

## 2 0 0

## 3 0 0

## 4 0 0

## 5 0 0

## 6 0 0

## 7 0 0

## 8 0 0

## 9 0 0

## 10 0 0

## 11 0 0

## 12 0 0

7

## 13 0 0

## 14 0 0

## 15 0 0

## 16 0 0

## 17 1 0

## 18 1 0

## 19 1 1

## 20 1 0

The Dominant Cognressional Committees in term of bill referral.

The following code is to figure out which congressional committees dominate the subsystem by looking the bill referral

# To counting committees b/w 1996/2016

Committees <- names(Num_Bills)[4:length(names(Num_Bills))]

DF <- data.frame(Committees, Num = NA, Pre = NA, stringsAsFactors = F) Totl <- sum(Num_Bills$Before_seprate)

for (i in 1:length(Committees)) {

DF$Num[i] <- sum(Num_Bills[,which(names(Num_Bills) == Committees[i])]) DF$Pre[i] <- sum(Num_Bills[,which(names(Num_Bills) == Committees[i])]) / To tl *100

}

Mn <- mean(DF$Num)

Snd <- sd(DF$Num, na.rm = T)

Ht2 <- qnorm(0.95, mean = Mn, sd = Snd) summary(DF$Num)

## Min. 1st Qu. Median Mean 3rd Qu. Max.

## 1.00 6.00 28.00 40.65 49.00 172.00 The top 95% committees

TopBR95 <- DF$Committees[which(DF$Num >= Ht2)]

TopBR95

## [1] "NR.H" "ENR.S" "App.H"

Committees b/w 95% and 75% range

TopBR75 <- DF$Committees[which(DF$Num <= Ht2 & DF$Num >= 49.00)]

TopBR75

## [1] "EC.H" "WM.H" "ST.H" "FA.H" "TI.H" "App.S" "CST.S"

Saving the dataframes

save(Bill_Referral, Num_Bills, file = "bill_Referral.RData" )

save(TopBR75, TopBR95, file = "Top_bilRef.RData")