This analysis is based on the raw “Population and live birth” data from the WHO mortality database (more info here), that contains data from 1950 to 2019 from countries all over the world.

Preparing the dataset

Loading required libraries and retrieve population data from WHO website:

library(tidyverse)
library(xtable)
if(!file.exists("pop")) {
    download.file("https://cdn.who.int/media/docs/default-source/world-health-data-platform/mortality-raw-data/mort_pop.zip?sfvrsn=937039fc_10&ua=1", "mort_pop.zip", method = "curl")
    unzip("mort_pop.zip")
}
df<-read.csv("pop")

if(!file.exists("country_codes")) {
    download.file("https://cdn.who.int/media/docs/default-source/world-health-data-platform/mortality-raw-data/mort_country_codes.zip?sfvrsn=800faac2_5&ua=1", "mort_country_codes.zip", method="curl")
    unzip("mort_country_codes.zip")
}
codes<-read.csv("country_codes")

Variable details according to the accompanying documentation:

Column name Content
Country Country code – see file “Country_codes.zip”
Admin1 Specified region/Category pertinent to each country– see file “Country_codes.zip”. If this field is blank, data reported refer to the country
Subdiv1 Category of data – see Annex Table 2 below. If this field is blank, data reported refer to the country.
Year Year to which data refer
Sex 1 male, 2 female
Frmat Age-group format for breakdown of deaths at 0-95+ yrs – see Annex Table 1 below for details
Pop1 Population at all ages
Pop2 Population at age 0 year
Pop3 Population at age1 year
Pop4 Population at age 2 years
Pop5 Population at age 3 years
Pop6 Population at age 4 years
Pop7 Population at age 5-9 years
Pop8 Population at age 10-14 years
Pop9 Population at age 15-19 years
Pop10 Population at age 20-24 years
Pop11 Population at age 25-29 years
Pop12 Population at age 30-34 years
Pop13 Population at age 35-39 years
Pop14 Population at age 40-44 years
Pop15 Population at age 45-49 years
Pop16 Population at age 50-54 years
Pop17 Population at age 55-59 years
Pop18 Population at age 60-64 years
Pop19 Population at age 65-69 years
Pop20 Population at age 70-74 years
Pop21 Population at age 75-79 years
Pop22 Population at age 80-84 years
Pop23 Population at age 85-89 years
Pop24 Population at age 90-94 years
Pop25 Population at age 95 years and over
Pop26 Population at age unspecified
Lb Live births

Joining country names with population data via the country codes and summarize by country:

data<-inner_join(df, codes, by=c("Country"="country"))
d<- data %>%
group_by(name, Year, Sex) %>%
summarize_at(colnames(df)[7:33], function(x) as.integer((sum(x, na.rm=T)))) %>% ungroup
dim(d)

[1] 9649 30

print(xtable(head(d)), type="html")
name Year Sex Pop1 Pop2 Pop3 Pop4 Pop5 Pop6 Pop7 Pop8 Pop9 Pop10 Pop11 Pop12 Pop13 Pop14 Pop15 Pop16 Pop17 Pop18 Pop19 Pop20 Pop21 Pop22 Pop23 Pop24 Pop25 Pop26 Lb
1 Albania 1985 1 1526000 40700 143800 0 0 0 167500 158300 156000 145400 143000 121200 92400 71100 71300 61900 49500 35900 29300 17800 12000 5700 3400 0 0 0 0
2 Albania 1985 2 1431400 36700 131200 0 0 0 152700 145600 142400 135500 133600 113200 84800 63700 65200 53800 45800 38600 32900 21400 17700 10200 6300 0 0 0 0
3 Albania 1986 1 1553300 41500 146300 0 0 0 170500 161100 158700 148000 145600 123400 94000 72400 72600 63000 50400 36500 29800 18100 12200 5800 3400 0 0 0 0
4 Albania 1986 2 1461900 37500 134000 0 0 0 156000 148700 145500 138400 136500 115600 86600 65100 66500 55000 46800 39400 33600 21900 18000 10500 6400 0 0 0 0
5 Albania 1987 1 1584200 42300 149200 0 0 0 173900 164300 161900 151000 148500 125800 95900 73800 74100 64200 51400 37200 30400 18500 12400 5900 3500 0 0 0 41479
6 Albania 1987 2 1491900 38300 136800 0 0 0 159200 151800 148500 141200 139300 117900 88400 66400 67900 56100 47800 40200 34300 22300 18400 10700 6500 0 0 0 38217

Data cleaning and exploration

Let’s see how many missing data are there in each column, and check if total population is always equal to the sum of all age groups:

apply(d[,2:ncol(d)],2,function(x) sum(x==0)) 
##  Year   Sex  Pop1  Pop2  Pop3  Pop4  Pop5  Pop6  Pop7  Pop8  Pop9 Pop10 Pop11 
##     0     0     5   141   141  4183  4183  4183   141   159   141   159   141 
## Pop12 Pop13 Pop14 Pop15 Pop16 Pop17 Pop18 Pop19 Pop20 Pop21 Pop22 Pop23 Pop24 
##   159   141   159   141   159   141   159   141   203   297  1193  1193  8303 
## Pop25 Pop26    Lb 
##  8303  9366   278
d$sum<-as.integer(rowSums(d[,5:29]))
sum(d$sum==d$Pop1) 
## [1] 5037

Now, let’s look at a summary of data grouped by year:

riass<-d[,-1] %>% group_by(Year) %>% 
    summarize_all(function(x) as.integer(sum(x, na.rm=T))) %>% 
    mutate(Year=as.character(Year)) %>% ungroup
