A government that can unpivot

I have over the years benefited greatly from the pitot-table feature of Excel. It was a pleasure to see the feature reinvented as PowerPivot that can more directly interact with an abstract data model. To be honest, I’m frequently frustrated by its feature not being as fully developed as other tools, but when Excel is the only data tool available, it is reasonably useful.

One of the earlier frustrations with the pivot tool was when working with data where a record contained multiple metrics. Using a different metric required replacing the data value with the desired data metric. When adding multiple data values they would show up as different rows or columns, but the different values would not behave as separate dimensions. The different values were multiple values associated with the same set of dimensions. This is often frustrating. Although a data source reasonably aggregated data with multiple measures on the same record, with each measure sharing the same dimensional parameters, the different metrics deserve separate metrics.

It is possible to work around the problem with a normalized schema of a single set of dimensions linked to multiple metrics, each with its own label. The result often leads to combinations of dimensions and measure dimensions that Excel cannot figure out.

A more robust solution is to de-normalize the data. This takes what was once a single record and clones it multiple times with two additional column: one that describes the metric, and the other to provide the metric. This makes the metric description a dimension and results in a single value in the data field, to be computed statistically.

Early on, I wrote procedural programs to rearrange the data, and in particular to de-normalize the data, resulting in multiply the same content in multiple records with only the metric name and value changing. Doing so, I prepared the input data to the pivot function in such a way that I can pivot on the derived dimensions of the measurement description.

Excel’s added PowerView or PowerQuery feature introduced a new unpivot function. When I first learned of this, I was relieved by the realization that I was not alone in wanting this feature. Certainly, I didn’t ask for it, but it came any way. Clearly others were doing what I was doing.

The problem is inherently inefficient. In order to leverage the full flexibility of the pivot feature, you first have to unpivot a data set so that can you can pivot it in a different way. The unpivot is wasteful, at least conceptually, by repeating the same dimensional information in multiple rows while only changing the metric value and description. However, doing this makes the final pivot table easier to use and easier to allow other people to use.

Optimal pivoting requires optimal unpivoting. If unpivoting is not an option, then the pivot feature will not be as useful.

In this blog, I have been imagining a government by data and urgency, where such a government constantly collected data but where the default condition is minimizing any enforced restrictions. The government will become authoritarian in nature only when the overall population expresses some urgency. Once triggered, the policy would be determined by some pre-determined algorithm applied to the most recent data. The actual policy may not be comprehensibly related to the urgency at hand, but the government will still have full authority to enforce the policy. The policy will be based on the the predefined objectives and priorities and will use all available data, not just the data related to the urgency. However, at least in my concept, the policies are short lived with a fixed expiration date not far into the future.

The concept is that a well-enforced policy will quickly change behavior to the extent that the behavior will continue even after the policy is removed. In fact, the policy adoption may continue to expand long after the government stops enforcing the policy. An example comes from recent events with the imposition of mask mandates in public spaces. In some areas, the mandates are at least not as forcefully enforced, but still most of the population continues to wear their masks, and though peer-pressure encourage others to begin doing the same. The point is that a policy does not need to be perpetual in order to have a perpetual impact. The impact of the policy lives longer than then policy itself is in effect.

One of the short-comings of the enlightenment style governments, such as democracies, is the confidence that deliberative processes can identify a permanent truth that justifies the perpetual enforcement of some complementary policy. Most of the time, the permanent truth falls apart but we still retain the rules derived from the earlier misconception. Even when the truth is long-lasting, the policies can end up being unenforced because the population voluntarily complies. At this point, there is no reason to keep the rules in place: the population has adjusted. Again, the current example of people wearing face masks that started as a mandate for indoor spaces (at least in the area I live) but now it is basically universal for anyone outside of their homes, and a lot of people even inside their own homes or vehicles.

There is an appeal of perpetual laws in that the law always remains available for prosecution when needed. An example is the body of law concerning various levels of homicide. People will always act in ways that leads to the death of another. Each time this happens, we desire the option to be able to convict the one whose actions lead to the death. Even in this extreme example, I question whether this is actually necessary. The population now fully understands the importance of avoiding endangering the lives of others. For the vast majority, they do not need a law to discourage dangerous behavior. On the other hand, having the laws in place do not stop those who act in ways that endanger the lives of others. Ultimately, I question the necessity or utility of a basic law like forbidding. If no law existed for this, how different would the murder rates be, or how different would be the number of unpunished murderers.

There will be some difference, but I suspect it would be self-limiting. In the unlikely event that the civilization begins to collapse to the point of widespread murder, there remains the option of activating the authoritarian enforcement of a data-driven policy based on the current data and the long-established goals and priorities. My point is that we can tolerate some level of something like murder, and indeed tolerate some level of people getting away with murder. If we had no laws in force against murder, we may similarly tolerate the resulting rates, as long as they remain reasonable. I suspect they will, at least for a significant period of time.

