Best way to do row operations in R using Pivot

Below is the sample data. The goal is to combine rows into one by indcode and ownership. Area 029 and Area 031 need to be combined into a new row, 039900. At the moment, there are four rows per area. Looking to create 4 more rows that are the sum of the two areas by indcode. I asked a very similar one yesterday but looking for a solution that involves a more dplyr-centric version. My first attempt is at the bottom

library(dplyr)
library(data.table)


areax <- c(029,029,029,029,031,031,031,031)
indcodex <- c(1011,1012,1011,1012,1011,1012,1011,1012)
time1 <- c(100,150,102,152,104,154,108,158)
time2 <- c(101,151,103,153,105,155,109,162)
ownership <- c(50,50,30,30,50,50,30,30)

test2 <- data.frame(areax,indcodex,time1,time2,ownership)

Desired result

   areax        indcodex      time1     time2    ownership
     029           1011         100        101       50
     029           1012         150        151       50
     029           1011         102        103       30
     029           1021         152        153       30
     031           1011         104        105       50
     031           1012         154        155       50
     031           1011         108        109       30
     031           1021         158        162       30
     039900        1011         204        206       50
     039900        1012         304        306       50
     039900        1011         210        212       30
     039900        1012         310        315       30
   

   test3 <- test2 %>%
   tidyr::pivot_wider(names_from = areax, values_from = time1:time2)

   test3$newarea <- (time1_29   time1_31)

CodePudding user response：

test2 %>%
  add_row(
    areax = 039900,
    group_by(.,indcodex, ownership)%>%
      summarise(across(-areax, sum), .groups = 'drop'))

  areax indcodex time1 time2 ownership
1     29     1011   100   101        50
2     29     1012   150   151        50
3     29     1011   102   103        30
4     29     1012   152   153        30
5     31     1011   104   105        50
6     31     1012   154   155        50
7     31     1011   108   109        30
8     31     1012   158   162        30
9  39900     1011   210   212        30
10 39900     1011   204   206        50
11 39900     1012   310   315        30
12 39900     1012   304   306        50

In Base R:

 rbind(test2, cbind(areax = 39900,
      aggregate(.~indcodex   ownership, test2[-1], sum)))

   areax indcodex time1 time2 ownership
1     29     1011   100   101        50
2     29     1012   150   151        50
3     29     1011   102   103        30
4     29     1012   152   153        30
5     31     1011   104   105        50
6     31     1012   154   155        50
7     31     1011   108   109        30
8     31     1012   158   162        30
9  39900     1011   210   212        30
10 39900     1012   310   315        30
11 39900     1011   204   206        50
12 39900     1012   304   306        50