# Load necessary libraries
library(tidyverse)
## Warning: package 'tidyverse' was built under R version 4.3.2
## Warning: package 'lubridate' was built under R version 4.3.2
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.3     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.0
## ✔ ggplot2   3.4.4     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.0
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(readr)
library(dplyr)

# Mengambil datasets
Global_Country_Information_Dataset_2023_exported <- read_csv("C:/Users/NHQEN/Downloads/Global Country Information Dataset 2023_exported.csv")
## Rows: 195 Columns: 35
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (19): Country, Abbreviation, Agricultural Land( %), Capital/Major City, ...
## dbl (11): Density
## (P/Km2), Birth Rate, Calling Code, CPI, Fertility Rate, In...
## num  (5): Land Area(Km2), Armed Forces size, Co2-Emissions, Population, Urba...
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# 1. Menghitung missing value 
missing_values_count <- Global_Country_Information_Dataset_2023_exported %>%
  summarise_all(~ sum(is.na(.)))

# 2. Clean data
cleaned_data <- Global_Country_Information_Dataset_2023_exported %>%
  drop_na()

# 3. Mengubah tipe data char (persentase dan dolar) ke tipe data dbl
df <- cleaned_data %>%
  mutate(across(where(~ any(grepl("[\\$%,]", .))),
                ~ ifelse(grepl("[\\$%,]", .), as.numeric(gsub("[\\$%,]", "", .)), .)))
## Warning: There were 2 warnings in `mutate()`.
## The first warning was:
## ℹ In argument: `across(...)`.
## Caused by warning in `ifelse()`:
## ! NAs introduced by coercion
## ℹ Run `dplyr::last_dplyr_warnings()` to see the 1 remaining warning.
glimpse(df)
## Rows: 111
## Columns: 35
## $ Country                                     <chr> "Afghanistan", "Albania", …
## $ `Density\n(P/Km2)`                          <dbl> 60, 105, 18, 26, 17, 104, …
## $ Abbreviation                                <chr> "AF", "AL", "DZ", "AO", "A…
## $ `Agricultural Land( %)`                     <dbl> 58.1, 43.1, 17.4, 47.5, 54…
## $ `Land Area(Km2)`                            <dbl> 652230, 28748, 2381741, 12…
## $ `Armed Forces size`                         <dbl> 323000, 9000, 317000, 1170…
## $ `Birth Rate`                                <dbl> 32.49, 11.78, 24.28, 40.73…
## $ `Calling Code`                              <dbl> 93, 355, 213, 244, 54, 374…
## $ `Capital/Major City`                        <chr> "Kabul", "Tirana", "Algier…
## $ `Co2-Emissions`                             <dbl> 8672, 4536, 150006, 34693,…
## $ CPI                                         <dbl> 149.90, 119.05, 151.36, 26…
## $ `CPI Change (%)`                            <dbl> 2.3, 1.4, 2.0, 17.1, 53.5,…
## $ `Currency-Code`                             <chr> "AFN", "ALL", "DZD", "AOA"…
## $ `Fertility Rate`                            <dbl> 4.47, 1.62, 3.02, 5.52, 2.…
## $ `Forested Area (%)`                         <dbl> 2.1, 28.1, 0.8, 46.3, 9.8,…
## $ `Gasoline Price`                            <dbl> 0.70, 1.36, 0.28, 0.97, 1.…
## $ GDP                                         <dbl> 1.910135e+10, 1.527808e+10…
## $ `Gross primary education enrollment (%)`    <dbl> 104.0, 107.0, 109.9, 113.5…
## $ `Gross tertiary education enrollment (%)`   <dbl> 9.7, 55.0, 51.4, 9.3, 90.0…
## $ `Infant mortality`                          <dbl> 47.9, 7.8, 20.1, 51.6, 8.8…
## $ `Largest city`                              <chr> "Kabul", "Tirana", "Algier…
## $ `Life expectancy`                           <dbl> 64.5, 78.5, 76.7, 60.8, 76…
## $ `Maternal mortality ratio`                  <dbl> 638, 15, 112, 241, 39, 26,…
## $ `Minimum wage`                              <dbl> 0.43, 1.12, 0.95, 0.71, 3.…
## $ `Official language`                         <chr> "Pashto", "Albanian", "Ara…
## $ `Out of pocket health expenditure`          <dbl> 78.4, 56.9, 28.1, 33.4, 17…
## $ `Physicians per thousand`                   <dbl> 0.28, 1.20, 1.72, 0.21, 3.…
## $ Population                                  <dbl> 38041754, 2854191, 4305305…
## $ `Population: Labor force participation (%)` <dbl> 48.9, 55.7, 41.2, 77.5, 61…
## $ `Tax revenue (%)`                           <dbl> 9.3, 18.6, 37.2, 9.2, 10.1…
## $ `Total tax rate`                            <dbl> 71.4, 36.6, 66.1, 49.1, 10…
## $ `Unemployment rate`                         <dbl> 11.12, 12.33, 11.70, 6.89,…
## $ Urban_population                            <dbl> 9797273, 1747593, 31510100…
## $ Latitude                                    <dbl> 33.939110, 41.153332, 28.0…
## $ Longitude                                   <dbl> 67.709953, 20.168331, 1.65…
# 4. Konversi nilai mata uang dolar US ke nilai mata uang Rupiah
kurs_dollar_to_rupiah <- 15000

