Validating totals over sex

Source: vignettes/dd_validate_totals_over_sex.Rmd

The purpose of this process is to check if counts by sex is equal to counts for both sexes and adjust in case of any difference i.e if Female counts + Male counts is different from the population for Both sexes, then Both sexes = Female + Male

There are two functions involved. dd_validate_totals_over_sex is implemented on births and deaths data while dd_validate_totals_over_sex_new is implemented on population data.

The functions are defined as follows:

dd_validate_totals_over_sex(data) 1

dd_validate_totals_over_sex_new(data) 2

Part 1: Validating totals over sex: births and deaths

To show how dd_validate_totals_over_sex_bd works, we will be using the validate_totals_over_sex dataset that is part of this package. This dataset contains death counts of Kuwait for the year 2009, obtained from the Demographic Yearbook.

## Load the packages required
library(rddharmony)
library(kableExtra)
library(dplyr)
library(purrr)
library(tidyr)

## Create a function to be used to generate the table output
tab_output <- function(tab) {
  kable(tab, booktabs = TRUE, align = "c", table.envir = "capctable", longtable = TRUE, row.names = FALSE) %>%
    kable_styling() %>%
    row_spec(0, bold = T, color = "white", background = "#6ebed8") %>%
    kable_paper(html_font = "helvetica") %>%
    scroll_box(width = "100%", height = "300px")
}
df <- validate_totals_over_sex_bd %>% 
  select(abridged, complete, SexID, AgeStart, AgeEnd, AgeSpan,AgeSort, AgeLabel,DataValue)

df %>% tab_output()
abridged complete SexID AgeStart AgeEnd AgeSpan AgeSort AgeLabel DataValue
TRUE FALSE 1 0 1 1 1 0 334.593407
TRUE FALSE 1 1 5 4 2 1-4 69.366926
TRUE FALSE 1 0 5 5 3 0-4 403.960332
TRUE FALSE 1 5 10 5 4 5-9 33.663361
TRUE FALSE 1 10 15 5 5 10-14 39.783972
TRUE FALSE 1 15 20 5 6 15-19 77.527741
TRUE FALSE 1 20 25 5 7 20-24 120.372018
TRUE FALSE 1 25 30 5 8 25-29 158.115787
TRUE FALSE 1 30 35 5 9 30-34 192.799250
TRUE FALSE 1 35 40 5 10 35-39 198.919861
TRUE FALSE 1 40 45 5 11 40-44 222.382203
TRUE FALSE 1 45 50 5 12 45-49 277.467703
TRUE FALSE 1 50 55 5 13 50-54 341.734120
TRUE FALSE 1 55 60 5 14 55-59 283.588314
TRUE FALSE 1 60 65 5 15 60-64 266.246583
TRUE FALSE 1 65 70 5 16 65-69 273.387296
TRUE FALSE 1 70 75 5 17 70-74 331.533101
TRUE FALSE 1 75 80 5 18 75-79 231.563120
TRUE FALSE 1 80 85 5 19 80-84 171.377111
TRUE FALSE 1 85 90 5 20 85-89 97.929778
TRUE FALSE 1 90 95 5 21 90-94 55.085500
TRUE FALSE 1 95 0 -1 176 95+ 28.562852
TRUE FALSE 1 0 -1 -1 184 Total 3806.000000
FALSE TRUE 1 0 1 1 1 0 334.593407
FALSE TRUE 1 1 2 1 29 1 19.381935
FALSE TRUE 1 2 3 1 30 2 16.321630
FALSE TRUE 1 3 4 1 31 3 15.301528
FALSE TRUE 1 4 5 1 32 4 18.361833
FALSE TRUE 1 5 6 1 33 5 6.120611
FALSE TRUE 1 6 7 1 34 6 8.160815
FALSE TRUE 1 7 8 1 35 7 5.100509
FALSE TRUE 1 8 9 1 36 8 1.020102
FALSE TRUE 1 9 10 1 37 9 13.261324
FALSE TRUE 1 10 11 1 38 10 7.140713
FALSE TRUE 1 11 12 1 39 11 4.080407
FALSE TRUE 1 12 13 1 40 12 3.060306
FALSE TRUE 1 13 14 1 41 13 12.241222
FALSE TRUE 1 14 15 1 42 14 13.261324
FALSE TRUE 1 15 16 1 43 15 9.180917
FALSE TRUE 1 16 17 1 44 16 15.301528
FALSE TRUE 1 17 18 1 45 17 20.402037
FALSE TRUE 1 18 19 1 46 18 10.201018
FALSE TRUE 1 19 20 1 47 19 22.442241
FALSE TRUE 1 20 21 1 48 20 22.442241
FALSE TRUE 1 21 22 1 49 21 23.462342
FALSE TRUE 1 22 23 1 50 22 18.361833
FALSE TRUE 1 23 24 1 51 23 30.603055
FALSE TRUE 1 24 25 1 52 24 25.502546
FALSE TRUE 1 25 26 1 53 25 26.522648
FALSE TRUE 1 26 27 1 54 26 45.904583
FALSE TRUE 1 27 28 1 55 27 31.623157
FALSE TRUE 1 28 29 1 56 28 29.582954
FALSE TRUE 1 29 30 1 57 29 24.482444
FALSE TRUE 1 30 31 1 58 30 35.703565
FALSE TRUE 1 31 32 1 59 31 31.623157
FALSE TRUE 1 32 33 1 60 32 41.824176
FALSE TRUE 1 33 34 1 61 33 43.864379
FALSE TRUE 1 34 35 1 62 34 39.783972
FALSE TRUE 1 35 36 1 63 35 32.643259
FALSE TRUE 1 36 37 1 64 36 40.804074
FALSE TRUE 1 37 38 1 65 37 42.844278
FALSE TRUE 1 38 39 1 66 38 47.944787
FALSE TRUE 1 39 40 1 67 39 34.683463
FALSE TRUE 1 40 41 1 68 40 45.904583
FALSE TRUE 1 41 42 1 69 41 49.984991
FALSE TRUE 1 42 43 1 70 42 44.884481
FALSE TRUE 1 43 44 1 71 43 29.582954
FALSE TRUE 1 44 45 1 72 44 52.025194
FALSE TRUE 1 45 46 1 73 45 52.025194
FALSE TRUE 1 46 47 1 74 46 54.065398
FALSE TRUE 1 47 48 1 75 47 57.125704
FALSE TRUE 1 48 49 1 76 48 44.884481
FALSE TRUE 1 49 50 1 77 49 69.366926
FALSE TRUE 1 50 51 1 78 50 59.165907
FALSE TRUE 1 51 52 1 79 51 79.567944
FALSE TRUE 1 52 53 1 80 52 70.387028
FALSE TRUE 1 53 54 1 81 53 80.588046
FALSE TRUE 1 54 55 1 82 54 52.025194
FALSE TRUE 1 55 56 1 83 55 65.286518
FALSE TRUE 1 56 57 1 84 56 66.306620
FALSE TRUE 1 57 58 1 85 57 56.105602
FALSE TRUE 1 58 59 1 86 58 51.005093
FALSE TRUE 1 59 60 1 87 59 44.884481
FALSE TRUE 1 60 61 1 88 60 58.145805
FALSE TRUE 1 61 62 1 89 61 37.743768
FALSE TRUE 1 62 63 1 90 62 52.025194
FALSE TRUE 1 63 64 1 91 63 59.165907
FALSE TRUE 1 64 65 1 92 64 59.165907
FALSE TRUE 1 65 66 1 93 65 61.206111
FALSE TRUE 1 66 67 1 94 66 51.005093
FALSE TRUE 1 67 68 1 95 67 55.085500
FALSE TRUE 1 68 69 1 96 68 44.884481
FALSE TRUE 1 69 70 1 97 69 61.206111
FALSE TRUE 1 70 71 1 98 70 70.387028
FALSE TRUE 1 71 72 1 99 71 61.206111
FALSE TRUE 1 72 73 1 100 72 69.366926
FALSE TRUE 1 73 74 1 101 73 68.346824
FALSE TRUE 1 74 75 1 102 74 62.226213
FALSE TRUE 1 75 76 1 103 75 54.065398
FALSE TRUE 1 76 77 1 104 76 51.005093
FALSE TRUE 1 77 78 1 105 77 46.924685
FALSE TRUE 1 78 79 1 106 78 39.783972
FALSE TRUE 1 79 80 1 107 79 39.783972
FALSE TRUE 1 80 81 1 108 80 46.924685
FALSE TRUE 1 81 82 1 109 81 26.522648
FALSE TRUE 1 82 83 1 110 82 39.783972
FALSE TRUE 1 83 84 1 111 83 27.542750
FALSE TRUE 1 84 85 1 112 84 30.603055
FALSE TRUE 1 85 86 1 113 85 20.402037
FALSE TRUE 1 86 87 1 114 86 25.502546
FALSE TRUE 1 87 88 1 115 87 22.442241
FALSE TRUE 1 88 89 1 116 88 19.381935
FALSE TRUE 1 89 90 1 117 89 10.201018
FALSE TRUE 1 90 91 1 118 90 13.261324
FALSE TRUE 1 91 92 1 119 91 10.201018
FALSE TRUE 1 92 93 1 120 92 13.261324
FALSE TRUE 1 93 94 1 121 93 14.281426
FALSE TRUE 1 94 95 1 122 94 4.080407
FALSE TRUE 1 95 0 -1 176 95+ 28.562852
FALSE TRUE 1 0 -1 -1 184 Total 3806.000000
TRUE FALSE 2 0 1 1 1 0 286.381910
TRUE FALSE 2 1 5 4 2 1-4 40.175879
TRUE FALSE 2 0 5 5 3 0-4 326.557789
TRUE FALSE 2 5 10 5 4 5-9 29.874372
TRUE FALSE 2 10 15 5 5 10-14 35.025126
TRUE FALSE 2 15 20 5 6 15-19 30.904523
TRUE FALSE 2 20 25 5 7 20-24 39.145729
TRUE FALSE 2 25 30 5 8 25-29 76.231156
TRUE FALSE 2 30 35 5 9 30-34 70.050251
TRUE FALSE 2 35 40 5 10 35-39 81.381910
TRUE FALSE 2 40 45 5 11 40-44 97.864322
TRUE FALSE 2 45 50 5 12 45-49 87.562814
TRUE FALSE 2 50 55 5 13 50-54 109.195980
TRUE FALSE 2 55 60 5 14 55-59 130.829146
TRUE FALSE 2 60 65 5 15 60-64 208.090452
TRUE FALSE 2 65 70 5 16 65-69 237.964824
TRUE FALSE 2 70 75 5 17 70-74 271.959799
TRUE FALSE 2 75 80 5 18 75-79 277.110553
TRUE FALSE 2 80 85 5 19 80-84 178.216080
TRUE FALSE 2 85 90 5 20 85-89 88.592965
TRUE FALSE 2 90 95 5 21 90-94 54.597990
TRUE FALSE 2 95 0 -1 176 95+ 28.844221
TRUE FALSE 2 0 -1 -1 184 Total 2460.000000
FALSE TRUE 2 0 1 1 1 0 286.381910
FALSE TRUE 2 1 2 1 29 1 14.422111
FALSE TRUE 2 2 3 1 30 2 9.271357
FALSE TRUE 2 3 4 1 31 3 8.241206
FALSE TRUE 2 4 5 1 32 4 8.241206
FALSE TRUE 2 5 6 1 33 5 6.180904
FALSE TRUE 2 6 7 1 34 6 5.150754
FALSE TRUE 2 7 8 1 35 7 6.180904
FALSE TRUE 2 8 9 1 36 8 5.150754
FALSE TRUE 2 9 10 1 37 9 7.211055
FALSE TRUE 2 10 11 1 38 10 3.090452
FALSE TRUE 2 11 12 1 39 11 7.211055
FALSE TRUE 2 12 13 1 40 12 6.180904
FALSE TRUE 2 13 14 1 41 13 5.150754
FALSE TRUE 2 14 15 1 42 14 13.391960
FALSE TRUE 2 15 16 1 43 15 8.241206
FALSE TRUE 2 16 17 1 44 16 4.120603
FALSE TRUE 2 17 18 1 45 17 3.090452
FALSE TRUE 2 18 19 1 46 18 8.241206
FALSE TRUE 2 19 20 1 47 19 7.211055
FALSE TRUE 2 20 21 1 48 20 6.180904
FALSE TRUE 2 21 22 1 49 21 4.120603
FALSE TRUE 2 22 23 1 50 22 7.211055
FALSE TRUE 2 23 24 1 51 23 8.241206
FALSE TRUE 2 24 25 1 52 24 13.391960
FALSE TRUE 2 25 26 1 53 25 17.512563
FALSE TRUE 2 26 27 1 54 26 17.512563
FALSE TRUE 2 27 28 1 55 27 10.301507
FALSE TRUE 2 28 29 1 56 28 10.301507
FALSE TRUE 2 29 30 1 57 29 20.603015
FALSE TRUE 2 30 31 1 58 30 15.452261
FALSE TRUE 2 31 32 1 59 31 7.211055
FALSE TRUE 2 32 33 1 60 32 12.361809
FALSE TRUE 2 33 34 1 61 33 12.361809
FALSE TRUE 2 34 35 1 62 34 22.663317
FALSE TRUE 2 35 36 1 63 35 16.482412
FALSE TRUE 2 36 37 1 64 36 14.422111
FALSE TRUE 2 37 38 1 65 37 16.482412
FALSE TRUE 2 38 39 1 66 38 15.452261
FALSE TRUE 2 39 40 1 67 39 18.542714
FALSE TRUE 2 40 41 1 68 40 18.542714
FALSE TRUE 2 41 42 1 69 41 22.663317
FALSE TRUE 2 42 43 1 70 42 18.542714
FALSE TRUE 2 43 44 1 71 43 18.542714
FALSE TRUE 2 44 45 1 72 44 19.572864
FALSE TRUE 2 45 46 1 73 45 12.361809
FALSE TRUE 2 46 47 1 74 46 12.361809
FALSE TRUE 2 47 48 1 75 47 21.633166
FALSE TRUE 2 48 49 1 76 48 22.663317
FALSE TRUE 2 49 50 1 77 49 18.542714
FALSE TRUE 2 50 51 1 78 50 19.572864
FALSE TRUE 2 51 52 1 79 51 18.542714
FALSE TRUE 2 52 53 1 80 52 24.723618
FALSE TRUE 2 53 54 1 81 53 19.572864
FALSE TRUE 2 54 55 1 82 54 26.783920
FALSE TRUE 2 55 56 1 83 55 26.783920
FALSE TRUE 2 56 57 1 84 56 19.572864
FALSE TRUE 2 57 58 1 85 57 27.814070
FALSE TRUE 2 58 59 1 86 58 25.753769
FALSE TRUE 2 59 60 1 87 59 30.904523
FALSE TRUE 2 60 61 1 88 60 27.814070
FALSE TRUE 2 61 62 1 89 61 25.753769
FALSE TRUE 2 62 63 1 90 62 36.055276
FALSE TRUE 2 63 64 1 91 63 58.718593
FALSE TRUE 2 64 65 1 92 64 59.748744
FALSE TRUE 2 65 66 1 93 65 42.236181
FALSE TRUE 2 66 67 1 94 66 51.507538
FALSE TRUE 2 67 68 1 95 67 46.356784
FALSE TRUE 2 68 69 1 96 68 49.447236
FALSE TRUE 2 69 70 1 97 69 48.417085
FALSE TRUE 2 70 71 1 98 70 46.356784
FALSE TRUE 2 71 72 1 99 71 61.809045
FALSE TRUE 2 72 73 1 100 72 49.447236
FALSE TRUE 2 73 74 1 101 73 53.567839
FALSE TRUE 2 74 75 1 102 74 60.778894
FALSE TRUE 2 75 76 1 103 75 48.417085
FALSE TRUE 2 76 77 1 104 76 46.356784
FALSE TRUE 2 77 78 1 105 77 63.869347
FALSE TRUE 2 78 79 1 106 78 56.658291
FALSE TRUE 2 79 80 1 107 79 61.809045
FALSE TRUE 2 80 81 1 108 80 38.115578
FALSE TRUE 2 81 82 1 109 81 31.934673
FALSE TRUE 2 82 83 1 110 82 32.964824
FALSE TRUE 2 83 84 1 111 83 30.904523
FALSE TRUE 2 84 85 1 112 84 44.296482
FALSE TRUE 2 85 86 1 113 85 19.572864
FALSE TRUE 2 86 87 1 114 86 15.452261
FALSE TRUE 2 87 88 1 115 87 21.633166
FALSE TRUE 2 88 89 1 116 88 18.542714
FALSE TRUE 2 89 90 1 117 89 13.391960
FALSE TRUE 2 90 91 1 118 90 12.361809
FALSE TRUE 2 91 92 1 119 91 13.391960
FALSE TRUE 2 92 93 1 120 92 10.301507
FALSE TRUE 2 93 94 1 121 93 7.211055
FALSE TRUE 2 94 95 1 122 94 11.331658
FALSE TRUE 2 95 0 -1 176 95+ 28.844221
FALSE TRUE 2 0 -1 -1 184 Total 2460.000000
TRUE FALSE 3 0 1 1 1 0 620.558261
TRUE FALSE 3 1 5 4 2 1-4 109.570518
TRUE FALSE 3 0 5 5 3 0-4 730.128779
TRUE FALSE 3 5 10 5 4 5-9 63.489459
TRUE FALSE 3 10 15 5 5 10-14 74.753718
TRUE FALSE 3 15 20 5 6 15-19 108.546494
TRUE FALSE 3 20 25 5 7 20-24 159.747671
TRUE FALSE 3 25 30 5 8 25-29 234.501389
TRUE FALSE 3 30 35 5 9 30-34 263.174048
TRUE FALSE 3 35 40 5 10 35-39 280.582448
TRUE FALSE 3 40 45 5 11 40-44 320.519366
TRUE FALSE 3 45 50 5 12 45-49 365.576401
TRUE FALSE 3 50 55 5 13 50-54 451.594378
TRUE FALSE 3 55 60 5 14 55-59 414.729531
TRUE FALSE 3 60 65 5 15 60-64 474.122896
TRUE FALSE 3 65 70 5 16 65-69 510.987743
TRUE FALSE 3 70 75 5 17 70-74 603.149861
TRUE FALSE 3 75 80 5 18 75-79 507.915673
TRUE FALSE 3 80 85 5 19 80-84 349.192025
TRUE FALSE 3 85 90 5 20 85-89 186.372283
TRUE FALSE 3 90 95 5 21 90-94 109.570518
TRUE FALSE 3 95 0 -1 176 95+ 57.345318
TRUE FALSE 3 0 -1 -1 184 Total 6266.000000
FALSE TRUE 3 0 1 1 1 0 620.558261
FALSE TRUE 3 1 2 1 29 1 33.792777
FALSE TRUE 3 2 3 1 30 2 25.600588
FALSE TRUE 3 3 4 1 31 3 23.552541
FALSE TRUE 3 4 5 1 32 4 26.624612
FALSE TRUE 3 5 6 1 33 5 12.288282
FALSE TRUE 3 6 7 1 34 6 13.312306
FALSE TRUE 3 7 8 1 35 7 11.264259
FALSE TRUE 3 8 9 1 36 8 6.144141
FALSE TRUE 3 9 10 1 37 9 20.480471
FALSE TRUE 3 10 11 1 38 10 10.240235
FALSE TRUE 3 11 12 1 39 11 11.264259
FALSE TRUE 3 12 13 1 40 12 9.216212
FALSE TRUE 3 13 14 1 41 13 17.408400
FALSE TRUE 3 14 15 1 42 14 26.624612
FALSE TRUE 3 15 16 1 43 15 17.408400
FALSE TRUE 3 16 17 1 44 16 19.456447
FALSE TRUE 3 17 18 1 45 17 23.552541
FALSE TRUE 3 18 19 1 46 18 18.432424
FALSE TRUE 3 19 20 1 47 19 29.696683
FALSE TRUE 3 20 21 1 48 20 28.672659
FALSE TRUE 3 21 22 1 49 21 27.648635
FALSE TRUE 3 22 23 1 50 22 25.600588
FALSE TRUE 3 23 24 1 51 23 38.912894
FALSE TRUE 3 24 25 1 52 24 38.912894
FALSE TRUE 3 25 26 1 53 25 44.033012
FALSE TRUE 3 26 27 1 54 26 63.489459
FALSE TRUE 3 27 28 1 55 27 41.984965
FALSE TRUE 3 28 29 1 56 28 39.936918
FALSE TRUE 3 29 30 1 57 29 45.057035
FALSE TRUE 3 30 31 1 58 30 51.201177
FALSE TRUE 3 31 32 1 59 31 38.912894
FALSE TRUE 3 32 33 1 60 32 54.273247
FALSE TRUE 3 33 34 1 61 33 56.321294
FALSE TRUE 3 34 35 1 62 34 62.465435
FALSE TRUE 3 35 36 1 63 35 49.153130
FALSE TRUE 3 36 37 1 64 36 55.297271
FALSE TRUE 3 37 38 1 65 37 59.393365
FALSE TRUE 3 38 39 1 66 38 63.489459
FALSE TRUE 3 39 40 1 67 39 53.249224
FALSE TRUE 3 40 41 1 68 40 64.513483
FALSE TRUE 3 41 42 1 69 41 72.705671
FALSE TRUE 3 42 43 1 70 42 63.489459
FALSE TRUE 3 43 44 1 71 43 48.129106
FALSE TRUE 3 44 45 1 72 44 71.681647
FALSE TRUE 3 45 46 1 73 45 64.513483
FALSE TRUE 3 46 47 1 74 46 66.561530
FALSE TRUE 3 47 48 1 75 47 78.849812
FALSE TRUE 3 48 49 1 76 48 67.585553
FALSE TRUE 3 49 50 1 77 49 88.066024
FALSE TRUE 3 50 51 1 78 50 78.849812
FALSE TRUE 3 51 52 1 79 51 98.306259
FALSE TRUE 3 52 53 1 80 52 95.234189
FALSE TRUE 3 53 54 1 81 53 100.354306
FALSE TRUE 3 54 55 1 82 54 78.849812
FALSE TRUE 3 55 56 1 83 55 92.162118
FALSE TRUE 3 56 57 1 84 56 86.017977
FALSE TRUE 3 57 58 1 85 57 83.969930
FALSE TRUE 3 58 59 1 86 58 76.801765
FALSE TRUE 3 59 60 1 87 59 75.777742
FALSE TRUE 3 60 61 1 88 60 86.017977
FALSE TRUE 3 61 62 1 89 61 63.489459
FALSE TRUE 3 62 63 1 90 62 88.066024
FALSE TRUE 3 63 64 1 91 63 117.762706
FALSE TRUE 3 64 65 1 92 64 118.786730
FALSE TRUE 3 65 66 1 93 65 103.426377
FALSE TRUE 3 66 67 1 94 66 102.402353
FALSE TRUE 3 67 68 1 95 67 101.378330
FALSE TRUE 3 68 69 1 96 68 94.210165
FALSE TRUE 3 69 70 1 97 69 109.570518
FALSE TRUE 3 70 71 1 98 70 116.738683
FALSE TRUE 3 71 72 1 99 71 122.882824
FALSE TRUE 3 72 73 1 100 72 118.786730
FALSE TRUE 3 73 74 1 101 73 121.858801
FALSE TRUE 3 74 75 1 102 74 122.882824
FALSE TRUE 3 75 76 1 103 75 102.402353
FALSE TRUE 3 76 77 1 104 76 97.282236
FALSE TRUE 3 77 78 1 105 77 110.594542
FALSE TRUE 3 78 79 1 106 78 96.258212
FALSE TRUE 3 79 80 1 107 79 101.378330
FALSE TRUE 3 80 81 1 108 80 84.993953
FALSE TRUE 3 81 82 1 109 81 58.369341
FALSE TRUE 3 82 83 1 110 82 72.705671
FALSE TRUE 3 83 84 1 111 83 58.369341
FALSE TRUE 3 84 85 1 112 84 74.753718
FALSE TRUE 3 85 86 1 113 85 39.936918
FALSE TRUE 3 86 87 1 114 86 40.960941
FALSE TRUE 3 87 88 1 115 87 44.033012
FALSE TRUE 3 88 89 1 116 88 37.888871
FALSE TRUE 3 89 90 1 117 89 23.552541
FALSE TRUE 3 90 91 1 118 90 25.600588
FALSE TRUE 3 91 92 1 119 91 23.552541
FALSE TRUE 3 92 93 1 120 92 23.552541
FALSE TRUE 3 93 94 1 121 93 21.504494
FALSE TRUE 3 94 95 1 122 94 15.360353
FALSE TRUE 3 95 0 -1 176 95+ 57.345318
FALSE TRUE 3 0 -1 -1 184 Total 6266.000000

