Tough Year

It's been a tough year. I covered the beginning of the year in another post regarding the Calculus course I took in preparation for applying to a master's program in applied statistics. I had planned on studying for the GRE this summer and taking it this fall as well as applying for the master's program, then working through the master's program for the next 3-5 years. My employer, HDMS (a subsidiary of Aetna), was going to help pay for the degree. However, those plans were going to get thrown off course.

I had just finished submitting my coursework for reimbursement when I was sent an out-of-place meeting request. At the meeting, I found out HDMS was letting me go. At first it appeared it was just me, but as I found out later in the day, they were laying off about 10 other employees and closing about 10 other open positions. For two hours, I was trying to figure out what I did wrong - but it had nothing to do with my performance. I was the most recent hire on the team, and there were others getting laid off too.

I hit the ground running. The severance package included a career consulting service - I looked it up and scheduled time to review my resume with a consultant. The paper / PDF version has undergone quite a few revisions over the past few weeks. I also started contacting my network, browsing through online job boards, and all the usual job-hunting tasks. I did find quite a few roles through my network, and a couple of them resulted in offers.

I quickly found a role as a consultant data analyst with Great Wolf Resorts. I stayed there for a few weeks, working on a single project integrating credit card transactions with the reservations. Essentially, if a guest uses a credit card for something on site (e.g., restaurant) and does not charge it back to the room, the transaction is not connected to the reservation. In order to connect the two, I had to merge transactions with reservations based on guest name and, if available, the last four digits of the credit card number. It was messy, and I was able to get about 57% of the transactions matched. I believe the best possible rate was somewhere around 65%, but it would have required a lot of exception handling, manual matching, and/or time-intensive matching processes (e.g., matching text within another text field). The company analyst and I decided the additional matches weren't worth the expense.

The position was a good fit for my skills, and I enjoyed working with the people there, but as a consultant role, the benefits were very expensive and of course it could have ended at any time. So, I kept looking for permanent roles while I was there. I need something more permanent right now, but I can definitely see myself as a successful consultant. In my short time there, I think I demonstrated a lot of value with my skills and the process and analysis I left behind.

Recently, I found a new role as a Senior Healthcare Analyst at SSM Health, a non-profit healthcare organization with hospitals from Wisconsin to Missouri. They also own Dean Health Plan, where I worked a few years ago. I still know a few people there, so it will be good to reconnect with them. I'll be analyzing healthcare data for a particular region of the system, starting in a few days. I feel very good about the team and the leader, so I'm looking forward to getting started. Luckily, I'll be working from home again, so I'll get to use my treadmill again.

Here's hoping quarter four is quite a bit less turbulent!

Gazetteer Database for Geographic Analysis

A couple of years ago, I had a tricky problem to solve. I inherited a tool a group of analysts were using to allocate website search based on ZIP code and location name (e.g., city, most commonly) for clients based on their own locations. The tool used the output of a predictive model for website search activity and inputs from the client, including addresses, for configuring the search locations that would be allocated for the client.

In addition to setting up relevant geographies based on the client's locations, the tool attempted to collect additional nearby locations that were likely relevant to the client (a "market"). The problem was that it did not find good matches for cities, towns, and other locations people were using on the website. As a result, the analysts were doing quite a bit of work to correct the output by removing and adding locations by hand. It was very time consuming, and I had to do something about it.

EDIT: I updated the following paragraph after I remembered how the algorithm was originally working. Initially I wrote that it calculated distances between locations, but it did not.

I reviewed the process and the data used to obtain location names. The algorithm used a simple lookup from ZIP code to location name, usually city or town. It did not attempt to look up nearby location names. The data did include latitude and longitude for the locations, so I thought I'd try adding code to lookup nearby locations with this data. I asked around in the software development area and found that they were using a fuzzy distance calculation based on a globe. When I tried it out using the existing location data, I found several problems. Some of the latitude/longitude coordinates were in the wrong state or in the middle of nowhere. Additionally, the data was missing quite a few relevant locations, like alternative names for cities and towns, as well as neighborhood names, parks, and a variety of other place names people use in web searches. I discovered it was several years out of date, and there was no chance it would be updated. So I decided the data was simply junk. I had to find a new source.

