Transportation Deployment Casebook/2021/Mississippi

Mississippi StreetcarEdit

A streetcar (also commonly known as a tram, trolley or electric railway) is a mode of public transport that operates on rail tracks constructed on the street. Unlike that of buses, streetcars are restrained to the existing rail tracks and cannot reach locations that have no pre-existing tracks. Also, unlike that of other public transport systems that operate on rail networks (particularly train and metro systems) streetcars commonly operate on the streetscape in mixed traffic and share the road with pedestrians and occasionally, vehicles, as opposed to operating in isolation deep underground or on dedicated rail tracks. While historically, streetcars have operated on various power sources, they are now most commonly relying on electricity.

IntroductionEdit

Technological CharacteristicsEdit

Streetcars have been designed to serve one of two main purposes: to carry cargo and to carry passengers, from one location to another. Like that of other modes of transport, the usage of the service incurs a small fee for the user and is paid to the owner of the network. In the United States as well as around the world, streetcar networks have been commonly owned and operated by private companies. It comes without question that these networks have been constructed with the aim of maximising returns for the company. Therefore, the rail tracks are typically built in areas with greatest predicted usage to optimise on returns. This incentive is in general desirable as it allows the network to provide accessibility to the maximum number of people, creating a healthy and natural supply and demand relationship.

Advantages and disadvantagesEdit

The main advantages and disadvantages of streetcars [1] are summarised in the following table

Advantages and disadvanges of the streetcar
Advantages Disadvantages
Services are reliable and frequent and avoids congestion and traffic in dense areas Much lower capacity than other fixed rail transit services
Stop distances are relatively short and can provide good accessibility through dense areas such as the central business district. The routes are also generally very short (less than 10 miles) Short stopping distances lead to significantly lower speeds since it limits the ability to accelerate to maximum possible speeds
While not as fast as high speed rail, streetcars still operate at speeds similar to other modes of transport Not viable as a long distance service where vehicle speeds are the critical area of interes
Streetcar routes are visible, easy to understand and accessible Streetcars share the right of way with other modes of traffic and can potentially be more dangerous if traffic signage is inadequate
Streetcars attract both visitors and local users due to its simplicity Streetcars are limited by speed, carriage size and frequencies and therefore cannot provide enough capacity to be the primary mode of transport
Can provide an efficient and reliable connection between higher capacity and higher speed modes of transport
Construction costs are significantly less than that of other fixed rail transit services likes light rail and monorail
Streetcar systems can significantly contribute to economic development of their neighbourhoods
Usage of electricity as opposed to other sources of energy reduces the environmental impact of transport

HistoryEdit

Prior to the development and widespread usage of the streetcar, transport of passengers across land was most commonly conducted through that of horse drawn vehicles; the most popular of which was the omnibuses.These typically consisted of an enclosed passenger cabin with wooden benches along the sides. The cabin was supported by four wooden spoked wheels and was drawn by one or two horses. The driver was usually seated on a separate front facing bench in the outside of the cabin. Omnibuses were popular in both the United States as well as Europe throughout the eighteenth to nineteenth century.

However the horse drawn omnibus was inefficient. The force required to pull the carriage was large due to the rolling resistance between the wheels and the unpaved ground. The horses which pulled the carriages would become fatigued very fast, limiting the operation of the omnibuses to a small number hours each day. Even then, in order for such a service to be properly operated in accordance with the schedule, the companies must house and care for tens of horses for every service they operate.

As a result, omnibus services were relatively expensive. In most cities, the middle to lower class would hence often choose to walk as the primary mode of transport [2]. The lack of pavement however, meant that the commute by walking was often muddy, if not dusty. The roads were also commonly filled with manure from the horses pulling the omnibuses. This made the commute an unpleasant journey. There was hence a constant interest for growth in public transport as the demand for better means of transport was persistently there.

Invention of the streetcarEdit

The development and growth of the streetcar was a result of an amalgamation of technological advances accumulated up over the course of several centuries. The success of it is mainly owed to a combination of iron working techniques from the industrial revolution, adoption of electric powered engines and human ingenuity for combining various ideas.