From the data, it can be seen that that Male counts and Female counts add up to Both sexes counts. For demonstration purposes, we will alter the data so that it can represent an imperfect situation where the totals do not add up. We will edit the abridged Both sexes Total value from 6266 to 8978 and the complete Female Total value from 2460 to 3810.

df <- df %>% 
      mutate(DataValue = ifelse(abridged == TRUE & AgeLabel == "Total" & SexID == 3, 8978, DataValue),
             DataValue = ifelse(complete == TRUE & AgeLabel == "Total" & SexID == 2, 3810, DataValue))
df %>% tab_output()
abridged complete SexID AgeStart AgeEnd AgeSpan AgeSort AgeLabel DataValue
TRUE FALSE 1 0 1 1 1 0 334.593407
TRUE FALSE 1 1 5 4 2 1-4 69.366926
TRUE FALSE 1 0 5 5 3 0-4 403.960332
TRUE FALSE 1 5 10 5 4 5-9 33.663361
TRUE FALSE 1 10 15 5 5 10-14 39.783972
TRUE FALSE 1 15 20 5 6 15-19 77.527741
TRUE FALSE 1 20 25 5 7 20-24 120.372018
TRUE FALSE 1 25 30 5 8 25-29 158.115787
TRUE FALSE 1 30 35 5 9 30-34 192.799250
TRUE FALSE 1 35 40 5 10 35-39 198.919861
TRUE FALSE 1 40 45 5 11 40-44 222.382203
TRUE FALSE 1 45 50 5 12 45-49 277.467703
TRUE FALSE 1 50 55 5 13 50-54 341.734120
TRUE FALSE 1 55 60 5 14 55-59 283.588314
TRUE FALSE 1 60 65 5 15 60-64 266.246583
TRUE FALSE 1 65 70 5 16 65-69 273.387296
TRUE FALSE 1 70 75 5 17 70-74 331.533101
TRUE FALSE 1 75 80 5 18 75-79 231.563120
TRUE FALSE 1 80 85 5 19 80-84 171.377111
TRUE FALSE 1 85 90 5 20 85-89 97.929778
TRUE FALSE 1 90 95 5 21 90-94 55.085500
TRUE FALSE 1 95 0 -1 176 95+ 28.562852
TRUE FALSE 1 0 -1 -1 184 Total 3806.000000
FALSE TRUE 1 0 1 1 1 0 334.593407
FALSE TRUE 1 1 2 1 29 1 19.381935
FALSE TRUE 1 2 3 1 30 2 16.321630
FALSE TRUE 1 3 4 1 31 3 15.301528
FALSE TRUE 1 4 5 1 32 4 18.361833
FALSE TRUE 1 5 6 1 33 5 6.120611
FALSE TRUE 1 6 7 1 34 6 8.160815
FALSE TRUE 1 7 8 1 35 7 5.100509
FALSE TRUE 1 8 9 1 36 8 1.020102
FALSE TRUE 1 9 10 1 37 9 13.261324
FALSE TRUE 1 10 11 1 38 10 7.140713
FALSE TRUE 1 11 12 1 39 11 4.080407
FALSE TRUE 1 12 13 1 40 12 3.060306
FALSE TRUE 1 13 14 1 41 13 12.241222
FALSE TRUE 1 14 15 1 42 14 13.261324
FALSE TRUE 1 15 16 1 43 15 9.180917
FALSE TRUE 1 16 17 1 44 16 15.301528
FALSE TRUE 1 17 18 1 45 17 20.402037
FALSE TRUE 1 18 19 1 46 18 10.201018
FALSE TRUE 1 19 20 1 47 19 22.442241
FALSE TRUE 1 20 21 1 48 20 22.442241
FALSE TRUE 1 21 22 1 49 21 23.462342
FALSE TRUE 1 22 23 1 50 22 18.361833
FALSE TRUE 1 23 24 1 51 23 30.603055
FALSE TRUE 1 24 25 1 52 24 25.502546
FALSE TRUE 1 25 26 1 53 25 26.522648
FALSE TRUE 1 26 27 1 54 26 45.904583
FALSE TRUE 1 27 28 1 55 27 31.623157
FALSE TRUE 1 28 29 1 56 28 29.582954
FALSE TRUE 1 29 30 1 57 29 24.482444
FALSE TRUE 1 30 31 1 58 30 35.703565
FALSE TRUE 1 31 32 1 59 31 31.623157
FALSE TRUE 1 32 33 1 60 32 41.824176
FALSE TRUE 1 33 34 1 61 33 43.864379
FALSE TRUE 1 34 35 1 62 34 39.783972
FALSE TRUE 1 35 36 1 63 35 32.643259
FALSE TRUE 1 36 37 1 64 36 40.804074
FALSE TRUE 1 37 38 1 65 37 42.844278
FALSE TRUE 1 38 39 1 66 38 47.944787
FALSE TRUE 1 39 40 1 67 39 34.683463
FALSE TRUE 1 40 41 1 68 40 45.904583
FALSE TRUE 1 41 42 1 69 41 49.984991
FALSE TRUE 1 42 43 1 70 42 44.884481
FALSE TRUE 1 43 44 1 71 43 29.582954
FALSE TRUE 1 44 45 1 72 44 52.025194
FALSE TRUE 1 45 46 1 73 45 52.025194
FALSE TRUE 1 46 47 1 74 46 54.065398
FALSE TRUE 1 47 48 1 75 47 57.125704
FALSE TRUE 1 48 49 1 76 48 44.884481
FALSE TRUE 1 49 50 1 77 49 69.366926
FALSE TRUE 1 50 51 1 78 50 59.165907
FALSE TRUE 1 51 52 1 79 51 79.567944
FALSE TRUE 1 52 53 1 80 52 70.387028
FALSE TRUE 1 53 54 1 81 53 80.588046
FALSE TRUE 1 54 55 1 82 54 52.025194
FALSE TRUE 1 55 56 1 83 55 65.286518
FALSE TRUE 1 56 57 1 84 56 66.306620
FALSE TRUE 1 57 58 1 85 57 56.105602
FALSE TRUE 1 58 59 1 86 58 51.005093
FALSE TRUE 1 59 60 1 87 59 44.884481
FALSE TRUE 1 60 61 1 88 60 58.145805
FALSE TRUE 1 61 62 1 89 61 37.743768
FALSE TRUE 1 62 63 1 90 62 52.025194
FALSE TRUE 1 63 64 1 91 63 59.165907
FALSE TRUE 1 64 65 1 92 64 59.165907
FALSE TRUE 1 65 66 1 93 65 61.206111
FALSE TRUE 1 66 67 1 94 66 51.005093
FALSE TRUE 1 67 68 1 95 67 55.085500
FALSE TRUE 1 68 69 1 96 68 44.884481
FALSE TRUE 1 69 70 1 97 69 61.206111
FALSE TRUE 1 70 71 1 98 70 70.387028
FALSE TRUE 1 71 72 1 99 71 61.206111
FALSE TRUE 1 72 73 1 100 72 69.366926
FALSE TRUE 1 73 74 1 101 73 68.346824
FALSE TRUE 1 74 75 1 102 74 62.226213
FALSE TRUE 1 75 76 1 103 75 54.065398
FALSE TRUE 1 76 77 1 104 76 51.005093
FALSE TRUE 1 77 78 1 105 77 46.924685
FALSE TRUE 1 78 79 1 106 78 39.783972
FALSE TRUE 1 79 80 1 107 79 39.783972
FALSE TRUE 1 80 81 1 108 80 46.924685
FALSE TRUE 1 81 82 1 109 81 26.522648
FALSE TRUE 1 82 83 1 110 82 39.783972
FALSE TRUE 1 83 84 1 111 83 27.542750
FALSE TRUE 1 84 85 1 112 84 30.603055
FALSE TRUE 1 85 86 1 113 85 20.402037
FALSE TRUE 1 86 87 1 114 86 25.502546
FALSE TRUE 1 87 88 1 115 87 22.442241
FALSE TRUE 1 88 89 1 116 88 19.381935
FALSE TRUE 1 89 90 1 117 89 10.201018
FALSE TRUE 1 90 91 1 118 90 13.261324
FALSE TRUE 1 91 92 1 119 91 10.201018
FALSE TRUE 1 92 93 1 120 92 13.261324
FALSE TRUE 1 93 94 1 121 93 14.281426
FALSE TRUE 1 94 95 1 122 94 4.080407
FALSE TRUE 1 95 0 -1 176 95+ 28.562852
FALSE TRUE 1 0 -1 -1 184 Total 3806.000000
TRUE FALSE 2 0 1 1 1 0 286.381910
TRUE FALSE 2 1 5 4 2 1-4 40.175879
TRUE FALSE 2 0 5 5 3 0-4 326.557789
TRUE FALSE 2 5 10 5 4 5-9 29.874372
TRUE FALSE 2 10 15 5 5 10-14 35.025126
TRUE FALSE 2 15 20 5 6 15-19 30.904523
TRUE FALSE 2 20 25 5 7 20-24 39.145729
TRUE FALSE 2 25 30 5 8 25-29 76.231156
TRUE FALSE 2 30 35 5 9 30-34 70.050251
TRUE FALSE 2 35 40 5 10 35-39 81.381910
TRUE FALSE 2 40 45 5 11 40-44 97.864322
TRUE FALSE 2 45 50 5 12 45-49 87.562814
TRUE FALSE 2 50 55 5 13 50-54 109.195980
TRUE FALSE 2 55 60 5 14 55-59 130.829146
TRUE FALSE 2 60 65 5 15 60-64 208.090452
TRUE FALSE 2 65 70 5 16 65-69 237.964824
TRUE FALSE 2 70 75 5 17 70-74 271.959799
TRUE FALSE 2 75 80 5 18 75-79 277.110553
TRUE FALSE 2 80 85 5 19 80-84 178.216080
TRUE FALSE 2 85 90 5 20 85-89 88.592965
TRUE FALSE 2 90 95 5 21 90-94 54.597990
TRUE FALSE 2 95 0 -1 176 95+ 28.844221
TRUE FALSE 2 0 -1 -1 184 Total 2460.000000
FALSE TRUE 2 0 1 1 1 0 286.381910
FALSE TRUE 2 1 2 1 29 1 14.422111
FALSE TRUE 2 2 3 1 30 2 9.271357
FALSE TRUE 2 3 4 1 31 3 8.241206
FALSE TRUE 2 4 5 1 32 4 8.241206
FALSE TRUE 2 5 6 1 33 5 6.180904
FALSE TRUE 2 6 7 1 34 6 5.150754
FALSE TRUE 2 7 8 1 35 7 6.180904
FALSE TRUE 2 8 9 1 36 8 5.150754
FALSE TRUE 2 9 10 1 37 9 7.211055
FALSE TRUE 2 10 11 1 38 10 3.090452
FALSE TRUE 2 11 12 1 39 11 7.211055
FALSE TRUE 2 12 13 1 40 12 6.180904
FALSE TRUE 2 13 14 1 41 13 5.150754
FALSE TRUE 2 14 15 1 42 14 13.391960
FALSE TRUE 2 15 16 1 43 15 8.241206
FALSE TRUE 2 16 17 1 44 16 4.120603
FALSE TRUE 2 17 18 1 45 17 3.090452
FALSE TRUE 2 18 19 1 46 18 8.241206
FALSE TRUE 2 19 20 1 47 19 7.211055
FALSE TRUE 2 20 21 1 48 20 6.180904
FALSE TRUE 2 21 22 1 49 21 4.120603
FALSE TRUE 2 22 23 1 50 22 7.211055
FALSE TRUE 2 23 24 1 51 23 8.241206
FALSE TRUE 2 24 25 1 52 24 13.391960
FALSE TRUE 2 25 26 1 53 25 17.512563
FALSE TRUE 2 26 27 1 54 26 17.512563
FALSE TRUE 2 27 28 1 55 27 10.301507
FALSE TRUE 2 28 29 1 56 28 10.301507
FALSE TRUE 2 29 30 1 57 29 20.603015
FALSE TRUE 2 30 31 1 58 30 15.452261
FALSE TRUE 2 31 32 1 59 31 7.211055
FALSE TRUE 2 32 33 1 60 32 12.361809
FALSE TRUE 2 33 34 1 61 33 12.361809
FALSE TRUE 2 34 35 1 62 34 22.663317
FALSE TRUE 2 35 36 1 63 35 16.482412
FALSE TRUE 2 36 37 1 64 36 14.422111
FALSE TRUE 2 37 38 1 65 37 16.482412
FALSE TRUE 2 38 39 1 66 38 15.452261
FALSE TRUE 2 39 40 1 67 39 18.542714
FALSE TRUE 2 40 41 1 68 40 18.542714
FALSE TRUE 2 41 42 1 69 41 22.663317
FALSE TRUE 2 42 43 1 70 42 18.542714
FALSE TRUE 2 43 44 1 71 43 18.542714
FALSE TRUE 2 44 45 1 72 44 19.572864
FALSE TRUE 2 45 46 1 73 45 12.361809
FALSE TRUE 2 46 47 1 74 46 12.361809
FALSE TRUE 2 47 48 1 75 47 21.633166
FALSE TRUE 2 48 49 1 76 48 22.663317
FALSE TRUE 2 49 50 1 77 49 18.542714
FALSE TRUE 2 50 51 1 78 50 19.572864
FALSE TRUE 2 51 52 1 79 51 18.542714
FALSE TRUE 2 52 53 1 80 52 24.723618
FALSE TRUE 2 53 54 1 81 53 19.572864
FALSE TRUE 2 54 55 1 82 54 26.783920
FALSE TRUE 2 55 56 1 83 55 26.783920
FALSE TRUE 2 56 57 1 84 56 19.572864
FALSE TRUE 2 57 58 1 85 57 27.814070
FALSE TRUE 2 58 59 1 86 58 25.753769
FALSE TRUE 2 59 60 1 87 59 30.904523
FALSE TRUE 2 60 61 1 88 60 27.814070
FALSE TRUE 2 61 62 1 89 61 25.753769
FALSE TRUE 2 62 63 1 90 62 36.055276
FALSE TRUE 2 63 64 1 91 63 58.718593
FALSE TRUE 2 64 65 1 92 64 59.748744
FALSE TRUE 2 65 66 1 93 65 42.236181
FALSE TRUE 2 66 67 1 94 66 51.507538
FALSE TRUE 2 67 68 1 95 67 46.356784
FALSE TRUE 2 68 69 1 96 68 49.447236
FALSE TRUE 2 69 70 1 97 69 48.417085
FALSE TRUE 2 70 71 1 98 70 46.356784
FALSE TRUE 2 71 72 1 99 71 61.809045
FALSE TRUE 2 72 73 1 100 72 49.447236
FALSE TRUE 2 73 74 1 101 73 53.567839
FALSE TRUE 2 74 75 1 102 74 60.778894
FALSE TRUE 2 75 76 1 103 75 48.417085
FALSE TRUE 2 76 77 1 104 76 46.356784
FALSE TRUE 2 77 78 1 105 77 63.869347
FALSE TRUE 2 78 79 1 106 78 56.658291
FALSE TRUE 2 79 80 1 107 79 61.809045
FALSE TRUE 2 80 81 1 108 80 38.115578
FALSE TRUE 2 81 82 1 109 81 31.934673
FALSE TRUE 2 82 83 1 110 82 32.964824
FALSE TRUE 2 83 84 1 111 83 30.904523
FALSE TRUE 2 84 85 1 112 84 44.296482
FALSE TRUE 2 85 86 1 113 85 19.572864
FALSE TRUE 2 86 87 1 114 86 15.452261
FALSE TRUE 2 87 88 1 115 87 21.633166
FALSE TRUE 2 88 89 1 116 88 18.542714
FALSE TRUE 2 89 90 1 117 89 13.391960
FALSE TRUE 2 90 91 1 118 90 12.361809
FALSE TRUE 2 91 92 1 119 91 13.391960
FALSE TRUE 2 92 93 1 120 92 10.301507
FALSE TRUE 2 93 94 1 121 93 7.211055
FALSE TRUE 2 94 95 1 122 94 11.331658
FALSE TRUE 2 95 0 -1 176 95+ 28.844221
FALSE TRUE 2 0 -1 -1 184 Total 3810.000000
TRUE FALSE 3 0 1 1 1 0 620.558261
TRUE FALSE 3 1 5 4 2 1-4 109.570518
TRUE FALSE 3 0 5 5 3 0-4 730.128779
TRUE FALSE 3 5 10 5 4 5-9 63.489459
TRUE FALSE 3 10 15 5 5 10-14 74.753718
TRUE FALSE 3 15 20 5 6 15-19 108.546494
TRUE FALSE 3 20 25 5 7 20-24 159.747671
TRUE FALSE 3 25 30 5 8 25-29 234.501389
TRUE FALSE 3 30 35 5 9 30-34 263.174048
TRUE FALSE 3 35 40 5 10 35-39 280.582448
TRUE FALSE 3 40 45 5 11 40-44 320.519366
TRUE FALSE 3 45 50 5 12 45-49 365.576401
TRUE FALSE 3 50 55 5 13 50-54 451.594378
TRUE FALSE 3 55 60 5 14 55-59 414.729531
TRUE FALSE 3 60 65 5 15 60-64 474.122896
TRUE FALSE 3 65 70 5 16 65-69 510.987743
TRUE FALSE 3 70 75 5 17 70-74 603.149861
TRUE FALSE 3 75 80 5 18 75-79 507.915673
TRUE FALSE 3 80 85 5 19 80-84 349.192025
TRUE FALSE 3 85 90 5 20 85-89 186.372283
TRUE FALSE 3 90 95 5 21 90-94 109.570518
TRUE FALSE 3 95 0 -1 176 95+ 57.345318
TRUE FALSE 3 0 -1 -1 184 Total 8978.000000
FALSE TRUE 3 0 1 1 1 0 620.558261
FALSE TRUE 3 1 2 1 29 1 33.792777
FALSE TRUE 3 2 3 1 30 2 25.600588
FALSE TRUE 3 3 4 1 31 3 23.552541
FALSE TRUE 3 4 5 1 32 4 26.624612
FALSE TRUE 3 5 6 1 33 5 12.288282
FALSE TRUE 3 6 7 1 34 6 13.312306
FALSE TRUE 3 7 8 1 35 7 11.264259
FALSE TRUE 3 8 9 1 36 8 6.144141
FALSE TRUE 3 9 10 1 37 9 20.480471
FALSE TRUE 3 10 11 1 38 10 10.240235
FALSE TRUE 3 11 12 1 39 11 11.264259
FALSE TRUE 3 12 13 1 40 12 9.216212
FALSE TRUE 3 13 14 1 41 13 17.408400
FALSE TRUE 3 14 15 1 42 14 26.624612
FALSE TRUE 3 15 16 1 43 15 17.408400
FALSE TRUE 3 16 17 1 44 16 19.456447
FALSE TRUE 3 17 18 1 45 17 23.552541
FALSE TRUE 3 18 19 1 46 18 18.432424
FALSE TRUE 3 19 20 1 47 19 29.696683
FALSE TRUE 3 20 21 1 48 20 28.672659
FALSE TRUE 3 21 22 1 49 21 27.648635
FALSE TRUE 3 22 23 1 50 22 25.600588
FALSE TRUE 3 23 24 1 51 23 38.912894
FALSE TRUE 3 24 25 1 52 24 38.912894
FALSE TRUE 3 25 26 1 53 25 44.033012
FALSE TRUE 3 26 27 1 54 26 63.489459
FALSE TRUE 3 27 28 1 55 27 41.984965
FALSE TRUE 3 28 29 1 56 28 39.936918
FALSE TRUE 3 29 30 1 57 29 45.057035
FALSE TRUE 3 30 31 1 58 30 51.201177
FALSE TRUE 3 31 32 1 59 31 38.912894
FALSE TRUE 3 32 33 1 60 32 54.273247
FALSE TRUE 3 33 34 1 61 33 56.321294
FALSE TRUE 3 34 35 1 62 34 62.465435
FALSE TRUE 3 35 36 1 63 35 49.153130
FALSE TRUE 3 36 37 1 64 36 55.297271
FALSE TRUE 3 37 38 1 65 37 59.393365
FALSE TRUE 3 38 39 1 66 38 63.489459
FALSE TRUE 3 39 40 1 67 39 53.249224
FALSE TRUE 3 40 41 1 68 40 64.513483
FALSE TRUE 3 41 42 1 69 41 72.705671
FALSE TRUE 3 42 43 1 70 42 63.489459
FALSE TRUE 3 43 44 1 71 43 48.129106
FALSE TRUE 3 44 45 1 72 44 71.681647
FALSE TRUE 3 45 46 1 73 45 64.513483
FALSE TRUE 3 46 47 1 74 46 66.561530
FALSE TRUE 3 47 48 1 75 47 78.849812
FALSE TRUE 3 48 49 1 76 48 67.585553
FALSE TRUE 3 49 50 1 77 49 88.066024
FALSE TRUE 3 50 51 1 78 50 78.849812
FALSE TRUE 3 51 52 1 79 51 98.306259
FALSE TRUE 3 52 53 1 80 52 95.234189
FALSE TRUE 3 53 54 1 81 53 100.354306
FALSE TRUE 3 54 55 1 82 54 78.849812
FALSE TRUE 3 55 56 1 83 55 92.162118
FALSE TRUE 3 56 57 1 84 56 86.017977
FALSE TRUE 3 57 58 1 85 57 83.969930
FALSE TRUE 3 58 59 1 86 58 76.801765
FALSE TRUE 3 59 60 1 87 59 75.777742
FALSE TRUE 3 60 61 1 88 60 86.017977
FALSE TRUE 3 61 62 1 89 61 63.489459
FALSE TRUE 3 62 63 1 90 62 88.066024
FALSE TRUE 3 63 64 1 91 63 117.762706
FALSE TRUE 3 64 65 1 92 64 118.786730
FALSE TRUE 3 65 66 1 93 65 103.426377
FALSE TRUE 3 66 67 1 94 66 102.402353
FALSE TRUE 3 67 68 1 95 67 101.378330
FALSE TRUE 3 68 69 1 96 68 94.210165
FALSE TRUE 3 69 70 1 97 69 109.570518
FALSE TRUE 3 70 71 1 98 70 116.738683
FALSE TRUE 3 71 72 1 99 71 122.882824
FALSE TRUE 3 72 73 1 100 72 118.786730
FALSE TRUE 3 73 74 1 101 73 121.858801
FALSE TRUE 3 74 75 1 102 74 122.882824
FALSE TRUE 3 75 76 1 103 75 102.402353
FALSE TRUE 3 76 77 1 104 76 97.282236
FALSE TRUE 3 77 78 1 105 77 110.594542
FALSE TRUE 3 78 79 1 106 78 96.258212
FALSE TRUE 3 79 80 1 107 79 101.378330
FALSE TRUE 3 80 81 1 108 80 84.993953
FALSE TRUE 3 81 82 1 109 81 58.369341
FALSE TRUE 3 82 83 1 110 82 72.705671
FALSE TRUE 3 83 84 1 111 83 58.369341
FALSE TRUE 3 84 85 1 112 84 74.753718
FALSE TRUE 3 85 86 1 113 85 39.936918
FALSE TRUE 3 86 87 1 114 86 40.960941
FALSE TRUE 3 87 88 1 115 87 44.033012
FALSE TRUE 3 88 89 1 116 88 37.888871
FALSE TRUE 3 89 90 1 117 89 23.552541
FALSE TRUE 3 90 91 1 118 90 25.600588
FALSE TRUE 3 91 92 1 119 91 23.552541
FALSE TRUE 3 92 93 1 120 92 23.552541
FALSE TRUE 3 93 94 1 121 93 21.504494
FALSE TRUE 3 94 95 1 122 94 15.360353
FALSE TRUE 3 95 0 -1 176 95+ 57.345318
FALSE TRUE 3 0 -1 -1 184 Total 6266.000000

