HIS480 logo

HISTORICAL ANALYSIS WITH SPREADSHEETS

Copyright 2006, 2011 by Dr. Jim Jones
of West Chester University


Go to the HIS480 Syllabus or Assignments.

INTRODUCTION

Far too often, when scholars, politicians and other experts cite statistics to support their arguments, most people (including historians) skip the charts and tables to focus on the text. With a spreadsheet, checking the accuracy of statistical arguments is easy, and it allows you to evaluate other people's arguments. Using a spreadsheet may even allow you to derive your own original conclusions. The following examples illustrate how.

CRITICAL ANALYSIS USING A SPREADSHEET

Post-World War II West Chester: On page 7 of the West Chester Directory, 1964 (Chillicothe OH & West Chester PA: Mullin-Kille of Pennsylvania & Daily Local News, 1964), there is a table called "Population Growth of Greater West Chester." Besides the dates when West Chester and the surrounding townships were founded, and their area in square miles, the table contains US Census data for population from 1930, 1940, 1950 and 1960. A quick look at the data shows that all seven municipalities grew during this period, but analysis using a spreadsheet reveals much more.

Start by putting the names and data on area and population into a spreadsheet. The result should look like this:

MUNICIPALITY AREA 1930 1940 1950 1960
East Bradford Township 15.4 906 1,033 1,187 1,713
East Goshen Township 10.5 739 867 1,039 1,694
Thornbury Township 3.9 244 233 297 746
West Chester Borough 1.8 12,325 13,289 15,168 15,705
West Goshen Township 12.1 1,958 2,456 3,542 8,214
Westtown Township 8.7 785 912 994 1947
West Whiteland Township 13.0 928 1,078 1,573 4,412

Now start asking questions. For instance, "did the population grow at the same rate in all seven municipalities between 1930 and 1960?" To determine this, add a new column on the right side and label it "Ratio 1960/1930" and then for each municipality, insert a formula that divides the 1960 population by the 1930 population. This results in the following speadsheet, where the shaded cells contain formulas, not actual numbers:

MUNICIPALITY AREA 1930 1940 1950 1960 Ratio 1960/1930
East Bradford Township 15.4 906 1,033 1,187 1,713 1.89
East Goshen Township 10.5 739 867 1,039 1,694 2.29
Thornbury Township 3.9 244 233 297 746 3.06
West Chester Borough 1.8 12,325 13,289 15,168 15,705 1.27
West Goshen Township 12.1 1,958 2,456 3,542 8,214 4.20
Westtown Township 8.7 785 912 994 1947 2.48
West Whiteland Township 13.0 928 1,078 1,573 4,412 4.75

Even though West Chester had that largest population, it grew slower than the surrounding municipalities. The fastest growth was in West Whiteland, followed by West Goshen, Thornbury, Westtown, East Goshen and East Bradford. (Note that if you needed to, you could use your spreadsheet to sort your rows from highest "Ratio 1960/1930" to lowest.) To explain why some places grew faster than others, you should look at a map to see where they are, and notice the location of major roads, industrial zones and other things that would attract people to an area.

Was the rate of growth contant over the thirty year period, or was growth faster in some decades than others? To answer this, you need to add three more columns that show the "Ratio 1940/1930," "Ratio 1950/1940," and "Ratio 1960/1950." While you are at it, add a row at the bottom that contains the totals for each of your original data items, and perform the same calculations on those totals that you did for each municipality.

MUNICIPALITY AREA 1930 1940 1950 1960 Ratio 1960/1930 Ratio 1940/1930 Ratio 1950/1940 Ratio 1960/1950
East Bradford Township 15.4 906 1,033 1,187 1,713 1.89 1.14 1.15 1.44
East Goshen Township 10.5 739 867 1,039 1,694 2.29 1.17 1.20 1.63
Thornbury Township 3.9 244 233 297 746 3.06 0.95 1.27 2.51
West Chester Borough 1.8 12,325 13,289 15,168 15,705 1.27 1.08 1.14 1.04
West Goshen Township 12.1 1,958 2,456 3,542 8,214 4.20 1.25 1.44 2.32
Westtown Township 8.7 785 912 994 1947 2.48 1.16 1.09 1.96
West Whiteland Township 13.0 928 1,078 1,573 4,412 4.75 1.16 1.46 2.80
Total 65.4 17,785 19,868 23,800 34,431 1.93 1.11 1.20 1.45