While rails have been historically used as early as the sixteenth century, they were originally used to carry heavy loads of ores from mines. The rail tracks in the early invention were constructed from wood as were the wheels of the cart. The cart itself was horse drawn or pulled by a cable. However, it wasn’t until the eighteenth century that concept of horse drawn carriages on a rail system was introduced as a mode of transport inside cities. This system significantly improved the smoothness of the ride by reducing the rolling resistance between the wheels of the cart and the rail, solving the issue of horse fatigue. A larger carriage was therefore able to be drawn but a single horse as opposed to two like that of the omnibus. Companies were thus able to operate transport services at lower expenses and at the same time, provide a service with greater comfort.

In the mid eighteenth century, the invention of the steam engine led to an explosive growth known as the industrial revolution. During this time, the invention of the blast furnace significantly increased the iron production. As a result, in the late eighteenth century, iron plates and wheels began to be formed using cast iron. A layer of cast iron was commonly fixed onto the upper surface of wooden rails to form a composite wood/iron rail track. The use of cast iron in tracks was a step up from that of using wood. They could hold significantly higher loads, and had even less rolling resistance in comparison to the wooden predecessor further increasing efficiency and comfort. Cast iron however also had its corresponding set of problems. It was brittle and was prone to wear and tear. High traffic and constant use of the rail with heavy loads would lead to damage to the rails. The cast iron plates will therefore need to be replaced often, also making it a significant economical expense.

The brittleness of cast iron was solved by the invention of a new metal work process which created a ductile material known as wrought iron in the early nineteenth century. This was another improvement since the ductility of the wrought iron allowed for the tracks to last for much longer, making it more economically efficient. However, the softness of wrought iron still meant that the tracks would last for less than 10 years before requiring maintenance, repair or replacement. This was a key constraining factor in the growth of the streetcar networks.

In the mid to late nineteenth century, the Bessemer process along with the open hearth furnace was invented. The combination of these two allowed for steel to be made efficiently at a very cheap cost. Given this condition, rail tracks constructed from steel were rapidly adopted due to the desirable properties. The high strength of steel along with its durability and low cost meant that it lasted many times longer than those made from wrought iron and further came at a similar price. This resulted in strong, long lasting and smooth rail tracks, sparking an explosive growth in the rail industry in the late nineteenth century. Many railway companies started and thousands of miles of railway tracks were constructed over the next decades.

There were many attempts at utilising different power sources for the streetcar. However, over time it was found that steam engines were too large in size to fit into a small vehicle and diesel engines would be too heavy and consequently inefficient. The source of power of choice therefore quickly shifted into electricity as it was small and efficient, particularly for light carriages like that of the streetcar. By the early nineteenth century, most street railways were converted from horse operated carriages into electrified railways.

The use of electrified streetcars meant that the initial investment was within reason. The system could be set up by constructing the rail tracks, the overhead wiring which carried the electricity and finally, constructing carriages that have electric motors on board. This led to the rapid expansion of the electric streetcar industry in the United States.

Birthing phaseEdit

Market DevelopmentEdit

The birthing phase of the streetcar in Mississippi began with the adoption of horse drawn carriages along wooden and iron composite rail tracks in the late nineteenth century. These acted as a replacement for the horse drawn omnibuses [3]. The earliest cities with such systems were Greenville (1887) and Natchez (1886) where the privately owned companies Greenville Street Railway and Co. and Natchez Street Railway Co. were the respective developers and owners of the system [3]. The networks were privately owned and maintained at the company’s own expense. By 1894, Greenville Street Railway and Co. had $100,000 of authorized capital stock and maintained 6 miles of tracks with 15 horses and 9 carriages [4]. On the other hand Natchez Street Railway Co. had grown to a similar size, also owning $100,000 of authorized capital stock and maintained 5 miles of tracks along with 20 horses and 7 carriages [4].