This dataset contains both abridged and complete series, so we will begin with the abridged series.

df_abr <- df %>% 
  filter(abridged == TRUE)
df_abr %>% tab_output()
abridged complete SexID AgeStart AgeEnd AgeSpan AgeSort AgeLabel DataValue
TRUE FALSE 1 0 1 1 1 0 334.59341
TRUE FALSE 1 1 5 4 2 1-4 69.36693
TRUE FALSE 1 0 5 5 3 0-4 403.96033
TRUE FALSE 1 5 10 5 4 5-9 33.66336
TRUE FALSE 1 10 15 5 5 10-14 39.78397
TRUE FALSE 1 15 20 5 6 15-19 77.52774
TRUE FALSE 1 20 25 5 7 20-24 120.37202
TRUE FALSE 1 25 30 5 8 25-29 158.11579
TRUE FALSE 1 30 35 5 9 30-34 192.79925
TRUE FALSE 1 35 40 5 10 35-39 198.91986
TRUE FALSE 1 40 45 5 11 40-44 222.38220
TRUE FALSE 1 45 50 5 12 45-49 277.46770
TRUE FALSE 1 50 55 5 13 50-54 341.73412
TRUE FALSE 1 55 60 5 14 55-59 283.58831
TRUE FALSE 1 60 65 5 15 60-64 266.24658
TRUE FALSE 1 65 70 5 16 65-69 273.38730
TRUE FALSE 1 70 75 5 17 70-74 331.53310
TRUE FALSE 1 75 80 5 18 75-79 231.56312
TRUE FALSE 1 80 85 5 19 80-84 171.37711
TRUE FALSE 1 85 90 5 20 85-89 97.92978
TRUE FALSE 1 90 95 5 21 90-94 55.08550
TRUE FALSE 1 95 0 -1 176 95+ 28.56285
TRUE FALSE 1 0 -1 -1 184 Total 3806.00000
TRUE FALSE 2 0 1 1 1 0 286.38191
TRUE FALSE 2 1 5 4 2 1-4 40.17588
TRUE FALSE 2 0 5 5 3 0-4 326.55779
TRUE FALSE 2 5 10 5 4 5-9 29.87437
TRUE FALSE 2 10 15 5 5 10-14 35.02513
TRUE FALSE 2 15 20 5 6 15-19 30.90452
TRUE FALSE 2 20 25 5 7 20-24 39.14573
TRUE FALSE 2 25 30 5 8 25-29 76.23116
TRUE FALSE 2 30 35 5 9 30-34 70.05025
TRUE FALSE 2 35 40 5 10 35-39 81.38191
TRUE FALSE 2 40 45 5 11 40-44 97.86432
TRUE FALSE 2 45 50 5 12 45-49 87.56281
TRUE FALSE 2 50 55 5 13 50-54 109.19598
TRUE FALSE 2 55 60 5 14 55-59 130.82915
TRUE FALSE 2 60 65 5 15 60-64 208.09045
TRUE FALSE 2 65 70 5 16 65-69 237.96482
TRUE FALSE 2 70 75 5 17 70-74 271.95980
TRUE FALSE 2 75 80 5 18 75-79 277.11055
TRUE FALSE 2 80 85 5 19 80-84 178.21608
TRUE FALSE 2 85 90 5 20 85-89 88.59296
TRUE FALSE 2 90 95 5 21 90-94 54.59799
TRUE FALSE 2 95 0 -1 176 95+ 28.84422
TRUE FALSE 2 0 -1 -1 184 Total 2460.00000
TRUE FALSE 3 0 1 1 1 0 620.55826
TRUE FALSE 3 1 5 4 2 1-4 109.57052
TRUE FALSE 3 0 5 5 3 0-4 730.12878
TRUE FALSE 3 5 10 5 4 5-9 63.48946
TRUE FALSE 3 10 15 5 5 10-14 74.75372
TRUE FALSE 3 15 20 5 6 15-19 108.54649
TRUE FALSE 3 20 25 5 7 20-24 159.74767
TRUE FALSE 3 25 30 5 8 25-29 234.50139
TRUE FALSE 3 30 35 5 9 30-34 263.17405
TRUE FALSE 3 35 40 5 10 35-39 280.58245
TRUE FALSE 3 40 45 5 11 40-44 320.51937
TRUE FALSE 3 45 50 5 12 45-49 365.57640
TRUE FALSE 3 50 55 5 13 50-54 451.59438
TRUE FALSE 3 55 60 5 14 55-59 414.72953
TRUE FALSE 3 60 65 5 15 60-64 474.12290
TRUE FALSE 3 65 70 5 16 65-69 510.98774
TRUE FALSE 3 70 75 5 17 70-74 603.14986
TRUE FALSE 3 75 80 5 18 75-79 507.91567
TRUE FALSE 3 80 85 5 19 80-84 349.19202
TRUE FALSE 3 85 90 5 20 85-89 186.37228
TRUE FALSE 3 90 95 5 21 90-94 109.57052
TRUE FALSE 3 95 0 -1 176 95+ 57.34532
TRUE FALSE 3 0 -1 -1 184 Total 8978.00000

