At this busy time of year, home economic efficiency is particular important. Some options for dealing with dusting jobs:
I suggest developing both pacifist and militant capabilities and then deploying the one best suited to the particular operational requirement.
Even in this age of big data, most persons collect data in spreadsheets. Two challenges are common with spreadsheet data, particularly spreadsheet data collected from a variety of sources. First, you need to understand what numbers you have. That means both the definition of a specific number and the presence or absence of particular numbers. Second, you need to combine unstructured data and data tables of various forms into an encompassing data structure that you can flexibly query and re-organize. Here are some ideas and data tools to address these challenges.
Suppose you are collecting unstructured data from a variety of sources. For example, a Wall Street Journal news article states:
Nielsen, at the request of The Wall Street Journal, analyzed cellphone bills of 60,000 mobile subscribers and found adults made and received an average of 188 mobile phone calls a month in the 2010 period, down 25% from the same period three years earlier.
To collect that data, you might paste the string “adults made and received an average of 188 mobile phone calls a month in the 2010 period” in a spreadsheet cell. But that’s data you can’t use in spreadsheet calculations. Ok, stick the number 188 in the cell to the right of that data string so you can do calculations on the relevant number. Even better, stick the number in the first cell and the description string in the second cell, so that the long, left-justified description string lines up close to the corresponding number. If you’re really serious, stick the source url in another column to the right so that you can check if you’ve missed something. This sort of procedure gives you a spreadsheet that looks something like this. This data format may be good enough for some private-sector work, or even some government work. If so, fine.
But collecting numbers and ad hoc data description strings has some weaknesses. In the example above, you haven’t fully captured the data description. That data concerns “mobile subscribers.” Mobile subscribers may or may not be different from mobile prepaid service users. Moreover, suppose you had a hundred numbers like that above. To find a particular statistic of interest, you would have to start reading through the description strings until you found one similar to the statistic that you sought. That’s tedious and time-consuming.
If you describe spreadsheet data in a more structured way, you can understand it better and process effectively larger amounts of it. A common approach would be to set up a spreadsheet table and stick numbers into it. For example, suppose you want to collect wireless service data by company. You place in a row your data categories (fields): company, customer type, number of customers 3Q 2010, etc. Then you start putting data into the relevant columns below. If you have a lot of categories and the data you find isn’t generally organized in the order of your categories, populating the table will be a tedious and time-consuming task. Moreover, suppose that you are collecting the data by company. You have to repeatedly enter the company name in the company column. That sort of annoyance can easily be multiplied if you are collecting data with additional categorical organizations such as date and business segments. If these slowly varying categories are spread across a large table, the pain is even more intense.[1]
Here’s some good news: that supercomputer on your desk can work as a tabulating machine. You just need to describe your data with the Spreadsheet Data Description Language (SDDL) and then tabulate it with STT (Spreadsheet Tabulating Tool, or Simple Tabulating Tool, or SDDL Tabulating Tool). SDDL and STT provide fine new acronyms for you to use without requiring you to learn much or do much different with the beloved spreadsheet programs that you’ve been using since Visicalc came out. To use SDDL, you put a category in one cell, and in the next cell to the right, an item for that category. You can add additional categories and items aligned below, in any order most convenient. When you stick an item into a category that already contains an item, you’ve implicitly tabulated a record and started a new record with empty categories. STT is a spreadsheet macro that adds to a table on a separate sheet the categories (if necessary) and items contained in a block of SDDL. SDDL is so simple that you’ve probably been setting up a lot of spreadsheet data in SDDL, without even knowing that you were using SDDL.
Here are some SDDL / STT examples using Google Docs spreadsheets.[2] So that you can see the SDDL and the tabulation side-by-side, I’ve copied the tabulations from the tabulated sheet to the SDDL examples sheet.[3] Examples 1.1-1.2 show placing items into categories, and how sticking an item into an occupied category generates a tabulated record. This technique is more efficient than hand-tabulating data for a wide, sparse table.
You can easily combine hand tabulating and SDDL / STT. If you start with a tabulation, running STT on a block of SDDL adds any needed categories and adds all items to their categories. See Example 2 in the SDDL example spreadsheet.
SDDL uses prefixes for pinning and unpinning items in categories. If you precede a category name with the character “*”, the associated item is pinned in that category across records. So if you are working on tabulating a group of records for a company, you can pin the company name in the company category for that record group. That company name will then populate each record in that record group. When you insert a new name in the company category, you decide whether to pin that new name. See Example 3.
You can create SDDL using all the editing, linking, and calculating capabilities of the spreadsheet. So, for example, if you have a figure somewhere in a spreadsheet, you can reference that figure and do a calculation using it to produce a figure in an SDDL cell. Similarly you can copy and paste categories to lessen the work of entering SDDL.
SDDL stack and twoway formats provide an easy way to create groups of similar records. A stack is a table of items with one dimension of categories (a record list). A twoway format describes a table with two dimensions of categories. Both stacks and twoways are commonly called tables, but technically these forms differ significantly.
In SDDL, a stack is one or more rows in a block where more than one item follows the category that starts each row. The row-starting categories, called 1cats, must not be duplicated within one stack.[5] STT tabulates one record for each stack column.[4] Each tabulated record contains all the items sitting in the current record before the stack occurred. To visually distinguish stacks, and for some minor cases of missing values, the stack category can be prefixed with a 1. See Examples 5.1 and 5.2.
While a stack implies a record for each data column, the twoway format typically creates a record for each item placed in a rectangular grid. The twoway format begins with the twoway directive “%twoway”, with “precat” categories following in that directive’s row. Below the twoway directive row are a stack of “1cat” rows and a set of “2cat” rows. The record for grid item at position (r,c) is created from items in the “precat” section of row r, the items in the stack above the grid in column c, and the grid item. The grid item goes into the “2cat” category that begins the grid row. See example 6. Once you understand how to read the relevant categories (precats, 1cats, and 2cats), a twoway SDDL form is just a well-described, two-way table.[6]
A key use for the twoway is to re-organize and aggregate various data tables. Financial publications typically include pivot tables (two-way tables) constructed from a master data table (a one-way table / record list). The SDDL twoway format allows you to reverse pivot the table to the extent possible (unfortunately, you can’t un-sum a cell total). For an example of an SDDL table-to-list conversion using real data, see the first data table here (from an AT&T 3Q2010 financial spreadsheet) and the first SDDL twoway here. A Google search shows that many others struggle to convert (two-way) tables to (one-way) record lists. With SDDL / STT, you can do that job easily and flexibly. Moreover, you can automatically aggregate the tables that you convert into one, big master tabulation.
Go ahead and experiment with SDDL / STT and consider whether it might be useful to you. Here’s an example of using SDDL / STT to aggregate both unstructured and structured data from AT&T’s and Verizon’s third-quarter, 2010, financial reports (wireless segment). Here’s a full definition of SDDL and the source code for the STT script. Want to customize the script to your liking? Got an idea for improving it? Go for it! If you make an improvement to the script, please make it available to everyone. That’s not a legal requirement; it’s just a polite request.
Only a few persons have big data. Ultimately, it’s not the size of your data, but what you do with it that really matters.
* * * * *
Notes:
[1] Tricks exist to fill down blank cells. But you still have to locate the right columns for the first-changed items and filling down.
[2] STT is currently freely available as a Google Apps Script. I hope that public-spirited programmers will port it to Microsoft Excel, OpenOffice, Zoho Spreadsheets, and any other applications where it would be useful, and that they will make the resulting ports freely available.
[3] The tabulations appear in a separate sheet from the SDDL. That sheet by default is named “tabulated”.
[4] My design philosophy for STT’s processing of SDDL is “STT will do the best it can with what it’s given”. If you duplicate 1cats in a stack, STT will break up the stack into sub-stacks to overcome the duplication. The STT log file records such situations.
[5] An SDDL stack is the same as the transpose of a record list, where the first row contains field names. The SDDL directive “gettab” adds a record list to an STT tabulation (which itself is a record list).
[6] The twoway can handle different categories placed as 2cats. See the SDDL / STT documentation.
In April, 2008, Burlington Telecom looked like a case study of a successful municipal fiber network. Burlington Telecom has subsequently become a financial and political disaster. It may soon become an operational disaster as well.
Some developments:
A key recommendation of the Blue Ribbon Committee on Burlington Telecom:
the overall strategic plan of Burlington Telecom must shift from engineering and technology based to one based on sales, marketing, and customer service in order to be successful in the future. Organizational structure should be refined to meet the needs of such a strategic plan.[2]
Municipal organizations that provide and maintain municipal roads, water, sewage, and in some jurisdictions, electricity, focus on engineering and implementing well-established technology. Municipal organizations don’t have a good track record in sales, marketing, and customer service in fast-changing retail sectors.
One of BT’s major assets was community recognition and community good-will. That asset allowed BT to avoid the high level of customer acquisition expenses that other communications companies incur. For example, Verizon spent $98 million on advertising for Fios in measured advertising media in 2008. Verizon’s advertising expenditure per Fios customer acquired has been estimated at $200.[3] Whatever organizational form BT adopts, it is likely to face much higher customer acquisition costs than it had in the past.
The Blue Ribbon Committee’s recommendation that BT’s organizational structure “should be refined” seems to point away from an organization that provides a municipal communication network in a way like municipalities provide municipal roads.[4]
* * * * *
Notes:
[1] Letter from Andrew Montroll et. al. to Bob Kiss, Mayor, City of Burlington, and Bill Keogh, President, Burlington City Council, Nov. 24, 2010.
[2] Blue Ribbon Committee (BRC) on Burlington Telecom, p. 4. Reboot BT (the G-9) “fully agrees with the BRC’s analysis and with its recommendations.” See Reboot BT FAQ.
[3] “Is Verizon’s $23 billion bet with Fios paying off?” Advertising Age, May 18, 2009.
[4] Reboot BT declared, “Ownership of BT by the City is not necessarily a bad thing. Management of BT by City Hall is.” It proposed that BT be restructured as an “independent locally owned non-profit that remains true to the original goals of BT.” The group that recently proposed to purchase BT declared that it sought to keep BT a “community focused entity.”
In China before the first century BGC, the ground on which persons lived was thought at its limits to be square. What was above that ground was thought at its limits to be round. Denote the first space as earth, and the second, as heaven, while carefully ignoring current ideas, beliefs, and knowledge associated with earth and heaven. Then ponder this Chinese text that probably dates from before the first century BGC:
The square pertains to Earth, and the circle pertains to Heaven. Heaven is a circle, and Earth is a square.[1]
This cosmography is known as gai tian. Fragments of a Chinese poem written about 300 BGC detail it:
The square earth is a chariot;
The round heaven is its canopy.[2]
Gai tian cosmography seems to have deep historical roots in Chinese culture. Persons living about 3300 to 2250 BGC in the region of present-day Shanghai (whose culture is known as Liangzhu culture) expended enormous effort to make jade disks (called bi) and circle-in-square jade objects (called cong).[3] Abstraction and formal simplicity don’t typically characterize folk art, even highly cultured folk art. Consider, for contrast, ancient Chinese bronzes such as those from the late Shang dynasty (1300 to 1050 BGC). These objects are intricately designed and feature fantastic life forms. They are emotionally and spiritually evocative. Their aesthetic is wholly unlike that of Liangzhu bi and cong.
Liangzhu bi and cong are plausibly related to gai tian cosmography. Bi and cong are typically found in materially rich (high status) burials, where they are placed on the body of the buried person. Non-decorative markings found on some bi link them to ritual sacrifice and indicate an orientation to the center and circular motion like that associated with a circular heaven.[4] Cong might be interpreted as providing a path to heaven (circle) through the earth (square).[5] A Chinese text from about the second century BGC associates cong with earth and bi with heaven.[6] The cosmic significance of the circle and square helps to explain the peculiar aesthetic of the Liangzhu bi and cong.
Describing the earth as square was an unusual and persisting aspect of Chinese culture. Seafaring cultures have frequent opportunities to watch objects gradually disappear over the horizon. After the 5th century BGC, Greek and subsequent European writers consistently described the earth as spherical. However, the flatness of the earth at large is an intuitive sense in everyday life. The belief that the world is flat is plausible enough that many persons today still believe that medieval Europeans believed that the world is flat.[7] Thinking of the earth as a flat disk makes it easy to imagine the boundary between the earth and a domed-shaped space above. But the Chinese described the earth as not just flat, but also square. That description continued to have an important place in Chinese scholars’ thinking at least until their engagement with Jesuit astronomers in the seventeenth century.[8]
Chinese gai tian cosmography has parallels in some early Mediterranean sources. In the Christian New Testament, Revelation 7:1 states, “I saw four angels standing at the four corners of the earth….” Revelations 20:8 refers to “nations at the four corners of the earth.”[9] Imagining the earth as square is rather odd within the Hellenistic world of the early Christians. That image of the earth, however, was common in Chinese civilization of that time.
A passage in Hebrew scripture places a circular heaven like a canopy above the earth. Isaiah 40:22 declares:
It is he [God] who sits above the circle of the earth,
and its inhabitants are like grasshoppers;
who stretches out the heavens like a curtain,
and spreads them like a tent to live in;
In this passage, God looks down on the ensemble of the heavens and the earth, hence “circle of the earth” describes the shape of the heavens/sky above the earth. The heavens are like a curtain, similar to a canopy, stretched above the earth. That is the sort of configuration imagined in gai tian cosmography.
A passage in the book of Job connects the shape of the heavens to a flat, circular luxury object. In Job 37:18, Elihu asks Job, “Can you, like him [God], beat out [raqa] the skies, hard as a molten mirror?”[10] Mirrors were personal luxury items in the ancient world. They were flat and usually roundish. The word for beat out, raqa, is the root for the world firmament/dome in Genesis’s account of the creation of the world (“Let there be a dome in the midst of the waters…. God called the dome Sky”).[11] Hence the mirror in this passage in Job relates to heaven like a Chinese bi does.
A square earth and a circular heaven, the curious elements of gai tian cosmography, can thus be found in scattered pieces in Hebrew and Christian scripture. But such fragments do not only exist there.
In the fifth-century BGC Etruscan Tomb of the Monkey in Chiusi, Italy, sirens placed at four quarters of a square hold up a circle. Sirens were associated with birds conveying souls to heaven and celestial representations on ceilings in early Greek and Etruscan art. Moreover, the canopy was a common symbol of heaven.[12] Hence this fifth-century BGC tomb in Italy could be interpreted as representing gai-tian cosmography.
Perhaps these fragments of western Eurasian cosmography merely indicate general patterns of human imagination. They may have developed independently of eastern Eurasia gai-tian cosmography and been understood much differently. But don’t dismiss too readily that considerable communication occurred across Eurasia thousands of years before the Internet.[13] The wise men from the East who came to pay homage and give gifts to the child Jesus in Bethlehem may have been Chinese.[14]
Read more:
* * * * *
Notes:
[1] Zhou Bi Suan Jing, #A6, trans. Cullen (1996) p. 174.
[2] Poem fragment by Song Yu, trans. Cullen (1996) p. 50.
[3] The Freer Gallery has an outstanding exhibit of ancient Chinese jades, including many bi and cong from the Liangzhu culture.
[4] See Teng (2000).
[5] Sanxingdui bo wu guan bian (2005) p. 92.
[6] Rites of Zhou. See Lu (1990) p. 30.
[7] On the flat-earth myth in Western culture, see Jeffrey Burton Russell, The Myth of the Flat Earth (1997), and for more details, his book, Inventing the Flat Earth (1991).
[8] Cullen (1976), p. 107, notes:
Chinese thought on the form of the Earth remained almost unchanged from early times until the first contacts with modern science through the medium of Jesuit missionaries in the seventeenth century. While the heavens were variously described as being like an umbrella covering the Earth (the Kai Tian theory), or like a sphere surrounding it (the Hun Tian theory), or as being without substance while the heavenly bodies float freely (the Hsüan yeh theory), the Earth was at all times flat, although perhaps bulging up slightly.
At least one 13th-century Chinese scholar apparently challenged earlier the belief that the earth was square. Mathematician Li Zhi argued that the earth must have rounded rather than square corners.
[9] This and other biblical quotations, unless otherwise noted, are translations from the New Revised Standard Version.
[10] Translation from the Wikipedia entry for shamayim.
[11] The King James Version of the bible used the word “firmament” in the place of “dome.” Wikipedia’s firmament entry describes the source of that translation:
The original Biblical Hebrew raqia, or raqiya` ( רקיע), is derived from the root word raqa ( רקיע) “to beat or spread out”, e.g., the process of making a dish by hammering thin a lump of metal. However, in Syriac this root word had adopted the meaning “to make firm or solid”, which led to the erroneous translation of raqia as “that which strengthens or supports” (i.e., Greek stereoma) in the Septuagint (circa 200BC). This was then carried forward from Greek into the Latin Vulgate translation (as firmamentum), and from Latin into English as firmament in the 13th century. The King James Bible later entrenched the use of the term firmament.
[12] See Lehmann (1945) pp. 2.-4. Id. Fig. 2 provides an image of the ceiling of the Tomb of the Monkey in Chiusi.
[13] Soper (1947) traces across Eurasia the influence of western Eurasian images of the dome of heaven. While id. emphasizes diffusion from west to east, the same communication channels could work in the reverse direction. Cullen (1996), p. xiii, states, “ancient Chinese astronomical theory and practice appears to have had no significant connections with the worlds of Hellas, Babylon or Egypt.” See above for apparent connections.
[14] Landau (2008) translates and analyzes an ancient Christian apocryphal writing called Revelation of the Magi. In that text, the magi who journeyed to Jesus came from “the great East” (RevMagi 1:2), from a land called Shir:
These are kings, sons of Eastern kings,in the land of Shir, which is the outer part of the entire East of the world inhabited by human beings, at the Ocean, the great sea beyond the world, east of the land of Nod, that place in which dwelt Adam, head and chief of all the families of the world. (RevMagi 2:4)
Shir is mentioned in other ancient sources. It seems to be connected with China and the silk trade. See Landau (2008) pp. 261-2. On the other hand, that Adam dwelt in China is clearly mythical. The Armenian Smbat the Constable journeyed to the great Mongol court at Karakorum in the mid-thirteenth century. In a letter in 1248 to King Henry I of Cyrprus, Smbat wrote that the magi (Three Kings) had come from Tanchat (Western Xia, present-day China) and that there are “many Christians scattered over the East, and many fine churches.” Although Smbat was correct about the presence of Christians in central Asia and China, most ancient commentators thought that the Magi came from Persia. In ancient western Eurasia, frankincense and myrrh were found in what is now Yemen, Oman, and Ethiopia.
References:
Cullen, Christopher. “A Chinese Eratosthenes of the Flat Earth: A Study of a Fragment of Cosmology in Huai Nan tzu 淮 南 子”, Bulletin of the School of Oriental and African Studies, Vol. 39, No. 1 (1976), pp. 106-127 (107).
Cullen, Christopher. 1996. Astronomy and mathematics in ancient China: the Zhou bi suan jing. Needham Research Institute studies, 1. Cambridge: Cambridge University Press.
Landau, Brent Christopher. 2008. The sages and the Star-Child: an introduction to the Revelation of the Magi, an ancient Christian apocryphon. Thesis (Ph.D.)–Harvard University, 2008.
Lehmann, Karl. 1945. “The Dome of Heaven”. Art Bulletin. 27 (1): 1-27.
Lu, Yaw. 1990. Lee Kong Chian Art Museum: collection of Chinese ceramics, bronze, archaic jade, painting & calligraphy in the light of recent archaeological discoveries. Singapore: Singapore University Press.
Sanxingdui museum (Guanghan, Chine). 2005. San xing dui: gu shu wang guo de shen mi mian ju. Bei jing: wu zhou chuan bo chu ban she.
Shu-p’ing, Teng. 2000. “The Original Significance of Bi Disks: Insights Based on Liangzhu Jade Bi with Incised Symbolic Motifs”. Journal of East Asian Archaeology. 2 (1/2): 165-194.
Soper, Alexander Coburn. 1947. “The ‘Dome of Heaven’ in Asia”. Art Bulletin. 29 (4): 225-248.