Several other cities had companies which constructed and maintained horse car systems in the late nineteenth century but due to low ridership, the routes were abandoned and the companies which operated the systems were dissolved. This is likely because Mississippi was a rural state with a low population density making the streetcar a difficult and non economically viable business to run. The lack of natural demand for the service therefore made it hard for companies to operate in smaller cities. The only cities with streetcar systems were therefore the larger ones such as Meridian, Greenville, Natchez, Jackson and Gulfport. At any one time, there was a maximum total of a mere thirteen trolley lines across the entire state of Mississippi.

After the widespread adoption of the steel rail track and electric carriage design, there was a rapid growth in the streetcar industry across the United States. This was a process of functional discovery and enhancement where many saw the new technology as an opportunity for growth. This was also true for Mississippi. However, since the construction of such systems required large capital investments, most local governments did not provide funding for the construction. Instead, they franchised the streets to private companies who would then proceed to get chartered for a length of time, typically in the order of twenty years. Those who saw this as a profitable opportunity therefore gathered the capital and formed a chartership with the local government. As a result,  a new wave of railway companies were chartered.

The original horse drawn streetcar systems in Greenville were halted in 1899 and the construction of new electrified steel tracks began over the course of two years. The new company Delta Light and Traction Co. was chartered and thereafter, purchased the previous railway companies in Greenville and monopolised Greenville’s lighting and streetcar industry. The line was a major success and experienced growth over the course of the following decade.

Policy developmentEdit

Like that of most other states, the legislation ruling the operation of the electric streetcar in its birthing phase was borrowed from the preceding transit services, namely the omnibus and the horse drawn streetcar service. Particularly, right of way policies were in line with those of the horse drawn streetcar services. This was mostly because of the similarities between the horse drawn streetcar and the electric powered streetcar.

However, the government further introduced more policies to reduce their costs of maintenance in public infrastructure. The price which private companies needed to pay for the franchise agreement involved negotiation of terms and often required the private companies to contribute to the expansion and improvement of the quality of the public roads [2]. While the private companies often argued against these policies, they were ultimately imposed upon the operators at the signing of the franchise agreement after a reluctant agreement.

Market growth patterns similar to that of Greenville were observed in other major streetcar cities across Mississippi including Meridian, Natchez and Jackson where companies which constructed, maintained and operated the streetcar system emerged. Quickly, each city had its own corresponding private company which adopted a model where they run both the lighting and electrified streetcar services. The private companies were relatively small in size, and did not expand beyond the city due to the limitation of demand for more services. The small size of the companies led to simplistic and direct management of the services, and overall resulted in a short but successful streetcar system [4].

However, the lack of competition and monopoly of the streetcar system across an entire city naturally drove the fare prices higher. Therefore, as the system matured, the government introduced a lock-in policy in the franchise agreements to prevent the operators from increasing the fare price beyond five cents. All operators who have agreed to the franchise therefore needed to keep their fares to five cents regardless of market conditions. As a result, the streetcar system was well received by the public and opening of new services often came with great celebration [3].

Growth PhaseEdit

Public Sector ContributionEdit

While the growth of the streetcar system was smaller than that of other cities around the United States at the time due to the smaller demand in Mississippi, the government still played a key role in its development. Particularly, the government mostly controlled the planning of the systems through its franchise agreement. By restricting the path of the streetcar to certain routes, the government had control over the urban planning component of the service. Furthermore, by introducing aforementioned policies such as a maximum fare on the service, the government was able to ensure that the services were readily accessible to the general public. This planning therefore guided a path towards a more efficient economic growth.

Private Sector ContributionEdit

The private sector was the main contributor to the streetcar services. Private companies constructed, owned, maintained and operated all of the systems with an expectation to profit from the services. While they must operate under the policies that have been agreed upon from the franchise agreement, private sector companies continually sought to improve efficiency and maximise profits. This pushed private companies to extend streetcar services to areas where the private companies saw as profitable and also created an incentive for the private sector companies to improve the quality of their services to increase ridership. Railway companies would hence purchase lighting companies and provide lighting services along their routes to attract passengers. As a result, the private sector contributed greatly to the public transit network infrastructure, operation of the services as well as improvements to the quality of infrastructure in cities.