print(xtable(riass), type="html", format.args = list(big.mark="'"))
Year Sex Pop1 Pop2 Pop3 Pop4 Pop5 Pop6 Pop7 Pop8 Pop9 Pop10 Pop11 Pop12 Pop13 Pop14 Pop15 Pop16 Pop17 Pop18 Pop19 Pop20 Pop21 Pop22 Pop23 Pop24 Pop25 Pop26 Lb sum
1 1950 126 737’609’300 15’492’000 35’212’900 9’042’600 9’096’400 7’568’500 65’624’600 62’398’900 59’660’800 59’607’900 57’919’400 47’233’700 52’064’800 50’121’200 45’340’800 39’590’400 33’056’800 28’554’000 22’995’700 17’170’400 10’578’300 5’174’400 2’428’500 0 0 39’900 16’689’904 735’972’900
2 1951 126 745’762’800 15’276’700 40’738’300 7’422’100 7’274’300 7’142’400 64’891’400 61’258’000 58’277’000 57’915’300 56’976’600 48’032’900 49’884’700 49’090’400 44’397’100 39’017’700 32’597’800 27’818’200 22’670’600 16’819’400 10’593’800 5’161’500 2’430’900 0 0 45’000 16’533’719 725’732’100
3 1952 129 755’978’100 15’582’200 38’634’500 8’075’500 8’281’500 8’138’900 69’219’800 63’950’700 59’948’700 59’071’600 58’368’700 51’648’000 49’363’700 51’058’200 46’593’700 41’344’400 34’497’400 29’323’600 23’890’300 17’784’100 11’325’500 5’487’300 2’580’200 0 0 61’000 16’642’430 754’229’500
4 1953 132 765’864’000 15’607’000 36’552’200 8’597’200 8’774’800 9’002’300 71’611’500 64’795’200 60’448’800 58’879’700 58’704’100 54’342’900 47’421’700 51’334’100 47’342’800 42’225’100 35’357’000 29’633’600 24’434’700 18’079’700 11’715’900 5’664’200 2’708’500 0 0 64’200 16’640’451 763’297’200
5 1954 132 775’613’500 15’740’500 36’904’900 8’440’100 8’591’500 8’740’500 73’995’300 65’913’900 60’929’100 58’870’600 58’946’300 56’837’000 45’913’900 51’519’300 48’183’000 43’024’000 36’186’500 30’068’500 24’904’600 18’459’200 11’994’300 5’911’500 2’819’600 0 0 67’200 16’827’916 772’961’300
6 1955 168 860’650’500 18’000’400 43’907’400 8’775’900 8’823’800 8’950’100 85’395’800 73’587’400 68’468’400 65’354’900 65’172’400 62’852’000 51’351’900 55’281’700 52’814’500 46’901’900 39’862’500 32’687’300 26’744’800 19’926’400 12’847’400 6’419’600 3’153’900 0 0 73’400 19’729’789 857’353’800
7 1956 168 871’366’300 18’299’400 45’421’300 8’480’800 8’529’500 8’566’200 88’069’200 73’911’500 69’026’400 65’724’000 65’417’500 63’245’800 53’466’300 54’277’300 53’382’400 47’508’400 40’811’400 33’366’400 27’095’700 20’278’900 13’084’400 6’673’600 3’263’900 0 0 80’200 19’803’260 867’980’500
8 1957 168 883’126’900 18’562’100 46’171’200 8’539’600 8’487’400 8’522’300 87’941’200 77’216’000 69’997’600 65’898’900 65’586’800 63’562’100 56’359’200 52’556’300 54’008’900 48’312’300 41’867’200 34’112’600 27’571’000 20’696’500 13’323’600 6’888’800 3’383’700 0 0 75’400 19’569’880 879’640’700
9 1958 186 951’521’400 20’794’700 55’195’300 8’507’400 8’496’300 8’433’000 97’511’700 87’281’200 76’851’200 71’369’200 69’559’500 67’549’000 62’344’200 53’009’500 56’579’800 50’934’900 44’221’700 36’219’200 28’626’400 21’766’500 14’055’300 7’309’400 3’685’000 0 0 64’800 21’996’354 950’365’200
10 1959 186 952’122’000 20’379’100 55’533’500 7’874’500 7’923’800 7’925’800 95’343’800 87’572’200 75’405’800 69’843’500 67’654’100 66’361’300 63’601’800 50’537’600 55’862’100 51’172’600 44’549’200 36’709’400 28’797’700 21’990’400 14’319’800 7’481’500 3’805’300 0 0 63’300 22’396’590 940’708’100
11 1960 210 1’004’439’000 22’368’300 62’261’800 8’091’700 8’051’400 8’095’600 101’989’500 95’451’800 79’260’500 73’616’100 70’565’100 69’298’400 66’407’800 53’292’000 57’083’700 53’149’400 46’358’400 38’324’400 29’885’400 22’984’600 15’026’400 7’551’700 3’791’000 0 0 47’500 23’650’079 992’952’500
12 1961 213 1’021’690’600 22’782’300 60’960’100 9’027’900 8’943’900 8’862’600 103’276’800 98’930’900 80’420’900 74’408’000 70’887’200 69’805’800 67’096’900 55’569’500 56’298’500 53’968’200 47’026’000 39’406’100 30’514’900 23’409’700 15’379’700 7’843’100 4’027’500 0 0 82’700 23’885’285 1’008’929’200
13 1962 213 1’041’700’100 23’209’100 62’359’800 9’066’500 9’055’200 8’963’300 105’010’800 99’629’300 84’527’400 75’931’200 71’808’600 70’433’600 67’946’700 58’649’700 54’933’600 54’912’700 48’011’600 40’697’300 31’311’200 23’972’200 15’819’300 8’096’000 4’177’200 0 0 47’600 24’281’077 1’028’569’900
14 1963 213 1’057’559’600 23’524’600 60’430’200 10’110’800 10’046’400 10’029’100 106’573’000 100’345’400 88’108’700 77’504’700 72’352’000 70’533’200 68’435’800 61’930’700 53’093’700 55’509’300 48’853’100 41’730’500 32’038’400 24’335’500 16’164’000 8’305’600 4’301’600 0 0 115’600 24’687’606 1’044’371’900
15 1964 213 1’085’953’900 24’737’600 64’293’900 10’355’100 10’240’800 10’166’600 111’704’700 104’012’600 93’451’900 80’592’100 74’982’700 72’145’700 70’513’300 65’679’100 52’862’900 56’765’300 50’302’800 43’171’800 33’035’800 24’929’200 16’930’500 8’428’700 4’384’700 0 0 46’100 25’567’118 1’083’733’900
16 1965 216 1’102’588’400 24’923’000 65’421’000 10’644’700 10’500’700 10’395’500 113’557’000 105’459’000 97’439’100 81’312’100 76’116’000 72’527’600 71’231’600 67’062’600 53’766’100 56’679’400 51’354’700 44’091’700 34’089’000 25’461’500 17’260’100 8’700’700 4’546’600 0 0 46’600 25’259’118 1’102’586’300
17 1966 219 1’117’132’100 24’335’200 53’188’100 14’953’600 14’799’800 14’740’200 114’756’000 106’271’800 100’362’000 81’791’300 76’863’900 72’533’000 71’409’500 67’391’200 55’866’000 55’602’600 51’909’900 44’627’400 34’968’400 25’912’000 17’560’100 8’937’200 4’683’800 0 0 61’500 24’591’326 1’113’524’500
18 1967 210 1’126’760’200 24’744’700 54’083’000 14’428’800 14’557’000 14’454’900 115’995’000 107’370’000 100’569’000 84’979’400 77’696’100 72’850’000 71’561’400 67’796’400 58’652’900 53’920’800 52’481’400 45’303’200 35’884’200 26’488’700 17’826’400 9’216’300 4’899’700 0 0 45’000 25’034’654 1’125’804’300
19 1968 204 1’109’049’200 23’920’400 62’516’900 10’122’500 10’565’400 10’518’600 112’956’600 104’974’800 98’608’000 86’082’500 76’707’400 71’615’400 69’576’700 66’805’700 60’329’800 50’837’300 52’209’000 45’204’400 36’390’500 26’688’900 17’818’200 9’491’300 5’068’400 0 0 45’000 23’796’803 1’109’053’700
20 1969 219 1’128’759’600 24’176’400 61’850’300 10’913’900 10’551’000 10’989’600 114’775’100 107’173’400 99’570’200 89’906’200 78’205’800 72’410’200 69’939’700 67’517’300 63’193’200 49’542’500 52’647’600 46’147’000 37’402’900 27’439’700 17’917’200 10’023’800 5’454’700 0 0 44’700 23’851’424 1’127’792’400
21 1970 228 1’129’443’700 23’648’200 61’873’900 11’299’300 11’122’100 10’718’500 116’031’400 107’635’300 98’959’900 92’673’700 78’508’600 72’473’100 69’335’200 66’515’100 62’901’400 49’654’600 51’362’200 46’152’100 37’249’300 27’511’100 18’199’500 9’915’500 5’536’900 0 0 174’400 24’099’616 1’129’451’300
22 1971 222 1’174’507’100 24’894’200 62’364’200 11’587’500 11’679’500 11’584’600 118’572’000 112’353’900 103’096’100 98’248’300 80’900’900 75’164’700 71’842’500 69’140’800 65’175’600 53’622’400 51’667’800 48’433’200 39’073’700 29’427’600 19’061’800 10’403’500 6’044’800 0 0 174’100 25’072’931 1’174’513’700
23 1972 222 1’179’124’000 24’358’300 64’209’300 10’698’600 10’853’700 10’951’700 117’595’500 112’311’000 103’370’000 97’764’500 83’943’200 75’505’700 71’538’900 68’959’100 65’195’300 56’168’400 49’808’600 48’784’500 39’714’500 30’140’500 19’435’900 10’608’800 6’192’900 0 0 177’600 24’130’351 1’178’286’500
24 1973 225 1’200’788’900 23’971’400 72’094’200 8’113’200 8’053’300 8’191’000 118’643’700 114’226’000 105’097’800 98’600’900 87’309’300 77’022’900 72’470’200 69’361’400 65’777’500 58’975’000 48’193’600 49’285’200 40’466’900 31’074’700 19’304’600 10’822’700 6’440’600 0 0 395’000 24’650’912 1’193’891’100
25 1974 222 1’214’914’100 23’890’800 58’561’600 12’415’900 12’832’200 12’791’700 117’809’900 115’618’300 106’904’900 99’600’000 90’901’800 78’349’600 72’738’400 69’339’700 66’372’300 61’785’500 46’885’800 49’453’200 41’588’900 32’097’500 20’416’200 10’694’200 6’475’800 0 0 268’000 24’521’398 1’207’792’200
26 1975 243 1’237’433’000 23’896’200 56’340’700 13’224’100 13’542’900 13’948’700 119’383’500 118’123’600 109’722’600 101’397’100 95’089’900 79’133’600 73’836’900 69’629’400 67’414’700 62’744’000 48’372’000 49’445’900 42’594’400 32’643’500 21’700’100 11’100’600 6’760’800 0 0 44’000 24’193’240 1’230’089’200
27 1976 246 1’253’386’000 24’313’800 57’860’200 12’542’300 12’794’100 13’188’600 119’900’900 118’565’600 111’666’500 102’972’500 98’567’800 80’330’300 74’507’300 70’215’700 67’728’000 63’429’000 50’791’500 48’720’400 43’446’700 33’689’700 22’264’300 11’366’400 6’879’400 0 0 53’800 24’200’320 1’245’794’800
28 1977 249 1’272’520’900 22’024’700 49’022’000 12’496’200 12’659’400 12’918’900 117’820’200 106’525’600 112’303’400 95’800’800 97’253’900 78’192’900 74’817’100 66’790’300 67’717’100 61’111’700 53’192’100 45’618’900 44’061’700 33’071’400 22’356’100 11’677’800 7’060’900 0 0 53’000 24’541’348 1’204’546’100
29 1978 249 1’283’286’600 24’520’300 54’695’200 13’904’800 14’097’400 14’197’500 120’513’200 119’083’500 113’751’000 105’338’900 98’973’700 87’328’900 76’675’300 71’168’500 68’117’500 64’370’100 56’430’100 45’702’700 44’407’800 35’101’600 23’593’700 12’068’300 7’429’100 0 0 48’700 24’443’582 1’271’517’800
30 1979 252 1’384’616’700 26’168’400 62’094’400 14’577’300 14’645’000 14’917’800 130’282’200 129’324’000 125’822’000 116’157’000 107’494’000 96’938’100 83’014’900 76’290’600 71’905’600 68’288’800 61’723’100 46’678’400 47’323’900 36’226’200 25’035’500 12’757’400 7’889’100 0 0 50’000 25’814’615 1’375’603’700
31 1980 255 1’565’989’454 29’398’510 56’414’009 20’688’701 20’697’000 20’671’062 143’904’748 142’633’059 142’826’968 133’950’271 123’116’338 112’223’074 91’683’473 90’116’843 81’880’325 80’316’992 69’653’225 52’905’943 52’327’039 41’718’269 29’091’913 14’924’664 9’160’528 0 0 86’100 29’037’976 1’560’389’054
32 1981 291 1’961’456’357 36’630’023 94’182’061 17’157’695 16’858’030 16’916’674 176’113’014 174’707’481 176’279’836 171’776’194 156’669’703 143’916’367 108’891’441 119’703’091 103’921’081 105’903’289 90’014’852 66’819’865 64’339’056 53’288’902 36’556’494 19’069’398 11’682’410 0 0 0 37’337’595 1’961’396’957
33 1982 291 1’934’409’768 35’351’668 112’007’762 9’658’259 9’644’579 9’622’959 169’956’415 169’043’564 169’622’645 168’931’795 155’086’636 143’217’050 110’494’616 115’738’996 104’083’442 103’369’676 91’726’522 69’293’363 60’853’554 54’304’726 36’540’913 19’873’174 11’947’897 14’441 2’416 1’700 35’748’215 1’930’388’768
34 1983 264 1’953’294’290 36’188’605 99’871’296 14’460’299 14’346’097 14’238’977 171’464’201 167’971’173 169’424’771 169’800’977 158’842’587 145’363’025 118’054’750 111’094’920 108’242’586 101’532’050 95’022’015 73’288’651 58’194’999 54’919’870 37’756’650 20’830’303 12’361’070 15’095 2’523 5’100 36’159’189 1’953’292’590
35 1984 225 1’821’166’971 31’761’112 82’061’232 16’221’845 16’223’112 16’401’531 154’769’827 153’887’030 152’278’647 157’656’662 149’392’621 138’026’440 117’808’535 101’796’721 102’543’949 92’750’405 90’252’590 73’010’037 53’345’141 52’136’800 36’784’199 20’086’338 11’954’715 15’880 2’702 0 32’197’555 1’821’168’071
36 1985 282 2’124’225’490 38’844’963 113’498’716 14’372’506 14’466’772 14’533’029 187’716’136 182’983’426 181’464’495 182’266’596 175’860’152 159’017’781 141’838’695 111’562’364 120’402’587 103’330’425 103’219’558 83’704’389 59’822’222 57’558’804 41’421’069 22’747’397 13’560’865 27’559 6’080 400 39’539’682 2’124’226’986
37 1986 255 38’681’255 119’262’150 11’838’257 12’072’132 12’186’373 187’162’491 183’833’404 181’074’504 181’798’240 177’481’450 163’265’766 149’280’685 112’311’729 122’039’506 105’616’601 104’780’469 87’484’805 62’223’189 56’272’847 42’328’027 23’580’549 14’340’581 27’778 6’253 4’400 38’852’302
38 1987 258 37’689’498 116’284’139 11’331’388 11’284’097 11’473’990 183’620’888 179’975’136 186’559’718 182’178’242 187’029’252 170’775’128 154’914’258 118’820’034 124’363’682 111’265’623 107’981’743 92’359’054 67’754’421 54’763’073 44’163’664 24’692’129 15’192’337 29’245 6’248 1’200 38’993’933
39 1988 240 37’654’560 124’492’158 8’146’277 8’235’739 8’242’921 182’434’946 180’432’691 184’306’368 180’541’855 184’812’221 173’656’032 156’651’843 125’097’876 120’080’701 114’419’217 106’550’665 95’534’115 71’482’606 52’869’862 45’233’837 25’405’026 15’791’977 31’443 6’228 700 38’189’418
40 1989 222 35’930’593 110’907’317 10’801’399 10’884’750 11’011’462 176’897’668 174’963’616 180’129’708 175’186’482 183’193’721 174’504’005 157’050’486 131’617’061 115’779’481 117’352’219 103’647’046 97’567’522 75’070’102 51’424’861 45’611’956 26’337’417 16’505’140 42’994 9’903 5’700 36’308’410
41 1990 237 35’402’993 124’051’792 6’801’842 6’843’450 6’877’573 178’273’270 174’165’447 179’234’203 177’953’181 185’627’031 179’142’657 163’721’284 143’839’093 115’640’736 123’383’256 105’740’072 102’385’001 80’570’437 53’801’847 47’931’816 28’987’469 18’139’444 47’461 10’782 200 36’306’803
42 1991 225 1’914’966’945 30’037’654 87’103’281 11’683’644 11’962’737 12’071’991 153’159’875 150’443’399 154’376’581 157’167’761 158’430’400 154’250’576 141’364’897 128’202’928 98’499’319 99’251’242 87’999’059 83’216’210 68’869’741 47’877’905 39’009’424 24’238’339 15’641’247 79’330 18’126 7’400 30’147’136 1’914’963’066
43 1992 216 1’871’511’623 27’510’066 85’734’416 9’959’002 10’253’010 10’512’626 146’685’819 146’223’753 149’063’315 151’375’012 152’510’663 149’946’748 140’479’779 126’661’340 100’850’760 97’001’157 87’507’267 81’649’707 69’412’381 49’612’484 37’093’371 25’127’896 16’023’623 261’758 53’895 1’100 28’570’725 1’871’510’948
44 1993 219 1’880’956’797 26’826’734 86’708’054 9’319’174 9’449’477 9’568’027 145’735’536 146’612’318 147’461’587 151’857’853 150’139’742 151’334’435 142’832’798 128’226’073 104’974’860 96’111’148 89’049’046 81’039’681 70’702’946 52’346’190 35’791’813 25’744’365 16’749’458 276’266 56’838 2’043’300 27’982’827 1’880’957’719
45 1994 228 2’089’615’331 30’029’084 89’564’558 12’662’953 12’952’368 13’223’122 164’523’854 163’902’360 163’881’632 168’831’495 167’952’146 168’577’117 158’725’174 141’259’250 119’337’081 104’554’787 99’891’019 86’875’099 77’659’739 59’573’671 37’705’152 28’679’811 18’910’771 286’581 58’720 0 30’336’643 2’089’617’544
46 1995 213 1’873’199’881 25’790’179 78’804’654 10’235’784 10’630’686 10’892’506 144’111’819 144’859’943 145’547’051 148’056’110 146’513’256 149’456’459 144’356’691 129’252’518 114’355’058 92’544’235 91’256’673 78’665’295 72’037’239 55’851’753 35’454’364 26’274’776 17’871’039 298’366 62’050 20’900 25’998’188 1’873’199’404
47 1996 192 1’686’585’803 21’163’729 57’172’855 11’260’670 11’552’404 12’004’161 121’955’453 123’381’030 125’087’320 128’359’448 130’585’623 133’322’647 132’699’756 120’042’717 110’289’902 85’278’446 86’046’048 74’338’615 68’752’965 54’551’653 35’473’286 24’866’699 17’954’265 354’308 74’747 17’800 20’737’415 1’686’586’547
48 1997 183 1’583’563’240 18’208’424 47’139’981 10’495’153 10’737’389 11’030’333 108’050’652 112’399’733 115’902’250 117’442’226 121’731’396 123’532’562 126’266’906 116’357’597 106’571’688 84’740’120 82’005’940 72’338’783 66’229’281 53’878’569 36’265’247 23’501’418 18’191’737 437’104 94’567 14’021 18’090’685 1’583’563’077
49 1998 189 1’613’868’548 18’518’741 52’984’626 8’978’684 9’189’097 9’399’616 110’175’903 115’576’616 119’539’030 118’604’994 122’830’611 123’673’130 127’957’284 119’259’546 108’274’434 89’241’282 81’581’082 74’639’754 66’386’615 55’242’312 38’416’815 22’758’437 18’855’686 525’849 113’343 1’147’600 18’419’562 1’613’871’087
50 1999 198 1’631’567’671 18’361’115 48’482’159 10’411’044 10’554’666 10’790’100 109’027’940 115’819’453 120’660’439 120’140’150 123’917’015 124’092’778 129’384’613 121’819’734 109’548’240 94’657’250 81’271’849 77’058’412 66’048’895 56’498’310 40’578’133 22’388’124 19’073’691 812’827 166’391 2’820 18’243’403 1’631’566’148
51 2000 195 1’634’909’227 18’168’998 50’828’820 9’021’374 9’216’727 9’489’659 106’505’699 115’624’768 120’914’143 120’249’943 123’258’252 123’212’967 128’338’379 122’470’923 111’170’235 100’450’034 79’562’490 77’763’315 65’873’967 57’308’292 41’877’663 23’092’346 18’520’683 1’621’805 365’411 2’402 18’479’233 1’634’909’295
52 2001 201 1’469’105’700 17’970’435 41’593’517 9’568’832 9’755’512 9’969’785 96’845’843 106’430’660 107’375’978 104’075’830 105’313’801 108’159’287 110’458’419 109’502’208 100’679’184 94’156’428 70’614’225 71’100’478 59’886’360 53’695’592 39’543’187 22’712’395 18’345’260 1’086’999 245’797 21’576 18’025’594 1’469’107’588
53 2002 204 1’538’700’776 18’660’368 44’185’920 9’891’155 9’986’663 10’223’146 100’203’582 110’489’077 112’705’674 110’225’281 110’108’482 114’564’497 115’376’033 115’448’961 105’922’533 98’136’782 75’927’716 73’104’747 62’646’467 54’851’313 41’200’514 24’769’575 18’478’683 1’250’537 342’094 1’190 18’856’275 1’538’700’990
54 2003 201 1’482’275’303 17’931’237 42’218’925 9’375’901 9’470’831 9’552’875 93’941’448 104’266’785 108’158’734 106’267’517 103’940’835 108’273’707 108’935’148 110’998’668 103’321’851 95’377’114 77’641’492 70’071’293 62’492’234 53’295’414 41’347’589 25’942’180 17’802’835 1’347’664 299’980 1’137 18’239’267 1’482’273’394
55 2004 201 1’566’178’454 20’296’605 51’096’402 9’309’683 9’384’575 9’485’819 103’125’086 112’052’778 116’394’505 114’528’643 110’434’006 113’575’492 113’218’054 115’347’286 107’998’869 98’031’531 83’566’659 70’421’784 64’884’502 53’375’841 42’002’759 27’384’622 18’570’076 1’381’822 317’192 1’024 18’385’315 1’566’185’615
56 2005 201 1’568’202’162 20’242’423 50’728’381 9’451’699 9’347’747 9’428’827 100’880’087 108’888’773 115’386’849 114’545’890 111’137’390 112’684’161 112’817’893 114’955’957 109’607’715 98’910’525 88’632’631 68’900’426 66’118’561 52’224’282 43’777’012 28’328’202 18’458’164 2’188’313 559’034 926 18’216’348 1’568’201’868
57 2006 186 1’424’088’691 17’255’316 41’317’120 8’734’439 8’616’248 8’541’184 85’647’262 91’567’837 99’863’130 101’262’652 100’001’346 101’041’563 103’472’896 104’071’852 102’342’885 93’113’975 86’975’938 64’707’337 63’325’925 50’581’099 41’886’492 28’285’033 18’596’232 2’259’824 617’637 851 13’415’491 1’424’086’073
58 2007 201 1’581’651’090 20’370’454 48’178’517 10’013’848 10’017’343 9’878’081 98’724’077 104’090’111 112’853’366 115’298’269 114’140’081 112’458’374 115’222’100 113’597’818 112’343’955 101’846’610 93’136’240 71’960’937 66’642’432 54’129’487 44’096’748 29’787’675 20’467’852 1’846’608 548’197 767 15’235’092 1’581’649’947
59 2008 201 1’284’979’812 16’544’090 32’887’515 9’569’764 9’476’940 9’492’786 76’847’162 80’812’476 89’708’669 95’548’749 95’032’960 92’474’251 94’254’264 91’761’433 90’381’453 82’532’694 75’603’402 60’640’909 54’735’595 46’690’290 36’222’259 25’028’584 15’810’972 2’205’162 718’782 669 13’844’230 1’284’981’830
60 2009 201 1’401’660’996 18’782’387 33’157’278 12’647’369 12’343’391 12’240’469 86’789’609 90’300’487 98’442’654 105’383’469 105’774’556 101’129’646 102’377’577 98’709’701 97’342’170 89’233’771 79’797’140 67’121’115 55’943’848 49’876’116 38’649’216 26’185’266 16’434’043 2’223’595 774’535 596 17’083’649 1’401’660’004
61 2010 210 1’403’797’400 18’830’777 33’763’090 12’699’051 12’302’308 12’002’175 86’287’501 89’217’946 97’022’173 104’124’215 104’862’475 101’116’539 102’264’656 98’751’572 97’467’620 90’310’966 80’254’363 71’090’486 54’614’631 50’992’590 38’621’931 26’834’351 17’758’439 1’925’097 681’457 513 14’188’558 1’403’796’922
62 2011 228 1’390’818’997 18’544’598 30’373’449 13’290’534 13’111’114 12’662’735 83’923’232 86’142’463 93’379’588 101’841’732 102’837’325 99’898’137 100’110’896 98’576’647 96’434’696 91’065’135 81’120’428 73’956’085 53’553’073 51’609’511 39’419’900 27’461’774 17’781’630 2’836’215 890’289 454 14’847’539 1’390’821’640
63 2012 204 1’398’676’316 18’849’953 28’664’032 13’844’782 13’799’256 13’670’085 84’799’604 85’704’782 92’353’056 100’852’285 102’623’753 100’592’913 99’614’770 99’128’708 96’331’004 92’388’030 82’334’596 74’766’761 55’832’363 51’470’412 40’582’431 27’904’485 18’916’112 2’839’449 812’686 412 14’530’542 1’398’676’720
64 2013 219 1’456’905’750 20’392’056 36’941’868 13’532’366 13’420’484 13’386’230 94’628’794 92’881’630 97’102’289 104’432’114 107’507’824 106’298’767 102’193’378 101’968’166 98’206’533 94’226’077 84’081’637 74’976’624 58’972’211 49’854’849 41’162’817 27’580’927 18’643’643 3’594’845 917’225 374 12’935’211 1’456’903’728
65 2014 210 1’495’408’716 19’764’194 31’905’381 15’983’906 15’911’051 15’907’588 96’634’033 93’479’114 98’141’591 106’760’638 110’879’096 108’954’236 104’346’871 104’257’290 100’648’109 97’707’798 87’490’347 76’778’358 62’340’846 51’446’456 42’529’981 28’873’411 19’609’284 4’067’884 990’918 334 17’576’441 1’495’408’715
66 2015 195 1’414’794’746 18’635’129 29’215’564 15’571’720 15’624’126 15’538’760 92’886’056 87’462’708 90’790’536 98’536’482 104’390’228 102’697’378 99’234’590 98’349’651 94’633’208 92’099’437 83’894’618 73’015’598 63’009’733 46’583’931 42’492’368 26’669’751 17’635’062 4’684’021 1’142’666 62 15’308’646 1’414’793’383
67 2016 186 1’154’646’681 13’869’469 22’445’390 13’624’679 13’828’665 13’692’943 76’214’348 71’709’535 74’850’066 80’370’187 84’267’639 83’753’066 80’836’021 79’394’754 78’233’881 73’749’343 66’577’907 58’263’727 52’780’451 39’021’055 33’605’044 23’015’429 15’261’023 4’203’384 1’077’525 0 10’487’664 1’154’645’531
68 2017 162 898’497’976 10’995’694 14’926’576 10’005’969 10’120’866 9’956’061 55’074’523 51’712’621 53’164’632 57’218’310 61’327’901 62’375’537 62’839’093 63’024’385 62’911’178 59’886’914 56’021’661 49’419’895 44’944’584 33’534’195 29’282’832 20’906’039 13’223’585 4’432’247 1’191’393 0 9’715’840 898’496’691
69 2018 129 744’468’552 9’215’750 12’108’400 8’661’484 8’758’256 8’521’256 45’895’148 43’557’057 44’156’457 47’521’182 50’882’204 51’707’641 51’862’203 50’823’404 51’280’228 48’971’886 46’329’067 41’342’029 37’324’533 28’741’222 24’077’153 17’337’721 10’812’055 3’583’338 999’437 0 7’959’367 744’469’111
70 2019 84 902’450’654 11’976’962 14’867’650 12’085’921 12’182’408 12’179’610 62’559’638 59’127’241 58’618’243 62’835’752 67’229’335 70’533’598 68’921’125 62’844’510 59’055’449 56’278’182 54’801’397 47’058’455 37’454’670 26’099’397 20’197’399 14’655’286 8’692’597 1’777’603 418’281 0 8’438’610 902’450’709


