Cleaning🧹

Preliminary Set up

library(tidyverse) # load required packages

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.2     ✔ tibble    3.3.0
✔ lubridate 1.9.4     ✔ tidyr     1.3.1
✔ purrr     1.1.0     
── 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

Data import

income<- read_csv("data/income.csv") # import and glimpse data

Rows: 11046 Columns: 8
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (4): STAT_VAR, STAT_PRES, AGE, SEX
dbl (4): CCYY, M3M, OBS_VALUE, SD_VALUE

ℹ 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.

head(income)

# A tibble: 6 × 8
  STAT_VAR STAT_PRES  CCYY   M3M AGE   SEX   OBS_VALUE SD_VALUE
  <chr>    <chr>     <dbl> <dbl> <chr> <chr>     <dbl>    <dbl>
1 MDNMEEEM Raw_hkd_d  1985    NA 15-24 M          2000       NA
2 MDNMEEEM Raw_hkd_d  1985    NA 15-24 F          2000       NA
3 MDNMEEEM Raw_hkd_d  1985    NA 15-24 <NA>       2000       NA
4 MDNMEEEM Raw_hkd_d  1985    NA 25-34 M          3000       NA
5 MDNMEEEM Raw_hkd_d  1985    NA 25-34 F          2300       NA
6 MDNMEEEM Raw_hkd_d  1985    NA 25-34 <NA>       3000       NA

rent<-read_csv("data/rent.csv")

Rows: 321 Columns: 31
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (18): Month, Class A Hong Kong - Remarks, Class A Kowloon - Remarks, Cla...
dbl (13): Class A Hong Kong, Class A Kowloon, Class A New Territories, Class...

ℹ 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.

head(rent)

# A tibble: 6 × 31
  Month  `Class A Hong Kong` `Class A Hong Kong - Remarks` `Class A Kowloon`
  <chr>                <dbl> <chr>                                     <dbl>
1 Jan-99                 190 <NA>                                        171
2 Feb-99                 196 <NA>                                        173
3 Mar-99                 199 <NA>                                        170
4 Apr-99                 191 <NA>                                        171
5 May-99                 191 <NA>                                        175
6 Jun-99                 193 <NA>                                        176
# ℹ 27 more variables: `Class A Kowloon - Remarks` <chr>,
#   `Class A New Territories` <dbl>, `Class A New Territories - Remarks` <chr>,
#   `Class B Hong Kong` <dbl>, `Class B Hong Kong - Remarks` <chr>,
#   `Class B Kowloon` <dbl>, `Class B Kowloon - Remarks` <chr>,
#   `Class B New Territories` <dbl>, `Class B New Territories - Remarks` <chr>,
#   `Class C Hong Kong` <dbl>, `Class C Hong Kong - Remarks` <chr>,
#   `Class C Kowloon` <dbl>, `Class C Kowloon - Remarks` <chr>, …

Data clean (rent)

rent_clean<-rent|>
  rename(b_hk='Class B Hong Kong',            # rename needed columns with shorter names
         b_kow='Class B Kowloon',
         b_nt='Class B New Territories',
         a_hk='Class A Hong Kong',
         a_kow='Class A Kowloon',
         a_nt='Class A New Territories')|>
  mutate(year=substr(Month,5,6))|>  
  # use last two digits of 'Month' in original data to represent 'year'
select(year,a_hk,a_kow,a_nt,b_hk,b_kow,b_nt)     # select column I want

rent_clean<-rent_clean|>
pivot_longer(cols=2:7,                  # edit wide dataset to a long one
             names_to="housetype",      # add a column 'housetype' to categorize 
             values_to="rent")|>
  select(year,housetype,rent)|>
  filter(year<=25,year>=00)
head(rent_clean)                        # preview cleaned data

# A tibble: 6 × 3
  year  housetype  rent
  <chr> <chr>     <dbl>
1 00    a_hk        187
2 00    a_kow       166
3 00    a_nt        132
4 00    b_hk        188
5 00    b_kow       149
6 00    b_nt        116

Data clean (income)

income_clean<-income|>
  rename(year=CCYY,age=AGE,income=OBS_VALUE)|> 
  # rename key columns for ease of understanding
  select(year,age,income)|>               # select key columns
  mutate(year=substr(year,3,4))|>         # edit a column representing year
  filter(year<=25, year>=00)              # only study year 2000 to 2025
head(income_clean)                        # preview the cleaned data

# A tibble: 6 × 3
  year  age   income
  <chr> <chr>  <dbl>
1 00    15-24   8000
2 00    15-24   7500
3 00    15-24   8000
4 00    25-34  12000
5 00    25-34  10000
6 00    25-34  11000

Save the Data

save(rent_clean, file = "data/rent.RData") 
save(income_clean, file = "data/income.RData") 
# save the data in RData since it preserves the format and is smaller in size