Successfully processed and saved file to: C:/Users/Imran/OneDrive/Documents/cuny university/607/Project 2/Health_care_Exp1.csv Project 2 — Data Tidying
Executive Overview
This Quarto documents / approach document is the end-to-end data transformation pipeline required to import untidy (“wide”) datasets across four domains (it was three mandatory), convert them into a normalized (“tidy”) data model using tidyr and dplyr, and analyze the transformed datasets.
Dataset 1: Health Care Expenditure (Text Data)
1. Data Source & Import from GitHub
At the very first instance I copy the data from some blog on the internet page and paste into the Health_care_exp.txt file. Thereafter, this .txt file is uploaded at the github. This dataset is a synthetic wide format benchmark dataset designed specifically for data transformation, tidying, and reshaping practice.
The next step id to download and read the raw text file line by line, directly into a data frame, where most of the raw data uses comma, tab or pipe delimiters.
The data is then Clean and inspect the imported data structure where fully empty rows removed if any.
The next step is to define destination file path on my local computer, by default, this saves to your current R working directory of my computer.
and finally write the Health_care_Exp1.csv in to the current R working Directory
2.Uploading the .csv file into github and getting directly in R
In this step, I uploaded the Health_care_Exp1.csv file in to the github
Secondly, read wide dataset with exact column names preserved
3. Data Structural Inspection Before Tidying
nrow(raw_data) & ncol(raw_data): These functions calculate the total number of rows (observations) and columns (variables) present in the raw_data data frame.
cat(…): Concatenates and prints formatted text directly to the output console or rendered document.
“\n\n”: Adds two line breaks after the printed string to create visual spacing before the next output.
It will furnish an immediate snapshot of the dataset size (e.g., Dataset Dimensions — Rows: 04 | Columns: 06).
Dataset Dimensions — Rows: 4 | Columns: 6 | Demographic / Income Level | 2018 | 2019 | 2020 | 2021 | 2022 |
|---|---|---|---|---|---|
| Aging Population (High Burden / High Income) | 10.8% | 11.2% | 12.9% | 12.4% | 12.1% |
| Working Population (Moderate Burden / High Income) | 6.5% | 6.7% | 7.8% | 7.4% | 7.2% |
| Aging Population (High Burden / Middle Income) | 5.2% | 5.4% | 6.1% | 6.0% | 6.3% |
| Youth Population (Low Burden / Low Income) | 3.1% | 3.2% | 3.6% | 3.5% | 3.7% |
4. Data Transformation Pipeline
There are 04 stages of my data transformation pipeline
1. Data Ingestion
- Fetching raw text or CSV files directly from GitHub (read_tsv / read.csv).
2. Data Cleansing & Normalization
Fixing structural issues, stripping unwanted characters, fixing data types, and standardizing naming conventions.
Removing $, ,, and % symbols using regular expressions (gsub(“[$,%]”, ““, expenditure_raw)).
Converting text strings to proper numeric types (as.numeric(), as.integer()).
Filtering out missing or empty rows (drop_na()).
Standardizing column headers to snake_case using janitor::clean_names().
3. Data Reshaping (Tidying)
Reorganizing data layout to follow Tidy Data principles:
Each variable forms a column.
Each observation forms a row.
Each cell contains a single value.
Converting wide year columns (2018, 2019, 2020…) into long key-value rows (year and expenditure) using pivot_longer().
4. Aggregation & Output Formatting
- Grouping by categories (group_by()), computing summary statistics (mean(), min(), max()), and outputting styled tables (kable()) and charts (ggplot2).
| demographic_income_level | year | expenditure_raw | expenditure |
|---|---|---|---|
| Aging Population (High Burden / High Income) | 2018 | 10.8% | 10.8 |
| Aging Population (High Burden / High Income) | 2019 | 11.2% | 11.2 |
| Aging Population (High Burden / High Income) | 2020 | 12.9% | 12.9 |
| Aging Population (High Burden / High Income) | 2021 | 12.4% | 12.4 |
| Aging Population (High Burden / High Income) | 2022 | 12.1% | 12.1 |
| Working Population (Moderate Burden / High Income) | 2018 | 6.5% | 6.5 |
| Working Population (Moderate Burden / High Income) | 2019 | 6.7% | 6.7 |
| Working Population (Moderate Burden / High Income) | 2020 | 7.8% | 7.8 |
| Working Population (Moderate Burden / High Income) | 2021 | 7.4% | 7.4 |
| Working Population (Moderate Burden / High Income) | 2022 | 7.2% | 7.2 |
5. Analytical Methods
Data processing and exploratory analysis were conducted using R (tidyverse, janitor, and knitr). The workflow comprised three main phases:
Data Ingestion and Cleansing: Raw data was ingested directly from GitHub. Non-informative empty rows and missing records were filtered out. Currency symbols ($), commas, and percentages were programmatically stripped using regular expressions to convert quantitative fields into clean numeric formats (double).
Data Reshaping (Tidying): The raw dataset exhibited an untidy, wide-column structure where individual years spanned across multiple columns. Using pivot_longer(), time-series variables were consolidated into a normalized key-value format (year and expenditure), adhering to tidy data principles where each variable forms a column and each observation forms a row.
Normalization and Aggregation: Variable names were standardized into snake_case using clean_names(). Summary statistics—including mean, minimum, maximum, and net expenditure changes—were computed across categories and time frames to quantify growth trends.
6. Visualization of the output
The visualization highlights key structural patterns in healthcare spending across the evaluated timeframe:
- Trajectory & Trend: Healthcare expenditure demonstrates an overall upward trend over time, reflecting increasing operational, administrative, and clinical costs.
- Category Variance: A noticeable divergence exists among spending categories/groups. Higher-tier spending brackets maintain a consistently elevated baseline compared to lower-tier expenditures, showing minimal crossover over the evaluated years.
- Growth Velocity: Year-over-year increases show steady growth rather than sudden spikes, indicating sustained systemic demand rather than localized anomalies.
7. Analysis / Interpretation of results in context
Highest Overall Expenditure: Aging Population (High Burden / High Income):
Holds the highest average expenditure (11.88) and peak spending (12.9).
Saw the greatest growth over time with a
total_changeof 1.3.Takeaway: High healthcare burden paired with high income resources correlates directly with maximum total spending and the fastest cost growth.
Moderate Expenditure: Working Population (Moderate Burden / High Income):
Maintains an average expenditure of 7.12 (ranging from 6.5 to 7.8) with a growth of 0.7.
Takeaway: Strong financial access enables high expenditure, but moderate health burden keeps overall spending substantially lower than the aging tier.
Significant Growth in Middle Income: Aging Population (High Burden / Middle Income):
Holds an average spending of 5.80 (ranging from 5.2 to 6.3).
Recorded the second-highest growth with a
total_changeof 1.1.Takeaway: High healthcare burden drives significant spending increases over time even within middle-income brackets.
Lowest Spending & Growth: Youth Population (Low Burden / Low Income):
Represents the lowest spending tier across all metrics: mean (3.42), min (3.1), max (3.7), and growth (0.6).
Takeaway: Lower medical burden combined with financial constraints yields the lowest overall healthcare expenditure.
| demographic_income_level | mean_expenditure | min_expenditure | max_expenditure | total_change |
|---|---|---|---|---|
| Aging Population (High Burden / High Income) | 11.88 | 10.8 | 12.9 | 1.3 |
| Working Population (Moderate Burden / High Income) | 7.12 | 6.5 | 7.8 | 0.7 |
| Aging Population (High Burden / Middle Income) | 5.80 | 5.2 | 6.3 | 1.1 |
| Youth Population (Low Burden / Low Income) | 3.42 | 3.1 | 3.7 | 0.6 |
8. Conclusion
This part of the Quarto document details an end-to-end R pipeline that ingests an untidy, wide-format healthcare expenditure dataset from GitHub, cleans raw text formatting, and reshapes it into a normalized long model using tidyr, dplyr, and janitor. Quantitative values are parsed via regex and column names are standardized to snake_case before calculating metrics and generating ggplot2 visualizations. The resulting analysis shows that healthcare expenditure trends are strongly driven by demographic burden and income level, with the Aging Population (High Burden / High Income) group demonstrating both the highest average spending and the greatest cost expansion, while the Youth Population (Low Burden / Low Income) group maintains the lowest spending baseline and slowest growth.
--------------------------------------------------------------------------------Dataset 2: E-Commerce growth
1. Data Source & Import from GitHub
To begin the data pipeline, a synthetic wide-format benchmark dataset was constructed from web-sourced e-commerce content and saved as ecommerce_growth.txt before being uploaded to GitHub for centralized hosting. The raw file was then imported directly into R as a structured data frame using delimiter handling to accommodate tab, comma, or pipe separators. Following ingestion, the raw structure underwent initial cleaning to inspect its dimensions and remove any fully blank or empty rows. Finally, a local destination file path was established targeting the active R working directory, and the cleaned data frame was exported and saved locally as ecommerce_growth.csv for downstream transformation and tidying.
Successfully processed and saved file to: C:\Users\Imran\OneDrive\Documents\cuny university\607\Project 2\E-commerce_growth.csv 2.Uploading the .csv file into github and getting directly in R
In this step, I uploaded the E-commerce_growth.csv file from the github and read wide dataset with exact column names preserved
3. Data Structural Inspection Before Tidying
To summarize dataset structure and dimensions, the nrow(raw_data) and ncol(raw_data) functions are utilized to extract the total count of observations and variables present within the imported data frame. These structural metrics are then formatted and output to the console using the cat() function, which incorporates \n\n line breaks to ensure clean visual separation from subsequent output. Executing this step provides an immediate, high-level snapshot of the raw dataset’s size (for instance, Dataset Dimensions — Rows: 04 | Columns: 06), allowing for quick verification of the data ingestion process before initiating further tidying.
Dataset Dimensions — Rows: 4 | Columns: 6 | Retail Sector / Market Type | 2018 | 2019 | 2020 | 2021 | 2022 |
|---|---|---|---|---|---|
| Consumer Electronics (High Demand / Mature) | 21.4% | 23.1% | 28.5% | 29.0% | 31.2% |
| Apparel & Fashion (High Demand / Emerging) | 14.2% | 15.8% | 21.0% | 22.4% | 24.1% |
| Grocery & Food (Low Demand / Emerging) | 3.1% | 4.0% | 7.8% | 8.2% | 9.5% |
| Home & Furniture (Moderate Demand / Mature) | 9.8% | 10.5% | 14.2% | 14.9% | 16.0% |
4. Data Transformation Pipeline
There are 04 stages of my data transformation pipeline
1. Data Ingestion
Fetching raw text or CSV files directly from GitHub (read_tsv / read.csv).
2. Data Cleansing & Normalization
Fixing structural issues, stripping unwanted characters, fixing data types, and standardizing naming conventions.
Removing $, ,, and % symbols using regular expressions (gsub(“[$,%]”, ““, expenditure_raw)).
Converting text strings to proper numeric types (as.numeric(), as.integer()).
Filtering out missing or empty rows (drop_na()).
Standardizing column headers to snake_case using janitor::clean_names().
3. Data Reshaping (Tidying)
Reorganizing data layout to follow Tidy Data principles:
Each variable forms a column.
Each observation forms a row.
Each cell contains a single value.
Converting wide year columns (2018, 2019, 2020…) into long key-value rows (year and expenditure) using pivot_longer().
4. Aggregation & Output Formatting
Grouping by categories (group_by()), computing summary statistics (mean(), min(), max()), and outputting styled tables (kable()) and charts (ggplot2)
| retail_sector_market_type | year | growth_rate_pct |
|---|---|---|
| Consumer Electronics (High Demand / Mature) | 2018 | 21.4 |
| Consumer Electronics (High Demand / Mature) | 2019 | 23.1 |
| Consumer Electronics (High Demand / Mature) | 2020 | 28.5 |
| Consumer Electronics (High Demand / Mature) | 2021 | 29.0 |
| Consumer Electronics (High Demand / Mature) | 2022 | 31.2 |
| Apparel & Fashion (High Demand / Emerging) | 2018 | 14.2 |
| Apparel & Fashion (High Demand / Emerging) | 2019 | 15.8 |
| Apparel & Fashion (High Demand / Emerging) | 2020 | 21.0 |
| Apparel & Fashion (High Demand / Emerging) | 2021 | 22.4 |
| Apparel & Fashion (High Demand / Emerging) | 2022 | 24.1 |
The below line plot displays the annual growth rate trajectory (%) for four distinct e-commerce retail sectors across a five-year period from 2018 to 2022.
5. Analytical Methods
Data processing and exploratory analysis were conducted using R (tidyverse, janitor, and knitr). The workflow comprised three main phases:
Data Ingestion and Cleansing: Raw data was ingested directly from GitHub. Non-informative empty rows and missing records were filtered out. Currency symbols ($), commas, and percentages were programmatically stripped using regular expressions to convert quantitative fields into clean numeric formats (double).
Data Reshaping (Tidying): The raw dataset exhibited an untidy, wide-column structure where individual years spanned across multiple columns. Using pivot_longer(), time-series variables were consolidated into a normalized key-value format (year and expenditure), adhering to tidy data principles where each variable forms a column and each observation forms a row.
Normalization and Aggregation: Variable names were standardized into snake_case using clean_names(). Summary statistics—including mean, minimum, maximum, and net expenditure changes—were computed across categories and time frames to quantify growth trends.
6. Visualization of the output
Overall Upward Trajectory Across All Sectors
Every retail sector experienced positive net growth over the 5-year period.
There is a noticeable acceleration/steeper slope between 2019 and 2020 across all categories, likely reflecting the e-commerce surge driven by global COVID-19 pandemic demand.
Post-2020 (2020–2022), growth rates continued to rise, but at a more gradual, stabilized pace.
Sector Performance Hierarchy
Consumer Electronics (High Demand / Mature) (Green Line): Maintains the highest growth rate throughout the entire timeline, starting around 21.5% in 2018 and expanding to over 31% by 2022.
Apparel & Fashion (High Demand / Emerging) (Coral/Red Line): Holds the second-highest trajectory, growing steadily from roughly 14% in 2018 to 24% in 2022.
Home & Living (or mid-tier sector) (Purple Line): Ranks third, expanding from 10% in 2018 to approximately 16% in 2022.
Grocery & Food (Low Demand / Emerging) (Teal Line): Recorded the lowest growth rates overall, starting at ~3% in 2018, making a sharp jump to ~8% in 2020, and finishing near 10% in 2022.
7. Analysis/ Interpretation of results in context
Performance Hierarchy: Market performance aligns directly with consumer demand tiers. Consumer Electronics (High Demand / Mature) leads with the highest average growth rate (26.64%) and peak performance (31.2%), followed by Apparel & Fashion (High Demand / Emerging) at 19.50%. Home & Furniture (Moderate Demand / Mature) occupies the mid-tier at 13.08%, while Grocery & Food (Low Demand / Emerging) records the lowest overall average at 6.52%.
Structural Volatility & Dispersion: Metric spreads (\(\text{max\_value} - \text{min\_value}\)) reveal a distinct divergence in growth elasticity:
High-Expansion Sectors: High-demand categories experienced broader growth swings (\(\approx 9.8 - 9.9\) percentage points), reflecting greater sensitivity to macroeconomic online adoption surges.
Stable Sectors: Lower-demand and mature categories exhibited tighter growth ranges (\(\approx 6.2 - 6.4\) percentage points), pointing to stable, incremental trajectory shifts rather than sharp expansion spikes.
| retail_sector_market_type | mean_value | min_value | max_value | total_spread |
|---|---|---|---|---|
| Consumer Electronics (High Demand / Mature) | 26.64 | 21.4 | 31.2 | 9.8 |
| Apparel & Fashion (High Demand / Emerging) | 19.50 | 14.2 | 24.1 | 9.9 |
| Home & Furniture (Moderate Demand / Mature) | 13.08 | 9.8 | 16.0 | 6.2 |
| Grocery & Food (Low Demand / Emerging) | 6.52 | 3.1 | 9.5 | 6.4 |
8. Conclusion
An analysis of e-commerce growth data (2018–2022) processed through R (tidyverse) reveals sustained positive growth across all four evaluated sectors, characterized by a sharp pandemic-driven acceleration in 2020 followed by steady post-2020 stabilization. High-demand categories led overall market performance and exhibited greater growth elasticity, with Consumer Electronics holding the highest average growth rate (26.64%, peaking at 31.2%) followed by Apparel & Fashion (19.50%), while Home & Furniture (13.08%) occupied the mid-tier and Grocery & Food (6.52%) maintained a more stable, incremental trajectory.
--------------------------------------------------------------------------------Dataset 3: Renewable Energy Grid Share
1. Data Source & Import from GitHub
At the very first instance I copy the data from some blog on the internet page and paste into the renewable_energy_Share.txt file. Thereafter, this .txt file is uploaded at the github. This dataset is a synthetic wide format benchmark dataset designed specifically for data transformation, tidying, and reshaping practice.
The next step id to download and read the raw text file line by line, directly into a data frame, where most of the raw data uses comma, tab or pipe delimiters.
The data is then Clean and inspect the imported data structure where fully empty rows removed if any.
The next step is to define destination file path on my local computer, by default, this saves to your current R working directory of my computer.
and finally write the renewable_energy_share.csv in to the current R working Directory.
Successfully processed and saved file to: C:\Users\Imran\OneDrive\Documents\cuny university\607\Project 2\renewable_energy_Share.csv 2.Uploading the .csv file into github and getting directly in R
In this step, I uploaded the renewable_energy_Share.csv file in to the github
Secondly, read wide dataset with exact column names preserved
3. Data Structural Inspection Before Tidying
nrow(raw_data) & ncol(raw_data): These functions calculate the total number of rows (observations) and columns (variables) present in the raw_data data frame.
cat(…): Concatenates and prints formatted text directly to the output console or rendered document.
“\n\n”: Adds two line breaks after the printed string to create visual spacing before the next output.
It will furnish an immediate snapshot of the dataset size (e.g., Dataset Dimensions — Rows: 10 | Columns: 6).
Dataset Dimensions — Rows: 4 | Columns: 6 | Grid Region / Scale Level | 2018 | 2019 | 2020 | 2021 | 2022 |
|---|---|---|---|---|---|
| Northern Grid (Utility Scale / Centralized) | 32.4% | 35.1% | 38.0% | 41.2% | 45.6% |
| Southern Grid (Utility Scale / Distributed) | 19.8% | 21.5% | 24.1% | 26.8% | 29.3% |
| Eastern Grid (Small Scale / Centralized) | 11.2% | 12.0% | 13.5% | 14.8% | 16.2% |
| Western Grid (Small Scale / Distributed) | 25.6% | 28.0% | 31.4% | 34.0% | 37.8% |
4. Data Transformation Pipeline
I perform the standard tidyr / dplyr tidying pipeline:
Pivoting: Convert year columns (
2018–2022) to long format.Entity Normalization: Split combined
Grid Region / Scale Levelinto distinct attributes.Cleaning: Strip percentage signs (
%) and convert strings to numeric floating points.
Key Changes Made:
Removed row.names = NULL (no longer needed once reading true CSV data)
Variable Normalization: Added janitor::clean_names() to convert all variable names into clean snake_case (e.g., grid_region, operation_scale, renewable_share_pct, mean_share, absolute_growth).
Table Output: Added kable() display right after name normalization to inspect the clean snake_case variable structure.
| grid_region | operation_scale | grid_structure | year | renewable_share_pct |
|---|---|---|---|---|
| Northern Grid | Utility Scale | Centralized | 2018 | 32.4 |
| Northern Grid | Utility Scale | Centralized | 2019 | 35.1 |
| Northern Grid | Utility Scale | Centralized | 2020 | 38.0 |
| Northern Grid | Utility Scale | Centralized | 2021 | 41.2 |
| Northern Grid | Utility Scale | Centralized | 2022 | 45.6 |
| Southern Grid | Utility Scale | Distributed | 2018 | 19.8 |
| Southern Grid | Utility Scale | Distributed | 2019 | 21.5 |
| Southern Grid | Utility Scale | Distributed | 2020 | 24.1 |
| Southern Grid | Utility Scale | Distributed | 2021 | 26.8 |
| Southern Grid | Utility Scale | Distributed | 2022 | 29.3 |
5. Analytical method
The analysis utilizes a structured three-stage data processing pipeline:
Tidy Data Transformation: Wide-format year columns (
2018–2022) are reshaped into standard long format viapivot_longer(), and composite string variables are parsed into discrete categorical features using regular expressions viaextract().Variable Normalization: Dataset attributes are cleaned using
janitor::clean_names()to standardize column names into a consistent, machine-readablesnake_caseformat.Descriptive & Exploratory Aggregation: Summary statistics—including arithmetic means, minimums, maximums, and 5-year absolute growth spans (\(\text{max\_share} - \text{min\_share}\))—are computed across grouped regional and scale categories.
6. Visualization of the output
The below visualization consists of two faceted line charts comparing the percentage share of renewable energy adoption from 2018 to 2022 across four grid regions and two operational scale levels.
Overall Positive Trajectory Across All Regions:
- Every grid region shows a consistent, upward slope over the 5-year period, indicating steady year-over-year adoption of renewable energy regardless of scale.
Small Scale Operations (Left Panel):
Western Grid (Purple Line): Leads the small-scale category by a wide margin, growing from ~25.5% in 2018 to nearly 38% in 2022.
Eastern Grid (Red Line): Operates at a much lower baseline, rising gradually from ~11% in 2018 to ~16% in 2022.
Utility Scale Operations (Right Panel):
Northern Grid (Green Line): Dominates the utility-scale category with the highest overall renewable share in the dataset, accelerating from ~32% in 2018 to over 45% by 2022.
Southern Grid (Teal Line): Shows steady growth, rising from ~20% in 2018 to roughly 29% in 2022.
Structural Takeaways:
Scale Segregation: Regions in this dataset are strictly divided by operational scale—Western and Eastern grids are tracked under Small Scale, while Northern and Southern grids are tracked under Utility Scale.
Growth Acceleration: Utility-scale adoption (specifically Northern Grid) shows a slightly steeper growth rate compared to small-scale implementations.
| grid_region | operation_scale | mean_share | min_share | max_share | absolute_growth |
|---|---|---|---|---|---|
| Northern Grid | Utility Scale | 38.46 | 32.4 | 45.6 | 13.2 |
| Western Grid | Small Scale | 31.36 | 25.6 | 37.8 | 12.2 |
| Southern Grid | Utility Scale | 24.30 | 19.8 | 29.3 | 9.5 |
| Eastern Grid | Small Scale | 13.54 | 11.2 | 16.2 | 5.0 |
7. Analysis/ Interpretation of results in context
Across the observed five-year period from 2018 to 2022, every analyzed grid region exhibited a continuous upward trajectory in renewable energy adoption. The data demonstrates clear structural segmentation based on both geographic region and operational scale, revealing distinct adoption behaviors between utility-scale and small-scale implementations. Within the utility-scale sector, the Northern Grid stands out as the overall market leader across all measured dimensions, maintaining the highest mean renewable share at 38.46% and reaching a peak value of 45.6% in 2022. Furthermore, it recorded the fastest acceleration in the dataset, expanding by 13.2 percentage points over five years. Operating under the same utility-scale classification, the Southern Grid followed a steady upward path, progressing from 19.8% in 2018 to 29.3% in 2022 with an overall mean share of 24.30% and a total growth of 9.5 percentage points.
In contrast, the small-scale sector demonstrated significant performance variations across geographies. The Western Grid achieved exceptional results within the small-scale category, recording an average renewable share of 31.36% and an overall growth of 12.2 percentage points as it climbed from 25.6% to 37.8%. Remarkably, the Western Grid’s small-scale operations surpassed the Southern Grid’s utility-scale metrics in both overall share and growth rate, proving that operational scale alone is not the sole driver of high renewable integration. Conversely, the Eastern Grid remained the lowest-performing sector in the dataset, starting at a baseline of 11.2% in 2018, averaging only 13.54%, and capping out at 16.2% in 2022. Its total expansion of 5.0 percentage points represents the slowest growth trajectory overall. This creates a notable growth disparity across the transition, where the leading Northern Grid expanded more than 2.6 times faster than the lagging Eastern Grid over the same timeframe.
8. Conclusion
From 2018 to 2022, all grid regions steadily increased their renewable energy share, with performance driven by both operational scale and geography. The utility-scale Northern Grid led overall adoption—achieving the highest mean share (38.46%) and fastest growth (+13.2%)—while the Southern Grid saw steady, moderate gains (+9.5%). In the small-scale category, results diverged dramatically: the Western Grid expanded rapidly from 25.6% to 37.8% (outperforming the utility-scale Southern Grid), whereas the Eastern Grid lagged behind as the lowest performer, growing just 5 percentage points to reach 16.2%.