This project uses the Business Dynamics Statistics data from the US Census Bureau. Each row is one state in one year, and the file covers 47 states from 1978 to 2018 (1,927 rows). The data tracks how businesses are born, how they die, and how many jobs get created or lost along the way. For context, an establishment is a single physical location of a business, like one store, one factory, or one office. A firm can own many establishments.
These are the variables I use:
state and year: which state and which year the row describes.
net_rate (Net Job Creation Rate): jobs created minus jobs lost, as a percent of the average number of jobs. A positive number means the state added jobs that year and a negative number means it lost jobs.
entry_rate (Establishments Entered Rate): new establishments opened that year, as a percent of the average number of establishments.
exit_rate (Establishments Exited Rate): establishments that closed that year, as a percent of the average number of establishments.
What I plan to explore: Do states grow jobs faster in years when more new establishments open? Do closings pull job growth down? I will build a multiple linear regression with net_rate as the outcome and entry_rate and exit_rate as the predictors, then make graphs to look at the relationship and to compare a few of the biggest states.
Load Libraries
library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.2.1 ✔ readr 2.2.0
✔ forcats 1.0.1 ✔ stringr 1.6.0
✔ ggplot2 4.0.3 ✔ tibble 3.3.1
✔ lubridate 1.9.5 ✔ tidyr 1.3.2
✔ purrr 1.2.2
── 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
Rows: 1927 Columns: 25
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): State
dbl (24): Year, Data.DHS Denominator, Data.Number of Firms, Data.Calculated....
ℹ 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.
#Look at the first few rows to see what we gothead(raw_business_data)
# ssummary() gives min, median, mean, and maax for every colum#if a column had missing values, an extra NA row would show up for it#summary(raw_business_data)
None of the columns show an NA’s row, so there are no missing values. I will still make sure the variables I need have no NA before I use them.
The original column names are long and have spaces and dots in them, which makes them annoying to type. The next chunk makes new columns with shorter names, then keeps the columns I need.
#mutate() makes a new column & the backticks are needed because the original names have spaces in themshort_rbd<-raw_business_data|>mutate(state=State,year=Year,net_rate=`Data.Calculated.Net Job Creation Rate`,entry_rate=`Data.Establishments.Entered Rate`,exit_rate =`Data.Establishments.Exited Rate`)#select() keeaps only the columns i wantnew_rbd<- short_rbd|>select(state,year,net_rate,entry_rate,exit_rate)#keep only rows that have value for the 3 variables i need using !is.na to only look for those variable namesrbd_clean<-new_rbd |>filter(!is.na(net_rate),!is.na(entry_rate),!is.na(exit_rate))head(rbd_clean)
state year net_rate entry_rate
Length :1927 Min. :1978 Min. :-10.405 Min. : 6.899
N.unique : 47 1st Qu.:1988 1st Qu.: 0.420 1st Qu.: 9.928
N.blank : 0 Median :1998 Median : 1.929 Median :11.416
Min.nchar: 4 Mean :1998 Mean : 1.932 Mean :11.714
Max.nchar: 20 3rd Qu.:2008 3rd Qu.: 3.483 3rd Qu.:13.234
Max. :2018 Max. : 17.248 Max. :24.960
exit_rate
Min. : 6.450
1st Qu.: 8.807
Median : 9.645
Mean : 9.812
3rd Qu.:10.643
Max. :18.863
Exploring the Data
#this is a simple scaterplot of net job creation vs entry rate.the smooth line is a straight line fit(method="lm")ggplot(data=rbd_clean,aes(x=entry_rate,y=net_rate))+geom_point(alpha=0.5,color="maroon")+geom_smooth(method="lm",formula= y~x,se=FALSE,color ="black") +labs(x="Establishment Enty Rate (% of establishments)",y="Net Job Creation Rate (%)",title="Net Job Creation vs establishment Entry Rate, US States",caption ="source: US Census Bureau, Business Dynamics Statistics")+theme_minimal(base_size=12)
Each dot is one state in one year, and the black line shows the ovwrall trend. The line slopes upward, so state-years with more new estabilshments tend to have more net job growth, about 0.6 extra points of job growth for each extra point of entry rate. The points are scatered along the line so entry rate alone is not enough to tell the whole story. the regression below will look into it further
Regression Analysis
#multiple linear regression: net_rate is predicted by entry_rate plus exit_ratemodel <-lm(net_rate ~ entry_rate + exit_rate, data = rbd_clean)#Summary shows the coefficients, p-values, and adjusted R-squaredsummary(model)
Call:
lm(formula = net_rate ~ entry_rate + exit_rate, data = rbd_clean)
Residuals:
Min 1Q Median 3Q Max
-12.6495 -1.0383 0.1539 1.1653 9.8482
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 2.09118 0.31502 6.638 4.12e-11 ***
entry_rate 1.12988 0.02472 45.708 < 2e-16 ***
exit_rate -1.36518 0.04198 -32.519 < 2e-16 ***
---
Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
Residual standard error: 2.064 on 1924 degrees of freedom
Multiple R-squared: 0.5219, Adjusted R-squared: 0.5214
F-statistic: 1050 on 2 and 1924 DF, p-value: < 2.2e-16
entry_rate (1.13): this is the effect of opening more new businesses. If the entry rate goes up by 1 point (for example from 11% to 12%) and the exit rate stays the same, the model predicts net job creation goes up by about 1.13 points. Its p-value is smaller than 2e-16, which is far below 0.05, so this relationship is statistically significant
exit_rate (-1.37): this is the effect of more businesses closing. The negative sign means it pushes job growth down. If the exit rate goes up by 1 point and the entry rate stays the same, the model predicts net job creation goes down by about 1.37 points. Its p-value is also smaller than 2e-16.
Intercept (2.09): this is just the starting number for the equation, the prediction when the entry rate and exit rate are both 0. That never happens in this data, so it does not describe a real state-year and I did not read anything into it.
Adjusted R-squared (about 0.52): this tells us how well the model fits. It means entry rate and exit rate together explain about 52% of the ups and downs in net job creation. The other 48% comes from things that are not in the model, like recessions or the kinds of industries in a state.
Entry and exit rates tend to rise and fall together, since states with lots of new businesses also tend to have lots of closings. That is why each coefficient has to be read while holding the other variable constant.
Visualization: Net Job Creation in Four Big States
#there are 47 states, which is way too many boxes and colors for one graph so i keep just four of the biggest states using filter() and pipefour_states<- rbd_clean |>filter(state %in%c("California","Florida","New York","Texas"))#This isa boxplot comparing net job creation across the four states#fill= state gives each state its own color and legend#(the hex codes are from the Okabe-Ito colorblind friendly palette)ggplot(data = four_states, aes(x=state,y=net_rate,fill =state)) +geom_boxplot()+scale_fill_manual(values =c("Orange","Blue","Green","Purple"))+labs(x="State",y ="Net Job Creation Rate (%)",title="Net Job Cretaion Rate in Four large States, 1978-2018",caption="Source:US Census Buraeu, Business Dynamics Statistics",fill ="State")+theme_bw()
New York has the highest median net job creation rate (about 2.3%) and Florida is close behind (about 2.1%). California is in the middle at around 1.5%, and Texas has the lowest median at about 1.0%. Florida and New York also have the widst boxes and whiskers, so their job growth swing up and down more from yaer to year. The boxes overlap a lot thogh, so the differences between these staates are small compared to how much each state changes over time.
Conclusion
States gain more jobs in years when more new establishments open and fewer establishments close. The regression equation is net_rate = 2.09 + 1.13 (entry_rate) - 1.37 (exit_rate). Both predictors have p-values smaller than 2e-16, so the relationships are statistically significant. The adjusted R-squared of about 0.52 means the model explains roughly half of the variation in net job creation, so the other half comes from things this model does not include, like recessions, booms, and the types of industries in each state.
The boxplot of California, Florida, New York, and Texas showed that the median net job creation rate ranged from about 1.0% in Texas to about 2.3% in New York. The boxes overlap a lot, so the differences between these states are small compared to how much each state changes from year to year.
One limitation is that this is observational data, so the results show that these variables move together but cannot prove that business openings and closings cause job growth. A good next step would be adding year to the model, since national booms and recessions affect every state at the same time.
Challenges I Ran Into
Long column names. The CSV column names look like Data.Calculated.Net Job Creation Rate, with dots and spaces in them. R only accepts a name like that if it is wrapped in backticks, and leaving them off gives an error. Instead of typing backticks everywhere, I made short names once with mutate() and used those for the rest of the project.
No category to color by. Every column in the file is a number except state, and I needed a categorical variable for the colored boxplot. Coloring all 47 states would be a mess, so I used filter() with %in% to keep four large states and color those.
Picking the variables for the regression. Many columns in this file are basically the same measurement. Net job creation rate is exactly the job creation rate minus the job destruction rate, so using those as predictors would give a near perfect R-squared without telling us anything. I went with the establishment entry and exit rates instead, since they describe business activity and are not built from net job creation.
Reading the coefficients. At first I wanted to read the entry rate and exit rate effects separately, but they tend to move together. I had to remember that each coefficient in the model means the effect of that variable while the other one is held constant.
final essay
The data was already pretty clean, with one row per state per year, so I did not need to reshape it. I loaded it with read_csv() and used summary() to check for missing values (na’s). There were none, but I still used filter() with !is.na() on only the three variables I needed instead of using na.omit() or drop_na() on the whole dataset. The original column names were long and had spaces, so I used mutate() to make short names (state, year, net_rate, entry_rate, exit_rate), with backticks around the old names, and select() to keep only those five columns.
The boxplot compares net job creation rate for California, Florida, New York, and Texas from 1978 to 2018. Each box covers the middle 50% of a state’s yearly values and the line inside is the median. New York has the highest median (about 2.3%) and Texas has the lowest (about 1.0%), which surprised me because I expected Texas to be growing faster. The boxes overlap a lot, so the states differ less than each state changes from year to year. The worst years also match across states, since 2009 is one of the three lowest years for all four, which suggests national recessions hit every state at about the same time.
What went wrong and what I wish I had included: The main thing that went wrong was the filter for the four states. I typed the pipe (|>) instead of %in% and kept getting an error. I finally realized I had to switch to %in% and that fixed it. I also wish I had colored points by region and made a line graph over time, but the dataset has no region variable and I only used this CSV. I would have liked to add years to the regression to account for recessions as well.
