rdplyrdata.tablepivot

# How best to do this specific row operation in R?

So for this task, in my real data set. I have 18 rows that are indcode = 000000 and ownership code = 10. The differentiating factor is area. Likewise, I have 18 rows that are indcode = 4911 and ownership code = 10. Sample data below narrows it to 4 for easy computing. Some context.. in my real data set, I have monthly data hence the year (02) and Month (Jan) from 02-Jan to 23-Jun. The 910 is the new indcode.. it represents total federal employment in a given area and time. Federal employment is defined as indcode = 000000 subtract indcode = 4911. the indcode = 55 is just to make it more realistic.

PS, I am having some difficulty with the "02-Jan" so feel free to rename it as Jan. Just trying to keep it in line with the real product.

`````` indcode <- c("000000","000000","000000","000000", "55", "4911","4911","4911","4911")
ownership <- c("10","10","10","10","10","10","10","10","10")
area <- c("000000","031","029","017","029","000000","031","029","017")
"02-Jan" <- c(1000,600,300,100,50,100,50,40,10)
"02-Feb" <- c(1003,601,301,101,51,101,51,41,11)

first <- data.frame(indcode, ownership, area, `02-Jan`, `02-Feb`)
``````

so for each area, here is an example. the actual 02 values would not be 1000-100 but rather 900 but I thought that this would make it clearer.

``````    indcode    ownership    area     02-Jan    02-Feb
910          10        000000    1000-100     1003-101
910          10        031       600-50       601-51
``````

Solution

• ``````library(dplyr)
first |>
summarize(across(3:4, ~max(.)-min(.)),
# OLD: summarize(across(3:4, ~paste(rev(range(.)), collapse = "-")),
.by = area) |>
#"3:4" refers to the 3rd and 4th column once we set aside the area grouping
# We could alternated specify the columns by name, e.g. X02.Jan:X02.Feb
mutate(indcode = 910, ownership = 10, .before = 1)
``````

Result

``````  indcode ownership   area X02.Jan X02.Feb
1     910        10 000000     900     902
2     910        10    031     550     550
3     910        10    029     260     260
4     910        10    017      90      90
``````