This reveals several things. Growth in every muncipality was faster in the 1950s than it was in the previous two decades. It was slowest in the 1930s -- in fact, Thornbury Township actually lost population -- and somewhere in between during the 1940s. Notice also that there were two exceptions, West Chester and Westtown, where the rate of growth did not increase consistently throughout the three decades. Applying what you know about each of those decades -- the 1930s was a time of Depression, the 1940s was a period of growth due to World War II, and the 1950s was a time when the suburbs began to form -- it becomes possible to suggest some hypotheses. Why, for example, was West Chester the only municipality to grow faster in the 1940s than in the 1950s?

Since our focus is on West Chester, think about this question: "How did the distribution of population change between 1930 and 1960?" In other words, do these figures show that most people lived in town in 1930, but that had changed by 1960? To figure this out, you need to know (from looking at a map) that of the seven municipalities listed in this spreadsheet, the only one with an urban center was West Chester Borough. As a consequence, one way to answer this question is to show what percentage of the area's population lived in West Chester in each of the census years. Do this by adding another row to the bottom of your spreadsheet and calling it "Ratio WC/area." Then place a formula in each cell that divides the number in the "Total" row by the number in the "West Chester Borough" row. (Note that this is only useful for comparing actual data, not calculated ratios.)

MUNICIPALITY AREA 1930 1940 1950 1960 Ratio 1960/1930 Ratio 1940/1930 Ratio 1950/1940 Ratio 1960/1950
East Bradford Township 15.4 906 1,033 1,187 1,713 1.89 1.14 1.15 1.44
East Goshen Township 10.5 739 867 1,039 1,694 2.29 1.17 1.20 1.63
Thornbury Township 3.9 244 233 297 746 3.06 0.95 1.27 2.51
West Chester Borough 1.8 12,325 13,289 15,168 15,705 1.27 1.08 1.14 1.04
West Goshen Township 12.1 1,958 2,456 3,542 8,214 4.20 1.25 1.44 2.32
Westtown Township 8.7 785 912 994 1947 2.48 1.16 1.09 1.96
West Whiteland Township 13.0 928 1,078 1,573 4,412 4.75 1.16 1.46 2.80
Total 65.4 17,785 19,868 23,800 34,431 1.93 1.11 1.20 1.45
Ratio WC/Area .03 .69 .67 .64 .46        

The results in the pink cells show, first of all, that with only 3/100ths of the land in the area, West Chester had more than two-thirds of the area's population as late as 1940, so it was much more "urban" than the surrounding townships. West Chester's share was still close to two-thirds in 1950, but during the next decade, as the area's population grew 45%, West Chester's share of that population dropped sharply. The population growth must have come in rural townships -- a phenomena that became known as "suburban sprawl" and which can be observed in many other sources such as advertisements for new homes, requests for building approvals, statistics on automobile use, and even aerial photographs like these (source: PennPilot ).

West Goshen Township at the junction of
Gay Street and West Chester Pike in 1937 and 1958

 


Analyzing Apartheid: This example relies on data from a book published by the South African government called Progress Through Separate Development, 4th edition (New York: Information Sevice of South Africa, 1973). In 1973, South Africa's government still followed a policy called apartheid which separated people by race. As the title suggests, this book was intended to promote the policy and to respond to South Africa's critics. A sample quotation from page 127 reveals the spirit of the book:

The degree of economic and other cooperation between South Africa and her immediate neighboring states is far advanced. These states not only form a customs union with South Africa, but also a monetary union. The Republic provides employment for a large percentage of their labor force, and also a ready market for their produce. Directly and indirectly, they benefit from the South African infrastructure such as transport, harbors, power grid, communications, health services, industry and technology, and research institutions in virtually every field of activity. ...

