This series of articles is devoted to the study of the construction activity of the main city of Silicon Valley - San Francisco. San Francisco is the technological “Moscow” of our world, on the example of which (with the help of open data) one can observe the development of the construction industry in large cities and capitals.
The construction of graphs and calculations was carried out in (on the Kaggle.com platform).
Data on more than a million building permits (records in two datasets) from the San Francisco Building Department - allow analyze not only construction activity in the citybut also critically consider recent trends and development history of the construction industry over the past 40 years, between 1980 and 2019.
Open data provides an opportunity to explore the main factors that influenced and will influence the development of the construction industry in the city, dividing them into “external” (economic booms and crises) and “internal” (the influence of holidays and seasonal-annual cycles).
Content
Open data and review of initial parameters.
This is not a translation of the article. I write on LinkedIn and in order not to create graphs in several languages, all graphs are in English.
Link to the second part:
City of San Francisco building permit data - taken from Open Data Portal - . The portal has several datasets on the topic of construction. Two such datasets store and update data on permits issued for the construction or repair of objects in the city:
- (850 thousand records)
- (280k records, data uploaded and updated weekly)
These datasets contain information on issued building permits, with various characteristics of the object for which the permit is issued. Total number of entries (permissions) received in the period 1980-2019 - 1 permits.
The main parameters from this dataset that were used for the analysis are:
- permit_creation_date – date of creation of the application (in fact, the day from which construction work begins)
- description — description of the application (two or three keywords describing the object of construction (work) for which the permit was created)
- estimated_cost - estimated (estimated) cost of construction work
- revised_cost — revised cost (cost of work after revaluation, increase or decrease in the initial volumes according to the application)
- existing_use - type of housing (one-, two-family house, apartments, offices, production, etc.)
- zip code, location – postal code and object coordinates
Annual construction activity in San Francisco
In the graph below, the data on the parameters estimated_cost и revised_cost presented as a distribution of the total cost of work by month.
data_cost_m = data_cost.groupby(pd.Grouper(freq='M')).sum()To reduce monthly “outliers”, monthly data are grouped by years. The graph of the amount of money invested over the years has received a more logical and analyzable form.
data_cost_y = data_cost.groupby(pd.Grouper(freq='Y')).sum()
According to the annual movement of the sum of costs (of all permits for the year) to urban facilities clearly visible economic factors that from 1980 to 2019 influenced on the number and cost of construction projects, or otherwise on investments in real estate in San Francisco.
The number of building permits (number of construction work or number of investments) over the past 40 years has been closely related to economic activity in Silicon Valley.
The first peak of construction activity was associated with the electronics hype of the mid-80s in the valley. The ensuing downturn in electronics and banking in 1985 sent the regional real estate market into a decline from which it did not recover for nearly a decade.
After that, two more times (in 1993-2000 and 2009-2016) before the collapse of the Dotcom bubble and the technological boom of recent years San Francisco's construction industry has gone through a parabolic growth of several thousand percent.
Removing intermediate peaks and valleys and leaving the minimum and maximum values in each economic cycle, you can see how big market fluctuations have plagued the industry for the last 40 years.
The biggest increase in investment in construction came during the dot-com boom, when over the period from 1993 to 2001 $ 10 billion was invested in repairs and construction, or about $ 1 billion a year. If we count in square meters (the cost of 1 m² in 1995 was $ 3000) - this is approximately 350 m000 per year for 2 years, starting from 10.
The growth of annual total investments for this period amounted to 1215%.
The firms that rented construction equipment during this period were similar to the offices that sold shovels during the gold rush (in the same region in the middle of the 19th century). Only instead of shovels - in the 2000s there were already cranes and concrete pumps for the newly formed construction companies that wanted to cash in on the construction boom.
After each of the many crises that the construction industry has experienced over the years, over the next two post-crisis years, investments (sum of applications for permits) in construction dropped by at least 50% each time.
The biggest crises in the construction industry in San Francisco came in the 90s. Where, with a frequency of 5 years, the industry either fell (-85% in the period 1983-1986), then rose again (+895% in the period 1988-1992), remaining in annual terms in 1981, 1986, 1988, 1993 - on the same level.
Already after 1993, all subsequent recessions in the construction industry amounted to no more than 50%. But approaching economic crisis (due to COVID-19) could create a record crisis in the construction industry in the period 2017-2021, the fall of which already for the period 2017-2019 is more than 60% in total.
Population growth in San Francisco dynamics in the period 1980-1993 also showed almost exponential growth. The economic strength and innovative vigor of Silicon Valley has been a solid foundation on which to build the hyperbole of the new economy, the American renaissance, and dot-coms. It was the epicenter of the new economy. But unlike the growth in real estate investment, after the peak of the dot-coms, the population actually plateaued.
If before the dot-com peak in 2001, since 1950, the annual population growth has been approximately 1% per year. After the bubble collapsed, the influx of new population slowed down and since 2001 has been only 0.2 percent per year.
In 2019 (for the first time since 1950), the growth dynamics showed an outflow of the population (-0.21% or 7000 people) from the city of San Francisco.
Expectation and reality in the preparation of the estimated cost
In the datasets used, the data on the cost of a building permit are divided into:
- initial estimated cost (estimated_cost)
- cost of work after revaluation (revised_cost)
During boom times, the main purpose of revaluation is to increase the initial cost, when the investor (customer of construction) shows an appetite after the start of construction.
During the crisis, they try not to exceed the estimated costs, and the initial estimates practically do not change. (with the exception of the 1989 earthquake).
According to the schedule built on the difference between the revalued and estimated cost (revised_cost - estimated_cost), one can observe that:
The amount of the increase in the value of the revaluation of the volume of construction work is directly dependent on economic boom cycles
data_spread = data_cost.assign(spread = (data_cost.revised_cost-data_cost.estimated_cost))
During periods of rapid economic growth, customers (investors) spend their money generously enough, increasing their requests after the start of work.
The customer (investor), feeling financially confident, asks the building contractor or architect to extend the already issued building permit. This may be a decision to increase the original length of the pool or increase the area of the house (already after the start of work and the issuance of a building permit).
At the peak of dot-coms, such “extra” expenses reached an “extra” 1 billion a year.
If you look at this table already in percentage change, then the peak increase in the estimate (100% or 2 times of the original estimated cost) occurred in the year before the earthquake that occurred in 1989 near the city. I suppose that after the earthquake, the construction projects that were started in 1988 required more time and money for implementation after the earthquake in 1989.
Conversely, a downward revision of the estimated cost (which happened only once during the period from 1980 to 2019) a few years before the earthquake, presumably due to the fact that some objects started in 1986-1987 were frozen or investments in these objects were cut down. On schedule on average for each object started in 1987 - the reduction in the estimated cost amounted to -20% of the original plan.
data_spred_percent = data_cost_y.assign(spred = ((data_cost_y.revised_cost-data_cost_y.estimated_cost)/data_cost_y.estimated_cost*100))
An increase in the initial cost estimate of more than 40% indicated or may have been a consequence of the approaching bubble in the financial and subsequent construction market.
What is the reason for the decrease in the spread (difference) between the estimated and revised cost after 2007?
Perhaps investors began to carefully look at the numbers (the average amount over 20 years has grown from $ 100 thousand to $ 2 million) or perhaps the construction department, preventing and slowing down emerging bubbles in the real estate market, introduced new rules and restrictions to reduce possible manipulations. and possible risks that will arise in the crisis years.
Construction activity depending on the season of the year
By grouping the data by calendar weeks of the year (54 weeks), you can observe the construction activity of the city of San Francisco depending on seasonality and time of year.
By Christmas, all construction organizations are trying to get permission for new “large” objects (at the same time! the number! of permits in the same months is at the same level throughout the year). Investors, planning to receive their object during the next year, conclude contracts in the winter months, counting on big discounts (since summer contracts, for the most part, come to an end by the end of the year and construction companies are interested in receiving new applications).
Before Christmas, the largest amounts in applications are submitted (an increase from an average of 1-1,5 billion per month to 5 billion in December alone). At the same time, the total number of applications by month remains at the same level. (See below section: statistics on total applications by month and day)
After the winter holidays, the construction industry is actively (almost without an increase in the number of permits) planning and implementing "Christmas" orders, so that by the middle of the year (before the Independence Day holiday) - to have time to free up resources before starting immediately after the June holidays - a new wave of summer contracts.
data_month_year = data_month_year.assign(week_year = data_month_year.permit_creation_date.dt.week)
data_month_year = data_month_year.groupby(['week_year'])['estimated_cost'].sum()
The same percentage data (orange line) also shows that the industry is running “flat” throughout the year, but before and after the holidays, permit activity increases to 150% between week 20-24 (before Independence Day), and decreases immediately after the holiday up to -70%.
Before Halloween and Christmas, activity in the construction industry in San Francisco week 43-44 increases by 150% (bottom to peak) and then decreases to zero during the holidays.
Thus, the industry is in a semi-annual cycle, which is separated by the holidays “US Independence Day” (week 20) and “Christmas” (week 52).
Total investment in San Francisco real estate
Based on city building permit data:
The total investment in construction projects in San Francisco from 1980 to 2019 is $91,5 billion.
sf_worth = data_location_lang_long.cost.sum()
The total market value of all residential property in San Francisco assessed for property tax (is the assessed value of all real estate and all personal property owned by San Francisco) .
Which areas of San Francisco have invested in over the past 40 years
With the help of the Folium library, let's see where these $91,5 billion were invested by region. To do this, grouping the data by postal code (zipcode), we present the obtained value using circles (the Circle function from the Folium library).
import folium
from folium import Circle
from folium import Marker
from folium.features import DivIcon
# map folium display
lat = data_location_lang_long.lat.mean()
long = data_location_lang_long.long.mean()
map1 = folium.Map(location = [lat, long], zoom_start = 12)
for i in range(0,len(data_location_lang_long)):
Circle(
location = [data_location_lang_long.iloc[i]['lat'], data_location_lang_long.iloc[i]['long']],
radius= [data_location_lang_long.iloc[i]['cost']/20000000],
fill = True, fill_color='#cc0000',color='#cc0000').add_to(map1)
Marker(
[data_location_mean.iloc[i]['lat'], data_location_mean.iloc[i]['long']],
icon=DivIcon(
icon_size=(6000,3336),
icon_anchor=(0,0),
html='<div style="font-size: 14pt; text-shadow: 0 0 10px #fff, 0 0 10px #fff;; color: #000";"">%s</div>'
%("$ "+ str((data_location_lang_long.iloc[i]['cost']/1000000000).round()) + ' mlrd.'))).add_to(map1)
map1
It can be seen from the regions that most of the pie logically went to DownTown. Simplified grouping all objects according to the distance to the city center and the time it takes to get to the city center (of course, expensive houses are also built on the coast), all permits were divided into 4 groups: 'Downtown', '<0.5H Downtown', '< 1H Downtown', 'Outside SF'.
from geopy.distance import vincenty
def distance_calc (row):
start = (row['lat'], row['long'])
stop = (37.7945742, -122.3999445)
return vincenty(start, stop).meters/1000
df_pr['distance'] = df_pr.apply (lambda row: distance_calc (row),axis=1)
def downtown_proximity(dist):
'''
< 2 -> Near Downtown, >= 2, <4 -> <0.5H Downtown
>= 4, <6 -> <1H Downtown, >= 8 -> Outside SF
'''
if dist < 2:
return 'Downtown'
elif dist < 4:
return '<0.5H Downtown'
elif dist < 6:
return '<1H Downtown'
elif dist >= 6:
return 'Outside SF'
df_pr['downtown_proximity'] = df_pr.distance.apply(downtown_proximity)Of the 91,5 billion invested in the city, almost 70 billion (75% of all investments) invested in renovation and construction are in the city center (green zone) and to the city area within a radius of 2 km. from the center (blue zone).
Average estimated cost of a construction application by city district
All data, as in the case of the total investment amount, was grouped by postal code. Only in this case with the average (.mean()) estimated cost of the application by zip code.
data_location_mean = data_location.groupby(['zipcode'])['lat','long','estimated_cost'].mean()
In ordinary areas of the city (more than 2 km from the city center) - the average estimated cost of a construction application is $ 50 thousand.
The average estimated cost in the city center area is about three times higher ($ 150 thousand to $ 400 thousand) than in other areas ($ 30-50 thousand).
In addition to the cost of land, three factors determine the overall cost of building a home: labor, materials, and government fees. These three components are higher in California than in the rest of the country. California building codes are considered among the most comprehensive and stringent in the country (due to earthquake and environmental regulations), often requiring more expensive materials and labor.
For example, governments require builders to use higher quality building materials (windows, insulation, heating and cooling systems) to achieve high energy efficiency standards.
From the general statistics on the average cost of an application for a permit, two locations stand out:
- Treasure Island is an artificial island in San Francisco Bay. The average estimated cost of a building permit is $6,5 million.
- Mission bay - (2926 people live) The average estimated cost of a building permit is $ 1,5 million.
In fact, the high average application in these two areas is due with the least number of applications for these postal locations (145 and 3064 respectively, construction on the island is very limited), while for the rest of the postcodes - XNUMXand the period 1980-2019 received approximately 1300 applications per year (on average 30-50 thousand applications for the entire period).
According to the “number of applications” parameter, a perfectly even distribution of the number of applications per postal code throughout the city is noticeable.
Statistics on the total number of applications by month and day
General statistics on the total number of applications by month and day of the week in the period from 1980 to 2019 shows that the most “calm” months for the construction department are the spring and winter months. At the same time, the amount of investments indicated in the applications varies greatly, and differs from month to month at times (see additionally “Construction activity depending on the season of the year”). Among the days of the week on Monday, the load on the department is approximately 20% less than on the rest of the week.
months = [ 'January', 'February', 'March', 'April', 'May','June', 'July', 'August', 'September', 'October', 'November', 'December' ]
data_month_count = data_month.groupby(['permit_creation_date']).count().reindex(months)
While the number of applications in June and July is practically the same, in terms of the total estimated cost, the difference reaches 100% (4,3 billion in May and July and 8,2 billion in June).
data_month_sum = data_month.groupby(['permit_creation_date']).sum().reindex(months)
The future of the San Francisco construction industry, predicting activity by patterns.
Finally, let's compare the construction activity chart in San Francisco with the Bitcoin price chart (2015-2018) and the gold price chart (1940-1980).
Pattern (from the English pattern - model, sample) - in technical analysis, stable recurring combinations of price, volume or indicator data are called. Pattern analysis is based on one of the axioms of technical analysis: "history repeats itself" - it is believed that repeated combinations of data lead to a similar result.
The main pattern that can be guessed on the annual activity chart is this is a “Head and Shoulders” trend reversal pattern. So named because the chart looks like a human head (peak) and shoulders on the sides (smaller peaks). When the price breaks through the line connecting the bottoms, the pattern is considered completed, and the movement is likely to take place down.
The movement of activity in the construction industry in San Francisco almost completely coincides with the growth schedule for the price of gold and bitcoin. The historical performance of these three price action and activity charts show remarkable similarities.
To be able to predict the behavior of the construction market in the future, it is necessary to calculate the correlation coefficient with each of these two trends.
Two random variables are called correlated if their correlation moment (or correlation coefficient) is different from zero; and are called uncorrelated quantities if their correlation moment is zero.
If the resulting value is closer to 0 than to 1, then it makes no sense to talk about a clear pattern. This is a complex mathematical problem, which may be taken up by older comrades who may be interested in this topic.
If! unscientific! look at the topic of the further development of the construction industry in San Francisco: if the pattern coincides further with the price of bitcoin, then in this pessimistic scenario — to get out of the crisis of the construction industry in San Francisco will not be easy in the near post-crisis time.
With a more "optimistic" version development, a re-exponential growth of the construction industry is possible if activity here follows the “gold price” scenario. In this variant, in 20-30 years (perhaps in 10) the construction industry will experience a new surge in employment and development.
I will take a closer look at individual construction sectors (repair of roofs, kitchens, construction of stairs, bathrooms, if you have any wishes by industry or other data, please write in the comments) and compare inflation for individual types of work with fixed rates on mortgage loans and government yields US bonds (Fixed Mortgage Rates & US Treasury Yield).
Link to the second part:
Link to Jupyter Notebook:
Please, those who are with Kaggle - put a Notebook plus (Thank you!).
(Comments and explanations on the code will be added to the Notebook later)
Link to English version:
If you like my content, please consider buying me a coffee.
thanks for your support!
Only registered users can participate in the survey. , you are welcome.
What is the future of the San Francisco construction industry?
-
66,7%The construction sector is more likely to follow the path of Bitcoin2
-
0,0%The construction sector may follow the path of gold prices0
-
0,0%The sector expects a hype over the next 10 years0
-
33,3%The development of the sector is not going according to patterns1
3 users voted. 6 users abstained.
Source: habr.com