We begin by checking if data for both males and females exists, and whether both series are full 3

    full_m <- dd_series_isfull(data = df_abr[df_abr$SexID == 1,], abridged = TRUE)
    full_f <- dd_series_isfull(data = df_abr[df_abr$SexID == 2,], abridged = TRUE)

Next, check whether all the age labels in the males data are present in the females data.

    ages_same <- all(df_abr$AgeLabel[df_abr$SexID == 1] %in% df_abr$AgeLabel[df_abr$SexID == 2])

If data for both males and females exists and are full, and all the age labels in the males data are present in the females data, re-calculate the values to ensure that Both sexes is the sum of Female and Male data.

    if (all(full_m, full_f, ages_same)) {
    df_abr <- df_abr %>%
      mutate(SexID = paste0("X", SexID)) %>%
      spread(key = SexID, value = DataValue) %>%
      mutate(X3 = X1 + X2) %>% # ensure that both sexes is sum of male and female
      gather(key = "SexID", value = "DataValue", c("X1","X2","X3")) %>%
      mutate(SexID = as.numeric(substr(SexID,2,2)),
             note = NA) %>%
      dplyr::filter(!is.na(DataValue)) %>% # need this bc NAs are introduced if both sexes has more age groups than by sex
      arrange(SexID, AgeSort)
    } else {
      df_abr <- df_abr %>%
        mutate(note = "Inconsistent age groups for males and females; Both sexes not reconciled")
    }
