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)
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