Policy IssuesEdit

The greatest conflict of interest between the public and private sector lies in the policies associated with fares. The governing body wanted ease of access at low costs while the private companies wanted to maximise profits. While the lock-in policy which limited the fares that streetcar operators could charge was not a problem to begin with, it became an issue when inflation began to occur in the twentieth century. As a result, many railway companies were no longer economically viable and were soon replaced by other forms of transport like that of the car.

Mature PhaseEdit

Due to the low population density across Mississippi, the mature phase of the streetcar network was reached relatively early, approximately around 1918. Most streetcar systems did not experience significant growth and ridership did not improve over the years. As such, there was no incentive for the private companies to continue expanding their networks as it would not be profitable.

This was further compounded with the lock-in policy for the maximum fare of 5 cents. As inflation grew, the lack of improvement in ridership meant that the private sector was gradually making declining profits and eventually, they were no longer economically viable. The onset of the streamlined automobile manufacturing from Ford Motor Co. was the final push that sent the streetcar industry into its declining phase. By 1930, the automobile industry was rapidly growing and streetcar systems were rapidly replaced by new buses that were more efficient, flexible and did not need an extensive rail infrastructure [5]. Since right of way policies haven’t changed since their horse drawn predecessors, streetcars needed to share a way with other vehicles on the road. The increase of other forms of transport including cars and buses therefore made competition for the right of way and pushed the streetcar out of service due to its lack of flexibility. As a result, between the 1930s and the 1950s, well over a hundred cities abandoned their streetcar systems and replaced it with buses and private cars [5]. All cities in Mississippi lie in this category of cities that have abandoned their streetcar systems. By the mid 1930s, there were no longer any more streecars to be seen in Mississippi [3].

Reinventing the ModeEdit

While the streetcar has been long abandoned, and replaced by more flexible services such as buses, there remains to be a list of advantages which streetcars hold. Consequently, there exists an opportunity to reinvent the streetcar for modern usage, particularly in short lengths in highly congested areas. However, policies must be changed accordingly to accommodate for this to happen. Highly congested roads in the central business district can be readily converted into streetcar systems at low costs. However, to prevent further conflicts for the right of way, it is necessary to adapt the roads so that there are no conflicts between the cars, buses and the streetcars.

Streetcar systems are also unlikely to be profitable to operate for the private sector. Funding from the public sector will therefore also be necessary for such a system to be reinvented. While the cost of capital is relatively high, the economic benefits and growth that comes along with the streetcar can potentially outweigh the costs of the line.

Quantitative AnalysisEdit

For each city in Mississippi with a streetcar system, quantitative analysis was conducted to fit the length of track in the Mississippi streetcar system to a logistic function to identify the three stages of development: the birthing, growth and mature stages. Data was collected from "McGraw electric railway manual - the red book of American street railway investment" from years 1894 to 1920 using an automated Python script where possible. The data was then checked manually and then used for curve fitting. The function that is used to fit the data is

 

where   represents the length of track in miles in the year  ,   represents the saturated length of track in the mature stage of the system, and   and   are parameters which are fitted using least squares regression in a linear form

 

Since some networks have not yet entered their mature stage, the parameter   is unknown. In such cases, we iterate through a range of possible values and maximise the correlation   in least squares regression. This optimisation problem is solved for using a brute force iterative method in Python. For each system in Mississippi over the study period, a logistic function is fitted, albeit some data shows that a logistic function may not be the best fit.

Mississippi - AggregatedEdit