df_abr %>% tab_output()
abridged complete AgeStart AgeEnd AgeSpan AgeSort AgeLabel SexID DataValue note
TRUE FALSE 0 1 1 1 0 1 334.59341 NA
TRUE FALSE 1 5 4 2 1-4 1 69.36693 NA
TRUE FALSE 0 5 5 3 0-4 1 403.96033 NA
TRUE FALSE 5 10 5 4 5-9 1 33.66336 NA
TRUE FALSE 10 15 5 5 10-14 1 39.78397 NA
TRUE FALSE 15 20 5 6 15-19 1 77.52774 NA
TRUE FALSE 20 25 5 7 20-24 1 120.37202 NA
TRUE FALSE 25 30 5 8 25-29 1 158.11579 NA
TRUE FALSE 30 35 5 9 30-34 1 192.79925 NA
TRUE FALSE 35 40 5 10 35-39 1 198.91986 NA
TRUE FALSE 40 45 5 11 40-44 1 222.38220 NA
TRUE FALSE 45 50 5 12 45-49 1 277.46770 NA
TRUE FALSE 50 55 5 13 50-54 1 341.73412 NA
TRUE FALSE 55 60 5 14 55-59 1 283.58831 NA
TRUE FALSE 60 65 5 15 60-64 1 266.24658 NA
TRUE FALSE 65 70 5 16 65-69 1 273.38730 NA
TRUE FALSE 70 75 5 17 70-74 1 331.53310 NA
TRUE FALSE 75 80 5 18 75-79 1 231.56312 NA
TRUE FALSE 80 85 5 19 80-84 1 171.37711 NA
TRUE FALSE 85 90 5 20 85-89 1 97.92978 NA
TRUE FALSE 90 95 5 21 90-94 1 55.08550 NA
TRUE FALSE 95 0 -1 176 95+ 1 28.56285 NA
TRUE FALSE 0 -1 -1 184 Total 1 3806.00000 NA
TRUE FALSE 0 1 1 1 0 2 286.38191 NA
TRUE FALSE 1 5 4 2 1-4 2 40.17588 NA
TRUE FALSE 0 5 5 3 0-4 2 326.55779 NA
TRUE FALSE 5 10 5 4 5-9 2 29.87437 NA
TRUE FALSE 10 15 5 5 10-14 2 35.02513 NA
TRUE FALSE 15 20 5 6 15-19 2 30.90452 NA
TRUE FALSE 20 25 5 7 20-24 2 39.14573 NA
TRUE FALSE 25 30 5 8 25-29 2 76.23116 NA
TRUE FALSE 30 35 5 9 30-34 2 70.05025 NA
TRUE FALSE 35 40 5 10 35-39 2 81.38191 NA
TRUE FALSE 40 45 5 11 40-44 2 97.86432 NA
TRUE FALSE 45 50 5 12 45-49 2 87.56281 NA
TRUE FALSE 50 55 5 13 50-54 2 109.19598 NA
TRUE FALSE 55 60 5 14 55-59 2 130.82915 NA
TRUE FALSE 60 65 5 15 60-64 2 208.09045 NA
TRUE FALSE 65 70 5 16 65-69 2 237.96482 NA
TRUE FALSE 70 75 5 17 70-74 2 271.95980 NA
TRUE FALSE 75 80 5 18 75-79 2 277.11055 NA
TRUE FALSE 80 85 5 19 80-84 2 178.21608 NA
TRUE FALSE 85 90 5 20 85-89 2 88.59296 NA
TRUE FALSE 90 95 5 21 90-94 2 54.59799 NA
TRUE FALSE 95 0 -1 176 95+ 2 28.84422 NA
TRUE FALSE 0 -1 -1 184 Total 2 2460.00000 NA
TRUE FALSE 0 1 1 1 0 3 620.97532 NA
TRUE FALSE 1 5 4 2 1-4 3 109.54281 NA
TRUE FALSE 0 5 5 3 0-4 3 730.51812 NA
TRUE FALSE 5 10 5 4 5-9 3 63.53773 NA
TRUE FALSE 10 15 5 5 10-14 3 74.80910 NA
TRUE FALSE 15 20 5 6 15-19 3 108.43226 NA
TRUE FALSE 20 25 5 7 20-24 3 159.51775 NA
TRUE FALSE 25 30 5 8 25-29 3 234.34694 NA
TRUE FALSE 30 35 5 9 30-34 3 262.84950 NA
TRUE FALSE 35 40 5 10 35-39 3 280.30177 NA
TRUE FALSE 40 45 5 11 40-44 3 320.24652 NA
TRUE FALSE 45 50 5 12 45-49 3 365.03052 NA
TRUE FALSE 50 55 5 13 50-54 3 450.93010 NA
TRUE FALSE 55 60 5 14 55-59 3 414.41746 NA
TRUE FALSE 60 65 5 15 60-64 3 474.33703 NA
TRUE FALSE 65 70 5 16 65-69 3 511.35212 NA
TRUE FALSE 70 75 5 17 70-74 3 603.49290 NA
TRUE FALSE 75 80 5 18 75-79 3 508.67367 NA
TRUE FALSE 80 85 5 19 80-84 3 349.59319 NA
TRUE FALSE 85 90 5 20 85-89 3 186.52274 NA
TRUE FALSE 90 95 5 21 90-94 3 109.68349 NA
TRUE FALSE 95 0 -1 176 95+ 3 57.40707 NA
TRUE FALSE 0 -1 -1 184 Total 3 6266.00000 NA

We now have the correct value for Both sexes total.

df_abr$DataValue[df_abr$abridged == TRUE & df_abr$AgeLabel == "Total" & df_abr$SexID == 3]
#> [1] 6266

The same process is repeated for the complete series

df_cpl <- df %>% 
  filter(complete == TRUE)
