-
Notifications
You must be signed in to change notification settings - Fork 1
/
cleaning.R
61 lines (48 loc) · 1.82 KB
/
cleaning.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
library('tidyverse')
library('janitor')
library('lubridate')
fy2019 <- read_csv('input/FY19 Salary and OT Data.csv') %>%
clean_names() %>% mutate(date = mdy(check_date))
# convert overtime amount paid to numeric
fy2019$amount <- str_replace(
fy2019$gl_pay_amount, # column we want to search
pattern = '\\$', # what to find
replacement = '' # what to replace it with
)
fy2019$amount <- as.numeric(str_replace(
fy2019$amount, # column we want to search
pattern = ',', # what to find
replacement = '' # what to replace it with
))
# names - since some names (e.g., married names change over the course of the fiscal year)
overtime.names.2019 <- fy2019 %>%
arrange(desc(date)) %>%
distinct(emplid, name) %>%
group_by(emplid) %>%
mutate(n = row_number()) %>%
filter(n == 1) %>% select(-n)
fy2019 <- fy2019 %>% merge(overtime.names.2019 %>% select(emplid, name.standardized = name), by = 'emplid', all = T)
write_csv(fy2019, 'output/overtime_fy2019.csv')
# fy 2018
fy2018 <- read_csv('input/FY 2018 - All GF Overtime after 26 Pay Periods.csv') %>%
clean_names() %>% mutate(date = mdy(check_date))
# convert overtime amount paid to numeric
fy2018$amount <- str_replace(
fy2018$gl_pay_amount, # column we want to search
pattern = '\\$', # what to find
replacement = '' # what to replace it with
)
fy2018$amount <- as.numeric(str_replace(
fy2018$amount, # column we want to search
pattern = ',', # what to find
replacement = '' # what to replace it with
))
# names
overtime.names.2018 <- fy2018 %>%
arrange(desc(date)) %>%
distinct(emplid, name) %>%
group_by(emplid) %>%
mutate(n = row_number()) %>%
filter(n == 1) %>% select(-n)
fy2018 <- fy2018 %>% merge(overtime.names.2018 %>% select(emplid, name.standardized = name), by = 'emplid', all = T)
write_csv(fy2018, 'output/overtime_fy2018.csv')