And finally, let’s see the complete list of countries in the dataset:

unique(d$name)
##   [1] "Albania"                            
##   [2] "Andorra"                            
##   [3] "Antigua and Barbuda"                
##   [4] "Argentina"                          
##   [5] "Armenia"                            
##   [6] "Australia"                          
##   [7] "Austria"                            
##   [8] "Azerbaijan"                         
##   [9] "Bahamas"                            
##  [10] "Bahrain"                            
##  [11] "Barbados"                           
##  [12] "Belarus"                            
##  [13] "Belgium"                            
##  [14] "Belize"                             
##  [15] "Bermuda"                            
##  [16] "Bolivia"                            
##  [17] "Bosnia and Herzegovina"             
##  [18] "Brazil"                             
##  [19] "British Virgin Islands"             
##  [20] "Brunei Darussalam"                  
##  [21] "Bulgaria"                           
##  [22] "Canada"                             
##  [23] "Cape Verde"                         
##  [24] "Cayman Islands"                     
##  [25] "Chile"                              
##  [26] "China"                              
##  [27] "China: Province of Taiwan only"     
##  [28] "Colombia"                           
##  [29] "Costa Rica"                         
##  [30] "Croatia"                            
##  [31] "Cuba"                               
##  [32] "Cyprus"                             
##  [33] "Czech Republic"                     
##  [34] "Czechoslovakia, Former"             
##  [35] "Denmark"                            
##  [36] "Dominica"                           
##  [37] "Dominican Republic"                 
##  [38] "Ecuador"                            
##  [39] "Egypt"                              
##  [40] "El Salvador"                        
##  [41] "Estonia"                            
##  [42] "Falkland Islands (Malvinas)"        
##  [43] "Fiji"                               
##  [44] "Finland"                            
##  [45] "France"                             
##  [46] "French Guiana"                      
##  [47] "Georgia"                            
##  [48] "Germany"                            
##  [49] "Germany, Former Democratic Republic"
##  [50] "Germany, Former Federal Republic"   
##  [51] "Germany, West Berlin"               
##  [52] "Greece"                             
##  [53] "Grenada"                            
##  [54] "Guadeloupe"                         
##  [55] "Guatemala"                          
##  [56] "Guyana"                             
##  [57] "Honduras"                           
##  [58] "Hong Kong SAR"                      
##  [59] "Hungary"                            
##  [60] "Iceland"                            
##  [61] "Iran (Islamic Republic of)"         
##  [62] "Iraq"                               
##  [63] "Ireland"                            
##  [64] "Israel"                             
##  [65] "Italy"                              
##  [66] "Jamaica"                            
##  [67] "Japan"                              
##  [68] "Jordan"                             
##  [69] "Kazakhstan"                         
##  [70] "Kiribati"                           
##  [71] "Kuwait"                             
##  [72] "Kyrgyzstan"                         
##  [73] "Latvia"                             
##  [74] "Lebanon"                            
##  [75] "Libyan Arab Jamahiriya"             
##  [76] "Lithuania"                          
##  [77] "Luxembourg"                         
##  [78] "Malaysia"                           
##  [79] "Maldives"                           
##  [80] "Malta"                              
##  [81] "Martinique"                         
##  [82] "Mauritius"                          
##  [83] "Mexico"                             
##  [84] "Mongolia"                           
##  [85] "Montenegro"                         
##  [86] "Montserrat"                         
##  [87] "Morocco"                            
##  [88] "Netherlands"                        
##  [89] "Netherlands Antilles"               
##  [90] "New Zealand"                        
##  [91] "Nicaragua"                          
##  [92] "North Macedonia"                    
##  [93] "Norway"                             
##  [94] "Occupied Palestinian Territory"     
##  [95] "Oman"                               
##  [96] "Panama"                             
##  [97] "Papua New Guinea"                   
##  [98] "Paraguay"                           
##  [99] "Peru"                               
## [100] "Philippines"                        
## [101] "Poland"                             
## [102] "Portugal"                           
## [103] "Puerto Rico"                        
## [104] "Qatar"                              
## [105] "Republic of Korea"                  
## [106] "Republic of Moldova"                
## [107] "Reunion"                            
## [108] "Rodrigues"                          
## [109] "Romania"                            
## [110] "Russian Federation"                 
## [111] "Ryu Kyu Islands"                    
## [112] "Saint Kitts and Nevis"              
## [113] "Saint Lucia"                        
## [114] "Saint Pierre and Miquelon"          
## [115] "Saint Vincent and Grenadines"       
## [116] "San Marino"                         
## [117] "Sao Tome and Principe"              
## [118] "Serbia"                             
## [119] "Serbia and Montenegro, Former"      
## [120] "Seychelles"                         
## [121] "Singapore"                          
## [122] "Slovakia"                           
## [123] "Slovenia"                           
## [124] "Solomon Islands"                    
## [125] "South Africa"                       
## [126] "Spain"                              
## [127] "Sri Lanka"                          
## [128] "Suriname"                           
## [129] "Sweden"                             
## [130] "Switzerland"                        
## [131] "Syrian Arab Republic"               
## [132] "Tajikistan"                         
## [133] "Thailand"                           
## [134] "Tonga"                              
## [135] "Trinidad and Tobago"                
## [136] "Tunisia"                            
## [137] "Turkey"                             
## [138] "Turkmenistan"                       
## [139] "Turks and Caicos Islands"           
## [140] "Ukraine"                            
## [141] "United Kingdom"                     
## [142] "United Kingdom, England and Wales"  
## [143] "United Kingdom, Northern Ireland"   
## [144] "United Kingdom, Scotland"           
## [145] "United States of America"           
## [146] "Uruguay"                            
## [147] "USSR, Former"                       
## [148] "Uzbekistan"                         
## [149] "Venezuela"                          
## [150] "Virgin Islands (USA)"               
## [151] "Yugoslavia, Former"