The data was summed across all the systems in Mississippi and aggregated together to find the total track length in Mississippi in the years between 1894 to 1920. When there was no reports to changes in the track length of a system, it was assumed that the track has experienced no changes and therefore maintained its previous reported length. The results are summarised for every year in the table below.

 
Logistic regression of the streetcar track lengths in Mississippi.
Year    
1894 26.50 16.18
1895 26.50 18.68
1896 26.50 21.51
1897 23.50 24.67
1898 21.50 28.19
1899 28.00 32.07
1900 29.00 36.30
1901 32.00 40.87
1902 28.50 45.76
1903 36.00 50.92
1904 56.70 56.31
1905 49.75 61.86
1906 58.30 67.50
1907 82.30 73.16
1908 95.30 78.76
1909 95.70 84.22
1910 98.90 89.49
1911 106.40 94.49
1912 107.90 99.20
1913 106.40 103.58
1914 104.20 107.60
1915 104.20 111.27
1916 104.20 114.58
1917 123.51 117.54
1918 123.51 120.18
1919 123.51 122.51
1920 117.73 124.56

The coefficients of the logistic return are summarised in the table below:

       
137.4 0.16 1906.2 0.927

From the logistic regression where   is optimised, it appears that the logistic function seems to be a reaonsable fit to the data. The   value is reasonably high at 0.927 which suggests that the goodness of fit for this curve is quite high. However, that being said, there still appears to be some disrepancies between the recorded data and the fitted line. After optimsiation of the goodness of fit, it is found that the saturation length of tracks in Mississippi was around 137.4 miles. Therefore, there was still some room for growth in the years outside the study period. Some approximate years for the birthing phase was from 1882 to 1898, the growth phase was from 1898 to 1917 and the mature phase was from 1917 to 1930 before declining shortly after.

CitiesEdit

For completeness, the logistic returns analysis is conducted for each city within Mississippi as well. However, some cities like that of Natchez demonstrate that the logistic function may perhaps not be the best model to fit to. Other cities such as Greenville, Jackson and Meridian, however, demonstrate that the logistic function is a reasonable fit since the goodness of fit is relatively high.

ColumbusEdit

 
Logistic regression of the streetcar track lengths in Columbus, Ms.

Columbus appeared to experience its birthing phase quite early, before 1906. However, it wasn't until 1906 when the growth rapidly expanded and quickly reached its mature and saturated phase in 1910.

Year    
1907 4.50 3.84
1908 5.60 5.40
1909 6.70 6.25
1910 6.70 6.61
1911 6.70 6.73
1914 5.00 6.80
1917 6.80 6.80
1918 6.80 6.80

GreenvilleEdit

 
Logistic regression of the streetcar track lengths in Greenville, Ms.

Greenville initially had a horse drawn streetcar system in place. However, after the introduction of the electric streetcar, the original system was abolished and the rail lines were electrified. The birthing phase of the electric streetcar system therefore started in around 1901 before a rapid expansion from 1904 to 1911, after which the network saturated at around 7.75 miles long.

Year    
1899 0.00 0.00
1900 0.00 0.00
1901 0.00 0.00
1902 2.00 0.00
1903 2.00 0.01
1904 2.00 0.04
1905 2.00 0.15
1906 4.30 0.53
1907 4.80 1.61
1908 5.00 3.78
1909 5.00 6.01
1910 5.00 7.17
1911 7.00 7.58
1914 7.00 7.75
1917 7.75 7.75
1918 7.75 7.75
1920 7.75 7.75

GulfportEdit

 
Logistic regression of the streetcar track lengths in Gulfport, Ms.

Gulfport also appeared to experience a rapid expansion from the birthing in 1905 into its growth phase during 1906 to 1910. The Gulfport network was larger than most other cities in Mississippi and saturated at 30 miles around 1911.

Year    
1905 1.75 9.75
1906 8.00 18.99
1907 23.00 25.82
1908 25.00 28.70
1909 25.00 29.63
1910 25.00 29.89
1911 30.00 29.97
1912 30.00 29.99
1913 30.00 30.00
1914 30.00 30.00
1917 30.00 30.00
1918 30.00 30.00
1920 30.00 30.00

JacksonEdit

 
Logistic regression of the streetcar track lengths in Jackson, Ms.