I began searching online for government sources of location information. After all, the US government establishes ZIP codes, city and town designations, and executes the census every once in a while. The US government also has to release this data publicly, according to law. (This doesn't mean it's free, or easy to obtain.) So there must be publicly-available data regarding locations. Luckily, I ended up finding a free online source: the US Gazetteer Files (see "Places" and "ZIP Code Tabulation Areas" sections).

What's a "gazetteer"? A gazetteer is a list of information about the locations on a map. In this case, the US Gazetteer data includes latitude and longitude, useful for geographic analysis.

As I used the data, I found a few gaps, so I searched again and found the US Board on Geographic Names (see "Populated Places" under "Topical Gazetteers"). By integrating these two data sets, I had a rather comprehensive listing of all sorts of places around the US.

Next, I had to get the new location data working with the search configuration tool. The tool was written with a web front-end for the inputs, SQL to collect the data and apply the inputs, and Excel as the output data. So I had to do a bit of ETL (actually, I did ELT, loading before transforming) to get the new location data working with the tool. I ended up designing the model pictured here:



The main data is in gz_place and gz_zip, storing locations and ZIP code data, respectively. On the right of gz_place are some lookup tables, including a table with alternative names (gz_name_xwalk - "xwalk" meaning crosswalk). The ZIP table references a master list of potential ZIP codes (see the prior post about creating that table), a list of invalid ZIP codes that showed up in the prior location data, and a list of ZIP codes I determined were "inside" other ZIP codes (the algorithm for is another discussion entirely).

The data on the left is a bit more interesting. There are some metadata tables not really connected to the rest (gz_metadata, gz_source), documenting quick facts about the data and where I found the data. Two reference tables also float off on their own, with a list of raw location names (gz_name) and a list of states (gz_state_51 - 51 to include DC), each including associated information.

Now I didn't want the tool to calculate distances between everything and everything else each time an analyst ran the tool, so I decided to precompute the distances and store only those within a certain proximity. I decided there were 3 types of distances required: ZIP to ZIP, location to location, and location to ZIP (and it could be used vice versa). To limit processing, I used a mapping of states and their neighbor states to connect the initial set of ZIPs and locations to use. This helped to decrease the run time. At the same time, I calculated the distances between each set of latitudes and longitudes, and retained only those within a certain number of miles. The final, filtered results are stored in gz_distance, with a lookup table describing the distance types (gz_distance_type).

Finally, I could get the better location data into the tool. I replaced the original code with new code that uses the new location data, doing a simple lookup of the locations specified by the client (ZIP codes) and filtering for an appropriate distance. I created a few new inputs to help the analyst tweak the distance that the tool would use to filter the crosswalk, with the idea that clients in rural areas may find a larger area more relevant, and clients in dense urban areas may find a smaller area more relevant.

