Project-2-Data Transforamtions

1.Objective

This project develops practical competency in transforming wide-format datasets into tidy formats suitable for downstream analysis. All transformations are performed using the tidyr and dplyr packages in R.

2. Dataset Selection

Medical dataset: https://www.kaggle.com/datasets/aamir5659/raw-medical-dataset-for-cleaning-practice?resource=download I obtained this dataset from kaggle while searching for practical examples dataset to learn data tidying using dplyr and tidy.

Baby names dataset:https://www.ssa.gov/oact/babynames/top5names.html Obtained from 5A discussion post contributed by Aniss Sahraoui.

Labor market dataset: https://www.bls.gov/news.release/archives/laus_04082026.htm Obtained from 5A discussion post contributed by Zaina Hassan.

2.1 Data structure before tidyng

Column names: All column names are written in uppercase snake case.

Inconsistent gender values: The gender column contains inconsistent capitalization, such as FEMALE, MALE, Male, and Female.

Inconsistent smoker values: The smoker column contains different representations, including Yes, No, Y, and N.

Inconsistent missing values: Missing values in the gender, smoker, diagnosis, and notes columns are represented using different formats, such as nan and N/A.

Missing values across columns: Missing data are present in the following columns: notes, diagnosis, cholesterol, bmi, smoker, age, gender, and blood_pressure.

3. Data Preparation

3.1 Raw Data Construction

library(readr)
library(dplyr)

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

library(stringr)
library(tidyr) 
library(naniar)
library(ggplot2)
# Import raw data
medical_df<-read_csv("https://raw.githubusercontent.com/lhamo07/Data-607-Assignment/refs/heads/main/project-2/realworld_medical_dirty.csv")

Rows: 100 Columns: 10

── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr  (5): Patient_ID, Gender, Smoker, Diagnosis, Notes
dbl  (4): Age, Blood_Pressure, Cholesterol, BMI
date (1): Admission_Date

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

medical_df

# A tibble: 100 × 10
   Patient_ID   Age Gender Blood_Pressure Cholesterol   BMI Smoker Diagnosis    
   <chr>      <dbl> <chr>           <dbl>       <dbl> <dbl> <chr>  <chr>        
 1 P1000         55 FEMALE            120          NA  35.4 Yes    Heart Disease
 2 P1001         65 Male              150         180  27.8 nan    None         
 3 P1002         45 Male              120         220  22.5 No     nan          
 4 P1003         65 Male               NA         180  35.4 No     nan          
 5 P1004         65 Male              120         300  40.1 nan    None         
 6 P1005         35 Female            130         200  35.4 N      Heart Disease
 7 P1006         45 MALE              150          NA  22.5 No     None         
 8 P1007         45 MALE              150         200  NA   N      nan          
 9 P1008         45 Male               NA         200  NA   No     Diabetes     
10 P1009         65 MALE              130          NA  35.4 No     Diabetes     
# ℹ 90 more rows
# ℹ 2 more variables: Admission_Date <date>, Notes <chr>

3.2 Data Import and Tidying

medical_clean_df<-medical_df%>%
# Rename variables to follow a consistent naming convention
  rename_with(tolower)%>%
# Data cleaning and recoding
  mutate(gender=str_to_title(gender),
         smoker=recode(smoker,"Y"="Yes",
                      
                    "N"="No"))%>%
  # Convert NA, nan,N/A and Nan text into real NA  variables.

  
  mutate(
    across(
      where(is.character),
      ~ ifelse(
        .x %in% c("NA", "N/A", "nan", "Nan"),
        NA,
        .x
      )
    )
     
  )

medical_clean_df

# A tibble: 100 × 10
   patient_id   age gender blood_pressure cholesterol   bmi smoker diagnosis    
   <chr>      <dbl> <chr>           <dbl>       <dbl> <dbl> <chr>  <chr>        
 1 P1000         55 Female            120          NA  35.4 Yes    Heart Disease
 2 P1001         65 Male              150         180  27.8 <NA>   None         
 3 P1002         45 Male              120         220  22.5 No     <NA>         
 4 P1003         65 Male               NA         180  35.4 No     <NA>         
 5 P1004         65 Male              120         300  40.1 <NA>   None         
 6 P1005         35 Female            130         200  35.4 No     Heart Disease
 7 P1006         45 Male              150          NA  22.5 No     None         
 8 P1007         45 Male              150         200  NA   No     <NA>         
 9 P1008         45 Male               NA         200  NA   No     Diabetes     
10 P1009         65 Male              130          NA  35.4 No     Diabetes     
# ℹ 90 more rows
# ℹ 2 more variables: admission_date <date>, notes <chr>

glimpse(medical_clean_df)

Rows: 100
Columns: 10
$ patient_id     <chr> "P1000", "P1001", "P1002", "P1003", "P1004", "P1005", "…
$ age            <dbl> 55, 65, 45, 65, 65, 35, 45, 45, 45, 65, 55, 45, NA, 65,…
$ gender         <chr> "Female", "Male", "Male", "Male", "Male", "Female", "Ma…
$ blood_pressure <dbl> 120, 150, 120, NA, 120, 130, 150, 150, NA, 130, 140, 12…
$ cholesterol    <dbl> NA, 180, 220, 180, 300, 200, NA, 200, 200, NA, 220, 300…
$ bmi            <dbl> 35.4, 27.8, 22.5, 35.4, 40.1, 35.4, 22.5, NA, NA, 35.4,…
$ smoker         <chr> "Yes", NA, "No", "No", NA, "No", "No", "No", "No", "No"…
$ diagnosis      <chr> "Heart Disease", "None", NA, NA, "None", "Heart Disease…
$ admission_date <date> 2023-12-27, 2023-01-01, 2023-12-14, 2023-07-09, 2023-0…
$ notes          <chr> NA, NA, NA, NA, "Follow-up required", "Follow-up requir…

