Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have data on period of construction for dwellings in dissemination areas. I have transferred this data to my study areas and would like to determine the median period of construction for each study area. The only problem is that the columns' information is the number of dwellings and I do not want the median of these, but the title of the column to populate the row in a new field (or something else indicating it, preferably the period in text format, but it's not the end of the world if it's just a number indicating the period).

I am attaching a picture of the attribute table with the relevant fields highlighted. There is a MEDIAN field but the data I am using was created by someone else and poorly documented, so I am not certain as to whether the calculation was already conducted or not.

(The seven columns represent seven non-overlapping time periods ordered chronologically. The [Median] field appears to index the period at which total construction was half-complete; that is, it records the median time.)

Screen shot of table

share|improve this question
1  
Are you asking for the name (or index) of the field in which the median value occurs? (That clearly is not the current [Median] value.) Or are you perhaps asking for the name (or index) of the time period during which the first half of all construction was encountered? E.g., in row 1, total construction at the end of the periods went 45, 180, 485, 1495, 1995, 2130, 2130. The median of 2130/2 = 1065 occurred during the fourth period (and, sure enough, [Median]=4). That wouldn't normally be called a "median," but it looks consistent with the current data. –  whuber Aug 16 '11 at 19:04
    
@whuber, I'm just paranoid that it is incorrect, because the man who compiled this is in Vancouver and I can't ask him outright and he didn't document anything in the metadata. –  Emily Aug 16 '11 at 19:07
    
I still have no idea what "median column" means -- can someone rephrase the title and question? –  blah238 Aug 17 '11 at 6:16
1  
@blah The terminology is indeed correct, as explained at the beginning of my reply and further clarified in a comment to the reply by Nathanus. To respond to your request, I have added a paragraph to the question. –  whuber Aug 17 '11 at 12:54
    
Thank you @whuber for making it clearer. –  Emily Aug 17 '11 at 15:32
add comment

2 Answers 2

up vote 8 down vote accepted

Assuming the columns appear in time order, the first row (for example) indicates that total construction through each period went

0, 0+45 = 45, 45+135 = 180, 180+405 = 585, 585+1010 = 1595, ..., 2230+0 = 2230.

Construction was halfway through at 2230/2 = 1115. This occurred during period 4, because at the end of period 3 the total was 585, at the end of period 4 the total was 1595, and 585 <= 1115 < 1595.

This appears to be the result reported by the [Median] column, which gives the index of the period (starting at 1 on the left).

You can code this in your favorite language. The table is so small (100 rows), though, that a spreadsheet will be convenient, if only for checking what you do more formally in Python or whatever. Here's what it might look like:

Spreadsheet

The first three data rows have the same values as yours. The next two data rows (surrounded by blank lines) are chosen further down in your table. The last five data rows exercise the algorithm a bit.

(Note, as shown in the last two lines of the spreadsheet, how Excel chooses the later period whenever the middle falls exactly between two periods. This isn't necessarily the "right" answer, but it's a valid one.)

Here are the formulas in columns H:R:

Formulas

You don't have to type them all. The only typing needed is:

  • =H2+A2 in I2. Drag this through O2. This computes the cumulative sums. It requires that columns A:G are in chronological order.

  • =O2/2 in P2. This finds half the total.

  • =Match(P2,H2:O2,1) in Q2. This indexes the column where construction was half complete.

  • =Offset($A$1:$G$1,0 0, Q2-1, 1, 1) in R2. This obtains the column heading corresponding to the index.

Then paste 0 in all of column H and drag I2:R2 down to as many rows as needed.

This effectively serves as pseudocode for the algorithm. The trickiest part will be the search to implement Excel's MATCH function. But that doesn't require any craft: it's not inefficient to search each array of cumulative sums sequentially (rather than with the preferred binary search algorithm) because these arrays are so short.

share|improve this answer
    
Thank you so much whuber! This is what I was looking for. :) –  Emily Aug 16 '11 at 19:41
add comment

If you want to return the median column, I would probably use a python cursor to iterate through rows. Use the fields to fill in a list and then return the center item (will be indexed at [3] for 7 items) after calling the sorted() function on the list you have created. I will write you a bit of code when I get back from lunch.

share|improve this answer
    
Hold off on coding until you're sure this is what's needed! –  whuber Aug 16 '11 at 19:07
    
Whether it's the year or the amount is only the difference between a dictionary and a list, I should think. –  Nathanus Aug 16 '11 at 19:08
    
I've been manually checking through a la @whuber's comment on my question and perhaps the column is correct, though it would be nice to know how to do this for future study-regions (which don't have these columns) –  Emily Aug 16 '11 at 19:28
    
I think it's a completely different calculation, @Nathanus. One views the data as an unordered set of counts {x1, x2, ..., x7} whereas the other views them as differences in a sequence (0, x1, x1+x2, x1+x2+x3, ..., x7) representing a cumulative distribution over time. The median of the counts will be a value exceeded by three of them and greater than or equal to the other three. The median of the cumulative distribution is a time, not a count, and is found using methods described in my reply. –  whuber Aug 16 '11 at 19:39
1  
You're right: the distinction is not clear in the question. It only came out in the comments. I have inserted a short explanatory paragraph in the question. –  whuber Aug 17 '11 at 12:55
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.