In the Pop1 column, you can see that total world population varies inconsistently from year to year, and is always much lower than the actual world population. In fact, several countries are missing (for example, India and a lot of countries in Africa).
As world data are highly incomplete, we’ll use only data from Europe (including Russian Federation for consistency with former USSR, see “Managing changes in european political subdivision” section).

eu<-c("Albania", "Andorra", "Austria", "Belarus", "Belgium", "Bosnia and Herzegovina", "Bulgaria", "Czech Republic", "Czechoslovakia, Former", "Croatia", "Denmark", "Estonia", "Finland", "France", "Germany", "Germany, Former Democratic Republic", "Germany, Former Federal Republic",
 "Greece", "Hungary", "Iceland", "Ireland", "Italy", "Latvia", "Lithuania", "Luxembourg", "Malta", "Netherlands", "Norway", "North Macedonia", "Poland", "Portugal", "Romania", "Russian Federation", "San Marino", "Serbia","Serbia and Montenegro, Former", "Slovakia", "Slovenia", "Spain", "Sweden", "Switzerland", "Turkey" , "Ukraine", "United Kingdom", "USSR, Former","Yugoslavia, Former" ) #I excluded Cyprus because it has too many missing values
europe<-d %>% filter(name %in% eu)
sort(abs(europe$sum-europe$Pop1), decreasing = T)[1:100]
##   [1] 10198500  8153000   167500   165400   163700   163600   163400   162500
##   [9]   161800   161500   160200   159100   158300   157300   157300   156300
##  [17]   155200   154700   154400   154200   153900   153800   153200   153100
##  [25]   152900   152300   152100   151800   151500   151100   150800   150700
##  [33]   150000   149900   149100   149000   148300   147500     5000     5000
##  [41]     4000     3000     3000     3000     3000     2000     2000     2000
##  [49]     2000     2000     1000     1000     1000     1000     1000     1000
##  [57]     1000     1000     1000      600      600      600      600      500
##  [65]      500      500      500      500      500      500      500      500
##  [73]      500      500      500      500      500      500      500      500
##  [81]      500      500      500      500      500      500      500      500
##  [89]      500      400      400      400      400      400      400      400
##  [97]      400      400      400      400
print(xtable(europe[abs(europe$sum-europe$Pop1)>=1000,]), type="html")
name Year Sex Pop1 Pop2 Pop3 Pop4 Pop5 Pop6 Pop7 Pop8 Pop9 Pop10 Pop11 Pop12 Pop13 Pop14 Pop15 Pop16 Pop17 Pop18 Pop19 Pop20 Pop21 Pop22 Pop23 Pop24 Pop25 Pop26 Lb sum
1 Germany, Former Democratic Republic 1951 1 8153000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 161117 0
2 Germany, Former Democratic Republic 1951 2 10198500 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 149655 0
3 Italy 1950 1 22951000 412000 425000 430000 459000 455000 1963000 2105000 2004000 2036000 1928000 1386000 1635000 1646000 1397000 1170000 940000 825000 678000 508000 329000 151000 64000 0 0 0 468860 22946000
4 Italy 1950 2 24153000 397000 407000 413000 441000 437000 1895000 2057000 1991000 2028000 2005000 1496000 1732000 1711000 1477000 1320000 1178000 1037000 839000 604000 398000 194000 93000 0 0 0 442945 24150000
5 Luxembourg 1950 1 147500 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2069 0
6 Luxembourg 1950 2 149000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2047 0
7 Luxembourg 1951 1 148300 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2187 0
8 Luxembourg 1951 2 150000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1987 0
9 Luxembourg 1952 1 149100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2323 0
10 Luxembourg 1952 2 151100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2212 0
11 Luxembourg 1953 1 149900 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2358 0
12 Luxembourg 1953 2 152100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2207 0
13 Luxembourg 1954 1 150700 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2400 0
14 Luxembourg 1954 2 153200 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2313 0
15 Luxembourg 1955 1 151500 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2406 0
16 Luxembourg 1955 2 154200 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2258 0
17 Luxembourg 1956 1 152300 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2400 0
18 Luxembourg 1956 2 155200 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2433 0
19 Luxembourg 1957 1 153100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2617 0
20 Luxembourg 1957 2 156300 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2337 0
21 Luxembourg 1958 1 153900 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2601 0
22 Luxembourg 1958 2 157300 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2358 0
23 Luxembourg 1959 1 154700 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2618 0
24 Luxembourg 1959 2 158300 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2419 0
25 Luxembourg 1961 1 157300 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2583 0
26 Luxembourg 1961 2 161500 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2529 0
27 Luxembourg 1962 1 159100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2614 0
28 Luxembourg 1962 2 163600 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2523 0
29 Luxembourg 1964 1 162500 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2759 0
30 Luxembourg 1964 2 167500 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2470 0
31 Malta 1950 1 151800 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5273 0
32 Malta 1950 2 160200 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5008 0
33 Malta 1951 1 150800 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4811 0
34 Malta 1951 2 161800 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4700 0
35 Malta 1952 1 152900 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4839 0
36 Malta 1952 2 163700 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4387 0
37 Malta 1953 1 153800 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4661 0
38 Malta 1953 2 163400 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4316 0
39 Malta 1954 1 154400 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4636 0
40 Malta 1954 2 165400 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4355 0
41 Spain 1951 2 14522000 257000 1079000 0 0 0 1261000 1154000 1274000 1259000 1203000 1037000 976000 933000 845000 769000 653000 561000 433000 382000 244000 130000 71000 0 0 0 274255 14521000
42 Spain 1952 2 14634000 270000 1066000 0 0 0 1273000 1175000 1236000 1263000 1204000 1071000 977000 937000 859000 776000 675000 569000 448000 384000 246000 131000 72000 0 0 0 285701 14632000
43 Spain 1953 1 13834000 282000 1131000 0 0 0 1313000 1214000 1215000 1247000 1188000 1043000 889000 856000 800000 714000 593000 473000 364000 263000 149000 70000 31000 0 0 0 301302 13835000
44 Spain 1953 2 14746000 270000 1067000 0 0 0 1285000 1196000 1198000 1267000 1205000 1105000 978000 941000 873000 783000 697000 576000 463000 387000 248000 132000 73000 0 0 0 285160 14744000
45 Spain 1954 1 13959000 277000 1143000 0 0 0 1327000 1231000 1186000 1247000 1196000 1083000 887000 856000 809000 727000 613000 476000 378000 267000 154000 72000 32000 0 0 0 294586 13961000
46 Spain 1954 2 14858000 267000 1070000 0 0 0 1297000 1217000 1160000 1271000 1206000 1139000 979000 945000 887000 790000 719000 584000 478000 389000 250000 134000 73000 0 0 0 280646 14855000
47 Spain 1956 1 14208000 292000 1156000 0 0 0 1354000 1261000 1163000 1221000 1201000 1131000 922000 853000 817000 746000 642000 498000 396000 280000 164000 76000 34000 0 0 0 310182 14207000
48 Spain 1956 2 15097000 281000 1083000 0 0 0 1307000 1248000 1140000 1238000 1214000 1174000 1014000 951000 906000 814000 746000 611000 503000 398000 257000 136000 75000 0 0 0 295026 15096000
49 Spain 1957 1 14332000 311000 1158000 0 0 0 1364000 1278000 1169000 1195000 1197000 1140000 959000 850000 817000 754000 653000 516000 399000 287000 170000 79000 35000 0 0 0 329837 14331000
50 Spain 1957 2 15222000 299000 1091000 0 0 0 1304000 1260000 1160000 1202000 1221000 1174000 1048000 953000 910000 829000 751000 628000 511000 404000 261000 138000 76000 0 0 0 313480 15220000
51 Spain 1958 1 14456000 315000 1175000 0 0 0 1374000 1295000 1175000 1169000 1193000 1149000 996000 847000 816000 762000 664000 534000 402000 294000 175000 82000 36000 0 0 0 332960 14453000
52 Spain 1958 2 15347000 303000 1113000 0 0 0 1301000 1272000 1180000 1166000 1228000 1174000 1082000 955000 914000 844000 756000 645000 519000 410000 265000 140000 78000 0 0 0 317079 15345000
53 Spain 1959 1 14580000 317000 1194000 0 0 0 1384000 1312000 1181000 1143000 1189000 1158000 1033000 843000 816000 770000 675000 552000 406000 301000 180000 85000 37000 0 0 0 334373 14576000
54 Spain 1959 2 15472000 303000 1139000 0 0 0 1298000 1284000 1200000 1130000 1235000 1175000 1116000 956000 918000 859000 761000 662000 526000 416000 269000 143000 79000 0 0 0 316847 15469000
55 Spain 1961 1 14848000 318000 1237000 0 0 0 1406000 1348000 1196000 1092000 1182000 1178000 1109000 842000 817000 787000 698000 589000 413000 316000 191000 89000 39000 0 0 0 335266 14847000
56 Spain 1961 2 15744000 303000 1194000 0 0 0 1294000 1310000 1243000 1059000 1251000 1177000 1186000 959000 927000 890000 772000 697000 542000 431000 279000 148000 81000 0 0 0 316292 15743000
57 Spain 1962 1 15009000 322000 1258000 0 0 0 1419000 1368000 1205000 1069000 1180000 1190000 1148000 843000 818000 797000 711000 608000 417000 324000 197000 91000 39000 0 0 0 337300 15004000
58 Spain 1962 2 15908000 307000 1220000 0 0 0 1294000 1325000 1266000 1026000 1261000 1179000 1223000 963000 934000 907000 779000 716000 551000 438000 284000 152000 82000 0 0 0 318529 15907000
59 Yugoslavia, Former 1962 1 9224000 196000 193000 191000 191000 189000 1027000 953000 719000 799000 861000 815000 605000 413000 358000 475000 416000 323000 201000 134000 91000 46000 27000 0 0 0 213179 9223000


