Data Pre-processing: Vietnam Enterprise Survey (VES) Data - Condensed Version

Data Pre-processing Series, FTU Short Course

Prepare Data for Correcting VSIC Code

Dưới đây là toàn bộ R codes phiên bản rút gọn (không giải thích dài dòng) của post trước.

# Clear R environment: 

rm(list = ls())

# Load some R packages: 

library(readxl)
library(dplyr)
library(tidyr)
library(stringr)

# Load data (download from https://dangkykinhdoanh.gov.vn/vn/Pages/NganhNghe.aspx): 

read_xls("Bảng chuyển đổi VSIC 2018 - VSIC 2007.xls") -> vsic_converted

vsic_converted %>% slice(-c(1:4)) -> vsic_converted

names(vsic_converted) <- c("vsic_new_c3", "vsic_new_c4", "sector_name_new", 
                           "vsic_old_c3", "vsic_old_c4", "sector_name_old")

# Prepare data for correcting: 

vsic_converted %>% 
  filter(vsic_new_c3 != vsic_old_c3) %>% 
  select(vsic_new_c3, vsic_old_c3, sector_name_new, sector_name_old) -> vsic_diff_c3

vsic_converted %>% 
  filter(vsic_new_c4 != vsic_old_c4) -> vsic_diff_c4

vsic_diff_c4 %>% 
  filter(!duplicated(vsic_old_c4)) %>% 
  select(vsic_new_c4, code_l4 = vsic_old_c4) -> convert_table_c4

read_excel("df_sector_code.xlsx") -> df_sector_code

# Prepare data for correcting: 

df_sector_code %>% 
  mutate(code_level1 = case_when(str_count(code_level1) == 0 ~ NA_character_, TRUE ~ code_level1)) %>% 
  fill(code_level1, .direction = "down") %>% 
  filter(str_detect(sector_name_latin, "[a-z]")) %>% 
  mutate(code_level2 = case_when(str_count(code_level2) == 0 ~ NA_character_, TRUE ~ code_level2)) %>% 
  fill(code_level2, .direction = "down") -> df_sector_code_filled

df_sector_code %>% 
  select(code_level1, sector_name_latin) %>% 
  filter(str_count(code_level1) != 0) -> df_sector_l1

full_join(df_sector_l1, 
          df_sector_code_filled %>% select(code_level1, code_level2)) -> df_sector_l1

df_sector_l1 %>% 
  rename(code_l2 = code_level2) %>% 
  filter(!duplicated(code_l2)) -> df_sector_l1

Functions for Cleaning VES Data

# Function for processing/cleaning data: 

cleaning_data_tax_vsic_code <- function(your_ves_data) {
  
  # Convert to text and extract sector codes at all levels: 
  
  your_ves_data %>% 
    mutate(nganh_kd = as.character(nganh_kd)) %>% 
    mutate(code_l2 = str_sub(nganh_kd, start = 1, end = 2), 
           code_l3 = str_sub(nganh_kd, start = 1, end = 3), 
           code_l4 = str_sub(nganh_kd, start = 1, end = 4), 
           code_5_end = str_sub(nganh_kd, start = 5, end = 5)) -> ves_data
  
  # Remove missing at nganh_kd: 
  
  ves_data %>% filter(!is.na(nganh_kd)) -> ves_data
  
  # Remove missing at ma_thue: 
  
  ves_data %>% filter(!is.na(ma_thue)) -> ves_data
  
  #---------------
  # VSIC 5 digits
  #---------------
  
  ves_data %>% 
    mutate(nganh_kd = case_when(str_count(nganh_kd) == 4 ~ str_c("0", nganh_kd), TRUE ~ nganh_kd)) %>% 
    filter(str_count(nganh_kd) == 5) -> ves_data
  
  #----------------------
  # Tax code processing
  #----------------------
  
  # Remove missing: 
  
  ves_data %>% filter(!is.na(ma_thue)) -> ves_data
  
  # Remove duplications: 
  
  ves_data %>% filter(!duplicated(ma_thue)) -> ves_data
  
  # Remove cases != 10 digits: 
  
  ves_data %>% filter(str_count(ma_thue) == 10) -> ves_data

  return(ves_data)
  
}