# Mengubah nilai mata uang Dollar ke Rupiah untuk tiga kolom yang berisi dolar
df_rupiah <- df %>%
  mutate(
    `Gasoline Price` = `Gasoline Price` * kurs_dollar_to_rupiah,
    `GDP` = `GDP` * kurs_dollar_to_rupiah,
    `Minimum wage` = `Minimum wage` * kurs_dollar_to_rupiah
  )

glimpse(df_rupiah)
## Rows: 111
## Columns: 35
## $ Country                                     <chr> "Afghanistan", "Albania", …
## $ `Density\n(P/Km2)`                          <dbl> 60, 105, 18, 26, 17, 104, …
## $ Abbreviation                                <chr> "AF", "AL", "DZ", "AO", "A…
## $ `Agricultural Land( %)`                     <dbl> 58.1, 43.1, 17.4, 47.5, 54…
## $ `Land Area(Km2)`                            <dbl> 652230, 28748, 2381741, 12…
## $ `Armed Forces size`                         <dbl> 323000, 9000, 317000, 1170…
## $ `Birth Rate`                                <dbl> 32.49, 11.78, 24.28, 40.73…
## $ `Calling Code`                              <dbl> 93, 355, 213, 244, 54, 374…
## $ `Capital/Major City`                        <chr> "Kabul", "Tirana", "Algier…
## $ `Co2-Emissions`                             <dbl> 8672, 4536, 150006, 34693,…
## $ CPI                                         <dbl> 149.90, 119.05, 151.36, 26…
## $ `CPI Change (%)`                            <dbl> 2.3, 1.4, 2.0, 17.1, 53.5,…
## $ `Currency-Code`                             <chr> "AFN", "ALL", "DZD", "AOA"…
## $ `Fertility Rate`                            <dbl> 4.47, 1.62, 3.02, 5.52, 2.…
## $ `Forested Area (%)`                         <dbl> 2.1, 28.1, 0.8, 46.3, 9.8,…
## $ `Gasoline Price`                            <dbl> 10500, 20400, 4200, 14550,…
## $ GDP                                         <dbl> 2.865203e+14, 2.291712e+14…
## $ `Gross primary education enrollment (%)`    <dbl> 104.0, 107.0, 109.9, 113.5…
## $ `Gross tertiary education enrollment (%)`   <dbl> 9.7, 55.0, 51.4, 9.3, 90.0…
## $ `Infant mortality`                          <dbl> 47.9, 7.8, 20.1, 51.6, 8.8…
## $ `Largest city`                              <chr> "Kabul", "Tirana", "Algier…
## $ `Life expectancy`                           <dbl> 64.5, 78.5, 76.7, 60.8, 76…
## $ `Maternal mortality ratio`                  <dbl> 638, 15, 112, 241, 39, 26,…
## $ `Minimum wage`                              <dbl> 6450, 16800, 14250, 10650,…
## $ `Official language`                         <chr> "Pashto", "Albanian", "Ara…
## $ `Out of pocket health expenditure`          <dbl> 78.4, 56.9, 28.1, 33.4, 17…
## $ `Physicians per thousand`                   <dbl> 0.28, 1.20, 1.72, 0.21, 3.…
## $ Population                                  <dbl> 38041754, 2854191, 4305305…
## $ `Population: Labor force participation (%)` <dbl> 48.9, 55.7, 41.2, 77.5, 61…
## $ `Tax revenue (%)`                           <dbl> 9.3, 18.6, 37.2, 9.2, 10.1…
## $ `Total tax rate`                            <dbl> 71.4, 36.6, 66.1, 49.1, 10…
## $ `Unemployment rate`                         <dbl> 11.12, 12.33, 11.70, 6.89,…
## $ Urban_population                            <dbl> 9797273, 1747593, 31510100…
## $ Latitude                                    <dbl> 33.939110, 41.153332, 28.0…
## $ Longitude                                   <dbl> 67.709953, 20.168331, 1.65…
# 5. Buatlah kolom country status yang berisi kategori "rich", "developing", dan "poor"
konversi <- df %>%
  select('Unemployment rate', GDP, CPI) %>%
  mutate(
    country_status = case_when(
      `Unemployment rate` < 1 ~ "rich",
      `Unemployment rate` > 0.999999 & `Unemployment rate` < 5 ~ "developing",
      `Unemployment rate` > 4.999999 ~ "poor",
      TRUE ~ NA_character_
    )
  )