df_cpl %>% tab_output()
abridged complete SexID AgeStart AgeEnd AgeSpan AgeSort AgeLabel DataValue
FALSE TRUE 1 0 1 1 1 0 334.593407
FALSE TRUE 1 1 2 1 29 1 19.381935
FALSE TRUE 1 2 3 1 30 2 16.321630
FALSE TRUE 1 3 4 1 31 3 15.301528
FALSE TRUE 1 4 5 1 32 4 18.361833
FALSE TRUE 1 5 6 1 33 5 6.120611
FALSE TRUE 1 6 7 1 34 6 8.160815
FALSE TRUE 1 7 8 1 35 7 5.100509
FALSE TRUE 1 8 9 1 36 8 1.020102
FALSE TRUE 1 9 10 1 37 9 13.261324
FALSE TRUE 1 10 11 1 38 10 7.140713
FALSE TRUE 1 11 12 1 39 11 4.080407
FALSE TRUE 1 12 13 1 40 12 3.060306
FALSE TRUE 1 13 14 1 41 13 12.241222
FALSE TRUE 1 14 15 1 42 14 13.261324
FALSE TRUE 1 15 16 1 43 15 9.180917
FALSE TRUE 1 16 17 1 44 16 15.301528
FALSE TRUE 1 17 18 1 45 17 20.402037
FALSE TRUE 1 18 19 1 46 18 10.201018
FALSE TRUE 1 19 20 1 47 19 22.442241
FALSE TRUE 1 20 21 1 48 20 22.442241
FALSE TRUE 1 21 22 1 49 21 23.462342
FALSE TRUE 1 22 23 1 50 22 18.361833
FALSE TRUE 1 23 24 1 51 23 30.603055
FALSE TRUE 1 24 25 1 52 24 25.502546
FALSE TRUE 1 25 26 1 53 25 26.522648
FALSE TRUE 1 26 27 1 54 26 45.904583
FALSE TRUE 1 27 28 1 55 27 31.623157
FALSE TRUE 1 28 29 1 56 28 29.582954
FALSE TRUE 1 29 30 1 57 29 24.482444
FALSE TRUE 1 30 31 1 58 30 35.703565
FALSE TRUE 1 31 32 1 59 31 31.623157
FALSE TRUE 1 32 33 1 60 32 41.824176
FALSE TRUE 1 33 34 1 61 33 43.864379
FALSE TRUE 1 34 35 1 62 34 39.783972
FALSE TRUE 1 35 36 1 63 35 32.643259
FALSE TRUE 1 36 37 1 64 36 40.804074
FALSE TRUE 1 37 38 1 65 37 42.844278
FALSE TRUE 1 38 39 1 66 38 47.944787
FALSE TRUE 1 39 40 1 67 39 34.683463
FALSE TRUE 1 40 41 1 68 40 45.904583
FALSE TRUE 1 41 42 1 69 41 49.984991
FALSE TRUE 1 42 43 1 70 42 44.884481
FALSE TRUE 1 43 44 1 71 43 29.582954
FALSE TRUE 1 44 45 1 72 44 52.025194
FALSE TRUE 1 45 46 1 73 45 52.025194
FALSE TRUE 1 46 47 1 74 46 54.065398
FALSE TRUE 1 47 48 1 75 47 57.125704
FALSE TRUE 1 48 49 1 76 48 44.884481
FALSE TRUE 1 49 50 1 77 49 69.366926
FALSE TRUE 1 50 51 1 78 50 59.165907
FALSE TRUE 1 51 52 1 79 51 79.567944
FALSE TRUE 1 52 53 1 80 52 70.387028
FALSE TRUE 1 53 54 1 81 53 80.588046
FALSE TRUE 1 54 55 1 82 54 52.025194
FALSE TRUE 1 55 56 1 83 55 65.286518
FALSE TRUE 1 56 57 1 84 56 66.306620
FALSE TRUE 1 57 58 1 85 57 56.105602
FALSE TRUE 1 58 59 1 86 58 51.005093
FALSE TRUE 1 59 60 1 87 59 44.884481
FALSE TRUE 1 60 61 1 88 60 58.145805
FALSE TRUE 1 61 62 1 89 61 37.743768
FALSE TRUE 1 62 63 1 90 62 52.025194
FALSE TRUE 1 63 64 1 91 63 59.165907
FALSE TRUE 1 64 65 1 92 64 59.165907
FALSE TRUE 1 65 66 1 93 65 61.206111
FALSE TRUE 1 66 67 1 94 66 51.005093
FALSE TRUE 1 67 68 1 95 67 55.085500
FALSE TRUE 1 68 69 1 96 68 44.884481
FALSE TRUE 1 69 70 1 97 69 61.206111
FALSE TRUE 1 70 71 1 98 70 70.387028
FALSE TRUE 1 71 72 1 99 71 61.206111
FALSE TRUE 1 72 73 1 100 72 69.366926
FALSE TRUE 1 73 74 1 101 73 68.346824
FALSE TRUE 1 74 75 1 102 74 62.226213
FALSE TRUE 1 75 76 1 103 75 54.065398
FALSE TRUE 1 76 77 1 104 76 51.005093
FALSE TRUE 1 77 78 1 105 77 46.924685
FALSE TRUE 1 78 79 1 106 78 39.783972
FALSE TRUE 1 79 80 1 107 79 39.783972
FALSE TRUE 1 80 81 1 108 80 46.924685
FALSE TRUE 1 81 82 1 109 81 26.522648
FALSE TRUE 1 82 83 1 110 82 39.783972
FALSE TRUE 1 83 84 1 111 83 27.542750
FALSE TRUE 1 84 85 1 112 84 30.603055
FALSE TRUE 1 85 86 1 113 85 20.402037
FALSE TRUE 1 86 87 1 114 86 25.502546
FALSE TRUE 1 87 88 1 115 87 22.442241
FALSE TRUE 1 88 89 1 116 88 19.381935
FALSE TRUE 1 89 90 1 117 89 10.201018
FALSE TRUE 1 90 91 1 118 90 13.261324
FALSE TRUE 1 91 92 1 119 91 10.201018
FALSE TRUE 1 92 93 1 120 92 13.261324
FALSE TRUE 1 93 94 1 121 93 14.281426
FALSE TRUE 1 94 95 1 122 94 4.080407
FALSE TRUE 1 95 0 -1 176 95+ 28.562852
FALSE TRUE 1 0 -1 -1 184 Total 3806.000000
FALSE TRUE 2 0 1 1 1 0 286.381910
FALSE TRUE 2 1 2 1 29 1 14.422111
FALSE TRUE 2 2 3 1 30 2 9.271357
FALSE TRUE 2 3 4 1 31 3 8.241206
FALSE TRUE 2 4 5 1 32 4 8.241206
FALSE TRUE 2 5 6 1 33 5 6.180904
FALSE TRUE 2 6 7 1 34 6 5.150754
FALSE TRUE 2 7 8 1 35 7 6.180904
FALSE TRUE 2 8 9 1 36 8 5.150754
FALSE TRUE 2 9 10 1 37 9 7.211055
FALSE TRUE 2 10 11 1 38 10 3.090452
FALSE TRUE 2 11 12 1 39 11 7.211055
FALSE TRUE 2 12 13 1 40 12 6.180904
FALSE TRUE 2 13 14 1 41 13 5.150754
FALSE TRUE 2 14 15 1 42 14 13.391960
FALSE TRUE 2 15 16 1 43 15 8.241206
FALSE TRUE 2 16 17 1 44 16 4.120603
FALSE TRUE 2 17 18 1 45 17 3.090452
FALSE TRUE 2 18 19 1 46 18 8.241206
FALSE TRUE 2 19 20 1 47 19 7.211055
FALSE TRUE 2 20 21 1 48 20 6.180904
FALSE TRUE 2 21 22 1 49 21 4.120603
FALSE TRUE 2 22 23 1 50 22 7.211055
FALSE TRUE 2 23 24 1 51 23 8.241206
FALSE TRUE 2 24 25 1 52 24 13.391960
FALSE TRUE 2 25 26 1 53 25 17.512563
FALSE TRUE 2 26 27 1 54 26 17.512563
FALSE TRUE 2 27 28 1 55 27 10.301507
FALSE TRUE 2 28 29 1 56 28 10.301507
FALSE TRUE 2 29 30 1 57 29 20.603015
FALSE TRUE 2 30 31 1 58 30 15.452261
FALSE TRUE 2 31 32 1 59 31 7.211055
FALSE TRUE 2 32 33 1 60 32 12.361809
FALSE TRUE 2 33 34 1 61 33 12.361809
FALSE TRUE 2 34 35 1 62 34 22.663317
FALSE TRUE 2 35 36 1 63 35 16.482412
FALSE TRUE 2 36 37 1 64 36 14.422111
FALSE TRUE 2 37 38 1 65 37 16.482412
FALSE TRUE 2 38 39 1 66 38 15.452261
FALSE TRUE 2 39 40 1 67 39 18.542714
FALSE TRUE 2 40 41 1 68 40 18.542714
FALSE TRUE 2 41 42 1 69 41 22.663317
FALSE TRUE 2 42 43 1 70 42 18.542714
FALSE TRUE 2 43 44 1 71 43 18.542714
FALSE TRUE 2 44 45 1 72 44 19.572864
FALSE TRUE 2 45 46 1 73 45 12.361809
FALSE TRUE 2 46 47 1 74 46 12.361809
FALSE TRUE 2 47 48 1 75 47 21.633166
FALSE TRUE 2 48 49 1 76 48 22.663317
FALSE TRUE 2 49 50 1 77 49 18.542714
FALSE TRUE 2 50 51 1 78 50 19.572864
FALSE TRUE 2 51 52 1 79 51 18.542714
FALSE TRUE 2 52 53 1 80 52 24.723618
FALSE TRUE 2 53 54 1 81 53 19.572864
FALSE TRUE 2 54 55 1 82 54 26.783920
FALSE TRUE 2 55 56 1 83 55 26.783920
FALSE TRUE 2 56 57 1 84 56 19.572864
FALSE TRUE 2 57 58 1 85 57 27.814070
FALSE TRUE 2 58 59 1 86 58 25.753769
FALSE TRUE 2 59 60 1 87 59 30.904523
FALSE TRUE 2 60 61 1 88 60 27.814070
FALSE TRUE 2 61 62 1 89 61 25.753769
FALSE TRUE 2 62 63 1 90 62 36.055276
FALSE TRUE 2 63 64 1 91 63 58.718593
FALSE TRUE 2 64 65 1 92 64 59.748744
FALSE TRUE 2 65 66 1 93 65 42.236181
FALSE TRUE 2 66 67 1 94 66 51.507538
FALSE TRUE 2 67 68 1 95 67 46.356784
FALSE TRUE 2 68 69 1 96 68 49.447236
FALSE TRUE 2 69 70 1 97 69 48.417085
FALSE TRUE 2 70 71 1 98 70 46.356784
FALSE TRUE 2 71 72 1 99 71 61.809045
FALSE TRUE 2 72 73 1 100 72 49.447236
FALSE TRUE 2 73 74 1 101 73 53.567839
FALSE TRUE 2 74 75 1 102 74 60.778894
FALSE TRUE 2 75 76 1 103 75 48.417085
FALSE TRUE 2 76 77 1 104 76 46.356784
FALSE TRUE 2 77 78 1 105 77 63.869347
FALSE TRUE 2 78 79 1 106 78 56.658291
FALSE TRUE 2 79 80 1 107 79 61.809045
FALSE TRUE 2 80 81 1 108 80 38.115578
FALSE TRUE 2 81 82 1 109 81 31.934673
FALSE TRUE 2 82 83 1 110 82 32.964824
FALSE TRUE 2 83 84 1 111 83 30.904523
FALSE TRUE 2 84 85 1 112 84 44.296482
FALSE TRUE 2 85 86 1 113 85 19.572864
FALSE TRUE 2 86 87 1 114 86 15.452261
FALSE TRUE 2 87 88 1 115 87 21.633166
FALSE TRUE 2 88 89 1 116 88 18.542714
FALSE TRUE 2 89 90 1 117 89 13.391960
FALSE TRUE 2 90 91 1 118 90 12.361809
FALSE TRUE 2 91 92 1 119 91 13.391960
FALSE TRUE 2 92 93 1 120 92 10.301507
FALSE TRUE 2 93 94 1 121 93 7.211055
FALSE TRUE 2 94 95 1 122 94 11.331658
FALSE TRUE 2 95 0 -1 176 95+ 28.844221
FALSE TRUE 2 0 -1 -1 184 Total 3810.000000
FALSE TRUE 3 0 1 1 1 0 620.558261
FALSE TRUE 3 1 2 1 29 1 33.792777
FALSE TRUE 3 2 3 1 30 2 25.600588
FALSE TRUE 3 3 4 1 31 3 23.552541
FALSE TRUE 3 4 5 1 32 4 26.624612
FALSE TRUE 3 5 6 1 33 5 12.288282
FALSE TRUE 3 6 7 1 34 6 13.312306
FALSE TRUE 3 7 8 1 35 7 11.264259
FALSE TRUE 3 8 9 1 36 8 6.144141
FALSE TRUE 3 9 10 1 37 9 20.480471
FALSE TRUE 3 10 11 1 38 10 10.240235
FALSE TRUE 3 11 12 1 39 11 11.264259
FALSE TRUE 3 12 13 1 40 12 9.216212
FALSE TRUE 3 13 14 1 41 13 17.408400
FALSE TRUE 3 14 15 1 42 14 26.624612
FALSE TRUE 3 15 16 1 43 15 17.408400
FALSE TRUE 3 16 17 1 44 16 19.456447
FALSE TRUE 3 17 18 1 45 17 23.552541
FALSE TRUE 3 18 19 1 46 18 18.432424
FALSE TRUE 3 19 20 1 47 19 29.696683
FALSE TRUE 3 20 21 1 48 20 28.672659
FALSE TRUE 3 21 22 1 49 21 27.648635
FALSE TRUE 3 22 23 1 50 22 25.600588
FALSE TRUE 3 23 24 1 51 23 38.912894
FALSE TRUE 3 24 25 1 52 24 38.912894
FALSE TRUE 3 25 26 1 53 25 44.033012
FALSE TRUE 3 26 27 1 54 26 63.489459
FALSE TRUE 3 27 28 1 55 27 41.984965
FALSE TRUE 3 28 29 1 56 28 39.936918
FALSE TRUE 3 29 30 1 57 29 45.057035
FALSE TRUE 3 30 31 1 58 30 51.201177
FALSE TRUE 3 31 32 1 59 31 38.912894
FALSE TRUE 3 32 33 1 60 32 54.273247
FALSE TRUE 3 33 34 1 61 33 56.321294
FALSE TRUE 3 34 35 1 62 34 62.465435
FALSE TRUE 3 35 36 1 63 35 49.153130
FALSE TRUE 3 36 37 1 64 36 55.297271
FALSE TRUE 3 37 38 1 65 37 59.393365
FALSE TRUE 3 38 39 1 66 38 63.489459
FALSE TRUE 3 39 40 1 67 39 53.249224
FALSE TRUE 3 40 41 1 68 40 64.513483
FALSE TRUE 3 41 42 1 69 41 72.705671
FALSE TRUE 3 42 43 1 70 42 63.489459
FALSE TRUE 3 43 44 1 71 43 48.129106
FALSE TRUE 3 44 45 1 72 44 71.681647
FALSE TRUE 3 45 46 1 73 45 64.513483
FALSE TRUE 3 46 47 1 74 46 66.561530
FALSE TRUE 3 47 48 1 75 47 78.849812
FALSE TRUE 3 48 49 1 76 48 67.585553
FALSE TRUE 3 49 50 1 77 49 88.066024
FALSE TRUE 3 50 51 1 78 50 78.849812
FALSE TRUE 3 51 52 1 79 51 98.306259
FALSE TRUE 3 52 53 1 80 52 95.234189
FALSE TRUE 3 53 54 1 81 53 100.354306
FALSE TRUE 3 54 55 1 82 54 78.849812
FALSE TRUE 3 55 56 1 83 55 92.162118
FALSE TRUE 3 56 57 1 84 56 86.017977
FALSE TRUE 3 57 58 1 85 57 83.969930
FALSE TRUE 3 58 59 1 86 58 76.801765
FALSE TRUE 3 59 60 1 87 59 75.777742
FALSE TRUE 3 60 61 1 88 60 86.017977
FALSE TRUE 3 61 62 1 89 61 63.489459
FALSE TRUE 3 62 63 1 90 62 88.066024
FALSE TRUE 3 63 64 1 91 63 117.762706
FALSE TRUE 3 64 65 1 92 64 118.786730
FALSE TRUE 3 65 66 1 93 65 103.426377
FALSE TRUE 3 66 67 1 94 66 102.402353
FALSE TRUE 3 67 68 1 95 67 101.378330
FALSE TRUE 3 68 69 1 96 68 94.210165
FALSE TRUE 3 69 70 1 97 69 109.570518
FALSE TRUE 3 70 71 1 98 70 116.738683
FALSE TRUE 3 71 72 1 99 71 122.882824
FALSE TRUE 3 72 73 1 100 72 118.786730
FALSE TRUE 3 73 74 1 101 73 121.858801
FALSE TRUE 3 74 75 1 102 74 122.882824
FALSE TRUE 3 75 76 1 103 75 102.402353
FALSE TRUE 3 76 77 1 104 76 97.282236
FALSE TRUE 3 77 78 1 105 77 110.594542
FALSE TRUE 3 78 79 1 106 78 96.258212
FALSE TRUE 3 79 80 1 107 79 101.378330
FALSE TRUE 3 80 81 1 108 80 84.993953
FALSE TRUE 3 81 82 1 109 81 58.369341
FALSE TRUE 3 82 83 1 110 82 72.705671
FALSE TRUE 3 83 84 1 111 83 58.369341
FALSE TRUE 3 84 85 1 112 84 74.753718
FALSE TRUE 3 85 86 1 113 85 39.936918
FALSE TRUE 3 86 87 1 114 86 40.960941
FALSE TRUE 3 87 88 1 115 87 44.033012
FALSE TRUE 3 88 89 1 116 88 37.888871
FALSE TRUE 3 89 90 1 117 89 23.552541
FALSE TRUE 3 90 91 1 118 90 25.600588
FALSE TRUE 3 91 92 1 119 91 23.552541
FALSE TRUE 3 92 93 1 120 92 23.552541
FALSE TRUE 3 93 94 1 121 93 21.504494
FALSE TRUE 3 94 95 1 122 94 15.360353
FALSE TRUE 3 95 0 -1 176 95+ 57.345318
FALSE TRUE 3 0 -1 -1 184 Total 6266.000000

Check that data for both males and females exists, and that both series are full

    full_m <- dd_series_isfull(data = df_cpl[df_cpl$SexID == 1,], abridged = TRUE)
    full_f <- dd_series_isfull(data = df_cpl[df_cpl$SexID == 2,], abridged = TRUE)

Check that all the age labels in the males data are present in the females data.

    ages_same <- all(df_cpl$AgeLabel[df_cpl$SexID == 1] %in% df_cpl$AgeLabel[df_cpl$SexID == 2])

And if the above condition are met, re-calculate the values to ensure that that they add up.

    if (all(full_m, full_f, ages_same)) {
    df_cpl <- df_cpl %>%
      mutate(SexID = paste0("X", SexID)) %>%
      spread(key = SexID, value = DataValue) %>%
      mutate(X3 = X1 + X2) %>% # ensure that both sexes is sum of male and female
      gather(key = "SexID", value = "DataValue", c("X1","X2","X3")) %>%
      mutate(SexID = as.numeric(substr(SexID,2,2)),
             note = NA) %>%
      dplyr::filter(!is.na(DataValue)) %>% # need this bc NAs are introduced if both sexes has more age groups than by sex
      arrange(SexID, AgeSort)
    }
