library(readxl)
library(dplyr)
Attaching package: 'dplyr'The following objects are masked from 'package:stats':
filter, lagThe following objects are masked from 'package:base':
intersect, setdiff, setequal, unionlibrary(tidyr)
library(zoo)
Attaching package: 'zoo'The following objects are masked from 'package:base':
as.Date, as.Date.numericdata_literate <- read_xls("Population_by_literacy_and_gender.xls", col_names = TRUE, range = "A4:Q27")New names:
• `` -> `...1`
• `` -> `...2`data_literate_t <- t(data_literate)
data_literate_percentage <- data.frame(data_literate_t[-c(1,2), c(16,17)])
data_literate_end <- tibble::rownames_to_column(data_literate_percentage, "Year")
colnames(data_literate_end)[2:3] <- c("Literate(%)", "Illiterate(%)")
data_population <- read_xls("Population.xls", col_names = TRUE, range = "A4:AA45")
data_population_clean <- data_population[-c(1,3,4,5,21,22,24,25,26), -c(2:12)]
data_population_clean_t <- t(data_population_clean)
colnames(data_population_clean_t) <- data_population_clean_t[1,1:32]
data_population_clean_t <- data_population_clean_t[-1,]
data_population_clean_t_sum <- data_population_clean_t
for (i in 1:16){
data_population_clean_t_sum[,i] <- as.numeric(data_population_clean_t[,i]) + as.numeric(data_population_clean_t[,(i+16)])
}
data_population_clean_t_sum <- data_population_clean_t_sum[,-c(17:32)]
data_population_clean_t_sum[,"15-19"] <- round(as.numeric(data_population_clean_t_sum[,"15-19"]) * 2 / 5)
colnames(data_population_clean_t_sum)[colnames(data_population_clean_t_sum) == "15-19"] <- "18-19"
data_population_by_age <- data.frame(data_population_clean_t_sum)
data_population_by_age <- data_population_by_age |> mutate("18-24" = as.numeric(data_population_by_age[,2]) + as.numeric(data_population_by_age[,3])) |>
mutate("25-34" = as.numeric(data_population_by_age[,4]) + as.numeric(data_population_by_age[,5])) |>
mutate("35-44" = as.numeric(data_population_by_age[,6]) + as.numeric(data_population_by_age[,7])) |>
mutate("45-54" = as.numeric(data_population_by_age[,8]) + as.numeric(data_population_by_age[,9])) |>
mutate("55-64" = as.numeric(data_population_by_age[,10]) + as.numeric(data_population_by_age[,11])) |>
mutate("65+" = as.numeric(data_population_by_age[,12]) + as.numeric(data_population_by_age[,13]) + as.numeric(data_population_by_age[,14]) + as.numeric(data_population_by_age[,15]) + as.numeric(data_population_by_age[,16]))
data_population_by_age <- data_population_by_age[,-c(2:16)]
column_names <- c("Pop(Total)", "Pop(18-24)", "Pop(25-34)", "Pop(35-44)", "Pop(45-54)", "Pop(55-64)", "Pop(65+)")
colnames(data_population_by_age) <- column_names
data_population_end <- tibble::rownames_to_column(data_population_by_age, "Year")
data_happiness <- read_xls("Happiness-Age.xls", col_names = TRUE, range = "A5:H85")New names:
• `` -> `...2`data_happiness_no_Na <- data_happiness[-c(1:21, 25, 29, 33, 37, 41, 45, 49, 53, 57, 61, 65, 69, 73, 77),]
data_happiness_no_Na <- na.locf(data_happiness_no_Na, fromLast = FALSE)
data_happiness_no_Na_wider <- data_happiness_no_Na |> gather(key = "Age", value = "Percentage", 3,4,5,6,7,8) |> pivot_wider(names_from = c(2, "Age"), values_from = "Percentage")
column_names_happiness <- as.character(c("Year", "Happy(18-24)(%)", "Neutral(18-24)(%)", "Unhappy(18-24)(%)", "Happy(25-34)(%)", "Neutral(25-34)(%)", "Unhappy(25-34)(%)", "Happy(35-44)(%)", "Neutral(35-44)(%)", "Unhappy(35-44)(%)", "Happy(45-54)(%)", "Neutral(45-54)(%)", "Unhappy(45-54)(%)", "Happy(55-64)(%)", "Neutral(55-64)(%)", "Unhappy(55-64)(%)", "Happy(65+)(%)", "Neutral(65+)(%)", "Unhappy(65+)(%)"))
colnames(data_happiness_no_Na_wider) <- column_names_happiness
data_happiness_end <- data_happiness_no_Na_wider
data_happiness_end$Year <- as.character(data_happiness_end$Year)literate_population <- left_join(data_literate_end, data_population_end, by = "Year")
literate_population_happiness <- left_join(literate_population, data_happiness_end, by = "Year")
final_data <- literate_population_happiness |> relocate("Pop(Total)", .after = 28)|>
relocate("Pop(18-24)", .before = "Happy(18-24)(%)") |>
relocate("Pop(25-34)", .before = "Happy(25-34)(%)") |>
relocate("Pop(35-44)", .before = "Happy(35-44)(%)") |>
relocate("Pop(45-54)", .before = "Happy(45-54)(%)") |>
relocate("Pop(55-64)", .before = "Happy(55-64)(%)") |>
relocate("Pop(65+)", .before = "Happy(65+)(%)")
final_data Year Literate(%) Illiterate(%) Pop(18-24) Happy(18-24)(%)
1 2008 90.007406587375414 9.9925934126245828 8730600 59.09000
2 2009 9.081672e+01 9.183279e+00 8773965 57.41000
3 2010 9.266060e+01 7.339400e+00 8778710 63.93000
4 2011 9.410609e+01 5.893909e+00 8751624 69.50000
5 2012 9.491975e+01 5.080252e+00 8748310 64.60000
6 2013 9.525666e+01 4.743344e+00 8805113 65.09000
7 2014 9.543963e+01 4.560366e+00 8871028 61.20000
8 2015 9.560142e+01 4.398581e+00 8948367 63.78700
9 2016 9.587569e+01 4.124307e+00 9015051 65.13400
10 2017 9.615053e+01 3.849470e+00 9067145 61.30150
11 2018 9.641868e+01 3.581320e+00 9116836 55.35172
12 2019 9.674221e+01 3.257794e+00 9156716 56.67982
13 2020 9.695450e+01 3.045501e+00 9155682 47.14917
14 2021 9.714514e+01 2.854856e+00 9233464 44.48447
15 2022 9.731440e+01 2.685604e+00 9160752 47.90992
Neutral(18-24)(%) Unhappy(18-24)(%) Pop(25-34) Happy(25-34)(%)
1 28.30000 12.600000 12328944 55.69000
2 31.45000 11.140000 12419892 55.92000
3 26.49000 9.580000 12647889 64.36000
4 25.00000 5.400000 12801867 62.00000
5 26.00000 9.400000 12815605 65.60000
6 26.82000 8.080000 12830001 60.29000
7 30.40000 8.400000 12789496 57.90000
8 28.41500 7.798000 12691399 58.60600
9 26.30100 8.565000 12556452 61.96100
10 29.90471 8.793788 12528466 58.57135
11 35.13875 9.509531 12609622 53.24284
12 33.75431 9.565878 12730328 52.04614
13 38.63200 14.218833 12689848 46.81024
14 35.10865 20.406887 12818686 46.57319
15 36.41563 15.674453 12868216 46.76606
Neutral(25-34)(%) Unhappy(25-34)(%) Pop(35-44) Happy(35-44)(%)
1 31.81000 12.50000 10070734 52.90000
2 31.40000 12.68000 10181458 53.49000
3 26.32000 9.32000 10160840 57.49000
4 28.10000 9.90000 10403516 60.70000
5 27.30000 7.10000 10795237 58.50000
6 30.89000 8.81000 11134409 55.13000
7 32.90000 9.20000 11428673 52.10000
8 31.88100 9.51300 11755903 54.17800
9 28.70200 9.33800 12128650 58.22800
10 31.93865 9.49000 12301515 55.85570
11 36.39018 10.36698 12422098 50.74637
12 34.27075 13.68311 12552227 50.64638
13 38.13035 15.05941 12708693 45.37726
14 36.35105 17.07576 12933370 50.22658
15 36.63361 16.60033 12972770 47.93897
Neutral(35-44)(%) Unhappy(35-44)(%) Pop(45-54) Happy(45-54)(%)
1 32.30000 14.80000 7927348 51.74000
2 33.20000 13.31000 8195696 51.31000
3 31.18000 11.34000 8406580 58.93000
4 30.30000 9.10000 8578520 59.70000
5 30.20000 11.30000 8738619 55.50000
6 33.76000 11.11000 8954555 54.49000
7 36.00000 11.90000 9112684 51.50000
8 33.98600 11.83500 9222988 51.66800
9 30.84700 10.92400 9504758 58.34400
10 33.27907 10.86522 9730609 53.08768
11 36.92331 12.33032 10012031 47.84929
12 37.16409 12.18953 10208932 49.70393
13 39.44927 15.17347 10148298 46.22153
14 33.48964 16.28378 10416745 48.75430
15 35.71641 16.34462 10818158 47.96122
Neutral(45-54)(%) Unhappy(45-54)(%) Pop(55-64) Happy(55-64)(%)
1 32.54000 15.71000 5066402 56.93000
2 30.96000 17.73000 5306781 51.99000
3 29.23000 11.84000 5756267 61.28000
4 28.60000 11.70000 6020902 62.30000
5 32.10000 12.40000 6147100 59.30000
6 33.16000 12.35000 6315645 58.15000
7 32.70000 15.80000 6527278 54.80000
8 34.67000 13.66200 6789637 55.07000
9 30.74600 10.91000 7058684 62.30000
10 32.99353 13.91879 7337965 56.21379
11 36.42783 15.72288 7618202 55.65419
12 35.67746 14.61861 7944766 48.67123
13 38.46309 15.31538 8151981 48.99747
14 33.92495 17.32076 8319096 50.91547
15 35.72690 16.31188 8483812 52.49462
Neutral(55-64)(%) Unhappy(55-64)(%) Pop(65+) Happy(65+)(%) Neutral(65+)(%)
1 28.40000 14.67000 4893423 61.60000 24.36000
2 29.22000 18.79000 5083414 54.27000 27.79000
3 27.82000 10.90000 5327736 60.03000 27.06000
4 26.40000 11.30000 5490715 57.80000 28.60000
5 29.00000 11.70000 5682003 60.30000 28.70000
6 28.88000 12.97000 5891694 63.36000 23.62000
7 31.30000 14.00000 6192962 62.80000 24.60000
8 30.70100 14.22900 6495239 56.84000 30.74000
9 24.94200 12.75800 6651503 64.47600 25.03100
10 31.01922 12.76699 6895385 66.09250 22.94151
11 31.75922 12.58659 7186204 61.19950 26.75070
12 35.50906 15.81971 7550727 58.54024 29.23618
13 36.50081 14.50173 7953555 57.74405 30.43805
14 33.71747 15.36706 8245124 56.16328 30.82194
15 31.68412 15.82126 8451669 57.72237 28.60509
Unhappy(65+)(%) Pop(Total)
1 14.03000 71517100
2 17.94000 72561312
3 12.92000 73722988
4 13.50000 74724269
5 11.10000 75627384
6 13.01000 76667864
7 12.60000 77695904
8 12.42000 78741053
9 10.49400 79814871
10 10.96599 80810525
11 12.04981 82003882
12 12.22358 83154997
13 11.81790 83614362
14 13.01478 84680273
15 13.67254 85279553