Code
library(readr)
library(dplyr)
library(tidyr)
library(ggplot2)
library(DBI)
library(RSQLite)
library(knitr)Macroeconomic Trend Analysis
Inflation, Unemployment & GDP | R + SQL Pipeline
Project Overview
This project builds a macroeconomic analysis pipeline using R and SQL to model an 11-month disinflation cycle (April 2025 – February 2026). A synthetic dataset was constructed to reflect plausible Federal Reserve policy responses — gradual rate reductions from 5.50% to 4.25% in response to declining inflation, with lagged effects on GDP growth, unemployment, and consumer sentiment.
The workflow covers the full analysis lifecycle: data ingestion, tidyverse wrangling, ggplot2 visualization, and SQLite-based aggregation using window functions and conditional signal classification — replicating the kind of pipeline used in economic research and financial analysis roles.
Setup
Dataset
The dataset models six macroeconomic indicators across 11 months. Variables were designed to reflect a realistic soft-landing scenario: inflation declining toward a 2% target while unemployment rises modestly and GDP decelerates.
Code
econ_data <- read_csv(I(
"month,inflation_rate,unemployment_rate,gdp_growth,consumer_sentiment,interest_rate,retail_sales_growth
2025-04,3.8,4.0,2.4,71,5.50,2.1
2025-05,3.7,4.0,2.3,72,5.50,2.0
2025-06,3.5,4.1,2.2,73,5.25,1.9
2025-07,3.4,4.1,2.1,74,5.25,1.8
2025-08,3.3,4.2,2.0,75,5.00,1.7
2025-09,3.2,4.2,1.9,76,5.00,1.6
2025-10,3.1,4.3,1.8,77,4.75,1.5
2025-11,3.0,4.3,1.8,78,4.75,1.4
2025-12,2.9,4.4,1.7,79,4.50,1.3
2026-01,2.8,4.4,1.6,80,4.50,1.2
2026-02,2.7,4.5,1.5,81,4.25,1.1"
)) %>%
mutate(month = as.Date(paste0(month, "-01")))
kable(econ_data, caption = "Table 1: Full Macroeconomic Dataset (Apr 2025 – Feb 2026)")| month | inflation_rate | unemployment_rate | gdp_growth | consumer_sentiment | interest_rate | retail_sales_growth |
|---|---|---|---|---|---|---|
| 2025-04-01 | 3.8 | 4.0 | 2.4 | 71 | 5.50 | 2.1 |
| 2025-05-01 | 3.7 | 4.0 | 2.3 | 72 | 5.50 | 2.0 |
| 2025-06-01 | 3.5 | 4.1 | 2.2 | 73 | 5.25 | 1.9 |
| 2025-07-01 | 3.4 | 4.1 | 2.1 | 74 | 5.25 | 1.8 |
| 2025-08-01 | 3.3 | 4.2 | 2.0 | 75 | 5.00 | 1.7 |
| 2025-09-01 | 3.2 | 4.2 | 1.9 | 76 | 5.00 | 1.6 |
| 2025-10-01 | 3.1 | 4.3 | 1.8 | 77 | 4.75 | 1.5 |
| 2025-11-01 | 3.0 | 4.3 | 1.8 | 78 | 4.75 | 1.4 |
| 2025-12-01 | 2.9 | 4.4 | 1.7 | 79 | 4.50 | 1.3 |
| 2026-01-01 | 2.8 | 4.4 | 1.6 | 80 | 4.50 | 1.2 |
| 2026-02-01 | 2.7 | 4.5 | 1.5 | 81 | 4.25 | 1.1 |
Visualizations
Inflation vs. Unemployment Rate
Both series were reshaped to long format using pivot_longer() before plotting. The inverse relationship between disinflation and rising unemployment is consistent with the expectations-augmented Phillips Curve.
Code
plot_data <- econ_data %>%
select(month, inflation_rate, unemployment_rate) %>%
pivot_longer(
cols = c(inflation_rate, unemployment_rate),
names_to = "metric",
values_to = "value"
)
ggplot(plot_data, aes(x = month, y = value, color = metric)) +
geom_line(linewidth = 1.2) +
geom_point(size = 2.5) +
scale_color_manual(
values = c("inflation_rate" = "#2980B9", "unemployment_rate" = "#E74C3C"),
labels = c("Inflation Rate", "Unemployment Rate")
) +
scale_x_date(date_labels = "%b '%y", date_breaks = "2 months") +
labs(
title = "Inflation vs. Unemployment Rate",
subtitle = "Apr 2025 – Feb 2026 | Synthetic Macroeconomic Dataset",
x = NULL, y = "Rate (%)", color = NULL,
caption = "Source: Simulated dataset | Analysis: R / ggplot2"
) +
theme_minimal(base_size = 12) +
theme(legend.position = "top")
GDP Growth vs. Retail Sales Growth
A line chart comparing GDP growth against retail sales growth to assess whether consumer spending aligned with output deceleration. Retail sales contracted faster than GDP — signaling weakening demand ahead of output.
Code
gdp_data <- econ_data %>%
select(month, gdp_growth, retail_sales_growth) %>%
pivot_longer(
cols = c(gdp_growth, retail_sales_growth),
names_to = "metric",
values_to = "value"
)
ggplot(gdp_data, aes(x = month, y = value, color = metric)) +
geom_line(linewidth = 1.2) +
geom_point(size = 2.5) +
scale_color_manual(
values = c("gdp_growth" = "#27AE60", "retail_sales_growth" = "#E67E22"),
labels = c("GDP Growth", "Retail Sales Growth")
) +
scale_x_date(date_labels = "%b '%y", date_breaks = "2 months") +
labs(
title = "GDP Growth vs. Retail Sales Growth",
subtitle = "Apr 2025 – Feb 2026 | Synthetic Macroeconomic Dataset",
x = NULL, y = "Percent (%)", color = NULL,
caption = "Source: Simulated dataset | Analysis: R / ggplot2"
) +
theme_minimal(base_size = 12) +
theme(legend.position = "top")
SQL Analysis
The dataset was loaded into an in-memory SQLite database via the DBI and RSQLite packages. Months are stored as character strings (YYYY-MM-DD) to avoid SQLite’s Unix day-integer conversion, which would otherwise render dates as raw integers in query output.
Code
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(
con,
"economic_indicators",
econ_data %>% mutate(month = as.character(month)),
overwrite = TRUE
)Query 1 — 3-Month Rolling Averages
AVG() OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) computes a trailing 3-month average for inflation and unemployment, smoothing short-term noise to reveal the underlying trend direction.
Code
rolling_avg <- dbGetQuery(con, "
SELECT
month,
inflation_rate,
ROUND(AVG(inflation_rate) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 3) AS inflation_3m_avg,
unemployment_rate,
ROUND(AVG(unemployment_rate) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 3) AS unemployment_3m_avg
FROM economic_indicators
")
kable(rolling_avg, caption = "Table 2: 3-Month Rolling Averages — Inflation & Unemployment")| month | inflation_rate | inflation_3m_avg | unemployment_rate | unemployment_3m_avg |
|---|---|---|---|---|
| 2025-04-01 | 3.8 | 3.800 | 4.0 | 4.000 |
| 2025-05-01 | 3.7 | 3.750 | 4.0 | 4.000 |
| 2025-06-01 | 3.5 | 3.667 | 4.1 | 4.033 |
| 2025-07-01 | 3.4 | 3.533 | 4.1 | 4.067 |
| 2025-08-01 | 3.3 | 3.400 | 4.2 | 4.133 |
| 2025-09-01 | 3.2 | 3.300 | 4.2 | 4.167 |
| 2025-10-01 | 3.1 | 3.200 | 4.3 | 4.233 |
| 2025-11-01 | 3.0 | 3.100 | 4.3 | 4.267 |
| 2025-12-01 | 2.9 | 3.000 | 4.4 | 4.333 |
| 2026-01-01 | 2.8 | 2.900 | 4.4 | 4.367 |
| 2026-02-01 | 2.7 | 2.800 | 4.5 | 4.433 |
Query 2 — Macro Signal Classification
A CASE WHEN statement classifies each month into one of three macro regimes based on GDP, retail sales, inflation, and interest rate thresholds — mirroring rule-based signal labeling used in economic policy dashboards.
Code
macro_signal <- dbGetQuery(con, "
SELECT
month,
inflation_rate,
interest_rate,
gdp_growth,
retail_sales_growth,
CASE
WHEN gdp_growth < 1.8 AND retail_sales_growth < 1.3 THEN 'Demand Softening'
WHEN inflation_rate > 3.5 AND interest_rate > 5.0 THEN 'High Inflation / Tight Policy'
ELSE 'Stable'
END AS macro_signal
FROM economic_indicators
")
kable(macro_signal, caption = "Table 3: Macro Regime Classification by Month")| month | inflation_rate | interest_rate | gdp_growth | retail_sales_growth | macro_signal |
|---|---|---|---|---|---|
| 2025-04-01 | 3.8 | 5.50 | 2.4 | 2.1 | High Inflation / Tight Policy |
| 2025-05-01 | 3.7 | 5.50 | 2.3 | 2.0 | High Inflation / Tight Policy |
| 2025-06-01 | 3.5 | 5.25 | 2.2 | 1.9 | Stable |
| 2025-07-01 | 3.4 | 5.25 | 2.1 | 1.8 | Stable |
| 2025-08-01 | 3.3 | 5.00 | 2.0 | 1.7 | Stable |
| 2025-09-01 | 3.2 | 5.00 | 1.9 | 1.6 | Stable |
| 2025-10-01 | 3.1 | 4.75 | 1.8 | 1.5 | Stable |
| 2025-11-01 | 3.0 | 4.75 | 1.8 | 1.4 | Stable |
| 2025-12-01 | 2.9 | 4.50 | 1.7 | 1.3 | Stable |
| 2026-01-01 | 2.8 | 4.50 | 1.6 | 1.2 | Demand Softening |
| 2026-02-01 | 2.7 | 4.25 | 1.5 | 1.1 | Demand Softening |
Query 3 — Month-over-Month Change
LAG() computes period-over-period deltas for inflation, unemployment, and GDP — quantifying the pace and direction of change across all key indicators.
Code
mom_change <- dbGetQuery(con, "
SELECT
month,
inflation_rate,
ROUND(inflation_rate - LAG(inflation_rate) OVER (ORDER BY month), 2) AS inflation_change,
unemployment_rate,
ROUND(unemployment_rate - LAG(unemployment_rate) OVER (ORDER BY month), 2) AS unemployment_change,
gdp_growth,
ROUND(gdp_growth - LAG(gdp_growth) OVER (ORDER BY month), 2) AS gdp_change
FROM economic_indicators
")
kable(mom_change, caption = "Table 4: Month-over-Month Change — Key Indicators")| month | inflation_rate | inflation_change | unemployment_rate | unemployment_change | gdp_growth | gdp_change |
|---|---|---|---|---|---|---|
| 2025-04-01 | 3.8 | NA | 4.0 | NA | 2.4 | NA |
| 2025-05-01 | 3.7 | -0.1 | 4.0 | 0.0 | 2.3 | -0.1 |
| 2025-06-01 | 3.5 | -0.2 | 4.1 | 0.1 | 2.2 | -0.1 |
| 2025-07-01 | 3.4 | -0.1 | 4.1 | 0.0 | 2.1 | -0.1 |
| 2025-08-01 | 3.3 | -0.1 | 4.2 | 0.1 | 2.0 | -0.1 |
| 2025-09-01 | 3.2 | -0.1 | 4.2 | 0.0 | 1.9 | -0.1 |
| 2025-10-01 | 3.1 | -0.1 | 4.3 | 0.1 | 1.8 | -0.1 |
| 2025-11-01 | 3.0 | -0.1 | 4.3 | 0.0 | 1.8 | 0.0 |
| 2025-12-01 | 2.9 | -0.1 | 4.4 | 0.1 | 1.7 | -0.1 |
| 2026-01-01 | 2.8 | -0.1 | 4.4 | 0.0 | 1.6 | -0.1 |
| 2026-02-01 | 2.7 | -0.1 | 4.5 | 0.1 | 1.5 | -0.1 |
Query 4 — Pearson Correlation (Inflation vs. Unemployment)
Manual computation of the Pearson correlation coefficient between inflation and unemployment directly in SQL — no R post-processing required. A negative result confirms the inverse Phillips Curve relationship present in the data.
Code
corr_result <- dbGetQuery(con, "
SELECT
ROUND(
(AVG(inflation_rate * unemployment_rate) - AVG(inflation_rate) * AVG(unemployment_rate))
/
(SQRT(AVG(inflation_rate * inflation_rate) - AVG(inflation_rate) * AVG(inflation_rate))
* SQRT(AVG(unemployment_rate * unemployment_rate) - AVG(unemployment_rate) * AVG(unemployment_rate))),
4) AS pearson_corr_infl_unemp
FROM economic_indicators
")
kable(corr_result, caption = "Table 5: Pearson Correlation — Inflation vs. Unemployment")| pearson_corr_infl_unemp |
|---|
| -0.9851 |
Key Findings
| Metric | Change | Interpretation |
|---|---|---|
| Inflation | -1.1 pp | Sustained disinflation toward 2% target |
| Unemployment | +0.5 pp | Modest labor market softening; soft-landing dynamic |
| GDP Growth | -0.9 pp | Steady deceleration; demand-side weakness in final months |
| Rate Cuts Modeled | 125 bps | 5 cuts of 25 bps across the easing cycle |
| Infl/Unemp Correlation | -0.9851 | Negative — Phillips Curve relationship confirmed |
Soft-landing signal: Inflation fell meaningfully while unemployment rose only modestly (+0.5 pp), consistent with a controlled disinflation scenario rather than a recession-driven correction. The final two months were classified as Demand Softening by the SQL signal logic, suggesting the economy was approaching — but had not yet entered — a contractionary phase.
Built with R · ggplot2 · dplyr · SQLite · Quarto