df_cpl %>% tab_output()
abridged complete SexID AgeStart AgeEnd AgeSpan AgeSort AgeLabel DataValue
FALSE TRUE 1 0 1 1 1 0 334.593407
FALSE TRUE 1 1 2 1 29 1 19.381935
FALSE TRUE 1 2 3 1 30 2 16.321630
FALSE TRUE 1 3 4 1 31 3 15.301528
FALSE TRUE 1 4 5 1 32 4 18.361833
FALSE TRUE 1 5 6 1 33 5 6.120611
FALSE TRUE 1 6 7 1 34 6 8.160815
FALSE TRUE 1 7 8 1 35 7 5.100509
FALSE TRUE 1 8 9 1 36 8 1.020102
FALSE TRUE 1 9 10 1 37 9 13.261324
FALSE TRUE 1 10 11 1 38 10 7.140713
FALSE TRUE 1 11 12 1 39 11 4.080407
FALSE TRUE 1 12 13 1 40 12 3.060306
FALSE TRUE 1 13 14 1 41 13 12.241222
FALSE TRUE 1 14 15 1 42 14 13.261324
FALSE TRUE 1 15 16 1 43 15 9.180917
FALSE TRUE 1 16 17 1 44 16 15.301528
FALSE TRUE 1 17 18 1 45 17 20.402037
FALSE TRUE 1 18 19 1 46 18 10.201018
FALSE TRUE 1 19 20 1 47 19 22.442241
FALSE TRUE 1 20 21 1 48 20 22.442241
FALSE TRUE 1 21 22 1 49 21 23.462342
FALSE TRUE 1 22 23 1 50 22 18.361833
FALSE TRUE 1 23 24 1 51 23 30.603055
FALSE TRUE 1 24 25 1 52 24 25.502546
FALSE TRUE 1 25 26 1 53 25 26.522648
FALSE TRUE 1 26 27 1 54 26 45.904583
FALSE TRUE 1 27 28 1 55 27 31.623157
FALSE TRUE 1 28 29 1 56 28 29.582954
FALSE TRUE 1 29 30 1 57 29 24.482444
FALSE TRUE 1 30 31 1 58 30 35.703565
FALSE TRUE 1 31 32 1 59 31 31.623157
FALSE TRUE 1 32 33 1 60 32 41.824176
FALSE TRUE 1 33 34 1 61 33 43.864379
FALSE TRUE 1 34 35 1 62 34 39.783972
FALSE TRUE 1 35 36 1 63 35 32.643259
FALSE TRUE 1 36 37 1 64 36 40.804074
FALSE TRUE 1 37 38 1 65 37 42.844278
FALSE TRUE 1 38 39 1 66 38 47.944787
FALSE TRUE 1 39 40 1 67 39 34.683463
FALSE TRUE 1 40 41 1 68 40 45.904583
FALSE TRUE 1 41 42 1 69 41 49.984991
FALSE TRUE 1 42 43 1 70 42 44.884481
FALSE TRUE 1 43 44 1 71 43 29.582954
FALSE TRUE 1 44 45 1 72 44 52.025194
FALSE TRUE 1 45 46 1 73 45 52.025194
FALSE TRUE 1 46 47 1 74 46 54.065398
FALSE TRUE 1 47 48 1 75 47 57.125704
FALSE TRUE 1 48 49 1 76 48 44.884481
FALSE TRUE 1 49 50 1 77 49 69.366926
FALSE TRUE 1 50 51 1 78 50 59.165907
FALSE TRUE 1 51 52 1 79 51 79.567944
FALSE TRUE 1 52 53 1 80 52 70.387028
FALSE TRUE 1 53 54 1 81 53 80.588046
FALSE TRUE 1 54 55 1 82 54 52.025194
FALSE TRUE 1 55 56 1 83 55 65.286518
FALSE TRUE 1 56 57 1 84 56 66.306620
FALSE TRUE 1 57 58 1 85 57 56.105602
FALSE TRUE 1 58 59 1 86 58 51.005093
FALSE TRUE 1 59 60 1 87 59 44.884481
FALSE TRUE 1 60 61 1 88 60 58.145805
FALSE TRUE 1 61 62 1 89 61 37.743768
FALSE TRUE 1 62 63 1 90 62 52.025194
FALSE TRUE 1 63 64 1 91 63 59.165907
FALSE TRUE 1 64 65 1 92 64 59.165907
FALSE TRUE 1 65 66 1 93 65 61.206111
FALSE TRUE 1 66 67 1 94 66 51.005093
FALSE TRUE 1 67 68 1 95 67 55.085500
FALSE TRUE 1 68 69 1 96 68 44.884481
FALSE TRUE 1 69 70 1 97 69 61.206111
FALSE TRUE 1 70 71 1 98 70 70.387028
FALSE TRUE 1 71 72 1 99 71 61.206111
FALSE TRUE 1 72 73 1 100 72 69.366926
FALSE TRUE 1 73 74 1 101 73 68.346824
FALSE TRUE 1 74 75 1 102 74 62.226213
FALSE TRUE 1 75 76 1 103 75 54.065398
FALSE TRUE 1 76 77 1 104 76 51.005093
FALSE TRUE 1 77 78 1 105 77 46.924685
FALSE TRUE 1 78 79 1 106 78 39.783972
FALSE TRUE 1 79 80 1 107 79 39.783972
FALSE TRUE 1 80 81 1 108 80 46.924685
FALSE TRUE 1 81 82 1 109 81 26.522648
FALSE TRUE 1 82 83 1 110 82 39.783972
FALSE TRUE 1 83 84 1 111 83 27.542750
FALSE TRUE 1 84 85 1 112 84 30.603055
FALSE TRUE 1 85 86 1 113 85 20.402037
FALSE TRUE 1 86 87 1 114 86 25.502546
FALSE TRUE 1 87 88 1 115 87 22.442241
FALSE TRUE 1 88 89 1 116 88 19.381935
FALSE TRUE 1 89 90 1 117 89 10.201018
FALSE TRUE 1 90 91 1 118 90 13.261324
FALSE TRUE 1 91 92 1 119 91 10.201018
FALSE TRUE 1 92 93 1 120 92 13.261324
FALSE TRUE 1 93 94 1 121 93 14.281426
FALSE TRUE 1 94 95 1 122 94 4.080407
FALSE TRUE 1 95 0 -1 176 95+ 28.562852
FALSE TRUE 1 0 -1 -1 184 Total 3806.000000
FALSE TRUE 2 0 1 1 1 0 286.381910
FALSE TRUE 2 1 2 1 29 1 14.422111
FALSE TRUE 2 2 3 1 30 2 9.271357
FALSE TRUE 2 3 4 1 31 3 8.241206
FALSE TRUE 2 4 5 1 32 4 8.241206
FALSE TRUE 2 5 6 1 33 5 6.180904
FALSE TRUE 2 6 7 1 34 6 5.150754
FALSE TRUE 2 7 8 1 35 7 6.180904
FALSE TRUE 2 8 9 1 36 8 5.150754
FALSE TRUE 2 9 10 1 37 9 7.211055
FALSE TRUE 2 10 11 1 38 10 3.090452
FALSE TRUE 2 11 12 1 39 11 7.211055
FALSE TRUE 2 12 13 1 40 12 6.180904
FALSE TRUE 2 13 14 1 41 13 5.150754
FALSE TRUE 2 14 15 1 42 14 13.391960
FALSE TRUE 2 15 16 1 43 15 8.241206
FALSE TRUE 2 16 17 1 44 16 4.120603
FALSE TRUE 2 17 18 1 45 17 3.090452
FALSE TRUE 2 18 19 1 46 18 8.241206
FALSE TRUE 2 19 20 1 47 19 7.211055
FALSE TRUE 2 20 21 1 48 20 6.180904
FALSE TRUE 2 21 22 1 49 21 4.120603
FALSE TRUE 2 22 23 1 50 22 7.211055
FALSE TRUE 2 23 24 1 51 23 8.241206
FALSE TRUE 2 24 25 1 52 24 13.391960
FALSE TRUE 2 25 26 1 53 25 17.512563
FALSE TRUE 2 26 27 1 54 26 17.512563
FALSE TRUE 2 27 28 1 55 27 10.301507
FALSE TRUE 2 28 29 1 56 28 10.301507
FALSE TRUE 2 29 30 1 57 29 20.603015
FALSE TRUE 2 30 31 1 58 30 15.452261
FALSE TRUE 2 31 32 1 59 31 7.211055
FALSE TRUE 2 32 33 1 60 32 12.361809
FALSE TRUE 2 33 34 1 61 33 12.361809
FALSE TRUE 2 34 35 1 62 34 22.663317
FALSE TRUE 2 35 36 1 63 35 16.482412
FALSE TRUE 2 36 37 1 64 36 14.422111
FALSE TRUE 2 37 38 1 65 37 16.482412
FALSE TRUE 2 38 39 1 66 38 15.452261
FALSE TRUE 2 39 40 1 67 39 18.542714
FALSE TRUE 2 40 41 1 68 40 18.542714
FALSE TRUE 2 41 42 1 69 41 22.663317
FALSE TRUE 2 42 43 1 70 42 18.542714
FALSE TRUE 2 43 44 1 71 43 18.542714
FALSE TRUE 2 44 45 1 72 44 19.572864
FALSE TRUE 2 45 46 1 73 45 12.361809
FALSE TRUE 2 46 47 1 74 46 12.361809
FALSE TRUE 2 47 48 1 75 47 21.633166
FALSE TRUE 2 48 49 1 76 48 22.663317
FALSE TRUE 2 49 50 1 77 49 18.542714
FALSE TRUE 2 50 51 1 78 50 19.572864
FALSE TRUE 2 51 52 1 79 51 18.542714
FALSE TRUE 2 52 53 1 80 52 24.723618
FALSE TRUE 2 53 54 1 81 53 19.572864
FALSE TRUE 2 54 55 1 82 54 26.783920
FALSE TRUE 2 55 56 1 83 55 26.783920
FALSE TRUE 2 56 57 1 84 56 19.572864
FALSE TRUE 2 57 58 1 85 57 27.814070
FALSE TRUE 2 58 59 1 86 58 25.753769
FALSE TRUE 2 59 60 1 87 59 30.904523
FALSE TRUE 2 60 61 1 88 60 27.814070
FALSE TRUE 2 61 62 1 89 61 25.753769
FALSE TRUE 2 62 63 1 90 62 36.055276
FALSE TRUE 2 63 64 1 91 63 58.718593
FALSE TRUE 2 64 65 1 92 64 59.748744
FALSE TRUE 2 65 66 1 93 65 42.236181
FALSE TRUE 2 66 67 1 94 66 51.507538
FALSE TRUE 2 67 68 1 95 67 46.356784
FALSE TRUE 2 68 69 1 96 68 49.447236
FALSE TRUE 2 69 70 1 97 69 48.417085
FALSE TRUE 2 70 71 1 98 70 46.356784
FALSE TRUE 2 71 72 1 99 71 61.809045
FALSE TRUE 2 72 73 1 100 72 49.447236
FALSE TRUE 2 73 74 1 101 73 53.567839
FALSE TRUE 2 74 75 1 102 74 60.778894
FALSE TRUE 2 75 76 1 103 75 48.417085
FALSE TRUE 2 76 77 1 104 76 46.356784
FALSE TRUE 2 77 78 1 105 77 63.869347
FALSE TRUE 2 78 79 1 106 78 56.658291
FALSE TRUE 2 79 80 1 107 79 61.809045
FALSE TRUE 2 80 81 1 108 80 38.115578
FALSE TRUE 2 81 82 1 109 81 31.934673
FALSE TRUE 2 82 83 1 110 82 32.964824
FALSE TRUE 2 83 84 1 111 83 30.904523
FALSE TRUE 2 84 85 1 112 84 44.296482
FALSE TRUE 2 85 86 1 113 85 19.572864
FALSE TRUE 2 86 87 1 114 86 15.452261
FALSE TRUE 2 87 88 1 115 87 21.633166
FALSE TRUE 2 88 89 1 116 88 18.542714
FALSE TRUE 2 89 90 1 117 89 13.391960
FALSE TRUE 2 90 91 1 118 90 12.361809
FALSE TRUE 2 91 92 1 119 91 13.391960
FALSE TRUE 2 92 93 1 120 92 10.301507
FALSE TRUE 2 93 94 1 121 93 7.211055
FALSE TRUE 2 94 95 1 122 94 11.331658
FALSE TRUE 2 95 0 -1 176 95+ 28.844221
FALSE TRUE 2 0 -1 -1 184 Total 3810.000000
FALSE TRUE 3 0 1 1 1 0 620.558261
FALSE TRUE 3 1 2 1 29 1 33.792777
FALSE TRUE 3 2 3 1 30 2 25.600588
FALSE TRUE 3 3 4 1 31 3 23.552541
FALSE TRUE 3 4 5 1 32 4 26.624612
FALSE TRUE 3 5 6 1 33 5 12.288282
FALSE TRUE 3 6 7 1 34 6 13.312306
FALSE TRUE 3 7 8 1 35 7 11.264259
FALSE TRUE 3 8 9 1 36 8 6.144141
FALSE TRUE 3 9 10 1 37 9 20.480471
FALSE TRUE 3 10 11 1 38 10 10.240235
FALSE TRUE 3 11 12 1 39 11 11.264259
FALSE TRUE 3 12 13 1 40 12 9.216212
FALSE TRUE 3 13 14 1 41 13 17.408400
FALSE TRUE 3 14 15 1 42 14 26.624612
FALSE TRUE 3 15 16 1 43 15 17.408400
FALSE TRUE 3 16 17 1 44 16 19.456447
FALSE TRUE 3 17 18 1 45 17 23.552541
FALSE TRUE 3 18 19 1 46 18 18.432424
FALSE TRUE 3 19 20 1 47 19 29.696683
FALSE TRUE 3 20 21 1 48 20 28.672659
FALSE TRUE 3 21 22 1 49 21 27.648635
FALSE TRUE 3 22 23 1 50 22 25.600588
FALSE TRUE 3 23 24 1 51 23 38.912894
FALSE TRUE 3 24 25 1 52 24 38.912894
FALSE TRUE 3 25 26 1 53 25 44.033012
FALSE TRUE 3 26 27 1 54 26 63.489459
FALSE TRUE 3 27 28 1 55 27 41.984965
FALSE TRUE 3 28 29 1 56 28 39.936918
FALSE TRUE 3 29 30 1 57 29 45.057035
FALSE TRUE 3 30 31 1 58 30 51.201177
FALSE TRUE 3 31 32 1 59 31 38.912894
FALSE TRUE 3 32 33 1 60 32 54.273247
FALSE TRUE 3 33 34 1 61 33 56.321294
FALSE TRUE 3 34 35 1 62 34 62.465435
FALSE TRUE 3 35 36 1 63 35 49.153130
FALSE TRUE 3 36 37 1 64 36 55.297271
FALSE TRUE 3 37 38 1 65 37 59.393365
FALSE TRUE 3 38 39 1 66 38 63.489459
FALSE TRUE 3 39 40 1 67 39 53.249224
FALSE TRUE 3 40 41 1 68 40 64.513483
FALSE TRUE 3 41 42 1 69 41 72.705671
FALSE TRUE 3 42 43 1 70 42 63.489459
FALSE TRUE 3 43 44 1 71 43 48.129106
FALSE TRUE 3 44 45 1 72 44 71.681647
FALSE TRUE 3 45 46 1 73 45 64.513483
FALSE TRUE 3 46 47 1 74 46 66.561530
FALSE TRUE 3 47 48 1 75 47 78.849812
FALSE TRUE 3 48 49 1 76 48 67.585553
FALSE TRUE 3 49 50 1 77 49 88.066024
FALSE TRUE 3 50 51 1 78 50 78.849812
FALSE TRUE 3 51 52 1 79 51 98.306259
FALSE TRUE 3 52 53 1 80 52 95.234189
FALSE TRUE 3 53 54 1 81 53 100.354306
FALSE TRUE 3 54 55 1 82 54 78.849812
FALSE TRUE 3 55 56 1 83 55 92.162118
FALSE TRUE 3 56 57 1 84 56 86.017977
FALSE TRUE 3 57 58 1 85 57 83.969930
FALSE TRUE 3 58 59 1 86 58 76.801765
FALSE TRUE 3 59 60 1 87 59 75.777742
FALSE TRUE 3 60 61 1 88 60 86.017977
FALSE TRUE 3 61 62 1 89 61 63.489459
FALSE TRUE 3 62 63 1 90 62 88.066024
FALSE TRUE 3 63 64 1 91 63 117.762706
FALSE TRUE 3 64 65 1 92 64 118.786730
FALSE TRUE 3 65 66 1 93 65 103.426377
FALSE TRUE 3 66 67 1 94 66 102.402353
FALSE TRUE 3 67 68 1 95 67 101.378330
FALSE TRUE 3 68 69 1 96 68 94.210165
FALSE TRUE 3 69 70 1 97 69 109.570518
FALSE TRUE 3 70 71 1 98 70 116.738683
FALSE TRUE 3 71 72 1 99 71 122.882824
FALSE TRUE 3 72 73 1 100 72 118.786730
FALSE TRUE 3 73 74 1 101 73 121.858801
FALSE TRUE 3 74 75 1 102 74 122.882824
FALSE TRUE 3 75 76 1 103 75 102.402353
FALSE TRUE 3 76 77 1 104 76 97.282236
FALSE TRUE 3 77 78 1 105 77 110.594542
FALSE TRUE 3 78 79 1 106 78 96.258212
FALSE TRUE 3 79 80 1 107 79 101.378330
FALSE TRUE 3 80 81 1 108 80 84.993953
FALSE TRUE 3 81 82 1 109 81 58.369341
FALSE TRUE 3 82 83 1 110 82 72.705671
FALSE TRUE 3 83 84 1 111 83 58.369341
FALSE TRUE 3 84 85 1 112 84 74.753718
FALSE TRUE 3 85 86 1 113 85 39.936918
FALSE TRUE 3 86 87 1 114 86 40.960941
FALSE TRUE 3 87 88 1 115 87 44.033012
FALSE TRUE 3 88 89 1 116 88 37.888871
FALSE TRUE 3 89 90 1 117 89 23.552541
FALSE TRUE 3 90 91 1 118 90 25.600588
FALSE TRUE 3 91 92 1 119 91 23.552541
FALSE TRUE 3 92 93 1 120 92 23.552541
FALSE TRUE 3 93 94 1 121 93 21.504494
FALSE TRUE 3 94 95 1 122 94 15.360353
FALSE TRUE 3 95 0 -1 176 95+ 57.345318
FALSE TRUE 3 0 -1 -1 184 Total 6266.000000