We can see that there are a few measures (59) for which there is a high discrepancy (>=1000 people) between the reported and the calculated total population. All those data points were collected from 1950 to 1964. So, we’ll exclude old and incomplete data and work with data from 1965. Also, we remove data where Sex column has an incorrect value and we change the sex coding to M and F

europe65<-filter(europe, Year>1964) 
europe65<-filter(europe65, Sex==1|Sex==2)
europe65$Sex<-ifelse(europe65$Sex==1, "M", "F")

Data manipulation

Even after those cleaning steps, there are still some columns with a lot of missing values; this is due to the fact that many countries sometimes used larger age groups:

apply(select(europe65, !c(name, Sex)),2,function(x) sum(as.numeric(x)==0))
##  Year  Pop1  Pop2  Pop3  Pop4  Pop5  Pop6  Pop7  Pop8  Pop9 Pop10 Pop11 Pop12 
##     0     0     0     0   664   664   664     0     0     0     0     0     0 
## Pop13 Pop14 Pop15 Pop16 Pop17 Pop18 Pop19 Pop20 Pop21 Pop22 Pop23 Pop24 Pop25 
##     0     0     0     0     0     0     0     0     0     0     0  2498  2498 
## Pop26    Lb   sum 
##  3562    56     0

Discarding all those observations would leave us with very few complete observation, so I decided to create larger age groups in my final dataset, as follows:

  • < 1 years old
  • 1-9 years old
  • 10-19 years old
  • 20-29 years old
  • 30-39 years old
  • 40-49 years old
  • 50-59 years old
  • 60-69 years old
  • 70-79 years old
  • older than 79 years old
  • Unspecified age

