Below is the sample data. The task at hand is to sum quarter1 and quarter2 for ownership code 30 but exclude indcode 115. From there complete a new row that contain this sum. In excel, this is very simple but hoping to automate this a bit using R. The bottom half of the desired result is below. First question is would I pivot_wider so that I summing columns not rows?
area <- c(000000,000000,000000,000000,000000,000000,000000,000000,000000,000000)
indcode <- c(111,112,113,114,115,111,112,113,114,115)
quarter1 <- c(2,4,6,16,3,1,2,3,8,2)
quarter2 <- c(3,5,7,22,1,1,2,4,11,1)
ownership <- c(00,00,00,00,00,30,30,30,30,30)
employment <- data.frame(area,indcode,quarter1,quarter2,ownership)
area indcode quarter1 quarter2 ownership
000000 111 1 1 30
000000 112 2 2 30
000000 113 3 4 30
000000 114 8 11 30
000000 115 2 1 30
000000 993 14 18 30
>Solution :
I’ve assumed you want this done for area groups, but if not you can delete the group_by(area) line.
employment %>%
group_by(area) %>%
summarize(
across(quarter1:quarter2, ~sum(.x[ownership == 30 & indcode != 115])),
indcode = 993,
ownership = 30
) %>%
bind_rows(employment, .)
# area indcode quarter1 quarter2 ownership
# 1 0 111 2 3 0
# 2 0 112 4 5 0
# 3 0 113 6 7 0
# 4 0 114 16 22 0
# 5 0 115 3 1 0
# 6 0 111 1 1 30
# 7 0 112 2 2 30
# 8 0 113 3 4 30
# 9 0 114 8 11 30
# 10 0 115 2 1 30
# 11 0 993 14 18 30