glimpse(konversi)
## Rows: 111
## Columns: 4
## $ `Unemployment rate` <dbl> 11.12, 12.33, 11.70, 6.89, 9.79, 16.99, 5.27, 5.51…
## $ GDP                 <dbl> 1.910135e+10, 1.527808e+10, 1.699882e+11, 9.463542…
## $ CPI                 <dbl> 149.90, 119.05, 151.36, 261.73, 232.75, 129.18, 11…
## $ country_status      <chr> "poor", "poor", "poor", "poor", "poor", "poor", "p…
# 6. Rata-rata dan nilai maximum GDP dan CPI serta banyak negara berdasarkan country status
summary_status <- konversi %>%
  group_by(country_status) %>%
  summarise(mean_GDP = mean(GDP),
            max_GDP = max(GDP),
            mean_CPI = mean(CPI),
            max_CPI = max(CPI),
            count_countries = n())
glimpse(summary_status)
## Rows: 3
## Columns: 6
## $ country_status  <chr> "developing", "poor", "rich"
## $ mean_GDP        <dbl> 804408074421, 647889907874, 191583986805
## $ max_GDP         <dbl> 1.99100e+13, 2.14277e+13, 5.43650e+11
## $ mean_CPI        <dbl> 142.2647, 154.5533, 119.4867
## $ max_CPI         <dbl> 268.36, 550.93, 135.87
## $ count_countries <int> 47, 61, 3
# 7. Hasil nomor 6 dalam format long
summary_status_long <- summary_status %>%
  pivot_longer(cols = c(mean_GDP, max_GDP, mean_CPI, max_CPI, count_countries),
               names_to = "statistic",
               values_to = "value")
glimpse(summary_status_long)
## Rows: 15
## Columns: 3
## $ country_status <chr> "developing", "developing", "developing", "developing",…
## $ statistic      <chr> "mean_GDP", "max_GDP", "mean_CPI", "max_CPI", "count_co…
## $ value          <dbl> 8.044081e+11, 1.991000e+13, 1.422647e+02, 2.683600e+02,…