The results were excellent. The analysts praised the new process for being more accurate, less time consuming, and easy to use. There were some manual aspects to the process, for example, correcting spelling errors entered by users on the website, but these would become less of an issue as time went by. (Especially the spelling errors. The website administrators were switching from one vendor data set to another, which had better location suggestions/requirements based on the user's input.) Overall, it was almost completely automated and only required updates once in a while when new locations were added.

This was one of those projects where I really enjoyed the autonomy I was given. I was simply given a task (make this tool work better), and given free reign over how to do that. I worked with many people to get their feedback and help, especially from the database maintainer and a few users for testing the new inputs on the tool. (One interesting thing I did with the database was to partition the gz_distance table based on distance type. I got help from the database maintainer on the best way to do that.) And best of all, I really enjoyed the project.

Bad SQL Writing Put to Good Use

There's a certain style of writing SQL that I really don't like. Here's an example that pulls names and addresses for people in Wisconsin and Illinois:

select p.name, a.address
from person p, person_address pa, address a
where p.person_id = pa.person_id
and pa.address_id = a.address_id
and a.state in ('WI', 'IL')
;

Basically, the author has stacked all the tables into the FROM statement, and specified how they join on the WHERE statement. This creates confusion about how the tables are intended to be joined as well as mixing actual filter criteria with the join conditions. However, it works, since the code results in inner joins between all tables, and that was okay.

My preferred style is like so:

select p.name, a.address
from person p
left join person_address pa
on p.person_id = pa.person_id
inner join address a
on pa.address_id = a.address_id
where a.state in ('WI', 'IL')
;

It's a bit more verbose, but that helps the reader. This style splits out the tables into different statements and results in clearly indicated join types and join fields. It is clear to a reader of the code the intention of joining each table. The filter criteria are located in the WHERE statement without any other statements to confuse them with. There are still cases where the result may not be as expected based on the filter criteria, but it's easier to debug.

Overall, the first example is a confusing style to use, and it can cause trouble if the joins were intended to be outer joins and were not, because the style does not have a way to specify outer joins. (LEFT JOIN is short for LEFT OUTER JOIN, which means, basically, return all records from the first table, and any data that matches in the next without missing any records from the first.)

The other day, though, I encountered a great way to use this potentially error-prone style in a way that is actually very useful.

I wanted to generate a master list of all potential ZIP codes in the US, and then filter out ones that are not in use or are otherwise invalid. I started by creating a small table with 10 rows that consist of 1 column with the numbers 0-9.  With no loop statements available in SQL, I wrote this table like so:

create temporary table num (n int);
insert into num values (0);
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
delete from num where n > 9;

That last statement is just in case I ran it too many times. I was even too lazy to write out 1-9, instead just repeating the max+1 code 9 times. It's a bit over-the-top, but it works.

To get my "master" list of ZIP codes, I joined the table to itself 5 times, one for each character in the ZIP code (because they can start with 0s, they should be treated as characters, not numbers!). Here's how:

create table master_zip as
select n1.n||n2.n||n3.n||n4.n||n5.n as zip
from num n1, num n2, num n3, num n4, num n5
order by 1
;

Simple, isn't it? Essentially, this just makes a big Cartesian product of the table with itself times 4. There's no WHERE statement, because there's no need to join on anything. (If a system required it, I would just write "where 1=1".) This generates 100,000 records. That's about 58,000 too many, according the US Postal Service, so we need to delete some of those that are not in use. But that process is for another time.

Using a style that I usually do not recommend or warn against was interesting and useful, but it requires knowing a bit more about how these things work. Had I run this last bit of code on a larger data set, it would have caused a lot of problems, like running out of disk space or RAM. So use this style sparingly and carefully!

What is a Data Scientist?

Today on Quora, someone asked, "What are some software and skills that every Data Scientist should know?". I wrote the following as a response, reflecting on my current position and the role I play.

---

I started adding post-it notes with sub-titles to my name/title tag on my cube, as sort of a joke regarding the question, "What is a Data Scientist?".

Here's the current list:

• [Client] Analyst
• [Product A] Analyst
• Financial Analyst
• Sales Analyst
• Contract Analyst
• Quality Assurance Analyst
• Call Center Analyst
• Data Surgeon (aka, data mining with the intent to figure out what's wrong)
• Data Diagnostician (alternative of above, maybe with no details to examine)
• [Product B] Analyst
• Database Developer
• Bug Finder (as in software bugs)

So it would appear from this list that there isn't a lot of data science going on. And that's partially true.

Each of our clients has its own relational database, so we do "meta-queries" to access them one by one in order to answer a question. That's sort of data science like. Eventually, though, we're going to have one master database with all clients that will cascade into individual databases. So our "meta-queries" will be obsolete.

We deal with a lot of "big data" too, but it's usually not that big of a deal. Even with relational databases, it's okay. Some queries may take a little longer (30-60 minutes), but that's rare. We have some machine learning tasks that pull in massive training data sets, so at that point you have to be more careful about "big data" problems like running out of RAM or disk space. But it can be handled, and rather simply.

What I really wish I could do more of is machine learning, and while I've accumulated several ideas that would enhance products or help us make better decisions in the year I've been a Data Scientist, these other tasks take up most of my day.

In the end, I write a lot of SQL, use the Linux command line moderately, and report on data in Excel spreadsheets. I use Python occasionally to write scripts. And I'm always learning something new (new SQL techniques, Python libraries, Linux command line tools, etc.).

Redundant SQL

Today I wrote some SQL that, when spoke, sounded like "select client from client as client", written:

select  (select client from client) as client ...

Then I thought, could I actually write something even more ridiculous? What about "select select as as from from", and so I came up with:

create table "from" as select 'select' as "select" from dummy;
select "select" as "as" from "from";

The result is a one-cell table:

as
------
select

The table "dummy" is an empty table, used for "selecting" from something not actually in any table. The double quotes make the SQL execute even though it is using special keywords.

I tried adding a "group by group" and an "order by order", but that didn't work as I would have thought. I guess I can take this silliness only so far.

An Amended Quote

Today on The Writer's Almanac, Garrison Keillor read a quote by Gustave Flaubert:

"I spent the morning putting in a comma and the afternoon removing it."

I thought this could use some amending for programmers:

I spent the morning putting in a parenthesis and the afternoon removing it.

Or for you SAS programmers:

I spent the morning putting in a semicolon and the afternoon removing it.

How about SQL:

I spent the morning putting in a column and the afternoon removing it.

Or if you write HTML:

I spent the morning putting in an escaped ampersand and the afternoon removing it.

Why not Python:

I spent the morning importing a library and the afternoon removing it.

Essentially, though, these all boil down to:

I spent the morning putting in (an) arbitrary character(s) and the afternoon removing it.

For all you programmers during this holiday season: May your parentheses be matched (your quotes too!), special characters escaped, and code executable on the first run.

Recipe Database

I've been working on a recipe database for my wife, to help her plan meals by being able to easily peruse recipes (especially favorite recipes) and associate those recipes with days and meals. I also wanted to be able to easily add recipes, ingredients, and grocery items. Here is the most recent relationship view of the database:



Each recipe is associated with a list of directions and a list of ingredients. Since ingredients are variations on specific items, they are not distinct grocery list items. For example, one recipe may call for 2 tomatoes and another may call for 3 tomatoes, and each would be a separate ingredient, but only one grocery item with a combined total of 5 units. Grocery items also have alternatives in a self-referential link (ALT_GROC_ID), which would be appropriate for recipes with "or"s in the ingredient list or legitimate alternatives (e.g., sea salt for salt).

Then there are the weekly plans, called "Lists". I realized this term may cause some confusion between grocery lists and plans, so I may change it in the future. For now, I'm focusing on completing the functionality of the database and not improvements to the delivery. Each list is associated with a beginning and end date ("from" and "thru" on the Lists table). Each list has several detail lines in the Lists_detail table that associates days with meals for the days between the beginning and end dates, inclusively. Each meal is then related to a recipe.

Currently the database exists in a Microsoft Access database, and eventually I would like to port it over to SQLite or something similar, with HTML forms. For now, there are several Access forms for data entry, and the initial "Start" form that opens with the database provides access to many of those forms.



The top section of buttons appends new plans to the Lists table with different start dates. For a typical week, a user might start planning on Saturday for the next week Sunday - Saturday and the following week Sunday - Saturday, and would thus start two new lists using the Tomorrow and "Week from Tomorrow" buttons. The two new lists would be associated with today's date, which is displayed in the upper right hand corner.

Once the new lists are initiated, the user can open them by using the "Today's Plans" button in the "Open..." section. The user can also open recipes for viewing and the grocery list based on today's plans. Eventually I'm going to switch the grocery list from a view-only report to a form based on an append query, which would keep track of the lists through time and build an inventory of the kitchen. (This is where the confusion with "lists" would come in to play.) Also from the start form, the user can also add or edit recipes, add or edit grocery items, or delete plans.

The main function of the database is to help the user plan for a week or two of meals by associating days and meals with recipes and ultimately generating a list of grocery items to use while shopping. Here is the plan entry form (click for a larger view) that opens when the "Today's Plans" button is used:



The list date range is the first section, with arrows allowing the user to switch lists. The navigable lists depends on how the user opened the form based on the buttons in the start form. The user then uses the drop-down boxes to select days and meals. Next, the user can use the bottom part of the form to look up recipes. Say the user wants a rice dish, so the user clicks on the binoculars and searches the Name field for "rice". Currently there are fields for descriptions, authors, origins, and URLs for recipes, which could also be added to the form to help the user find a particular recipe.

The ingredients and directions are listed to help the user figure out what would be appropriate to prepare for any given day's plan. Once the recipe is selected, the user enters the recipe ID next to the meal. Alternatively, the user can enter something other than a recipe for a meal, for example, "go out to eat with friends" or "order pizza". Once the weekly plan(s) are complete, the user can return to the start form and open the grocery list report, which is organized by the location the items are found in the store, like produce, meats, aisles, etc.:



There is a lot of work still to do. As I mentioned, I have to figure out how to append the grocery lists to a list table to maintain a history. Then, I need to enter a lot of recipes. The recipes in the database currently were entered using a SAS program and borrowing work's SAS server to process it. The next recipes will be entered using the new form as a test run of how well it works. I expect I will revise the form quite a bit to make it flow better. I also have to figure out how to merge disparate measurements, e.g., cups and ounces, and only display a total quantity on the grocery list. Some merges will not be possible, as the above image shows for kosher salt: 7.5 tsp and "to taste". (Note the odd "40 taste" - this is a conversion of the ingredient "8 dashes of hot sauce", which is unquantifiable, and the dummy records I entered had 5 of the same recipe, thus "40 taste".) Then there's the conversion into a "better" database tool and using web forms instead of these proprietary-format forms.

For now, though, I am very happy that the database, although not fully populated with recipes, is usable and can save the user (just my wife, for now) a lot of time.

The Serial Comma

This morning I was wandering through Wikipedia when I stumbled on "The Oxford Comma". I looked it up, and realized I had read about it before - the serial comma: the final comma that is followed by an and or an or, which is controversial in whether it should be used. In reading the arguments for and against, I realized that one argument for the usage was left out, and that much of the ambiguity in using it or not using it seem to be related to inconsistent noun usage. Let me explain.

The argument for the serial comma that I would like to add is for those people who convert our human languages into computer languages. It is much clearer what is desired of a computer when all commas are specified, for example: XYQ report needs names, jobs and titles. Does that mean two distinct fields: name and job/title; or three distinct fields: name, job, and title. Clarification is necessary, and at that point the original communication regarding the need would have been less useful.

One of the examples of ambiguity on the Wikipedia article was the following: They went to Oregon with Betty, a maid, and a cook. To a programmer, this statement mixes different fields up. The origin of the issue is not using a serial comma, but mixing Names with Jobs. In a database, there would two fields: Names and Jobs. No one would set up a database mixing the two because it would cause much confusion and conflict (e.g., if you find someone's name but the field is already occupied by their title, where do you put the name?), and language should follow the clarity of such a database structure.

That is to say that the original statement should use all names, all jobs, or both. For example: They went to Oregon with Betty, a nurse; Jenny, a maid; and Jim, a cook. Or: They went to Oregon with Betty, a maid, and Jim, a cook. Or: They went to Oregon with Betty, who is both the maid and cook. These statements are clearly about three people, two people, and one person, respectively. The original statement was confusing because of the mix of descriptions: Names and Jobs.

Back to the first part: In actual code, it is necessary to separate items in a list with commas every time. (Of course, there usually isn't an and separating items...) I wrote a query in SAS using spaces to separate items and then passed it to Oracle, but Oracle generated an error. Oracle required the commas, whereas SAS implies the separation of items by spaces alone. For example: I need the names of people who do the following jobs: journalist, lawyer, and accountant; so I would write the following criteria (let's ignore the possible issue of case-sensitivity):

where job in ('Journalist', 'Lawyer', 'Accountant')

Each item requires the comma separator. Now had the report required the names of people who eat eggs, toast and jam, I would be confused. Would that be 'eggs', 'toast and jam' or 'eggs', 'toast', 'jam'? A better way to say it, whichever way was meant would be: eggs, toast, and jam; or eggs, and toast with jam. I would have to figure out whether the database recorded 'toast with jam' or 'toast and jam' or some other variant ('jammed toast'?), but either revision would be much clearer.

In the end, if the serial comma creates confusion, revise the statement altogether; otherwise, use the serial comma!

Proc SQL

Today at work, the following conversation took place:

Me: A watched proc SQL never quits.
Supervisor: Or worse, generates errors!

(Note: Proc SQL is procedure for using SQL in SAS.)