Other columns:

  • I decided to use the calculated “sum” column as the total population and the “Pop2” column as a metric for natality (as it has no missing data, while the “Live birth” column has some).
  • Natality rate is therefore calculated as \(\frac{Pop2}{sum}*100\).
  • Mean age is calculated considering the central age for each group (and 87 years old for the last group).
europe65_pop<- europe65 %>% mutate(Country=name, Year, Sex, Total_pop=sum, Age0=Pop2, "Natality_rate%"=round(Pop2/sum*100,2), "Age1-9"=Pop3+Pop4+Pop5+Pop6+Pop7, "Age10-19"=Pop8+Pop9, "Age20-29"=Pop10+Pop11, "Age30-39"=Pop12+Pop13, "Age40-49"=Pop14+Pop15, "Age50-59"=Pop16+Pop17, "Age60-69"=Pop18+Pop19, "Age70-79"=Pop20+Pop21, "Age80+"=Pop22+Pop23+Pop24+Pop25, Mean_age=round((.5*Pop2+5.5*`Age1-9`+15*`Age10-19`+25*`Age20-29`+35*`Age30-39`+45*`Age40-49`+55*`Age50-59`+65*`Age60-69`+75*`Age70-79`+87*`Age80+`)/sum,2), Age_unknown=Pop26, .keep="none")
print(xtable(head(europe65_pop,20)), type="html")
Country Year Sex Total_pop Age0 Natality_rate% Age1-9 Age10-19 Age20-29 Age30-39 Age40-49 Age50-59 Age60-69 Age70-79 Age80+ Mean_age Age_unknown
1 Albania 1985 M 1526200 40700 2.67 311300 314300 288400 213600 142400 111400 65200 29800 9100 26.82 0
2 Albania 1985 F 1431300 36700 2.56 283900 288000 269100 198000 128900 99600 71500 39100 16500 27.84 0
3 Albania 1986 M 1553300 41500 2.67 316800 319800 293600 217400 145000 113400 66300 30300 9200 26.82 0
4 Albania 1986 F 1462000 37500 2.56 290000 294200 274900 202200 131600 101800 73000 39900 16900 27.84 0
5 Albania 1987 M 1584200 42300 2.67 323100 326200 299500 221700 147900 115600 67600 30900 9400 26.82 0
6 Albania 1987 F 1492000 38300 2.57 296000 300300 280500 206300 134300 103900 74500 40700 17200 27.84 0
7 Albania 1988 M 1616000 43100 2.67 329600 332800 305500 226200 150800 117900 69000 31500 9600 26.82 0
8 Albania 1988 F 1522200 39100 2.57 301900 306300 286200 210500 137100 105900 76000 41600 17600 27.84 0
9 Albania 1989 M 1637800 43700 2.67 334100 337300 309600 229200 152900 119500 69900 31900 9700 26.81 0
10 Albania 1989 F 1544500 39600 2.56 306400 310800 290400 213600 139000 107500 77100 42200 17900 27.84 0
11 Albania 1990 M 1685700 40800 2.42 341400 337600 317200 246100 160100 125900 73000 34000 9600 27.15 0
12 Albania 1990 F 1600500 38100 2.38 315700 315800 298500 230600 146300 113000 79800 45100 17600 28.07 0
13 Albania 1991 M 1654100 40000 2.42 340200 330300 288800 243400 162800 128100 75600 35000 9900 27.42 0
14 Albania 1991 F 1605800 36800 2.29 315800 315700 291700 234100 149800 115700 81500 46400 18300 28.30 0
15 Albania 1992 M 1589500 39600 2.49 338400 316700 242700 234100 164100 129900 78000 35800 10200 27.72 0
16 Albania 1992 F 1600700 34300 2.14 317100 314000 280000 235300 152600 118000 83100 47500 18800 28.53 0
17 Albania 1993 M 1565900 35400 2.26 340800 310700 217800 233400 167400 132400 80800 36700 10500 28.03 0
18 Albania 1993 F 1601400 31400 1.96 318900 313000 270300 237900 156100 120700 84800 48900 19400 28.77 0
19 Albania 1994 M 1586200 37000 2.33 337900 312800 215300 241700 173200 135700 83900 37900 10800 28.31 0
20 Albania 1994 F 1615700 33300 2.06 316300 313800 266100 244200 161000 123800 86800 50400 20000 29.01 0


Here is a table summarising available data for each country and year:

t<-as.data.frame(pivot_wider(as.data.frame(table(europe65_pop$Country, europe65_pop$Year)), names_from = Var2, values_from = Freq))
row.names(t)<-t$Var1
t<-select(t, -Var1)

print(xtable(t), type="html")
1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
Albania 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0
Andorra 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 0
Austria 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Belarus 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 2 0
Belgium 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
Bosnia and Herzegovina 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 0 2 0 2 0 0 0
Bulgaria 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Croatia 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Czech Republic 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Czechoslovakia, Former 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Denmark 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Estonia 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
Finland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 2 0
France 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0
Germany 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Germany, Former Democratic Republic 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Germany, Former Federal Republic 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Greece 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Hungary 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Iceland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Ireland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 2 0 2 0 0 0 0
Italy 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Latvia 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Lithuania 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Luxembourg 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Malta 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Netherlands 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
North Macedonia 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0
Norway 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
Poland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Portugal 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Romania 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Russian Federation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 2
San Marino 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 0 0 0 2 0 0 2 2 2 2 2 2 2 2 2 2 2 2
Serbia 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Serbia and Montenegro, Former 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Slovakia 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0
Slovenia 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Spain 2 2 2 2 2 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 0 0
Sweden 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Switzerland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 2 2 2
Turkey 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 0 0 2
Ukraine 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 0 2 2 2
United Kingdom 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
USSR, Former 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Yugoslavia, Former 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


Managing changes in european political subdivision

In previous table, “0” means that there are no available data for that country/year, while “2” means there are available data (1 row for males and 1 row for females).

Since 1965, a few countries were founded and other disappeared. Thus, previous table was modified by replacing the 0 data with NA (standing for “Not applicable”) for countries that didn’t exist in a given year (thanks to Wikipedia). This way, we’ll be able to assess the completeness of our dataset for each year and to avoid some double-counted data.

t[c("Bosnia and Herzegovina", "Croatia", "North Macedonia", "Serbia", "Serbia and Montenegro, Former", "Slovenia"),which(t["Yugoslavia, Former",]=="2")]<-NA
t["Yugoslavia, Former",which(t["Yugoslavia, Former",]==0)]<-NA

t[c("Germany, Former Democratic Republic","Germany, Former Federal Republic"),which(t["Germany",]=="2")]<-NA
t["Germany",which(t["Germany",]==0)]<-NA

t[c("Czech Republic","Slovakia"),which(t["Czechoslovakia, Former",]=="2")]<-NA
t["Czechoslovakia, Former",which(t["Czechoslovakia, Former",]==0)]<-NA

t[c("Belarus", "Estonia", "Latvia", "Lithuania", "Ukraine", "Russian Federation"),1:26]<-NA
t["USSR, Former", 27:ncol(t)]<-NA
t["Serbia and Montenegro, Former", 34:ncol(t)]<-NA