Jackson also appeared to fir the logistic function reasonably well. The birthing started in around 1893 and quickly proceeded into its growth phase from about 1900 to 1910, after which the network matured. While the data is limtied to before 1920, the logistic function suggests that the network continued to mature until reaching a potential saturation length of 15.6 miles.

Year    
1894 1.50 1.87
1897 1.50 3.00
1898 1.50 3.47
1899 6.00 4.00
1900 6.00 4.58
1901 6.00 5.20
1902 6.00 5.86
1903 8.00 6.55
1904 5.20 7.27
1905 5.50 7.99
1906 5.50 8.71
1907 14.00 9.41
1908 12.00 10.09
1909 12.00 10.73
1910 12.00 11.32
1911 12.50 11.87
1912 14.00 12.37
1913 12.50 12.82
1914 14.00 13.21
1917 13.50 14.13
1918 13.50 14.35
1920 13.50 14.71

MeridianEdit

 
Logistic regression of the streetcar track lengths in Meridian, Ms.

Meridian demonstrated a high goodness of fit to the logistic function. The network birthed fairly early at around 1890 before reaching its growth phase from 1894 to 1910 over which, the length of the tracks extended to a length of about 11 miles. The network then reached its mature phase and eventually saturated at around 14 miles in length.

Year    
1898 6.00 4.65
1899 6.00 5.16
1900 6.00 5.70
1901 6.00 6.25
1902 6.00 6.81
1903 6.00 7.38
1904 7.50 7.94
1905 7.50 8.48
1906 7.50 9.01
1907 9.00 9.52
1908 11.00 10.00
1909 11.50 10.44
1910 11.50 10.85
1911 11.50 11.23
1912 11.50 11.57
1913 11.50 11.88
1914 11.50 12.16
1917 13.12 12.81
1918 13.12 12.97
1920 13.12 13.24

NatchezEdit

 
Logistic regression of the streetcar track lengths in Natchez, Ms.

Natchez was an interesting case that did not fit very well to a logistic function. The goodness of the fit is poor and the parameters of the fit do not reflect very well on the three stages in the development of the network. However, it is safe to claim that the network matured very early and stayed at a saturated length of 7 miles.

Year    
1897 6.00 6.81
1898 6.00 6.90
1899 6.00 6.94
1900 6.00 6.97
1901 4.00 6.98
1902 3.50 6.99
1903 7.00 6.99
1904 7.00 7.00
1905 7.00 7.00
1906 7.00 7.00
1907 7.00 7.00
1908 7.00 7.00
1909 7.00 7.00
1910 7.00 7.00
1911 7.00 7.00
1912 7.00 7.00
1913 7.00 7.00
1914 6.00 7.00
1917 7.00 7.00
1918 7.00 7.00

Code - Scraping the dataEdit

When a txt file of the data was avaialble, the following code was run to simplify the data extraction process. This was not perfect and thus, after extracting the data, there needed to be some manual work to clean and check the data.

import pandas as pd
import re
import us
import matplotlib.pyplot as plt

def read_file(year):
    out = None
    with open(
        f'McGraw electric railway manual - the red book of American street railway investment - {year}.txt',
        encoding="utf8"
    ) as f:
        out = pd.DataFrame(f)
        out['Year'] = year
        out[0] = out[0].apply(lambda x: x.strip())
    return out
    
final_data = None

regex_exp = re.compile(
    '.*(Miles[,]?[\ ]?of track|Miles[\ ]?of tracfc|Miles[\ ]?of tyack)' + # 1
    '([\ ]?\(?\S*\)?)?' + # 2
    '[,|\.]?' + 
    '(\ \(.*\)' + # 3
    '[,|\.]?)?' + 
    '[\ |\.]' + 
    '([0-9]*)' + # 4
    '\.?' + 
    '([0-9]*).*' # 5
)

