-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy path1-DataPrep.R
132 lines (102 loc) · 6.67 KB
/
1-DataPrep.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
#######################################################################
# Data Prep
#######################################################################
library(readxl)
library(dplyr)
library(tidyr)
# Reads in the Excel File (I had to convert it to an xlsx because it wasn't reading as an Excel)
v13=read_excel("CNTYDET_2015.xlsx", sheet=3)
v14=read_excel("CNTYDET_2015.xlsx", sheet=2)
v15=read_excel("CNTYDET_2015.xlsx", sheet=4)
v16=read_excel("CNTYDET_2016.xlsx", sheet=1)
rv13=read_excel("REGDET_13v_14v_2015.xlsx", sheet=1)
rv14=read_excel("REGDET_13v_14v_2015.xlsx", sheet=2)
rv15=read_excel("REGDET_13v_14v_2015.xlsx", sheet=4)
rv16=read_excel("REGDET_2016.xlsx", sheet=1)
# Pipe of functions that parses the data
j13=v13%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0C", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "countyfips"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(j13, "totalJobs_v13.csv", row.names = FALSE)
# Pipe of functions that parses the data
j14=v14%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0C", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "countyfips"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(j14, "totalJobs_v14.csv", row.names = FALSE)
# Pipe of functions that parses the data
j15=v15%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0C", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "countyfips"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(j15, "totalJobs_v15.csv", row.names = FALSE)
# Pipe of functions that parses the data
j16=v16%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0C", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "countyfips"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(j16, "totalJobs_v16.csv", row.names = FALSE)
# Pipe of functions that parses the data
p16=v16%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("ADJPOPC", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "countyfips"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(p16, "totalPop_v16.csv", row.names = FALSE)
# Pipe of functions that parses the data
l16=v16%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("LFRESC", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "countyfips"), sep=6 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(l15, "totalLabor_v16.csv", row.names = FALSE)
# Pipe of functions that parses the data
rj13=rv13%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0R", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "regionnumber"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(rj13, "totalJobsReg_v13.csv", row.names = FALSE)
# Pipe of functions that parses the data
rj14=rv14[,-1]%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0R", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "regionnumber"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(rj14, "totalJobsReg_v14.csv", row.names = FALSE)
# Pipe of functions that parses the data
rj15=rv15%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0R", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "regionnumber"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(rj15, "totalJobsReg_v15.csv", row.names = FALSE)
# Pipe of functions that parses the data
rj16=rv16%>% #Original Data to be passed
gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
filter(grepl("JOBSI0R", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
separate(OBS, c("variable", "regionnumber"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#Writes out the parsed data to a csv
write.csv(rj16, "totalJobsReg_v16.csv", row.names = FALSE)
# rp15=rv15[,-1]%>% #Original Data to be passed
# gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
# filter(grepl("ADJPOPR", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
# separate(OBS, c("variable", "regionnumber"), sep=7 ) #Splits the OBS column into the variable name and county number in separate columns
#
# #Writes out the parsed data to a csv
# write.csv(rp15, "totalPopReg_v15.csv", row.names = FALSE)
#
# rl15=rv15[,-1]%>% #Original Data to be passed
# gather(year, value, -OBS)%>% # takes original data and reshapes it long from wide
# filter(grepl("LFRESR", OBS))%>% # takes long data and filters obs without "JOBSI0C" in the OBS column
# separate(OBS, c("variable", "regionnumber"), sep=6 ) #Splits the OBS column into the variable name and county number in separate columns
#
# #Writes out the parsed data to a csv
# write.csv(rl15, "totalLaborReg_v15.csv", row.names = FALSE)
#Removes the objects from the environment
rm(j14,v14, j15, v15, j13, v13)