av<-t #conversion in longer format to be used in Tableau
av$Countries<-row.names(av)
av<-select(av, c(56,1:55))
av<-pivot_longer(av, 2:56, names_to = "Year", values_to="available")
write_csv(t, "available.csv")

print(xtable(t), type="html", NA.string="NA")
1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
Albania 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0
Andorra 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 0
Austria 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Belarus NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 2 0
Belgium 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
Bosnia and Herzegovina NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 0 2 0 2 0 0 0
Bulgaria 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Croatia NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Czech Republic NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Czechoslovakia, Former 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Denmark 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Estonia NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
Finland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 2 0
France 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0
Germany NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Germany, Former Democratic Republic 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Germany, Former Federal Republic 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Greece 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Hungary 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Iceland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Ireland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 2 0 2 0 0 0 0
Italy 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Latvia NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Lithuania NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Luxembourg 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Malta 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Netherlands 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
North Macedonia NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0
Norway 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
Poland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Portugal 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Romania 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Russian Federation NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 2
San Marino 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 0 0 0 2 0 0 2 2 2 2 2 2 2 2 2 2 2 2
Serbia NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0
Serbia and Montenegro, Former NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 0 2 2 2 2 2 2 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Slovakia NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 0 0
Slovenia NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
Spain 2 2 2 2 2 0 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 0 0
Sweden 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0
Switzerland 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 2 2 2
Turkey 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 0 0 2
Ukraine NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 2 2 0 2 2 2
United Kingdom 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0
USSR, Former 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 2 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
Yugoslavia, Former 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA


Now, let’s create a vector to check which years have complete records:

apply(t, 2, function(x) all(x!=0, na.rm=T))
##  1965  1966  1967  1968  1969  1970  1971  1972  1973  1974  1975  1976  1977 
## FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 
##  1978  1979  1980  1981  1982  1983  1984  1985  1986  1987  1988  1989  1990 
## FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 
##  1991  1992  1993  1994  1995  1996  1997  1998  1999  2000  2001  2002  2003 
## FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 
##  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016 
## FALSE FALSE FALSE FALSE FALSE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE 
##  2017  2018  2019 
## FALSE FALSE FALSE

The only years with complete records are 2011 and 2012, so any time comparison between the aggregate country population is meaningless.

Here is a list of years with the respective number of countries with available, sorted from the one with least data to the one with more data:

sort(apply(t, 2, function(x) sum(x, na.rm=T)/2))
## 2019 2018 1970 1965 1966 1967 1968 1969 1971 1972 1973 1974 1975 1976 1977 1978 
##   12   23   25   26   26   26   26   26   26   26   26   26   26   26   26   26 
## 1979 1980 1981 1982 1983 1984 1990 2017 1985 1986 1987 1988 1989 2016 1991 2015 
##   26   26   27   27   27   27   27   27   28   28   28   28   28   31   35   35 
## 1992 1993 1994 2002 2003 2004 2006 2007 1995 1996 1997 1998 1999 2000 2001 2005 
##   36   36   36   36   36   36   36   36   37   37   37   37   37   37   37   37 
## 2008 2009 2013 2014 2010 2011 2012 
##   37   38   38   38   39   40   40

The final cleaning step will be to delete all the 202 rows containing double-counted population data (i.e. regarding countries and years we have set to NA in the summary table).

w<-which(is.na(t), arr.ind = T)
u<-matrix("",nrow(w), ncol(w))
u[,1]<-row.names(t)[w[,1]]
u[,2]<-colnames(t)[w[,2]]
d<-integer()
for(i in 1:nrow(u)) {
d<-c(d, which(europe65_pop$Country==u[i,1] & europe65_pop$Year==u[i,2]))
}
europe65_pop<-europe65_pop[-d,]

Analysis

Here you can see the most recent data ordered by increasing mean age.

dim(europe65_pop)

[1] 3414 17

print(xtable(head(arrange(europe65_pop, -Year, Mean_age),20)), type="html")
Country Year Sex Total_pop Age0 Natality_rate% Age1-9 Age10-19 Age20-29 Age30-39 Age40-49 Age50-59 Age60-69 Age70-79 Age80+ Mean_age Age_unknown
1 Turkey 2019 M 41430563 607078 1.47 5974737 6548412 6585176 6533974 5706625 4497548 2996990 1421862 558161 33.28 0
2 Turkey 2019 F 41148888 576613 1.40 5666615 6202816 6322318 6394569 5622215 4471970 3170765 1800481 920526 34.67 0
3 Russian Federation 2019 M 68109748 791898 1.16 8459941 7669504 8589124 12250101 9822145 8907586 7381408 2837181 1400860 37.47 0
4 Iceland 2019 M 184894 2256 1.22 20678 23157 29411 28221 24152 22186 18597 10912 5324 37.77 0
5 Iceland 2019 F 175679 2091 1.19 19697 22238 26500 24335 22136 21774 18299 11392 7217 38.84 0
6 Ukraine 2019 M 19399356 165710 0.85 2023742 2078170 2543279 3492509 2909622 2624355 2103252 954146 504571 38.95 0
7 Lithuania 2019 M 1299973 14214 1.09 135235 135700 178337 184899 183627 197155 145690 81305 43811 40.16 0
8 Hungary 2019 M 4678311 47666 1.02 427972 504927 610185 656429 799937 596661 576337 329500 128697 40.69 0
9 Czech Republic 2019 M 5256864 57824 1.10 524092 533052 591234 773385 895629 672166 637748 424376 147358 41.11 0
10 Switzerland 2019 M 4252998 43701 1.03 403250 433922 536466 617201 604108 649077 461946 332329 170998 41.49 0
11 Austria 2019 M 4367291 42855 0.98 398856 442300 577795 613181 596487 696011 481896 348741 169169 41.69 0
12 Slovenia 2019 M 1015910 10115 1.00 98446 99363 103230 143464 154759 151369 138396 78856 37912 42.46 0
13 Russian Federation 2019 F 78654907 745610 0.95 8005474 7321603 8236976 12294569 10613632 10652288 10944000 5716065 4124690 42.63 0
14 Germany 2019 M 41002158 399821 0.98 3521593 3965564 5081714 5475392 5185525 6761029 5039670 3477404 2094446 43.25 0
15 Switzerland 2019 F 4322295 41633 0.96 382032 408793 512577 604986 597272 638281 477797 381258 277666 43.50 0
16 San Marino 2019 M 16520 106 0.64 1415 1787 1631 1831 2716 2876 1947 1380 831 43.72 0
17 Czech Republic 2019 F 5412460 55139 1.02 498528 505357 559353 724807 847494 657350 706658 568120 289654 43.92 0
18 Austria 2019 F 4510346 40798 0.90 375371 417640 547703 597552 600473 694286 520342 427596 288585 44.16 0
19 Ukraine 2019 F 22458816 155032 0.69 1902484 1960901 2411803 3427957 3095660 3164294 3096672 1918002 1326011 44.26 0
20 Hungary 2019 F 5092838 44981 0.88 405475 477878 571411 626302 782024 630078 727297 520177 307215 44.86 0


The final dataset was converted in a longer format and then used to create a Tableau dashboard showing the main insights and trends.

We also set up a small dataframe (following table) that will be useful for our analysis in Tableau, containing for each country the oldest and the latest summarized data, including the percentage of females:

europe65_pop_long<-pivot_longer(europe65_pop[,-4], c(4,6:14, 16), names_to = "age", values_to = "Total_pop")

percF<-europe65_pop_long %>% group_by(Country, Year,Sex) %>% summarise(tot_by_sex=sum(Total_pop)) %>% summarize(percF=tot_by_sex[1]/sum(tot_by_sex))
europe65_pop_long<- inner_join(europe65_pop_long, percF)

old<-europe65_pop_long %>% group_by(Country) %>% filter(Year==min(Year)) %>%
summarise(Natality=mean(`Natality_rate%`), Mean_age=mean(Mean_age),
population=sum(Total_pop), year=mean(Year), percF=mean(percF), time="old")

latest<-europe65_pop_long %>% group_by(Country) %>% filter(Year==max(Year)) %>%
summarise(Natality=mean(`Natality_rate%`), Mean_age=mean(Mean_age),
population=sum(Total_pop), year=mean(Year), percF=mean(percF), time="recent")

variation<-rbind(old, latest)
variation<-variation %>% group_by(Country) %>% summarize(Natality=Natality, Mean_age=Mean_age, 
                                              population=population, year=year, 
                                              time=time, percF=percF, diff=max(year)-min(year))