# Summarize missing data for all columns 
miss_var_summary(medical_clean_df)

# A tibble: 10 × 3
   variable       n_miss pct_miss
   <chr>           <int>    <num>
 1 notes              43       43
 2 diagnosis          21       21
 3 cholesterol        20       20
 4 bmi                20       20
 5 smoker             20       20
 6 age                17       17
 7 gender             15       15
 8 blood_pressure     14       14
 9 patient_id          0        0
10 admission_date      0        0

Before imputing any variable, I checked the percentage of miss by summarizing missing data, then I found cholesterol and bmi variable are skewed from follwing visualization. So I choose median imputaion on than mean, since median is resistance to outliers.

ggplot(medical_clean_df, aes(cholesterol)) + geom_histogram(bins = 20)

Warning: Removed 20 rows containing non-finite outside the scale range
(`stat_bin()`).

ggplot(medical_clean_df, aes(bmi)) + geom_histogram(bins = 20) 

Warning: Removed 20 rows containing non-finite outside the scale range
(`stat_bin()`).

ggplot(medical_clean_df, aes(blood_pressure)) + geom_histogram(bins = 20) 

Warning: Removed 14 rows containing non-finite outside the scale range
(`stat_bin()`).

ggplot(medical_clean_df, aes(age)) + geom_histogram(bins = 20) 

Warning: Removed 17 rows containing non-finite outside the scale range
(`stat_bin()`).

vars <- c("age", "bmi", "cholesterol", "blood_pressure")

medical_imputed_df <- medical_clean_df %>%
 
# Handle missing numeric data by conditional median imputation 
  group_by(diagnosis)%>%
  mutate(across(all_of(vars),
                ~ ifelse(is.na(.x), median(.x, na.rm = TRUE), .x)))%>%
  ungroup()%>%
# Handle categorical data by descriptive categories
   mutate(gender = replace_na(gender, "Unknown"),
    smoker=replace_na(smoker,"Unknown"),
    diagnosis = replace_na(diagnosis, "Unknown"),
    notes=replace_na(notes,"No notes"))
    
  
medical_imputed_df

# A tibble: 100 × 10
   patient_id   age gender blood_pressure cholesterol   bmi smoker  diagnosis   
   <chr>      <dbl> <chr>           <dbl>       <dbl> <dbl> <chr>   <chr>       
 1 P1000         55 Female            120         220  35.4 Yes     Heart Disea…
 2 P1001         65 Male              150         180  27.8 Unknown None        
 3 P1002         45 Male              120         220  22.5 No      Unknown     
 4 P1003         65 Male              130         180  35.4 No      Unknown     
 5 P1004         65 Male              120         300  40.1 Unknown None        
 6 P1005         35 Female            130         200  35.4 No      Heart Disea…
 7 P1006         45 Male              150         200  22.5 No      None        
 8 P1007         45 Male              150         200  35.4 No      Unknown     
 9 P1008         45 Male              140         200  28.9 No      Diabetes    
10 P1009         65 Male              130         220  35.4 No      Diabetes    
# ℹ 90 more rows
# ℹ 2 more variables: admission_date <date>, notes <chr>

I chose to impute missing values specifically for Cholesterol, BMI, blood pressure, and age because they are numeric clinical variables that correlate with each other and with diagnosis, so the other columns carry real information about the missing ones. Since gender is a demographic identity, guessing it adds noise. smoker is self-reported and sensitive data, keeping Unkown is safer. notes is free text, Imputing has no meaning.

Using overall median imputation created a major statistical issue: every single diagnosis group ended up with the exact same median value. Switching to conditional median imputation calculates a separate median for each category. This ensures missing patient measurements are replaced using data from patients with the same medical condition, preserving real clinical differences.

# Reshape from wide to long format
medical_df_long <- medical_imputed_df %>%
  pivot_longer(
    cols = c(age, blood_pressure, cholesterol, bmi),
    names_to = "measurement",
    values_to = "value"
  )
 
write.csv(medical_df_long,"medi.csv")

3.3 Analysis

medical_df_long %>%
  filter(measurement == "age") %>%
  ggplot(aes(x = diagnosis, y = value)) +
  geom_boxplot() +
  labs(
    title = "Age Distribution by Diagnosis",
    x = "Diagnosis",
    y = "Age (years)"
  ) +
  theme_minimal()

This box-plot shows the ages of patients in different diagnosis groups. Patients in the Diabetes group have a median age of around 55 years, while patients in the Heart Disease group have a median age of around 45 years.Patients with no diagnosis also have a median age of around 45 years. This suggests that patients in the Diabetes group tend to be older than those in the Heart Disease group in this dataset.

4. Conclusion and future enhancement

In this project, I learned to clean and transform data using tidyr and dplyr. I also learned the importance of understanding missing data before choosing an imputation method. I used conditional median imputation, but in future projects, I would consider MICE to account for relationships between variables.

5. Reference

Anthropic. (2026). Claude Sonnet 5.5 [Large language model]. https://claude.ai. Accessed October 11, 2026. https://claude.ai/share/19de782d-5da9-486c-ad03-17ed79f38705