for year in [1894, 1898, 1899, 1900, 1901, 1902]:
    df = read_file(year)

    companies = list(df.iloc[df[
        (df[0].str.lower().str.contains('co[\.]?[\ ]?—|co.$'))
    ].index][0])



    def get_company(x):
        if x in companies:
            return x
        else:
            return pd.np.nan

    df['Company'] = df[0].apply(get_company)
    df['Company'] = df['Company'].ffill()

    headings = df[
        (df[0].str.isupper() | df[0].str.islower()) &
        (df[0].str.contains('.*[,|\.].*\.$'))
    ].copy()

    next_heading = list(headings.index)
    next_heading.pop(0)
    next_heading.append(0)
    headings['Next'] = next_heading

    interest = headings[
        (headings[0].str.contains('MISS\.|HISS\.'))
    ]

    cities = []
    miles = []
    years = []
    companies = []

    for i, row in interest.iterrows(): # Iterate through all areas in the state
        strip_non_alpha = re.compile('[^a-zA-Z,\ ]')
        city = strip_non_alpha.sub('', row[0].strip().replace('\t', ' ')).strip().replace(',', '')
        query = df.iloc[i: row['Next'] + 1]
        sub_query = query[
            query[0].str.contains('Miles[,]? of t')
        ]

        if len(sub_query) == 0:
            cities.append(city)
            miles.append(-1)
            years.append(year)
            companies.append('Unknown')
            
            print(year, city)
            display(query)

        for i, data in sub_query.iterrows(): # Iterate through all the companies in the area
            try:
                mile = float('.'.join(regex_exp.match(data[0].replace('S', '5')).group(4, 5)))
            except:
                print(data[0])
            
            company = data['Company'].split('—')[0].strip()
            if len(company) > 50:
                company = company.split('.')[-2] + '.'
                
            cities.append(city.split(' ')[0])
            miles.append(mile)
            years.append(year)
            companies.append(company)

    result = pd.DataFrame({
        'year' : years,
        'city': cities,
        'miles' : miles,
        'company' : companies
    })
    
    if final_data is None:
        final_data = result
    else:
        try:
            final_data = final_data.append(result).reset_index(inplace=False, drop=True)
        except:
            display(result)
            
states = [str(i).upper() + '.' for i in us.states.STATES]

for year in range(1903, 1915):
    df = read_file(year)
    companies_list = list(df.iloc[df[
        (df[0].str.lower().str.contains('capital stock')) &
        (df[0].str.lower().str.contains('authorized'))
    ].index - 1][0])

    def get_company(x):
        if x in companies_list:
            return x
        else:
            return pd.np.nan

    df['Company'] = df[0].apply(get_company)
    df['Company'] = df['Company'].ffill()

    def get_state(x):
        if x in states:
            return x
        else:
            return pd.np.nan

    df['State'] = df[0].apply(get_state).ffill().fillna('')

    headings = df[
        (df[0].str.isupper()) &
        (df[0].str.contains('^[A-Z\ ]*\.$')) &
        (~df[0].isin(states)) &
        (
            (~df[0].str.contains('STATISTICS')) &
            (~df[0].str.contains('LIABILITIES')) &
            (~df[0].str.contains('ASSETS'))
        )
    ].copy()

    next_heading = list(headings.index)
    next_heading.pop(0)
    next_heading.append(0)
    headings['Next'] = next_heading

    interest = headings[
        (headings['State'].str.contains('MISSISSIPPI'))
    ]

    cities = []
    miles = []
    years = []
    companies = []

    for i, row in interest.iterrows(): # Iterate through all areas in the state
        strip_non_alpha = re.compile('[^a-zA-Z,\ ]')
        city = strip_non_alpha.sub('', row[0].strip().replace('\t', ' ')).strip().replace(',', '')
        query = df.iloc[i: row['Next'] + 1]
        
        sub_query = query[
            query[0].str.contains('Miles[\ ]?of')
        ]

        if len(sub_query) == 0:
            cities.append(city)
            miles.append(0)
            years.append(year)
            companies.append('Unknown')

            print(year, city)
            display(query)

        for i, data in sub_query.iterrows(): # Iterate through all the companies in the area
            try:
                mile = float(('.'.join(regex_exp.match(data[0].replace('S', '5').replace(' and ', '/')).group(4, 5))))
            except:
                print(city)
                print(year)
                print(data[0])
                mile = -1
                
            company = data['Company'].split('—')[0].strip()
            if len(company) > 50:
                company = company.split('.')[-2] + '.'

            cities.append(city)
            miles.append(mile)
            years.append(year)
            companies.append(company)

    result = pd.DataFrame({
        'year' : years,
        'city': cities,
        'miles' : miles,
        'company': companies
    })

    if final_data is None:
        final_data = result
    else:
        try:
            final_data = final_data.append(result).reset_index(inplace=False, drop=True)
        except:
            display(result)