Assistance has been given in the form of, for example, famine relief, supply of electricity, provision of health and welfare services, signing of trade agreements to ensure markets for agricultural produce, the planning, designing and construction of various projects, and the provision of low-interest loans, while the private sector in South Africa has been responsible for the opening of hotels, factories, plants and works, as well as the construction of railway lines and the building up of a physical infrastructure.

The phrase "neighboring states" refers to the African "homelands" which were previously portions of South Africa until they were granted "independence" by the white-controlled national parliament. Black Africans were designated "citizens" of a homeland, and issued passports to allow them to travel in white-controlled "South Africa," which possessed most of the wealth and jobs. By treating blacks as foreigners in South Africa, they could be more easily controlled by threatening them with expulsion to their "homeland."

The book contains essays on each of the homelands that include numerical data, but the data is organized in a way that makes it difficult to see how the homelands compare on any single characteristic. By entering data from different portions of the book into a single spreadsheet, it becomes much easier to compare the performance of each of the homelands.

HOMELAND Area (acres) Residents Citizens
Basotho-Qwaqwa 114,355 25,000 1,245,000
South Ndebele 352,000 n/a 233,000
Swazi 529,518 118,000 460,000
Venda 1,510,888 264,000 358,000
Gazankulu 1,668,230 267,000 649,000
Ciskei 2,296,368 524,000 924,000
Lebowa 5,535,215 1,084,000 2,019,000
Kwazulu 7,861,053 2,097,000 4,026,000
Bophuthatswana 9,385,045 884,000 1,658,000
Transkei 9,639,230 1,734,000 3,005,000

Analyzing the data: The first thing you notice is that there are many citizens of each homeland who are not resident in their homeland. Why is that? The previous quotation suggests the answer -- it says that the Republic [of South Africa] provides employment for a large percentage of their labor force. That suggests a different question -- if most of the jobs are in white-controlled South Africa, does everyone, both black and white, have equal access to the country's economic resources?

A complete answer to that requires knowledge of the location of natural resources, especially water, but a preliminary answer can be derived by calculating population densities. If the division of South Africa into homelands was done equitably, then everyone should wind up with approximately the same amount of land. You can calculate the density of the population in each homeland by dividing the number of residents by the number of acres and placing the results in a new column.

How do those figures compare to the population density of South Africa as a whole? The book does not give either the total population or land area of South Africa, but a differrent source (1973 World Almanac) does: population of 29,290,000 and total land area of 471,819 square miles. With those two numbers, you can easily calculate the population density of the country as a whole, but in order to get a number that you can compare to the population densities of the homelands, you must first convert square miles into acres (or vice versa). Since population densities are usually expressed in terms of people per square mile, convert the land area of each homeland from acres to square miles using the ratio of 640 acres to one square mile (i.e. divide the number of acres by 640) and place the results in a new column.

To determine whether the homelands policy treats black and white areas equally, you need to compare the population density for the black-occupied parts of South Africa with that occupied by the white South Africans. You do this by adding the land area of all the black homelands. Then subtract that total from the land area of the entire country to determine who much land was set aside for whites. Do the same with the population numbers to determine the white population. Create a new row called "white South Africa" and put both of those numbers in their respective columns. Finally, calculate the population density of white South Africa the same way that you did it for each of the homelands, by dividing the number of white South Africans by the number of square miles they inhabit.

HOMELAND Area (acres) Residents Citizens Area (square miles) Population Density
Basotho-Qwaqwa 114,355 25,000 1,245,000 179 139.9
South Ndebele 352,000 n/a 233,000    
Swazi 529,518 118,000 460,000    
Venda 1,510,888 264,000 358,000    
Gazankulu 1,668,230 267,000 649,000    
Ciskei 2,296,368 524,000 924,000    
Lebowa 5,535,215 1,084,000 2,019,000    
Kwazulu 7,861,053 2,097,000 4,026,000    
Bophuthatswana 9,385,045 884,000 1,658,000    
Transkei 9,639,230 1,734,000 3,005,000    
whole country   29,290,000   471,819 7.2
"white" South Africa          

Is white South Africa more or less densely populated that the black South African homelands? Does your answer support the authors' contention that the homelands are headed towards "progress through separate development?"


Go to the HIS480 Syllabus or Assignments.