How to do the following row operation in R-CodePudding

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

CodePudding user response：

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