Code - Optimising the linear regression and plotsEdit

import numpy as np
import pandas as pd
from matplotlib.ticker import (MultipleLocator, FormatStrFormatter, AutoMinorLocator)

df = pd.read_csv('data.csv')
mindex = []
for city in df['City'].unique():
    for year in range(df['Year'].min(), df['Year'].max() + 1):
        mindex.append((year, city))
df = df.groupby(['Year', 'City'], as_index=True).agg({
    'Miles' : 'sum'
})
df = df.reindex(mindex).unstack()

def autofit(S, plot=True, major_y=30, major_x=4, minor_y=15, minor_x=2, city=''):
    best_R2 = -1
    fit = None
    best_S_max = None
    
    for r in np.linspace(1 + 1e-6, 5, 1000):
        S_max = S.max() * r
        Y = np.log((S + 1e-6)/(S_max - S))
        R2 = np.corrcoef(Y.index, Y)[0][1]**2
        if R2 > best_R2:
            best_R2 = R2
            best_S_max = S_max
            b, bt0 = np.polyfit(Y.index, Y, deg=1)
            t0 = - bt0 / b
    
    S_t_fit = lambda t: best_S_max / (1 + np.exp(-b * (t - t0)))
    
    if plot:
        X = np.linspace(min(Y.index), max(Y.index), 100)
        S_fitted = S_t_fit(X)
        fig, ax = plt.subplots(figsize=(15, 8))
        ax.scatter(S.index, S, label='Recorded data')
        ax.plot(
            X, S_fitted, 
            label=f'$S(t) = \\dfrac{{{best_S_max:.1f}}}{{1 + \exp(-{b:.2f}(t - {t0:.1f}))}}$', 
            linestyle='--', color='r'
        )
        
        ax.set_ylabel(f'Total Track Length in {city.capitalize()} (Miles)')
        ax.set_xlabel('Year')
        ax.legend(loc='best', bbox_to_anchor=(0.5, 0., 0.4, 0.5))
        ax.set_title(f'A logistic function regression of the total track length across {city.capitalize()}')
        ax.xaxis.set_major_locator(MultipleLocator(major_x))
        ax.xaxis.set_minor_locator(MultipleLocator(minor_x))
        ax.yaxis.set_major_locator(MultipleLocator(major_y))
        ax.yaxis.set_minor_locator(MultipleLocator(minor_y))
        ax.grid(b=True, which='major', color='lightgray', linestyle='-')
        ax.grid(b=True, which='minor', color='lightgray', linestyle='--')
        
        ax.annotate(f'$R^2 = {best_R2:.3f}$', (min(X) + minor_x, min(S_fitted) + minor_y))
    
    out = pd.DataFrame({
        'S': S,
        'S_fit': S_t_fit(np.array(S.index)) 
    })
    return out

ReferencesEdit

[1] Parsons Brinckerhoff. Seattle Streetcar Network And Feasibility Analysis.Seattle Department of Transportation. 2004

[2] Monroe Dodd. A splendid ride - The Streetcars of Kansas City 1870 - 1957. Kansas City Star Books. 2002.

[3] Frank A. Brooks, Jr.  Stories about street cars - Southern Traction - Travelling by Trolley in Mississippi. 1983.

[4] McGraw electric railway manual - the red book of American street railway investment. 1894.

[5] Peter Spearritt. Why Melbourne Kept Its Trams. Landscapes and Ecologies of Urban and Planning history.