The concept of laws for homicide is something that might be unpivoted. Instead of having a set of parameters, such as defining a degree of homicide, having a fixed metric of punishment, we could have a descriptive column for a metric. The description and metric pair would then be free to add on other options to the “punishment” and “sentence” default we have now. Once we permit this type of de-normalization, we will recognize that we have multiple responses to the crime.

My biased opinion is that most persons guilty of homicide are unlikely to repeat a similar offense in the future. If this is true, there is nothing to gain from imposing a sentence. Certainly, there is a small population who will go on and commit more homicides, and maybe to the point of achieving some tyranny through terrorism. The government can respond to that condition as something different from normal homicide. My imagined government by data and urgency has the full authority to crack down on this even though it may cause collateral impact on innocent people.

My imagined government of, for, and by data has an objective of optimizing the future. The future will only include the survivors from the present. The future survivors, in that distant time, will tolerate quite a bit of nastiness in the present. The casualties of the present will not be around in the future to complain.

The darkness of the previous few paragraphs is extreme. The more basic point is allowing government to allow multiple responses to be applied to a condition, and allowing the government to treat each condition-response pair as a separate entity for consideration. We can take multiply something like a specific degree of homicide to make multiple results being different responses for the same condition. This unpivots the condition so that we can satisfy our desire for a response by choosing multiple options where only one involves punishing the responsible person.

I think a major failing of enlightenment-inspired governments and democracies is the expectation of a consistent response to a singular condition. The enlightenment ideal is of fairness of justice: if one person suffers some punishment for some action, then everyone else guilty of the same type of action in any future time should suffer the punishment. At stake are the concepts of consistency and fairness.

The elevation as an ideal the concept of fairness and consistency can unnecessarily constrain our options for optimizing the future. Once punished, there is nothing we can do to undo the punishment, especially if the punishment is swiftly finalized.

I’ve questioned before the legitimacy of incarceration as a punishment. At the time, I objected to how people will continue to serve time long past the time when the crime was as aggressively enforced as during the present. Another problem is how a continued punishment of a distant crime serves as a reminder that someone continues to suffer a punishment for something someone recently repeated. That reminder alone compels us to be consistent in order to be fair.

Consistency and fairness is one of many options when considering a response to a current condition. Unpivoting the situation can result in multiple options, each with the same condition, but each varying by some label and some metric. One option would be a particular punishment, but it would compete with equally valid responses that may leave the guilty unpunished. There are more options to optimize the future, when the government has full access to all the options available for the condition.

This type of optimization is probably impossible with human government. The people in the positions of making these decisions will inevitably be held to a standard of consistency and fairness for any response to repeated condition. The governor does not have the option of dismissing past responses as irrelevant to the current condition.

In contrast, an automated policy-making by some predefined algorithm using current data could be free to make such inconsistent decisions. Assuming that we can trust the algorithm and the available data, we can accept the resulting policies as attempting to best exploit the opportunities for the future while avoiding the worst hazards. With this perspective, there is no demand for consistency with the past. The actions of the past had their opportunities to improve its future, or our present. Forcing us to repeat the past actions inevitably requires us to ignore questioning whether the past action gave us any benefit outside of some emotional satisfaction of being consistent and fair.

All throughout government, there are rigid patterns of government with very fixed associations of responses for very specific conditions. If there was any really future benefit to the initial responses, that benefit is now in the distant past yet now we continue to follow the same guidance. The default response to a condition is the same as the response we did before as long at it didn’t completely cause some disaster before. There are no other options available. Potential have the burden of the proof that they will be better than the old response that has quantifiable data about its impact.

Fundamentally, a government by data and urgency inverts the burden of proof. The default presumption is to do something different than before, placing the burden of proof one what has already been tried. The proof is whether it will succeed in the future, and often past performance alone has no predictive power. In a government by data and urgency, the fundamental fact is that we have access to more current information that was not available when past decisions were made. The new data is not only more recent, but with technological advancement, there is more variety in the data and more dimensions for an algorithm to consider. In such circumstances, it is not reasonable to expect that a past response is relevant to a current condition no matter how closely the characteristics of the conditions matches some past event.

I propose that we would benefit under a government that is free to unpivot its data to permit a multitude of answers to the same basic conditions. This is contrary to the current government that demands a single solution and that single solution to be repeated perpetually. This approach is inevitable consequence of how we produce legislation and bureaucratic policies. Human deliberation and human accountability forces us into having no option to consider some radically different approach even with the approach is supported by data not previously available.