In cases where all age labels in female data are not present in male data or both series are not full, a note is generated alerting the user of the same issue.

# df_cpl <- df_cpl %>%
#         mutate(note = "Inconsistent age groups for males and females; Both sexes not reconciled")

Part 2: Validating totals over sex: population counts

To demonstrate this process for population counts, we will make use of the validate_totals_over_sex_pop dataset. This data represents Myanmar population counts for the year 2014 extracted from census records.

df <- validate_totals_over_sex_pop %>% 
  select(abridged, complete, SexID, AgeStart, AgeEnd, AgeSpan,AgeSort, AgeLabel,DataValue)

df %>% tab_output()
abridged complete SexID AgeStart AgeEnd AgeSpan AgeSort AgeLabel DataValue
TRUE FALSE 1 0 5 5 3 0-4 2262783
TRUE FALSE 1 5 10 5 4 5-9 2438372
TRUE FALSE 1 10 15 5 5 10-14 2595749
TRUE FALSE 1 15 20 5 6 15-19 2290998
TRUE FALSE 1 20 25 5 7 20-24 2091525
TRUE FALSE 1 25 30 5 8 25-29 1995465
TRUE FALSE 1 30 35 5 9 30-34 1884549
TRUE FALSE 1 35 40 5 10 35-39 1705630
TRUE FALSE 1 40 45 5 11 40-44 1548942
TRUE FALSE 1 45 50 5 12 45-49 1375041
TRUE FALSE 1 50 55 5 13 50-54 1182341
TRUE FALSE 1 55 60 5 14 55-59 935979
TRUE FALSE 1 60 65 5 15 60-64 712040
TRUE FALSE 1 65 70 5 16 65-69 466618
TRUE FALSE 1 70 75 5 17 70-74 301679
TRUE FALSE 1 75 80 5 18 75-79 228315
TRUE FALSE 1 80 85 5 19 80-84 130875
TRUE FALSE 1 85 90 5 20 85-89 56979
TRUE FALSE 1 90 0 -1 175 90+ 24834
TRUE FALSE 1 0 -1 -1 184 Total 24228714
TRUE FALSE 1 -2 -2 -2 185 Unknown 0
TRUE FALSE 2 0 5 5 3 0-4 2209347
TRUE FALSE 2 5 10 5 4 5-9 2380705
TRUE FALSE 2 10 15 5 5 10-14 2512613
TRUE FALSE 2 15 20 5 6 15-19 2334991
TRUE FALSE 2 20 25 5 7 20-24 2239544
TRUE FALSE 2 25 30 5 8 25-29 2150669
TRUE FALSE 2 30 35 5 9 30-34 2014312
TRUE FALSE 2 35 40 5 10 35-39 1857850
TRUE FALSE 2 40 45 5 11 40-44 1734131
TRUE FALSE 2 45 50 5 12 45-49 1571107
TRUE FALSE 2 50 55 5 13 50-54 1376891
TRUE FALSE 2 55 60 5 14 55-59 1115958
TRUE FALSE 2 60 65 5 15 60-64 864805
TRUE FALSE 2 65 70 5 16 65-69 597875
TRUE FALSE 2 70 75 5 17 70-74 411491
TRUE FALSE 2 75 80 5 18 75-79 324983
TRUE FALSE 2 80 85 5 19 80-84 204701
TRUE FALSE 2 85 90 5 20 85-89 101090
TRUE FALSE 2 90 0 -1 175 90+ 48123
TRUE FALSE 2 0 -1 -1 184 Total 26051186
TRUE FALSE 2 -2 -2 -2 185 Unknown 0
TRUE FALSE 3 0 5 5 3 0-4 4472130
TRUE FALSE 3 5 10 5 4 5-9 4819077
TRUE FALSE 3 10 15 5 5 10-14 5108362
TRUE FALSE 3 15 20 5 6 15-19 4625989
TRUE FALSE 3 20 25 5 7 20-24 4331069
TRUE FALSE 3 25 30 5 8 25-29 4146134
TRUE FALSE 3 30 35 5 9 30-34 3898861
TRUE FALSE 3 35 40 5 10 35-39 3563480
TRUE FALSE 3 40 45 5 11 40-44 3283073
TRUE FALSE 3 45 50 5 12 45-49 2946148
TRUE FALSE 3 50 55 5 13 50-54 2559232
TRUE FALSE 3 55 60 5 14 55-59 2051937
TRUE FALSE 3 60 65 5 15 60-64 1576845
TRUE FALSE 3 65 70 5 16 65-69 1064493
TRUE FALSE 3 70 75 5 17 70-74 713170
TRUE FALSE 3 75 80 5 18 75-79 553298
TRUE FALSE 3 80 85 5 19 80-84 335576
TRUE FALSE 3 85 90 5 20 85-89 158069
TRUE FALSE 3 90 0 -1 175 90+ 72957
TRUE FALSE 3 0 -1 -1 184 Total 50279900
TRUE FALSE 3 -2 -2 -2 185 Unknown 0

The process here is a bit different in that we first harmonize open age groups by truncating to minimum open age group of males and females and later validate the totals by sex.

We begin by subsetting the data to only have abridged records.

  abr <- validate_totals_over_sex_pop %>%
    dplyr::filter(abridged == TRUE)

We then determine the minimum open age group in the males and females data and extract the record containing this minimum age.

  oa_min <- suppressWarnings(min(abr$AgeStart[abr$AgeSpan == -1 & abr$AgeLabel != "Total" & abr$SexID %in% c(1,2)]))
  oa_record <- abr %>%
    dplyr::filter(AgeStart == oa_min & AgeSpan == -1) %>%
    select(-SexID, -DataValue) %>%
    distinct()

We go ahead and calculate the summation of data values of age labels that exceed this minimum open age group for each of the sex ids and store the result in different datasets.

if (1 %in% abr$SexID) {
    oa_record_m <- oa_record %>%
      mutate(SexID =1,
             DataValue = sum(abr$DataValue[abr$AgeStart >= oa_min & abr$SexID ==1]))
  } else {
    oa_record_m <- NULL
  }
  if (2 %in% abr$SexID) {
    oa_record_f <- oa_record %>%
      mutate(SexID =2,
             DataValue = sum(abr$DataValue[abr$AgeStart >= oa_min & abr$SexID ==2]))
  } else {
    oa_record_f <- NULL
  }
  if (3 %in% abr$SexID) {
    oa_record_b <- oa_record %>%
      mutate(SexID =3,
             DataValue = sum(abr$DataValue[abr$AgeStart >= oa_min & abr$SexID ==3]))
  } else {
    oa_record_b <- NULL
  }

  if (0 %in% abr$SexID) {
    oa_record_o <- oa_record %>%
      mutate(SexID =0,
             DataValue = sum(abr$DataValue[abr$AgeStart >= oa_min & abr$SexID ==0]))
  } else {
    oa_record_o <- NULL
  }

Later, we drop all the age labels that exceed this minimum open age group, and append the resulting data with the datasets generated above.

  abr <- abr %>%
    dplyr::filter(AgeStart < oa_min) %>%
    dplyr::filter(!(AgeSpan == -1 & AgeLabel != "Total" & AgeStart < oa_min)) %>% # remove any oa that is below oa_min 
    bind_rows(oa_record_m, oa_record_f, oa_record_b, oa_record_o) %>%
    arrange(SexID, AgeStart)

This process is repeated for the complete series …

cpl <- validate_totals_over_sex_pop %>%
    dplyr::filter(complete == TRUE)

  if (nrow(cpl) > 50){ # only bother with this if complete series is usable

      oa_min <- suppressWarnings(min(cpl$AgeStart[cpl$AgeSpan == -1 & cpl$AgeLabel != "Total" & cpl$SexID %in% c(1,2)]))
      
      oa_record <- cpl %>%
        dplyr::filter(AgeStart == oa_min & AgeSpan == -1) %>%
        select(-SexID, -DataValue) %>%
        distinct()
      
      if (1 %in% cpl$SexID) {
      oa_record_m <- oa_record %>%
        mutate(SexID =1,
               DataValue = sum(cpl$DataValue[cpl$AgeStart >= oa_min & cpl$SexID ==1]))
      } else {
        oa_record_m <- NULL
      }
      if (2 %in% cpl$SexID) {
      oa_record_f <- oa_record %>%
        mutate(SexID =2,
               DataValue = sum(cpl$DataValue[cpl$AgeStart >= oa_min & cpl$SexID ==2]))
      } else {
        oa_record_f <- NULL
      }
      if (3 %in% cpl$SexID) {
      oa_record_b <- oa_record %>%
        mutate(SexID =3,
               DataValue = sum(cpl$DataValue[cpl$AgeStart >= oa_min & cpl$SexID ==3]))
      } else {
        oa_record_b <- NULL
      }
      if (0 %in% cpl$SexID) {
      oa_record_o <- oa_record %>%
        mutate(SexID =0,
               DataValue = sum(cpl$DataValue[cpl$AgeStart >= oa_min & cpl$SexID ==0]))
      } else {
        oa_record_o <- NULL
      }

      cpl <- cpl %>%
        dplyr::filter(AgeStart < oa_min) %>%
        dplyr::filter(!(AgeSpan == -1 & AgeLabel != "Total" & AgeStart < oa_min)) %>%
        bind_rows(oa_record_m, oa_record_f, oa_record_b, oa_record_o) %>%
        arrange(SexID, AgeStart)
  } else {
    cpl <- NULL
  }

… and the two resulting datasets are appended to form one.

  df <- rbind(abr, cpl)

It is at this point that the validation of totals by sex takes place.

# now validate totals over sex
  df <- df %>%
    mutate(SexID = paste0("X", SexID)) %>%
    spread(key = SexID, value = DataValue)

  if (!("X0" %in% names(df))) {
    df$X0 <- 0
  }
  if (!("X1" %in% names(df))) {
    df$X1 <- 0
  }
  if (!("X2" %in% names(df))) {
    df$X2 <- 0
  }
  if (!("X3" %in% names(df))) {
    df$X3 <- 0
  }


  data.out <- df %>%
    mutate(X0 = replace(X0, is.na(X0), 0),
           X1 = replace(X1, is.na(X1), 0),
           X2 = replace(X2, is.na(X2), 0),
           X3 = replace(X3, is.na(X3), 0),
           total_over_sex = X0 + X1 + X2,
           X3 = ifelse(X3 > total_over_sex, X3, total_over_sex), # total as max of reported total or sum over sex
           X1 = ifelse(X1 == 0 & total_over_sex > 0 & X3 > total_over_sex, X3 - total_over_sex, X1),
           total_over_sex = X0 + X1 + X2,
           X2 = ifelse(X2 == 0 & total_over_sex > 0 & X3 > total_over_sex, X3 - total_over_sex, X2),
           total_over_sex = X0 + X1 + X2,
           X0 = ifelse(X3 > total_over_sex & total_over_sex > 0, X0 + (X3 - total_over_sex), X0 ),
           pct_m = X1 / (X1 + X2), # percentage of males among males plus females
           pct_m = replace(pct_m, is.na(pct_m), 0), # avoids divide by zero problems
           X1 = X1 + (X0 * pct_m),
           X2 = X2 + (X0 * (1-pct_m)),
           X3 = X1 + X2) %>%
    select(-X0, -total_over_sex, -pct_m) %>%
    gather(key = "SexID", value = "DataValue", c("X1","X2","X3")) %>%
    mutate(SexID = as.numeric(substr(SexID,2,2)),
           note = NA) %>%
    arrange(SexID, AgeSort)

  1. dd_validate_totals_over_sex↩︎

  2. dd_validate_totals_over_sex_new↩︎

  3. dd_series_isfull↩︎