Google Capstone Project:
Mendocino County Poverty — Are Outsized Government Payments Creating Appalachia in Northern California?
by Vernon H. Budinger for the Google Data Analytics Certificate
Note: This document is based on a hypothetical situation, Neural Profit Engines has never been hired by the Board of Supervisors of Mendocino County. The Capstone Project is the 8th component of Google’s Data Analytics Course. Google gives program participants three options for the Capstone Project: two Google-prepared case studies and a third to create our own study using a public database. I chose the third option; I explored the U.S Census Bureau’s American Community Survey to evaluate the economy of the county where I live, Mendocino County in California. This fictional situation was created to satisfy Google’s requirements for the different phases of this project. The data is not fictional and assesses very serious economic challenges that leaders of Mendocino County face today.
Assignment
As a junior data analyst at Neural Profit Engines, a business intelligence consulting company, I have been asked to lead a project for a new client, The Board of Supervisors of Mendocino County.
The Task:
The Mendocino County Board of Supervisors hired Neural Profit Engines to examine the factors underlying Mendocino County’s subpar economic growth over the past decade. The Board has read past economic studies that discuss the County’s subpar economic growth and lack of economic opportunity in the County. The Board wants to know the economic drivers affecting the slow economic growth and hear any policy actions that the County can take to improve economic opportunity in Mendocino County.
Deliverables:
1. Analysis that provides insight into the data and identifies the economic drivers that stunt economic growth in Mendocino County.
2. Ideas for policy decisions that support economic growth.
Introduction: Questions to Frame the Assignment
Background
How poor is Mendocino County? Exhibit I informs that, in terms of GDP growth, Mendocino County ranks 20th out of 58 counties in California and stands in stark contrast to Sonoma, its neighbor to the south. The surprising aspect of this statistic is that Mendocino is adjacent to Sonoma County, one of the more affluent counties in California, yet Mendocino’s economic growth continues to lag the state and the country.
Even more surprising, in terms of economic affordability and livability, Mendocino County’s economy is one of the poorest counties in the United States and compares unfavorably to the poorest regions of Appalachia. Wheeler County, Georgia, is part of Southern Appalachia and is often ranked as the poorest county in the United States in terms of per capita income. Table I and Exhibit II show that, in terms of rent, Wheeler County is more affordable than Mendocino County.
Exhibit I
Exhibit II
Sources:
https://www.census.gov/quickfacts/mendocinocountycalifornia
https://www.census.gov/quickfacts/sonomacounty
According to the US Census Bureau, the per capita income in Mendocino County was $31,324 a year and the median gross rent is $14,112 a year. This gives a rent-based livability index of 2.22 (income 2.22 times higher than rent expense). In contrast, Wheeler County Georgia, which sits squarely in the bottom third of Appalachia, is the poorest county in the United States based solely on per capita personal income. The per capita income is $17,652 but rent is only $5,976 a year, for a livability index of 2.95 and higher than Mendocino.
How can Mendocino County be so poor when it lies adjacent to one of the most economically successful counties in California?
Key Factors
Stakeholders
Board of Supervisors
The primary stakeholders are the Board of Supervisors, composed of 5 members, who are elected to a four-year term. The Supervisors have varying levels of economic knowledge and mixed political backgrounds.
County Executives
One of the key factors to consider in this engagement is that the Client does not have executive powers to implement any program or any solutions. The client provides guidance to the Mendocino County Government, headed by the County’s Chief Executive Officer. The Board of Supervisors provides the guidance, but the County CEO and other Mendocino County Executives hold the power to implement policy.
County Residents
The ultimate stakeholders are the residents, the employees, and the business owners of Mendocino County. The final product of the economy is jobs, business opportunities, and hopefully affordable goods.
Business Task:
Use data analysis and statistical tests to identify relationships between economic opportunities, Mendocino’s economic characteristics, and policy decisions.
Business Objectives:
- What are the underlying drivers of underperformance?
- Is there a trend in economic underperformance?
- Is it a long-term trend in the drivers of economic performance?
Prepare
Identify County Level Data with Economic Data and Spending Data
Review of Past Studies:
A recent study titled “County of Mendocino Economic Development Analysis” was conducted by Beacon Economics and commissioned by West Business Development Center of Ukiah to look many of the issues but does not identify any one particular issue as the cause of lower economic growth.
The study provides these “Key Takeaways”:
- Despite Aggregate readings for the State of California, the general economic experience at the county level is direr for Mendocino.
- Mendocino is undergoing a significant demographic change at rates that outpace the state overall; outmigration and age dynamics are leading to stagnation and more recently depopulation.
- Mendocino’s labor force and job base have experienced suboptimal growth following the decline of timber-focused manufacturing.
- A lack of growth and diversification in traded, export-oriented industries will continue to diminish Mendocino over the long run.
- Staunch existential challenges — ranging from wildfires to housing affordability to broadband connectivity — are also working toward the county’s detriment.
- Despite having many agents, the local economic development ecosystem is severely fragmented. Meanwhile, there exists a grave need for a concerted effort toward tackling the county’s basal issues. Moreover, there needs to be a commitment of resources toward economic development.
I employed the “5 Whys” technique, we can explore Mendocino County’s dire prognosis. The “5 Whys” technique requires that the analyst asks why 5 times when confronted with a problem.
1. Why is economic growth low? Answer: Lack of opportunity or growth is being constrained.
2. Since other nearby counties experienced strong growth, what could constrain economic growth? Answer: government policies
3. Is there evidence of excess government? Answer yes.
4. Is government spending large relative to the rest of the county’s economy? Answer yes
5. Could government spending hinder economic growth?
A 2018 paper titled “Mendocino County: Economic and Demographic Profile” was commissioned by the Rural County Representatives of California and Golden State Finance Authority; the study was conducted by the Center for Economic Development at California State University, Chico. The paper points to a few key economic factors where Mendocino County differs from California as a whole.
- Mendocino’s income from wages as a percentage of total income is much lower than the state average.
- Government spending is much higher in Mendocino than in California and the United States on average.
- Much of the increase in government spending seems to be related to costs for an aging population.
https://www.marquetteassociates.com/impact-of-government-transfer-payments-on-disposable-income/
While there are a few factors that could be responsible for the county’s subpar economic performance, government spending seems to be at the center of the storm. This study will focus on government spending:
1. Where does Mendocino’s economy stand in relation to California and the United States?
2. How does Mendocino rank in terms of government spending as a percentage of total spending?
3. How does government spending affect Wages and Self-Employment and is there also an affect GDP growth?
This assignment requires high-quality, county-level data that is broken down into economic sectors, specifically government spending versus income from business operations across counties. California is unique in many ways and, as a result, some economic findings for California might not provide insight into general economic themes. As a result, the analysis included almost all U.S. counties so as to identify general trends.
Identify data needed and map the data sources
Planning for Data
Data Needed for an Economic Analysis of Income Sources and GDP growth in Mendocino
1. County-level data for every county in the United States
2. Economic data that breaks down income and expenditures into subclasses
a. Income from all industries
b. Types of income
c. Types of expenditures
d. Per capita and household income broken down into subsectors
The data should be broad enough to understand the general population trends but detailed by industry and production enough to allow an understanding of any subtle economic factors that could be negatively impacting the economy.
Based on our evaluation of previous studies and economic databases, our team identified four sources of high-quality data with the detail needed for this study:
1. U.S. Census Bureau
2. California Region Economic Analysis Project
3. Bureau of Economic Analysis
4. Bureau of Labor Statistics
Data Source #1: The American Community Survey is a database that was created by the United States Census Bureau. This study will utilize the Census Bureau tables to look at income and industrial production by county.
https://www.census.gov/programs-surveys/acs/
Quoting the Census Bureau:
“The American Community Survey (ACS) is an ongoing survey that provides vital information on a yearly basis about our nation and its people. Information from the survey generates data that help determine how more than $675 billion in federal and state funds are distributed each year.” The American Community Survey is conducted by the Census Bureau, the same government entity that conducts the Decennial Census.
The American Community Survey (taken directly from the ACS):
- Conducted every month
- Sent to a sample of approximately 3.5 million addresses in 50 states, the District of Columbia, and Puerto Rico
- Asks about topics not on the 2020 Census, such as education, employment, internet access, and transportation
The Decennial Census
- Conducted every 10 years
- Counts every person living in the 50 states, District of Columbia, and the five U.S.
- Asks a short set of questions
- Provides an official count of the population
Data Source #2: The California Regional Economic Analysis Project
https://california.reaproject.org/
The cornerstone for the data used on REAP are the state and county level income, earnings, employment, transfer payments and product data compiled and updated annually by the Regional Income and Product Divisions of the Bureau of Economic Analysis, U.S. Department of Commerce (Regional — BEA, DOC).
Bureau of Economic Analysis, U.S. Department of Commerce (Regional — BEA, DOC)
Data Source #3: The Bureau of Economic Analysis
The BEA data play a widespread and pivotal role in private and public sector state, regional and local area market research, economic forecasting, policy analysis and planning. Twenty states impose revenue or spending statutory limits based on the BEA state income data. In FY 2011 alone, over $339 billion in federal funds were distributed to state based upon BEA’s region income statistics
The data are comparable for all states and counties and are consistent with national totals, thus insuring the uniformity of the results deriving from the analytic approaches available on this website for calibrating, monitoring and diagnosing current and historical regional economic conditions and trends.
Bureau of Economic Analysis, U.S. Department of Commerce (Regional — BEA, DOC).
Data Source #4: The Bureau of Labor Statistics
Data Source #4: Previous studies:
County of Mendocino Economic Development Analysis
Prepared by Beacon Economics
Commissioned by West Enterprise Center, Inc.
Other data considered:
MOVE 2030:
Mendocino Opportunities for Building a Vibrant Economy
Marie Jones Consulting
November 2020
SoMo Strong 2025
Sonoma Mendocino Economic Development District
https://www.westcenter.org/wp-content/uploads/2022/06/CEDS-Public-Review-Draft.pdf
Process
The raw data is housed in the Census Bureau Databases, the Bureau of Economic Analysis, and the Real. BigQuery was not available to me for analyzing this data because the BEA data and Census Bureau tables require an API key and Big Query charges for data resources that require keys.
ROCCC Analysis of the Data
Reliable: Government data is the primary source of all the data used in this analysis. Some secondary data sources were used where the format was more convenient. The data from the U.S. Census Bureau, the Bureau of Economic Analysis, The California Regional Economic Analysis Project, and the Bureau of Labor Statistics is very reliable.
Original: In the case of the Census Bureau Data, BEA, and the Bureau of Labor Statistics, the data is original. The data from The California Regional Economic Analysis Project uses primary and secondary data (some numbers also pulled from the Census Bureau and the Bureau of Labor Statistics).
Comprehensive: The advantage of using the 5-year American Community Survey is that it is more comprehensive. While there were a few locations with missing data, they were mostly small geographic entities associated with Alaskan Native American villages or Puerto Rico.
Current: I used the ACS5 data, which is a 5-year average, it is not as current as the ACS 1-year data, but there was detailed information for all counties. While it was based on surveys and relied on sampling, the comprehensive dataset was needed and validated working with slightly older data.
Cited: The Census Bureau and the Bureau of Labor Statistics are frequently cited and used as the basis for many social and economic surveys.
Note: The estimates were another possible compromising issue for the ACS data since it is data based on a sample. To evaluate the ramifications of using sample data, I evaluated each data item with the margin of error supplied by ACS. In no case did I find that the expected possible change in errors would change the analysis.
Sampling and census data could be biased in Mendocino County as it is one leg of the Emerald Triangle, which at one time produced 45% of the cannabis in the United States according to various industry sources. A good number of Mendocino residents have spent their life living off the grid and definitely would not participate in the census. Most industry experts feel that only 10% to 15% of the mariajuana trade is reported. It could be that a a significant portion of the GDP was not reported.
I accessed the U.S. Census Bureau ACS data with R, the scripts are available in Appendix B and C. The BEA CAGDP2 data was downloaded from the BEA website into an Excel file.
Data Cleaning
Data sources were checked for missing data and suspected bad data. I chose R to evaluate and clean the ACS data because it easily handled the large datasets, and I was able to activate the API keys for no cost. I utilized Tidycensus to access the data and features of Tidyverse to evaluate and clean the Census ACS data. In addition, R features advanced graphics and sophisticated packages of analytics. I also used Excel to evaluate, manipulate and analyze the smaller components of the ACS data set.
The BEA CAGDP2 data was downloaded as an Excel table. This data was cleaned and evaluated in Excel.
The main challenge with the Census ACS and the BEA CAGDP2 data was that some of the geographic locations were so small that the data was not available and there tended to be quite a bit of missing data.
I cleaned and verified the data by sorting the data to look for outliers and I used Excel functions to calculate the Maximum, Minimum, Median, and Standard Deviation for each data series. The biggest problem with the BEA data, as with the ACS data, was the lack of data for some smaller non-county governments and cities that are classified as counties in some states. Since I had over 3,000 observations and the counties/locations with missing data were small in terms of population and economic importance, I decided to delete geographic entities with missing data. The deleted data are in Appendix A. The R analysis of all the variables is in Appendix D.
The economic growth by county was matched to the ACS data using the VLOOKUP function in Excel. I checked the matching by sampling the spreadsheet and manually recalculating all the statistics for Mendocino and other sample counties, California as a state, and the United States to verify the uploaded data, my calculations in Excel Spreadsheets, and the R statistical programing language.
It is important to note that the total of the components of income reported by the ACS does not equal exactly Aggregate Income; however, Aggregate Income serves as a government-calculated estimate for income from all sources that can be used to understand the percent contribution coming from the other components of Aggregate Income.
Analyze Data
Mendocino Data Review
After compiling and cleaning the data, I looked at the data available and examined the definitions to understand the data completely. The key objective of this exercise is to develop a strong understanding of Mendocino’s economy and the challenges that it faces.
Table II on the next page breaks down Mendocino’s economy and compares it to California and the United States. The data comes from the United States Bureau of Economic Analysis and is for 2021 — this verifies the other studies cited earlier.
In addition to alarming levels of poverty, Table II shows that Mendocino County has several distinct economic attributes and challenges:
1. With wine, lumber, and cannabis farming as the featured industries, Mendocino County is more concentrated in agriculture.
2. Real estate investing and income from real estate are more heavily weighted in Mendocino’s economy than in California or the national average.
3. Healthcare and Healthcare Services play a big role in Mendocino’s economy, most likely the result of the aging population and Mendocino’s growing reputation as a place to retire.
4. Mendocino needs more businesses that provide professional, scientific, and technical services.
5. The Manufacturing and Information industries are heavily underweighted in the County’s economic profile.
6. Government spending plays a much larger role in Mendocino’s economy than in the State as a whole and across the United States.
All these observations are consistent with previous studies. Many of the studies alluded to the possible stifling effect of government spending, so I decided to look at all the counties across the entire United States to understand the effect that government spending has on wages and economic growth. See Table II.
Evaluating Economic Growth
I accessed the Economic Growth by County in the Bureau of Economic Analysis Tables. I used the BEA query tools to download the data to my computer. I then isolated the economic data by county and created a table with economic growth by county. Mendocino County’s Gross Domestic Product (GDP) grew at almost the same rate in the decade 2011 to 2021 as in 2001 to 2010.
Table III data demonstrate that Mendocino’s economic growth has lagged California and the economy. This is important because the data shows that this is not a temporary problem but is based on long-term issues that are deeply ingrained in the county’s economy.
Exhibit III
Exhibit IV
While Mendocino’s dependence on above-normal levels of government spending is declining, government spending at the State and Federal level is also declining. The county’s dependence on government spending is also a very long-term and deeply ingrained aspect of the county’s economy.
Exhibit V
Exhibit VI
Exhibit VII
Exhibits VIII and IX rank Mendocino County relative to 2,997 counties in the United States. Note that Mendocino ranks poorly in terms of low levels of Wages and high levels of Public Assistance as a percentage of Aggregate Income. Is there evidence that government spending is crowding out other economic activities and constraining economic growth?
Exhibit VIII
Exhibit IX
Exhibit X displays the overall distribution of Public Assistance vs Wages as a percentage of Aggregate Income. Since Mendocino is buried in the 2,997 observations, Exhibit XI breaks out the data to show Mendocino’s location.
Exhibit X
Exhibit XI
Exhibit XII shows the challenges that Mendocino faces from other nearby superstar counties.
Exhibit XII
Wages as a Percent of Aggregate Income are low in Mendocino County relative to California and the rest of the United States. When the nearby counties are higher-growth counties such as Sonoma, Marin, Napa, San Francisco, and San Mateo, workers will migrate to the other counties for better wages and business opportunities.
Sharing and Explaining the Drivers of Economic Underperformance — Regression Tests
The analysis up to this point only discusses some coincidental evidence of possible causes of Mendocino’s lagging economic performance. Regression helps understand the nature of the relationship between economic performance and government spending.
Regression Results
I used regression analysis to look for a relationship between the components of economic activity and GDP growth in each county. Interestingly, there was no relationship between Wages and Self-Employment as a Percent of Aggregate Income (Wages) and GDP growth. However, there were very strong negative relationships between the sum of Social Security, Public Assistance, and Other Sources as a Percent of Aggregate Income versus County Wages as a Percent of Aggregate Income and the Growth as measured by the Change in GDP for each county from 2011 (Data Source: Bureau of Economic Analysis — the CAGPD2 Table, All Industry data — The Census Data is the B19065_001E, B19067_001E, B19070_001).
The independent variable (Social Security, Public Assistance, and Other Sources as a Percent of Aggregate Income) is used to predict Wages and Self-Employment as a Percent of Aggregate Income, which is the dependent variable.
Note: The relationship between Wages and Self-Employed Earnings and government transfer payments, such as the sum of Social Security, Public Assistance, and Other Income, is almost tautological since Aggregate Income, by definition, approximately equals the sum of Wages, Self-Employed Earnings, Public Assistance, Other Income plus a host of other variables such as Retirement Income, Dividend Income. It is expected that there will be a negative relationship between Wages and Salary as a Percent of Aggregate Income and the sum of the items considered to be transfer payments. The usefulness of this regression is to verify the relationship and to establish the strength of the relationship. See Exhibit XIII
Exhibit XIII
Exhibit XIII demonstrates that the relationship between these variables is very strong and very negative. With a coefficient of -1.316, the relationship is greater than a 1-to-1 relationship between the reduction of Wages and Self-Employed earnings when government transfers earnings increase. This means that increases in payments for Social Security, Public Assistance and Other Items decreases Wages and Self-Employment more than the increase in government spending.
I labeled Mendocino’s data point to show that the county seems to be more affected than other counties since it is almost an outlier at the edge of the other county data. This could indicate that other factors are at play in Mendocino.
The R Square data highlighted in blue in Table IV shows that the final model explains 65.5% of the variance in the volatility of Wages as a Percent of Agg Income across all counties. The yellow highlighted F Statistics indicate that the relationship is statistically significant, meaning that this is not a spurious result and that we can be very confident in the model estimates. Exhibit XV, for statistical purity, shows that there is no serious bias entering the relationship because the residuals form an even band around the X-axis.
Source: U.S. Census Bureau American Community 5 Year Survey 2017 to 2021 and BEA Industry Data
Exhibit XV
While the previous regression explored the degree to which transfer payments negatively influence wages and earnings from self-employment, it doesn’t demonstrate the possible negative economic effects when transfer payments and other government expenditures compose an outsized portion of a county’s economic income stream.
Exhibit XVI
I used the GDP growth from 2011 to 2021 at the county level as the dependent variable versus the sum of Social Security, Public Assistance, and Other Income (the independent variable) to explore the effect of proportionally high levels of income from government on economic growth. Since outliers can dramatically influence regression results in Ordinary Least Squares regression, I eliminated some observations that were outliers (most were economic growth above 2 — or two times 2010 levels (this would have made the relationship more negative). The regression results show that high levels of transfer payments negatively impacted economic growth in the period.
The negative regression coefficient -0.20684, means that increase of 1% (in yellow in Table V) in Social Security, Public Assistance, and Other Income as a proportion of Aggregate Income will depress economic growth by an estimated 0.21%. The T-Statistic is 15.277; this indicates that we can be very sure of this statistical relationship even though the predictions will not be as accurate because the R Square is low. The pronounced cloud-like pattern of blue observations around the orange trend line shows that there needs to be a large margin of error attached to estimates from the model.
The amorphous, cloud-like nature of the distribution of the residuals around the X axis in Exhibit XVII indicates that there are no serious bias issues with the model and that the model was not unduly influenced by some of the remaining outlying observations.
Exhibit XVII
Conclusions
We can reach several conclusions with a high degree of confidence:
1. Mendocino County is a relatively poor county in terms of income relative to California counties and all counties in the United States.
2. Wages relative to housing and other costs are low for California and the United States.
3. Government spending is much larger as a percentage of the County’s economy than the average for the state.
4. The high level of government-related transfer payments is depressing economic performance and wage levels in the county.
5. Wages as a percent of Aggregate Income are higher in adjacent counties and nearby high-growth regions such as Silicon Valley and San Francisco and present attractive alternatives for workers in Mendocino.
6. Mendocino County clearly spends a relatively large portion of the county budget on public assistance component of government spending.
The chicken or the egg question can be validly applied to Mendocino because the regressions, on their own, only show correlation — we must ask if government spending is high because the county is so poor, or is the county poor because government spending is high. There is no smoking gun that directly condemns government spending as the source of Mendocino County’s economic woes. However, when the sample size consists of over 2,000 counties, the relational evidence and the weight of the circumstantial evidence points to the conclusion that high government spending is impeding economic growth.
Proposed solutions (other sources are starred):
- Evaluate government spending in Mendocino County to search for fiscal items that could be released from the budget.
- Improve access and speed of the local internet. Internet access in Ukiah is monumentally slow, unreliable, and expensive.* Faster, reliable internet would provide necessary infrastructure for diversifying the County’s economy.
3. Use some funds slated for promoting Cannabis and Wine to promote Professional, Scientific, and Technical Services and diversify the economy.
4. While some government transfer payments are due to changing demographics — the population is aging as younger residents leave and as Mendocino County has become known as a place to retire — the County Government might look at avenues for reducing Public Assistance, especially regarding the homeless.*
Background:
Vernon H. Budinger has an MBA in Finance from the Stern School of Business at New York University with emphasis in Statistics and Operations Research. He also holds the CFA and CAIA designation. He has designed and led large-scale commercial projects to build mortgage-backed securities prepayment models and risk monitoring platforms.
Vernon took the Google Data Analytics Course to hone his skills and to learn new tools for Data Science - specifically to learn R, Tableau and tune up his SQL skills.
Appendix A Deleted Data
Census Bureau — American Community Survey Data Deleted
Appendix B
R Code for Retrieving ACS Data
### ACS Data
### 1 Install Packages
install.packages(“tidyverse”)
install.packages(“plotly”)
install.packages(“tidycensus”)
install.packages(“tidyBLS”)
install.packages(“bea.R”)
### Loading Packages
library(tidycensus)
library(bea.R)
census_api_key(“XXXXXXXXXXXXXXXXXXXXXXXXX”, install = TRUE)
readRenviron(“~/.Renviron”)
Help(bea.R)
##### Use Data
#### Get Decennial Data
#### ACS Data County for California
types_county_income_wide <- get_acs(
geography = “county”,
table = “B19051”,
year = 2021,
output = “wide”
)
### Get wide-form data by County
types_county_Act2_income_5yrwide <- get_acs(
geography = “county”,
variables = c(Agg_Income_19313 = “B19313_001”,
Agg_HH_Income_19025 = “B19025_001”,
Earnings_Households = “B19051_001”,
Wage_Salary_Households = “B19052_001”,
SelfEmployed_Households = “B19053_001”,
Int_Div_Inc_Households = “B19054_001”,
Soc_Sec_Households = “B19055_001”,
Sup_Soc_Sec_Households = “B19056_001”,
Public_Assistance_Inc_HH = “B19057_001”,
Pulic_Asst_Food_Stamps_SNAP = “B19058_001”,
Retirement_Income_HH = “B19059_001”,
Other_Types_Inc_HH = “B19060_001”,
Agg_Earnings_for_HH = “B19061_001”,
Agg_Wage_Salary_Inc = “B19062_001”,
Agg_Self_Employment_Inc = “B19063_001”,
Agg_Int_Div_Inc = “B19064_001”,
Agg_Social_Sec_Inc = “B19065_001”,
Agg_Sup_Social_Sec_Inc = “B19066_001”,
Agg_Public_Assistance_Inc = “B19067_001”,
Agg_Retirement_Inc = “B19069_001”,
Agg_Other_types_Inc = “B19070_001”),
output = “wide”
)
### Display ACS Data County Level Data
types_county_Act2_income_5yrwide
write.table(types_county_Act2_income_5yrwide,
file = “ACS_Income_ACT2Data_Breakdown2.csv”,
sep = “\t”,
row.names = FALSE,
)
income_breakdown_cty = (mutate(types_county_Act2_income_5yrwide, Tot_income = B19052_001 + B19054_001 ))
### variables = c(Agg_Income_19313 = “B19313_001”,
### Agg_HH_Income_19025 = “B19025_001”,
### Earnings_Households = “B19051_001”,
### Wage_Salary_Households = “B19052_001”,
### SelfEmployed_Households = “B19053_001”,
### Int_Div_Inc_Households = “B19054_001”,
### Soc_Sec_Households = “B19055_001”,
### Sup_Soc_Sec_Households = “B19056_001”,
### Public_Assistance_Inc_HH = “B19057_001”,
### Pulic_Asst_Food_Stamps_SNAP = “B19058_001”,
### Retirement_Income_HH = “B19059_001”,
### Other_Types_Inc_HH = “B19060_001”,
### Agg_Earnings_for_HH = “B19061_001”,
### Agg_Wage_Salary_Inc = “B19062_001”,
### Agg_Self_Employment_Inc = “B19063_001”,
### Agg_Int_Div_Inc = “B19064_001”,
### Agg_Social_Sec_Inc = “B19065_001”,
### Agg_Sup_Social_Sec_Inc = “B19066_001”,
### Agg_Public_Assistance_Inc = “B19067_001”,
### Agg_Retirement_Inc = “B19069_001”,
### Agg_Other_types_Inc = “B19070_001”),
###)
Appendix C
R Code for Graphics
### This Script Reads the CSV file Downloaded from ACS Mendo Income Breakdown
### Read the data for all counties ####
ACS_data_all_counties_income_breakdown_newestt_final <- read_csv(“ACS_Income_Data_Breakdown_Complete_CSV.csv”)
### plot the data for % income from wages vs % income from Public Assistance
ggplot(data = ACS_data_all_counties_income_breakdown_newestt_final)+
geom_point(mapping = aes(x = Wage_Salary_divby_Agg_Earnings, y = Public_Assistance_div_by_Agg_Earnings, shape = Decile_Label, color = Decile_Label))+
facet_grid(factor(Decile_Label, levels = c(‘Top 10%’, ‘Mendocino’, ‘Bottom 90%’))~.) +
labs(title = “Levels of Public Assistance as a Percent of Income by County”, subtitle = “Top Ten Percent of Counties Paying the Most Relative to the Bottom Ninety Percent”)+
xlab(“Wages as a Percent of Total Income”) + ylab(“Public Assistance as a Percent of Total Income”)
### annotate(“text”, x = .4,y = .003, label=”Mendocino earns less from Wages”)+
### annotate(“text”, x = .4,y = .002000, label=”and more from Public Assistance”)
### Read the data for California counties ####
ACS_data_all_CA_counties_income_breakdown <- read_csv(“ACS_Income_CA_Data_Breakdown_Complete_CSV.csv”)
ggplot(data = ACS_data_all_CA_counties_income_breakdown)+
geom_point(mapping = aes(x = Wage_Salary_divby_Agg_Earnings, y = Public_Assistance_div_by_Agg_Earnings, shape = Decile_Label, color = Decile_Label))+
facet_grid(factor(Decile_Label, levels = c(‘Top 10%’, ‘Mendocino’, ‘Bottom 90%’))~.) +
labs(title = “Levels of Public Assistance as a Percent of Income by County”, subtitle = “Top Ten Percent of Counties Paying the Most Relative to the Bottom Ninety Percent”)+
xlab(“Wages as a Percent of Total Income”) + ylab(“Public Assistance as a Percent of Total Income”)
### annotate(“text”, x = .4,y = .003, label=”Mendocino earns less from Wages”)+
### annotate(“text”, x = .4,y = .002000, label=”and more from Public Assistance”)
### Ggplot for California for counties no facets
ggplot(data = ACS_data_all_CA_counties_income_breakdown)+
geom_point(mapping = aes(x = Wage_Salary_divby_Agg_Earnings, y = Public_Assistance_div_by_Agg_Earnings, shape = Decile_Label, color = Decile_Label))+
labs(title = “Levels of Public Assistance In California as a Percent of Income by County”, subtitle = “Top Ten Percent of Counties Paying the Most Relative to the Bottom Ninety Percent”)+
xlab(“Wages as a Percent of Total Income”) + ylab(“Public Assistance as a Percent of Total Income”)
### annotate(“text”, x = .4,y = .003, label=”Mendocino earns less from Wages”)+
### annotate(“text”, x = .4,y = .002000, label=”and more from Public Assistance”)
### Decile Plot for all counties for % of public assistance
### ACS_data_all_CA_counties_income_breakdown$income_decile_PA <- get_decile
### Percent to reformat graphs
### Rerun Graph
ggplot(data = ACS_data_all_counties_income_breakdown_newestt_final)+
geom_point(mapping = aes(x = Wage_Salary_divby_Agg_Earnings_Pct, y = Public_Assistance_div_by_Agg_Earnings_Pct, shape = Decile_Label, color = Decile_Label))+
facet_grid(factor(Decile_Label, levels = c(‘Top 10%’, ‘Mendocino’, ‘Bottom 90%’))~.) +
labs(title = “Levels of Public Assistance as a Percent of Income by County”, subtitle = “Top Ten Percent of Counties Paying the Most Relative to the Bottom Ninety Percent”)+
xlab(“Wages as a Percent of Total Income”) + ylab(“Public Assistance as a Percent of Total Income”)
ACS_data_all_counties_income_breakdown_newestt_final <- mutate(ACS_data_all_counties_income_breakdown_newestt_final, Wage_Salary_divby_Agg_Earnings_Pct = Wage_Salary_divby_Agg_Earnings *100, Public_Assistance_div_by_Agg_Earnings_Pct = Public_Assistance_div_by_Agg_Earnings * 100)
##################################################################
### Mutate Example
##################################################################
###hrv <- mutate(hrv, upper_limit = round(HRVRM + 1.5 * HRVSD, 3),
### lower_limit = round(HRVRM — 1.5 * HRVSD, 3),
### lower_limit2 = round(HRVRM — .75 * HRVSD, 3))
###################################################################
### mutate(ACS_data_all_counties_income_breakdown_newestt_final,Wage_Salary_divby_Agg_Earnings_Pct = Wage_Salary_divby_Agg_Earnings *100, Public_Assistance_div_by_Agg_Earnings_Pct = Public_Assistance_div_by_Agg_Earnings * 100)
### select(ACS_data_all_counties_income_breakdown_newestt_final:Wage_Salary_divby_Agg_Earnings)
###View(ACS_data_all_counties_income_breakdown_newestt_final)
###colnames(ACS_data_all_counties_income_breakdown_newestt_final)
### small_acs_data_all_counties <- data.frame(Wage_Salary_divby_Agg_Earnings,Public_Assistance_div_by_Agg_Earnings )
##############################################################
### Plot with the new percentage number
#############################################################
ggplot(data = ACS_data_all_counties_income_breakdown_newestt_final)+
geom_point(mapping = aes(x = Wage_Salary_divby_Agg_Earnings_Pct, y = Public_Assistance_div_by_Agg_Earnings_Pct, shape = Decile_Label, color = Decile_Label))+
facet_grid(factor(Decile_Label, levels = c(‘Top 10%’, ‘Mendocino’, ‘Bottom 90%’))~.) +
labs(title = “Levels of Public Assistance as a Percent of Income by County”, subtitle = “Top Ten Percent of Counties Paying the Most Relative to the Bottom Ninety Percent”)+
xlab(“Wages as a Percent of Total Income”) + ylab(“Public Assistance as a Percent of Total Income”)
##############################################################
### Plot with the new percentage number without grd
#############################################################
ggplot(data = ACS_data_all_counties_income_breakdown_newestt_final)+
geom_point(mapping = aes(x = Wage_Salary_divby_Agg_Earnings_Pct, y = Public_Assistance_div_by_Agg_Earnings_Pct, shape = Decile_Label, color = Decile_Label))+
labs(title = “Levels of Public Assistance as a Percent of Income by County”, subtitle = “Top Ten Percent of Counties Paying the Most Relative to the Bottom Ninety Percent”)+
xlab(“Wages as a Percent of Total Income”) + ylab(“Public Assistance as a Percent of Total Income”)
###########################################################
### Graphics for California Counties
############################################################
ACS_data_all_CA_counties_income_breakdown <- mutate(ACS_data_all_CA_counties_income_breakdown, Wage_Salary_divby_Agg_Earnings_Pct = Wage_Salary_divby_Agg_Earnings *100, Public_Assistance_div_by_Agg_Earnings_Pct = Public_Assistance_div_by_Agg_Earnings * 100)
View(ACS_data_all_CA_counties_income_breakdown)
ggplot(data = ACS_data_all_CA_counties_income_breakdown)+
geom_point(mapping = aes(x = Wage_Salary_divby_Agg_Earnings_Pct, y = Public_Assistance_div_by_Agg_Earnings_Pct, shape = Decile_Label, color = Decile_Label))+
labs(title = “Levels of Public Assistance In California as a Percent of Income by County”, subtitle = “Top Ten Percent of Counties Paying the Most Relative to the Bottom Ninety Percent”)+
xlab(“Wages as a Percent of Total Income”) + ylab(“Public Assistance as a Percent of Total Income”)
Appendix D
R Data Cleaning and Exploration