An example of government policy that could benefit from unpivoting or denormalization is healthcare. In the past, I wrote about innovating healthcare by breaking it apart.

  1. I described breaking health insurance into different age cohorts where society prioritizes the healthcare of the youngest population and their parents in order to assure an optimal future. The consequence is to leave older age groups, past the child-raising years, to have less support especially for major and costly chronic illnesses.
  2. I described breaking apart the health delivery, in particular to separate how we accommodate different diseases. Acute or infectious diseases are best treated at home or in specialized centers that are separate and distinct from hospitals treating chronic and non-infectious diseases. The treatment of the acute conditions take precedence over chronic treatments. The ideal location of acute care is at home with telemedicine or house visits by travelling medical staff.

In contrast to current debate that centers around competing concepts of unification of healthcare, we could instead be thinking about a variety of health care for different conditions. By the very definition, there will be many more distinct options available than what would be relevant for any particular person.

Currently, we have a notion of comprehensive health insurance that specifies benefits for the entire range of possible health-care services any person may encounter. Meanwhile, our providers are linked to hospitals and those hospitals are a single facility that attempts to be prepared for any and all medical needs.

Breaking apart healthcare can present opportunities unavailable to us currently. The segregation of different age groups and of distinctions between acute, infectious, and chronic care can lead to specializations that may create new markets for healthcare devices, and easier paths of entry for medical staff. For example, it is probably easy and quick to train people to work in managing infectious diseases. It is probably easier to staff the treatment of chronic diseases for younger people who generally have good prospects of improving compared to treating comparable cases among older people who have little prospects of improving.

Thinking more specifically about the current pandemic, I suspect the large burst of excess deaths early on was a result of attempting to accommodate the situation within a multi-purpose approach. Cases needed to be sent to hospitals that during normal times had high occupancy for non-infectious conditions. Similarly, doctors and nurses trained to handle all manner of conditions were spending time with a large number of patients with largely very similar needs and concerns. Consequently, we had to shut down hospitals from their other functions in part because their providers were busy with the pandemic, and in part to minimize risk of spreading contagion within the hospital.

More recently, the recently introduced vaccine is leading us toward situation that may turn out toe be worse than the previous year. The problem is that people are having reactions to the vaccines. This is slowing the vaccination roll out because there is need to more carefully pre-screen the recipients, and to accommodate close watching for a period of time after the innoculuation.

There are additional problems with the scheduling. Health departments are making decisions to follow vaccination schedules that were not tested in during the earlier trials. In particular, to continue to get more people with their first shots, the providers are postponing the second injection or substituting different vaccines for the the second injection. The testing that won emergency use authorization found a benefit only after the second injection, having just one injection may not provide any benefit and in fact may make the individual more susceptible to infections. There is no testing of what would happen when different vaccine formulations are uses for the two injections.

What stands out to me is how inflexible this process is running out. There is no slowing in the distribution of the vaccines even as there are reports of severe adverse reactions. Similarly, there is no slowing despite the fact that it is clear we will not be able to adhere to the defined schedule of two shots from same formula spaced within a specific time window. The process is not working as planned, and yet the process proceeds exactly as it would if everything were running smoothly.

I fear that in the coming months we will experience a calamity that is a combination of multiple failures. The vaccines will not stop the spread of the virus and will not change the case fatality rates. The vaccine itself will continue to accumulate adverse reactions that possibly may result in chronic illnesses for people who either would not be susceptible to the virus, or would fully recover within a few days. The haphazard vaccine schedule may lead more people susceptible to the disease than they would have been without a vaccine.

An alternative approach would be to unpivot the situation to make multiple responses available, each being equally valid to pursue. The validity is from the perspective of optimizing the future benefit of the survivors. Implicitly, this approach accepts losses, and inconsistent reasons for the losses. Different populations would have access to different options (and lack of access to others). People getting one treatment may have benefited more by getting another, but there are similar examples with those with their options swapped. Each individual is different, thus having different options for different people may not matter much.

There is a risk to mandating a single option for everyone. If that option would eventual start to show serious problems later on, everyone would face the risk of experiencing similar problems. Having a multitude of options available for different groups, and pursuing all of these could have a better overall outcome than focusing on the current objective of vaccinating everyone with a very new technology.

The government by data and urgency is not a very humane government. It reduces people to data points with a large number of dimensional attributes. During calmer times, it will quietly collect data. When a crisis comes up, it will look at the data points to find ways to make the best of the situation. From this perspective of just considering the data and the goals of having the best future outcomes, I can imagined a diversified portfolio approach, permitting a large number of different approaches for different populations. Some may work better than others, but when aggregated over the entire population, this approach may be superior to our current approach, especially when considering the future prospects of the survivors.

I visualize this as our current approach of assigning each individual with a single record that has a predefined value: whether or not the individual has a vaccine. We can transform the possibilities by unpivoting the data to create an attribute description of “vaccine” with a corresponding value of “yes or no”. This opens the possibility of population the attribute with other options instead of vaccine.

Prior to the unpivot, we had a column heading of “vaccine” and thus our only choice was to supply the value of yes or no.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s