write.csv(variation, "variation.csv")
print(xtable(variation), type="html")
Country Natality Mean_age population year time percF diff
1 Albania 2.62 27.33 2957500 1985.00 old 0.48 30.00
2 Albania 1.16 37.78 2889173 2015.00 recent 0.49 30.00
3 Andorra 0.75 40.61 85015 2010.00 old 0.48 8.00
4 Andorra 0.47 42.11 80253 2018.00 recent 0.49 8.00
5 Austria 1.78 36.35 7254800 1965.00 old 0.53 54.00
6 Austria 0.94 42.92 8877637 2019.00 recent 0.51 54.00
7 Belarus 1.29 35.39 10232800 1991.00 old 0.53 27.00
8 Belarus 1.05 40.31 9483499 2018.00 recent 0.53 27.00
9 Belgium 1.66 36.04 9448000 1965.00 old 0.51 51.00
10 Belgium 1.08 41.47 11352259 2016.00 recent 0.51 51.00
11 Bosnia and Herzegovina 1.41 32.10 4518400 1991.00 old 0.50 25.00
12 Bosnia and Herzegovina 0.94 40.08 3511372 2016.00 recent 0.51 25.00
13 Bulgaria 1.58 33.76 8201500 1965.00 old 0.50 53.00
14 Bulgaria 0.90 43.82 7025048 2018.00 recent 0.51 53.00
15 Croatia 1.14 37.22 4786000 1991.00 old 0.52 26.00
16 Croatia 0.90 43.15 4124531 2017.00 recent 0.52 26.00
17 Czech Republic 1.21 36.59 10317900 1992.00 old 0.51 27.00
18 Czech Republic 1.06 42.52 10669324 2019.00 recent 0.51 27.00
19 Czechoslovakia, Former 1.64 34.10 14158700 1965.00 old 0.51 26.00
20 Czechoslovakia, Former 1.32 35.51 15592000 1991.00 recent 0.51 26.00
21 Denmark 1.75 35.05 4758100 1965.00 old 0.50 53.00
22 Denmark 1.08 41.69 5788917 2018.00 recent 0.50 53.00
23 Estonia 1.32 36.14 1561314 1991.00 old 0.53 25.00
24 Estonia 1.07 41.92 1315776 2016.00 recent 0.53 25.00
25 Finland 1.70 32.01 4563700 1965.00 old 0.52 53.00
26 Finland 0.89 42.89 5515536 2018.00 recent 0.51 53.00
27 France 1.77 34.89 48757800 1965.00 old 0.51 49.00
28 France 1.18 41.05 64129491 2014.00 recent 0.52 49.00
29 Germany 1.11 39.37 79364400 1990.00 old 0.52 29.00
30 Germany 0.94 44.57 83092974 2019.00 recent 0.51 29.00
31 Germany, Former Democratic Republic 1.66 37.03 17019600 1965.00 old 0.54 24.00
32 Germany, Former Democratic Republic 1.25 37.25 16629800 1989.00 recent 0.52 24.00
33 Germany, Former Federal Republic 1.75 36.06 59011700 1965.00 old 0.52 24.00
34 Germany, Former Federal Republic 1.10 39.73 62062600 1989.00 recent 0.52 24.00
35 Greece 1.79 33.04 8550200 1965.00 old 0.51 53.00
36 Greece 0.82 44.28 10732898 2018.00 recent 0.51 53.00
37 Hungary 1.27 35.13 10147900 1965.00 old 0.52 54.00
38 Hungary 0.95 42.77 9771149 2019.00 recent 0.52 54.00
39 Iceland 2.45 29.54 192200 1965.00 old 0.49 54.00
40 Iceland 1.21 38.31 360573 2019.00 recent 0.49 54.00
41 Ireland 2.16 32.66 2875800 1965.00 old 0.50 50.00
42 Ireland 1.37 37.19 4687787 2015.00 recent 0.51 50.00
43 Italy 1.90 34.09 51987200 1965.00 old 0.51 52.00
44 Italy 0.76 45.08 60536721 2017.00 recent 0.51 52.00
45 Latvia 1.35 36.52 2662500 1991.00 old 0.53 27.00
46 Latvia 1.04 42.81 1927185 2018.00 recent 0.54 27.00
47 Lithuania 1.52 35.28 3704146 1991.00 old 0.53 28.00
48 Lithuania 1.00 43.05 2794137 2019.00 recent 0.53 28.00
49 Luxembourg 1.50 36.34 333900 1965.00 old 0.50 53.00
50 Luxembourg 1.03 40.02 607938 2018.00 recent 0.50 53.00
51 Malta 1.82 29.54 318800 1965.00 old 0.52 52.00
52 Malta 0.97 41.89 468056 2017.00 recent 0.50 52.00
53 Netherlands 1.99 32.25 12294600 1965.00 old 0.50 53.00
54 Netherlands 0.98 42.27 17316419 2018.00 recent 0.50 53.00
55 North Macedonia 1.67 32.39 1915500 1991.00 old 0.50 23.00
56 North Macedonia 1.12 38.34 2067471 2014.00 recent 0.50 23.00
57 Norway 1.75 35.39 3722900 1965.00 old 0.50 51.00
58 Norway 1.14 39.88 5236138 2016.00 recent 0.50 51.00
59 Poland 1.75 30.52 31495900 1965.00 old 0.51 53.00
60 Poland 0.98 41.83 37948140 2018.00 recent 0.52 53.00
61 Portugal 2.17 31.48 9116200 1965.00 old 0.52 53.00
62 Portugal 0.84 44.33 10283810 2018.00 recent 0.53 53.00
63 Romania 1.45 32.41 19027400 1965.00 old 0.51 53.00
64 Romania 1.03 42.06 19476713 2018.00 recent 0.51 53.00
65 Russian Federation 1.27 35.30 148244900 1991.00 old 0.53 28.00
66 Russian Federation 1.05 40.05 146764655 2019.00 recent 0.54 28.00
67 San Marino 0.97 39.50 25058 1995.00 old 0.51 24.00
68 San Marino 0.64 44.63 33477 2019.00 recent 0.51 24.00
69 Serbia 0.98 39.47 7567745 1998.00 old 0.51 19.00
70 Serbia 0.92 43.11 7020858 2017.00 recent 0.51 19.00
71 Serbia and Montenegro, Former 1.36 35.63 10448018 1992.00 old 0.50 5.00
72 Serbia and Montenegro, Former 1.27 36.50 10600067 1997.00 recent 0.50 5.00
73 Slovakia 1.46 34.56 5306539 1992.00 old 0.51 22.00
74 Slovakia 1.02 39.81 5418636 2014.00 recent 0.51 22.00
75 Slovenia 1.07 36.25 1999100 1991.00 old 0.51 28.00
76 Slovenia 0.95 43.87 2055166 2019.00 recent 0.51 28.00
77 Spain 2.04 32.41 31948100 1965.00 old 0.52 52.00
78 Spain 0.85 43.15 46532870 2017.00 recent 0.51 52.00
79 Sweden 1.57 36.97 7733900 1965.00 old 0.50 53.00
80 Sweden 1.14 41.31 10175201 2018.00 recent 0.50 53.00
81 Switzerland 1.88 34.24 5857300 1965.00 old 0.51 54.00
82 Switzerland 0.99 42.50 8575293 2019.00 recent 0.50 54.00
83 Turkey 1.64 31.29 72039196 2009.00 old 0.50 10.00
84 Turkey 1.44 33.98 82579451 2019.00 recent 0.50 10.00
85 Ukraine 1.25 36.75 51745600 1991.00 old 0.54 28.00
86 Ukraine 0.77 41.61 41858172 2019.00 recent 0.54 28.00
87 United Kingdom 1.81 36.00 54441700 1965.00 old 0.51 51.00
88 United Kingdom 1.19 40.55 65648054 2016.00 recent 0.51 51.00
89 USSR, Former 1.82 32.95 267110600 1981.00 old 0.53 9.00
90 USSR, Former 1.70 33.44 288265800 1990.00 recent 0.53 9.00
91 Yugoslavia, Former 1.96 30.45 19484700 1965.00 old 0.51 25.00
92 Yugoslavia, Former 1.38 34.73 23817900 1990.00 recent 0.51 25.00


Finally, average variation rates for mean age and natality were calculated and added to the dataframe:

slope_age<-variation %>% group_by(Country) %>% arrange(year) %>% summarize(slope_age=(Mean_age[2]-Mean_age[1])/mean(diff))
slope_natality<-variation %>% group_by(Country) %>% arrange(year) %>% summarize(slope_natality=(Natality[2]-Natality[1])/mean(diff))
europe65_pop_long<-europe65_pop_long %>% inner_join(slope_age) %>% inner_join(slope_natality)
write.csv(europe65_pop_long, "europe65_population_long.csv")

You can find the dashboard here

And here is a presentation in Google Slides

Technical details

sessionInfo()
## R version 4.0.2 (2020-06-22)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 19042)
## 
## Matrix products: default
## 
## locale:
## [1] LC_COLLATE=English_United States.1252 
## [2] LC_CTYPE=English_United States.1252   
## [3] LC_MONETARY=English_United States.1252
## [4] LC_NUMERIC=C                          
## [5] LC_TIME=English_United States.1252    
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
##  [1] xtable_1.8-4    forcats_0.5.1   stringr_1.4.0   dplyr_1.0.7    
##  [5] purrr_0.3.4     readr_1.4.0     tidyr_1.1.4     tibble_3.1.0   
##  [9] ggplot2_3.3.3   tidyverse_1.3.1
## 
## loaded via a namespace (and not attached):
##  [1] tidyselect_1.1.0  xfun_0.23         bslib_0.2.4       haven_2.3.1      
##  [5] colorspace_2.0-0  vctrs_0.3.8       generics_0.1.0    htmltools_0.5.1.1
##  [9] yaml_2.2.1        utf8_1.2.1        rlang_0.4.12      jquerylib_0.1.3  
## [13] pillar_1.6.4      glue_1.4.2        withr_2.4.1       DBI_1.1.1        
## [17] dbplyr_2.1.1      modelr_0.1.8      readxl_1.3.1      lifecycle_1.0.0  
## [21] munsell_0.5.0     gtable_0.3.0      cellranger_1.1.0  rvest_1.0.0      
## [25] evaluate_0.14     knitr_1.31        fansi_0.4.2       broom_0.7.11     
## [29] Rcpp_1.0.6        backports_1.2.1   scales_1.1.1      jsonlite_1.7.2   
## [33] fs_1.5.0          hms_1.0.0         digest_0.6.29     stringi_1.5.3    
## [37] grid_4.0.2        cli_3.1.0         tools_4.0.2       magrittr_2.0.1   
## [41] sass_0.3.1        crayon_1.4.2      pkgconfig_2.0.3   ellipsis_0.3.2   
## [45] xml2_1.3.2        reprex_2.0.1      lubridate_1.8.0   rstudioapi_0.13  
## [49] assertthat_0.2.1  rmarkdown_2.7     httr_1.4.2        R6_2.5.0         
## [53] compiler_4.0.2

Notes

Data from Cyprus were discarded because incomplete
Data from Liechtenstein, Vatican City, Monaco and Moldova were not present in the original dataset