# Function corrects VSIC Level 3: 

process_vsic3 <- function(your_df) {

  full_join(your_df, 
            vsic_diff_c3 %>% select(vsic_new_c3, code_l3 = vsic_old_c3),
            by = c("code_l3")) -> your_df
  
  your_df %>% 
    mutate(code_l3_adj = case_when(is.na(vsic_new_c3) ~ code_l3, TRUE ~ vsic_new_c3)) -> final_df
  
  return(final_df)
}

# Function corrects VSIC Level 4:


process_vsic4 <- function(your_df) {
  
  full_join(your_df, convert_table_c4, by = "code_l4") -> your_df
  
  your_df %>% 
    mutate(code_l4_adj = case_when(is.na(vsic_new_c4) ~ code_l4, TRUE ~ vsic_new_c4)) %>% 
    mutate(nganh_kd_adj = str_c(code_l4_adj, code_5_end)) %>% 
    mutate(code_l2_adj = str_sub(nganh_kd_adj, start = 1, end = 2)) -> final_df
  
  return(final_df)
  
}


# Funtions join sector name: 

add_sector_name_at_level_1 <- function(your_df) {
  
  inner_join(your_df, 
             df_sector_l1, 
             by = c("code_l2_adj" = "code_l2")) -> final_df
  
  return(final_df)
  
}

Use The Functions

library(haven) # For importing data from Stata files: 

read_dta("F:\\VES_from_MaiVu_FTU\\Stata_2015\\dn2015.dta") %>% 
  cleaning_data_tax_vsic_code() %>% 
  process_vsic3() %>% 
  process_vsic4() %>% 
  add_sector_name_at_level_1() -> dn2015_after_processing

For Reporting

library(knitr)

dn2015_after_processing %>% 
  filter(!is.na(kqkd1)) %>% 
  group_by(sector_name_latin) %>% 
  summarise(Min = min(kqkd1), 
            Max = max(kqkd1), 
            SD = sd(kqkd1), 
            Median = median(kqkd1), 
            N_obs = n()) %>% 
  arrange(-N_obs) %>% 
  mutate(sector_name_short = str_sub(sector_name_latin, 1, 20)) %>% 
  select(-sector_name_latin) %>% 
  select(sector_name_short, everything()) %>% 
  kable()

sector_name_short	Min	Max	SD	Median	N_obs
BAN BUON VA BAN LE;	0	105559749	395966.60	2102.0	169612
CONG NGHIEP CHE BIEN	0	375812792	2059143.28	2529.0	72810
XAY DUNG	-26	11966123	133276.12	1717.0	58651
HOAT DONG CHUYEN MON	-95	3404914	43026.90	437.0	36985
VAN TAI KHO BAI	0	12235791	177607.50	2182.0	26511
HOAT DONG HANH CHINH	0	4815353	68250.97	672.0	17313
DICH VU LUU TRU VA A	0	4694252	62100.38	586.0	16056
THONG TIN VA TRUYEN	0	57474075	803345.92	232.0	9458
HOAT DONG KINH DOANH	0	7292986	169680.21	290.5	8624
GIAO DUC VA DAO TAO	0	1136408	35180.19	106.0	5283
HOAT DONG TAI CHINH,	-292	22942233	785044.73	1699.0	3175
HOAT DONG DICH VU KH	0	366965	13330.10	165.0	3155
NGHE THUAT, VUI CHOI	0	38174412	843315.37	153.0	2417
CUNG CAP NUOC; HOAT	0	2826496	118502.49	1002.0	1702
SAN XUAT VA PHAN PHO	0	77951360	3103259.73	2445.0	1495
Y TE VA HOAT DONG TR	0	969653	59694.20	753.9	1420
HOAT DONG CUA CAC TO	0	455638	55533.97	1050.0	74