Recently, I’ve been getting more and more bogged down in my EFL curriculum work. It’s a very rough process, but even so it’s become unwieldy. The problem is the way I’m keeping track of vocabulary words.

Frequency is key

Whenever I create a new lesson, I have to choose vocabulary items to introduce. Rather than do so on a strictly topical basis, I’ve opted to focus on usage frequency. In particular, I do everything within reason to teach the headwords used for the Oxford Bookworms series of graded readers. Extensive reading is possibly the single most important part of our program, and it’s worth it to make a few sacrifices to get the kids reading actual books as soon as possible.

Spreadsheets can save time

In order to keep track of everything, I’ve made a big spreadsheet for all the vocabulary items in the curriculum. One field is the word itself, another field represents which lesson I teach it in, another is the Bookworms level in which it first appears. The Chinese translation is in still another field, and there’s one final one for any notes I might have about the vocab item. I can sort this spreadsheet by column headers to see the words taught in a given lesson, or all the adjectives taught between any given lessons, or any number of other useful combination. It’s great for making review sheets for my students.

Spreadsheets still aren’t perfect, though

So what’s the problem? The problem is that I started the list with only level one Bookworms words. Before, when I made new lessons, I used to look through the remaining level one words and choose the most appropriate group of them I could. After exhausting the level one list, things got a bit tougher. Now, when make a new lesson, that means adding new words to my master spreadsheet. Obviously, I want to add level 2 words, but sometimes there’s a word that just has to be added regardless of where it appears in the Oxford frequency lists. When that happens, I have to hunt through the list of words that appear in level two readers. If the word I’m looking for isn’t there, I have to look through the level three list, and then the level four list. It sucks.

Worse still, if I were to just copy the entire level 2 list into my master spreadsheet, there would be hundreds of duplicates, which would take hours to remove by hand. No good. Especially since the task would just become all the more unmanageable when it was time to add the level 3 list into my master spreadsheet.

Aha! I’ll just whip up a quick program to parse my excel file and remove duplicates!

Ha! What a fool I was! After spending hours pounding my head against my computer desk, I came to the realization that the excel file format is pure evil. It has decades of cruft that hark back to the days in which desktops had less computing power than my sports watch! And it won’t yield its information gracefully! Had the .xls file format been the result of one particular person, I’d have said his decision making skills rivaled those of Easter Island native who said, “I know! Let’s cut down the last tree and commit suicide because we need more identical statues!“.

Finally, I came to my senses, saved the list as text file and wrote a quick python script to read the vocabulary list into a few big arrays of strings, and then it was easy to remove duplicates:

for i in range(0, len(b2words)-1):
	for w in l2words:
		if w == b2words[i]:
			del b2words[i]
			del b2pos[i]

Then I output my list of level two bookworms (that aren’t duplicates of any words already in my curriculum) into a simple text file, and the part of speech information into another text file. Finally, I used my good friends cut and paste to add them into my master spreadsheet and I set the Bookworm level for the whole group to two.

Why, oh why couldn’t I have thought of that solution before 4 a.m.?